Monitoring Tasks

This section summarizes the monitoring tasks you can perform with SQLMON, giving the appropriate SQLMON screens and fields for each task. The tasks fall into the following categories:

disk usage
memory usage
tables
hash structures
indexes and referential constraints
transactions
sessions
I/O
logging
locking

For example, if you are interested in checking the size of a DBEFile, look at the tasks listed in Table 6-4 “Monitoring Disk Usage”. To perform the task, go to the screen listed in the Screens column and the fields listed in the Fields column. If you need more information on a screen's fields, see the corresponding section in the "SQLMON Screen Reference" chapter.

Table 6-4 Monitoring Disk Usage

Task	Screens	Fields
Determining log file capacity	Overview	LOG FULL % Used LgPgs Max LgPgs
Determining DBEFile capacity in a DBEFileSet	Static DBEFile	DBEFILE DBEFILE FULLNESS % USED PAGES MAX PAGES
Identifying DBEFile storage restrictions	Static DBEFile	DBEFILE TYP BD
Determining DBEFileSet capacity	Static DBEFile	DBEFILESET DBEFILESET FULLNESS % FSUSED PAGES FSMAX PAGES
Determining the size of database objects in a DBEFileSet	Static Size	DBEFILESET OWNER.TABLE TABLE PAGES INDEX PAGES TOTAL PAGES
Identifying detached DBEFiles	Static DBEFile	DBEFILESET FULLNESS DBEFILE FULLNESS

Table 6-5 Monitoring Memory Usage

Task	Screens	Fields
Identifying the size of the data buffer pool	IO	TOTAL DATA BUFFER PAGES
Identifying the size of the log buffer pool	IO	TOTAL LOG BUFFER PAGES
Determining the size of the runtime control block	Overview	RUNTIME CB % Used Pages Max Pages
Resolving memory problems	Lock Memory Lock TabSummary	all fields (hint: type HELP LOCK MEMORY TUNE 4 for more information)

Table 6-6 Monitoring Tables

Task	Screens	Fields
Identifying tables in a DBEFileSet	Static	DBEFILESET OWNER.TABLE
Identifying tables stored in TurboIMAGE data sets	Static	OWNER.TABLE IMAGE
Determining a table type	Static	OWNER.TABLE TYPE
Determining the number of indexes and referential constraints on a table	Static	OWNER.TABLE NUM INDEXES
Determining the size of a table	Static Size Static Cluster	OWNER.TABLE TABLE PAGES
Determining the number of rows in a table	Static Indirect Static Cluster	OWNER.TABLE TOTAL ROWS
Determining the percentage of indirect rows in a table	Static Indirect	OWNER.TABLE TABLE INDIRECT ROW %
Comparing the number of locks by table	Lock TabSummary	OWNER.TABLE G TOTAL LOCKS
Identifying the locks on a table	Lock	OWNER.TABLE G PAGE/ROW ID LOCK QUEUE (hint: use SET LOCKTABFILTER)
Monitoring table I/O	SampleIO Tables SampleIO TabIndex	OWNER.TABLE SWAPIN SWAPOUT TOTALIO
Identifying the tables currently residing in the data buffer pool	SampleIO Objects	OWNER.TABLE CURRENT PGS

Table 6-7 Monitoring Hash Structures

Task	Screens	Fields
Identifying hashed tables in a DBEFileSet	Static	DBEFILESET OWNER.TABLE HASH
Identifying hashed tables in a DBEFileSet	Static Hash	DBEFILESET OWNER.TABLE> (hint: only hashed tables are displayed)
Monitoring primary pages	Static Hash	PRIMPAGES PRIMDATA PRIMOVERF
Monitoring overflow pages	Static Hash	OVERPAGES OVERFLOW CHAIN LNGTH MAXOVERFLOW AVGOVERFLOW
Identifying DBEFiles that are bound to hashed tables	Static DBEFile	DBEFILE BD

Table 6-8 Monitoring Indexes and Referential Constraints

Task	Screens	Fields
Identifying the indexes and referential constraints on a table or in a DBEFileSet	Static Size Static Cluster	DBEFILESET OWNER.TABLE
Determining the number of indexes and referential constraints on a table or in a DBEFileSet	Static	DBEFILESET OWNER.TABLE NUM INDEXES
Determining the size of an index or a referential constraint	Static Size	OWNER.TABLE INDEX PAGES
Identifying locks on a referential constraint	Lock	OWNER.TABLE/CONSTRAINT G PAGE/ROW ID LOCK QUEUE (use SET LOCKTABFILTER)
Monitoring index and referential constraint I/O	SampleIO Indexes SampleIO TabIndex	OWNER.TABLE[/INDEX,CONSTRAINT] SWAPIN SWAPOUT TOTALIO
Identifying the indexes and referential constraints currently residing in the data buffer pool	SampleIO Objects	OWNER.TABLE[/INDEX,CONSTRAINT] CURRENT PGS
Determining the efficiency of index scan over an index or referential constraint	Static Cluster	CCOUNT UNLOAD/LOAD SUGGESTED %

Table 6-9 Monitoring Transactions

