Sample Header Ad - 728x90

ADMIN_REVALIDATE_DB_OBJECTS over a cursor?

0 votes
1 answer
102 views
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