global temporary table help

[saxgeek]

In the following stored procedure I have the table t_lookup_changes_temp defined as a perm table and the procedure works. When I try to define the table as global temporary, the procedure runs but does nothing. Any ideas? I'd like to use global temp because I may have several procedures hitting this table simultaneously.

Replace PROCEDURE ncr_temp.spCpaisUpdate( )
BEGIN

-- **********************************************************
-- FILENAME : spCpaisUpdate
-- PURPOSE : UPdates cpaisjoins with new lookup table values
-- **********************************************************

Declare codecolname varchar(50);
Declare codecolval varchar(10);
Declare newnamevalue varchar(250);
Declare dynsql1 varchar(255);
Declare acount integer;

-- get rows that apply only to t_cpaisjoins table

insert into ncr_temp.t_lookup_changes_temp
sel a.codecolname, a.codecolvalue, newnamevalue, effectivedate,
'update ncr_temp.t_cpaisjoins set ' || trim(a.codecolname) || '_name = ' || '''' || trim(newnamevalue) || '''' || ' where ' || trim(a.codecolname) || ' = ' || trim(a.codecolvalue) || ';'
from ncr_temp.t_lookup_changes a
,(sel codecolname, codecolvalue, max(effectivedate) as lastupddate
from ncr_temp.t_lookup_changes
group by 1,2) b
where joinstablename = 't_cpaisjoins'
and a.codecolname = b.codecolname
and a.codecolvalue = b.codecolvalue
and a.effectivedate = b.lastupddate;



insert into ncr_temp.t_logtable
values ('insert1', :activity_count,null);
-- delete rows from t_lookup_changes that have been applied to t_cpaisjoins

--delete from ncr_temp.t_lookup_changes
--where joinstablename = 't_cpaisjoins';

insert into ncr_temp.t_logtable
values ('delete1', :activity_count,null);

--upd1:

for UpdCpais as c_cpais cursor for
Select dynupdate from ncr_temp.t_lookup_changes_temp



DO
insert into ncr_temp.t_logtable
values ('update1', :activity_count, :UpdCpais.dynupdate);

CALL dbc.sysexecsql UpdCpais.dynupdate);
End For;
END;

 

[dnoeth]

"When I try to define the table as global temporary, the procedure runs but does nothing."

Sounds familiar ;-)

Do a SHOW TABLE and look at the ON COMMIT option, it's
ON COMMIT DELETE ROWS.
Your SP is probably run in a Teradata mode session, so the default is auto-commit. When the Insert commits, the table is emptied ;-)
So just replace it with a
ON COMMIT PRESERVE ROWS.

Dieter

 

[saxgeek]

You are right again - thanks.