CPU and Storage reduction for RC/Migrator Strategy and Analysis processing with ACM
Article ID: 54760
Updated On:
Products
RC/Migrator for DB2 for z/OS
RC/Update for DB2 for z/OS
RC Compare for DB2 for z/OS
Issue/Introduction
More and more customers are adopting our ACM VIEW support especially in RC/Migrator for Db2 for z/OS (RCM and
and RC/Compare for Db2 for z/OS (RCC) in order to gain CPU and storage relief. The resolution below contains CREATE VIEW statements
to match the ACM mapping screen; WHERE predicates have been added to specific views to further facilitate performance gains.
Resolution
This script will create VIEWS to be used by ACM in a Db2 z/OS environment.
This will provide performance improvements for several products where ACM is an option - especially RC/Migrator.
It's not necessary to define an ACM VIEW for each catalog table. Nevertheless, this member contains CREATE VIEW statements for all catalog
tables which are being referred within ACM. Predicates have been defined in specific instances where a significant performance improvement is likely.
Please note - these views ONLY reflect the catalog tables being referenced within ACM.
EXECUTE THE FOLLOWING COMMANDS PRIOR TO CREATING THE VIEWS:
===========================================================
CHANGE ALL "ACMDB" "database name"
CHANGE ALL "ACMTBCR" "table creator"
CHANGE ALL "ACMVWCR" "view creator"
CHANGE ALL "ACMIXCR" "index creator"
CHANGE ALL "ACMDB2" "ssid where views must be created"
CHANGE ALL "ACMID" "The ACM-VIEW creator - e.g. the same
name as used for the ACMID"
NOTE : The views where ACMDB is used in the predicate has an additional line commented out with a LIKE predicate
for the ACMDB. Execute a FIND command for ACMDB prior to CHANGE ALL if the LIKE predicate is desired
(typical in PeopleSoft environments).
Batch processor SYNC commands are included in order to ease a potential restart. This means the ".SYNC" statements must
be commented out if this script is executed outside of the Unicenter Batch Processor.
The EQUAL PREDICATES can be changed to LIKE PREDICATES when it is not possible to use EQUAL predicates.
.CONNECT ssid
CREATE VIEW ACMID.IPNAMES
AS SELECT * FROM SYSIBM.IPNAMES;
CREATE VIEW ACMID.LOCATIONS
AS SELECT * FROM SYSIBM.LOCATIONS;
CREATE VIEW ACMID.LULIST
AS SELECT * FROM SYSIBM.LULIST;
CREATE VIEW ACMID.LUMODES
AS SELECT * FROM SYSIBM.LUMODES;
CREATE VIEW ACMID.LUNAMES
AS SELECT * FROM SYSIBM.LUNAMES;
CREATE VIEW ACMID.MODESELECT
AS SELECT * FROM SYSIBM.MODESELECT;
.SYNC 60
CREATE VIEW ACMID.SYSAUXRELS
AS SELECT * FROM SYSIBM.SYSAUXRELS;
CREATE VIEW ACMID.SYSCHECKDEP
AS SELECT * FROM SYSIBM.SYSCHECKDEP;
CREATE VIEW ACMID.SYSCHECKS
AS SELECT * FROM SYSIBM.SYSCHECKS;
CREATE VIEW ACMID.SYSCHECKS2
AS SELECT * FROM SYSIBM.SYSCHECKS2;
CREATE VIEW ACMID.SYSCOLAUTH
AS SELECT * FROM SYSIBM.SYSCOLAUTH
WHERE CREATOR='ACMTBCR';
.SYNC 70
CREATE VIEW ACMID.SYSCOLDIST
AS SELECT * FROM SYSIBM.SYSCOLDIST;
CREATE VIEW ACMID.SYSCOLDISTSTATS
AS SELECT * FROM SYSIBM.SYSCOLDISTSTATS;
CREATE VIEW ACMID.SYSCOLDIST_HIST
AS SELECT * FROM SYSIBM.SYSCOLDIST_HIST;
CREATE VIEW ACMID.SYSCOLSTATS
AS SELECT * FROM SYSIBM.SYSCOLSTATS;
CREATE VIEW ACMID.SYSCOLUMNS
AS SELECT * FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR='ACMTBCR';
.SYNC 80
CREATE VIEW ACMID.SYSCOLUMNS_HIST
AS SELECT * FROM SYSIBM.SYSCOLUMNS_HIST;
CREATE VIEW ACMID.SYSCONSTDEP
AS SELECT * FROM SYSIBM.SYSCONSTDEP;
CREATE VIEW ACMID.SYSCOPY
AS SELECT * FROM SYSIBM.SYSCOPY;
CREATE VIEW ACMID.SYSDATABASE
AS SELECT * FROM SYSIBM.SYSDATABASE
WHERE NAME='ACMDB';
-- WHERE NAME LIKE 'ACMDB%';
.SYNC 90
CREATE VIEW ACMID.SYSDATATYPES
AS SELECT * FROM SYSIBM.SYSDATATYPES;
CREATE VIEW ACMID.SYSDBAUTH
AS SELECT * FROM SYSIBM.SYSDBAUTH;
CREATE VIEW ACMID.SYSDBRM
AS SELECT * FROM SYSIBM.SYSDBRM;
.SYNC 100
CREATE VIEW ACMID.SYSFIELDS
AS SELECT * FROM SYSIBM.SYSFIELDS
WHERE TBCREATOR='ACMTBCR';
CREATE VIEW ACMID.SYSFOREIGNKEYS
AS SELECT * FROM SYSIBM.SYSFOREIGNKEYS
WHERE CREATOR='ACMTBCR';
CREATE VIEW ACMID.SYSINDEXES
AS SELECT * FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR='ACMTBCR'
AND DBNAME='ACMDB';
-- AND DBNAME LIKE 'ACMDB%';
CREATE VIEW ACMID.SYSINDEXES_HIST
AS SELECT * FROM SYSIBM.SYSINDEXES_HIST;
CREATE VIEW ACMID.SYSINDEXPART
AS SELECT * FROM SYSIBM.SYSINDEXPART
WHERE IXCREATOR='ACMIXCR';
.SYNC 120
CREATE VIEW ACMID.SYSINDEXPART_HIST
AS SELECT * FROM SYSIBM.SYSINDEXPART_HIST;
CREATE VIEW ACMID.SYSINDEXSTATS
AS SELECT * FROM SYSIBM.SYSINDEXSTATS;
CREATE VIEW ACMID.SYSINDEXSTATS_HIST
AS SELECT * FROM SYSIBM.SYSINDEXSTATS_HIST;
CREATE VIEW ACMID.SYSJAVAOPTS
AS SELECT * FROM SYSIBM.SYSJAVAOPTS;
.SYNC 130
CREATE VIEW ACMID.SYSKEYCOLUSE
AS SELECT * FROM SYSIBM.SYSKEYCOLUSE
WHERE TBCREATOR='ACMTBCR';
CREATE VIEW ACMID.SYSKEYS
AS SELECT * FROM SYSIBM.SYSKEYS
WHERE IXCREATOR='ACMIXCR';
CREATE VIEW ACMID.SYSLOBSTATS
AS SELECT * FROM SYSIBM.SYSLOBSTATS;
.SYNC 140
CREATE VIEW ACMID.SYSPACKAGE
AS SELECT * FROM SYSIBM.SYSPACKAGE;
CREATE VIEW ACMID.SYSPACKAUTH
AS SELECT * FROM SYSIBM.SYSPACKAUTH;
CREATE VIEW ACMID.SYSPACKDEP
AS SELECT * FROM SYSIBM.SYSPACKDEP;
.SYNC 150
CREATE VIEW ACMID.SYSPACKLIST
AS SELECT * FROM SYSIBM.SYSPACKLIST;
CREATE VIEW ACMID.SYSPACKSTMT
AS SELECT * FROM SYSIBM.SYSPACKSTMT;
CREATE VIEW ACMID.SYSPARMS
AS SELECT * FROM SYSIBM.SYSPARMS;
CREATE VIEW ACMID.SYSPKSYSTEM
AS SELECT * FROM SYSIBM.SYSPKSYSTEM;
.SYNC 160
CREATE VIEW ACMID.SYSPLAN
AS SELECT * FROM SYSIBM.SYSPLAN;
CREATE VIEW ACMID.SYSPLANAUTH
AS SELECT * FROM SYSIBM.SYSPLANAUTH;
CREATE VIEW ACMID.SYSPLANDEP
AS SELECT * FROM SYSIBM.SYSPLANDEP;
CREATE VIEW ACMID.SYSPLSYSTEM
AS SELECT * FROM SYSIBM.SYSPLSYSTEM;
.SYNC 170
CREATE VIEW ACMID.SYSRELS
AS SELECT * FROM SYSIBM.SYSRELS
WHERE REFTBCREATOR='ACMTBCR';
CREATE VIEW ACMID.SYSRESAUTH
AS SELECT * FROM SYSIBM.SYSRESAUTH;
CREATE VIEW ACMID.SYSLINKS
AS SELECT * FROM SYSIBM.SYSLINKS;
CREATE VIEW ACMID.SYSPROCEDURES
AS SELECT * FROM SYSIBM.SYSPROCEDURES;
.SYNC 180
CREATE VIEW ACMID.SYSROUTINEAUTH
AS SELECT * FROM SYSIBM.SYSROUTINEAUTH;
CREATE VIEW ACMID.SYSROUTINES
AS SELECT * FROM SYSIBM.SYSROUTINES;
CREATE VIEW ACMID.SYSROUTINES_OPTS
AS SELECT * FROM SYSIBM.SYSROUTINES_OPTS;
CREATE VIEW ACMID.SYSROUTINES_SRC
AS SELECT * FROM SYSIBM.SYSROUTINES_SRC;
.SYNC 190
CREATE VIEW ACMID.SYSSCHEMAAUTH
AS SELECT * FROM SYSIBM.SYSSCHEMAAUTH;
CREATE VIEW ACMID.SYSSEQUENCES
AS SELECT * FROM SYSIBM.SYSSEQUENCES;
CREATE VIEW ACMID.SYSSEQUENCESDEP
AS SELECT * FROM SYSIBM.SYSSEQUENCESDEP;
.SYNC 200
CREATE VIEW ACMID.SYSSTMT
AS SELECT * FROM SYSIBM.SYSSTMT;
CREATE VIEW ACMID.SYSSTOGROUP
AS SELECT * FROM SYSIBM.SYSSTOGROUP;
CREATE VIEW ACMID.SYSSTRINGS
AS SELECT * FROM SYSIBM.SYSSTRINGS;
CREATE VIEW ACMID.SYSSYNONYMS
AS SELECT * FROM SYSIBM.SYSSYNONYMS
WHERE TBCREATOR='ACMTBCR';
.SYNC 210
CREATE VIEW ACMID.SYSTABAUTH
AS SELECT * FROM SYSIBM.SYSTABAUTH
WHERE TCREATOR='ACMTBCR'
AND DBNAME IN ('ACMDB', ' ');
-- AND (DBNAME LIKE 'ACMDB%' or DBNAME = ' ');
CREATE VIEW ACMID.SYSTABCONST
AS SELECT * FROM SYSIBM.SYSTABCONST;
CREATE VIEW ACMID.SYSTABLEPART
AS SELECT * FROM SYSIBM.SYSTABLEPART
WHERE DBNAME='ACMDB';
-- WHERE DBNAME LIKE 'ACMDB%';
CREATE VIEW ACMID.SYSTABLEPART_HIST
AS SELECT * FROM SYSIBM.SYSTABLEPART_HIST;
.SYNC 220
CREATE VIEW ACMID.SYSTABLES
AS SELECT * FROM SYSIBM.SYSTABLES
WHERE CREATOR='ACMTBCR'
AND DBNAME='ACMDB';
-- AND DBNAME LIKE 'ACMDB%';
CREATE VIEW ACMID.SYSTABLESPACE
AS SELECT * FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME='ACMDB';
-- WHERE DBNAME LIKE 'ACMDB%';
CREATE VIEW ACMID.SYSTABLES_HIST
AS SELECT * FROM SYSIBM.SYSTABLES_HIST;
CREATE VIEW ACMID.SYSTABSTATS
AS SELECT * FROM SYSIBM.SYSTABSTATS;
.SYNC 230
CREATE VIEW ACMID.SYSTABSTATS_HIST
AS SELECT * FROM SYSIBM.SYSTABSTATS_HIST;
CREATE VIEW ACMID.SYSTRIGGERS
AS SELECT * FROM SYSIBM.SYSTRIGGERS;
CREATE VIEW ACMID.SYSVIEWDEP
AS SELECT * FROM SYSIBM.SYSVIEWDEP
WHERE BCREATOR='ACMTBCR';
.SYNC 240
CREATE VIEW ACMID.SYSVIEWS
AS SELECT * FROM SYSIBM.SYSVIEWS
WHERE CREATOR='ACMVWCR';
CREATE VIEW ACMID.SYSVLTREE
AS SELECT * FROM SYSIBM.SYSVLTREE;
CREATE VIEW ACMID.SYSVOLUMES
AS SELECT * FROM SYSIBM.SYSVOLUMES;
CREATE VIEW ACMID.SYSVTREE
AS SELECT * FROM SYSIBM.SYSVTREE;
CREATE VIEW ACMID.SYSDUMMY1
AS SELECT * FROM SYSIBM.SYSDUMMY1;
CREATE VIEW ACMID.SYSUSERAUTH
AS SELECT * FROM SYSIBM.SYSUSERAUTH;
CREATE VIEW ACMID.USERNAMES
AS SELECT * FROM SYSIBM.USERNAMES;
.SYNC 250
Feedback
Yes
No
Powered by
