DB2 will not INSERT into Created Temp Table that I created

By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table.

You should either create the table using the ON COMMIT PRESERVE ROWS option, or disable autocommit while running your commands and issue an explicit COMMIT when you are done. Which option you choose depends on your business logic.

Screenshot of successful execution


Please try using following steps

[db2inst9@jaimatadi ~]$ db2 "DECLARE GLOBAL TEMPORARY TABLE DEP6 LIKE V_TOTAL_SALES
> ON COMMIT PRESERVE ROWS
> WITH REPLACE
> NOT LOGGED
> IN USER_TEMP_TBSP"
DB20000I The SQL command completed successfully.
[db2inst9@jaimatadi ~]$ db2 "insert into SESSION.DEP6 select * from V_TOTAL_SALES""
> "
DB20000I The SQL command completed successfully.
[db2inst9@jaimatadi ~]$ db2 "select * from SESSION.DEP6"

COUNTRY              CITY                 SALES_AMT
-------------------- -------------------- ---------------------------------
UK                   London               2100.00
UK                   Manchester           1290.00
USA                  Alaska               2130.00
USA                  California           1420.00
USA                  Los Angeles          1110.00
USA                  New York             1420.00

Thanks, Prashant

Tags:

Db2