Previous Topic: DB2 Asynchronous SamplingNext Topic: Synchronous Data Gatherer

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 MAT 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 MAT 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 MAT extracts the SQL and obtains access path information for each statement.

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 monitor time options 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 MAT).

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 MAT).

If obtaining the SQL statements from the DB2 catalog is an issue, CA MAT provides an option on the DB2 Monitor Criteria panel to suppress the collection of SQL from the DB2 catalog.