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?
Asked by Lennart - Slava Ukraini
(23862 rep)
Jan 8, 2024, 11:37 AM
Last activity: Jan 8, 2024, 12:51 PM
Last activity: Jan 8, 2024, 12:51 PM