Isidre Pons Roca
2013-04-17 06:45:28 UTC
Hi all,
We have a Stored Procedure that uses temporary tables.
A process calling this procedure hundreds of thousands every day.
Each call must clean the contents of the temporary table.
To improve performance we have moved to use delete to use the truncate, to clean temporary tables.
Once the procedure done this move only works right the first time is called, in the same session (same process).
The error at second call is -710 (Sqlerror), -101 (Isamerror) to any action over the temp table, the truncate works wit no error.
We do not know what is wrong
We would appreciate any help, comments.
Environment:
Informix Dynamic Server 11.50.FC4
Sun solaris 10
Sparc computer.
Exemple of procedure:
create procedure test_truncate()
returning smallint, varchar(40);
define p_v_loop smallint;
define p_v_column1 smallint;
define p_v_column2 varchar(40);
set debug file to "/tmp/test_truncate.log";
trace on;
begin
on exception
create temp table table1(
column1 smallint,
column2 varchar(40,0)
) with no log;
create unique index i1_table1 on table1
(column1);
end exception with resume;
-- delete from table1 where 1 = 1;
truncate table table1;
end;
for p_v_loop in (1 to 10 step 1)
let p_v_column1 = p_v_loop;
let p_v_column2 = "Column 2 of row "||p_v_loop;
insert into table1 values (p_v_column1, p_v_column2);
end for;
foreach
select * into p_v_column1, p_v_column2
from table1
order by column1
return p_v_column1, p_v_column2 with resume;
end foreach;
trace off;
end procedure;
TIA,
Isidre
.
We have a Stored Procedure that uses temporary tables.
A process calling this procedure hundreds of thousands every day.
Each call must clean the contents of the temporary table.
To improve performance we have moved to use delete to use the truncate, to clean temporary tables.
Once the procedure done this move only works right the first time is called, in the same session (same process).
The error at second call is -710 (Sqlerror), -101 (Isamerror) to any action over the temp table, the truncate works wit no error.
We do not know what is wrong
We would appreciate any help, comments.
Environment:
Informix Dynamic Server 11.50.FC4
Sun solaris 10
Sparc computer.
Exemple of procedure:
create procedure test_truncate()
returning smallint, varchar(40);
define p_v_loop smallint;
define p_v_column1 smallint;
define p_v_column2 varchar(40);
set debug file to "/tmp/test_truncate.log";
trace on;
begin
on exception
create temp table table1(
column1 smallint,
column2 varchar(40,0)
) with no log;
create unique index i1_table1 on table1
(column1);
end exception with resume;
-- delete from table1 where 1 = 1;
truncate table table1;
end;
for p_v_loop in (1 to 10 step 1)
let p_v_column1 = p_v_loop;
let p_v_column2 = "Column 2 of row "||p_v_loop;
insert into table1 values (p_v_column1, p_v_column2);
end for;
foreach
select * into p_v_column1, p_v_column2
from table1
order by column1
return p_v_column1, p_v_column2 with resume;
end foreach;
trace off;
end procedure;
TIA,
Isidre
.