Return
-
Title
Error 42704 occurs when exporting data from a Global Temp Table into a text file -
Description
When exporting data from a Global Temp Table into a text file, the following error may occur: ERROR [42704] IBM [DB2 | AIX64] SQL 0204N] "Session.temp_table" is unidentify name SQL=42704.
-
Resolution
DB2 Global Temp Tables (GTT) are not persistent and cannot be shared with other sessions (i.e. Toad editor sessions). This use is not supported in Toad and will have unpredictable results as the GTT is controlled by the transaction (thread) that has created it and will be disposed when that thread ends.
When a transaction runs it can DECLARE global temp tables and then populate them at will and access them as regular tables for the duration of the transaction. This is normally done programmatically using dynamic SQL. DB2 will guarantee the data within the current thread only. This means only the thread (transaction) that DECLARES the GTT should be accessing it. If we run a thread that creates and populates GLOBAL1.GTT table and someone else launches a TOAD editor session and does a SELECT * FROM GLOBAL1.GTT, the results are unpredictable because the transaction that DECLARES and populates it controls the resources. Even if the Toad editor session were able to see data in GLOBAL1.GTT it doesn't know if we are still loading it or getting ready to DELETE it because our transaction is ending. That is why it is not persistent. It's not the type of resource that DB2 can guarantee data integrity across multiple threads. Toad uses multiple threads/connections all the time, so accessing GTT data is not advisable. We cannot guarantee the validity of GTT data being accessed by Toad using a secondary thread.We apologize for this inconvenience. Please let me know if you have further questions or I have answered why you are not able to export the data from these global temp tables.