Insert into temp tables not working

[Ross]

Hi,
I want to run this query. The query runs fine but it always inserts
blanks into the temp database. Any suggestions would be appreciated.
DECLARE GLOBAL TEMPORARY TABLE temp_date (v_date timestamp) in VDATE;

INSERT INTO SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1);

SELECT * FROM SESSION.temp_date;

DROP TABLE SESSION.temp_date;
------------------------------------------------------------------------------------------------------------
DECLARE GLOBAL TEMPORARY TABLE temp_date (v_date timestamp) in VDATE
DB20000I The SQL command completed successfully.

insert into SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1)
DB20000I The SQL command completed successfully.

SELECT * FROM SESSION.temp_date

V_DATE
--------------------------

0 record(s) selected.
DROP TABLE SESSION.temp_date
DB20000I The SQL command completed successfully.

[Ian]

Ross wrote:

Hi,
I want to run this query. The query runs fine but it always inserts
blanks into the temp database. Any suggestions would be appreciated.
DECLARE GLOBAL TEMPORARY TABLE temp_date (v_date timestamp) in VDATE;

INSERT INTO SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1);

SELECT * FROM SESSION.temp_date;

DROP TABLE SESSION.temp_date;
------------------------------------------------------------------------------------------------------------
DECLARE GLOBAL TEMPORARY TABLE temp_date (v_date timestamp) in VDATE
DB20000I The SQL command completed successfully.

insert into SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1)
DB20000I The SQL command completed successfully.

SELECT * FROM SESSION.temp_date

V_DATE
--------------------------

By default a global temporary table drops all rows as soon as the
transaction ends. If you are not turning off auto-commit (it is
on by default), then your statements above are 4 distinct transactions.

Try:

declare global temporary table temp_date (v_date timestamp) in vdate
on commit preserve rows;
Or, disable auto-commit and issue commit explicitly.

[shenanwei]

temp table has to be in a session.
begin transaction
DECLARE GLOBAL TEMPORARY TABLE temp_date (v_date timestamp) in VDATE;

INSERT INTO SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1);

SELECT * FROM SESSION.temp_date;

DROP TABLE SESSION.temp_date;
end transaction

[Ross]

Thanks
on commit preserve rows did the trick

sh*******@gmail.com wrote:

temp table has to be in a session.
begin transaction
DECLARE GLOBAL TEMPORARY TABLE temp_date (v_date timestamp) in VDATE;

INSERT INTO SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1);

SELECT * FROM SESSION.temp_date;

DROP TABLE SESSION.temp_date;
end transaction

[Serge Rielau]

Ross wrote:

INSERT INTO SESSION.temp_date values(SELECT current timestamp FROM
sysibm.sysdummy1);

Anything wrong with using values as the gods of SQL intended to?
INSERT INTO SESSION.temp_date values (current timestamp)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab