Following code snippet is used to create GLOBAL TEMPORARY TABLE, INSERT into the created temporary table, SELECT the same for the output.

db2 "declare global temporary table temp1(col1 integer,col2 varchar(30)) with replace"
db2 "insert into temp1 values(1,a)"
db2 "select * from session.temp2"

COL1 COL2
----------- ------------------------------

0 record(s) selected.

In the above SELECT statement output it shows "0 record(s) selected", after inserting a record previously.

What is the problem? Can any one resolve the same?.

Thanks,
Kamesh.

the corrected statements are:


db2 "declare global temporary table temp1(col1 integer,col2 varchar(30)) with replace"

db2 "insert into session.temp1 values(1,'a')"
db2 "select * from session.temp1"

Also on your DECLARE GLOBAL TEMPORARY TABLE statement, add the clause "ON COMMIT PRESERVE ROWS". Otherwise all the rows will be deleted when a commit/autocommit occurs.

Andy

Hi BKamesh:

I have the same problem...did you solve it?
how?.

Thanks in advance...

Try auto commit off, when you running 'declare/insert/select sql'.

One way to this would be to execute query using file input on command line and use "db2 -e- -svtf input_file_nm.sql" command