I have a very big Stored Procedure in iSeries DB2 v8 which does the following:
- Calls other stored procedures inside the same schema
- Prepares dynamic sql statments from strings and runs them
- Calls other functions from the same schema
- Uses various tables from the same schema
My problem is that this Stored Procedure and the accompanying functions may change from that schema into another (ie. from 'superlib' to 'restorelib') and the whole code is currently hardcoded to run with the named schema.
What I want is to be able to do one of the two: either pass the name of the schema where everything is located via a parameter to the stored procedure, or have the stored procedure detect the name of the schema and use it to run itself.
This is a sample of my current code:
SELECT COUNT(*) INTO TotalNotDone FROM superlib.PROCESSTABLES WHERE PROCESS_FLAG < 1;
WHILE TotalNotDone > 0 DO
SET SQLLOOPSTMT = 'select name_to_proces from ' CONCAT SOURCELIBRARY CONCAT '.processtables where process_flag = 0' ;
PREPARE LOOPSTMT FROM SQLLOOPSTMT ;
OPEN LOOPCUR ;
FETCH LOOPCUR INTO TABLETOPROCESS ;
CALL superlib.SP_RESTORE_INSERTS ( SOURCELIBRARY , DESTLIBRARY , TABLETOPROCESS, P_STARTTIME ) ;
CLOSE LOOPCUR;
SELECT COUNT(*) INTO TotalNotDone FROM superlib.PROCESSTABLES WHERE PROCESS_FLAG < 1;
END WHILE ;
What I want is for NOT to have to write superlib inside the stored procedure to call or reference the tables i'm using and just have the Stored Procedure recognize it currently IS living in the schema superlib.
I've tried SET CURRENT SCHEMA = 'SUPERLIB'; and SET SCHEMA = 'SUPERLIB'; but neither works when calling the TABLES.
I've also changed the path when creating the Stored Procedure from:
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","PROGUSER1" ;
to
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SUPERLIB" ;
but that apparently does nothing.
