SQL Blocks (a.k.a. Anonymous blocks in Oracle) in DB2 which are not inside any stored procedure or trigger were the bane of my entire team for quite a while. H requested me to take care of the same since I am the only one qualified enough (being DB2 700 certified and stuff). So I set about doing this yesterday. Finally managed to get it to work. Strong recommendation if you are interested in this stuff or work on it everyday, join this Google group.

Here’s the code that works in Command Center (obviously the logic is useless, this is just to explain the syntax to you):

BEGIN ATOMIC
declare a integer default 5;
declare b integer default 5;
delete from test.table1;
Set a = (select count(*) from test.table2);
Set b = (select count(*) from test.table3);
if a=b then
insert into test.table1(’A');
else
insert into test.table1(’B');
end if;
END
@

Need an explanation? Here goes…

The first statement (BEGIN ATOMIC) is like any other Begin statement you would encounter in a Oracle PL/SQL Block. It tells the compiler that a block is beginning. But there is something additional. The keyword ATOMIC, which basically means that the **entire** statement contained inside the BEGIN…END clauses is either executed in full or not at all. So in case there is an error while executing one of the SQL queries (say the insert query), the whole of the statement is rolled back.

The next 2 lines of code obviously declare 2 variables a and b each assigned a default value of 5. You could also leave out the default value.

Next up the delete statement. Note that DB2 does NOT have a TRUNCATE statement. So you either have to use a DELETE FROM without giving a where condition, or try the REPLACE INTO command with a blank file. The difference? Well, a DELETE FROM will log all the rows being deleted, thereby increasing the log size and also taking more time. This is not the case with the TRUNCATE statement on other DBMSs. In DB2, using the LOAD COMMAND with the REPLACE INTO statement accomplishes this. Much much more on this here.

The Set statement is assigning values to the declared variables. Please note, SELECT INTO or equating the variables to functional expressions (eg. a=count(*)) does not work and neither do you need to prefix the variable names with a colon(:) or an at(@). Just use the variable names as they are.

The IF conditional. Hmm…Note the syntax. IF..THEN..(ELSEIF..THEN..)ELSE..END IF;.
And the semi-colon must come after the END IF to indicate the end of the IF statement.

Whats the @ doing right at the end? Hmm…You havent been reading my blog regularly…have you? Read this. You will omit the @ symbol in case you are using this code elsewhere in DB2.