sinistral.sourcery

Since v7.1 DB2 LUW (or UDB, if you prefer) has supported session-specific global temporary tables. Once declared, the table may be referenced until the session ends, at which point the rows are deleted and the description is dropped. (See the IBM Infocenter pages for details). The syntax for declaring these tables is, however, a little arcane, and I found using them in conjuction with cursor declarations somewhat tricky.

Specifically, if the cursor is opened over the temporary table, the cursor declaration must follow the table declaration. However, it seems that DB2′s parser expects only variable declarations to follow the table declaration. For example, the following code:

   connect to testdb@
   set current schema='testschema'@
   
   DROP PROCEDURE temp_proc@
   CREATE PROCEDURE temp_proc(
         IN input_var CHAR(1))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
      CALLED ON NULL INPUT
   BEGIN
   
      DECLARE SQLCODE INTEGER DEFAULT 0;
      DECLARE GLOBAL TEMPORARY TABLE t_y_or_n (y_or_n char(1))
         ON COMMIT PRESERVE ROWS NOT LOGGED;
   
      DECLARE local_var CHAR(1);
      DECLARE crsr CURSOR WITH RETURN TO CLIENT FOR
         SELECT y_or_n FROM session.t_y_or_n WHERE y_or_n=local_var;
      INSERT INTO session.t_y_or_n
         SELECT ibmreqd FROM sysibm.sysdummy1;
      SET local_var = input_var;
   
      OPEN crsr;
   
   END@
   
   CALL temp_proc('Y')@
   terminate@

yields the error:

   DB21034E The command was processed as an SQL statement because it
   was not a valid Command Line Processor command. During SQL
   processing it returned:
   SQL0104N An unexpected token "<variable declaration>" was
   found following "". Expected tokens may include: "<SQL statement>".
   LINE NUMBER=12.
   SQLSTATE=42601

The solution seems to be to put the table declaration and the the cursor declaration in different scope. This is achieved using labels, i.e.:

   connect to testdb@
   set current schema='testschema'@
   
   DROP PROCEDURE temp_proc@
   CREATE PROCEDURE temp_proc(
         IN input_var CHAR(1))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
      CALLED ON NULL INPUT
   P1 : BEGIN
      DECLARE SQLCODE INTEGER DEFAULT 0;
      DECLARE GLOBAL TEMPORARY TABLE t_y_or_n (y_or_n char(1))
         ON COMMIT PRESERVE ROWS NOT LOGGED;
      P2 : BEGIN
         DECLARE local_var CHAR(1);
         DECLARE crsr CURSOR WITH RETURN TO CLIENT FOR
            SELECT y_or_n FROM session.t_y_or_n WHERE y_or_n=local_var;
         INSERT INTO session.t_y_or_n
            SELECT ibmreqd FROM sysibm.sysdummy1;
         SET local_var = input_var;
         OPEN crsr;
      END P2;
   END P1@
   
   CALL temp_proc('Y')@
   terminate@

The above code can be run by copying it into a file and executing it with:

   db2 -td@ -v -f <script_name>

Thanks to nemali on the Dev Shed forums for his/her post on the subject.