0

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 2x10 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!

3
  • Yeah I've gotten so many different errors most of which lead nowhere, so I don't rightly know. But my point is that I've done the loop by just reading dynamic SQL from a test table, and using them to create dynamic insert statements to another table. This works just fine. But for some reason executing procedure calls as above doesn't work, nor does the whole call dbms_alert.sleep. I thought to combine them both into this same question in the hopes that someone would just see what I'm doing wrong right away. Basically, the last script should be the final product, but doesn't work. Why?
    – Kahn
    Jun 15 '21 at 21:05
  • Does your script work if you omit the WHILE ... END WHILE; portion of the script and run it in a test environment?
    – John K. N.
    Jun 17 '21 at 7:14
  • @JohnK.N. No, the error comes from the part preceding it (I mean I've commented / deleted the WHILE loop and no difference). For some reason calling the procudes dynamically like that gives the error. If the source table has just regular insert commands instead of procedure calls, it works fine, but not with procedure calls. It's almost like db2 doesn't allow for executing procedure calls dynamically within a loop like that. But I don't get why, SQL Server has no such limitations. Also the error message makes no sense.
    – Kahn
    Jun 17 '21 at 10:11
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.