How to improve the performance of a RC/Migrator Analysis

Products

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

Issue/Introduction

Why does an RC/Migrator for Db2 for z/OS (RCM) analysis use unqualified SQL to access the catalog
tables and how can the overall performance be enhanced?

Resolution

Because RC/Migrator was designed for medium to large migrations, the design utilizes just ONE unqualified cursor to access catalog data as opposed
to opening a qualified cursor many times. If you are experiencing performance problems, there are many ways to potentially boost Migrator performance;
these performance-boosting techniques include:

1. Limit the Number of Objects
   
   The simplest way to boost performance is to limit the number of objects selected into the strategy. Breaking up your strategy into multiple smaller
   strategies is a sure-fire way to increase performance.
   
   
2. Max Threads
   
   The MAXTHREADS parameter in parmlib(MIGRATORr) member limits the number of available threads per analysis. Since a batch analysis can
   use up to 12 threads, increasing MAXTHREADS to 12 could help the analysis run faster, provided your IDFORE (online maximum connections) or
   IDBACK (batch maximum connections) DSNZPARM parameter is 12 or more.
   
   
3. ACM
   
   Alternate Catalog Mapping (ACM) improves performance by reducing contention on the catalog tablespaces. Additionally, isolating the ACM tables into
   separate tablespaces, (as opposed to the catalog which has several tables in same tablespace) could further improve performance benefits.
   
   Shadow Tables
   When shadow tables are accessed, only the tablespace containing the shadow table is locked, reducing system catalog contention. Performance can further
   be improved by defining additional indexes on the shadow tables. See d) below
   
   Views
   Benchmark testing has shown that the use of views with a where clause in ACM mappings can greatly reduce getpage access and CPU utilization.
   When using views with a where clause in ACM be careful that all related objects can be accessed since only the objects qualifying the filtering in
   the views are recognized. For example if your ACMID SYSTABLES setting links to a view having clause 'WHERE CREATOR = CREATORA',
   and if a strategy table named CREATORA.TABLEA contained a view called CREATORB.VIEWB then this view could not be migrated in the
   strategy because only SYSTABLES rows having CREATOR = 'CREATORA' are available.
   
   
4. Additional Indexes
   
   Beginning with DB2 V4.1 additional indexes can be created on the catalog tables. Appendix A of the Database Management Solutions for Db2 for z/OS
   Post-Installation and Customization Guide contains our recommendations for creating additional indexes on catalog tables, with the goal of
   improving product performance. Additional indexes are also beneficial to ACM tables.
   
   
5. Fulltree
   
   During a migration or alter strategy analysis, unqualified calls to the catalog tables can be avoided by setting the FULLTREE to 'NO' in
   parmlib(migrator). FULLTREE NO initiates CAF processing which ultimately minimizes SQL access. Note: FULLTREE NO does not
   affect the a Compare analysis. FULLTREE NO improves analysis performance only.
   
   
6. VSAM Reads
   
   The use of VSAM READS speeds up processing by avoiding SQL access when creating, updating and analyzing RC/Migrator strategies.
   Vsam Reads is also useful is speeding up the initial object tree build process. When VSAM READS is activated catalog information is
   accessed by reading underlying vsam datasets. VSAM READS can be activated by setting the VSAMID parm to (*) or (userid) in parmlib(migrator).
   If (*) is used, the primary logon ID is used for authority. If (userid) is used, then the authority of the (userid) will always be used.
   The ID(s) used for VSAM READS authority must have Read Control Interval Access to the DB2 catalog underlying VSAM datasets.
   * Note: Vsam Reads cannot be used in conjunction with ACM
   
   * NOTE: The VSAM analysis option is no longer a valid option when operating in a V8 subsystem.
   
   
7. SIZE Parameter in Model Services
   
   If you're encountering analysis performance problems, the problem could be caused by obsolete or missing statistical information.
   Whenever RCM builds utility statements, it needs to determine the size of the unload or temporary work datasets.
   This is done 1 of 3 ways: Using RUNSTATS information from the DB2 catalog Using PDA stats from PDA tables.
   Using IDCAMS information from the VSAM files of the underlying object. The 'SIZE' parameter in Model Services is used to determine where to
   retrieve this information from. So, RPI means RUNSTATS first (if available), then PDA (if available) and then finally IDCAMS.
   Similarly, PRI means RCM uses PDA statistics first (if available), then RUNSTATS (if available), and then IDCAMS. (We recommend PRI if using PDA).
   Performance wise, IDCAMS is the least optimal choice because IDCAMS can be very slow as it involves accessing the ICF catalog
   Tabletrieve dataset information. Note that although the size setting is PRI or RPI, if the data is not available in the catalog or in PDA,
   RCM will then use IDCAMS to retrieve size information In one case study, analysis time dropped from 55 to 1 minute when RUNSTATS
   were used instead of IDCAMS.
   *
   Remember IDCAMS is slow, so keep RUNSTATS or PDA STATS current and
   use RPI or PRI in SIZE parm to ensure optimal performance
   
   
8. Use of UNDERSCORES in object name
   
   If you use underscores (_) in view or table names and experience poor response time when listing views/tables in
   either RC/Migrator,RC/Query or RC/Update, try adding a new index to SYSVIEWS and/or SYSTABLES using
   columns (Name,Creator) followed by a REBIND. This should improve performance significantly. In general - there
   is a MUCH better filter factor on NAME compared to CREATOR particularly in ERP environments, where it is common to use a limited number
   creators, but have thousands of different tables names.
   
   
9. It has been shown that a significant performance gain can be realized if the tablespace PTITSMG2 is compressed and then reorganized.
   The more rows present on the PTMG2_ALTER _0200 table the more significant
   the elapsed time savings. Tablespace PTITSMG2 contains the table PTMG2_ALTER_0200 and index PTMG2_ALTERIX_0200.