DECLARE GLOBAL TEMPORARY TABLE

[DSbox61]

Hi ppl,

I have job which runs on production box. I am creating a temporary table in DB2 for that particular session with the following query:
DECLARE GLOBAL TEMPORARY TABLE session.XTEMPPRICING1
(
PRODUCTID VARCHAR(16) not null,
NEWPRICETYPE CHAR(1) not null,
NEWPRICE DECIMAL(20,5),
PRECEDENCE INTEGER
)
NOT LOGGED WITH REPLACE IN SYSTOOLSTMPSPACE ON COMMIT PRESERVE ROWS;

This query will create the temporary table only for that particular session and the table will not be present once the connection is lost. I'm not able to use that table on output link if i'm running this query on input link. THe error i find is that the table is an undefined name.

Any suggestions......

Thanks...............

[kumar_s]

Yes, Global table will persist only till that session alive. Its a temporary table.
You ll need to go for normal persistent table creation.

[DSbox61]

So, does it mean that there will be are two different sessions used for input link and output link to the same DB2 stage.

[kcbland]

Yes, each link has its own connection. Consider using the before/after-SQL tabs if possible. If you're creating a temporary table, then selecting from it, modifying results, and then writing back, you're going to have other problems. Your table disappears once the link ends.

Maybe you should describe what you wish to do logically so that we can help you out with the physical side.