When using RC/Migrator, RC/Compare or RC/Update to analyze changes to objects, Model Services is one of the features being used to generate the Analysis Output. The analysis output contains the necessary utilities to unload/load/image copy your data, DDL to drop and create your objects, Rebind statements to make your packages and plans valid and operative again and the necessary grants statements to maintain the object authorizations.
WHERE is a customized Model Services Entry generated in the RC/Migrator worklist/script?
Release: R20
Component: RCM
When analyzing a strategy no matter which RCM strategy type, it is mandatory to specify a Model Service. This is where the control cards for all utilities are stored including symbolic variables to specify how the various datasets for unload, reorg, copy etc. must be defined and allocated.
Customers can create their own Models to be included when analyzing strategies – creating the worklist/script to implement the schema changes. Here are a couple of examples:
One important piece to remember is WHERE the customized Model Services Entry must be generated in the worklist/script. This is specified using one of the special symbolic variables available:
SQLPOSB |
Position for optional utility is before all SQL to create objects and after the unload utility. |
SQLPOSO |
Position for optional utility is with the SQL to create an object. |
SQLPOSA |
Position for optional utility is after all SQL and standard utilities. |
Example:
Let's assume you want to add a GRANT statement every time a table is being created:
1. From RC/Migrator main menu enter option 0.
2. Enter Option 6 for Model Services.
3. Find the model you want to update and type U.
4. Type I for insert on one of the CMD fields and hit Enter.
5. Specify a name (e.g. GRANT01), T for Table under column OB and a text description.
6. PF3 back and type E for EDIT at the GRANT01 line just created.
7. Within this ISPF session you can enter the necessary control cards and variables.
#IF(%SQLPOSA)
GRANT SELECT on table %CREATOR..%OBJECT to public ;
#ENDIF
The variable SQLPOSA will be checked after sql and standard utilities. Since T was specified under OB, it will only be generated for Tables.
The model line looks like this:
ROPMODU1 ----------- General Model Utilities ---------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
MODEL ID ===> GRANT01 DESCRIPTION ===> SEE GRANT01 MEMBER
CREATOR ===> authid01 SHAREOPTION ===> U (U,Y,N) SSID ===> ssid
--------------------------------------------------------------------- authid01
+ ---- LAST UPDATE ---- +
CMD SYM UTILITY OB DESCRIPTION SIZE + USER DATE TIME +
_ D BIND P BIND (DSN) authid01 yy/mm/dd hh:mm
_ Y CHECK TS IBM CHECK DATA RPI authid01 yy/mm/dd hh:mm
_ Y CHECK_R TS IBM CHECK DATA - RECOVERY RPI authid01 yy/mm/dd hh:mm
_ Y COPY TS IBM COPY (IC) RPI authid01 yy/mm/dd hh:mm
_ X COPY_B TS IBM COPY (IC) - BEFORE RPI authid01 yy/mm/dd hh:mm
_ Y COPY_R TS IBM COPY (IC) - RECOVERY RPI authid01 yy/mm/dd hh:mm
_ D COPYREXX T COPYREXX authid01 yy/mm/dd hh:mm
_ X FCHECK TS FAST CHECK DATA RPI authid01 yy/mm/dd hh:mm
_ X FLOAD T FAST LOAD RPI authid01 yy/mm/dd hh:mm
_ X FLOAD_R T FAST LOAD - RECOVERY RPI authid01 yy/mm/dd hh:mm
_ X FRECIXAL TS FAST RECOVER INDEX ALL RPI authid01 yy/mm/dd hh:mm
_ X FRECOVIX I FAST RECOVER INDEX RPI authid01 yy/mm/dd hh:mm
_ X FUNLD T FAST UNLOAD RPI authid01 yy/mm/dd hh:mm
_ X FUNLD_B T FAST UNLOAD - BEFORE RPI authid01 yy/mm/dd hh:mm
_ X FUNLD_R T FAST UNLOAD - RECOVERY RPI authid01 yy/mm/dd hh:mm
_ D GRANT01 T GRANT01 authid01 yy/mm/dd hh:mm <===========this one!
_ Y IBMULD T IBM UNLOAD RPI authid01 yy/mm/dd hh:mm
Consider this Migration:
P TS DBCORP.TSROLE4
X T authid1.TBROLE4
X I authid1.TXROLE4
The strategy analysis generates :
1. Unload of the table
2. Create the tablespace, table and Index on the target SSID.
3. Load the new table
4. Image copy of the tablespace
5. Runstats Utility is run
6. AND THEN after the last utility...the GRANT is generated. Position for optional utility is after all SQL and standard utilities.
.SYNC 45 'RUNSTATS TABLESPACE DBCORP.TSROLE4'
GRANT SELECT ON TABLE authid1.TBROLE4 TO PUBLIC ;
.SYNC 50 'GRANT01 TABLE authid1.TBROLE4'
If the code is changed so that it reads like this:
#IF(%SQLPOSO) <===================SQLPOSO instead of SQLPOSA
GRANT SELECT on table %CREATOR..%OBJECT to public ;
#ENDIF
The analysis result is:
.SYNC 15 'CREATE TABLE authid1.TBROLE4'
SET CURRENT SQLID = USER;
GRANT SELECT ON TABLE authid1.TBROLE4 TO PUBLIC ;
.SYNC 20 'GRANT01 TABLE authid1.TBROLE4'
More information you will find at the RC/Migrator Guide at section Utility Model Services.