I'm new to DB2. I'm running into an issue where I'm trying to write a compound statement that can REORG all tables in a database that have a REORG_PENDING='Y' state.
Running CALL SYSPROC.ADMIN_CMD('REORG TABLE TABLENAME');
by itself works fine.
However when I run it in a compound SQL block, like so..
BEGIN ATOMIC
CALL SYSPROC.ADMIN_CMD('REORG TABLE TABLENAME');
END@
I get the following error:
Routine "SYSPROC.ADMIN_CMD" (specific name "ADMIN_CMD") attempted to execute a statement that is not allowed. SQLCODE=-751, SQLSTATE= , DRIVER=4.33.31
My understanding is that if I wanted to use a FOR
loop to run SYSPROC.ADMIN_CMD
for every table with a positive reorg pending status, the FOR
must be embedded in a SQL procedure or a Compound SQL statement. However running SYSPROC.ADMIN_CMD
within the compound SQL statement always gets me this error. Is there a reason this statement works outside of the block vs inside of the block?
The answer is use a compiled block i.e BEGIN
, instead of a BEGIN ATOMIC
block (also known as inlined block). The BEGIN ATOMIC
identifies an inlined-block, while BEGIN
on its own identifies the start of a compiled block.
Db2-LUW has two kinds of compound-SQL, "compound-SQL compiled" and "compound-SQL inlined". Each has different intentions, different capabilities, different restrictions. Compound-SQL is also possible in embedded-SQL programs (that need a precompiler) but that is a separate topic.
After compilation, the compound-SQL-compiled block will have its executable form stored as a discrete package
in the database-catalog (it is this package that is the runnable code). Compiled blocks have much more power/scope-of-functionality/syntax than inlined-blocks.
By contrast, a compound-SQL-inlined block will not have a discrete executable package because instead it is incorporated into the body of a different SQL statement at run time (i.e into an INSERT, UPDATE, DELETE, MERGE ). Separately the inlined block will execute atomically which is one reason there are many documented restrictions on what is possible with inlined compound-SQL.