The COUNTER and SHOW commands let you monitor DB2 Server for VSE & VM performance. When VSE guests in a VM system are accessing the application server, and the application server is running with CICS, you can also get more performance data by using the CICS/VSE Monitoring Facility.
See the CICS/VSE Installation and Operations Guide for a description of this facility.
You can use performance data to tune the application server. This manual does not give guidelines for making decisions on tuning performance; that information is in the DB2 Server for VSE & VM Performance Tuning Handbook.
You can also use the SHOW commands to find system bottlenecks. The DB2 Server for VSE & VM Diagnosis Guide and Reference, describes how to use the SHOW commands for problem determination.
You can enter the COUNTER and SHOW commands from the ISQL facility. The following sections list and describe the COUNTER and SHOW commands; each command is followed by an example of the information it displays.
>>-COUNTER----+-*-----------+----------------------------------><
| .--------. |
| V | |
'----name---+-'
|
The application server counts the occurrences of key events. To display all counters, enter:
COUNTER *
To display specific counters, list them after the command name like this:
COUNTER LOGREAD LOGWRITE
To set the counters to zero, enter the RESET command. Refer to RESET for a description of the RESET command. Each counter is set to zero when you start the application server. If a counter reaches its limit (2,147,483,647) and overflows, it is reset to zero and starts over.
Below are the names of the counters and what they count.
RDSCALL represents the number of times that application programs (including user programs, the ISQL facility, the DBS Utility, and the preprocessors) access the application server.
The DBSSCALL counter represents the number of times that the RDS system accesses the DBSS system to satisfy RDS calls.
when the application server tries to replace small locks acquired by a logical unit of work with a single larger lock. (The larger lock is always a DBSPACE lock.) When an escalation fails, the usual reason is that the attempt to acquire a DBSPACE lock causes a deadlock. A logical unit of work that reaches a lock limit must be rolled back. The process could also fail when an insufficient number of locks are freed by the acquisition of the larger lock. (This rarely happens.)
LOCKLMT plus ESCALATE is the number of times that logical units of work reach the user or system lock limit. You set lock limits with the NLRBU initialization parameter. You define the lock limit for the system with the NLRBS initialization parameter.
ESCALATE plus LOCKLMT is the number of times that a logical unit of work reached the user (NLRBU) or system (NLRBS) lock limit.
The following examples show the counters displayed when you enter COUNTER *.
Figure 26. A Typical COUNTER Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 counter * |
|F4 004 Counter values at DATE='01-10-96' TIME='20:59:59' |
|F4 004 Calls to RDS RDSCALL : 431 |
|F4 004 Calls to DBSS DBSSCALL: 2044 |
|F4 004 LUWS started BEGINLUW: 82 |
|F4 004 LUWS rolled back ROLLBACK: 9 |
|F4 004 System checkpoints taken CHKPOINT: 2 |
|F4 004 Maximum locks exceeded LOCKLMT : 0 |
|F4 004 Lock escalations ESCALATE: 0 |
|F4 004 Waits for lock WAITLOCK: 4 |
|F4 004 Deadlocks detected DEADLCK : 0 |
|F4 004 Looks in page buffer LPAGBUFF: 4503 |
|F4 004 DBSPACE page reads PAGEREAD: 370 |
|F4 004 DBSPACE page writes PAGWRITE: 108 |
|F4 004 Looks in directory buffer LDIRBUFF: 770 |
|F4 004 Directory block reads DIRREAD : 23 |
|F4 004 Directory block writes DIRWRITE: 0 |
|F4 004 Log page reads LOGREAD : 20 |
|F4 004 Log page writes LOGWRITE: 19 |
|F4 004 Total DASD reads DASDREAD: 413 |
|F4 004 Total DASD writes DASDWRIT: 127 |
|F4 004 Total DASD I/O DASDIO : 540 |
|F4 004 Lock timeouts detected LTIMEOUT: 0 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 27. A Typical COUNTER Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|counter * |
|Counter values at DATE='01-10-96' TIME='20:59:59' |
|Calls to RDS RDSCALL : 431 |
|Calls to DBSS DBSSCALL: 2044 |
|LUWS started BEGINLUW: 82 |
|LUWS rolled back ROLLBACK: 9 |
|System checkpoints taken CHKPOINT: 2 |
|Maximum locks exceeded LOCKLMT : 0 |
|Lock escalations ESCALATE: 0 |
|Waits for lock WAITLOCK: 4 |
|Deadlocks detected DEADLCK : 0 |
|Looks in page buffer LPAGBUFF: 4503 |
|DBSPACE page reads PAGEREAD: 370 |
|DBSPACE page writes PAGWRITE: 108 |
|Looks in directory buffer LDIRBUFF: 770 |
|Directory block reads DIRREAD : 23 |
|Directory block writes DIRWRITE: 0 |
|Log page reads LOGREAD : 20 |
|Log page writes LOGWRITE: 19 |
|Total DASD reads DASDREAD: 413 |
|Total DASD writes DASDWRIT: 127 |
|Total DASD I/O DASDIO : 540 |
|Lock timeouts detected LTIMEOUT: 0 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
| Note: | There are several important things to remember about these coutners
if you have installed the optional VMDSS code to support VM Data Spaces:
|
>>-RESET----+-*-----------+------------------------------------><
| .--------. |
| V | |
'----name---+-'
|
The RESET command sets the named counters to zero. Specify RESET * to set all counters to zero. (See COUNTER for the counter names.)
| Note: | You cannot reset the DASDIO, DASDREAD, and DASDWRIT counters because they are sums of other counters. |
You can enter the RESET command only from the DB2 Server for VM or VSE system operator consoles.
>>-RESET--CRR--LOGNAMES---------------------------------------->< |
The RESET CRR LOGNAMES command lets the database manager reset its own log name and the CRR recovery server's TPN and log name to hex zeros. This command can be used when log name mismatch situations arise. When issued, it displays the following message:
ARI0173I The CRR recovery server's TPN and log name and the database
manager's log name have been reset. On the next
resynchronization, the database manager's log status
will be cold.
Notes:
ARI0198E Reset CRR Lognames cannot be issued when
Resynchronization Initialization is active.
ARI0198E Reset CRR Lognames cannot be issued when
Resynchronization Recovery is active.
>>-RESET HIGHSTOR---------------------------------------------->< |
The RESET HIGHSTOR command resets the previous high storage values to the current allocated values; it will also reset the USERID field to show the current high storage holder. The RESET HIGHSTOR command has no parameters.
The RESET HIGHSTOR command, when used in conjunction with the SHOW STORAGE command, can aid in determining the maximum storage required to perform a specific function.
The RESET HIGHSTOR command can be entered only from the VSE system operator console or the DB2 Server for VM operator console.
.-------------.
V |
>>-RESET INDOUBT----+------tranid----+-+-----------------------><
'-+---+------------'
'-*-'
|
The RESET INDOUBT command lets you erase the records of in-doubt LUWs that have been forced by heuristic actions. This should only be done after being reconciled with directions from the recovery server.
The operator can specify one or more TRANIDs for the in-doubt LUWs that have been forced. The value of the TRANIDs are obtained by issuing the SHOW INDOUBT command. The TRANIDs must be separated by a blank. If several TRANIDs are entered and one is invalid or not found, the RESET INDOUBT command continues processing the remaining TRANIDs. If * is specified, all records of heuristic actions taken since the last cold start of DB2 Server for VSE & VM will be erased with the RESET INDOUBT command.
A warning message listing the records of the forced in-doubt LUWs to be erased will be displayed prompting the operator to confirm.
After the forced in-doubt LUWs have been removed from DB2 Server for VSE & VM by using the RESET INDOUBT command, other participants of the LUW which are in-doubt will be forced to use a heuristic decision process to resolve the in-doubt logical unit of work.
>>-SET----+-CHKINTVL-+--newval---------------------------------><
+-DISPBIAS-+
+-DSPSTATS-+
+-DUMPTYPE-+
+-LTIMEOUT-+
+-PTIMEOUT-+
'-PROCMXAB-'
|
The SET command lets you change the settings of the CHKINTVL, DISPBIAS, DUMPTYPE, LTIMEOUT, PTIMEOUT, and PROCMXAB initialization parameters.
See Appendix A, DB2 Server for VSE & VM Initialization Parameters for more information on the initialization parameters.
Figure 28 is an example of the SET command.
Figure 28. SET command examples
+--------------------------------------------------------------------------------+
|ARI0045I Ready for operator communications. |
| |
|SET CHKINTVL 200 |
|ARI0065I Operator command processing is complete. |
| |
|SET DISPBIAS 7 |
|ARI0065I Operator command processing is complete. |
| |
|SET DSPSTATS 21 |
|ARI0065I Operator command processing is complete. |
| |
|SET DUMPTYPE F |
|ARI0065I Operator command processing is complete. |
| |
|SET LTIMEOUT 300 |
|ARI0065I Operator command processing is complete. |
| |
|SET PTIMEOUT 180 |
|ARI0065I Operator command processing is complete. |
| |
|SET PROCMXAB 1 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW ACTIVE------------------------------------------------->< |
The SHOW ACTIVE command displays the status of the active agent structures in the database partition or machine. An agent structure, or agent, is the internal representation of an active user. The application server uses these structures to service SQL requests by users, to process operator commands, and to perform checkpoints and database archives.
The status of certain agent structures are not displayed by the SHOW ACTIVE command. They are the operator agent, ready or recovery agent, and the TCP/IP agent if TCP/IP is active. The operator agent structure and the ready or recovery agent structure always exist in the application server. The operation agent is used for operator command processing, and it exists in both multiple and single user modes. The ready or recovery agent is only used in the VM/ESA operating system for VSE guests. It exists in multiple user mode only. The TCP/IP agent handles the co-ordination of TCP/IP clients. It only exists in multiple user mode and when TCP/IP is active.
Another agent structure is used only for checkpoint and database archive work. This structure is called the CHECKPOINT AGENT in the command display. The checkpoint agent always exists in both single and multiple user modes. The display shows whether the agent is dormant, waiting to start, or processing. If the checkpoint agent is waiting to start or is processing, the display also shows if it is an ARCHIVE checkpoint.
The status of each active agent structure is displayed following this information as described below:
For remote connections, the DB2 Server for VM authorization ID can also be the ID specified by the userid tag in the communications directory file, or a non-VM user ID. If the application server is in the process of verifying the authorization ID, it is not shown. The string null id is displayed instead.
The SHOW ACTIVE command displays:
| Note: | If the logical unit of work is updating only data that resides in a nonrecoverable storage pool, then R/O is displayed (not R/W). |
NEW is applicable only when log archiving is starting. NEW units of work may not continue until the checkpoint that starts a log archive ends. See Figure 34 for an example of NEW agent structures.
For batch, APPL (batch application) is displayed; for CICS online, SUBS (representing subsystem) is displayed. In the VSE console example below, user JONES is an online user whose requests are coming from the CICS subsystem.
| Note: | An agent is in communication wait when it is waiting for an SQL request from the user, or is waiting for an APPC/VM function to end. An agent is in latch wait when it is waiting for a latch on a page or block buffer. If the agent is not waiting, it will display the number of times the database manager looks in the page buffer (LPAGBUFF) since the beginning of the logical unit of work. |
If the application server or the CICS system had previously abnormally ended, some agents may have a display like the following:
Figure 29. A Typical Example of an In-doubt Logical Unit of Work in VM
+--------------------------------------------------------------------------------+
| User Agent 7 User ID: JONES is prepared |
| for commit or rollback |
| VM ID= VSE1 COORDINATOR= DBDCCICS RESOURCE ADAPTER= 0 |
| TRANSACTION= TPSP CICS SIGNON= PETER TERMINAL= L012 |
+--------------------------------------------------------------------------------+
Figure 30. A Typical Example of an In-doubt Logical Unit of Work in VSE
+--------------------------------------------------------------------------------+
|F4 004 User Agent 7 User ID: JONES is prepared |
|F4 004 for commit or rollback |
|F4 004 COORDINATOR= DBDCCICS RESOURCE ADAPTER= 0 |
|F4 004 TRANSACTION= TPSP CICS SIGNON= PETER TERMINAL= L012 |
+--------------------------------------------------------------------------------+
In particular, note that the user ID is followed by the message IS PREPARED FOR COMMIT OR ROLLBACK. This identifies the agent's logical unit of work as being in-doubt.
(In-doubt logical units of work occur when a system failure happens while an online user is committing a logical unit of work.) If running under the CICS subsystem, in-doubt logical units of work will be resolved automatically the next time you run the CIRB transaction. However, you may want to resolve in-doubt logical units of work manually. If you enter SHOW ACTIVE and see that some logical units of work are in-doubt, refer to Resolving CICS In-Doubt Logical Units of Work.
Following the display of the status of each active agent structure, SHOW ACTIVE displays the number of inactive (NOT CONNECTED...) agent structures.
The system operator or an ISQL user can enter the SHOW ACTIVE command. An ISQL user can only enter the SHOW ACTIVE command when connected to an application server using SQLDS protocol.
When you enter SHOW ACTIVE on the operator console, any agent that starts a logical unit of work becomes forceable. The agent remains forceable until the logical unit of work ends. You can use the FORCE command (described on page ***) only on forceable agents and you can enter the FORCE command only from the operator console. The application server resets the agent's forceable indicator when the agent's logical unit of work commits or rolls back.
In VM, SHOW ACTIVE shows only the users who currently have an agent structure. To display all users who are connected to the application server and their status, the DB2 Server for VM operator or ISQL terminal user can enter the SHOW USERS command or the SHOW CONNECT command. For further information on these commands, see Figure 82 and SHOW CONNECT.
Figure 31. A Typical SHOW ACTIVE Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show active |
|F4 004 Status of agents: |
|F4 004 Checkpoint agent is not active. |
|F4 004 User Agent: 1 User ID: SMITH is R/W APPL 7B4 |
|F4 004 Agent is processing and is in I/O Wait. |
|F4 004 User Agent: 2 User ID: MICHAEL is R/O SUBS 7B9 |
|F4 004 Agent is not processing and is in communication Wait. |
|F4 004 User Agent: 3 User ID: JESSICA IS R/O APPL 5A4 |
|F4 004 Agent is processing an SQL/DS operator command. |
|F4 004 User Agent: 4 User ID: TESTUSER IS R/W APPL 7BB |
|F4 004 Agent is processing and is in I/O. |
|F4 004 User Agent: 5 User ID: MACNIELL IS R/O APPL 7B9 |
|F4 004 Agent is not processing and is in communication Wait. |
|F4 004 5 agent(s) not connected to an APPL or SUBSYS. |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 31 is a typical SHOW ACTIVE display. Figure 32 shows the display you might see when R/O or R/W logical units of work are delaying the checkpoint that starts a log archive. If checkpoints are delayed, the database manager delays new logical units of work until the checkpoint finishes.
Figure 32. A Typical Example of Logical Units of Work Delaying a Checkpoint on VSE
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show active |
|F4 004 Status of agents: |
|F4 004 Checkpoint agent is waiting to start log archive |
|F4 004 New logical units of work and the log archive |
|F4 004 process cannot continue until all R/O, R/W, |
|F4 004 and prepared logical units of work are completed. |
|F4 004 User Agent: 1 User ID: CHAN is R/W APPL 106 |
|F4 004 Agent is not processing and is in communication Wait. |
|F4 004 User Agent: 2 User ID: EWING is NEW APPL 107 |
|F4 004 Agent is waiting for log archive checkpoint. |
|F4 004 3 agent(s) not connected to an APPL or SUBSYS. |
+--------------------------------------------------------------------------------+
Figure 33. A Typical SHOW ACTIVE Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|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 SQL/DS operator command. |
| User Agent: 4 User ID: TESTUSER IS R/W APPL 7BB |
| Agent is processing and is in I/O. |
| 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. |
+--------------------------------------------------------------------------------+
Figure 33 is a typical SHOW ACTIVE display. Figure 34 shows the display you might see when R/O or R/W logical units of work are delaying the checkpoint that starts a log archive. If checkpoints are delayed, the application server also delays new logical units of work until the checkpoint finishes.
Figure 34. A Typical Example of Logical Units of Work Delaying a Checkpoint on VM
+--------------------------------------------------------------------------------+
|show active |
|Status of agents: |
| Checkpoint agent is waiting to start log archive |
| New logical units of work and the log archive |
| process cannot continue until all R/O, R/W, |
| and prepared logical units of work are completed. |
| User Agent: 1 User ID: CHAN is R/W APPL 106 |
| Agent is not processing and is in communication Wait. |
| User Agent: 2 User ID: EWING is NEW APPL 107 |
| Agent is waiting for log archive checkpoint. |
| 3 agent(s) not connected to an APPL or SUBSYS. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW ADDRESS--module-name----------------------------------->< |
The SHOW ADDRESS command displays the load address, compile date and PTF identifier (if it exists) of CONV, DSC, DBSS, DRRM, RDS, and WUM modules. This command can help with problem determination. You can use it for debugging. This command can also be used to determine if a PTF has been applied.
The output of this command is the hexadecimal load address of the module you specify, or an error message if the module is not found. This load address applies only to the application server, not to an application requester from which the command may have been entered (in SQLDS protocol).
Figure 35 shows sample output from the SHOW ADDRESS command.
Figure 35. A Typical SHOW ADDRESS Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show address ariym00 |
|ARIYM00 97.090 PLX : 6CDBA0 HEX |
+--------------------------------------------------------------------------------+
Figure 36. A Typical SHOW ADDRESS Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show address ariym00 |
|F4 004 ARIYM00 97.090 PLX : 6CDBA0 HEX |
+--------------------------------------------------------------------------------+
>>-SHOW BUFFERS------------------------------------------------>< |
The database manager maintains a pool of 4096-byte buffers that contain the most frequently accessed database pages. It also maintains a set of 512-byte buffers that contain the most frequently accessed directory blocks. The SHOW BUFFERS command displays information about the buffer pool size and usage. This information can be used to tune the database manager.
Figure 37 shows the output of the SHOW BUFFERS command.
Figure 37. A Typical SHOW BUFFERS Display on a VSE Operator Console
AR MSG F4 AR 015 1I40I READY F4 004 ARI0062A SQLDS : F4-004 Enter a DB2 Server for VSE operator command. 4 show buffers F4 004 Only used buffers are displayed. F4 004 DBSPACE REC ADDR FLAGS FIX CNT F4 004 Page Buffers F4 004 1 000090 00608000 00 0 F4 004 1 000093 00609000 00 0 F4 004 1 00014D 0060A000 00 0 F4 004 1 00014C 0060B000 80 0 F4 004 2 000094 0060C000 00 0 F4 004 2 000091 0060D000 00 0 F4 004 1 00008D 0060E000 00 0 F4 004 1 000088 0060F000 80 0 F4 004 5 00008B 00610000 00 0 F4 004 1 00008F 00611000 00 0 F4 004 2 000080 00612000 00 0 F4 004 1 000092 00613000 80 0 F4 004 1 00015A 00614000 80 0 F4 004 1 00014E 00615000 80 0 F4 004 1 00008E 00616000 00 0 F4 004 1 00008C 00617000 00 0 F4 004 32001 0000FD 00618000 00 1 F4 004 1 000156 00619000 80 0 F4 004 32001 0000F9 0061A000 00 0 F4 004 1 000155 0061B000 80 0 F4 004 1 00014A 0061C000 80 0 F4 004 1 000089 0061D000 00 0 F4 004 1 00008A 0061E000 00 0 F4 004 1 000149 0061F000 80 0 F4 004 1 00014F 00620000 80 0 F4 004 1 00014B 00621000 80 0 F4 004 Directory Buffers F4 004 1 000001 00622000 80 0 F4 004 0 000079 00622200 80 0 F4 004 1 000002 00622400 80 0 F4 004 2 000001 00622600 80 0 F4 004 2 000002 00622800 00 0 F4 004 2 000003 00622A00 00 0 F4 004 2 000004 00622C00 00 0 F4 004 89 000001 00622E00 00 0 F4 004 0 000004 00623000 00 0 F4 004 88 000001 00623200 00 0 F4 004 ARI0065I Operator command processing is complete. |
Figure 38. A Typical SHOW BUFFERS Display on a VM Operator Console or ISQL Terminal
show buffers Only used buffers are displayed. DBSPACE REC ADDR FLAGS FIX CNT Page Buffers 1 000090 00608000 00 0 1 000093 00609000 00 0 1 00014D 0060A000 00 0 1 00014C 0060B000 80 0 2 000094 0060C000 00 0 2 000091 0060D000 00 0 1 00008D 0060E000 00 0 1 000088 0060F000 80 0 5 00008B 00610000 00 0 1 00008F 00611000 00 0 2 000080 00612000 00 0 1 000092 00613000 80 0 1 00015A 00614000 80 0 1 00014E 00615000 80 0 1 00008E 00616000 00 0 1 00008C 00617000 00 0 32001 0000FD 00618000 00 1 1 000156 00619000 80 0 32001 0000F9 0061A000 00 0 1 000155 0061B000 80 0 1 00014A 0061C000 80 0 1 000089 0061D000 00 0 1 00008A 0061E000 00 0 1 000149 0061F000 80 0 1 00014F 00620000 80 0 1 00014B 00621000 80 0 Directory Buffers 1 000001 00622000 80 0 0 000079 00622200 80 0 1 000002 00622400 80 0 2 000001 00622600 80 0 2 000002 00622800 00 0 2 000003 00622A00 00 0 2 000004 00622C00 00 0 89 000001 00622E00 00 0 0 000004 00623000 00 0 88 000001 00623200 00 0 ARI0065I Operator command processing is complete. |
The columns of the output of SHOW BUFFERS have these meanings:
The above conditions can occur in combination and will result in the ORing of the individual bit values to display a FLAGS value for the combination. For example, you could have a modified page that is in transit back to DASD. This would combine the '80' and '40' bits to display a FLAGS value of 'C0'.
.-ALL--------------.
>>-SHOW CONNECT----+------------------+------------------------><
+-userid-----------+
+-USERID--userid---+
+-AGENT--agent_no--+
+-LUWID--luwid-----+
+-ACTIVE-----------+
| (1) |
+-WAITING----------+
'-INACTIVE---------'
Notes:
|
The SHOW CONNECT command displays the status information of all users or selected users connected to the application server.
The parameters of the SHOW CONNECT command have the following meanings:
| Note: | The SHOW CONNECT ACTIVE command should be used before issuing the FORCE or FORCE RMTUSER commands to force users and remote users to end their work. |
The following examples show the output of the SHOW CONNECT command.
Figure 39. A Typical SHOW CONNECT Display on a VSE Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show connect |
|F4 004 Status of Connected Users 1998-04-07 16:06:31 |
|F4 004 Checkpoint agent is not active. |
|F4 004 User Agent: 1 User-ID: SAMUEL SQL-ID: SAMUEL |
|F4 004 is R/O APPL 12BCF |
|F4 004 Agent is processing and is in communication wait. |
|F4 004 State started: 1994-12-02 15:21:22 |
|F4 004 Conversation started: 1996-12-02 15:21:22 |
|F4 004 Task No.: 147 RMID: 32 Term. id: 077D |
|F4 004 CPU time: 00:00:02 |
|F4 004 Protocol: SQLDS via SNA |
|F4 004 User Agent: 2 User-ID: MARISSA SQL-ID: MARISSA |
|F4 004 is R/W APPL 12FC4 |
|F4 004 Agent is processing and is in communication wait. |
|F4 004 State started: 1996-12-02 15:23:17 |
|F4 004 Conversation started: 1996-12-02 15:23:15 |
|F4 004 CPU time: 00:00:01 |
|F4 004 LUWID: CAIBMOML.OECGW001.A6773D6F8611.0001 |
|F4 004 EXTNAM: MARISSA.1 |
|F4 004 Requester: SQLDS/VM V3.3.0 at TOIVMLB6 |
|F4 004 Rmtuser ID: 2 |
|F4 004 LU name: OMPGW001 |
|F4 004 Task no.: 0000134 |
|F4 004 Protocol: SQLDS via SNA |
|F4 004 User Agent: 3 User-ID: LAURA SQL-ID: LAURA |
|F4 004 is R/O APPL 12FC6 |
|F4 004 Agent is processing and is in I/O wait. |
|F4 004 State started: 1994-12-02 15:23:24 |
|F4 004 Conversation started: 1994-12-02 15:23:15 |
|F4 004 CPU time: 00:00:01 |
|F4 004 LUWID: CAIBMOML.OECGW001.A6773D6E52C9.0001 |
|F4 004 EXTNAM: LAURA.1 |
|F4 004 Requester: SQLDS/VM V3.3.0 at TOIVMLB6 |
|F4 004 Rmtuser ID: 3 |
|F4 004 LU name: OMPGW001 |
|F4 004 Task no.: 0000133 |
|F4 004 Protocol: SQLDS via SNA |
|F4 004 User ID: ANDREW SQL ID: ANDREW |
|F4 004 User is inactive. |
|F4 004 State started: 1994-12-02 14:58:18 |
|F4 004 Conversation started: 1994-12-02 14:58:16 |
|F4 004 CPU time: 00:00:00 |
|F4 004 LUWID: CAIBMOML.OECGW001.A67737DC6BEB.0001 |
|F4 004 EXTNAM: ANDREW.1 |
|F4 004 Requester: SQLDS/VM V3.3.0 at TOIVMLB6 |
|F4 004 Rmtuser ID: 1 |
|F4 004 LU name: OMPGW001 |
|F4 004 Task no.: 0000110 |
|F4 004 Protocol: SQLDS via SNA |
|F4 004 User ID: OLIVER SQL ID: OLIVER |
|F4 004 User is inactive. |
|F4 004 State started: 1999-03-15 10:56:29 |
|F4 004 Conversation started: 1999-03-15 10:56:28 |
|F4 004 CPU time: 00:00:00 |
|F4 004 LUWID: G9151F6D.AE07.02DB15155638.0000 |
|F4 004 EXTNAM: db2bp.exe 324851734 015E017D |
|F4 004 Requester: DB2 Connect 5.2 (DB2/NT) at BEDROCK |
|F4 004 Protocol: DRDA via TCP/IP |
|F4 004 3 SQL/DS remote users are active. |
|F4 004 2 SQL/DS remote users are inactive. |
|F4 004 1 SQL/DS agents are available. |
|F4 004 1 SQL/DS remote connections are available. |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 40. A Typical SHOW CONNECT Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show connect |
|Status of Connected Users 1996-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 LPAGEBUFF=1032 |
| State started: 1996-02-04 10:15:30 |
| Conversation started: 1996-02-04 10:12:45 |
| CPU time: 00:00:01 |
| Protocol: SQLDS via SNA |
| 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: 1996-02-04 09:48:28 |
| Conversation started: 1996-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 |
| Protocol: SQLDS via SNA |
| User Agent: 3 User-ID: PETERSON SQL-ID: PETERSON |
| is R/O APPL 3758 |
| Agent is processing and is in LOCK wait. |
| State started: 1996-02-04 10:23:11 |
| Conversation started: 1996-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 |
| Protocol: DRDA via SNA |
| User Agent: 4 User-ID: VSEMCH10 SQL-ID: SQLDBA |
| is R/O SUBS 1796 |
| Agent is not processing and is in communication wait. |
| State started: 1996-09-08 10:42:55 |
| Conversation started: 1996-09-08 10:42:43 |
| Task no.: 371 RMID: 12 Term. id: N/A |
| User-ID: SWAGRMAN SQL-ID: SQLDBA |
| User is waiting for an agent |
| State started: 1992-02-04 10:22:11 |
| Conversation started: 1992-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 |
| Protocol: SQLDS via SNA |
| User-ID: JAVIER SQL-ID: JAVIER |
| User is inactive |
| State started: 1992-02-04 10:02:11 |
| Conversation started: 1992-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 |
| Protocol: SQLDS via SNA |
| 4 SQL/DS users are active. |
| 1 SQL/DS users are waiting. |
| 1 SQL/DS users are inactive. |
| 0 SQL/DS agents are available. |
| 94 SQL/DS user connections are available. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
The SHOW CONNECT output values have these meanings:
with the following connection:
| Note: | The LUWID, EXTNAM, and Requester lines are not displayed in the following
situations:
|
When DB2 Server for VM is performing resynchronization initialization with the CRR recovery server, its status may be seen by the SHOW CONNECT. The following message is displayed:
Recovery Agent is processing Resynchronization Initialization
If the database manager is in a communications wait, waiting for the CRR recovery server to reply to its exchange log names request, the following message is displayed:
Recovery Agent is processing Resynchronization Initialization and is in a communications wait with the CRR Recovery Server
When the CRR recovery server is performing resynchronization recovery with DB2 Server for VM, its status may be seen by the SHOW CONNECT. The following message is displayed:
Recovery Agent is processing Resynchronization Recovery
If the database manager is in a communications wait, waiting for the CRR recovery server to acknowledge its exchange log names and compare states replies, following message is displayed:
Recovery Agent is processing Resynchronization Recovery and is in a communications wait with the CRR Recovery Server
If the database manager is committing or rolling back the logical unit of work requested by the CRR recovery server, the following message is displayed:
Recovery Agent is processing Resynchronization Recovery and is waiting for a <commit|rollback> to complete.
This information is available in tokenized format 1 .
If a stored procedure is executing when the command is invoked, the output shows the name of the stored procedure, the name of the module or phase associated with it, the name of the stored procedure package, and the number of the section that is running. This information is displayed only if the command is issued from the operator console, or if the user issuing the command has DBA authority.
If an agent is waiting for a stored procedure server, this is indicated in the output displayed for that agent.
If an agent is executing a stored procedure when the SHOW CONNECT command is invoked, any package information displayed will be for the stored procedure and not the package that issued the CALL statement. Following is an example of the output you might receive.
Figure 41. A Typical SHOW CONNECT Display
+--------------------------------------------------------------------------------+
| show connect |
| Status of Connected Users 1997-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: 1997-09-30 08:56:39 |
| Conversation started: 1997-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: 1997-09-30 08:56:39 |
| Conversation started: 1997-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: 1997-09-30 08:56:39 |
| Conversation started: 1997-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: 1997-09-30 08:58:00 |
| Conversation started: 1997-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. |
+--------------------------------------------------------------------------------+
>>-SHOW--CRR--LOGNAMES----------------------------------------->< |
The SHOW CRR LOGNAMES command can be used when log name mismatch situations arise between the database manager and VM/CRR. When this command is issued, the database manager displays:
Because the CRR recovery server is local to the database manager, the LUNAME is *IDENT and is not displayed.
Sample output is as follows:
Figure 42. Sample output from SHOW CRR LOGNAMES command (SYNCPNT=Y)
+--------------------------------------------------------------------------------+
|show crr lognames |
| SHOW CRR LOGNAMES entered at DATE='mm-dd-yyyy' TIME='hh:mm:ss' |
| Log Name information for the database manager: |
| TPN: tpn |
| LOG NAME: log name |
| Log Name information for the CRR recovery server: |
| TPN: tpn |
| LOG NAME: log name |
| At the last Resynchronization Initialization, |
| the log status was warm|cold. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
| Note: | When SYNCPNT=N, this command may still be executed. Sample output is as follows: |
Figure 43. Sample output from SHOW CRR LOGNAMES command (SYNCPNT=N)
+--------------------------------------------------------------------------------+
|show crr lognames |
| SHOW CRR LOGNAMES entered at DATE='mm-dd-yyyy' TIME='hh:mm:ss' |
| Log Name information for the database manager: |
| TPN: tpn |
| LOG NAME: log name |
| Log Name information for the CRR recovery server: |
| TPN: tpn |
| LOG NAME: log name |
| There is no log status since SYNCPNT = N |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW DBCONFIG----------------------------------------------->< |
The SHOW DBCONFIG command displays the database configuration chosen when the database was originally generated. It can help you to determine whether you can increase the size of the database, by displaying its potential maximum size.
Figure 44. A Typical SHOW DBCONFIG Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show dbconfig |
|F4 004 System identification at DB generation = DB2 Server for VSE & VM 6.1 |
|F4 004 |
|F4 004 DBA specified the following at DB definition: |
|F4 004 Maximum pools = 32 |
|F4 004 Maximum DBEXTENTS = 64 |
|F4 004 Maximum DBSPACES = 1000 |
|F4 004 |
|F4 004 Computed: |
|F4 004 Total number of DBSPACE blocks = 3582 |
|F4 004 Total amount of DBSPACE = 916992 K |
|F4 004 Total number of physical pages = 233472 |
|F4 004 Total amount of physical space = 933888 K |
|F4 004 Total number of directory blocks = 3800 |
|F4 004 |
|F4 004 Number of DBSPACE blocks left = 400 |
+--------------------------------------------------------------------------------+
Figure 45. A Typical SHOW DBCONFIG Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show dbconfig |
|System identification at DB generation = DB2 Server for VSE & VM 6.1 |
| |
|DBA specified the following at DB definition: |
| Maximum pools = 32 |
| Maximum DBEXTENTS = 64 |
| Maximum DBSPACES = 1000 |
| |
|Computed: |
| Total number of DBSPACE blocks = 3582 |
| Total amount of DBSPACE = 916992 K |
| Total number of physical pages = 233472 |
| Total amount of physical space = 933888 K |
| Total number of directory blocks = 3800 |
| |
| Number of DBSPACE blocks left = 400 |
+--------------------------------------------------------------------------------+
SHOW DBCONFIG displays this information:
It is possible to reach the maximum physical page limit (see "Total number of physical pages" below) before reaching the maximum number of dbextents. For information on what to do if you reach the maximum physical page limit, refer to the DB2 Server for VSE & VM Database Administration.
These values, which represent the potential database size, are also shown:
TOTAL AMOUNT OF DBSPACE = total number of dbspace blocks * 256K |
The number of dbspace blocks left determines the amount of dbspace that you can add to the database. This is the total number of blocks not yet allocated to a dbspace, and available for future ADD DBSPACE functions. Every 128 pages of public or private dbspace defined requires two dbspace blocks. Every 128 pages of internal dbspace defined requires one dbspace block.
>>-SHOW DBEXTENT----------------------------------------------->< |
A dbextent is the physical medium where the database manager stores data. Internally, all database extents appear like a continuous DASD address space of physical 4096-byte (4-kilobyte) pages. A storage pool is made up of one or more database extents.
The SHOW DBEXTENT command displays physical storage information about each storage pool defined. You can use this command to monitor physical storage usage. The SHOW DBEXTENT command gives output identical to the SHOW POOL command with the SUMMARY parameter. For a more detailed display of the dbextents and pools, refer to SHOW POOL.
The following examples show the output of the SHOW DBEXTENT command.
Figure 46. A Typical SHOW DBEXTENT Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show dbextent |
|F4 004 POOL TOTAL NO. OF NO. OF NO. OF % NO. OF |
|F4 004 NO. PAGES PAGES USED FREE PAGES RESV PAGES USED EXTENTS SOS |
|F4 004 1 741 178 563 20 24 1 |
|F4 004 2 171 11 160 20 6 1 |
|F4 004 3 114 0 114 20 0 1 |
|F4 004 4 114 0 114 20 0 1 |
|F4 004 FREE 22500 |
+--------------------------------------------------------------------------------+
Figure 47. A Typical SHOW DBEXTENT Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|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 178 563 20 24 1 |
| 2 171 11 160 20 6 1 |
| 3 114 0 114 20 0 1 |
| 4 114 0 114 20 0 1 |
|FREE 22500 |
+--------------------------------------------------------------------------------+
The columns of the output of SHOW DBEXTENT have these meanings:
For example, -54 indicates that storage pool number 54 is nonrecoverable.
>>-SHOW DBSPACE--n--------------------------------------------->< |
The database manager stores data in logical collections called dbspaces. A dbspace consists of multiples of 128 pages of linear space. There are three types of pages in a dbspace: header, data, and index.
Header pages contain control information about the contents of the dbspace. Data pages contain the tables created in the dbspace. Index pages contain the indexes on tables created in the dbspace. You choose the number of pages for each type when you acquire the dbspace. After you specify these numbers, you cannot change them.
SHOW DBSPACE lets you monitor the number of pages used by any public or private dbspace. In the SHOW DBSPACE command, n is the number of the dbspace you want to show. The dbspace number is in the DBSPACENO column of the SYSDBSPACES catalog. Note that dbspace 1 is the catalog dbspace. The following is an example of the SQL SELECT statement for obtaining a dbspace number:
SELECT DBSPACENO FROM SYSTEM.SYSDBSPACES WHERE DBSPACENAME = <name of desired dbspace> |
You can enter this SELECT statement from the ISQL facility or from the DBS Utility.
The SHOW DBSPACE command may take a long time to be completed if the dbspace is large.
The columns of output resulting from a SHOW DBSPACE command have the following meanings:
The following examples show the output of the SHOW DBSPACE command.
Figure 48. A Typical SHOW DBSPACE Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show dbspace 3 |
|F4 004 TYPE NUMBER NUMBER OF % FREE NUMBER OF |
|F4 004 OF PAGES OF PAGES OCCUPIED PAGES SPACE EMPTY PAGES |
|F4 004 HEADER 8 1 ( 12 %) 73 % 0 |
|F4 004 DATA 1365 0 ( 0 %) * 0 |
|F4 004 INDEX 128 27 ( 21 %) 79 % 18 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 49. A Typical SHOW DBSPACE Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|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 0 ( 0 %) * 0 |
| INDEX 128 27 ( 21 %) 79 % 18 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW INDOUBT------------------------------------------------>< |
The SHOW INDOUBT command displays the heuristic decisions made on in-doubt LUWs. SHOW INDOUBT displays the TRANID, USERID, LUWID, EXTNAM, REQUESTER, PACKAGE, STATE, DAMAGE, Prepared-To-Commit state started timestamp, and Heuristic state started timestamp for all in-doubt LUWs involved in two phase commits. In a VSE system, SHOW INDOUBT also displays RECTOK information.
| Note: | Any guest sharing agents which have been forced will not be displayed in SHOW INDOUBT outputs. |
The following are examples of the SHOW INDOUBT command.
Figure 50. Sample output from SHOW INDOUBT command on VM
+--------------------------------------------------------------------------------+
|show indoubt |
| TRANID: 1 User ID: FRED |
| is prepared for COMMIT or ROLLBACK. |
| LUWID: CAIBMOML.OMXRCB0A.ACA86699EA6A.0001 |
| EXTNAM: DB2BP.EXE 00940001 |
| Requester: DDCS/2 V2.1.0 at BEDROCK |
| Package: NULLID.SQLC24D0 Section: 201 |
| PTC State started: 1996-04-02 10:11:18 |
| Heuristic state started: N/A |
| Damage: No |
| TRANID: 2 User ID: BARNEY |
| is COMMITTED-H. |
| LUWID: CAIBMOML.OMXRCB0A.ACA86699EA6A.0002 |
| EXTNAM: DB2BP.EXE 00940001 |
| Requester: DDCS/2 V2.1.0 at BEDROCK |
| Package: NULLID.SQLC24D0 Section: 201 |
| PTC State started: 1996-04-02 09:13:27 |
| Heuristic state started: 1996-04-02 09:52:03 |
| Damage: Yes |
+--------------------------------------------------------------------------------+
Figure 51. Sample output from SHOW INDOUBT command on VSE
+--------------------------------------------------------------------------------+
|4 show indoubt |
|F4 004 TRANID: 1 User ID: WILMA |
|F4 004 is prepared for COMMIT or ROLLBACK. |
|F4 004 LUWID: CAIBMOML.OMXRCB0A.ACA86699EA6A.0001 |
|F4 004 EXTNAM: DB2BP.EXE 00940001 |
|F4 004 Requester: DDCS/2 V2.1.0 at BEDROCK |
|F4 004 Package: NULLID.SQLC24D0 Section: 201 |
|F4 004 PTC State started: 1996-04-02 10:11:18 |
|F4 004 Heuristic state started: N/A |
|F4 004 Damage: No |
|F4 004 RECTOK: X'AC89520F AB638400' |
|F4 004 TRANID: 2 User ID: BETTY |
|F4 004 is ROLLEDBACK-H. |
|F4 004 LUWID: CAIBMOML.OMXRCB0A.ACA86699EA6A.0002 |
|F4 004 EXTNAM: DB2BP.EXE 00940001 |
|F4 004 Requester: DDCS/2 V2.1.0 at BEDROCK |
|F4 004 Package: NULLID.SQLC24D0 Section: 201 |
|F4 004 PTC State started: 1996-04-02 09:13:27 |
|F4 004 Heuristic state started: 1996-04-02 09:52:03 |
|F4 004 Damage: Yes |
|F4 004 RECTOK: X'AC935204 AB637432' |
+--------------------------------------------------------------------------------+
SHOW INDOUBT displays one of the following three messages after the user ID for each in-doubt LUW:
>>-SHOW INITPARM----------------------------------------------->< |
The SHOW INITPARM command displays the current settings of each of the initialization parameters. This command can be used to decide whether fine-tuning of these parameters should be performed.
The following are examples of the SHOW INITPARM command.
Figure 52. SHOW INITPARM Display for VM
+--------------------------------------------------------------------------------+
| System: ARI0045I Ready for operator communications. |
| User: show initparm |
| |
| System: Initialization parameters at 1996-02-27 15:29:27 |
| |
| AMODE 24 DUALLOG N PROTOCOL SQLDS |
| SYNCPNT N ACCOUNT N DBMODE G |
| DUMPTYPE N LOGMODE Y STARTUP W |
| SYSMODE M EXTEND N CHARNAME INTERNATIONAL|
| DBNAME SQLMACH1 RESID SQLMACH1 |
| PARMID TRACDBSS 00000000000 TRACDSC 00 |
| TRACRDS 0002100* TRACCONV 0 TRACSTG 1 |
| TRACEBUF 0 ARCHPCT 80 CHKINTVL 10 |
| NCSCANS 30 NCUSERS 5 NDIRBUF 30 |
| NLRBS 2520 NLRBU 1000 NPACKAGE 10 |
| NPACKPCT 30 NPAGBUF 30 SLOGCUSH 90 |
| SOSLEVEL 10 DISPBIAS 7 LTIMEOUT 0 |
| DSPSTATS 21 SECALVER N SECTYPE DB2 |
| SERVICE SQLMACH1 TCPPORT 6100 IPADDR 9.33.33.33 |
| HOST thehost.system.ibm.com PTIMEOUT 180 |
| PROCMXAB 0 |
| |
| ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 53. SHOW INITPARM Display for VSE
+--------------------------------------------------------------------------------+
| System: F4-004 ARI0062A SQLDS : |
| Enter a DB2 Server for VSE operator command. |
| User: 4 show initparm |
| System: F4 004 |
| F4 004 Initialization parameters at 1996-02-27 20:30:20 |
| F4 004 |
| F4 004 DSPLYDEV B DUALLOG N SYNCPNT N |
| F4 004 RMTUSERS 0 ACCOUNT N DUMPTYPE F |
| F4 004 LOGMODE Y STARTUP W SYSMODE M |
| F4 004 EXTEND N CHARNAME INTERNATIONAL |
| F4 004 DBNAME SQLDS APPLID SYSARI|
| F4 004 PARMID TRACDBSS 00000000000 TRACDSC 00 |
| F4 004 TRACRDS 0000000 TRACCONV 0 TRACSTG 0 |
| F4 004 TRACEBUF 0 ARCHPCT 80 CHKINTVL 10 |
| F4 004 NCSCANS 30 NCUSERS 5 NDIRBUF 30 |
| F4 004 NLRBS 2520 NLRBU 1000 NPACKAGE 10 |
| F4 004 NPACKPCT 30 NPAGBUF 30 SLOGCUSH 90 |
| F4 004 SOSLEVEL 10 DISPBIAS 7 LTIMEOUT 0 |
| F4 004 DSPSTATS 21 PTIMEOUT 55 PROCMXAB 0 |
| F4 004 TCPPORT 8000 HOST VSEMCH01 IPADDR 9.21.23|
| F4 004 |
| F4 004 ARI0065I Operator command processing is complete. |
| |
+--------------------------------------------------------------------------------+
Notes:
>>-SHOW INVALID------------------------------------------------>< |
The SHOW INVALID command displays the entities in the application server that are invalid or transient. Only indexes can become invalid or transient.
An index can become invalid (unusable) if a system error occurs that affects the particular index, or if the application server ends abnormally during an operation involving an index and the system is restarted without a log.
An index becomes transient when a logical unit of work (LUW) is performing an operation that puts the index in a temporarily inconsistent state. The index becomes valid again after the operation or LUW is completed successfully. Some operations make an index transient for the operation only. Others make an index transient for the life of the LUW. A transient index should not cause concern, because the index will become valid during normal processing.
The columns of output from a SHOW INVALID command have the following meanings:
| Note: | An index in the system catalog dbspace (SYS0001) cannot be marked invalid. It can however be marked transient. The application server will end if an attempt is made to invalidate an index in this dbspace. |
SELECT A.DBSPACENO, B.IID, B.INAME, B.CREATOR, A.TNAME
FROM SYSTEM.SYSCATALOG A, SYSTEM.SYSINDEXES B
WHERE A.TNAME = B.TNAME AND A.CREATOR = B.CREATOR
AND A.DBSPACENO IN (dbspace-no-list)
AND B.IID IN (index-no-list)
ORDER BY A.DBSPACENO, B.IID
|
where dbspace-no-list and index-no-list are lists of the invalid index IDs and their dbspace numbers.
1. For invalid indexes:
If an index appears in the SHOW INVALID display but does not appear in the catalog, the changes to the application server from a CREATE INDEX command were not recovered after a system failure.
If an index appears in the SHOW INVALID display and in the catalog but you cannot drop or reorganize it, the changes to the application server from a REORGANIZE INDEX command were not recovered after a system failure.
In both of these instances, either drop and recreate the dbspace containing the index, or restore a previous database archive.
To recover, the user drops the index, removes duplicates from the table, and recreates the index. After the index is dropped, the following statement displays the duplicate keys:
SELECT column-name FROM table-name - GROUP BY column-name - HAVING COUNT (*) > 1 |
where:
The user can then remove the duplicates by deleting or updating the rows. The user can then recreate the index. If the index was created in support of a primary key or unique constraint, the user must deactivate the primary key or unique constraint, remove the duplicates, and then activate the primary key or unique constraint again.
2. For transient indexes:
Note that because an index can become transient for an operation or an LUW, an index can be transient for more than one reason.
The following examples show the output of the SHOW INVALID command.
Figure 54. A Typical SHOW INVALID Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show invalid |
| |
|INVALID ENTITIES: 2 |
| DBSPACE ENTITY |
|ENTITY NUMBER ID DESCRIPTION |
|INDEX 15 -32342 No room |
|INDEX 11 -32849 Duplicates Exist in UNIQUE INDEX |
| |
|TRANSIENT ENTITIES: 3 |
| DBSPACE ENTITY |
|ENTITY NUMBER ID DESCRIPTION |
|INDEX 13 -31816 Incomplete Index due to CREATE/REORGANIZE |
|INDEX 11 -33014 Searched UPDATE with deferring |
| Incomplete Index due to CREATE/REORGANIZE |
|INDEX 11 -32849 Searched UPDATE with deferring |
| |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 55. A Typical SHOW INVALID Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show invalid |
|F4 004 |
|F4 004 INVALID ENTITIES: 2 |
|F4 004 DBSPACE ENTITY |
|F4 004 ENTITY NUMBER ID DESCRIPTION |
|F4 004 INDEX 15 -32342 No room |
|F4 004 INDEX 11 -32849 Duplicates Exist in UNIQUE INDEX |
|F4 004 |
|F4 004 TRANSIENT ENTITIES: 3 |
|F4 004 DBSPACE ENTITY |
|F4 004 ENTITY NUMBER ID DESCRIPTION |
|F4 004 INDEX 13 -31816 Incomplete Index due to CREATE/REORGANIZ|
|F4 004 INDEX 11 -33014 Searched UPDATE with deferring |
|F4 004 Incomplete Index due to CREATE/REORGANIZ|
|F4 004 INDEX 11 -32849 Searched UPDATE with deferring |
|F4 004 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
See the DB2 Server for VSE & VM Diagnosis Guide and Reference for more information about invalid and transient indexes.
>>-SHOW LOCK ACTIVE-------------------------------------------->< |
The SHOW LOCK ACTIVE command displays locking information for agent structures. An agent structure is the internal representation of an active user (DB2 Server for VSE & VM application program, ISQL user, or system agent such as checkpoint). The following is shown for each agent structure:
Note that the agent identifier for the checkpoint agent is C.
Note that the authorization ID of the checkpoint agent is CHECKPT.
If the database manager is in the process of verifying the authorization ID, it is not shown. The string null id is shown instead.
If you are using the VM/ESA operating systems, a VM user can be connected to an application server under one or more different authorization IDs, within separate CMS units of work with one work unit waiting for another. To see if a user is in this wait state, enter the SHOW USERS command. See Figure 82 for information on what to do when a VM user is in a wait state for a resource or database under one or more authorization IDs.
The database lock is for log archive checkpoints. The database manager always takes a checkpoint before creating a log archive. The checkpoint agent acquires a database lock with short duration in exclusive (X) mode when taking the checkpoint. The checkpoint agent's acquisition of the database lock ensures that no logical units of work are active when the checkpoint that precedes the log archive is in progress. The checkpoint agent releases the database lock when the checkpoint ends.
For a lock type of SYS, you should use the SHOW ACTIVE command to display the status of the active agent structures.
SELECT * FROM SYSTEM.SYSDBSPACES WHERE DBSPACENO = n |
Specify the number of the dbspace for n.
The following examples show the output of the SHOW LOCK ACTIVE command.
Figure 56. A Typical SHOW LOCK ACTIVE Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show lock active |
|F4 004 WAIT TOTAL LONG WANTLOCK WANTLOCK |
|F4 004 AGENT USER STATE LOCKS LOCKS TYPE DBSPACE |
|F4 004 C CHECKPT NIW 0 0 |
|F4 004 1 JESSICA I/O 140 100 |
|F4 004 2 LEIGH LOCK 226 220 DBSP 12 |
|F4 004 3 MARY COMM 97 49 |
|F4 004 4 MIKE LOCK 108 101 DBSP 12 |
|F4 004 5 BOB LOCK 107 98 PAGE 14 |
|F4 004 6 IRENE LOCK 635 623 DBSP 14 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 57. A Typical SHOW LOCK ACTIVE Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show lock active |
| WAIT TOTAL LONG WANTLOCK WANTLOCK |
|AGENT USER STATE LOCKS LOCKS TYPE DBSPACE |
|C CHECKPT NIW 0 0 |
|1 JESSICA I/O 140 100 |
|2 LEIGH LOCK 226 220 DBSP 12 |
|3 MARY COMM 97 49 |
|4 MIKE LOCK 108 101 DBSP 12 |
|5 BOB LOCK 107 98 PAGE 14 |
|6 IRENE LOCK 635 623 DBSP 14 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW LOCK DBSPACE----+-ALL-+--------------------------------><
'-n---'
|
The SHOW LOCK DBSPACE command displays information about locks within a public or private dbspace. If you specify ALL, you see information for all public and private dbspaces that have lock activity. If you want information for a particular dbspace, specify the number of the dbspace for n. You must specify either ALL or n.
To display the number of a dbspace, query the DB2 Server for VSE & VM catalog SYSTEM.SYSDBSPACES. The DBSPACENO column of that table contains the dbspace number. For example, this SELECT statement finds the dbspace number for MYSPACE:
SELECT DBSPACENO FROM SYSTEM.SYSDBSPACES WHERE DBSPACENAME='MYSPACE' |
You can enter this SELECT statement from the ISQL facility or the DBS Utility. If dbspace number 1 is displayed, the catalog tables are locked.
For the dbspaces you specify in SHOW LOCK DBSPACE, the database manager displays a line for each lock type that is held within that dbspace. The following information is displayed in the output lines:
(One line is displayed for each active lock type within a dbspace.)
For more information about lock modes, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.
The following examples show the output of the SHOW LOCK DBSPACE command.
Figure 58. A Typical SHOW LOCK DBSPACE Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show lock dbspace 12 |
|F4 004 DBSPACE LOCK NUMBER |
|F4 004 NUMBER TYPE IN SIX IS IX S U X Z WAITERS |
|F4 004 |
|F4 004 12 DBSP 0 0 0 3 0 0 0 0 0 |
|F4 004 12 IKEY 0 0 0 3 38 0 0 0 0 |
|F4 004 12 ROW 0 0 0 3 16 0 0 0 0 |
|F4 004 12 TABL 0 0 0 3 0 0 0 0 0 |
|F4 004 12 DBSP 0 0 0 3 0 0 0 0 0 |
|F4 004 12 IPAG 0 0 0 3 0 0 0 0 0 |
|F4 004 12 PAGE 0 2 0 4 0 1 2 0 1 |
|F4 004 12 TABL 0 0 2 0 0 0 0 0 0 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 59. A Typical SHOW LOCK DBSPACE Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show lock dbspace 7 |
|DBSPACE LOCK NUMBER |
|NUMBER TYPE IN SIX IS IX S U X Z WAITERS |
|7 DBSP 1 0 0 2 0 0 0 0 0 |
|7 IKEY 0 0 0 0 0 0 2 0 0 |
|7 ROW 0 0 0 0 0 0 1 0 0 |
|7 TABL 1 0 0 1 0 0 0 0 1 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW LOCK GRAPH----+-+--------+--authorization_ID--+--------><
| '-USERID-' |
'-AGENT--agent_no---------------'
|
The SHOW LOCK GRAPH command displays information about the lock dependencies among agents. For the authorization ID (or agent number) you choose, SHOW LOCK GRAPH shows the users that must relinquish their locks before the specified user can get the lock.
| Note: | If you enter a nonunique authorization ID, the SHOW LOCK GRAPH command displays information about the lowest numbered agent that is processing for the authorization ID. This may not be the agent you are interested in. If not, enter a SHOW ACTIVE command to obtain the agent number. Then reenter the SHOW LOCK GRAPH command with the agent number. |
You can think of the SHOW LOCK GRAPH command as displaying a hierarchy of waiting users. For example, if you enter SHOW LOCK GRAPH AGENT 2, the display might show that agent 2 is waiting for agents 5 and 7, and that agent 7 is waiting for agents 1, 4, and 6.
As shown in the above command format, you must specify either an authorization ID or an agent number. If you specify an agent number, you must enter the AGENT keyword. The USERID keyword is optional when you specify an authorization ID.
To get information about the checkpoint agent, specify either the letter C for the agent number or CHECKPT for the authorization ID.
If you are using the VM/ESA operating systems, a VM user can be connected to an application server under one or more different authorization IDs, within separate CMS units of work with one work unit waiting for another. To see if a user is in this wait state, enter the SHOW USERS command. See Figure 82 for information on what to do when a VM user is in a wait state for a resource or database under one or more authorization IDs.
SHOW LOCK GRAPH displays the following:
or, in VM, if the agent is in communication wait, it is waiting for an SQL request from the user or for an APPC/VM function to end.
| Note: | A database lock is acquired with long duration in intention exclusive (IX) mode at the start of every logical unit of work. A special internal system lock can actually be a latch on a page or block buffer. This will show as a LATCH WAIT if the SHOW ACTIVE command is entered. |
The database lock is for log archive checkpoints. The database manager always takes a checkpoint before creating a log archive. When the database manager does a checkpoint, the checkpoint agent acquires a database lock with short duration in exclusive (X) mode. The checkpoint agent's acquisition of the database lock ensures that no logical units of work are active when the checkpoint that precedes the log archive is in progress. The checkpoint agent releases the database lock at the end of the checkpoint.
The values PAGE, ROW, IKEY, and IPAG are useful only for pattern matching during problem determination. The DBSP and TABL values correspond to the DBSPACENO and TABID columns in the DB2 Server for VSE & VM catalogs. For example, the following SELECT statement identifies the table by using the DBSP and TABL values:
SELECT TABID, TNAME FROM SYSTEM.SYSCATALOG WHERE TABID=tabl-value AND DBSPACENO=dbsp-value |
If some other user also held a lock on the resource, the requested mode could be incompatible. A conversion is not immediately possible. The user waits because the lock is waiting to be converted.
In this situation, the agent requested a lock that was not available. The agent indicated by its request that, if no lock was available, it would not wait for the lock. The database manager denied the request rather than put the agent in a lock wait. An example of this kind of request is a user trying to access a private dbspace that is currently in use. If the private dbspace is currently in use and the lock request is not compatible, the database manager does not let the lock request wait. Instead, the database manager denies the request.
For more information about lock modes, see the DB2 Server for VSE & VM Diagnosis Guide and Reference.
The following figures show that LEIGH (the specified user) is waiting for MARK. LEIGH wants a long lock on the entire dbspace 15. The WAIT STAT column on that first line shows that MARK (the LOCK HOLDER) is in a lock wait also.
The next line shows that MARK is waiting for a lock on DBSPACE 16, and that MIKE is holding that lock. The WAIT STAT column here shows that MIKE is also in a lock wait.
The third and fourth lines show that MIKE is waiting for CAROL and CAROL is waiting for STEVE. STEVE's agent is in a communication wait.
STEVE is delaying LEIGH, MARK, MIKE, and CAROL.
The final line shows that MARY is in a communication wait, and that CAROL is waiting for her. (STEVE and MARY are both holding the lock for which CAROL is waiting.)
Figure 60. A Typical SHOW LOCK GRAPH Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show lock graph leigh |
|F4 004 LOCK LOCK WAIT LOCK DBSP LOCK REQ REQ |
|F4 004 REQUESTER HOLDER STAT TYPE NUMBR QUALFIER STATE MODE DUR |
|F4 004 2 LEIGH 4 MARK LOCK DBSP 15 G WAIT S LONG |
|F4 004 4 MARK 5 MIKE LOCK DBSP 16 G WAIT X LONG |
|F4 004 5 MIKE 6 CAROL LOCK PAGE 21 88 G WAIT IS LONG |
|F4 004 6 CAROL 1 STEVE COMM DBSP 1 G WAIT IX LONG |
|F4 004 6 CAROL 3 MARY COMM DBSP 1 G WAIT IX LONG |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 61. A Typical SHOW LOCK GRAPH Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show lock graph leigh |
|LOCK LOCK WAIT LOCK DBSP LOCK REQ REQ |
|REQUESTER HOLDER STAT TYPE NUMBR QUALFIER STATE MODE DUR |
|2 LEIGH 4 MARK LOCK DBSP 15 G WAIT S LONG |
|4 MARK 5 MIKE LOCK DBSP 16 G WAIT X LONG |
|5 MIKE 6 CAROL LOCK PAGE 21 88 G WAIT IS LONG |
|6 CAROL 1 STEVE COMM DBSP 1 G WAIT IX LONG |
|6 CAROL 3 MARY COMM DBSP 1 G WAIT IX LONG |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.-MATRIX--. >>-SHOW LOCK----+---------+------------------------------------>< |
The SHOW LOCK MATRIX command displays information about the usage of lock request blocks, and displays a lock wait table. It is optional to type the keyword MATRIX. You can enter SHOW LOCK and get the same results.
If you are using the VM/ESA operating systems, a VM user can be connected to an application server under one or more different DB2 Server for VSE & VM authorization IDs, within separate CMS units of work with one work unit waiting for another. To see if a user is in this wait state, enter the SHOW USERS command. See Figure 82 for information on what to do when a VM user is in a wait state for a resource or database under one or more authorization IDs.
Lock request blocks are the internal control blocks that the database manager uses for data sharing. The following information is displayed about lock request block usage:
If you are monitoring IN USE and MAX USED BY LUW, and only one application is active, MAX USED BY LUW is generally greater than IN USE. An application does not keep all of the lock request blocks while it runs. An application will keep some lock request blocks until it issues an SQL COMMIT WORK or ROLLBACK WORK but frees others after a very short time.
The application server resets MAX USED BY LUW to 0 any time an application reaches either the NLRBU or NLRBS limit and the lock escalation process occurs.
You can use MAX USED BY LUW to determine the lock request block requirements for preprocessing a DB2 Server for VSE & VM application program or for running an application that uses many locks. You use this support when the application server is running with NCUSERS=1.
The lock wait table is an n x n matrix where n is the number of users (NCUSERS). This matrix shows which users are waiting for locks and which are holding them. You can use this display to find users who are creating a bottleneck. Note that both SHOW LOCK GRAPH and SHOW LOCK MATRIX show the lock hierarchy, but they show it in different representations.
When the checkpoint agent is in lock contention because of a log archive checkpoint, the checkpoint agent is shown in the matrix along with the user agents. The database manager displays the agent identifier and authorization ID for the checkpoint agent in the first row and column of the matrix. C is the agent identifier, and CHECKPT is the authorization ID.
The entry in the matrix is the dbspace number where the lock contention has occurred. You can use this number on the SHOW LOCK DBSPACE command to find out more about the locking occurring in that dbspace. If lock contention has occurred on an internal system lock, the entry in the matrix is SYS or DB. Refer to SHOW LOCK ACTIVE for more detail on these lock types. If the entry in the matrix shows lock contention, but no agent authorization ID, the application server is in the process of verifying CONNECT authority for the authorization ID.
In the console examples that follow, user agent 4 (MARY) is waiting for a lock that user agent 1 (CAROL) is holding. The lock is for some resource in dbspace number 12.
Figure 62. A Typical SHOW LOCK MATRIX Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show lock |
|F4 004 Lock Request Block (LRB) and Lock Status: |
|F4 004 NLRBS IN USE FREE NLRBU MAX USED BY LUW |
|F4 004 ------- ------- ------- ------- --------------- |
|F4 004 2510 490 2020 1000 208 |
|F4 004 *** THE LOCKWAIT TABLE *** |
|F4 004 ENTRY = DBSPACE NUMBER ON WHICH THERE IS LOCK CONTENTION |
|F4 004 The presence of an entry shows |
|F4 004 the agent requesting the lock and |
|F4 004 the agent contending for or holding the lock. |
|F4 004 AGENT AGENT CONTENDING FOR OR HOLDING THE LOCK |
|F4 004 REQUESTING |
|F4 004 LOCK |
|F4 004 1 2 3 4 5 |
|F4 004 CAROL BRUCE EDWARD MARY |
|F4 004 1 CAROL ........ ........ ........ ........ ........ |
|F4 004 2 BRUCE ........ ........ ........ ........ ........ |
|F4 004 3 EDWARD ........ ........ ........ ........ ........ |
|F4 004 4 MARY 12 ........ ........ ........ ........ |
|F4 004 5 ........ ........ ........ ........ ........ |
|F4 004 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 63. A Typical SHOW LOCK MATRIX Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show lock |
|Lock Request Block (LRB) and Lock Status: |
| NLRBS IN USE FREE NLRBU MAX USED BY LUW |
|------- ------- ------- ------- --------------- |
| 2510 490 2020 1000 208 |
| *** 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 |
| CAROL BRUCE EDWARD MARY |
|1 CAROL ............................................. |
|2 BRUCE ............................................. |
|3 EDWARD ............................................. |
|4 MARY 12 ........................................ |
|5 ............................................. |
| |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.-ALL-------------------------------.
>>-SHOW LOCK USER----+-----------------------------------+-----><
'-+-+--------+--authorization ID--+-'
| '-USERID-' |
'-AGENT--agent_no---------------'
|
The SHOW LOCK USER command displays locking information for a set of users. You can get locking information for all users that are active within the database manager by specifying the ALL parameter. ALL is the default. For information about a specific user, specify either the authorization ID or the agent number for the user. If you specify an agent number, you must specify the AGENT keyword. The USERID keyword is optional when you are specifying an authorization ID.
You can obtain information about users that are waiting with the SHOW LOCK WANTLOCK and SHOW LOCK MATRIX commands.
| Note: | If you enter a nonunique authorization ID, the SHOW LOCK USER command displays information about the lowest numbered agent that is processing for the specified authorization ID. This may not be the agent you are interested in. If it is not, enter a SHOW ACTIVE command to obtain the agent number. Then reenter the SHOW LOCK USER command and specify the agent number. |
To get information about the checkpoint agent, specify either the letter C for the agent number or CHECKPT for the authorization ID.
There is one line of output for each resource or database that is locked by or is pending locking by a particular user. The lines are sorted by dbspace number and, when you specify ALL, by agent number. The following is displayed for each user:
The agent identifier for the checkpoint agent is C.
Note that the authorization ID of the checkpoint agent is CHECKPT.
If you are using the VM/ESA operating systems, a VM user can be connected to an application server under one or more different authorization IDs, within separate CMS units of work, with one work unit waiting for another. To see if a user is in this wait state, enter the SHOW USERS command. See Figure 82 for information on what to do when a VM user is in a wait state for a resource or database under one or more authorization IDs.
| Note: | For internal dbspaces (lock type is INT) a unique dbspace identifier is displayed. This identifier is generated internally and does not correspond to the DBSPACENO column of SYSTEM.SYSCATALOG and SYSTEM.SYSDBSPACES. |
| Note: | A database lock is acquired with long duration in intention exclusive (IX) mode at the start of every logical unit of work. A special internal system lock can actually be a latch on a page or block buffer. This will show as a LATCH WAIT if the SHOW ACTIVE command is entered. |
The database lock is for log archive checkpoints. The database manager always takes a checkpoint before creating a log archive. The checkpoint agent acquires a database lock with short duration in exclusive (X) mode when doing the checkpoint. The checkpoint agent's acquisition of the database lock ensures that no logical units of work are active when the checkpoint that precedes the log archive is in progress. The checkpoint agent releases the database lock at the end of the checkpoint.
(One line is displayed for each active lock type within a dbspace.)
For more information about lock modes, see the DB2 Server for VSE & VM Diagnosis Guide and Reference.
The following examples show the output of the SHOW LOCK USER command.
Figure 64. A Typical SHOW LOCK USER Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show lock user jessica |
|F4 004 DBSPACE LOCK NUMBE|
|F4 004 AGENT USER NUMBER TYPE IN SIX IS IX S U X Z WAITE|
|F4 004 7 JESSICA DBSP 1 0 0 2 0 0 0 0 0 |
|F4 004 7 JESSICA IKEY 0 0 0 0 0 0 2 0 0 |
|F4 004 7 JESSICA ROW 0 0 0 0 0 0 1 0 0 |
|F4 004 7 JESSICA TABL 1 0 0 1 0 0 0 0 1 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 65. A Typical SHOW LOCK USER Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show lock user all |
| DBSPACE LOCK NUMBER |
|AGENT USER NUMBER TYPE IN SIX IS IX S U X Z WAITERS |
| 1 SQLDBA DB 0 0 0 1 0 0 0 0 0 |
| 1 SQLDBA 1 DBSP 0 0 1 0 0 0 0 0 0 |
| 1 SQLDBA 1 ROW 0 0 0 0 1 0 0 0 0 |
| 1 SQLDBA 1 TABL 0 0 1 0 0 0 0 0 0 |
| 1 SQLDBA 7 DBSP 0 0 0 1 0 0 0 0 0 |
| 1 SQLDBA 7 IKEY 0 0 0 0 0 0 2 0 0 |
| 1 SQLDBA 7 ROW 0 0 0 0 0 0 1 0 0 |
| 1 SQLDBA 7 TABL 0 0 0 1 0 0 0 0 0 |
| 2 SQLUSRH DB 0 0 0 1 0 0 0 0 0 |
| 2 SQLUSRH 1 DBSP 0 0 1 0 0 0 0 0 0 |
| 2 SQLUSRH 1 ROW 0 0 0 0 1 0 0 0 0 |
| 2 SQLUSRH 1 TABL 0 0 1 0 0 0 0 0 0 |
| 2 SQLUSRH 7 DBSP 1 0 0 0 0 0 0 0 0 |
| 2 SQLUSRH 7 TABL 1 0 0 0 0 0 0 0 1 |
| 3 SQLUSRJH DB 0 0 0 1 0 0 0 0 0 |
| 3 SQLUSRJH 1 DBSP 0 0 1 0 0 0 0 0 0 |
| 3 SQLUSRJH 1 ROW 0 0 0 0 1 0 0 0 0 |
| 3 SQLUSRJH 1 TABL 0 0 1 0 0 0 0 0 0 |
| 3 SQLUSRJH 7 DBSP 0 0 0 1 0 0 0 0 0 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.-ALL-------------------------------.
>>-SHOW LOCK WANTLOCK----+-----------------------------------+-><
'-+-+--------+--authorization ID--+-'
| '-USERID-' |
'-AGENT--agent_no---------------'
|
The SHOW LOCK WANTLOCK command displays information about a lock that an agent wants. The command also displays the queue of requests for the lock, including the user who is currently holding the lock.
To display information about all locks currently wanted by all active agents, specify ALL (the default). When you specify ALL, one line is displayed for each user that is in a lock wait.
To display information about the lock that a particular agent wants, specify either the agent number or the authorization ID for which the agent is doing work.
| Note: | If you enter a nonunique authorization ID, the SHOW LOCK WANTLOCK command displays information about the lowest numbered agent that is processing for the specified authorization ID. This may not be the agent you are interested in. If not, enter a SHOW ACTIVE command to obtain the agent number. Then reenter the SHOW LOCK WANTLOCK command with the agent number specified. |
If you specify an agent number, you must specify the AGENT keyword. The USERID keyword is optional when you are specifying an authorization ID. When an authorization ID or agent number is specified, the application server displays a waiting list of all users holding the lock and all users waiting for the lock. They are shown in the order in which they are waiting.
To display information about the checkpoint agent, specify either the letter C for the agent number or CHECKPT for the authorization ID.
The following is displayed for each lock that the agent (or agents) wants:
Note that the agent identifier for the checkpoint agent is the letter C.
Note that the authorization ID of the checkpoint agent is CHECKPT.
If you are using the VM/ESA operating systems, a VM user can be connected to an application server under one or more different authorization IDs, within separate CMS work units, with one work unit waiting for another. To see if a user is in this wait state, enter the SHOW USERS command. See Figure 82 for information on what to do when a VM user It is in a wait state for a resource or database under one or more authorization IDs.
| Note: | A database lock is acquired with long duration in intention exclusive (IX) mode at the start of every logical unit of work. A special internal system lock can actually be a latch on a page or block buffer. This will show as a LATCH WAIT if the SHOW ACTIVE command is entered. |
The database lock is for log archive checkpoints. The application server always takes a checkpoint before creating a log archive. The checkpoint agent acquires a database lock with short duration in exclusive (X) mode when doing the checkpoint. The checkpoint agent's acquisition of the database lock ensures that no logical units of work are active when the checkpoint that precedes the log archive is in progress. The checkpoint agent releases the database lock at the end of the checkpoint.
The values PAGE, ROW, IKEY, and IPAG are useful only for pattern matching during problem determination. The DBSP and TABL values, however, correspond to the DBSPACENO and TABID columns in the catalogs. For example, the following SELECT statement identifies the table by using the DBSP and TABL values:
SELECT TABID, TNAME FROM SYSTEM.SYSCATALOG WHERE TABID=tabl_value AND DBSPACENO=dbsp_value |
This is called lock conversion.
If another user also held a lock on the resource, the requested mode could be incompatible. A conversion is not immediately possible. The user waits because the lock is waiting to be converted.
In this case, the agent requested a lock that was not available. The agent indicated by its request that, if no lock was available, it would not wait for the lock. The database manager denied the request rather than put the agent in a lock wait. An example of this kind of request is a user trying to access a private dbspace that is currently in use. If the private dbspace is currently in use and the lock request is not compatible, the database manager does not let the lock request wait. Instead the application server denies the request.
For more information about lock modes, see the DB2 Server for VSE & VM Diagnosis Guide and Reference.
The following examples show the output from the SHOW LOCK WANTLOCK command.
Figure 66. A Typical SHOW LOCK WANTLOCK Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show lock wantlock |
|F4 004 LOCK DBSPACE LOCK REQUEST REQUEST |
|F4 004 AGENT USER TYPE NUMBER QUALIFIER STATE MODE DURATION |
|F4 004 5 CLIFF PAGE 12 88 G WAIT S LONG |
|F4 004 6 DOT PAGE 12 88 G WAIT X LONG |
|F4 004 4 MIKE PAGE 12 88 G WAIT S LONG |
|F4 004 2 BUTCH PAGE 12 88 G WAIT X LONG |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 67. A Typical SHOW LOCK WANTLOCK Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show lock wantlock |
| LOCK DBSPACE LOCK REQUEST REQUEST |
|AGENT USER TYPE NUMBER QUALIFIER STATE MODE DURATION |
| 5 CLIFF PAGE 12 88 G WAIT S LONG |
| 6 DOT PAGE 12 88 G WAIT X LONG |
| 4 MIKE PAGE 12 88 G WAIT S LONG |
| 2 BUTCH PAGE 12 88 G WAIT X LONG |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW LOG---------------------------------------------------->< |
The SHOW LOG command displays global log control information and status as follows:
before SLOGCUSH (the DB2 Server for VSE & VM log cushion) is reached. It also displays a list of agents that were active before the last checkpoint.
SLOGCUSH is a DB2 Server for VSE & VM initialization parameter value. When the log reaches this cushion, the log overflow procedure begins. This procedure rolls back the oldest logical unit of work that began before the last checkpoint.
When ARCHPCT is reached, the application server begins an archive. (ARCHPCT is a DB2 Server for VSE & VM initialization parameter.)
The following examples show the output from the SHOW LOG command.
Figure 68. A Typical SHOW LOG Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show log |
|F4 004 Log Status: |
|F4 004 Log size is 700416 bytes. |
|F4 004 Log used is 3 percent 23326 bytes. |
|F4 004 Log remaining before overflow procedure is 86 percent |
|F4 004 607048 bytes. |
|F4 004 No non-R/O agents began before the last checkpoint. |
|F4 004 Archiving is not enabled. |
|F4 004 Log pages remaining before checkpoint is 5. |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 69. A Typical SHOW LOG Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show log |
|Log Status: |
| Log size is 700416 bytes. |
| Log used is 3 percent 23326 bytes. |
| Log remaining before overflow procedure is 86 percent |
| 607048 bytes. |
| No non-R/O agents began before the last checkpoint. |
| Archiving is not enabled. |
| Log pages remaining before checkpoint is 5. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.-ALL----.
>>-SHOW LOGHIST----+--------+---+---------+--------------------><
'-+---+--' '-SERVICE-'
'-n-'
|
The log history is a 4-kilobyte page at the end of the log file. Its layout consists of a header area followed by multiple entries, each recording a particular log-related event. These events usually fall into one of the following categories:
For more information on the history area, see the DB2 Server for VSE System Administration, or DB2 Server for VM System Administration.
The SHOW LOGHIST command lets you view the log history without having to manually record the information.
The following rules apply to the parameters of the SHOW LOGHIST command:
The first line of output for the SHOW LOGHIST command has three possible responses:
The most recent archive date and time will be displayed even if you request too few entries to have the output extend as far back as the time of that archive.
The entry number (1 represents the first entry listed) is shown to allow you to resubmit the command, if necessary, to display as far back as that archive.
| Note: | Before Version 3 Release 1, DATE and TIME entries for ARCHIVE and RESTORE, with the exception of End of restore set, displayed date and time information respectively. |
The following examples show the output from the SHOW LOGHIST command.
Figure 70. A Typical SHOW LOGHIST Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show loghist all service |
|F4 004 |
|F4 004 Restored database archive at entry 2. |
|F4 004 Database archive was created on 12-29-1996 at 14:42:43. |
|F4 004 |
|F4 004 Entry Type Date Time Flags |
|F4 004 Log arch restored 12-29-1996 14:54:59 B000 |
|F4 004 User db arch restored 12-29-1996 14:42:43 A000 |
|F4 004 End of restore set 12-30-1996 13:21:57 0000 |
|F4 004 Log arch created 12-29-1996 14:54:59 9000 |
|F4 004 User db arch created 12-29-1996 14:42:43 8000 |
|F4 004 Log arch restored 12-10-1996 16:19:09 B800 |
|F4 004 Log arch restored 12-10-1996 16:18:32 B000 |
|F4 004 Sys db arch restored 12-10-1996 16:11:25 A000 |
|F4 004 End of restore set 12-10-1996 16:20:49 0000 |
|F4 004 Log arch crt by restore 12-10-1996 16:20:49 9000 |
|F4 004 Log arch created 12-10-1996 16:19:09 9000 |
|F4 004 Log arch created 12-10-1996 16:18:32 9000 |
|F4 004 Logmode L 12-10-1996 16:16:44 0000 |
|F4 004 Sys db arch created 12-10-1996 16:11:25 8000 |
|F4 004 Logmode Y 12-01-1996 08:45:33 0000 |
|F4 004 Logmode N 11-31-1996 09:15:08 0000 |
|F4 004 Coldlog reformat 11-29-1996 13:35:51 0000 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 71. A Typical SHOW LOGHIST Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show loghist all service |
| |
| Restored database archive at entry 2. |
| Database archive was created on 12-29-1996 at 14:42:43. |
| |
| Entry Type Date Time Flags Log FN Log FT |
|Log arch restored 12-29-1996 14:54:59 B000 SQLMACHO 12298901 |
|User db arch restored 12-29-1996 14:42:43 A000 |
|End of restore set 12-30-1996 13:21:57 0000 |
|Log arch created 12-29-1996 14:54:59 9000 SQLMACHO 12298901 |
|User db arch created 12-29-1996 14:42:43 8000 |
|Log arch restored 12-10-1996 16:19:09 B800 SQLMACHO 12108902 |
|Log arch restored 12-10-1996 16:18:32 B000 SQLMACHO 12108901 |
|Sys db arch restored 12-10-1996 16:11:25 A000 |
|End of restore set 12-10-1996 16:20:49 0000 |
|Log arch crt by restore 12-10-1996 16:20:49 9000 SQLMACHO 12108903 |
|Log arch created 12-10-1996 16:19:09 9000 SQLMACHO 12108902 |
|Log arch created 12-10-1996 16:18:32 9000 SQLMACHO 12108901 |
|Logmode L 12-10-1996 16:16:44 0000 |
|Sys db arch created 12-10-1996 16:11:25 8000 |
|Logmode Y 12-01-1996 08:45:33 0000 |
|Logmode N 11-31-1996 09:15:08 0000 |
|Coldlog reformat 11-29-1996 13:35:51 0000 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.-ALL-----.
>>-SHOW POOL----+---------+------------------------------------><
+-SUMMARY-+
+-DELETED-+
'-n-------'
|
A storage pool is made up of one or more database extents.
A dbextent is the physical medium in which the application server stores data. Internally, all database extents look like a continuous DASD address space of physical 4096-byte (4-kilobyte) pages.
The SHOW POOL command displays physical storage information about each storage pool defined. You can use this command to monitor physical storage usage.
The following examples show the output of the SHOW POOL command.
Figure 72. A Typical SHOW POOL Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show pool |
|F4 004 |
|F4 004 POOL NO. 1: NUMBER OF EXTENTS = 3 |
|F4 004 |
|F4 004 EXTENT TOTAL NO. OF NO. OF NO. OF % |
|F4 004 NO. PAGES PAGES USED FREE PAGES RESV PAGES USED |
|F4 004 1 285 274 11 96 |
|F4 004 2 285 33 252 11 |
|F4 004 6 741 0 741 0 |
|F4 004 TOTAL 1311 307 1004 20 23 |
|F4 004 |
|F4 004 POOL NO. 2: NUMBER OF EXTENTS = 3 |
|F4 004 |
|F4 004 EXTENT TOTAL NO. OF NO. OF NO. OF % |
|F4 004 NO. PAGES PAGES USED FREE PAGES RESV PAGES USED |
|F4 004 3 114 2 112 1 |
|F4 004 4 114 0 114 0 |
|F4 004 9 114 0 114 0 |
|F4 004 TOTAL 342 2 340 20 0 |
|F4 004 |
|F4 004 POOL NO. 4: NUMBER OF EXTENTS = 1 SHORT ON STORAGE |
|F4 004 |
|F4 004 EXTENT TOTAL NO. OF NO. OF NO. OF % |
|F4 004 NO. PAGES PAGES USED FREE PAGES RESV PAGES USED |
|F4 004 10 285 260 25 91 |
|F4 004 TOTAL 285 260 25 20 91 |
|F4 004 |
|F4 004 |
|F4 004 FREE AREAS: NUMBER OF DELETED EXTENTS = 3 |
|F4 004 |
|F4 004 EXTENT TOTAL |
|F4 004 NO. PAGES |
|F4 004 (2+) 57 |
|F4 004 5 171 |
|F4 004 7 228 |
|F4 004 8 342 |
|F4 004 END 9552 |
|F4 004 TOTAL 10350 |
|F4 004 |
|F4 004 Maximum number of DBEXTENTs = 64 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 73. A Typical SHOW POOL Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|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 |
| 6 741 0 741 0 |
|TOTAL 1311 307 1004 20 23 |
| |
|POOL NO. 2: NUMBER OF EXTENTS = 3 |
| |
|EXTENT TOTAL NO. OF NO. OF NO. OF % |
|NO. PAGES PAGES USED FREE PAGES RESV PAGES USED |
| 3 114 2 112 1 |
| 4 114 0 114 0 |
| 9 114 0 114 0 |
|TOTAL 342 2 340 20 0 |
| |
|POOL NO. 4: NUMBER OF EXTENTS = 1 SHORT ON STORAGE |
| |
|EXTENT TOTAL NO. OF NO. OF NO. OF % |
|NO. PAGES PAGES USED FREE PAGES RESV PAGES USED |
| 10 285 260 25 91 |
|TOTAL 285 260 25 20 91 |
| |
| |
|FREE AREAS: NUMBER OF DELETED EXTENTS = 3 |
| |
|EXTENT TOTAL |
| NO. PAGES |
| (2+) 57 |
| 5 171 |
| 7 228 |
| 8 342 |
| END 9552 |
|TOTAL 10350 |
| |
|Maximum number of DBEXTENTs = 64 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
The SHOW POOL command with the SUMMARY parameter gives output identical to the SHOW DBEXTENT command. The SHOW DBEXTENT command only displays information for pools.
The SHOW POOL command with the DELETED parameter displays all the free area on the directory's page map table, including deleted dbextents.
The SHOW POOL command with a pool number specified for n displays information for that pool.
When you enter the SHOW POOL command without specifying a parameter, the default is SHOW POOL ALL. The following examples show the default SHOW POOL command.
Figure 74. A Typical SHOW POOL SUMMARY Display on a VSE Operator Console
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show pool summary |
|F4 004 POOL TOTAL NO. OF NO. OF NO. OF % NO. OF |
|F4 004 NO. PAGES PAGES USED FREE PAGES RESV PAGES USED EXTENTS SOS |
|F4 004 1 1311 307 1004 20 23 3 |
|F4 004 2 342 2 340 20 0 3 |
|F4 004 4 285 260 25 20 91 1 * |
|F4 004 FREE 10350 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 75. A Typical SHOW POOL SUMMARY Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show pool summary |
|POOL TOTAL NO. OF NO. OF NO. OF % NO. OF |
|NO. PAGES PAGES USED FREE PAGES RESV PAGES USED EXTENTS SOS |
| 1 1311 307 1004 20 23 3 |
| 2 342 2 340 20 0 3 |
| 4 285 260 25 20 91 1 * |
|FREE 10350 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
The SHOW POOL output values have the following meanings.
For example, -54 indicates that storage pool number 54 is nonrecoverable.
| Note: | Extra information is displayed beside this number because of the VMDSS code enabled. For more information see the DB2 Server for VSE & VM Performance Tuning Handbook manual. |
| Note: | The SHORT ON STORAGE flag appears as SOS in the SHOW POOL SUMMARY display in Figure 74. |
| Note: | The application server calculates pages based on 57 pages per cylinder, rather than the actual number of pages per cylinder of the DASD. |
.-------------------------------------.
V |
>>-SHOW PROC----+----procedure-name-+-----------------+--+-+---><
| '-AUTHID--authid--' |
'-*-+-----------------+--------------------'
'-AUTHID--authid--'
|
The SHOW PROC command displays cached information about stored procedures. Several output lines are displayed for each procedure. Note that if more than one version of a stored procedure exists and AUTHID is not specified, output is displayed for each version. For example, if there are two versions of PROC1, one for public use (that is, the AUTHID column in SYSTEM.SYSROUTINES is blank), and one for use by a specific individual (for example, the AUTHID column might contain authid1), output is displayed for both the public copy and the copy that is accessible only by authid. The following information is displayed:
The following are examples of the SHOW PROC command.
SHOW PROC * SHOW PROC PROC1 SHOW PROC PROC2 PROC20 SHOW PROC PROC2* SHOW PROC MYPROC AUTHID USER1
The first statement above displays information for all stored procedures. The second example displays information for procedure PROC1 only. The third shows information for PROC2 and PROC20. The fourth statement displays information for any procedure that starts with 'PROC2'. The last statement displays information for the version of MYPRO that is accessible only by authorization ID USER1.
The following example shows the output from the SHOW PROC command.
Figure 76. A Typical SHOW PROC Display on a VM Operator Console
+--------------------------------------------------------------------------------+
|show proc * |
|Status of stored procedure(s) 1997-09-30 08:56:42 |
|Procedure: USERPRC1 AUTHID: |
| Module: MODULE1 Running in Group: PSRV1 |
| Status: STARTED. Running in Default Group: N |
| Agents currently running this procedure: 1 |
| Max num of agents that have run this proc concurrently: 1 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 1 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|Procedure: USERPRC2 AUTHID: |
| Module: MODULE2 Running in Group: |
| Status: STOP-QUE Running in Default Group: N |
| Agents currently running this procedure: 0 |
| Max num of agents that have run this proc concurrently: 2 |
| Agents waiting to run this procedure: 5 |
| Max num of agents that have waited to run this proc: 5 |
| Number of timeouts: 3 |
| Number of abends: 1 |
|Procedure: USERPRC3 AUTHID: |
| Module: MODULE3 Running in Group: PSRV1 |
| Status: STARTED. |
| Agents currently running this procedure: 2 |
| Max num of agents that have run this proc concurrently: 2 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 6 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|Procedure: USERPRC4 AUTHID: |
| Module: MODULE4 Running in Group: |
| Status: STOP-REJ Running in Default Group: N |
| Agents currently running this procedure: 0 |
| Max num of agents that have run this proc concurrently: 1 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 1 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------+
|show proc * |
|Status of stored procedure(s) 1997-09-30 08:59:29 |
|Procedure: PROC1 AUTHID: USERX |
| Module: PMOD1 Running in Group: PSRV2 |
| Status: STARTED. Running in Default Group: N |
| Agents currently running this procedure: 1 |
| Max num of agents that have run this proc concurrently: 1 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 1 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|Procedure: PROC2 AUTHID: |
| Module: PMOD2 Running in Group: |
| Status: STOP-REJ |
| Agents currently running this procedure: 0 |
| Max num of agents that have run this proc concurrently: 2 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 2 |
| Number of timeouts: 1 |
| Number of abends: 1 |
|Procedure: PROC3 AUTHID: |
| Module: PMOD3 Running in Group: |
| Status: STOP-QUE |
| Agents currently running this procedure: 0 |
| Max num of agents that have run this proc concurrently: 1 |
| Agents waiting to run this procedure: 3 |
| Max num of agents that have waited to run this proc: 5 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|Procedure: TQUERY1 AUTHID: |
| Module: TMOD1 Running in Group: PSRV2 |
| Status: STARTED. Running in Default Group: Y |
| Agents currently running this procedure: 2 |
| Max num of agents that have run this proc concurrently: 4 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 2 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|Procedure: TQUERY2 AUTHID: USER2 |
| Module: TMOD2 Running in Group: PSRV2 |
| Status: STARTED. |
| Agents currently running this procedure: 1 |
| Max num of agents that have run this proc concurrently: 1 |
| Agents waiting to run this procedure: 0 |
| Max num of agents that have waited to run this proc: 0 |
| Number of timeouts: 0 |
| Number of abends: 0 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.--------.
V |
>>-SHOW PSERVER----+-------+---+----name---+-+-----------------><
'-GROUP-' '-*-----------'
|
The SHOW PSERVER command displays cached information about stored procedure servers. The command displays one line for each server for which information is requested. Each line displays:
| Note: | This command can be issued from the operator console or from ISQL. |
An asterisk ('*') in the last position of the name acts as a wildcard. For example, 'ABC*' indicates that the command should be applied to all stored procedure servers (or server groups, if the GROUP keyword is specified) that have names beginning with 'ABC'.
The following are examples of the SHOW PSERVER command.
SHOW PSERVER * SHOW PSERVER SERV1 SHOW PSERVER SERV1* SHOW PSERVER GROUP GRP1 GRP2
The first example displays information for all stored procedure servers for the database. The second statement displays information for SERV1 only. The third shows information for any server whose name starts with 'SERV1'. The last statement shows information for all servers in groups GRP1 and GRP2.
The following example shows the output from the SHOW PSERVER command.
Figure 77. A Typical SHOW PSERVER Display on a VM Operator Console
+--------------------------------------------------------------------------------+
|show pserver * |
|Status of stored procedure server(s) 1997-09-21 13:35:49 |
|PSERVER GROUP PROCNAME AUTHID STATUS IMPL |
|DEFSERV MOD1 STARTED. N |
|PSERV1 PSERV PMOD1 USER1 STARTED. Y |
|PSERV2 PSERV PMOD2 USER2 STOPPING Y |
|PSERV3 PSERV PMOD3 STOPPING Y |
|ARI0065I Operator command processing is complete. |
| |
|show pserver group pserv |
|Status of stored procedure server(s) 1997-09-21 13:35:49 |
|PSERVER GROUP PROCNAME AUTHID STATUS IMPL |
|PSERV1 PSERV PMOD1 USER1 STARTED. Y |
|PSERV2 PSERV PMOD2 USER2 STOPPING Y |
|PSERV3 PSERV PMOD3 STOPPING Y |
|ARI0065I Operator command processing is complete. |
| |
|show pserver servyyy |
|Status of stored procedure server(s) 1997-09-21 13:42:54 |
|ARI4163I Stored procedure server SERVYYY is not defined. |
|ARI4158E Error in operator command SHOW PSERVER. |
| Reason Code 6. |
|PSERVER GROUP PROCNAME AUTHID STATUS IMP |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW STORAGE------------------------------------------------>< |
The SHOW STORAGE command displays storage values currently allocated and in use, and free and high storage values for each real agent; as well, it displays total system storage allocation, system high storage values and the user IDs that last caused the high storage marks. The SHOW STORAGE command does not have any parameters.
The SHOW STORAGE command can be used with the RESET HIGHSTOR command to help determine storage required to perform a specific function. By resetting the HIGHSTOR value, performing a function and then invoking the SHOW STORAGE command, you can determine the maximum storage required for that function.
The following examples show the output from the SHOW STORAGE command.
Figure 78. SHOW STORAGE Display on VM
+--------------------------------------------------------------------------------+
|show storage |
|Status of Storage at DATE='1998-09-22' TIME='14:54:09' |
| |
|AGENT LOC ALLOCATED IN USE FREE HIGHSTOR USERID |
|OPERATOR A 4096 0 4096 4096 |
| B 16 0 16 16 |
|CHECKPT A 2048 0 2048 2048 |
| B 16 0 16 16 |
|RECOVERY A 4096 0 4096 4096 |
| B 16 0 16 16 |
|TCP/IP A 512 0 512 512 |
| B 16 0 16 16 |
|1 A 8192 0 8192 71096 MARISSA |
| B 16 0 16 16 |
|2 A 8192 0 8192 8192 |
| B 16 0 16 16 |
|3 A 8192 0 8192 8192 |
| B 16 0 16 16 |
|4 A 8192 0 8192 8192 |
| B 16 0 16 16 |
|5 A 8192 0 8192 8192 |
| B 16 0 16 16 |
|PROTOTYPE A 701888 693664 8224 701888 |
| B 360488 307336 53152 360488 |
|SUMMARY |
|QUEUES A 753600 693664 59936 816504 |
| B 360632 307336 53296 360632 |
|PACKAGES A 49008 47240 1768 49008 |
|SYSTEM A 802664 865568 |
| B 391728 391728 |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 79. SHOW STORAGE Display on VSE
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 SHOW STORAGE |
|F4 004 Status of Storage at DATE='1998-09-22' TIME='14:54:09' |
|F4 004 |
|F4 004 AGENT LOC ALLOCATED IN USE FREE HIGHSTOR USERID |
|F4 004 OPERATOR A 4096 0 4096 4096 |
|F4 004 B 16 0 16 16 |
|F4 004 CHECKPT A 2048 0 2048 2048 |
|F4 004 B 16 0 16 16 |
|F4 004 RECOVERY A 4096 0 4096 4096 |
|F4 004 B 16 0 16 16 |
|F4 004 1 A 8192 0 8192 71096 MARISSA |
|F4 004 B 16 0 16 16 |
|F4 004 2 A 8192 0 8192 8192 |
|F4 004 B 16 0 16 16 |
|F4 004 3 A 8192 0 8192 8192 |
|F4 004 B 16 0 16 16 |
|F4 004 4 A 8192 0 8192 8192 |
|F4 004 B 16 0 16 16 |
|F4 004 5 A 8192 0 8192 8192 |
|F4 004 B 16 0 16 16 |
|F4 004 PROTOTYPE A 701888 693664 8224 701888 |
|F4 004 B 360488 307336 53152 360488 |
|F4 004 SUMMARY |
|F4 004 QUEUES A 753088 693664 59424 816504 |
|F4 004 B 360616 307336 53280 360616 |
|F4 004 PACKAGES A 49008 47240 1768 49008 |
|F4 004 SYSTEM A 802152 865568 |
|F4 004 B 391712 391712 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
The SHOW STORAGE display fields are described below.
| Note: | Some storage, such as storage used for local variables in SQL/DS code, is not included in these numbers. |
>>-SHOW SYSTEM------------------------------------------------->< |
The SHOW SYSTEM command displays much of the data described in the other SHOW commands.
Note that the SHOW SYSTEM command internally issues a SHOW CONNECT ACTIVE command. If you enter the SHOW SYSTEM command from either the DB2 Server for VM operator console or from the VSE system operator console, certain agents are marked forceable. See Resolving CICS In-Doubt Logical Units of Work for more information about forceable agents.
The following examples show the output from the SHOW SYSTEM command.
Figure 80. SHOW SYSTEM Display on VSE
+--------------------------------------------------------------------------------+
|AR |
|MSG F4 |
|AR 015 1I40I READY |
|F4 004 ARI0062A SQLDS : |
|F4-004 Enter a DB2 Server for VSE operator command. |
|4 show system |
|F4 004 System state at DATE='01-15-96' TIME='11:57:35' |
|F4 004 |
|F4 004 POOL TOTAL NO. OF NO. OF NO. OF % NO. OF |
|F4 004 NO. PAGES PAGES USED FREE PAGES RESV PAGES USED EXTENTS SOS |
|F4 004 1 11514 2710 8804 20 23 1 |
|F4 004 2 7467 136 7331 20 1 7 |
|F4 004 FREE 1394139 |
|F4 004 Log Status: |
|F4 004 Log size is 4661248 bytes. |
|F4 004 Log used is 0 percent 4686 bytes. |
|F4 004 Log remaining before overflow procedure is 89 percent |
|F4 004 4190394 bytes. |
|F4 004 No non-R/O agents began before the last checkpoint. |
|F4 004 Archiving is not enabled. |
|F4 004 Log pages remaining before checkpoint is 10. |
|F4 004 Status of agents: |
|F4 004 Checkpoint agent is not active. |
|F4 004 User Agent: 1 User ID: MARISSA is R/O APPL BE01 |
|F4 004 Agent is processing and is in I/O wait. |
|F4 004 User Agent: 2 User ID: ANDREW is R/O APPL BE13 |
|F4 004 Agent is processing and is in LOCK wait. |
|F4 004 User Agent: 3 User ID: LAURA is R/O APPL BE10 |
|F4 004 Agent is processing and is in I/O wait. |
|F4 004 User Agent: 4 User ID: DAVID is R/O APPL BE14 |
|F4 004 Agent is processing and is in LOCK wait. |
|F4 004 User Agent: 5 User ID: DANIEL is R/O APPL BE12 |
|F4 004 Agent is processing and is in I/O wait. |
|F4 004 0 agent(s) not connected to an APPL or SUBSYS. |
|F4 004 Lock Request Block (LRB) and Lock Status: |
|F4 004 NLRBS IN USE FREE NLRBU MAX USED BY LUW |
|F4 004 ------- ------- ------- ------- --------------- |
|F4 004 2520 452 2068 1000 554 |
|F4 004 *** THE LOCKWAIT TABLE *** |
|F4 004 ENTRY = DBSPACE NUMBER ON WHICH THERE IS LOCK CONTENTION |
|F4 004 The presence of an entry shows |
|F4 004 the agent requesting the lock and |
|F4 004 the agent contending for or holding the lock. |
|F4 004 AGENT AGENT CONTENDING FOR OR HOLDING THE LOCK |
|F4 004 REQUESTING |
|F4 004 LOCK |
|F4 004 1 2 3 4 5 |
|F4 004 MARISSA ANDREW LAURA DAVID DANIEL |
|F4 004 1 MARISSA ............................................. |
|F4 004 2 ANDREW ..................11 ...................... |
|F4 004 3 LAURA ............................................. |
|F4 004 4 DAVID .........11 ....11 ...................... |
|F4 004 5 DANIEL ............................................. |
|F4 004 |
|F4 004 ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
Figure 81. SHOW SYSTEM Display on VM
+--------------------------------------------------------------------------------+
|show system |
|System state at DATE='01-15-96' TIME='11:57:35' |
| |
|POOL TOTAL NO. OF NO. OF NO. OF % NO. OF |
|NO. PAGES PAGES USED FREE PAGES RESV PAGES USED EXTENTS SOS |
| 1 11514 2710 8804 20 23 1 |
| 2 7467 136 7331 20 1 7 |
|FREE 1394139 |
|Log Status: |
| Log size is 4661248 bytes. |
| Log used is 0 percent 4686 bytes. |
| Log remaining before overflow procedure is 89 percent |
| 4190394 bytes. |
| No non-R/O agents began before the last checkpoint. |
| Archiving is not enabled. |
| Log pages remaining before checkpoint is 10. |
|Status of agents: |
| Checkpoint agent is not active. |
| User Agent: 1 User ID: MARISSA is R/O APPL BE01 |
| Agent is processing and is in I/O wait. |
| User Agent: 2 User ID: ANDREW is R/O APPL BE13 |
| Agent is processing and is in LOCK wait. |
| User Agent: 3 User ID: LAURA is R/O APPL BE10 |
| Agent is processing and is in I/O wait. |
| User Agent: 4 User ID: DAVID is R/O APPL BE14 |
| Agent is processing and is in LOCK wait. |
| User Agent: 5 User ID: DANIEL is R/O APPL BE12 |
| Agent is processing and is in I/O wait. |
| 0 agent(s) not connected to an APPL or SUBSYS. |
|Lock Request Block (LRB) and Lock Status: |
| NLRBS IN USE FREE NLRBU MAX USED BY LUW |
| ------- ------- ------- ------- --------------- |
| 2520 452 2068 1000 554 |
| *** 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 |
| MARISSA ANDREW LAURA DAVID DANIEL |
| 1 MARISSA ............................................. |
| 2 ANDREW ..................11 ...................... |
| 3 LAURA ............................................. |
| 4 DAVID .........11 ....11 ...................... |
| 5 DANIEL ............................................. |
| |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-SHOW USERS-------------------------------------------------->< |
The SHOW USERS command displays a summary of users (DB2 Server for VM applications or ISQL users) connected to the database machine. The operator and ISQL users can enter this command.
SHOW USERS displays the following information:
The lines of output have these meanings:
If you are using the VM/ESA operating systems, a VM user can be connected to an application server under one or more different DB2 Server for VM authorization IDs, within separate CMS units of work, with one work unit waiting for another.
If a user is waiting for a real agent held by the user's own ID, the SHOW USERS command displays the information like this:
2 SQL/DS USER(S) ARE WAITING
User ID = SQLUSER3 SQL ID = JOHN1
User ID = SQLUSER3 SQL ID = JOHN2
In this situation, you must force one of JOHN's work units to end its work. For information on forcing users to end their work, see Forcing Users to End Their Work.
| Note: | The SHOW USERS command can be useful during application server shutdown processing (SQLEND) in determining whether there are inactive users who are preventing the application server from ending. The application server does not end (unless you enter SQLEND QUICK) until all users have ended their connections with the database machine. If TCP/IP is enabled, the TCP/IP agent will be included in the SHOW USERS command output as one of the connected users. It is not required to be ended by the user before shutdown. The SQLEND shutdown process will disconnect the TCP/IP agent without requiring the operator's intervention. |
The following example shows the output from the SHOW USERS command.
Figure 82. A Typical SHOW USERS Display on a VM Operator Console or ISQL Terminal
+--------------------------------------------------------------------------------+
|show users |
|Status of Connected Users: 1998-07-24 10:13:41 |
| 4 users are connected to SQL/DS. |
| 1 SQL/DS users are active. |
| User ID: PETERS SQL ID: SMITH not processing |
| 2 SQL/DS users are waiting. |
| User ID: DORA SQL ID: TASK115 |
| User ID: MICHAEL SQL ID: MIKE2 |
| 1 SQL/DS users are inactive. |
| User ID: JENNY SQL ID: JENNY |
| 0 SQL/DS agents are available. |
| 94 SQL/DS user connections are available. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.-------------------------------------.
V |
>>-START PROC----+----procedure-name-+-----------------+--+-+--><
| '-AUTHID--authid--' |
'-*-+-----------------+--------------------'
'-AUTHID--authid--'
|
The START PROC command can be used to:
The START PROC command starts a stored procedure that was stopped previously. If the stored procedure was not stopped prior to the START PROC command, a message is displayed and no action is taken. In this case, since the procedure is left in STARTED status, the command is considered to be successful.
Upon successful completion of the START PROC command, queued requests for the specified stored procedures are allowed to begin execution. Additionally, the abnormal termination counts for the specified stored procedures are set to zero.
| Note: | This command can be issued only from the operator console. |
Message ARI4151I is displayed for each procedure started. If the name specified does not contain a wildcard, and is not found, message ARI4152E is displayed.
| Note: | procedure-name cannot be AUTHID or ACTION, because these are treated as keywords by the START PROC and STOP PROC commands. |
The following are examples of the START PROC command.
START PROC * START PROC PROC2* START PROC PROC1 START PROC MYPROC AUTHID USER1 START PROC * AUTHID USER1 START PROC MYPROC AUTHID USER1 YOURPROC AUTHID USER2
The first statement above starts all stored procedures. The second starts the public version (that is, the version that does not have an AUTHID associated with it) of all procedures whose names start with 'PROC2'. The third statement starts the public version of one stored procedure, PROC1. The fourth example starts the version of procedure MYPROC that is accessible only by authorization ID USER1. The fifth starts all procedures that are accessible only by authorization ID USER1. The last statement starts the version of MYPROC that is accessible only by USER1 and the version of YOURPROC that is accessible only by USER2.
The following example shows the output from the START PROC command.
Figure 83. A Typical START PROC Display on a VM Operator Console
+--------------------------------------------------------------------------------+
|start proc proc* tquery2 yyy |
|Starting stored procedure(s) 1997-07-21 13:56:22 |
|ARI4151I START PROC successful for PROC1. |
|ARI4151I START PROC successful for PROC2. |
|ARI4153I Stored procedure PROC3 is already started. |
|ARI4151I START PROC successful for PROC3. |
|ARI4151I START PROC successful for TQUERY2. |
|ARI4154I Stored procedure YYY is not found. |
|ARI4152E START PROC failed for YYY. |
| Reason Code 1. |
|ARI0065I Operator command processing is complete. |
| |
|start proc storproc1 authid user1 |
|Starting stored procedure(s) 1997-09-21 14:01:00 |
|ARI4151I START PROC successful for STORPROC1 for AUTHID |
| USER1. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.--------.
V |
>>-START PSERVER----+-------+---+----name---+-+----------------><
'-GROUP-' '-*-----------'
|
The START PSERVER command is used to start a stored procedure server, establish a connection between it and the database machine, and initialize the stored procedure environment.
When the START PSERVER command is issued, the database manager checks the cached information from SYSTEM.SYSPSERVERS for a row in which the value of the column PSERVER matches the name specified on the command. If a row is found, the action taken depends on the status:
If the name is not found, message ARI4163I is displayed. In this case, if only one stored procedure server is to be started, the command terminates. If more than one stored procedure server is to be started, the database manager continues with the next one.
Starting a stored procedure server is a two part process. In the first part, when the START PSERVER command is issued, the status of the stored procedure server is updated to STARTING. A storage block is reserved, to be used for the connection between the database manager and the stored procedure server. The second part takes place the first time SQL CALL is processed. If the stored procedure server that is to run the stored procedure has a status of STARTING, the database manager completes the START PSERVER processing prior to sending the SQL CALL statement to the stored procedure server.
| Note: | If the stored procedure server does not start sucessfully within the time
specified by the PTIMEOUT initialization parameter, message ARI4168I is
displayed and the command terminates.
This command can be issued only from the operator console. |
An asterisk ('*') in the last position of the name acts as a wildcard. For example, 'ABC*' indicates that the command should be applied to all stored procedure servers (or server groups, if the GROUP keyword is specified) that have names beginning with 'ABC'.
| Note: | name cannot be any of the following:
because these are treated as keywords by the START PSERVER and STOP PSERVER commands. |
The following are examples of the START PSERVER command.
START PSERVER * START PSERVER SERV1 START PSERVER SERV1* START PSERVER GROUP GRP1 GRP2
The first example starts all stored procedure servers for the database. The second statement starts SERV1 only. The third starts any server whose name starts with 'SERV1'. The last statement starts all servers in groups GRP1 and GRP2.
The following example shows the output from the START PSERVER command.
Figure 84. A Typical START PSERVER Display on a VM Operator Console
+--------------------------------------------------------------------------------+
|start pserver * |
|Starting stored procedure server(s) 1997-09-21 14:23:06 |
|Starting stored procedure server PSERV1 |
|Starting stored procedure server PSERV2 |
|ARI4164I Stored procedure server DEFSERV |
| is already started. |
|ARI4165I START PSERVER cannot be processed for SERYYZ |
| because it has a status of STARTING. |
|ARI4167E START PSERVER for SERYYZ failed. |
| Reason Code 5. |
|ARI0065I Operator command processing is complete. |
| |
|start pserver group pserv |
|Starting stored procedure server(s) 1997-09-21 14:37:40 |
|ARI4165I START PSERVER cannot be processed for PSERV1 |
| because it has a status of STARTING. |
|ARI4167E START PSERVER for PSERV1 failed. |
| Reason Code 5. |
|ARI4165I START PSERVER cannot be processed for PSERV2 |
| because it has a status of STARTING. |
|ARI4167E START PSERVER for PSERV2 failed. |
| Reason Code 5. |
|ARI0065I Operator command processing is complete. |
| |
|start pserver seryyy seryyx |
|Starting stored procedure server(s) 1997-09-21 14:37:40 |
|ARI4163I Stored procedure server SERYYY is not defined. |
|ARI4167E START PSERVER for SERYYY failed. |
| Reason Code 1. |
|ARI4166I Unable to acquire storage for a pseudoagent |
| for server SERYYX. |
|ARI4167E START PSERVER for SERYYX failed. |
| Reason Code 3. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
>>-START TCPIP--+-----------------+----------------------------><
'-TCPPORT--nnnnn--'
|
The START TCPIP command is used to restart TCP/IP support. Once the TCP/IP support for the application server is initiated, a TCP/IP agent is created to handle all TCP/IP related functions. If any TCP/IP function failure was detected by the TCP/IP agent, the TCP/IP support for the application server will be disabled. It is possible to restart the TCPIP support for the application server without recycling the application server.
| Note: | For VSE, the same TCP/IP port number can be used among different application servers to initialize TCP/IP support. In this case, the last server using the duplicate port number will become the target server owning the port. All later incoming connect requests using this port number will be connected to this target server. To avoid misdirected connection requests, duplicate port numbers should not be used. |
.-------------------------------------.
V |
>>-STOP PROC----+----procedure-name-+-----------------+--+-+---->
| '-AUTHID--authid--' |
'-*-+-----------------+--------------------'
'-AUTHID--authid--'
.-ACTION QUEUE--.
>-----+---------------+----------------------------------------><
'-ACTION REJECT-'
|
The STOP PROC command is introduced to allow the database administrator to temporarily stop access to one or more stored procedures. The database administrator can choose to either queue or reject requests to run a stored procedure that has been stopped.
The database administrator may need to stop access to one or more stored procedures for any of the following reasons:
When STOP PROC is issued, the database manager looks for the procedure in the cache. If it is found, the status of the procedure is updated, and message ARI4155I is displayed indicating that the command was successful. If the procedure is not found, message ARI4154I is displayed, and then message ARI4157E is displayed, indicating that the command was not successful.
The database manager automatically performs a STOP PROC ACTION REJECT operation on any stored procedure that exceeds the installation-defined maximum abnormal termination count.
The STOP PROC command prevents the database manager from accepting SQL CALL statements for one or more stored procedures. The STOP PROC command does not prevent CALL statements that have already been assigned to a stored procedure server from running.
The effects of the STOP PROC command do not persist after SQLEND has been issued. If the installation wishes to permanently disable a procedure, any of the following actions can be taken:
| Note: | This command is valid only when issued at the operator console. |
| Note: | procedure-name cannot be AUTHID or ACTION, because these are treated as keywords by the START PROC and STOP PROC commands. |
The following are examples of the STOP PROC command.
STOP PROC * STOP PROC PROC2* STOP PROC PROC1 STOP PROC PROC1 ACTION REJECT STOP PROC MYPROC AUTHID USER1
The first statement above stops all stored procedures. The second stops the public version (that is, the version that does not have an AUTHID associated with it) for all procedures whose names start with 'PROC2'. The third statement stops the public version of one stored procedure, PROC1. The fourth example also stops procedure PROC1, but changes the cached status to STOP-REJ, so that further attempts to call this procedure will be rejected rather than queued. The last example stops the procedure MYPROC, which is accessible only by authorization ID USER1.
The following example shows the output from the STOP PROC command.
Figure 85. A Typical STOP PROC Display on a VM Operator Console
+--------------------------------------------------------------------------------+
|stop proc proc* tquery1 tquery2 authid user2 yyy action queue |
|Stopping stored procedure(s) 1997-09-21 15:02:04 |
|ARI4155I STOP PROC successful for PROC1. |
|ARI4156I New calls to stored procedure PROC1 |
| will be QUEUED. |
|ARI4155I STOP PROC successful for PROC2. |
|ARI4156I New calls to stored procedure PROC2 |
| will be QUEUED. |
|ARI4155I STOP PROC successful for PROC3. |
|ARI4156I New calls to stored procedure PROC3 |
| will be QUEUED. |
|ARI4159I Stored procedure TQUERY1 is already stopped. |
|ARI4155I STOP PROC successful for TQUERY1. |
|ARI4156I New calls to stored procedure TQUERY1 |
| will be QUEUED. |
|ARI4155I STOP PROC successful for TQUERY2 for AUTHID |
| USER2. |
|ARI4156I New calls to stored procedure TQUERY2 |
| with AUTHID USER2 will be QUEUED. |
|ARI4154I Stored procedure YYY is not found. |
|ARI4157E STOP PROC failed for YYY. |
| Reason Code 1. |
|ARI0065I Operator command processing is complete. |
| |
|stop proc badproc action reject |
|Stopping stored procedure(s) 1997-09-21 15:09:07 |
|ARI4159I Stored procedure BADPROC is already stopped. |
|ARI4155I STOP PROC successful for BADPROC. |
|ARI4156I New calls to stored procedure BADPROC |
| will be REJECTED. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+
.--------.
V |
>>-STOP PSERVER----+-------+---+----name---+-+------------------>
'-GROUP-' '-*-----------'
.-NOIMPLICIT--. .-NORMAL--.
>-----+-------------+---+---------+----------------------------><
'-IMPLICIT----' '-QUICK---'
|
The STOP PSERVER command is used to stop the handler on a stored procedure server, sever the connection between the stored procedure server and the database machine, and stop the stored procedure server.
When the STOP PSERVER command is issued, the database manager checks the cached information from SYSTEM.SYSPSERVERS for a row in which the value of the column PSERVER matches the name specified on the command. If such a row is not found, message ARI4163I is issued. In this case, if only one stored procedure server is to be stopped, the command terminates. If more than one stored procedure server is to be stopped, the database manager continues with the next one. Note that the stored procedure server does not have to be started for the STOP PSERVER command to be successful. This can be useful, for example if the database administrator wants to enable implicit starts for a procedure server that was stopped with the NOIMPLICIT option. In this case the database administrator would issue the STOP PSERVER command for that procedure server, specifying IMPLICIT. A STOP PSERVER NORMAL can also be overruled by a STOP PSERVER QUICK. However, a STOP PSERVER QUICK cannot be overruled by a subsequent STOP PSERVER NORMAL.
| Note: | This command can be issued only from the operator console. |
An asterisk ('*') in the last position of the name acts as a wildcard. For example, 'ABC*' indicates that the command should be applied to all stored procedure servers (or server groups, if the GROUP keyword is specified) that have names beginning with 'ABC'.
| Note: | name cannot be any of the following:
because these are treated as keywords by the START PSERVER and STOP PSERVER commands. |
When the STOP PSERVER command is issued, the IMPLICIT/NOIMPLICIT option defaults to NOIMPLICIT. However, any stored procedure server that have not been explicitly stopped since the database manager was started can be implicitly started. In other words, the STOP PSERVER command must be issued to disallow implicit startups of stored procedure servers.
Note that a status of NOIMPLICIT is not maintained after SQLEND. This means that servers that were stopped with the NOIMPLICIT option prior to an SQLEND must be explicitly stopped after the database manager is restarted if implicit startups are to be disallowed.
The following are examples of the STOP PSERVER command.
STOP PSERVER * STOP PSERVER SERV1 STOP PSERVER SERV1* STOP PSERVER GROUP GRP1 GRP2
The first example stops all stored procedure servers for the database. The second statement stops SERV1 only. The third stops any server whose name starts with 'SERV1'. The last statement stops all servers in groups GRP1 and GRP2.
The following example shows the output from the STOP PSERVER command.
Figure 86. A Typical STOP PSERVER Display on a VM Operator Console
+--------------------------------------------------------------------------------+
|stop pserver pserv1 implicit |
|Stopping stored procedure server(s) 1997-09-21 15:42:53 |
|ARI4171I Stored procedure server PSERV1 |
| has been stopped. It can be implicitly started. |
|ARI0065I Operator command processing is complete. |
| |
|stop pserver pserv1* |
|Stopping stored procedure server(s) 1997-09-21 15:44:43 |
|ARI4172I Stored procedure server PSERV1 |
| is already stopped. |
|ARI4171I Stored procedure server PSERV1 |
| has been stopped. It cannot be implicitly started. |
|ARI4171I Stored procedure server PSERV10 |
| has been stopped. It cannot be implicitly started. |
|ARI4173I Stored procedure server PSERV15 is waiting for |
| stored procedure PROCX to finish. |
|Stopping stored procedure server PSERV15 |
|ARI4173I Stored procedure server PSERV16 is waiting for |
| stored procedure PROCY to finish. |
|Stopping stored procedure server PSERV16 |
|ARI0065I Operator command processing is complete. |
|ARI4171I Stored procedure server PSERV16 |
| has been stopped. It cannot be implicitly started. |
|ARI4171I Stored procedure server PSERV15 |
| has been stopped. It cannot be implicitly started. |
| |
|stop pserver group group1 group2 |
|Stopping stored procedure server(s) 1997-09-21 16:09:10 |
|ARI4172I Stored procedure server PSERV1 |
| is already stopped. |
|ARI4171I Stored procedure server PSERV1 |
| has been stopped. It cannot be implicitly started. |
|ARI4171I Stored procedure server PSERV2 |
| has been stopped. It cannot be implicitly started. |
|ARI4171I Stored procedure server PSERV3 |
| has been stopped. It cannot be implicitly started. |
|ARI0065I Operator command processing is complete. |
+--------------------------------------------------------------------------------+