Erroc when dropping a table -- how to debug?

Hi, after some queries I try to drop a temporary (created as CREATE TEMP TABLE) table which results in SQLCODE -242 (Could not open database table table-name.) and an accompanying ISAM error 103 (no exclusive access afaik). The table was created by the same session and afaik no other process should be able to interfere with that table. This makes me wonder if I still have some cursors/locks somewhere that result in that behaviour. Is there any way for me to run a query from that connection when the error occurs that would provide me with more information -- or am I missing something plain and simple? Thanks & regards, Florian

Sounds weird ... is this situation still persisting? Could it even be=20 reproduced? Re. -242 "could not open database table": a temp table should not be=20 considered a "database table", and dropping it should not cause any=20 catalog activity. Re. -103 "illegal key descriptor": this would indicate some sort of=20 index related problem, but again can't see how this could occur. I'd suggest: - onmode -I -103 to enable error trap - retry dropping the table (causing the -103) - onmode -I to clear the error trap - secure the af file (and shmem dump) created from error trap - open a PMR if worth the effort - otherwise post at least the sqlexec thread's Stack here, plus the=20 'onstat -g ses <sid>', both from af file. From: "FLORIAN APOLLONER" <florian.apolloner@bap.at> To: ids@iiug.org Date: 18.05.2017 11:19 Subject: Erroc when dropping a table -- how to debug? [39228] Sent by: ids-bounces@iiug.org Hi,=20 after some queries I try to drop a temporary (created as CREATE TEMP=20 TABLE)=20 table which results in SQLCODE -242 (Could not open database table=20 table-name.) and an accompanying ISAM error 103 (no exclusive access=20 afaik).=20 The table was created by the same session and afaik no other process=20 should be=20 able to interfere with that table.=20 This makes me wonder if I still have some cursors/locks somewhere that=20 result=20 in that behaviour. Is there any way for me to run a query from that=20 connection=20 when the error occurs that would provide me with more information -- or am = I=20 missing something plain and simple?=20 Thanks & regards,=20 Florian=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

I wonder if the 2nd error may actually be "-106 ISAM error: non-exclusive access", this is the most common error. There is a writeup on this at https://www-01.ibm.com/support/docview.wss?uid=swg21450868. For temporary tables you will not be able to get the partnum from systables. From the IIUG software repository http://members.iiug.org/software/index_all.html try find_tmp_tbls. Otherwise use onstat -g ses to get thread ids for the session and onstat -g opn to see what those threads have open, then eliminate any permanent tables from the list. Regards, David. > On 18 May 2017 at 10:32 Andreas Legner <andreas.legner@de.ibm.com> wrote: > > > Sounds weird ... is this situation still persisting? Could it even be=20 > reproduced? > > Re. -242 "could not open database table": a temp table should not be=20 > considered a "database table", and dropping it should not cause any=20 > catalog activity. > Re. -103 "illegal key descriptor": this would indicate some sort of=20 > index related problem, but again can't see how this could occur. > > I'd suggest: > - onmode -I -103 to enable error trap > - retry dropping the table (causing the -103) > - onmode -I to clear the error trap > - secure the af file (and shmem dump) created from error trap > - open a PMR if worth the effort > - otherwise post at least the sqlexec thread's Stack here, plus the=20 > 'onstat -g ses <sid>', both from af file. > > From: "FLORIAN APOLLONER" <florian.apolloner@bap.at> > To: ids@iiug.org > Date: 18.05.2017 11:19 > Subject: Erroc when dropping a table -- how to debug? [39228] > Sent by: ids-bounces@iiug.org > > Hi,=20 > > after some queries I try to drop a temporary (created as CREATE TEMP=20 > TABLE)=20 > table which results in SQLCODE -242 (Could not open database table=20 > table-name.) and an accompanying ISAM error 103 (no exclusive access=20 > afaik).=20 > The table was created by the same session and afaik no other process=20 > should be=20 > able to interfere with that table.=20 > > This makes me wonder if I still have some cursors/locks somewhere that=20 > result=20 > in that behaviour. Is there any way for me to run a query from that=20 > connection=20 > when the error occurs that would provide me with more information -- or am = > > I=20 > missing something plain and simple?=20 > > Thanks & regards,=20 > Florian=20 > > ***************************************************************************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, > I wonder if the 2nd error may actually be "-106 ISAM error: non-exclusive access", this is the most common error. Diagnostics returns that message, though I am not sure why my code returns 103 as error num -- but I agree, it should be 106 (will double check that) > From the IIUG software repository http://members.iiug.org/software/index_all.html try find_tmp_tbls. > Otherwise use onstat -g ses to get thread ids for the session and onstat -g opn to see what those threads have open, then eliminate any permanent tables from the list. Ok, will see if I can come up with something. Cheers, Florian

