Plan/Package Activity

This topic shows detailed information about Statistics - Plan/Package Activity.

Statistics - Plan/Package Activity

The field labels shown in the following sample layout of Statistics - Plan/Package Activity are described in the following section.


PLAN/PACKAGE PROCESSING      QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
INCREMENTAL BINDS                0.00     0.00      N/C     0.00
INCREMENTAL BINDS INVALID STMT   0.00     0.00      N/C     0.00
INCREMENTAL BINDS REDRIVEN       0.00     0.00      N/C     0.00

PLAN ALLOCATION ATTEMPTS         2.00     0.01      N/C     0.06
PLAN ALLOCATION SUCCESSFUL       2.00     0.01      N/C     0.06
PACKAGE ALLOCATION ATTEMPT      17.00     0.09      N/C     0.52
PACKAGE ALLOCATION SUCCESS      17.00     0.09      N/C     0.52

PLANS BOUND                      0.00     0.00      N/C     0.00
BIND ADD SUBCOMMANDS             0.00     0.00      N/C     0.00
BIND REPLACE SUBCOMMANDS         0.00     0.00      N/C     0.00
TEST BINDS NO PLAN-ID            0.00     0.00      N/C     0.00
PACKAGES BOUND                  14.00     0.08      N/C     0.42
BIND ADD PACKAGE SUBCOMMAND     49.00     0.27      N/C     1.48
BIND REPLACE PACKAGE SUBCOM     14.00     0.08      N/C     0.42

AUTOMATIC BIND ATTEMPTS          0.00     0.00      N/C     0.00
AUTOMATIC BINDS SUCCESSFUL       0.00     0.00      N/C     0.00
AUTO.BIND INVALID RES. IDS       0.00     0.00      N/C     0.00
AUTO.BIND PACKAGE ATTEMPTS       0.00     0.00      N/C     0.00
AUTO.BIND PACKAGES SUCCESS       0.00     0.00      N/C     0.00
AUTO.BIND PHASE-IN ATTEMPTS      0.00     0.00      N/C     0.00
AUTO.BIND PHASE-IN QUEUED        0.00     0.00      N/C     0.00
AUTO.BIND PHASE-IN SUCCESS       0.00     0.00      N/C     0.00

REBIND SUBCOMMANDS               0.00     0.00      N/C     0.00
ATTEMPTS TO REBIND A PLAN        0.00     0.00      N/C     0.00
PLANS REBOUND                    0.00     0.00      N/C     0.00
REBIND PACKAGE SUBCOMMANDS       0.00     0.00      N/C     0.00
ATTEMPTS TO REBIND PACKAGE       0.00     0.00      N/C     0.00
PACKAGES REBOUND                 0.00     0.00      N/C     0.00

FREE PLAN SUBCOMMANDS            0.00     0.00      N/C     0.00
ATTEMPTS TO FREE A PLAN          0.00     0.00      N/C     0.00
PLANS FREED                      0.00     0.00      N/C     0.00
FREE PACKAGE SUBCOMMANDS         0.00     0.00      N/C     0.00
ATTEMPTS TO FREE A PACKAGE       0.00     0.00      N/C     0.00
PACKAGES FREED                   0.00     0.00      N/C     0.00

INCREMENTAL BINDS

The number of incremental binds (excluding prepare). It is incremented by:

SQL statements with BIND VALIDATE(RUN) that fail at bind time and are bound again at execution time
Static DDL statements (such as CREATE TABLE, DROP TABLE, LOCK TABLE) that use Db2 private protocol

Background and Tuning Information

If a plan is bound with VALIDATE(RUN), Db2 performs validity checks at bind time and rechecks any failures at run time. This can result in catalog contention and degraded application performance, depending on the number of statements flagged and how many times they are executed. Avoid VALIDATE(RUN) if possible. Ensure that all objects are created and all privileges are granted before bind, and select the VALIDATE(BIND) option.

