Trying to use temporary table in IBM DB2 and facing issues

Question

I am getting the following error while creating a stored procedure for testing purpose:

SQL Error [42601]: An unexpected token "DECLARE GLOBAL TEMPORARY TABLE SESSION" was found following "RSOR WITH RETURN FOR". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29

Code:

CREATE OR REPLACE PROCEDURE Test ( IN GE_OutPutType SMALLINT)
----------------------------------------------------------------------------------------------------
 DYNAMIC RESULT SETS 1 LANGUAGE SQL
BEGIN
DECLARE C CURSOR WITH RETURN FOR DECLARE GLOBAL TEMPORARY TABLE
    SESSION.TEMP (DATE CHAR(10) NOT NULL,
    SALARY DECIMAL(9,
    2) ,
    COMM DECIMAL(9,
    2));

INSERT
    INTO
        SESSION.TEMP (DATE,
        SALARY,
        COMM) SELECT
            VARCHAR_FORMAT(CURRENT_DATE,
            'MM/DD/YYYY'),
            10.2,
            11.5
        FROM
            sysibm.sysdummy1 
            
IF GE_OutPutType = 1
        BEGIN
            SELECT
                *
            FROM
                TEMP
            ELSEIF GE_OutPutType = 2 SELECT
                'HEADER' CONCAT SPACE(1979) CONCAT 'H'
            FROM
                sysibm.sysdummy1
            END OPEN C;
END

mao · Accepted Answer · 2020-10-08T14:40:32.917

1

Your syntax is not valid.

You must declare your temporary table independently of your cursor.

You cannot combine these in a single statement.

Use dynamic-SQL features to achieve what you need.

Use instead the format:

Declare c1 cursor with return to caller for Statement1

and

set v_cursor_text = 'select ... from session.temp ; `

then use

prepare Statement1 from v_cursor_text;

and before you exit the stored procedure you need to leave the cursor opened:

open c1;

Do study the Db2 online documentation to learn more about these features.

Here is a small fragment of your procedure showing what I mean:

CREATE OR REPLACE PROCEDURE mytest ( IN GE_OutPutType SMALLINT)
DYNAMIC RESULT SETS 1 
LANGUAGE SQL
specific mytest
BEGIN
    DECLARE v_cursor_text varchar(1024);
    DECLARE C1 CURSOR WITH RETURN FOR Statement1;

    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP (
        DATE CHAR(10) NOT NULL,
        SALARY DECIMAL(9,
        2) ,
        COMM DECIMAL(9,
        2))
     with replace on commit preserve rows not logged;

    INSERT INTO SESSION.TEMP (DATE, SALARY, COMM) 
      SELECT VARCHAR_FORMAT(CURRENT_DATE, 'MM/DD/YYYY'),
            10.2,
            11.5
       FROM sysibm.sysdummy1 ;

     if GE_OutPutType = 1
     then 
        set v_cursor_text = 'select * from session.temp';
     end if;
     if GE_OutPutType = 2
     then
        set v_cursor_text = 'select ''header'' concat space(1979) concat ''H'' from sysibm.sysdummy1';
     end if;

     prepare Statement1 from v_cursor_text;
     open c1;
    
            
END@

edited Oct 08 '20 at 14:40

answered Oct 08 '20 at 13:58

mao

11,321
2
13
29

Hi @mao Is it possible for you to provide a sample code, so that I can understand better, I am new to db2. – Robin Oct 08 '20 at 14:12
Thank you @mao but still I have one question. In the above code I dont see how to incorporate the if codition which tells which select statement to execute based on the input parameter... Does it mean I have to open 2 cursor? – Robin Oct 08 '20 at 14:27
@Robin . If you have 2 different queries (two different select statements), but only wish to execute exactly one of them, then just assign the relevant query to the `v_cursor_text` variable, and run the prepare and open as above. In other words, you can conditionally (and dynamically) assign the query-text to the statement and use a single cursor. You can also if you prefer have two separate cursors, with 2 separate statements , only one of which will be prepared and opened. – mao Oct 08 '20 at 14:31
When you say conditionally I can assign the query test using a single cursor, do you mean something like this: set v_cursor_text = if GE_OutPutType =1 then 'select * from session.temp' elseif GE_OutPutType =2 then 'Select * from session.temp and other conditions' end – Robin Oct 08 '20 at 14:34
Thank you very much @mao – Robin Oct 08 '20 at 14:52
I was able to work on the code and have something like this for first if conditon: if GE_OutPutType = 1 then set v_cursor_text = 'select * from ((select col1,1 as rnk from session.temp) union all (select col1,2 as rnk from session.temp)) overall order by overall.rnk asc'; when I remove the order by condition it works but when I put the order by I get the following error: SQL Error [54048]: A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=4.21.29 – Robin Oct 09 '20 at 01:38

Trying to use temporary table in IBM DB2 and facing issues

1 Answers1