<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=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-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.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Plain Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.PlainTextChar
{mso-style-name:"Plain Text Char";
mso-style-priority:99;
mso-style-link:"Plain Text";
font-family:"Calibri","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@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="MsoPlainText">Hi Mark,<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">If by the server account you mean the instance owner (typically ingres) then yes as they will have trace point privilege. Your own account may not have that privilege.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">The command to set trace record directory and turn on sc930 do NOT need to be placed in your ESQLC code, you can just do those separately at a terminal monitor connection.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">I think Alex has mentioned that if you have multiple dbms servers running then you will have to connect to each one and issue the statement. To determine if you have multiple servers log in as the instance owner and fire up the iinamu
command. Type the command 'show servers' and then 'quit' at the prompt. You should see a display like:<o:p></o:p></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">[ingres@ithubII ~]$ iinamu<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">Ingres NAME SERVICE MANAGEMENT UTILITY --<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">-- Copyright (c) 2009 Actian Corporation<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">IINAMU> show servers<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">STAR * 34106<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">DASVR * 45373<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">COMSVR * 43145<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New";background:yellow;mso-highlight:yellow">INGRES * 33231 (sole server)</span><span style="font-family:"Courier New""><o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">IUSVR * 36993<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">IINMSVR * 46534<o:p></o:p></span></p>
<p class="MsoPlainText"><span style="font-family:"Courier New"">IINAMU> quit <o:p>
</o:p></span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">This says on my instance that I have a single server ...the thing labelled INGRES * ... and it is also configured as a sole server.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">If you are not sure what you have then send the output and we can interpret it for you.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">You mentioned in your other email that sqlca.sqlcode was not < 0…is it zero? If not what value is it?<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Marty<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText"><span lang="EN-US">-----Original Message-----<br>
From: Mark [mailto:i@dontgetlotsofspamanymore.net] <br>
Sent: 16 May 2018 16:12<br>
To: info-ingres@lists.planetingres.org<br>
Subject: Re: [Info-ingres] Weird problem in Ingres 10</span></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">On Wed, 16 May 2018 12:11:08 +0000, Martin Bowes<o:p></o:p></p>
<p class="MsoPlainText"><martin.bowes@ndph.ox.ac.uk> wrote:<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">>Just to expand on Karl's sc930...You may already know this...<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>To turn it on, make a recording directory...mkdir /full/path/to/directory'<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>And then...<o:p></o:p></p>
<p class="MsoPlainText">>sql iidbdb << SQL_END<o:p></o:p></p>
<p class="MsoPlainText">>set trace record '/full/path/to/directory';<o:p></o:p></p>
<p class="MsoPlainText">>set trace point sc930 1;<o:p></o:p></p>
<p class="MsoPlainText">>\p\g<o:p></o:p></p>
<p class="MsoPlainText">>\q<o:p></o:p></p>
<p class="MsoPlainText">>SQL_END<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">Do I need to run this on the server account? I have implemented it<o:p></o:p></p>
<p class="MsoPlainText">currently in the embedded SQL of my program.<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">>FYI. The digit after the sc930 indicates a tracing level, 1 should be sufficient.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>Run the errant query.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>And turn off the sc930.<o:p></o:p></p>
<p class="MsoPlainText">>sql iidbdb << SQL_END<o:p></o:p></p>
<p class="MsoPlainText">>set trace point sc930 0;<o:p></o:p></p>
<p class="MsoPlainText">>\p\g<o:p></o:p></p>
<p class="MsoPlainText">>\q<o:p></o:p></p>
<p class="MsoPlainText">>SQL_END<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>You can now access the recording directory and start scanning the files for any sign of life from your query.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>Marty<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>-----Original Message-----<o:p></o:p></p>
<p class="MsoPlainText">>From: Karl and Betty Schendel [mailto:schendel@kbcomputer.com]
<o:p></o:p></p>
<p class="MsoPlainText">>Sent: 16 May 2018 12:54<o:p></o:p></p>
<p class="MsoPlainText">>To: Ingres and related product discussion forum<o:p></o:p></p>
<p class="MsoPlainText">>Subject: Re: [Info-ingres] Weird problem in Ingres 10<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>On May 16, 2018, at 7:44 AM, Mark <i@dontgetlotsofspamanymore.net> wrote:<o:p></o:p></p>
<p class="MsoPlainText">>> <o:p></o:p></p>
<p class="MsoPlainText">>> On Tue, 15 May 2018 12:27:09 -0400, Karl and Betty Schendel<o:p></o:p></p>
<p class="MsoPlainText">>> <schendel@kbcomputer.com> wrote:<o:p></o:p></p>
<p class="MsoPlainText">>> <o:p></o:p></p>
<p class="MsoPlainText">>>> It's certainly not something I have heard of or seen before. Do you have any rules<o:p></o:p></p>
<p class="MsoPlainText">>>> defined on the relevant tables? Try enabling LOG_TRACE if the problem is<o:p></o:p></p>
<p class="MsoPlainText">>>> sufficiently predictable, or do a logdump after the problem occurs if it's not;<o:p></o:p></p>
<p class="MsoPlainText">>>> the idea being to try to see whether you actually got any PUT (insert) or<o:p></o:p></p>
<p class="MsoPlainText">>>> REP (replace) log records that were then rolled back, or whether the insert / update<o:p></o:p></p>
<p class="MsoPlainText">>>> was never executed at all.<o:p></o:p></p>
<p class="MsoPlainText">>> <o:p></o:p></p>
<p class="MsoPlainText">>> After enabling log_trace all I got was:<o:p></o:p></p>
<p class="MsoPlainText">>> <o:p></o:p></p>
<p class="MsoPlainText">>> LOG: SAVEPOINT Size written/reserved: 0/ 0 Flags:<o:p></o:p></p>
<p class="MsoPlainText">>> -------------------------------------------------------------------<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>So the insert/update isn't ever being executed. Either it's failing with some sort of<o:p></o:p></p>
<p class="MsoPlainText">>silent error, which would seem odd, or it's not reaching the backend at all, or<o:p></o:p></p>
<p class="MsoPlainText">>it's being pre-empted by a before rule. I think the next step would be to enable<o:p></o:p></p>
<p class="MsoPlainText">>sc930 tracing and see if the backend is getting the insert-update, and what<o:p></o:p></p>
<p class="MsoPlainText">>end-of-query status it's recording. There should be KB articles on enabling<o:p></o:p></p>
<p class="MsoPlainText">>SC930 tracing.<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>Karl<o:p></o:p></p>
<p class="MsoPlainText">><o:p> </o:p></p>
<p class="MsoPlainText">>_______________________________________________<o:p></o:p></p>
<p class="MsoPlainText">>Info-ingres mailing list<o:p></o:p></p>
<p class="MsoPlainText">>Info-ingres@lists.planetingres.org<o:p></o:p></p>
<p class="MsoPlainText">>http://lists.planetingres.org/mailman/listinfo/info-ingres<o:p></o:p></p>
<p class="MsoPlainText"><o:p> </o:p></p>
<p class="MsoPlainText">-- <o:p></o:p></p>
<p class="MsoPlainText"><insert witty sig here><o:p></o:p></p>
<p class="MsoPlainText">_______________________________________________<o:p></o:p></p>
<p class="MsoPlainText">Info-ingres mailing list<o:p></o:p></p>
<p class="MsoPlainText">Info-ingres@lists.planetingres.org<o:p></o:p></p>
<p class="MsoPlainText">http://lists.planetingres.org/mailman/listinfo/info-ingres<o:p></o:p></p>
</div>
</body>
</html>