Problem solve Get help with specific problems with your technologies, process and projects.

What SQL can be used in a compound SQL statement?

My question is very simple. I guess I am missing something. When I use the command center and type:

begin atomic
insert into test1 values('1');
insert into test1 values('2');
insert into test1 values('3');
it works. But
begin atomic
create table test2(col1 integer);
create table test3(col1 integer);
does not... My statement terminator is '@'.

Compound SQL - that is SQL between a BEGIN ATOMIC and an END - causes either all of the statements to successfully execute or none of the statements to successfully execute. But DB2 limits the type of SQL that can be placed in a compound SQL statement. As you have found, DDL is not permitted.

The following list of SQL-control-statements can be used within the dynamic compound statement:

  • FOR Statement
  • IF Statement
  • ITERATE Statement
  • LEAVE Statement
  • SIGNAL Statement
  • WHILE Statement
The SQL statements that can be issued are:
  • fullselect
  • Searched UPDATE
  • Searched DELETE
  • SET variable statement
Other SQL statements are not allowed in compound SQL.

For More Information

This was last published in November 2005

Dig Deeper on IBM system z and mainframe systems

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.