Insert into temp tables not working

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.

re: Insert into temp tables not working

Ross wrote:[color=blue]
> 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
> --------------------------
>[/color]

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.

re: Insert into temp tables not working

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

re: Insert into temp tables not working

Thanks


on commit preserve rows did the trick

shenanwei@gmail.com wrote:[color=blue]
> 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[/color]

re: Insert into temp tables not working

Ross wrote:[color=blue]
> INSERT INTO SESSION.temp_date values(SELECT current timestamp FROM
> sysibm.sysdummy1);[/color]
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