<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.webworksnumber
{mso-style-name:webworks_number;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">It’s on unjournalled tables with long varchar columns.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I’ve just developed a test case doing simple inserts into the table. Each insert has to be in a separate session. Break 2048 inserts
and the auditdb goes nuts.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Try This (test.sh):<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">#! /bin/bash<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">nInserts=$1<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">##destroydb bowtest > /dev/null<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">##<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">##createdb bowtest<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">##<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">##ckpdb +j bowtest<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">##<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">sql bowtest << SQL_END<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">drop table if exists test_long;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">create table test_long<span style="background:teal;mso-highlight:teal">(<o:p></o:p></span></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> id integer4 not null not default,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> a_long long varchar not null not default,<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> a_date ingresdate not null default 'now'<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console";background:teal;mso-highlight:teal">)</span><span style="font-size:10.0pt;font-family:"Lucida Console""> with nojournaling;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">commit;<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">\p\g<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">\q<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">SQL_END<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">ckpdb +j bowtest<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">for ((id=1; id<=$nInserts; id++)); do<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> sql -S bowtest << _SQL_INSERT<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> insert into test_long(id, a_long) values ($id, 'tom, dick, and harry.');<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> \g<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""> \q<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">_SQL_INSERT<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">done<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">auditFile=auditFile.${nInserts}.log<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">auditdb -a bowtest >& $auditFile<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">nErrors=`grep -c E_DM $auditFile`<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">nOpen=`egrep 'End : |End Mini: |Begin :' $auditFile | awk '{print $5}' | sort | uniq -c | sort | grep -e' 1 ' | wc -l`<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console""><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">echo "$auditFile:"<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">echo " E_DM: $nErrors"<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Lucida Console"">echo " open: $nOpen"<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">The test.sh 2050 … takes a little time but it’s worth it!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Paul White <paul.white@shift7solutions.com.au>
<br>
<b>Sent:</b> 06 May 2021 10:09<br>
<b>To:</b> info-ingres@lists.planetingres.org<br>
<b>Subject:</b> Re: [Info-ingres] Error from auditdb<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<pre>I reckon it happens when there is a usermod or sysmod on a table with a long byte field right?<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Marty writes<o:p></o:p></pre>
<pre>> <span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">The alteration to iirelation affects only the </span><span style="font-family:"Lucida Console"">rellow_logkey</span><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> field. Anyone know what that refers to?<o:p></o:p></span></pre>
<pre><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></pre>
<pre>Here is what I found looking up rellow_logkey and "end mini"<o:p></o:p></pre>
<p><a href="https://github.com/fosslc/Ingres/blob/master/src/dbutil/duf/duve/duvechk.sc">https://github.com/fosslc/Ingres/blob/master/src/dbutil/duf/duve/duvechk.sc</a><o:p></o:p></p>
<p><a href="https://communities.actian.com/s/article/Tracing-Use-of-Transaction-Log-File">https://communities.actian.com/s/article/Tracing-Use-of-Transaction-Log-File</a><o:p></o:p></p>
<p><a href="https://docs.actian.com/actianx/11.1/index.html#page/CommandRef/verifylob_Command--Analyze_Long_Data_Types.htm">https://docs.actian.com/actianx/11.1/index.html#page/CommandRef/verifylob_Command--Analyze_Long_Data_Types.htm</a><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<pre>verifylob Command--Analyze Long Data Types<o:p></o:p></pre>
<div style="margin-top:9.0pt;orphans: 2;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness:
initial;text-decoration-style: initial;text-decoration-color:
initial;word-spacing:0px" id="ww99645">
<pre style="vertical-align:baseline"><span style="color:black">The verifylob utility analyzes errors in long data types (blobs and clobs). It scans the specified blob table and examines the blob coupons and etab tables for errors. Rows with errors are dumped with error indicators.<o:p></o:p></span></pre>
</div>
<div style="margin-top:9.0pt;orphans: 2;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness:
initial;text-decoration-style: initial;text-decoration-color:
initial;word-spacing:0px" id="ww99646">
<pre style="vertical-align:baseline"><span style="color:black">Verifylob detects four types of errors:<o:p></o:p></span></pre>
</div>
<div style="margin-left:18.0pt;margin-top:6.0pt;orphans: 2;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness:
initial;text-decoration-style: initial;text-decoration-color:
initial;word-spacing:0px" id="ww99647">
<pre style="text-indent:-18.0pt;vertical-align:baseline"><span class="webworksnumber"><span style="color:black">•</span></span><span style="color:black">(D) Duplicate keys<o:p></o:p></span></pre>
</div>
<div style="margin-left:18.0pt;margin-top:6.0pt;orphans: 2;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness:
initial;text-decoration-style: initial;text-decoration-color:
initial;word-spacing:0px" id="ww99648">
<pre style="text-indent:-18.0pt;vertical-align:baseline"><span class="webworksnumber"><span style="color:black">•</span></span><span style="color:black">(L) Non matching lengths<o:p></o:p></span></pre>
</div>
<div style="margin-left:18.0pt;margin-top:6.0pt;orphans: 2;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness:
initial;text-decoration-style: initial;text-decoration-color:
initial;word-spacing:0px" id="ww99649">
<pre style="text-indent:-18.0pt;vertical-align:baseline"><span class="webworksnumber"><span style="color:black">•</span></span><span style="color:black">(O) Orphaned rows<o:p></o:p></span></pre>
</div>
<div style="margin-left:18.0pt;margin-top:6.0pt;orphans: 2;widows: 2;-webkit-text-stroke-width: 0px;text-decoration-thickness:
initial;text-decoration-style: initial;text-decoration-color:
initial;word-spacing:0px" id="ww99650">
<pre style="text-indent:-18.0pt;vertical-align:baseline"><span class="webworksnumber"><span style="color:black">•</span></span><span style="color:black">(R) Invalid iirelation rellow_logkey, rellow_logkey pair<o:p></o:p></span></pre>
</div>
<pre><o:p> </o:p></pre>
<pre>Next step, I plan to track the entries going to the transaction log to see if both BM (Begin Mini) and EM (End Mini) are being written. <o:p></o:p></pre>
<pre>My hunch is the BM is not being written to the journals because I am sure I reached the transaction limit error in the rollforwarddb.<o:p></o:p></pre>
<pre>Paul<o:p></o:p></pre>
</div>
</body>
</html>