CODEWITHSUNDEEP
sqlstored-proceduresdb2db2-luwisolation-level
Achilles
Achilles

Reputation: 741

How to use SET OPTION within a DB2 stored procedure

I read (and tried) that I cannot use WITH UR in DB2 stored procedures. I am told that I can use SET OPTION to achieve the same. However, when I implement it in my stored procedure, it fails to compile (I moved around its location same error). My questions are:

Here is a simplified version of my code:

    CREATE OR REPLACE PROCEDURE MySchema.MySampleProcedure()
        DYNAMIC RESULT SETS 1 
        LANGUAGE SQL 
        SET OPTION COMMIT=*CHG 
        
        BEGIN
             DECLARE GLOBAL TEMPORARY TABLE TEMP_TABLE AS (
             SELECT 'testValue' as "Col Name"
             ) WITH DATA
        

        BEGIN
             DECLARE  exitCursor CURSOR WITH RETURN FOR
             SELECT *
             FROM SESSION.TEMP_TABLE;
             OPEN exitCursor;
        END;

        END 
        @

Error Message:

SQL0104N An unexpected token "SET OPTION COMMIT=*CHG" was found following " LANGUAGE SQL

Here is code/error when I use WITH UR

CREATE OR REPLACE PROCEDURE MySchema.MySampleProcedure()

    LANGUAGE SQL 
    DYNAMIC RESULT SETS 1 

--#SET TERMINATOR @
BEGIN

    DECLARE GLOBAL TEMPORARY TABLE TEMP_TABLE AS (
        SELECT UTI AS "Trade ID" FROM XYZ WITH UR
        ) WITH DATA;

    BEGIN
        DECLARE  exitCursor CURSOR WITH RETURN FOR
            SELECT *
            FROM SESSION.TEMP_TABLE;
        OPEN exitCursor;
    END;

END
@

line 9 is where the DECLARE GLOBAL TEMPORARY ... is

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0109N The statement or command was not processed because the following clause is not supported in the context where it is used: "WITH ISOLATION USE AND KEEP". LINE NUMBER=9. SQLSTATE=42601

Upvotes: 0

Views: 1354

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12324

Specifying the isolation level:

For static SQL:

  • If an isolation-clause is specified in the statement, the value of that clause is used.
  • If an isolation-clause is not specified in the statement, the isolation level that was specified for the package when the package was bound to the database is used.

You need to bind the routine package with UR, since your DECLARE GTT statement is static. Before CREATE OR REPLACE use the following in the same session:

CALL SET_ROUTINE_OPTS('ISOLATION UR')

P.S.: If you want to run your routine not only 1 time in the same session without an error, use additional WITH REPLACE option of DECLARE.

Upvotes: 1

mao
mao

Reputation: 12267

If your Db2 server runs on Linux/Unix/Windows (Db2-LUW), then there is no such statement as SET OPTION COMMIT=*CHG , and so Db2 will throw an exception for that invalid syntax.

It is important to only use the matching Db2 Knowledge Centre for your Db2 platform and your Db2-version. Don't use Db2-Z/OS documentation for Db2-LUW development. The syntax and functionalities differ per platform and per version.

A Db2-LUW SQL PL procedure can use with ur in its internal queries, and if you are getting an error then something else is wrong. You have to use with ur in the correct syntax however, i.e in a statement that supports this clause. For your example you get the error because the clause does not appear to be valid in the depicted context. You can achieve the desired result in other ways, one of them being to populate the table in a separate statement from the declaration (e.g insert into session.temp_table("Trade ID") select uti from xyz with ur; ) and other ways are also possible.

One reason to use the online Db2 Knowledge Cenbtre documentation is that it includes sample programs, including sample SQL PL procedures, which are also available in source code form in the sample directory of your DB2-LUW server, in addition to being available on github. It is wise to study these, and get them working for you.

Upvotes: 1

Related Questions