RC/Migrator : Performance Enhancement Techniques

Products

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

Issue/Introduction

RC/Migrator analysis uses unqualified SQL to access the catalog tables. 

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 experiencing performance problems, there are many ways to potentially boost Migrator performance.

Environment

Release: R20
Component: RCM

Resolution

These performance-boosting techniques include:

Table of Contents

• ACM
• Additional Indexes
• FULLTREE
• SIZE Parameter in Model Services
• Use of UNDERSCORES in object name
• PTITSMG2 compressed and then REORGed.

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.

Max Threads

The MAXTHREADS parameter in hlq.CDBAPARM(MIGRATOR) 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.

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 the 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 3) Additional Indexes 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.

Additional Indexes

From DB2 V4.1 additional indexes can be created on the catalog tables. Database Management Solutions for DB2 for z/OS , Create Catalog Indexes , 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.

FULLTREE

During a migration or alter strategy analysis, unqualified calls to the catalog tables can be avoided by setting the FULLTREE to 'NO' in the MIGRATOR member of hlq.CDBAPARM. FULLTREE NO initiates CAF processing which ultimately minimizes SQL access.

Note: FULLTREE NO does not affect a Compare analysis. FULLTREE NO improves Migration and Alteration analysis performance only.

SIZE Parameter in Model Services

If 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:

1. 1. Using RUNSTATS information from the DB2 catalog
      
      
   2. Using PDA (Database Analyzer for DB2 for Z/OS) stats from PDA tables.
      
      
   3. 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 Database Analyzer is utilized).
      
      Performance wise, IDCAMS is the least optimal choice because IDCAMS can be very slow as it involves accessing the ICF catalog Table to retrieve 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 the 'SIZE' parameter to ensure optimal performance
      
      

Use of UNDERSCORES in object name

If using underscores (_) in view or table names and poor response time is experienced 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 of creators, but have thousands of different tables names.

PTITSMG2 compressed and then REORGed.

It has been shown that a significant performance gain can be realized if the tablespace PTITSMG2 is compressed(COMPRESS YES) and then reorged. Tablespace PTITSMG2 contains the table PTMG2_ALTER_0200 and index PTMG2_ALTERIX_0200.

The more rows present on the PTMG2_ALTER _0200 table the more significant the elapsed time savings.