But his both onstat outputs where from 14.10.FC3 which is why I asked about their significance.
The code Gábor posted would cause a 206/111 (even without ExecIt), but for sysduals (rather than sysdual)...
Original Message:
Sent: Wed February 15, 2023 07:43 AM
From: Art Kagel
Subject: Droping a non exists temporary temp table does not cause ISAM error -111, only error 206.
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:40
session 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
Original Message:
Sent: Wed February 15, 2023 06:00 AM
From: Gábor Fekete
Subject: Droping a non exists temporary temp table does not cause ISAM error -111, only error 206.
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
Original Message:
Sent: Wed February 15, 2023 05:01 AM
From: Andreas Legner
Subject: Droping a non exists temporary temp table does not cause ISAM error -111, only error 206.
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
Original Message:
Sent: Wed February 15, 2023 04:53 AM
From: Gábor Fekete
Subject: 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 Kbytes
Sess 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 Kbytes
Sess 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
------------------------------