Db2 – Creating a dynamic db2 reorg script with in-built delay for concurrency

cursorsdb2db2-luwdelayed-execution

So I'm new to db2, and some of the syntax and other execution restrictions give me trouble. My DB is using DB2 v11.1.2.2. I've created a reorg script which calls the REORGCHK_TB_STATS and REORGCHK_IX_STATS procedures, and then uses the results to create the recommended reorg clauses per IBM's instructions. This works fine, the trouble is actually running them.

Right now the script fetches all the necessary reorgs and produces the following commands, which are stored to a table for processing:

'CALL SYSPROC.ADMIN_CMD(''REORG TABLE '||TABLE_SCHEMA||'.'||TABLE_NAME||' INPLACE ALLOW WRITE ACCESS NOTRUNCATE TABLE'')'
'CALL SYSPROC.ADMIN_CMD(''REORG INDEXES ALL FOR TABLE '||I.TABLE_SCHEMA||'.'||I.TABLE_NAME||' ALLOW WRITE ACCESS'')'
-- etc...

The problem is with the final part of the script. The results above are executed in a loop where we check for too many concurrently running reorgs, to avoid bottlenecking the server. But while I tested the execution of dynamic SQL and it works, I can't get the procedure calls, nor the delay itself to work. And google hasn't been helpful. Please advise, what is wrong with the following script:

BEGIN

    DECLARE V_MYSQL VARCHAR(2000);  
    
    FOR T AS CUR0 CURSOR FOR 
        (SELECT REORG_CLAUSE
        FROM MYSCHEMA.TEMP_REORG_TAB
        ORDER BY RN ASC)
    DO   
        -- Execute the reorg clause from prior query
        SET V_MYSQL = (T.REORG_CLAUSE);
        PREPARE S0 FROM V_MYSQL;
        EXECUTE S0;
        
        -- If 3 or more concurrent reorgs active, wait for 10 seconds and check again. Else move to next reorg.
        WHILE 
            (SELECT COUNT(*) 
            FROM SYSIBMADM.SNAPTAB_REORG 
            WHERE REORG_STATUS <> 'COMPLETED') >= 2 
        DO
            
            call dbms_alert.sleep(10);
            
        END WHILE;
        
    END FOR;        
    
END
@

Right now if I execute the script above without the delay block, I get the following error:

SQL0501N  The cursor specified in a FETCH statement or CLOSE statement is not
open or a cursor variable in a cursor scalar function reference is not open.
SQLSTATE=24501

I tried declaring the cursor WITH HOLD, but it didn't help. The exact same cursor block works just fine if I'm using traditional SQL varchar commands instead. Currently I can't get to the delay block error.

Another question regarding the delay is that when I tested it separately and created a loop of say waitin 2 seconds for 5 times, inserting a TIMESTAMP value to a table each time, the total delay matched 2×10 seconds, but all rows had the same timestamp regardles of whether the timestamp was created in the script, or used the target table's default value. For logging purposes I need to get the actual timestamp for when these commands are being run.

Thanks in advance!

Best Answer

As the manual says,

The reorganize utility implicitly closes all the cursors.

Try fetching your SQL statement strings into an array and iterate over that instead.