CODEWITHSUNDEEP
isolation-leveldb2-luw
Rade
Rade

Reputation: 43

Getting error writing an anonymous block in TOAD DB2

I am new to DB2. I want to execute an anonymous black in toad. 

BEGIN ATOMIC
DECLARE TEMP_SCHEMA VARCHAR(12) ;
SET TEMP_SCHEMA = 'SCHEMA1';
SELECT * FROM TEMP_SCHEMA.TABLE_NAME
WHERE 1=1 
WITH UR;
END;

I am getting following error: 20159: [IBM][DB2/AIX64] SQL20159W The isolation clause is ignored because of the statement context.

Can you please help.

Upvotes: 0

Views: 735

Answers (1)

user7334210
user7334210

Reputation: 21

According to the documentation at https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak04940_.htm?view=embed , "If ATOMIC is specified, only one instance of a message flow (that is, one thread) is allowed to execute the statements of a specific BEGIN ATOMIC... END statement (identified by its schema and label), at any one time. If no label is present, the behavior is as if a zero length label had been specified. The BEGIN ATOMIC construct is useful when a number of changes need to be made to a shared variable and it is important to prevent other instances seeing the intermediate states of the data."

Using ATOMIC in a stored procedure means that your code will execute as a singleton, providing maximal isolation. That would be in direct conflict with your "WITH UR" isolation option. Even though you are using the ATOMIC keyword in a script, not in a stored procedure, DB2 still treats it as a single thread, so it will complain if you include query hints that attempt to lower the isolation level.

After removing the ATOMIC keyword, you are getting the token error because your SELECT * FROM TEMP_SCHEMA.TABLE_NAME WHERE 1=1 statement is attempting to return a result set to Toad from inside a BEGIN block. Unfortunately, this is not possible in DB2. As soon as you have any procedural code, which forces you to utilize a BEGIN block, DB2 steadfastly refuses to return data to the client. The only way that I found to return results from inside a BEGIN block is to place the BEGIN block in a stored procedure, and then use a CURSOR to return the result set to Toad, e.g.,

BEGIN

DECLARE C1 CURSOR WITH RETURN WITH HOLD FOR SELECT * FROM .EMPLOYEE; OPEN C1;

END;

Note that you must enclose the CURSOR code in an inner BEGIN block, which DB2 requires when sending a result set back to the client WITH HOLD.

If you want to return the value of a variable to Toad from your prototype stored procedure, you can use this approach:

BEGIN

DECLARE C1 CURSOR WITH RETURN WITH HOLD FOR 
SELECT * FROM TABLE(SELECT * FROM (VALUES(<variable goes here>)) 
AS TEMP(<descriptive name for the variable goes here>)) AS TEMP1;
OPEN C1;

END;

To summarize, in order to use the Anonymous Block as a prototyping vehicle in a development tool such as Toad, you have to wrap it in a stored procedure if you want to return any results, and you must use a CURSOR embedded in an inner BEGIN block to do so. Its unfortunate that DB2 is so much more cumbersome than MS SQL Server in this regard.

Upvotes: 1

Related Questions