Compound statement in db2 command line processor

Question

I am trying to execute a compound statement of following type in DB2 command line processor

BEGIN ATOMIC
UPDATE schema.tablename set column1 = xyz where condition = somecondition;
UPDATE schema.tablename2 set column2 = abc where condition = somecondition
END

But I am receiving following error

BEGIN ATOMIC
UPDATE schema.tablename set column1 = xyz where condition = somecondition
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "where condition = somecondition". 
Expected tokens may include:  "<delim_semicolon>".  LINE 
NUMBER=2.  SQLSTATE=42601

UPDATE schema.tablename2 set column2 = abc where condition = somecondition END
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END" was found following "where condition = somecondition".  
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

SQL0104N  An unexpected token "END" was found following "where condition = somecondition".  Expected tokens may include:  "END-OF-STATEMENT                             ".

I am not sure if this is supported in db2 sql command line processor. The requirement is to execute three queries in an atomic fashion but not in a stored procedure. Please guide if there is any other alternative too.

Answer 1

You will need to define a non-default statement terminator character, otherwise the command line processor cannot distinguish semicolons within a compound statement and the one after the compound statement.

So, you would invoke it as db2 -td@, setting the statement terminator to "@", and your statement will look like:

BEGIN ATOMIC
 UPDATE schema.tablename set column1 = xyz where condition = somecondition;
 UPDATE schema.tablename2 set column2 = abc where condition = somecondition;
END@