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:
indexes and referential constraints
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 | IO | TOTAL LOG BUFFER PAGES |
| Monitoring log buffer I/O | IO IO Log Session IO Log Program | LOG BUFF WR LOG DISK RD LOG DISK WR |
| Monitoring checkpoints | IO | 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 IO | 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) |
