What SQL can be used in a compound SQL statement?

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');
end
it works. But
begin atomic
create table test2(col1 integer);
create table test3(col1 integer);
end
does not... My statement terminator is '@'.

    Requires Free Membership to View

    Login

    When you register, you’ll also receive targeted alerts from my team of editorial writers and independent industry experts with the latest news, tips, and advice to help you do your job more efficiently and effectively. Our goal is to keep you informed on the hottest topics and biggest challenges faced by IT professionals today working with data center technologies.

    Margie Semilof, Editorial Director

    By submitting your registration information to SearchDataCenter.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchDataCenter.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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
  • GET DIAGNOSTICS Statement
  • IF Statement
  • ITERATE Statement
  • LEAVE Statement
  • SIGNAL Statement
  • WHILE Statement
The SQL statements that can be issued are:
  • fullselect
  • Searched UPDATE
  • Searched DELETE
  • INSERT
  • SET variable statement
Other SQL statements are not allowed in compound SQL.

For More Information


This was first published in November 2005

Back to top