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'); endit works. But
begin atomic create table test2(col1 integer); create table test3(col1 integer); enddoes 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
- GET DIAGNOSTICS Statement
- IF Statement
- ITERATE Statement
- LEAVE Statement
- SIGNAL Statement
- WHILE Statement
- Searched UPDATE
- Searched DELETE
- SET variable statement
For More Information
- Dozens more answers to tough DB2 questions from Craig Mullins are available.
- The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
- Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical DB2 questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.