How to use SET OPTION within a DB2 stored procedure

Question

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:

• Can I really not use WITH UR after my SELECT statements within a procedure?
• Why is my stored procedure failing to compile with the below error message?

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

Answer 1

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.