Task	Screens	Fields
Determining the transaction identifier	Overview Program Overview Session Lock Session Lock Object Lock Impede Lock TabSummary	XID
Determining the isolation level	Overview Program Overview Session Lock Session Lock Object Lock Impede Lock TabSummary	ISO
Determining the transaction priority	Overview Program Overview Session Lock Session Lock Object Lock Impede Lock TabSummary	PRI
Determining the transaction label	Overview Program Overview Session Lock Session Lock Object Lock Impede Lock TabSummary	LABEL
Determining the maximum number of active transactions	Overview Load	MAX XACT
Determining the current number of active transactions	Overview Load	ACTIVE XACT
Determining the number of active transactions waiting for a lock	Overview Load	IMPEDE XACT
Determining the number of transactions waiting for a transaction slot	Load	THROTTLE WT
Monitoring throughput	Load Load Session Load Program	BEGIN WORK COMMIT WORK ROLLBK WORK DEADLOCK

Table 6-10 Monitoring Sessions

Task	Screens	Fields
Determining the number of sessions	Overview Overview Session Overview Program IO IO Data Session IO Data Program IO Log Session IO Log Program Load Load Session Load Program	SESSIONS
Determining the process identifier of a session	Overview Session	PID LOGIN NAME PROGRAM NAME
Determining the transaction information of a session	Overview Session Lock Session Lock Impede Lock TabSummary	XID ISO PRI LABEL
Identifying the program being run by a session	Overview Program Lock Session Lock Impede Lock TabSummary	PID LOGIN NAME PROGRAM NAME
Identifying the sessions running a particular program	Overview Program	PID LOGIN NAME PROGRAM NAME
Identifying all waiting sessions	Overview Session Overview Program	PID LOGIN NAME STATUS
Monitoring session lock activity	Lock Session	All fields
Monitoring session data buffer I/O	IO Data Session IO Data Program	All fields
Monitoring session log buffer I/O	IO Log Session IO Log Program	All fields
Comparing session throughput	Load Load Session Load Program	BEGIN WORK COMMIT WORK ROLLBK WORK DEADLOCKS

Table 6-11 Monitoring I/O for Data

Task	Screens	Fields
Identifying the size of the data buffer pool	IO	TOTAL DATA BUFFER PAGES
Monitoring data buffer I/O	IO IO Data Session IO Data Program	BUFF ACCESS DATA DISK RD DATA DISK WR MISS RATE
Monitoring DBEFile I/O	SampleIO	DBEFILE SWAPIN SWAPOUT TOTALIO
Monitoring table I/O	SampleIO Tables SampleIO TabIndex	OWNER.TABLE SWAPIN SWAPOUT TOTALIO
Monitoring index and referential constraint I/O	SampleIO Indexes SampleIO TabIndex	INDEX,CONSTRAINT SWAPIN SWAPOUT TOTALIO
Determining the number of data buffer pages occupied by an object	SampleIO Object	CURRENT PGS
Determining the percentage of indirect rows in a table	Static Indirect	OWNER.TABLE TABLE INDIRECT ROW %
Determining the efficiency of an index scan over an index or referential constraint	Static Cluster	CCOUNT UNLOAD/LOAD SUGGESTED %

Table 6-12 Monitoring I/O for Logging

Task

Screens

Fields

Identifying the size of the log buffer pool

TOTAL LOG BUFFER PAGES

Monitoring log buffer I/O

IO Log Session

IO Log Program

LOG BUFF WR

LOG DISK RD

LOG DISK WR

Monitoring checkpoints

CHECKPOINTS

Table 6-13 Additional Monitoring for Logging

Task

Screens

Fields

Determining log file capacity

Overview

LOG FULL %

Used LgPgs

Max LgPgs

Identifying log mode

Overview

Archive Mode

Detecting logging errors

Overview

LOG ERRORS

Table 6-14 Monitoring Locking

Task	Screens	Fields
Determining the size of a runtime control block	Overview	RUNTIME CB % Used Pages Max Pages
Monitoring DBEnvironment lock activity	Load	LOCK REQTS LOCK WAITS LOCK WAIT %
Comparing the number of locks by table	Lock TabSummary	OWNER.TABLE G TOTAL LOCKS
Comparing the number of locks by session	Lock Memory	TABLE PAGE ROW TOTAL MAXTOTAL
Identifying locks on a table or referential constraint	Lock	OWNER.TABLE[/CONSTRAINT] G PAGE/ROW ID LOCK QUEUE (hint: use SET LOCKTABFILTER)
Determining the number of sessions that are accessing a particular lock	Lock	LOCK QUEUE
Determining the number of transactions that are waiting for locks	Overview Load	IMPEDE XACT
Determining the isolation level of a transaction	Overview Program Overview Session Lock Impede Lock Object Lock TabSummary Lock Session	XID ISO PRI LABEL
Identifying locks for which sessions are waiting	Lock	all fields (hint: use SET LOCKFILTER)
Identifying sessions that have obtained a particular lock	Lock Object	GWC MOD PID
Identifying sessions that are waiting to obtain (or to convert) a particular lock	Lock Object	GWC MOD NEW PID
Identifying lock activity for a particular session	Lock Session	all fields
Identifying locks obtained by a particular session that are causing other sessions to wait	Lock Impede	all fields
Detecting deadlocks	Load Load Session Load Program	DEADLOCKS
Resolving deadlocks	Lock Lock Object Lock Impede	all fields (hint: type HELP LOCK TUNE 13 for more information)

Monitoring Tasks

Technical documentation

» Table of Contents

» Index