The file definitions that the application server uses to point to the directory, log, and dbextent disks appear in the start up message stream. For example:
+--------------------------------------------------------------------------------+ |sqlstart DB(SQLMACH1) | |Ready; T=0.03/0.05 14:22:40 | |ARI0717I Start SQLSTART EXEC: 09/15/99 14:22:40 EDT. | |ARI0663I FILEDEFS in effect are: | |ARISQLLD DISK ARISQLLD LOADLIB Q1 | |BDISK DISK 300 | |LOGDSK1 DISK 301 | |LOGDSK2 DISK 302 | |DDSK1 DISK 303 | |DDSK2 DISK 304 | |DDSK3 DISK 305 | |DDSK4 DISK 306 | |
·
·
·
| +--------------------------------------------------------------------------------+
This database has its directory disk at virtual address 300, its log disks at 301 and 302, and its dbextents from 303 to 306. The physical minidisk locations are defined in the VM Directory. To find out the DASD type, volume identifier, and size of each disk, type: #CP Q V DASD from the operator console. For example:
+--------------------------------------------------------------------------------+ |#cp q v dasd | |
·
·
·
| |DASD 0300 3390 PA326B R/W 6 CYL ON DASD 168B | |DASD 0301 3390 PA3268 R/W 3 CYL ON DASD 1688 | |DASD 0302 3390 PA3268 R/W 3 CYL ON DASD 1688 | |DASD 0303 3390 PA326A R/W 5 CYL ON DASD 168A | |DASD 0304 3390 PA326A R/W 5 CYL ON DASD 168A | |DASD 0305 3390 PA326A R/W 2 CYL ON DASD 168A | |DASD 0306 3390 PA3269 R/W 2 CYL ON DASD 1689 | |
·
·
·
| +--------------------------------------------------------------------------------+
In DB2 Server for VSE, dbextents are defined as VSAM datasets. To find out their dataset names, look in the database identification procedure for your server (shipped as an example procedure ARIS41DB), which is executed just before the ARISQLDS start up job step in the start up job stream. (Procedure ARIS41DB is only an example. The database identification procedure for your server may have a different name and point to different disks.)
+--------------------------------------------------------------------------------+ |// DLBL BDISK,'SQL.BDISK.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL LOGDSK1,'SQL.LOGDSK1.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL LOGDSK2,'SQL.LOGDSK2.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL DDSK1,'SQL.DDSK1.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL DDSK2,'SQL.DDSK2.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL DDSK3,'SQL.DDSK3.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL DDSK4,'SQL.DDSK4.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL DDSK5,'SQL.DDSK5.DBASE.DB',,VSAM,CAT=SQLCAT | |// DLBL DDSK6,'SQL.DDSK6.DBASE.DB',,VSAM,CAT=SQLCAT | +--------------------------------------------------------------------------------+
You can find the size and location of the datasets either by using the Access Method Services (IDCAMS) utility (part of VSAM LISTCAT), or through the VSE interactive interface. Both are documented in the DB2 Server for VSE & VM Operation manual.
When you initialize the application server, important information is presented on the operator console:
+--------------------------------------------------------------------------------+ |sqlstart DB(SQLMACH1) | |
·
·
·
| |ARI0020I Virtual machine addressing mode = 31 | | Virtual machine storage size = 24576KB | |
·
·
·
| |ARI0015I SYNCPNT parameter value is Y. | |
·
·
·
| |ARI0016I TRACEBUF parameter value is 0. | |ARI0016I CHKINTVL parameter value is 150. | |ARI0016I NCSCANS parameter value is 30. | |ARI0016I NCUSERS parameter value is 5. | |ARI0016I NDIRBUF parameter value is 100. | |ARI0016I NLRBS parameter value is 2520. | |ARI0016I NLRBU parameter value is 1000. | |ARI0016I NPACKAGE parameter value is 10. | |ARI0016I NPACKPCT parameter value is 30. | |ARI0016I NPAGBUF parameter value is 100. | |ARI0016I SLOGCUSH parameter value is 90. | |ARI0016I SOSLEVEL parameter value is 10. | |ARI0016I DISPBIAS parameter value is 7. | |ARI0016I LTIMEOUT parameter value is 0. | |ARI0283I Log analysis is complete. | |ARI0282I LUW UNDO is completed. | |ARI0281I LUW REDO is completed. | |ARI0143I The application server has been initialized | | with the following values: | | CHARNAME = INTERNATIONAL, DBCS = NO, CHARSUB = SBCS, | | CCSIDSBCS = 500, CCSIDMIXED = 0, CCSIDGRAPHIC = 0. | |ARI0060I Database manager initialization complete. | |ARI0045I Ready for operator communications. | +--------------------------------------------------------------------------------+
You can find the value of some of these parameters from the console log, or from the start up options file in VM, or from an options member that is specified in the PARM list of the start up EXEC statement. You can also use the 'SHOW INITPARM' operator command to display most of the parameters when the server is running in multiple user mode. For more information, refer to the DB2 Server for VSE & VM Operation manual.
CIRD is a DB2 Server for VSE-supplied transaction, that lets you display a snapshot of the links between CICS and the application server. While it does not provide historical information, it can help you diagnose problems with individual transactions or get an immediate feel for the level of link contention between CICS and the server.
It contains the following information:
For example:
+--------------------------------------------------------------------------------+ |DBDCCICS CONNECTED TO SERVER SQLDB1_NEWYORK_INV. | |STATUS OF ONLINE DB2 FOR VSE APPLICATIONS: | | | |TRANSACTIONS WAITING TO ESTABLISH A LINK TO THE APPLICATION | |SERVER ARE: | | TASKNO TRANID TERMID USERID USERDATA WAIT TIME | | ------ ------ ------ -------- -------- --------- | | 000033 MKE2 L222 00:01:32 | | 000025 INV L224 JIM 00:08:32 | | | |TRANSACTIONS HOLDING A LINK AND NOW ACCESSING THE APPLICATION | |SERVER ARE: | | TASKNO TRANID TERMID USERID USERDATA TIME USED TOTAL LUW | | FOR CURRENT TIME | | ACCESS | | ------ ------ ------ -------- -------- ------------ --------- | | 000019 CISQ DEPT222 L199 00:01:32 00:03:48 | | 000037 INV L209 TERRY 00:00:01 00:00:03 | | | |TRANSACTIONS HOLDING A LINK TO THE APPLICATION SERVER BUT NOT | |USING ARE: | | TASKNO TRANID TERMID USERID USERDATA TIME SINCE TOTAL LUW | | LAST ACCESS TIME | | ------ ------ ------ -------- -------- ------------ --------- | | 000003 CISQ WILLIAM L210 00:07:01 00:10:56 | | | |TRANSACTIONS WHICH PREVIOUSLY ACCESSED THE APPLICATION SERVER | |(NOT HOLDING LINK): | | TASKNO TRANID TERMID USERID USERDATA TIME SINCE | | LAST ACCESS | | ------ ------ ------ -------- -------- ------------ | | 000003 MKE2 ROBERT L210 00:20:04 | | | |TIME=14:28:23 DATE=04/30/99 | +--------------------------------------------------------------------------------+
For information on how to display CIRD transaction information, and detailed information on how to use and interpret CIRD display information, refer to the DB2 Server for VSE System Administration manual.
While a detailed description of this operator command is included in the DB2 Server for VSE & VM Operation manual, this section includes an example of how to:
After resetting the counters (with the RESET operator command) and performing several queries, the COUNTER * command was issued:
+--------------------------------------------------------------------------------+ |reset * | |Counters reset at DATE='09-06-99' TIME='14:27:00' | |ARI0065I Operator command processing is complete. | |counter * | |Counter values at DATE='09-06-99' TIME='14:58:12' | | Calls to RDS RDSCALL : 68 | | Calls to DBSS DBSSCALL: 139 | | LUWs started BEGINLUW: 58 | | LUWs rolled back ROLLBACK: 11 | | System checkpoints taken CHKPOINT: 1 | | Maximum locks exceeded LOCKLMT : 0 | | Lock escalations ESCALATE: 0 | | Waits for lock WAITLOCK: 4 | | Deadlocks detected DEADLCK : 1 | | Looks in page buffer LPAGBUFF: 298722 | | DBSPACE page reads PAGEREAD: 200134 | | DBSPACE page writes PAGWRITE: 97451 | | Looks in directory buffer LDIRBUFF: 5054 | | Directory block reads DIRREAD : 4014 | | Directory block writes DIRWRITE: 120 | | Log page reads LOGREAD : 2 | | Log page writes LOGWRITE: 40 | | Total DASD reads DASDREAD: 4524 | | Total DASD writes DASDWRIT: 49 | | Total DASD I/O DASDIO : 3986 | | Lock timeouts detected LTIMEOUT: 2 | | ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
There are several important values that you can calculate from the COUNTER command:
This information can also be expressed as the deadlock hit ratio (BEGINLUW/DEADLCK). Any value over 20 is usually acceptable.
| Note: | While this value gives you an indication of how often an agent waits, it does not indicate the length of each wait. Always listen to your users and look for agents that are stuck in a lock wait with the operator SHOW LOCK commands, refer to Locking Contention. |
This information can also be expressed as the waitlock hit ratio (RDSCALL/WAITLOCK). Any value over 10 is usually acceptable.
This information can also be expressed as the local buffers hit ratio (LPAGBUFF/PAGEREAD).
This information can also be expressed as the directory buffers hit ratio (LDIRBUFF/DIRREAD).
For example, from the previous output:
DB2 VM Data Spaces Support also includes additional counters that can help you monitor the performance of the DASD I/O systems. Each storage pool has its own set of four counters. There is also a set for internal dbspaces and a set for the directory. For a complete description of all these counters and how to use them, refer to the DB2 Server for VSE & VM Operation manual.
The SHOW commands, documented in the DB2 Server for VSE & VM Operation manual, provide information on how your application server is performing. This section includes examples of how to use these commands to understand how the server is managing: storage, concurrency, locking.
The SHOW DBEXTENT command displays physical storage information about each storage pool defined. For example, consider a database with an SOSLEVEL of 10% (refer to Short on Storage Cushion):
+--------------------------------------------------------------------------------+ |show dbextent | |POOL TOTAL NO. OF NO. OF NO. OF % NO. OF | | NO. PAGES PAGES USED FREE PAGES RESV PAGES USED EXTENTS SOS | | 1 741 730 11 20 99 1 * | | 2 171 11 160 20 6 1 | | 3 114 56 58 20 49 1 | | 4 114 0 114 20 0 1 | |FREE 22500 | +--------------------------------------------------------------------------------+
The asterisk (*) under the SOS column indicates that storage pool number one is short on storage. The flag is set if the difference between the NO. OF PAGES USED and the TOTAL PAGES is less than the SOSLEVEL percentage times the TOTAL PAGES. In this case, pool one has 99% (730/741 X 100) of its pages used or 1% or its pages are free. Because this is less than the 10% SOSLEVEL, the flag is set.
| Note: | The NO. OF PAGES USED includes the number of shadow pages in the pool. |
The SHOW DBSPACE command shows the division of pages between header, data, and index pages in a dbspace. For example:
+--------------------------------------------------------------------------------+ | | |show dbspace 3 | | TYPE NUMBER NUMBER OF % FREE NUMBER OF | |OF PAGES OF PAGES OCCUPIED PAGES SPACE EMPTY PAGES | | HEADER 8 1 ( 12 %) 73 % 0 | | DATA 1365 756 ( 55 %) 25 % 392 | | INDEX 128 27 ( 27 %) 79 % 18 | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
(The SYSTEM.SYSDBSPACES catalog table contains additional information on dbspaces, refer to page ***.)
This example shows more than half of the data page space is occupied. It also shows a large number of empty data pages, which indicates that you may need to drop and reacquire (reorganize) this dbspace. For more information, refer to Running out of Dbspace Pages.
| Note: | This command performs a dbspace scan, which can take a significant amount of time and affect the performance of other users if the dbspace is large. Refer to Dbspace Scans. |
The SHOW POOL command displays physical storage information about each dbextent in a storage pool.
For example, consider a database with two storage pools and an SOSLEVEL of 10% (refer to Short on Storage Cushion ):
+--------------------------------------------------------------------------------+ |show pool | | | |POOL NO. 1: NUMBER OF EXTENTS = 3 | | | |EXTENT TOTAL NO. OF NO. OF NO. OF % | |NO. PAGES PAGES USED FREE PAGES RESV PAGES USED | | 1 285 274 11 96 | | 2 285 33 252 11 | | 3 741 0 741 0 | |TOTAL 1311 307 1004 20 23 | | | |POOL NO. 2: NUMBER OF EXTENTS = 1 SHORT ON STORAGE | | | |EXTENT TOTAL NO. OF NO. OF NO. OF % | |NO. PAGES PAGES USED FREE PAGES RESV PAGES USED | | 4 285 260 25 91 | |TOTAL 285 260 25 20 91 | | | | | |FREE AREAS: NUMBER OF DELETED EXTENTS = 0 | | | |EXTENT TOTAL | | NO. PAGES | | END 10350 | |TOTAL 10350 | | | |Maximum number of DBEXTENTs = 64 | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
This example shows that pool number two is short on storage (SOS). While dbextent number one has less storage available than dbextent number four, the pool it belongs to (pool one) is not short of storage because it still contains two dbextents, each containing a significant amount of storage.
The SHOW STORAGE command displays how much of the database machine or partition's virtual address space has been allocated to various storage queues, refer to Storage Queues. Two storage queues are created for:
Storage that has been allocated to control blocks and programming structures that must reside below the 16MB line are indicated by a B in the LOC column. Storage that may be allocated anywhere above or below the line is indicated by an A in the LOC column.
The SHOW STORAGE command displays how much of the database machine or partition's address space has been ALLOCATED to each queue. It also displays how much of it is actually IN USE and how much is FREE. "Free space" is space that has been reserved by the queue but is not currently in use.
Space allocated to a specific real agent queue (including "free" space) is released at the end of a logical unit of work (LUW). (A minimum amount of space, 8KB, always remains allocated for each real agent queue.)
The HIGHSTOR column contains the maximum amount of storage allocated to a queue since the RESET HIGHSTOR operator command was last issued. One high storage entry exists for storage that is restricted to below the 16MB line and one exists for storage that can reside either above or below the line.
A USERID column is also included for each real agent. It indicates the user ID that held the real agent when the A, or B high storage value was set.
SUMMARY contains the total amount of virtual storage allocated to all the QUEUES. It also contains the total amount of virtual storage allocated to the entire database manager SYSTEM. These values include the storage allocated to the queues plus the storage used by the database manager itself for structures such as trace buffers.
+--------------------------------------------------------------------------------+ |show storage | |Status of Storage at DATE='01-15-99' TIME='11:57:28' | | | |AGENT LOC ALLOCATED IN USE FREE HIGHSTOR USERID | |OPERATOR A 4096 1104 2992 4096 | | B 4096 0 4096 4096 | |CHECKPT A 2048 0 2048 2048 | | B 2048 0 2048 2048 | |RECOVERY A 4096 0 4096 4096 | | B 4096 0 4096 4096 | |1 A 16384 1952 14432 144816 MARISSA | | B 8192 0 8192 8192 | |2 A 72776 67360 5416 136472 LAURA | | B 8192 0 8192 8192 | |3 A 8192 2912 5280 161200 ANDREW | | B 8192 0 8192 8192 | |4 A 16384 3936 12448 185016 DAVID | | | | B 8192 0 8192 8192 | |5 A 16384 2992 13392 241600 DANIEL | | B 8192 0 8192 8192 | |PROTOTYPE A 1154768 1053240 101528 1218784 | | B 307232 299136 8096 307232 | |SUMMARY | |QUEUES A 1295128 1133496 161632 1702992 | | B 358432 299136 59296 358432 | |PACKAGES A 245760 201000 44760 270048 | |SYSTEM A 1295952 1703816 | | B 388936 388936 | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
The SHOW ACTIVE command displays the status of active real agent structures. Agents are used by the database manager to divide processor time between multiple users and its own internal tasks, such as checkpoint processing and operator commands. For more information, refer to Agents.
Use this command to:
For example:
+--------------------------------------------------------------------------------+ |show active | |Status of agents: | | Checkpoint agent is not active. | | User Agent: 1 User ID: SMITH is R/W APPL 7B4 | | Agent is processing and is in I/O Wait. | | User Agent: 2 User ID: MICHAEL is R/O SUBS 7B9 | | Agent is not processing and is in communication Wait. | | User Agent: 3 User ID: JESSICA IS R/O APPL 5A4 | | Agent is processing an operator command. | | User Agent: 4 User ID: TESTUSER IS R/W APPL 7BB | | Agent is processing and is in I/O Wait. | | User Agent: 5 User ID: MACNIELL IS R/O APPL 7B9 | | Agent is not processing and is in communication Wait. | | 0 agent(s) not connected to an APPL or SUBSYS. | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
This display shows that there are five real agents available and they are all currently being used (0 agent(s) not connected). It also shows that agents one, two, four, and five are all processing work but are waiting for either communications or an I/O operation. Agent three is currently processing an operator command.
The SHOW USERS command (VM systems only) displays the status of both pseudo and real agent structures. (For information on these structures, refer to Agents.) You can use it to see how many, if any, pseudo agents are waiting for real agent structures. For example, consider a database machine that owns seven disks: four dbextents, one directory disk, and two log disks (do not include the service or the production disk):
+--------------------------------------------------------------------------------+ |show users | |Status of connected users: | | 6 users are connected to the application server. | | 3 Users are active. | | User ID: DAVE SQL ID: SMITH not processing | | User ID: POTTS SQL ID: BRIAN not processing | | User ID: TUNA SQL ID: FISH | | 2 Users are waiting. | | User ID: KIM SQL ID: TASK115 | | User ID: MICHAEL SQL ID: MIKE2 | | 1 Users are inactive. | | User ID: KOHLMANN SQL ID: PETER | | 0 Agents are available. | | 44 User connections are available. | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
This example shows that:
Not only does the SHOW CONNECT command display much of the information included with the SHOW ACTIVE and SHOW USERS operator commands, it also includes:
| For the VSE System |
|---|
|
The VSE SHOW CONNECT statement contains several additional values. See VSE SHOW CONNECT. You can use this information to force specific users to end their work and terminate their conversations with the server. Refer to the DB2 Server for VSE & VM Operation manual. |
+--------------------------------------------------------------------------------+ |show connect | |Status of Connected Users 1999-02-04 10:25:33 | | Checkpoint agent is not active. | | User Agent: 1 User-ID: SHUM SQL-ID: SHUM | | is R/W APPL 7B1 | | Agent is processing with LPAGEBUF=1032 | | State started: 1999-02-04 10:15:30 | | Conversation started: 1999-02-04 10:12:45 | | CPU time: 00:00:01 | | User Agent: 2 User-ID: SQLUSRSS SQL-ID: SQLUSRSS | | is R/O APPL 30BD | | Agent is not processing and is in communication wait. | | State started: 1999-02-04 09:48:28 | | Conversation started: 1999-02-04 09:48:00 | | CPU time: 00:00:02 | | LUWID: CAIBMOML.*IDENT.A532D460755B.0001 | | EXTNAM: SQLUSRSS.1 | | Requester: SQLDS/VM V3.3.0 at TORVMLB4 | | User Agent: 3 User-ID: PETERSON SQL-ID: PETERSON | | is R/O APPL 3758 | | Agent is processing and is in LOCK wait. | | State started: 1999-02-04 10:23:11 | | Conversation started: 1999-02-04 10:22:15 | | CPU time: 00:00:01 | | LUWID: CAIBMOML.STLLU.A5241A50FABD.0001 | | EXTNAM: PETERSON .BATCH .PETERSON.DSNESPRR | | Requester: DB2 V2.3.0 at IBMREGRDBSTL0012 | | User-ID: SWAGRMAN SQL-ID: SQLDBA | | User is waiting for an agent | | State started: 1999-02-04 10:22:11 | | Conversation started: 1999-02-04 10:03:05 | | CPU time: 00:00:02 | | LUWID: IBMNET07.*IDENT.AB457DFF69BC.0001 | | EXTNAM: SWAGRMAN.1 | | Requester: SQLDS/VM V3.3.0 at TOROLAB3 | | User-ID: JAVIER SQL-ID: JAVIER | | User is inactive | | State started: 1999-02-04 10:02:11 | | Conversation started: 1999-02-04 09:27:49 | | CPU time: 00:00:03 | | LUWID: IBMNET07.*IDENT.AB457DFF6ABC.0001 | | EXTNAM: JAVIER.1 | | Requester: SQLDS/VM V3.3.0 at TOROLAB | | 3 Users are active. | | 1 Users are waiting. | | 1 Users are inactive. | | 0 Agents are available. | | 94 User connections are available. | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
The current time is 10:25:33. There are three active users:
There are two other connected users. One is waiting for an agent, the other is inactive.
You can also determine from the additional lines of information (LUWID, EXTNAM, Requester) that all the requestors, with the exception of Agent 1, are DRDA requestors.
The example below an agent is executing a stored procedure.
+--------------------------------------------------------------------------------+ | show connect | | Status of Connected Users 1999-09-30 08:56:42 | | Checkpoint agent is not active. | | User Agent: 1 User-ID: SQLUSRKJ SQL-ID: SQLUSRKJ | | is R/O APPL 1666 | | Agent is not processing and is in communication wait. | | State started: 1999-09-30 08:56:39 | | Conversation started: 1999-09-30 08:56:12 | | Protocol: SQLDS | | Package: SQLDBA.MAINPGM Section: 4 | | Procedure: PROC1 Modname: MYPROC | | Procedure Package: SQLDBA.MYPROC Section: 4 | | User Agent: 2 User-ID: SQLUSRJR SQL-ID: SQLUSRJR | | is R/O APPL 1667 | | Agent is not processing and is waiting for a stored procedure | | server in group GROUP1 | | State started: 1999-09-30 08:56:39 | | Conversation started: 1999-09-30 08:56:12 | | Protocol: SQLDS | | Package: SQLDBA.MAINPGM2 Section: 3 | | User Agent: 3 User-ID: SQLUSRTH SQL-ID: SQLUSRTH | | is R/O APPL 1668 | | Agent is processing with LPAGEBUFF=1032 | | State started: 1999-09-30 08:56:39 | | Conversation started: 1999-09-30 08:56:12 | | Protocol: SQLDS | | Package: SQLDBA.MAINPGM3 Section: 4 | | Procedure: PROC3 Modname: MYPROC3 | | Procedure Package: SQLDBA.MYPROC3 Section: 2 | | User Agent: 4 User-ID: SQLUSRJR SQL-ID: SQLUSRTL | | is R/O APPL 1669 | | Agent is not processing and is waiting for stored procedure | | PROC4 AUTHID SQLUSRTL to be started | | State started: 1999-09-30 08:58:00 | | Conversation started: 1999-09-30 08:57:35 | | Protocol: SQLDS | | Package: SQLDBA.MAINPGM4 Section: 3 | | 4 Users are active. | | 0 Users are waiting. | | 0 Users are inactive. | | 2 Agents are available. | | 2 User connections are available. | | | | ARI0065I Operator command processing is complete. | | | +--------------------------------------------------------------------------------+
In the example above:
If a CICS user requests that the operator terminate a CICS transaction containing DB2 Server for VSE statements, the operator should first force the associated DB2 Server for VSE agent by using the FORCE command before terminating the transaction. Prior to SQLDS Version 3 Release 5, the operator may not be able to determine which agent to force, because multiple agents may use the same DB2 Server for VSE user ID. Furthermore, if the agent is not forced before the transaction is terminated, the resource adapter may encounter an error and shut itself down. This would cause the links to DB2 Server for VSE through that particular resource adapter to be lost. In SQLDS Version 3 Release 4, only the CICS task number representing the AXE transaction is displayed, and only for remote (DRDA) users.
Operators can now identify which agent should be forced by displaying the CICS task number, the CICS terminal ID, and the RMID for all local CICS users as part of the output for the SHOW CONNECT command. This information will be displayed for both VSE and VM (Guest Sharing) users.
The additional information on the SHOW CONNECT command will enable the operator to identify which agent should be forced. This is accomplished by the following steps:
The CICS task number, CICS terminal id, and RMID will be displayed for all local (VSE or VM Guest Sharing) CICS transactions whenever the agent is in work. The CICS terminal ID may contain a value of 'N/A' indicating that the terminal ID is not available, such as when a user issues queries through ISQL. The CICS task number, the CICS terminal id, and the RMID will not be displayed for batch users, or for agents whose work status is NIW (not in work). For remote (DRDA) users, only the CICS task number representing the AXE transaction will be displayed; the CICS terminal id and the RMID will not be displayed.
When a CICS transaction is using a release of the Resource Adapter prior to Version 3 Release 5, the CICS terminal id and the RMID are not available to the database server and 'N/A' will be displayed.
Included below are sample outputs for each of the cases where additional information may be displayed.
Figure 2. CICS Transaction (Version 3 Release 5 Requester)
F4 004 User Agent: 1 User-ID: JOAO SQL-ID: JOAO
F4 004 is R/O APPL 12BCF
F4 004 Agent is processing and is in communication wait.
F4 004 State started: 1999-09-02 15:21:22
F4 004 Conversation started: 1999-09-02 15:21:22
F4 004 Task no.: 147 RMID: 32 Term. id: 077D
Figure 3. ISQL Query
F4 004 User Agent: 1 User-ID: JOAO SQL-ID: JOAO
F4 004 is R/O APPL 12BCF
F4 004 Agent is processing and is in communication wait.
F4 004 State started: 1999-09-02 15:21:22
F4 004 Conversation started: 1999-09-02 15:21:22
F4 004 Task no.: 147 RMID: 32 Term. id: N/A
Figure 4. Agent Not in Work
F4 004 User Agent: 2 User-ID: DBDCCICS SQL-ID: DBDCCICS
F4 004 is NIW SUBS
F4 004 Agent is not processing and is in communication wait.
F4 004 State started: 1999-09-03 15:19:57
F4 004 Conversation started: 1999-09-03 15:19:57
Figure 5. Batch User
F4 004 User Agent: 2 User-ID: SQLDBA SQL-ID: SQLDBA
F4 004 is R/W APPL 18D9
F4 004 Agent is not processing and is in communication wait.
F4 004 State started: 1999-09-08 15:34:51
F4 004 Conversation started: 1999-09-08 15:34:41
Figure 6. DRDA User Accessing VSE Database
F4 004 User Agent: 2 User-ID: EDUARDA SQL-ID: EDUARDA
F4 004 is R/W APPL 12FC4
F4 004 Agent is processing and is in communication wait.
F4 004 State started: 1999-09-02 15:23:17
F4 004 Conversation started: 1999-09-02 15:23:15
F4 004 CPU time: 00:00:01
F4 004 LUWID: CAIBMOML.OECGW001.A6773D6F8611.0001
F4 004 EXTNAM: EDUARDA.1
F4 004 Requester: SQLDS/VM V3.5.0 at TOIVMLB6
F4 004 Rmtuser ID: 2
F4 004 LU name: OMPGW001
F4 004 Task no.: 0000134
Figure 7. VSE Guest Sharing User to VM Database Using ISQL
User Agent: 1 User-ID: VSEMCH10 SQL-ID: SQLDBA
is R/O SUBS 1796
Agent is not processing and is in communication wait.
State started: 1999-09-08 10:42:55
Conversation started: 1999-09-08 10:42:43
Task no.: 371 RMID: 12 Term. id: N/A
Figure 8. VM Requester Accessing VM Database
User Agent: 1 User-ID: SQLUSRMR SQL-ID: SQLUSRMR
is R/O APPL 178F
Agent is not processing and is in communication wait.
State started: 1999-09-08 10:41:11
Conversation started: 1999-09-08 10:41:04
Figure 9. CICS Transaction (Version 3 Release 4 Requester)
F4 004 User Agent: 1 User-ID: JOAO SQL-ID: JOAO
F4 004 is R/O APPL 12BCF
F4 004 Agent is processing and is in communication wait.
F4 004 State started: 1999-09-02 15:21:22
F4 004 Conversation started: 1999-09-02 15:21:22
F4 004 Task no.: 147 RMID: N/A Term. id: N/A
The SHOW LOCK command can help you understand and resolve immediate locking contention problems. (For information on this area, refer to Locking Contention.) Consider the following situation:
A SHOW LOCK ACTIVE command reveals that LAURA is in a communication wait, and BRAIN and PETER are in a lock wait. (You can also determine this with a SHOW ACTIVE command.)
+--------------------------------------------------------------------------------+ |show lock active | | WAIT TOTAL LONG WANTLOCK WANTLOCK | |AGENT USER STATE LOCKS LOCKS TYPE DBSPACE | | C CHECKPT NIW 0 0 | | 1 BRIAN LOCK 55 55 PAGE 7 | | 2 PETER LOCK 55 55 PAGE 7 | | 3 LAURA COMM 44 44 | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
A SHOW LOCK MATRIX reveals that BRIAN is waiting for PETER, and PETER is waiting for LAURA. (The number seven in the lock matrix indicates that the contention is in dbspace seven.)
+--------------------------------------------------------------------------------+ |show lock matrix | |Lock Request Block (LRB) and Lock Status: | | NLRBS IN USE FREE NLRBU MAX USED BY LUW | | ------- ------- ------- ------- --------------- | | 2520 213 2307 1000 386 | | *** THE LOCKWAIT TABLE *** | | ENTRY = DBSPACE NUMBER ON WHICH THERE IS LOCK CONTENTION | | The presence of an entry shows | | the agent requesting the lock and | | the agent contending for or holding the lock. | | AGENT AGENT CONTENDING FOR OR HOLDING THE LOCK | | REQUESTING | | LOCK | | 1 2 3 4 5 | | BRIAN PETER LAURA | | 1 BRIAN ........ 7 ........ ........ ........ | | 2 PETER ........ ........ 7 ........ ........ | | 3 LAURA ........ ........ ........ ........ ........ | | 4 ........ ........ ........ ........ ........ | | 5 ........ ........ ........ ........ ........ | | | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
A SHOW LOCK GRAPH of BRIAN clearly shows the chain of lock contention that has occurred. Until LAURA clears her screen and returns her SHARE (S) lock, neither PETER nor BRIAN can leave their lock wait.
+--------------------------------------------------------------------------------+ |show lock graph brian | |LOCK LOCK WAIT LOCK DBSP LOCK REQ REQ | |REQUESTER HOLDER STAT TYPE NUMBR QUALIFIER STATE MODE DUR | |1 BRIAN 2 PETER LOCK PAGE 7 88 G WAIT U LONG | |2 PETER 3 LAURA COMM PAGE 7 88 C WAIT X LONG | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
The SHOW LOCK USER command displays both how many locks of each type are held plus the number they are waiting for.
+--------------------------------------------------------------------------------+ |show lock user | | DBSPACE LOCK NUMBER | |AGENT USER NUMBER TYPE IN SIX IS IX S U X Z WAITERS | |
·
·
·
| | 1 BRIAN 7 DBSP 0 0 1 0 0 0 0 0 0 | | 1 BRIAN 7 IPAG 0 0 0 0 1 0 0 0 0 | | 1 BRIAN 7 TABL 0 0 1 0 0 0 0 0 0 | |
·
·
·
| | 2 LAURA 7 DBSP 0 0 1 0 0 0 0 0 0 | | 2 LAURA 7 IPAG 0 0 0 0 1 0 0 0 0 | | 2 LAURA 7 PAGE 0 0 0 0 2 0 0 0 2 | | 2 LAURA 7 TABL 0 0 1 0 0 0 0 0 0 | |
·
·
·
| | 3 PETER 7 DBSP 0 0 0 1 0 0 0 0 0 | | 3 PETER 7 IPAG 0 0 0 0 1 0 0 0 0 | | 3 PETER 7 PAGE 0 0 0 0 0 1 0 0 2 | | 3 PETER 7 TABL 0 0 0 1 0 0 0 0 0 | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
In this case, PETER has an UPDATE (U) lock on the page that holds THOMPSON's salary, but it cannot be promoted to an EXCLUSIVE (X) lock because it is incompatible with LAURA's SHARE (S) lock. BRIAN has a SHARE lock but cannot promote it to an UPDATE lock because it is incompatible with PETER's UPDATE lock.
The SHOW LOCK MATRIX command can help you understand and resolve lock escalation problems. (For information on this area, refer to Lock Escalation.) Consider the following situation:
+--------------------------------------------------------------------------------+ |show lock matrix | |Lock Request Block (LRB) and Lock Status: | | NLRBS IN USE FREE NLRBU MAX USED BY LUW | | ------- ------- ------- ------- --------------- | | 2520 213 2307 1000 386 | |
·
·
·
| +--------------------------------------------------------------------------------+
The maximum number of lock request blocks that can be held by a single agent (NLRBU) is set to 1000. The number that can be held by all the agents (NLRBS) is set to 2520. 213 blocks are currently in use and 2307 are free. The maximum number of blocks held since the last lock escalation is 386.
Do not rely on this command alone. The MAX USED BY LUW may appear to be significantly lower than NLRBS, but remember MAX USED is reset to zero after every escalation. If the database manager is constantly escalating locks, you may be unlucky enough to only see the value immediately following an escalation. Make sure that you consult the ESCALATE and LOCKLMT counters as well. Refer to "Lock Request Block Performance" on page ***.
Information about the database is maintained in a set of tables called the catalog which are created during database generation. They describe tables, columns, indexes, keys, packages, authorities and other objects in the database. This section describes how to select information from various tables that contain performance information.
The catalog also holds statistical information on the data stored in the database. The database manager uses it to select an access path for each SQL request it processes. Refer to Keeping Database Statistics Current for a description of each table that contains this information and what the values in each column means. Also refer to Using Catalog Statistics for a discussion of how to model a large production database with a small test database by altering the values in the catalog.
The following SQL statement retrieves performance information from the SYSCATALOG table about all the tables in the sample dbspace:
+--------------------------------------------------------------------------------+ |SELECT tname, avgrowlen, rowcount, npages, noverflow | | FROM system.syscatalog | | WHERE dbspacename='sample' | | AND creator='sqldba' | | | | | |TNAME AVGROWLEN ROWCOUNT NPAGES NOVERFLOW | |------------------ --------- ------------ ------------ ------------ | |ACTIVITY 31 18 2 0 | |DEPARTMENT 39 9 1 0 | |EMP_ACT 36 74 2 0 | |EMPLOYEE 80 32 2 0 | |INVENTORY 21 22 1 0 | |OPERATIONS 44 15 2 0 | |PROJ_ACT 29 77 2 0 | |PROJECT 64 20 1 0 | |PROJECTS 43 5 1 0 | |QUOTATIONS 24 53 1 0 | |SUPPLIERS 57 10 2 0 | +--------------------------------------------------------------------------------+
If you decide to reorganize the table because of this, you may also want to use the ALTER DBSPACE command to increase the PCTFREE value of the dbspace that contains the table, refer to Free Space in Data Pages.
The following SQL statement retrieves performance information from the SYSCOLUMNS table about all columns in the employee table:
+--------------------------------------------------------------------------------+ |SELECT cname, coltype, length, nulls, ccsid | | FROM system.syscolumns | | WHERE tname = 'employee' | | AND creator='sqldba' | | | | | | | | CNAME COLTYPE LENGTH NULLS CCSID | | ------------------ -------- ------- ----- ------------ | | BIRTHDATE DATE Y ? | | BONUS DECIMAL ( 9, 2) Y ? | | COMM DECIMAL ( 9, 2) Y ? | | EDLEVEL SMALLINT N ? | | EMPNO CHAR 6 N 500 | | FIRSTNME VARCHAR 12 N 500 | | HIREDATE DATE Y ? | | JOB CHAR 8 Y 500 | | LASTNAME VARCHAR 15 N 500 | | MIDINIT CHAR 1 N 500 | | PHONENO CHAR 4 Y 500 | | SALARY DECIMAL ( 9, 2) Y ? | | SEX CHAR 1 Y 500 | | WORKDEPT CHAR 3 Y 500 | +--------------------------------------------------------------------------------+
The following SQL statement retrieves performance information from the SYSDBSPACES table about the subscriptions dbspace:
+--------------------------------------------------------------------------------+ |SELECT dbspaceno, npages, nactive, pctindx, freepct, lockmode, pool | | FROM system.sysdbspaces | | WHERE dbspacename='subscriptions' | | AND creator='sqldba' | | | | | | DBSPACENO NPAGES NACTIVE PCTINDX FREEPCT LOCKMODE POOL | | --------- ------------ ------------ ------- ------- -------- ------ | | 75 480819 18427 25 0 T 2 | | * End of Result *** 1 Rows Displayed ***Cost Estimate is 1*********************| +--------------------------------------------------------------------------------+
The following SQL statement retrieves performance information from the SYSINDEXES table about all the indexes in the sample dbspace:
+--------------------------------------------------------------------------------+ |SELECT iname, cluster, clusterratio, lockmode, ipctfree, release | | FROM system.sysindexes, system.syscatalog | | WHERE system.sysindexes.tname = system.syscatalog.tname | | AND dbspacename='sample' | | AND creator='sqldba' | | | | | |INAME CLUSTER CLUSTERRATIO LOCKMODE IPCTFREE RELEASE | |------------------ ------- ------------ -------- -------- ------- | |PKEYB1PAIBMXWNCV W 9375 P 10 7.1.0 | |PKEYB1PAIAXH1U6L F 10000 P 10 7.1.0 | |MGRNOI C 10000 P 10 7.1.0 | |PROJNOIN W 9306 P 10 7.1.0 | |EMPNOIN C 10000 P 10 7.1.0 | |PKEYB1PAIA5RUD1W F 10000 P 10 7.1.0 | |WORKDEPTI N 8667 P 10 7.1.0 | |INV1 F 10000 P 10 7.1.0 | |OPE1 W 9231 P 10 7.1.0 | |PKEYB1PAIBSXHRBH F 10000 P 10 7.1.0 | |DEPTNOI C 10000 P 10 7.1.0 | |PKEYB1PAIBEPN7Y2 F 10000 P 10 7.1.0 | |RESPEMPI C 10000 P 10 7.1.0 | |PRO1 F 10000 P 10 7.1.0 | |QUO1 F 10000 P 10 7.1.0 | |SUP1 F 10000 P 10 7.1.0 | +--------------------------------------------------------------------------------+
You can also retrieve the same information for the indexes of a single table. In this case the employee table:
+--------------------------------------------------------------------------------+ |SELECT iname, cluster, clusterratio, lockmode, ipctfree, release | | FROM system.sysindexes | | WHERE tname='employee' | | AND creator='sqldba' | | | | | |INAME CLUSTER CLUSTERRATIO LOCKMODE IPCTFREE RELEASE | |------------------ ------- ------------ -------- -------- ------- | |PKEYB1PAIA5RUD1W F 10000 P 10 7.1.0 | |WORKDEPTI N 8667 P 10 7.1.0 | +--------------------------------------------------------------------------------+
CLUSTERRATIO is updated when the index's statistics are updated (SYSTEM.SYSINDEXES catalog table). It indicates the percentage of time that the data pages are in a logical sequence in relation to the index. In this case, when the statistics for WORKDEPTI were last updated, the data pages it referred to were in a logical sequence 86.67% of the time.
For more information on how to interpret CLUSTER and CLUSTERRATIO, refer to Identifying Unclustered Indexes.