0

Before I realized that there existed a procedure for revalidating objects (SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS), I wrote my own. Now, I try to replace that with the standard one, but it appears as if the call to SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS destroys the cursor I'm looping over. Example of old procedure:

db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL TOOLBOX.COMPILE_SCHEMA2(v.schemaname); END FOR; END @"
DB20000I  The SQL command completed successfully.

Example using ADMISN_REVALIDATE_DB_OBJECTS

db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')"
Return Status = 0
db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')"
Return Status = 0

Trying to use that in a loop over a cursor:

db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>v.schemaname); END FOR; END @"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
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

Note that it does not help to hard wire the schema:

db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')
[...]

also yields an error. I tried variations on looping over a cursor:

BEGIN
    DECLARE s VARCHAR(128);
    DECLARE v_at_end INTEGER default 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE C1 CURSOR for
        SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS;
    DECLARE CONTINUE HANDLER FOR not_found  SET v_at_end = 1 ;

    OPEN C1;
    fetch_loop:
    LOOP
        FETCH FROM C1 INTO s;
        IF v_at_end <>0 THEN LEAVE fetch_loop; END IF;
        CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>s);
    END LOOP;
    CLOSE C1;
END
@

But it does not work either. Any clue, what needs to be done to be able to call ADMIN_REVALIDATE_DB_OBJECTS in a loop?

3
  • 1
    Does it make any difference if you use a with hold cursor? Check if the called sproc does a commit either explicitly or implicitly.
    – mao
    Commented Jan 8 at 11:50
  • Thanks, I'll have a look Commented Jan 8 at 11:54
  • WITH HOLD seems to solve this issue, if you add an answer I'll accept it (If you don't I can add it myself). Commented Jan 8 at 12:41

1 Answer 1

1

To ensure consistency of catalogs data, the ADMIN_REVALIDATE_DB_OBJECTS routine is designed to run explicit COMMIT statements.

Therefore, the cursor must be declared WITH HOLD for it to remain valid across commits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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