BEGIN ATOMIC limitations

Marcus_A · #1 (**permalink**) 11-16-07, 12:51

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.

jsharon1248 · #2 (**permalink**) 11-16-07, 14:27

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.

Marcus_A · #3 (**permalink**) 11-16-07, 14:49

Did you run it inside a BEGIN ATOMIC statement?

jsharon1248 · #4 (**permalink**) 11-16-07, 15:37

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.

Marcus_A · #5 (**permalink**) 11-16-07, 16:01

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.

jsharon1248 · #6 (**permalink**) 11-16-07, 16:21

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---------------------------------------------'

stolze · #7 (**permalink**) 11-17-07, 05:46

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.

Marcus_A · #8 (**permalink**) 11-17-07, 08:59

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.

JJAllenFit · #9 (**permalink**) 06-06-09, 11:07

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

Peter.Vanroose · #10 (**permalink**) 06-06-09, 13:24

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!

tonkuma · #11 (**permalink**) 06-06-09, 14:12

jsharon1248 wrote

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech