News
Abstract
Improved catalog management for procedures and functions
Content
- Create Duplicate Object (CRTDUPOBJ) - the routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object
- Copy Library (CPYLIB) - the routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object
- Rename Object (RNMOBJ) - the routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column pointing to the renamed executable object
- Move Object (MOVOBJ) - the routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column pointing to the moved executable object
This enhancement includes coverage for Librarian APIs or other operations built upon these commands.
Example 1. Use the Create Duplicate Object command against an SQL Procdure's executable *PGM object
CREATE SCHEMA PRODUCTION_SCHEMA;
CREATE OR REPLACE PROCEDURE PRODUCTION_SCHEMA.ADD_NUMS(in input_number_1 bigint, in input_number_2 bigint, out result_number bigint)
LANGUAGE SQL
BEGIN
SET result_number = input_number_1 + input_number_2;
END;
CREATE SCHEMA PRODUCTION_SCHEMA_NEW;
CL: CRTDUPOBJ OBJ(ADD_NUMS) FROMLIB(PRODU00001) OBJTYPE(*PGM) TOLIB(PRODU00002);
CALL PRODUCTION_SCHEMA.ADD_NUMS(2, 7, ?);
CALL PRODUCTION_SCHEMA_NEW.ADD_NUMS(5, 12, ?);
Run SQL Scripts output:
> CALL PRODUCTION_SCHEMA.ADD_NUMS(2, 7, ?)
Return Code = 0
Output Parameter #3 = 9
Statement ran successfully (47 ms)
> CALL PRODUCTION_SCHEMA_NEW.ADD_NUMS(5, 12, ?)
Return Code = 0
Output Parameter #3 = 17
Statement ran successfully (15 ms)
DB2 PTF Group SF99701 level 18 (or higher) includes improved database catalog management when SQL routines are copied, moved or renamed When an SQL or external procedure or function is created, the routine information is stored within the *PGM or *SRVPGM. Previously, when Librarian commands were used to copy/move/rename the object, the QSYS2/SYSROUTINE, QSYS2/SYSPARMS and QSYS2/SYSRTNDEP catalogs were left unchanged.
The following commands (and their API counterparts) are changed to keep the catalogs in sync with the executable object for procedures and functions:
Create Duplicate Object (CRTDUPOBJ) - the routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object
Copy Library (CPYLIB) - the routine catalog information is duplicated and the SYSROUTINE EXTERNAL_NAME column points to the newly duplicated executable object
Rename Object (RNMOBJ) - the routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column pointing to the renamed executable object
Move Object (MOVOBJ) - the routine catalog information is modified with the SYSROUTINE EXTERNAL_NAME column pointing to the moved executable object
If you have processing that is reliant upon the previous catalog behavior, the changed behavior can be partially disabled by adding an environment variable named QIBM_SQL_NO_CATALOG_UPDATE. If this environment variable exists system wide or within the job that is executing the MOVOBJ or RNMOBJ commands, the catalog entries will not be changed. The environment variable has no affect on the CPYLIB and CRTDUPOBJ commands.
For example:
ADDENVVAR ENVVAR(QIBM_SQL_NO_CATALOG_UPDATE) LEVEL(*SYS)
Was this topic helpful?
Document Information
Modified date:
11 April 2021
UID
ibm11169026