Search code examples
sqldb2db2-luw

IBM DB2 for LUW 11.5 - Running CALL SYSPROC.ADMIN_CMD in body of compound SQL block gives a "attempted to execute a statement that is not allowed" err

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?

Solution

  • 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.