Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737

    Unanswered: BEGIN ATOMIC limitations

    I tired to run the following statement, but it does not work inside a BEGIN ATOMIC. I assume this is a known limitation, but I could not find it documented:

    BEGIN ATOMIC
    SET CURRENT QUERY OPTIMIZATION 3;
    END


    The reason for the BEGIN ATOMIC is that I want to include other statements after it, but it does work even by itself inside the BEGIN ATOMIC, even though it works outside the BEGIN ATOMIC.
    Last edited by Marcus_A; 11-16-07 at 14:23.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    What exactly do you mean when you state that 'it does not work'? I coded a stored proc that executed a dynamically prepared SET CURRENT QUERY OPTIMIZATION statement, so I don't think there's any restriction. We're running UDB 8.2 on AIX.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you run it inside a BEGIN ATOMIC statement?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    This is the stored proc text more or less.

    Code:
    CREATE PROCEDURE sch1.sp_001 (     IN pQUERY_OPT         VARCHAR(15) 
                                    , OUT pSTART_TS          TIMESTAMP 
                                    , OUT pEND_TS            TIMESTAMP   )
    RESULT SETS 0
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN ATOMIC
      DECLARE vSQL_TEXT VARCHAR(1000);
      DECLARE vQUERY_OPT VARCHAR(255);
      DECLARE vPM1 TIMESTAMP;
      DECLARE vPM2 VARCHAR(15);
      SET vSQL_TEXT = '<update statement text with parameter markers>';
      SET CURRENT QUERY OPTIMIZATION 3;
      PREPARE UPDT_STMT FROM vSQL_TEXT;
      SET vPM1 = CURRENT TIMESTAMP;
      SET vPM2 = 'xyz';
      SET pSTART_TS = CURRENT_TIMESTAMP;
      EXECUTE UPDT_STMT USING vPM1,vPM2;
      SET pEND_TS = CURRENT_TIMESTAMP;
    END#
    The original was not utilizing BEGIN ATOMIC, so I created a new stored proc with BEGIN ATOMIC. The original dynamically prepared and executed the SET. I changed that to a static SET. The first time I created the stored proc, it failed because I had a COMMIT from the original version. I removed the COMMIT, and after that, I didn't have any problems creating or executing it.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The limitation of not having a COMMIT inside the BEGIN ATOMIC block is documented.

    I was trying to do this in a cli script, not a stored procedure.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    I wasn't sure how you were coding this. Based on the description of the Compound SQL (dynamic) Statement, you can only use a SET to set a variable.

    Code:
    SQL-routine-statement:
    
    |--+-CALL----------------------------------------------+--------|
       +-FOR-----------------------------------------------+
       +-+-----------------------------------+--fullselect-+
       | |       .-,-----------------------. |             |
       | |       V                         | |             |
       | '-WITH----common-table-expression-+-'             |
       +-GET DIAGNOSTICS-----------------------------------+
       +-IF------------------------------------------------+
       +-INSERT--------------------------------------------+
       +-ITERATE-------------------------------------------+
       +-LEAVE---------------------------------------------+
       +-MERGE---------------------------------------------+
       +-searched-delete-----------------------------------+
       +-searched-update-----------------------------------+
       +-SET Variable--------------------------------------+
       +-SIGNAL--------------------------------------------+
       '-WHILE---------------------------------------------'

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    The limitation of not having a COMMIT inside the BEGIN ATOMIC block is documented.
    p.s: Allowing COMMIT or ROLLBACK inside a BEGIN ATOMIC block would be contrary to the "atomic" part of such a compound statement. Thus, it is obvious that DB2 doesn't allow it.

    What exactly is the problem with "it does not work"? Did you get an error message? Setting the optimization level has only an effect on dynamic SQL statements.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    What exactly is the problem with "it does not work"? Did you get an error message? Setting the optimization level has only an effect on dynamic SQL statements.
    Even though my SQL statements were dynamic it does get an error message (not very clear).

    I have been advised that it would not have the intended effect anyway, since the SET CURRENT QUERY OPTIMIZATION would not apply to any SQL statements in the same BEGIN ATOMIC block (only to subsequent statements), even if it were supported.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2009
    Location
    Iceland
    Posts
    2

    hey sup?

    Just wanted to say you all have a great forum. Seems like a good place I can actually be a part of.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by JJAllenFit
    Just wanted to say you all have a great forum. Seems like a good place I can actually be a part of.
    Fully agreed!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    jsharon1248 wrote
    Based on the description of the Compound SQL (dynamic) Statement, you can only use a SET to set a variable.
    "SET CURRENT QUERY OPTIMIZATION" is a statement.
    It is different from "SET Variable" statement.
    You can use "SET Variable" statement in "Compound SQL (Dynamic)" statement.
    But, you cannot use "SET CURRENT QUERY OPTIMIZATION" statement in "Compound SQL (Dynamic)" statement.

    You can use "SET CURRENT QUERY OPTIMIZATION" statement in "Compound SQL (Procedure)".

    See "DB2 Version 9 for Linux, UNIX, and Window SQL Reference Volume 2" for SQL statements enclosed by double quotations.
    Last edited by tonkuma; 06-06-09 at 22:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •