IBM Support

SQL0204 returned for declared global temporary table.

Troubleshooting


Problem

When creating a stored procedure which calls a nested stored procedure to create a declared global temporary table (DGTT), SQL0204 (-204) may be returned for the DGTT.

Symptom


Creating the stored procedure DB2INST1.USE_DGTT returns SQL0204 for the DGTT SESSION.TEMP_TABLE.


CREATE PROCEDURE DB2INST1.CREATE_DGTT()  
DYNAMIC RESULT SETS 1  
NOT DETERMINISTIC  
LANGUAGE SQL  
BEGIN  
   
-- Create DGTT    
DECLARE GLOBAL TEMPORARY TABLE session.temp_table  
(  
        C1 INTEGER
)  
  ON COMMIT PRESERVE ROWS  
  WITH REPLACE  
  NOT LOGGED;  
END  
DB20000I  The SQL command completed successfully.  

   
CREATE PROCEDURE DB2INST1.USE_DGTT  
(  
    IN P_NUM  INTEGER  
)  
DYNAMIC RESULT SETS 1  
NOT DETERMINISTIC  
LANGUAGE SQL  
BEGIN
   
-- creates the DGTT 
CALL DB2INST1.CREATE_DGTT();  
   
INSERT INTO session.temp_table (C1) VALUES(P_NUM);
END
 
DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0204N  "SESSION.TEMP_TABLE" is an undefined name.  LINE NUMBER=13.  
SQLSTATE=42704

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP \u0026 UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
30 April 2025

UID

swg21681365

Page Feedback