title: How to use SET OPTION within a DB2 stored procedure
body: <p>I read (and tried) that I cannot use <strong>WITH UR</strong> in DB2 stored procedures. I am told that I can use <strong>SET OPTION</strong> 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:</p>
  <ul>
  <li>Can I really not use WITH UR after my SELECT statements within a procedure?</li>
  <li>Why is my stored procedure failing to compile with the below error
  message?</li>
  </ul>
  <p>Here is a simplified version of my code:</p>
  <pre><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 &quot;Col Name&quot;
               ) WITH DATA
          
  
          BEGIN
               DECLARE  exitCursor CURSOR WITH RETURN FOR
               SELECT *
               FROM SESSION.TEMP_TABLE;
               OPEN exitCursor;
          END;
  
          END 
          @
  
  Error Message: 
  </code></pre>
  <blockquote>
  <p>SQL0104N  An unexpected token &quot;SET OPTION COMMIT=*CHG&quot; was found
  following &quot;    LANGUAGE SQL</p>
  </blockquote>
  <p>Here is code/error when I use <strong>WITH UR</strong></p>
  <pre><code>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 &quot;Trade ID&quot; FROM XYZ WITH UR
          ) WITH DATA;
  
      BEGIN
          DECLARE  exitCursor CURSOR WITH RETURN FOR
              SELECT *
              FROM SESSION.TEMP_TABLE;
          OPEN exitCursor;
      END;
  
  END
  @
  </code></pre>
  <p>line 9 is where the DECLARE GLOBAL TEMPORARY ... is</p>
  <blockquote>
  <p>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: &quot;WITH ISOLATION USE  AND KEEP&quot;.  LINE NUMBER=9.  SQLSTATE=42601</p>
  </blockquote>