CPU and Storage reduction for Migrator Strategy and Analysis processing with ACM

Document ID : KB000054760
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

More and more customers are adopting our ACM VIEW support especially in RC/Migrator and RC/Compare 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.

Solution:

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 Unicenter 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 ACMDB2

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