Using the Product in a DB2 Environment › Background DB2 Catalog Extraction

Background DB2 Catalog Extraction

DB2 maintains information about applications requesting DB2 services in its catalog. The SQL statements contained in a package or DBRM, for example, are stored by the BIND process. This information, along with table statistics and other data, is used to determine an optimum access path to execute the SQL statement.

CA Mainframe Application Tuner can access the DB2 catalog to obtain static SQL statements and collect access path information through the DB2 EXPLAIN process. This process runs as a background function and can run longer than the actual monitor, especially if a great deal of dynamic SQL is present or a DBRM or package contains many SQL statements.

For static statements, obtaining the SQL statement from the DB2 catalog is more reliable than the sample process. The statement that is stored in the DB2 catalog is the same statement coded in the application with extraneous spaces and HOST variables removed. For performance reasons, CA Mainframe Application Tuner extracts the SQL statements from the DB2 catalog for each package or DBRM encounter during sampling. This can become an issue if the package or DBRM is used for many functions and contains SQL that is not always used by every program. The result is an extended monitor while CA Mainframe Application Tuner extracts the SQL and obtains access path information for each statement.

In order to access DB2, the server needs to know the plan and package that was used during the installation process. The plan and package values are specified in the TUNSSP00 member and must match the plan and package values specified in the DB2 BIND control statements.

DB2PACK

DB2 package name. Specify the name of the DB2 package assigned during the DB2 BIND job during customization.

The default is MATnn, where nn is the value specified for the DB2LIBS keyword (normally the release number of CA Mainframe Application Tuner).

DB2PLAN

DB2 plan name. Specify the name of the DB2 plan assigned during the DB2 BIND job during customization.

The default is MATnnDB2, where nn is the value specified for the DB2LIBS keyword (normally the release number of CA Mainframe Application Tuner).

If obtaining the SQL statements from the DB2 catalog is an issue, CA Mainframe Application Tuner provides a command in the TUNSSP00 member to suppress the collection of SQL from the DB2 catalog.

DB2CTSQL

SQL from DB2 catalog.

DB2CTSQL is used to determine whether or not CA Mainframe Application Tuner should extract the plan or package statements from the DB2 catalog or extract them directly from DB2 control blocks when monitoring a DB2 application.

DB2HVSQL=YES and DB2HRVST=YES should be used in conjunction with DB2CTSQL=NO since the Data Harvester collects information in a synchronous manner.

Specify whether or not to collect the SQL statement from the DB2 catalog. The following are valid responses:

YES will collect the SQL data for each plan or package sampled or harvested from the DB2 catalog.

NO will not collect the SQL statement from the DB2 catalog.

It is recommend that DB2CTSQL=YES be used.

The default is DB2CTSQL=YES.

Suppressing the collection of the SQL statements from the DB2 catalog can result in the incorrect statement being identified during the sample process.

Another function of the DB2 background processing is obtaining current access path information through the DB2 EXPLAIN process. This function is discussed in more detail in Use the Explain Function.