Field Name: QXINCRB

This is an exception field.

INCREMENTAL BINDS INVALID STMT

The number of incremental binds due to invalidated statements.

Field Name: QXIBINVS

INCREMENTAL BINDS REDRIVEN

The number of incremental bind statements with APREUSE that Db2 retried.

Field Name: QXIBAPRE

PLAN ALLOCATION ATTEMPTS

The number of times a request was made to allocate a bound plan for an agent.

It represents the number of times Db2 was requested to create a thread by the attachment facility for the user. This does not include allocations for Db2 system agents.

Field Name: QTALLOCA

This is an exception field.

PLAN ALLOCATION SUCCESSFUL

The number of successful plan allocation attempts.

The cause of plan allocation failure could be plan unavailability or attempting to allocate a nonexistent plan.

Field Name: QTALLOC

PACKAGE ALLOCATION ATTEMPT

The number of attempts to allocate a package.

Field Name: QTPKALLA

PACKAGE ALLOCATION SUCCESS

The number of successful package allocation attempts.

Background and Tuning Information

Package allocation failure can occur when a package is unavailable or does not exist.

A high count of the number of packages unsuccessfully allocated (QTPKALLA - QTPKALL) typically occurs when a package list with multiple collections is used and frequently-used packages are found in the back end rather than in the front end of a package list. For example, when a package is found in the tenth collection, QTPKALLA is incremented by 10, one for each collection searched, but QTPKALL is incremented by 1.

A high number of packages unsuccessfully allocated can be accompanied by a high count of the number of unsuccessful checks for package execute authority made using the package authorization check because an application entry was not found in the cache (QTPACNOT). In this case, placing frequently used packages in the front end of a package list would reduce the number of Buffer Manager Getpages to the catalog/directory tablespaces.

Field Name: QTPKALL

PLANS BOUND

The number of plans successfully bound and kept for future agent allocations.

This field represents the sum of successful BIND ADD (QTBINDA) and successful BIND REPLACE (QTBINDR) commands. This counter is not incremented for BIND subcommands with no plan ID specified, as identified by QTTESTB. Note that QTBINDA + QTBINDR is not necessarily equal to this field. It is equal only if all BIND ADD and BIND REPLACE subcommands issued are successful.

Field Name: QTPLNBD

BIND ADD SUBCOMMANDS

The number of successful and unsuccessful BIND ADD subcommands issued.

The sum of QTBINDA, QTBINDR, and QTTESTB equals the total number of BIND subcommands.

Field Name: QTBINDA

BIND REPLACE SUBCOMMANDS

The number of successful and unsuccessful BIND REPLACE subcommands issued.

Field Name: QTBINDR

TEST BINDS NO PLAN-ID

The number of BIND subcommands issued without a plan ID.

Field Name: QTTESTB

PACKAGES BOUND

The number of packages bound and kept for future package allocations.

It is the sum of successful BIND ADD PACKAGE and BIND REPLACE PACKAGE subcommands, but only if all these commands are really issued successfully.

Field Name: QTPKGBD

BIND ADD PACKAGE SUBCOMMAND

The number of successful and unsuccessful BIND ADD PACKAGE subcommands issued.

Field Name: QTBINDPA

BIND REPLACE PACKAGE SUBCOM

The number of successful and unsuccessful BIND REPLACE PACKAGE subcommands issued.

Field Name: QTBINDPR

AUTOMATIC BIND ATTEMPTS

The number of attempts to autobind a plan. This occurs when the plan was invalidated by modifications to the declarations of the data referenced by the programs bound as part of the plan. For example, dropping an index when it is used in the plan results in automatic bind.

Field Name: QTABINDA

AUTOMATIC BINDS SUCCESSFUL

The number of plans successfully autobound.

Field Name: QTABIND

AUTO.BIND INVALID RES. IDS

The number of requests to allocate a nonexistent plan or package. This is the number of all plan and package allocation attempts that failed because the resource was unavailable or the object did not exist.

