DB2 Temp Tables: Not storing or not retrieving information

Question

I'm an MSSQL guy, but I'm working on a DB2 query that needs to create a temp table, insert into it, and do stuff with it. As a much-shortened test, I'm using the following query, which is providing the same result..

declare global temporary table tt_testingSyntax (id int);
insert into session.tt_testingSyntax (id) values (1);
insert into session.tt_testingSyntax (id) values (2);
insert into session.tt_testingSyntax (id) values (3);
insert into session.tt_testingSyntax (id) values (4);
select * from session.tt_testingSyntax;

Zero rows are returned. Why would that be? I've created the tablespace and verified the table is in scope throughout the query.

Answer 1

Try:

declare global temporary table tt_testingSyntax (id int) 
ON COMMIT PRESERVE ROWS NOT LOGGED;

insert into session.tt_testingSyntax (id) values (1);
insert into session.tt_testingSyntax (id) values (2);
insert into session.tt_testingSyntax (id) values (3);
insert into session.tt_testingSyntax (id) values (4);
select * from session.tt_testingSyntax;

There are two options...ON COMMIT DELETE ROWS (the default) or ON COMMIT PRESERVE ROWS.

Answer 2

I ended up unknowingly having access to create my own tables (i.e. for user X, I could create X.temp1). Since this query need only be run once, this works fine. Thanks.