How to Reduce CA Mainframe Application Tuner High Cpu Usage and Monitor Size By DB2 Monitors

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

Introduction:

CA Mainframe Application Tuner (CA MAT) takes samples of a task or job being monitored, and these samples are used when the monitor is Analyzed.  How many samples, and the information they contain, is largely determined by the Monitor Definition and by the values of various parameters in TUNSSP00.  When too many samples are taken, or the samples contain too much information, then the Analyze of the monitor can use a higher percentage of CPU cycles, and longer clock time, during the processing of the samples.

Instructions:

First, check the size of your DB2 monitor dataset.  If it's "larger than normal", and this can mean greater than 100 megabytes or more, than there is a good chance that by following the recommendations below you will substantially cut down the size of the DB2 monitor.

Referring to the CA MAT User Guide, Chapter 10, "Using the TriTune Explain function", make sure that TUNSSPOO parameters DB2EXPL=NO and DB2HEXPL=NO:

       DB2EXPL     requests that information regarding DB2 access path selection be obtained
                   from DB2 SQL statements by issuing the EXPLAIN command and externalizing the data.
 
                   The call for Explain data is made while the address space is being measured.
                   Specifying DB2EXPL=YES indicates that Explain data will be collected for
                   each dynamic SQL statement and all SQL statements in a DBRM or package.
                   DB2EXPL=NO indicates no DB2 Explain data is to be gathered. BATCH
                   indicates DB2 Explain data is collected only for batch jobs and not for online
                   systems, such as CICS.
 
                   Explain will be performed for all static SQL found in the DBRM or package, as
                   bound into the DB2 catalog. When DB2CTSQL=NO (do not access the DB2
                   catalog for SQL) is specified, the statement that is explained is derived from
                   internal DB2 objects.
 
                   Dynamic SQL is always explained from SQL derived from internal DB2 objects.
 
                   The default is DB2EXPL=NO.
 
      DB2HEXPL     requests that information regarding DB2 access path selection be obtained
                   from DB2 SQL statements that were extracted using the TriTune Synchronous Data Gatherer.
 
                   DB2EXPL=YES must be specified in conjunction with this option.
 
                   Specifying DB2HEXPL=YES indicates that Explain data will be collected for
                   each dynamic SQL statement and all SQL statements in a DBRM or package
                   that are seen by the TriTune Synchronous Data Gatherer. DB2HEXPL=NO
                   indicates that no DB2 Explain of the harvested SQL is performed.
 
                   This statement is only valid when DB2HVSQL=YES (harvest all SQL) or
                   DB2HVSQL=NO and DB2HVDYN=YES (harvest only dynamic SQL) are specified.
 
                   The default is DB2HEXPL=NO.

Recreate the DB2 monitor.  If it is still not significantly reduced in size, with the Analyze taking much less CPU cycles and finishing markedly faster, then please open a issue with CA Technologies Support.

Additional Information:

Click DB2 Explain Function for more information about using the Explain Function for your DB2 monitors.