Field Name: QTINVRID

AUTO.BIND PACKAGE ATTEMPTS

The number of attempts to autobind a package.

Background and Tuning Information

If YES was specified, or defaulted, for autobind on Db2 install panel DSNTIPB, an autobind occurs when a plan or package:

Is invalid because declarations of the data referenced by the program or package were modified. For example, when an index used in a package is dropped, an automatic bind occurs when the package is run for the first time after the index was dropped.
Was bound in a later release and is used in a previous release for the first time.
Was used in a previous release but is later remigrated and used in a later release for the first time.

Field Name: QTAUTOBA

This is an exception field.

AUTO.BIND PACKAGES SUCCESS

The number of packages successfully autobound.

Field Name: QTPKABND

This is an exception field.

AUTO.BIND PHASE-IN ATTEMPTS

The number of autobind phase-in attempts.

Field Name: QTAPATTM

AUTO.BIND PHASE-IN QUEUED

The number of autobind phase-in requests in the service task queue.

Field Name: QTAPQUED

AUTO.BIND PHASE-IN SUCCESS

The number of successful autobind phase-in processes.

Field Name: QTAPSUCC

REBIND SUBCOMMANDS

The number of REBIND subcommands issued. More than one plan can be rebound with a single REBIND subcommand. If the value in this field is 1, the number of plans you are attempting to rebind is shown in the Rebind - plan attempts field.

Field Name: QTREBIND

ATTEMPTS TO REBIND A PLAN

The number of attempts to rebind a plan. This number can be larger than the value shown in the Rebind - plan subcommands field because you can specify more than one plan in a single REBIND subcommand.

Field Name: QTRBINDA

PLANS REBOUND

The number of rebind attempts that completed successfully. This field is equal to the Rebind - Plan attempts field if all specified plans rebound successfully.

Field Name: QTPLNRBD

REBIND PACKAGE SUBCOMMANDS

The number of REBIND PACKAGE subcommands issued. More than one package can be rebound with a single subcommand. If the value in this field is 1, Rebind - package attempts shows the number of packages you are attempting to rebind.

Field Name: QTRBINDP

ATTEMPTS TO REBIND PACKAGE

The number of attempts to rebind a package. This can be larger than the value shown in Rebind package subcommands because you can rebind more than one package with a single command.

Field Name: QTRBNDPA

PACKAGES REBOUND

The number of packages successfully rebound. If all specified packages were rebound successfully, this field is equal to Rebind package attempts.

Field Name: QTPKGRBD

FREE PLAN SUBCOMMANDS

The number of FREE subcommands issued.

More than one plan can be freed with a single FREE subcommand. If this field is 1, then the number of plans you are trying to free is shown in ATTEMPTS TO FREE A PLAN.

Field Name: QTFREE

ATTEMPTS TO FREE A PLAN

The number of attempts to free a plan.

This value can be larger than FREE PLAN SUBCOMMANDS because multiple plan IDs can be specified in a single FREE subcommand.

Field Name: QTFREEA

PLANS FREED

The number of times a plan was successfully freed.

Freeing a plan can fail if someone else is using the plan and holds a lock on it.

Field Name: QTPLNFRD

FREE PACKAGE SUBCOMMANDS

The number of FREE PACKAGE subcommands issued.

More than one package can be freed with a single FREE subcommand. If the value in this field is 1, then the number of packages you are attempting to free is shown in ATTEMPTS TO FREE A PACKAGE.

Field Name: QTFREEP

ATTEMPTS TO FREE A PACKAGE

The number of attempts to free a package. This number can be larger than FREE PACKAGE SUBCOMMANDS because you can free several packages with a single command.

Field Name: QTFREEAP

PACKAGES FREED

The number of times a package was successfully freed. If all the specified packages were freed successfully, the value of this field is equal to ATTEMPTS TO FREE A PACKAGE.

Field Name: QTPKGFRD