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



Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.