Hi
1) Thank you for trying different systems. :)
2) Yes, I have already contacted IBM. They couldn't reproduce either.
However, it was possible to trap the -106 / -214 / -242 errors. (onmode -I [errno]). This generate the AF file when was the error occurs.
I hope they can analyze it soon.
3) I also tried to move the temp table to another dbspace. Creating the temp table in rootdbs causes the same error.
4) I upload my latest code.
I'll let you know what's causing the error if we find out.
------------------------------
Gábor Fekete
------------------------------
Original Message:
Sent: Tue February 25, 2020 03:08 AM
From: SangGyu Jeong
Subject: Drop temp table cause -106 non-exclusive access.
Hi Gábor,
I installed informix version 14.10.FC3DE on ubuntu 18.04.4 and centos 7.6 and tested it with the temp_test2 procedure, but the -242 / -106 error was not reproduced.
I'm sorry it didn't help.
Have you already contacted IBM?
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
Original Message:
Sent: Fri February 21, 2020 03:10 AM
From: Gábor Fekete
Subject: Drop temp table cause -106 non-exclusive access.
Hi Jeong
Sorry I wrote the example wrong for a foreach. Our foreach end before we want to drop it the temp table.
The correct example is this with the whole code.
drop procedure if exists temp_test2;
create procedure temp_test2(_repeat integer);
define i integer;
define num integer;
let i = 0;
while i < _repeat
create temp table tmp_test( t_num integer ) with no log;
foreach
select t_num
into num
from tmp_test
end foreach;
-- system 'sleep 0.001';
let i = i + 1;
drop table tmp_test;
end while;
end procedure;
execute procedure temp_test2(1000000);-- SQL Error (-242) : Could not open database table (informix.tmp_test). [ ISAM error -106 : ISAM error: non-exclusive access. ]
------------------------------
Gábor Fekete
Original Message:
Sent: Fri February 21, 2020 01:59 AM
From: SangGyu Jeong
Subject: Drop temp table cause -106 non-exclusive access.
Hi Gábor,
I ran sleep command inside foreach loop as below but got 106 error. How is it different from yours?
If you run the drop table outside of the foreach statement, of course there are no errors, but it should probably be handled that way...
create procedure temp_test(_repeat integer);define i integer;define num integer;let i = 0;while i < _repeatcreate temp table tmp_test( t_num integer );insert into tmp_test values (1);foreach select t_num into num from tmp_testsystem 'sleep 5';drop table tmp_test;end foreach;let i = i + 1;end while;end procedure;$ dbaccess testdb -Database selected.> execute procedure temp_test(100000); 242: Could not open database table (informix.tmp_test). 106: ISAM error: non-exclusive access.Error in line 1Near character position 1>
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
Original Message:
Sent: Thu February 20, 2020 01:16 PM
From: Gábor Fekete
Subject: Drop temp table cause -106 non-exclusive access.
Hi,
We tested the script in 2 different server, the first error occurs (prod enviroment), the second does not (dev enviroment).
1) cpuinfo: model name : Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz
"Ubuntu 18.04.3 LTS"
2) cpuinfo: Vendor=GenuineIntel Model=Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz
"CentOS Linux release 7.5.1804 (Core)"
The code only represents a working principle.The while loop represents as foreach on a customer table. The original code contains a separate procedure for handling the temporary table and we use it more then once. (The procedure leaves no trash behind.) We'll consider rewriting unless otherwise resolved.
This is my problem:
The -106 error occurs when a DDL(DROP, ALTER..) statement is executed without closing the cursor (the select/foreach finished) or statement in another session. The temp table is only available in my session.
If I put some code after the select/foreach and this code occurs 0.001-0.003 sec {system("sleep 0.001")} delay before a drop statement our code work fine. Our code will slow down a lot after that. (300000 row in the customer table.)
------------------------------
Gábor Fekete
Original Message:
Sent: Thu February 20, 2020 11:50 AM
From: SangGyu Jeong
Subject: Drop temp table cause -106 non-exclusive access.
Hi Gábor,
I tested your code with version 14.10.FC3, but the error wasn't reproduced as it was tested by Andreas.
The -106 error occurs when a DDL(DROP, ALTER..) statement is executed without closing the cursor or statement in another session.
Is the procedure code used in production? Or is it test code that omits some logic?
Should the temp table be dropped within the while loop? How about using the DELETE statement instead of DROP statement if the data is repeatedly inserted and deleted in a loop in the temp table?
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
Original Message:
Sent: Thu February 20, 2020 09:20 AM
From: Gábor Fekete
Subject: Drop temp table cause -106 non-exclusive access.
Hi All!
I ran into the next issue:
I created a temporary table and created a "cursor" on it (foreach or select). If I then want to drop the table, sometimes I get a non-exclusive access error.
What could cause my session to not have exclusive access? (I'm thinking about the cursor destroy mechanism cause it)
Version:
Informix 14.10.FC3
create database if not exists testdb;
drop procedure if exists temp_test;
create procedure if not exists temp_test(_repeat integer);
define i integer;define num integer;
let i = 0;
while i < _repeat
create temp table tmp_test( t_num integer );
select t_num into num from tmp_test;
drop table tmp_test;
let i = i + 1;
end while;
end procedure;
execute procedure temp_test(100000);
------------------------------
Gabor Fekete
------------------------------
#Informix