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.
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