RC Products CPU and Storage Relief with ACM for DB2 10.

Document ID:  TEC568868
Last Modified Date:  07/04/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA RC/Migrator for DB2 for z/OS
  • CA RC/Compare for DB2 for z/OS
  • CA RC/Update for DB2 for z/OS

Components

  • CA RC/Migrator for DB2 for z/OS:RCM
Introduction:

More and more customers are adopting our Alternate Catalog mapping (ACM) VIEW support especially in RC/Migrator, RC/Compare and RC/Query in order to gain CPU and storage relief. The resolution below contains CREATE VIEW statements to match the ACM mapping screen with WHERE predicates added to specific Views to further facilitate performance gains. Refer to the instructions in the script for changes to make.

Instructions:
- *********************************************************************************
- *  This script will create VIEWS to be used by ACM in a DB2 z/OS V1- 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 
- * referenced within ACM.
- * Predicates have been defined in specific instances where a significant performance improvement is likely.
- *
- * 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" statement must
- * be commented out if this script is executed outside of the Batch Processor.
- *
- * The EQUAL PREDICATES can be changed to LIKE PREDICATES when it is not possible to use EQUAL predicates.
- *
- ***********************************************************************************
  
  
.CONNECT ACMDB2
 
--* CONNECTIVITY - REMOTE ACCESS MANAGEMENT
 
CREATE VIEW ACMID.IPLIST
                   AS SELECT * FROM SYSIBM.IPLIST;
 
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;
 
CREATE VIEW ACMID.USERNAMES
                   AS SELECT * FROM SYSIBM.USERNAMES;
 
 
--* DATA DESIGN
 
CREATE VIEW ACMID.SYSRELS
                   AS SELECT * FROM SYSIBM.SYSRELS
                   WHERE REFTBCREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSFOREIGNKEYS
                   AS SELECT * FROM SYSIBM.SYSFOREIGNKEYS
                   WHERE CREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSCHECKS
                   AS SELECT * FROM SYSIBM.SYSCHECKS;
 
CREATE VIEW ACMID.SYSCHECKS2
                   AS SELECT * FROM SYSIBM.SYSCHECKS2;
 
CREATE VIEW ACMID.SYSCOLUMNS
                   AS SELECT * FROM SYSIBM.SYSCOLUMNS
                   WHERE TBCREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSDATABASE
                   AS SELECT * FROM SYSIBM.SYSDATABASE
                   WHERE NAME = 'ACMDB';
--                WHERE NAME LIKE 'ACMDB%';
 
CREATE VIEW ACMID.SYSDATATYPES
                    AS SELECT * FROM SYSIBM.SYSDATATYPES;
  
CREATE VIEW ACMID.SYSINDEXES
                   AS SELECT * FROM SYSIBM.SYSINDEXES
                   WHERE TBCREATOR = 'ACMTBCR'
                   AND DBNAME = 'ACMDB';
--                AND DBANME LIKE 'ACMDB%';
 
CREATE VIEW ACMID.SYSKEYTARGETS
                   AS SELECT * FROM SYSIBM.SYSKEYTARGETS;
 
CREATE VIEW ACMID.SYSKEYS
                   AS SELECT * FROM SYSIBM.SYSKEYS
                   WHERE IXCREATOR = 'ACMIXCR';
  
CREATE VIEW ACMID.SYSSEQUENCES
                   AS SELECT * FROM SYSIBM.SYSSEQUENCES;
 
CREATE VIEW ACMID.SYSTABCONST
                    AS SELECT * FROM SYSIBM.SYSTABCONST;
  
CREATE VIEW ACMID.SYSSYNONYMS
                   AS SELECT * FROM SYSIBM.SYSSYNONYMS
                   WHERE TBCREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSTABLES
                   AS SELECT * FROM SYSIBM.SYSTABLES
                   WHERE CREATOR = 'ACMTBCR' ;
-- PICK ONE of the following
--                AND DBANEM = 'ACMDB'
--                AND DBNAME LIKE 'ACMDB%';
 
CREATE VIEW ACMID.SYSVIEWS
                   AS SELECT * FROM SYSIBM.SYSVIEWS
                   WHERE CREATOR = 'ACMVWCR';
 
CREATE VIEW ACMID.SYSINDEXPART
                   AS SELECT * FROM SYSIBM.SYSINDEXPART
                   WHERE IXCREATOR = 'ACMIXCR';
  
CREATE VIEW ACMID.SYSTABLEPART
                   AS SELECT * FROM SYSIBM.SYSTABLEPART
                   WHERE DBNAME = 'ACMDB';
--                WHERE DBNAME LIKE 'ACMDB%';
 
CREATE VIEW ACMID.SYSTABLESPACE
                   AS SELECT * FROM SYSIBM.SYSTABLESPACE
                   WHERE DBNAME = 'ACMDB';