Hi, > Sounds weird ... is this situation still persisting? Could it even be reproduced? Yes, actually easily. Just not so easy to put into a separate program that I could show. > Re. -103 "illegal key descriptor": this would indicate some sort of index related problem, but again can't see how this could occur. Interesting, there is a check constraint -- gotta see if that is indeed the case (that said I actually think that the error should be 106 [at least according to diagnostics text]) > - secure the af file (and shmem dump) created from error trap Is there anything I could read out from those files or is that just useful for IBM? I'll have to read up on how to generate the requested information, will get back to you. Thanks & cheers, Florian

> I wonder if the 2nd error may actually be "-106 ISAM error: non-exclusive access", this is the most common error. I just confirmed that this is the error. Have to check where/how I get 103 from, but for now lets operate under the assumption that it is indeed -106

Okay, the find_tmp_tbls script didn't work (have to debug that later on, but getting ksh syntax errors for now). As for onstat, the relevant session/thread information is: https://gist.github.com/apollo13/3e3afdc6e6db41df7edfd2b9974bfb5a The second partnum there is: partnum 4196104 dbsname persta owner informix tabname systables collate de_DE.819 dbsnum 4 I'll have to doublecheck why systables is there, I do not think I explicitly opened it in my code. Can this be informix on it's own? Cheers, Florian

Hi Andreas, I've managed to get an af file by capturing the error 242 -- 103/106 did never trigger, so I assume onmode -I wants the "major" error num and not the ISAM errorcode. I've uploaded the file to http://apolloner.eu/~apollo13/.tmp/af.956a1ad -- is there any indication in there about what is going wrong? Thanks & cheers, Florian

Hi Florian, in that af file, thread 1390 (session 762), is trying to drop the qfakt=20 table (partnum 600005), running on -242/-106. Searching the file for 600005 you'll find two hits in 'onstat -g opn'=20 output, for thread 1390 having the table opened twice. So something in your session still would have a cursor or two open in this = .... ? HTH, Andreas From: "FLORIAN APOLLONER" <florian.apolloner@bap.at> To: ids@iiug.org Date: 18.05.2017 15:53 Subject: Re: Erroc when dropping a table -- how to debug? [39236] Sent by: ids-bounces@iiug.org Hi Andreas,=20 I've managed to get an af file by capturing the error 242 -- 103/106 did=20 never=20 trigger, so I assume onmode -I wants the "major" error num and not the=20 ISAM=20 errorcode. I've uploaded the file to=20 http://apolloner.eu/~apollo13/.tmp/af.956a1ad -- is there any indication=20 in=20 there about what is going wrong?=20 Thanks & cheers,=20 Florian=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Damn it, I feared as much -- I thought I was using RAII everywhere (resource acquisition is initialization). Any thoughts on debugging that sanely?

Even a prepared statement that wasn't dropped will hold a lock on the tables it references, not just defined cursors. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 18, 2017 at 9:29 AM, Andreas Legner <andreas.legner@de.ibm.com> wrote: > Hi Florian, > > in that af file, thread 1390 (session 762), is trying to drop the qfakt=20 > table (partnum 600005), running on -242/-106. > > Searching the file for 600005 you'll find two hits in 'onstat -g opn'=20 > output, for thread 1390 having the table opened twice. > So something in your session still would have a cursor or two open in this > = > > ..... ? > > HTH, > Andreas > > From: "FLORIAN APOLLONER" <florian.apolloner@bap.at> > To: ids@iiug.org > Date: 18.05.2017 15:53 > Subject: Re: Erroc when dropping a table -- how to debug? [39236] > Sent by: ids-bounces@iiug.org > > Hi Andreas,=20 > > I've managed to get an af file by capturing the error 242 -- 103/106 did=20 > never=20 > trigger, so I assume onmode -I wants the "major" error num and not the=20 > ISAM=20 > errorcode. I've uploaded the file to=20 > http://apolloner.eu/~apollo13/.tmp/af.956a1ad -- is there any > indication=20 > in=20 > there about what is going wrong?=20 > > Thanks & cheers,=20 > Florian=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Found it, I had a ScopedCursor class which should clean up on leaving the scope -- but it ended up being a temporary variable and got destroyed immediately and not when the scope ended XD Thanks for your help! Cheers, Florian

Nope. It's an exercise in going through the code and making sure every PREPARE and DECLARE has corresponding CLOSE and FREE statements. A quick fix would be to SET AUTOFREE; in the session before declaring the cursors. This will automatically free cursors when they are closed. There is also the OPTOFC environment variable. setting that to '1' causes cursor open operations to be deferred until the first fetch and the last fetch that drains the cursor also automatically closes it. If OPTOFC is set and AUTOFREE is also set then draining a cursor closes and frees it automatically. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 18, 2017 at 9:34 AM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Damn it, I feared as much -- I thought I was using RAII everywhere > (resource > acquisition is initialization). Any thoughts on debugging that sanely? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >