Droping a non exists temporary temp table does not cause ISAM error -111, only error 206.

Hi What could be the reason why the system does not throw the ISAM -111 error when dropping the temporary table? We have a drop_temptable spl routine witch is handeling -111 exception. This code has been working correctly for about 10 years. How can we find the reason, what causes this malfunction? We added the -206 to the exception handling. IBM Informix Dynamic Server Version 14.10.FC3 -- On-Line -- Up 32 days 20:17:41 -- 1630592 KbytesSess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 51901 DROP TABLE capsys CR Wait 3 -206 0 4.10 Off Current Role : ZeusAdm Current SQL statement (3147) : drop table tmpbefjdij2;Stored procedure stack : context proc-counter opcode name ------------------------------ 0x000000008c910be0 0x820cdee0+0x0010 EXPR capsys:drop_temptable IBM Informix Dynamic Server Version 14.10.FC3 -- On-Line -- Up 32 days 20:16:45 -- 1630592 KbytesSess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 51901 DROP TABLE capsys CR Wait 3 -206 -111 4.10 Off Current Role : ZeusAdm Current SQL statement (3108) : drop table tmpcartfelvmod;Stored procedure stack : context proc-counter opcode name ------------------------------ 0x0000000067371c58 0x820cdee0+0x0010 EXPR capsys:drop_temptable ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Might be simply: ISAM code 111 indicates something hadn't been found in a table, e.g. a system table. Real temp tables don't get recorded in any (system) tables, they only exist in their session's minds (structures). So what's the significance of your two onstat outputs? What's the difference between tmpbefjdij2 and tmpcartfelvmod ? Both are called tmp*, but are they really both temp tables? Andreas ------------------------------ Andreas Legner ------------------------------

Yes booth table is a real temp table. The drop_temptable procedure has this code. The _tabname is input variable for the drop_temptable procedure. The exec function is from ExecIt datablade. foreach select 'drop table ' || trim(_tabname) || ';' into sqlstmt from sysmaster:sysduals where not exists ( select 1 from systables where tabname = _tabname ) on exception in ( -111 ) -- No record found end exception; let nemkell = exec( sqlstmt ); end foreach; We are using this procedure more than 10 years. We are using this Informix version more than 1 year. We basically use the following method to manage temporary tables. execute procedure drop_temptable('temptablename1'); create temp table temptablename1( tmpcol1,... ) with no log; ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Gábor: It might be a bug in the 14.10.FC3 release. I have .FC9W1 and it is working here: $ onstat -g ses 10 IBM Informix Dynamic Server Version 14.10.FC9W1DE -- On-Line -- Up 1 days 13:19:33 -- 1119364 Kbytes 2023-02-15 07:38:40session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 10 informix - 1 246216 Elezar-II 1 110592 88688 off Program : /opt/informix/infmx.14.10.FC9W1DE/bin/dbaccess tid name rstcb flags curstk status 1152 sqlexec 44c5e890 Y--P--- 3728 cond wait sm_read - Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 10 V 468fe040 110592 21904 76 12 name free used name free used overhead 0 3352 scb 0 144 opentable 0 3816 filetable 0 536 log 0 16536 temprec 0 21664 keys 0 176 gentcb 0 1616 ostcb 0 2992 sqscb 0 26456 hashfiletab 0 552 osenv 0 2576 sqtcb 0 7792 fragman 0 336 sapi 0 144 sqscb info scb sqscb optofc pdqpriority optcompind directives 45f331c0 468ff028 0 0 0 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 10 - art CR Not Wait -206 -111 9.24 Off Last parsed SQL statement : drop table fred_temp ------------------------------Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

But his both onstat outputs where from 14.10.FC3 which is why I asked about their significance. What's the ExecIt blade? The code Gábor posted would cause a 206/111 (even without ExecIt), but for sysduals (rather than sysdual)... ------------------------------ Andreas Legner ------------------------------

Is there a pattern as to when you get the ISAM of 0 and when you get the ISAM of -111 as in each of your examples? I doubt that it's just random. Did the table exist in one example, and not exist in the other. I can't explain why this was working, and now it's not, but the use of "sysduals" (not sysdual) should trigger the -206/-111 regardless of whether or not the temp table exists. Also, I would think that the "on exception" block should surround the SQL statement being executed. This is not related to the problem that you are now seeing with the -111/-206 errors, but as an aside, the procedure could likely be replaced with: drop table if exists temptablename1; ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------