--                WHERE DBNAME LIKE 'ACMDB%';
 
 
--* CROSS REFERENCE
 
CREATE VIEW ACMID.SYSCHECKDEP
                   AS SELECT * FROM SYSIBM.SYSCHECKDEP;
 
CREATE VIEW ACMID.SYSCONSTDEP
                   AS SELECT * FROM SYSIBM.SYSCONSTDEP;
 
CREATE VIEW ACMID.SYSAUXRELS
                   AS SELECT * FROM SYSIBM.SYSAUXRELS;
 
CREATE VIEW ACMID.SYSKEYCOLUSE
                   AS SELECT * FROM SYSIBM.SYSKEYCOLUSE
                   WHERE TBCREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSSEQUENCESDEP
                    AS SELECT * FROM SYSIBM.SYSSEQUENCESDEP;
 
CREATE VIEW ACMID.SYSVIEWDEP
                   AS SELECT * FROM SYSIBM.SYSVIEWDEP
                   WHERE BCREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSPACKDEP
                   AS SELECT * FROM SYSIBM.SYSPACKDEP;
 
CREATE VIEW ACMID.SYSPACKLIST
                   AS SELECT * FROM SYSIBM.SYSPACKLIST;
 
CREATE VIEW ACMID.SYSPLANDEP
                   AS SELECT * FROM SYSIBM.SYSPLANDEP;
 
CREATE VIEW ACMID.SYSDEPENDENCIES
                   AS SELECT * FROM SYSIBM.SYSDEPENDENCIES;
 
CREATE VIEW ACMID.SYSQUERY
                   AS SELECT * FROM SYSIBM.SYSQUERY;
 
CREATE VIEW ACMID.SYSQUERYOPTS
                   AS SELECT * FROM SYSIBM.SYSQUERYOPTS;
 
CREATE VIEW ACMID.SYSQUERYPLAN
                   AS SELECT * FROM SYSIBM.SYSQUERYPLAN;
 
CREATE VIEW ACMID.SYSPENDINGDDL
                   AS SELECT * FROM SYSIBM.SYSPENDINGDDL
                   WHERE DBNAME = 'ACMDB';
 
CREATE VIEW ACMID.SYSPENDINGOBJECTS
                   AS SELECT * FROM SYSIBM.SYSPENDINGOBJECTS
                   WHERE DBNAME = 'ACMDB';
 
 
--* MAINTENANCE
 
CREATE VIEW ACMID.SYSCOPY
                   AS SELECT * FROM SYSIBM.SYSCOPY;
 
CREATE VIEW ACMID.SYSSTOGROUP
                   AS SELECT * FROM SYSIBM.SYSSTOGROUP;
 
CREATE VIEW ACMID.SYSVOLUMES
                   AS SELECT * FROM SYSIBM.SYSVOLUMES;
 
 
--* DUMMY TABLE
 
CREATE VIEW ACMID.SYSDUMMY1
                   AS SELECT * FROM SYSIBM.SYSDUMMY1;
 
CREATE VIEW ACMID.SYSDUMMYA
                   AS SELECT * FROM SYSIBM.SYSDUMMYA;
 
CREATE VIEW ACMID.SYSDUMMYE
                   AS SELECT * FROM SYSIBM.SYSDUMMYE;
 
CREATE VIEW ACMID.SYSDUMMYU
                   AS SELECT * FROM SYSIBM.SYSDUMMYU;
 
 
--* AUTHORIZATION
 
CREATE VIEW ACMID.SYSENVIRONMENT
                   AS SELECT * FROM SYSIBM.SYSENVIRONMENT; 
 
CREATE VIEW ACMID.SYSPACKAUTH
                   AS SELECT * FROM SYSIBM.SYSPACKAUTH;
 
CREATE VIEW ACMID.SYSPLANAUTH
                   AS SELECT * FROM SYSIBM.SYSPLANAUTH;
 
CREATE VIEW ACMID.SYSCOLAUTH
                   AS SELECT * FROM SYSIBM.SYSCOLAUTH
                   WHERE CREATOR = 'ACMTBCR';
 
CREATE VIEW ACMID.SYSDBAUTH
                    AS SELECT * FROM SYSIBM.SYSDBAUTH;
 
CREATE VIEW ACMID.SYSRESAUTH
                   AS SELECT * FROM SYSIBM.SYSRESAUTH;
 
CREATE VIEW ACMID.SYSTABAUTH
                   AS SELECT * FROM SYSIBM.SYSTABAUTH
                   WHERE TCREATOR = 'ACMTBCR' ;
