Subsystem services (BSTATDR)

IDENTIFY

Successful identify requests (Q3STIDEN)

This reports the number of threads that have gone through successful connection processing to the DB2 being observed. Connections can be from any supported environment, such as TSO, IMS, CICS, CAF (call attach), or a utility.

Tuning Tip: Watch for peak time spike periods. The DB2 DSNZPARM parameters CTHREAD, IDFORE, IDBACK, and other distributed parameters should be adjusted so the system can respond to the maximum load expected.

CREATE THREAD

Number of create thread requests (Q3STCTHD)

This reflects the number of threads created (not including distributed database access threads). A thread is created at first execution of an SQL statement (assuming that no

pre-existing thread for the same authorization ID and plan name exists).

SIGNON

Successful signon events (Q3STSIGN)

This field has meaning only when applied to CICS and IMS. It represents the number of times an existing thread has gone through new user signon reusing an existing thread.

Tuning Tip: The cost of thread creation can be quite considerable in CICS transactions and IMS WFI BMP transactions. In general, it is desirable to reuse an existing thread rather than suffer the destruction and recreation of a new thread. High numbers generally indicate good thread reuse. Tuning factors which can impact this are GRANTing plans to PUBLIC (avoiding an authorization check) or utilizing the authorization cache by specifying a large enough CACHESIZE at bind time to keep as many user IDs in the EDM pool as possible.

TERMINATE

Successful terminate thread requests (Q3STTERM)

This number reflects thread termination as a result of a program releasing resources or of a thread dropping its access level from thread active back to signon and back to identify status. This count will be higher than the create thread count.

Tuning Tip: In online transactions, watch for thread creations and terminations for similar transactions. Thread reuse may be achieved in CICS by raising the THRDS count in the CICS resource control table; in an IMS environment, a Wait-for-Input WFI) BMP can be a useful method of avoiding costly thread creations and terminations.

ROLLBACK

Number of successful rollbacks (Q3STABRT)

In the DB2 Statistics Overview Report--averages, this value is the average number of aborts per CREATE THREAD during this statistics interval. This is a count of the number of rollbacks taken by the system to back out a unit of recovery. Rollbacks occur because of a program abend, application rollback request, deadlock or timeout situation, a -CANCEL THREAD command, or some resource shortage. If it is a resource shortage, a -904 return code message is displayed on the MSTR job log.

COMMIT PHASE 1

Successful prepare to commits (Q3STPREP)

This number is a count of the number of prepare to commit requests for a two-phase commit unit of work, which includes CICS update and IMS transactions. The prepare to commit is the result of the end of phase 1 which causes log records to be externalized. This counter is appropriate only for two-phase commit operations.

Tuning Tip: The use of this field is to determine how much forced logging is occurring as a result of end of phase 1 processing. The number of prepares for phase 1 minus the successful commit phase 2 requests is the indoubt count. However, because some phase 2 operations are not completed in the same time interval, the number of prepares for phase 1 does not always agree with successful phase 2 requests.

COMMIT PHASE 2

Successful phase 2 requests (Q3STCOMM)

In the DB2 Statistics Overview Report--averages and the DB2 Statistics SQL Report, this value is the average number of phase 2 commits per CREATE THREAD during this statistics interval. This number is a counter of successful phase 2 commits from IMS or CICS transactions. This field is not incremented for distributed two-phase commits or single unit of work tasks (for example, TSO, batch).

READ-ONLY COMMIT

Number of read-only commits (Q3STRDON)

This situation occurs only in CICS and IMS when execution of a program has not updated a DB2 resource. When this occurs DB2 simply increments the read-only counter, performs both phases of the two-phase commit process and records that the job was read-only. This count does not include CICS synchronous commits.

Tuning Tip: This information is useful in determining the read/write ratio of a CICS or IMS system.

UNIT REC INDOUBT

Total number of indoubt units of recovery (Q3STINDT)

This number is a count of indoubt threads, caused when a failure occurs after a successful prepare but before a successful commit. The failure can occur in the address space of the application, the transaction manager, DB2, or a distributed requester/server.

Tuning Tip: Most indoubt situations are resolved automatically when the participants are restarted. These are counted in successful indoubt resolutions (Q3STRIUR). However, in some cases, such as an operator cold starting one participant, manual resolution might be required.

UNIT REC INBT RSLVD

Successful indoubt resolutions (Q3STRIUR)

This number is a count of successful indoubt thread resolutions. Indoubt situations arise when a failure occurs after a successful prepare but before a successful commit. The status of retained locks against resources cannot be resolved until the coordinator and all participants have been recovered/restarted. This resolution usually occurs automatically, as reflected in this count.

Tuning Tip: This field applies only to CICS and IMS transactions participating in two-phase commit or in distributed processing using DRDA level 2 processing. In some cases, such as an operator cold starting one participant, manual resolution may be required. Another counter shows the total number of indoubt threads (Q3STINDT).

SYNCHS SINGLE PHASE

Number of successful single phase commits (Q3STSYNC)

In the DB2 Statistics Overview Report--averages, this value is the average number of synchronized commit requests per CREATE THREAD during this statistics interval. This is a count of all synchronous commits issued by TSO, batch, CAF, and utility programs. CICS applications use both synchronous commits and two-phase commits. IMS uses only two-phase commits.

Q’D CREATE THREAD

Number of create thread requests queued (Q3STCTHW)

This field is a counter of how many times the maximum thread count (CTHREAD in DSNZPARM) was reached and a user had to wait to acquire an available thread. It does not include DBATs.

Tuning Tip: The general rule is to keep CTHREAD high enough to avoid thread queuing. However, if memory resources are tight (and the users are willing to live with the pain), you can limit CTHREAD to an arbitrary number. This limit controls memory allocation for thread creation at the expense of user wait time and some overhead in keeping track of the queued threads for scheduling.

SUBSYSTEM MEMORY EOT

Abnormal allied memory end of task (Q3STMEOT)

This field is a counter of those tasks which have abended while connected to DB2. The counts reflected should equal the number of Abnormal EOT messages in the MSTR job log.

Tuning Tip: This field is useful in determining how many abends occurred.

SUBSYSTEM MEMORY EOM

Abnormal allied memory end of memory (Q3STMEOM)

This counter reflects program abends due to end of memory situations (such as an MVS FORCE command or lack of region to successfully abend).

Tuning Tip: Nonzero numbers in this counter should be investigated to determine why FORCE commands were issued or an X78 abend occurred.

SYSTEM EVENT CHKPT

DB2 checkpoint count since startup

HIGH WATER MARK IDBACK

Maximum number of connections to a single instance from batch or TSO background tasks (Q3STHWIB)

HIGH WATER MARK IDFORE

Maximum number of connections to a single instance from TSO foreground tasks (Q3STHWIF)

HIGH WATER MARK CTHREAD

Highest number of batch CICS, IMS, and TSO tasks (CTHREAD) to a single instance (Q3STHWCT)