-- Pick one of the following
--                AND (DBNAME IN ('ACMDB', ' ') ;
--                AND (DBNAME LIKE 'ACMDB%' OR DBNAME = ' ');
 
CREATE VIEW ACMID.SYSSCHEMAAUTH
                   AS SELECT * FROM SYSIBM.SYSSCHEMAAUTH;
 
CREATE VIEW ACMID.SYSSEQUENCEAUTH
                   AS SELECT * FROM SYSIBM.SYSSEQUENCEAUTH;
 
CREATE VIEW ACMID.SYSROUTINEAUTH
                   AS SELECT * FROM SYSIBM.SYSROUTINEAUTH;
 
CREATE VIEW ACMID.SYSUSERAUTH
                   AS SELECT * FROM SYSIBM.SYSUSERAUTH;
 
CREATE VIEW ACMID.SYSOBJROLEDEP
                   AS SELECT * FROM SYSIBM.SYSOBJROLEDEP;
 
CREATE VIEW ACMID.SYSROLES
                   AS SELECT * FROM SYSIBM.SYSROLES;
 
CREATE VIEW ACMID.SYSCONTROLS
                   AS SELECT * FROM SYSIBM.SYSCONTROLS;
 
CREATE VIEW ACMID.SYSTABLES_PROFILES
                   AS SELECT * FROM SYSIBM.SYSTABLES_PROFILES;
 
 
--* ENCODING/DECODING
 
CREATE VIEW ACMID.SYSSTRINGS
                   AS SELECT * FROM SYSIBM.SYSSTRINGS;
 
CREATE VIEW ACMID.SYSFIELDS
                   AS SELECT * FROM SYSIBM.SYSFIELDS
                   WHERE TBCREATOR = 'ACMTBCR';
 
 
--* ROUTINES - STORED PROCEDURES - FUNCTIONS and TRIGGERS
 
CREATE VIEW ACMID.SYSTRIGGERS
                   AS SELECT * FROM SYSIBM.SYSTRIGGERS;
 
CREATE VIEW ACMID.SYSPARMS
                   AS SELECT * FROM SYSIBM.SYSPARMS;
 
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;
 
 
--* PLAN/PACKAGE
 
CREATE VIEW ACMID.SYSDBRM
                   AS SELECT * FROM SYSIBM.SYSDBRM;
 
CREATE VIEW ACMID.SYSPACKAGE
                   AS SELECT * FROM SYSIBM.SYSPACKAGE;
 
CREATE VIEW ACMID.SYSPACKSTMT
                   AS SELECT * FROM SYSIBM.SYSPACKSTMT;
 
CREATE VIEW ACMID.SYSPKSYSTEM
                   AS SELECT * FROM SYSIBM.SYSPKSYSTEM;
 
CREATE VIEW ACMID.SYSPLAN
                   AS SELECT * FROM SYSIBM.SYSPLAN;
  
CREATE VIEW ACMID.SYSPLSYSTEM
                   AS SELECT * FROM SYSIBM.SYSPLSYSTEM;
 
CREATE VIEW ACMID.SYSSTMT
                   AS SELECT * FROM SYSIBM.SYSSTMT;
 
CREATE VIEW ACMID.SYSPACKCOPY
                   AS SELECT * FROM SYSIBM.SYSPACKCOPY;
  
 
--* TRUSTED CONTEXT
 
CREATE VIEW ACMID.SYSCONTEXT
                   AS SELECT * FROM SYSIBM.SYSCONTEXT;
 
CREATE VIEW ACMID.SYSCONTEXTAUTHIDS
                   AS SELECT * FROM SYSIBM.SYSCONTEXTAUTHIDS;
 
CREATE VIEW ACMID.SYSCTXTTRUSTATTRS
                   AS SELECT * FROM SYSIBM.SYSCTXTTRUSTATTRS;
 
 
--* PERFORMANCE - STATISTICAL
 
CREATE VIEW ACMID.SYSCOLDIST
                   AS SELECT * FROM SYSIBM.SYSCOLDIST;
 
CREATE VIEW ACMID.SYSCOLDISTSTATS
                   AS SELECT * FROM SYSIBM.SYSCOLDISTSTATS;
 
CREATE VIEW ACMID.SYSCOLSTATS
                   AS SELECT * FROM SYSIBM.SYSCOLSTATS;
 
CREATE VIEW ACMID.SYSINDEXSTATS
                   AS SELECT * FROM SYSIBM.SYSINDEXSTATS;
 
CREATE VIEW ACMID.SYSLOBSTATS
                   AS SELECT * FROM SYSIBM.SYSLOBSTATS;
 
CREATE VIEW ACMID.SYSKEYTARGETSTATS
                   AS SELECT * FROM SYSIBM.SYSKEYTARGETSTATS;
 
CREATE VIEW ACMID.SYSKEYTGTDIST
                   AS SELECT * FROM SYSIBM.SYSKEYTGTDIST;
 
CREATE VIEW ACMID.SYSTABSTATS
                    AS SELECT * FROM SYSIBM.SYSTABSTATS;
 
 
--* PERFORMANCE - STATISTICAL - HISTORY
 
CREATE VIEW ACMID.SYSINDEXES_HIST
                   AS SELECT * FROM SYSIBM.SYSINDEXES_HIST;
 
CREATE VIEW ACMID.SYSINDEXPART_HIST
                   AS SELECT * FROM SYSIBM.SYSINDEXPART;
 
CREATE VIEW ACMID.SYSINDEXSTATS_HIST
                   AS SELECT * FROM SYSIBM.SYSINDEXSTATS_HIST;
 
CREATE VIEW ACMID.SYSKEYTARGETS_HIST
                   AS SELECT * FROM SYSIBM.SYSKEYTARGETS_HIST;
 
CREATE VIEW ACMID.SYSKEYTGTDIST_HIST
                   AS SELECT * FROM SYSIBM.SYSKEYTGTDIST_HIST;
 
CREATE VIEW ACMID.SYSLOBSTATS_HIST
                   AS SELECT * FROM SYSIBM.SYSLOBSTATS_HIST;
 
CREATE VIEW ACMID.SYSTABSTATS_HIST
                   AS SELECT * FROM SYSIBM.SYSTABSTATS_HIST;
 
CREATE VIEW ACMID.SYSTABLES_HIST
                   AS SELECT * FROM SYSIBM.SYSTABLES_HIST;
 
CREATE VIEW ACMID.STSTABLEPART_HIST
                   AS SELECT * FROM SYSIBM.SYSTABLEPART_HIST;
 
CREATE VIEW ACMID.SYSCOLDIST_HIST
                   AS SELECT * FROM SYSIBM.SYSCOLDIST_HIST;
 
CREATE VIEW ACMID.SYSCOLUMNS_HIST
                   AS SELECT * FROM SYSIBM.SYSCOLUMNS_HIST;
 
  
 
--* REAL TIME STATISTICS
 
CREATE VIEW ACMID.SYSINDEXSPACESTATS
                   AS SELECT * FROM SYSIBM.SYSINDEXSPACESTATS;
 
CREATE VIEW ACMID.SYSTABLESPACESTATS
                   AS SELECT * FROM SYSIBM.SYSTABLESPACESTATS;
 
 
--* JAVA
 
CREATE VIEW ACMID.SYSJARCONTENTS
                   AS SELECT * FROM SYSIBM.SYSJARCONTENTS;
 
CREATE VIEW ACMID.SYSJAROBJECTS
                   AS SELECT * FROM SYSIBM.SYSJAROBJECTS;
 
CREATE VIEW ACMID.SYSJAVAOPTS
                   AS SELECT * FROM SYSIBM.SYSJAVAOPTS;
 
CREATE VIEW ACMID.SYSJAVAPATHS
                   AS SELECT * FROM SYSIBM.SYSJAVAPATHS;
 
 
--* XML
 
CREATE VIEW ACMID.XSROBJECTS
                   AS SELECT * FROM SYSIBM.XSROBJECTS;
 
CREATE VIEW ACMID.XSROBJECTHIERARCHIES
                   AS SELECT * FROM SYSIBM.XSROBJECTHIERARCHIES; 
 
CREATE VIEW ACMID.SYSXMLRELS
                   AS SELECT * FROM SYSIBM.SYSXMLRELS;
  
CREATE VIEW ACMID.SYSXMLSTRINGS
                   AS SELECT * FROM SYSIBM.SYSXMLSTRINGS;
 
CREATE VIEW ACMID.XSROBJECTCOMPONENTS
                   AS SELECT * FROM SYSIBM.XSROBJECTCOMPONENTS;
  
CREATE VIEW ACMID.SYSXMLTYPMOD
                   AS SELECT * FROM SYSIBM.SYSXMLTYPMOD;
 
CREATE VIEW ACMID.SYSXMLTYPMSCHEMA
                   AS SELECT * FROM SYSIBM.SYSXMLTYPMSCHEMA;
  
 
--* AUDIT
 
CREATE VIEW ACMID.SYSAUDITPOLICIES
                   AS SELECT * FROM SYSIBM.SYSAUDITPOLICIES;
 
 
--* AUTONOMIC
 
CREATE VIEW ACMID.SYSAUTOALERTS
                   AS SELECT * FROM SYSIBM.SYSAUTOALERTS;
 
CREATE VIEW ACMID.SYSAUTORUNS_HIST
                   AS SELECT * FROM SYSIBM.SYSAUTORUNS_HIST;
 
CREATE VIEW ACMID.SYSAUTOTIMEWINDOWS
                   AS SELECT * FROM SYSIBM.SYSAUTOTIMEWINDOWS;

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >