DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 24
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:
DBSPACE NUMBER
is the number of the dbspace for which information is being displayed.
The number corresponds to the DBSPACENO column of
SYSTEM.SYSCATALOG and SYSTEM.SYSDBSPACES.
LOCK TYPE
is the type of database resource that is locked. LOCK TYPE can be one of
the following:
DBSP for a lock on an entire dbspace.
IKEY for a lock on a key of an index.
IPAG for a lock on a page of an index.
PAGE for a lock on a page (4 096 bytes) of data.
ROW for a lock on a row of data.
TABL for a lock on an entire table.
(One line is displayed for each active lock type within a dbspace.)
IN
is the number of locks of the indicated type that are intention none locks.
SIX
is the number of locks of the indicated type that are share and intention
exclusive locks.
IS
is the number of locks of the indicated type that are intention share locks.
IX
is the number of locks of the indicated type that are intention exclusive
locks.
S
is the number of locks of the indicated type that are share locks.
U
is the number of locks of the indicated type that are update locks.
X
is the number of locks of the indicated type that are exclusive locks.
Z
is the number of locks of the indicated type that are super exclusive locks.
For more information about lock modes, see the DB2 Server for VSE & VM
Diagnosis Guide and Reference manual.
NUMBER WAITERS
is the number of users waiting for a lock of the type indicated.
The following examples show the output of the SHOW LOCK DBSPACE
command.
Chapter 3. Operating the Application Server
97
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 57. A Typical SHOW LOCK DBSPACE Display on a VSE Operator Console
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.
Figure 58. A Typical SHOW LOCK DBSPACE Display on a VM Operator Console or ISQL
Terminal
SHOW LOCK GRAPH
►► 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.
98
Operation
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 83 on page
137 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:
LOCK REQUESTER
is the agent number followed by the authorization ID of the user who is
currently using that agent. This agent is waiting for a lock held by the
LOCK HOLDER.
LOCK HOLDER
is the agent number followed by the authorization ID of the user who is
currently using that agent. This agent is preventing the LOCK
REQUESTER from getting a lock. The LOCK HOLDER may be waiting for
some other user to free a lock.
WAIT STAT (Wait State)
indicates the wait state of the agent of the LOCK HOLDER. The WAIT
STAT can be:
CKPT
if the agent is waiting for the database manager to do a
checkpoint for the database.
COMM
if the agent is waiting for an SQL request from the user.
(That is, the agent is waiting for the user to ask it to do
something.)
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.
DSPF
if the agent is waiting for a data space page fault to be
resolved.3
I/O
if the agent is waiting for database input or output.
LOCK
if the agent is waiting for a database resource to become
free.
OUTB
if the agent is waiting for a block buffer (directory buffer).
OUTP
if the agent is waiting for a page buffer.
SLD
if the agent is waiting for a data space save list definition
block.3
3. This wait state will only occur when the data spaces are actually being used. For more information, see “Checking the Status of
Users” in the DB2 Server for VSE & VM Performance Tuning Handbook manual.
Chapter 3. Operating the Application Server
99
LOCK TYPE
is the type of database resource for which the LOCK REQUESTER has
requested a lock. The LOCK TYPE can be:
DB for a lock on the database.
DBSP for a lock on an entire dbspace.
IKEY for a lock on a key of an index.
IPAG for a lock on a page of an index.
PAGE for a lock on a page (4 096 bytes) of data.
ROW for a lock on a row of data.
SYS for a special internal system lock.
TABL for a lock on an entire table.
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 or switching to an
alternate log. When the database manager takes 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.
DBSP NUMBR (Dbspace Number)
is the number of the dbspace where the LOCK REQUESTER requested a
lock. If the LOCK TYPE is DB or SYS, blanks appear in this column. The
values for DBSP NUMBR correspond to those in the DBSPACENO columns
of SYSTEM.SYSCATALOG and SYSTEM.SYSDBSPACES.
LOCK QUALF
is an entity that the database manager uses internally to qualify the lock
that is wanted. The LOCK QUALF varies depending on LOCK TYPE:
LOCK TYPE LOCK QUALF
DB
blanks.
DBSP
blanks, because the dbspace number (which is the
qualifier) is already displayed under DBSP NUMBR.
PAGE
the page number that the database manager previously
assigned to the page.
TABL
the internal identifier that the database manager uses for
the table. The values in TABL correspond to those in the
TABID column of SYSTEM.SYSCATALOG.
ROW
the internal identifier that the database manager uses for
the row.
IKEY
an internal value that the database manager uses to
represent the index key. The Lock Qualifier can be an
100
Operation
internal ROW identifier and an internal INDEX identifier if
the index is unique and KEY LEVEL Locking is in effect.
IPAG
the index page number. The database manager assigns
numbers to pages that contain indexes in the same manner
that it assigns data page numbers.
SYS
blanks.
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
REQ STATE (Request State)
is the status of the lock that the LOCK REQUESTER requested. The REQ
STATE can be:
C WAIT
which means waiting to be converted. The agent has made a lock
request, but the lock cannot be allocated now. The agent had
already locked a database resource in a particular lock mode. Now
the agent wants to change the mode of the lock it acquired, so it
issues a new lock request. The agent could issue a new lock
request when, for example, a user is doing read-only work during
a logical unit of work, and then wants to modify the data. The
database manager needs to change a read-only lock to a write lock.
This is a lock conversion.
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.
G WAIT
which means waiting to be granted. The agent made a lock
request, but someone else has already locked the resource. The
agent is waiting to be granted the lock.
DENIED
which means a requested lock was not available, and the database
manager canceled the request. That is, the lock was denied and the
agent is not waiting for it.
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.
Chapter 3. Operating the Application Server
101
REQ MODE (Request Mode)
is the mode of the lock that the agent waiter requested:
IN
indicates that the waiter requested an intention none lock.
SIX indicates that the waiter requested a share and intention exclusive
lock.
IS
indicates that the waiter requested an intention share lock.
IX
indicates that the waiter requested an intention exclusive lock.
S
indicates that the waiter requested a share lock.
U
indicates that the waiter requested an update lock.
X
indicates that the waiter requested an exclusive lock.
Z
indicates that the waiter requested a super exclusive lock.
For more information about lock modes, see the DB2 Server for VSE & VM
Diagnosis Guide and Reference.
DUR (Duration)
indicates the duration of the lock that the waiter requested. DUR can be:
INST (instant)
used to test the request state of the lock.
That is, to test if a particular database
resource (an index key, for example) is in
use. The lock will be freed when the
request is granted.
LONG
indicates that the lock, when granted, is
held until the end of the logical unit of
work.
MED (medium)
indicates that the lock, when granted, will
be held until the database manager
explicitly releases it, or until the end of the
logical unit of work.
SHORT
indicates that the waiter will not require
this lock for the entire duration of the
logical unit of work. Instead, the lock will
be released at the end of the call to the
Database Storage Subsystem (DBSS).
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.)
102
Operation
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 59. A Typical SHOW LOCK GRAPH Display on a VSE Operator Console
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.
Figure 60. A Typical SHOW LOCK GRAPH Display on a VM Operator Console or ISQL
Terminal
SHOW LOCK MATRIX
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 83 on page 137 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:
NLRBS
is the total number of lock request blocks that were defined. You specify
this value for the NLRBS parameter when you start the application server.
IN USE
is the number of lock request blocks that are currently in use by the
database manager. When IN USE starts to approach NLRBS, the database
manager starts a process called escalation. (The process starts when IN
Chapter 3. Operating the Application Server
103
USE reaches NLRBS - 2*NCUSERS.) The escalation process attempts to free
lock request blocks by exchanging lower-level locks (such as page or table
locks) for a higher-level lock (a dbspace lock). Escalation can cause reduced
sharing of data and rollback of some applications because of deadlock.
FREE is the number of lock request blocks that are currently free or available for
use by the system for applications.
NLRBU
is the user limit of lock request blocks. You specify this value in the
NLRBU parameter when you start the database manager. When an
application reaches this limit, the application server starts the escalation
process.
MAX USED BY LUW
is the maximum number of lock request blocks used by any one
application. For example, if three applications accessed the database
manager and required 200, 285, and 100 lock request blocks respectively,
285 would be displayed for MAX USED BY LUW.
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” on page 93 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
104
Operation
12.
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 61. A Typical SHOW LOCK MATRIX Display on a VSE Operator Console
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.
Figure 62. A Typical SHOW LOCK MATRIX Display on a VM Operator Console or ISQL
Terminal
Chapter 3. Operating the Application Server
105
SHOW LOCK USER
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:
AGENT
is the agent number or identifier.
The agent identifier for the checkpoint agent is C.
USER is the authorization ID of the person who is currently using the agent. In
VM, the authorization ID is typically the user’s virtual machine user ID.
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 83 on page 137 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.
DBSPACE NUMBER
is the number of the dbspace for which information is being displayed. If
the LOCK TYPE is DB or SYS, blanks appear in this column. The number
corresponds to the DBSPACENO column of SYSTEM.SYSCATALOG and
SYSTEM.SYSDBSPACES. You see information for each dbspace where the
user has acquired or requested locks.
106
Operation
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.
LOCK TYPE
is the type of database resource that is locked by or is pending locking by
the user. The LOCK TYPE can be one of the following:
DB for a lock on the database.
DBSP for a lock on an entire dbspace.
IKEY for a lock on a key of an index.
INT for a lock on an entire internal dbspace. There is never lock
contention for internal dbspaces.
IPAG for a lock on a page of an index.
PAGE for a lock on a page (4 096 bytes) of data.
ROW for a lock on a row of data.
SYS for a special internal system lock.
TABL for a lock on an entire table.
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 or switching to an
alternate log. 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.)
IN
is the number of locks of the indicated type that are intention none locks.
SIX
is the number of locks of the indicated type that are share and intention
exclusive locks.
IS
is the number of locks of the indicated type that are intention share locks.
IX
is the number of locks of the indicated type that are intention exclusive
locks.
S
is the number of locks of the indicated type that are share locks.
U
is the number of locks of the indicated type that are update locks.
X
is the number of locks of the indicated type that are exclusive locks.
Z
is the number of locks of the indicated type that are super exclusive locks.
For more information about lock modes, see the DB2 Server for VSE & VM
Diagnosis Guide and Reference.
Chapter 3. Operating the Application Server
107
NUMBER WAITERS
is the number of users waiting for a lock of the type indicated. One of
these waiters may be the user for whom the SHOW LOCK USER
command was entered; that is, the lock has not yet been granted.
The following examples show the output of the SHOW LOCK USER command.
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
NUMBER
F4 004 AGENT USER
NUMBER TYPE IN SIX IS IX S U X Z
WAITERS
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 63. A Typical SHOW LOCK USER Display on a VSE Operator Console
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.
Figure 64. A Typical SHOW LOCK USER Display on a VM Operator Console or ISQL
Terminal
SHOW LOCK WANTLOCK
ALL
►► SHOW LOCK WANTLOCK
►◄
authorization ID
USERID
AGENT agent_no
108
Operation
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:
AGENT
is the agent number (identifier).
Note that the agent identifier for the checkpoint agent is the letter C.
USER
is the authorization ID of the person who is currently using the agent. In
VM, the authorization ID is typically the user’s virtual machine
authorization ID.
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 83 on page 137 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.
LOCK TYPE
is the type of database resource that the user wants to lock. LOCK TYPE
can be one of the following:
DB for a lock on the database.
DBSP for a lock on an entire dbspace.
IKEY for a lock on a key of an index.
IPAG for a lock on a page of an index.
PAGE for a lock on a page (4 096 bytes) of data.
Chapter 3. Operating the Application Server
109
ROW for a lock on a row of data.
SYS for a special internal system lock.
TABL for a lock on an entire table.
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 or switching to an
alternate log. 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.
DBSPACE NUMBER
is the number of the dbspace where the agent wants to acquire a lock. If
the LOCK TYPE is DB or SYS, blanks appear in this column. The number
corresponds to the DBSPACENO column of SYSTEM.SYSCATALOG and
SYSTEM.SYSDBSPACES.
LOCK QUALIFIER
is an entity that the database uses internally to qualify the lock that is
wanted. The LOCK QUALIFIER varies depending on LOCK TYPE:
LOCK TYPE LOCK QUALIFIER
DB
blanks.
DBSP
blanks, because the dbspace number (which is the
qualifier) is already displayed under DBSP NUMBR.
PAGE
the page number that the database manager has previously
assigned to the page.
TABL
the internal identifier that the database manager uses for
the table. The values in TABL correspond to those in the
TABID column of SYSTEM.SYSCATALOG.
ROW
the internal identifier that the database manager uses for
the row.
IKEY
an internal value that the database manager uses to
represent the index key. The Lock Qualifier can be an
internal ROW identifier and an internal INDEX identifier if
the index is unique and KEY LEVEL Locking is in effect.
IPAG
the index page number. As with data page numbers, the
application server assigns numbers to pages that contain
indexes.
SYS
blanks.
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
110
Operation
by using the DBSP and TABL values:
SELECT TABID, TNAME FROM SYSTEM.SYSCATALOG
WHERE TABID=tabl_value AND DBSPACENO=dbsp_value
REQUEST STATE
the status of the requested lock. REQUEST STATE can be:
GRANT
means that the agent has requested the lock and has received it.
The agent is currently holding the lock (that is, not waiting for it).
C WAIT
means waiting to be converted. The agent has made a lock request,
but the lock cannot be allocated now. The agent has already locked
a database resource in a particular lock mode. Now the agent
wants to change the mode of the lock it acquired, so it issues a
new lock request. This could happen, for example, when a user is
doing read-only work during a logical unit of work, and then
wants to modify the data. The application server must change a
read-only lock to a write lock. 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.
G WAIT
means waiting to be granted. The agent made a lock request, but
someone else has already locked the resource. The agent is waiting
to be granted the lock.
DENIED
means a requested lock was not available, and the database
manager canceled the request. That is, the lock was denied and the
agent is not waiting for it.
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.
REQUEST MODE
is the mode of the lock for which the user is waiting:
IN
indicates that the user requested an intention none lock.
SIX indicates that the user requested a share and intention exclusive
lock.
IS
indicates that the user requested an intention share lock.
IX
indicates that the user requested an intention exclusive lock.
S
indicates that the user requested a share lock.
Chapter 3. Operating the Application Server
111
U
indicates that the user requested an update lock.
X
indicates that the user requested an exclusive lock.
Z
indicates that the user requested a super exclusive lock.
For more information about lock modes, see the DB2 Server for VSE & VM
Diagnosis Guide and Reference.
DUR (Duration)
indicates the duration of the lock that the waiter requested. DUR can be:
INST (instant)
used to test the request state of the lock.
That is, it is used to test if a particular
database resource (an index key, for
example) is in use. The database manager
frees the lock when the request is granted.
LONG
indicates that when the lock is granted, it
will be held until the end of the logical
unit of work.
MED (medium)
indicates that when the lock is granted, it
will be held until the database manager
explicitly releases it, or until the end of the
logical unit of work.
SHORT
indicates that the waiter will not require
this lock until the logical unit of work
ends. Instead, the lock will be released at
the end of the call to the database storage
subsystem (DBSS).
The following examples show the output from the SHOW LOCK WANTLOCK
command.
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 65. A Typical SHOW LOCK WANTLOCK Display on a VSE Operator Console
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.
Figure 66. A Typical SHOW LOCK WANTLOCK Display on a VM Operator Console or ISQL
Terminal
112
Operation
SHOW LOG
►► SHOW LOG
►◄
The SHOW LOG command displays global log control information and status as
follows:
v
Active log (if alternate logging is enabled).
v
Inactive log (if alternate logging is enabled) and whether it has been archived or
is empty.
v
Log size. The total size of the log in bytes.
v
Log usage. The percentage of log space used and the number of bytes used.
v
If archiving is disabled (that is, LOGMODE=Y or N), SHOW LOG displays the
number of bytes and the percentage of the log remaining 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.
v
If archiving is enabled (LOGMODE=A or L), and an archive is not taking place,
you see the number of bytes and the percentage of the log remaining before
ARCHPCT is reached. If alternate logging is not enabled and ARCHPCT is
reached, the application server begins an archive. If alternate logging is enabled
and ARCHPCT is reached, the application server attempts to switch to the
inactive log if possible. (ARCHPCT is a DB2 Server for VSE & VM initialization
parameter.)
v
The number of log pages is displayed if there are any log pages remaining
before a checkpoint. This is not displayed if an archive is taking place. A
message is displayed if a checkpoint is pending. The DB2 Server for VSE & VM
initialization parameter CHKINTVL determines how many log pages are written
between checkpoints.
The following examples show the output from the SHOW LOG command.
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 Active Log: ALTLGD1
F4 004 Inactive Log: LOGDSK1 - is not archived
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 67. A Typical SHOW LOG Display on a VSE Operator Console
Chapter 3. Operating the Application Server
113
show log
Log Status:
F4 004 Active Log: ALTLGD1
F4 004 Inactive Log: LOGDSK1 - is not archived
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.
Figure 68. A Typical SHOW LOG Display on a VM Operator Console or ISQL Terminal
SHOW LOGHIST
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:
v Database or log archives and restores
v Changes to the LOGMODE startup parameter value
v Log initialization using SQLLOG (COLDLOG).
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:
v If you omit the first parameter, ALL is assumed. The ALL parameter displays all
used entries of the history area.
v If you enter a number (n) as the first parameter, the SHOW LOGHIST command
displays only the most recent n entries. If n is greater than the number of
entries, n is equivalent to ALL.
v If you specify the SERVICE parameter, the SHOW LOGHIST command displays
information generally of interest only to IBM service personnel (the FLAGS
heading). All other output appears whether you specify the SERVICE parameter
or not.
The first line of output for the SHOW LOGHIST command has three possible
responses:
Created last database archive on mm-dd-yyyy at hh:mm:ss at entry n
gives the date and time of the most recent database manager or user
database archive, if any are recorded in the history area. You can use this
information to find the corresponding sys db arch created or user db arch
created entry in the list. You can then use this information to determine the
events since that archive.
114
Operation
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.
Restored database archive at entry n. Database archive was created on
mm-dd-yyyy at hh:mm:ss
gives the date and time of the most recent database restore if one has been
performed since the last database archive. You can use this information to
find the corresponding restore set entries. You will require this information
if the database archive requires restoring again.
No database archives recorded
indicates that no database archives are recorded in the history area. This
result can occur in one of three ways:
v The database has not been archived since the database was generated.
v The database has been archived at least once and you have since done a
log reconfiguration. A log reconfiguration erases the history area.
v The history area became full and the most recent database archive entry
was overwritten. Entries are not often overwritten. You are warned
when the history area becomes full to the ARCHPCT value specified
during initialization.
ENTRY TYPE
is the type of entry shown on that line. It indicates what activity is
recorded in the entry. The user specifies the number of entries shown.
These entries can be the following:
Coldlog reformat
indicates a coldlog was done to reformat the logs. In this
case, the history area remains intact.
Coldlog reconfig
indicates a coldlog was done to reconfigure the logs. In this
case, the history area was erased.
Logmode N indicates that the logmode was changed to N.
Logmode Y indicates that the logmode was changed to Y.
Logmode A indicates that the logmode was changed to A.
Logmode L indicates that the logmode was changed to L.
Sys db arch created
indicates that a database archive was created.
User db arch created
indicates a user archive was created. The date and time
indicate when the SQLEND UARCHIVE command shut
the system down, not when the archive was actually
performed.
Log swtch - &a to &b
indicates that the active log has changed from &a to &b.
&a and &b can be pri (for LOGDSK1) or alt (for
ALTLGD1).
Chapter 3. Operating the Application Server
115
End of restore set
indicates the end of a restore set. The continuity of log
archives is broken. A database restore, either database
manager or user, follows this entry.
Sys db arch restored
indicates the database archive, identified by its date and
time, was successfully restored.
User db arch restored
indicates the user archive, identified by its date and time,
was successfully restored.
Log arch created
indicates a log archive was created.
Log arch crt by restore
indicates a log archive was created to save the active log
because a database restore, either database manager or
user, was initiated. This log will be the last member of the
restore set for the last database archive. If the last database
archive was restored, this log archive would not be listed
among the restored logs because its contents were restored
to the active log before startup.
Log arch restored
indicates that the log archive, identified by its date and
time, was successfully restored.
DATE
is the date of the entry in system date format. There are two exceptions:
v In an entry for a database restore, DATE is the date on which the
restored archive was created.
v Before Version 3 Release 1 of the database manager, the date displayed
for the following entries was 00-00-0000:
Coldlog
Logmode changes
End of restore set.
TIME
is the time at which the entry was made, in 24-hour clock format. There
are two exceptions:
v In an entry for a restore, TIME is the time at which the restored archive
was created.
v Prior to Version 3 Release 1 of the database manager, the time displayed
for the following entries was 00-00-00:
Coldlog
Logmode changes
End of restore set.
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.
FLAGS
shows the value of some internal-use flags intended for IBM service use
only. The heading and data are blank unless you specify the SERVICE
parameter.
LOG FN (Valid for VM only)
is the file name of the output disk file for a log archived to disk.
116
Operation
LOG FT (Valid for VM only)
is the file type of the output disk file for a log archived to disk.
The following examples show the output from the SHOW LOGHIST command.
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 swtch - alt to pri
12-10-1996
16:19:00
9000
F4 004 Log arch created
12-10-1996
16:18:32
9000
F4 004 Log swtch - pri to alt
12-10-1996
16:17: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 69. A Typical SHOW LOGHIST Display on a VSE Operator Console
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 swtch - alt to pri
12-10-1996
16:19:00
9000
SQLMACHO
12108902
Log arch created
12-10-1996
16:18:32
9000
SQLMACHO
12108901
Log swtch - pri to alt
12-10-1996
16:17: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.
Figure 70. A Typical SHOW LOGHIST Display on a VM Operator Console or ISQL Terminal
Chapter 3. Operating the Application Server
117
SHOW POOL
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.
118
Operation
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 71. A Typical SHOW POOL Display on a VSE Operator Console
Chapter 3. Operating the Application Server
119
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.
Figure 72. A Typical SHOW POOL Display on a VM Operator Console or ISQL Terminal
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.
120
Operation
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 73. A Typical SHOW POOL SUMMARY Display on a VSE Operator Console
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.
Figure 74. A Typical SHOW POOL SUMMARY Display on a VM Operator Console or ISQL
Terminal
The SHOW POOL output values have the following meanings.
POOL NO.
is the number of the storage pool in which the dbextents reside. If the
value of the number is positive, the storage pool is recoverable; that is, the
storage pool is one for which the application server does recovery, through
logging every change to the pool. (This is usually the situation.) The
application server records every change to recoverable pools. If the value is
negative, the storage pool is nonrecoverable. The application server does
not log changes to a nonrecoverable pool. The application server does
however, take a checkpoint after every LUW; therefore, partial recovery is
still available. The absolute value of a negative storage pool number is the
number of the storage pool. For example, -54 indicates that storage pool
number 54 is nonrecoverable.
NUMBER OF EXTENTS
is the number of dbextents defined for that storage pool.
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.
SHORT ON STORAGE
is a flag indicating when the storage pool is running out of storage. An
asterisk (*) appears in the SHORT ON STORAGE column of the SHOW
POOL SUMMARY display when the storage pool is running out of storage
(otherwise the column is blank). In the SHOW POOL ALL display, the
words SHORT ON STORAGE appear to the right of POOL NO. when that
storage pool is running out of storage. Otherwise, no words appear. The
level that sets this flag is the value used for the SOSLEVEL parameter
when you started the application server
Chapter 3. Operating the Application Server
121
Note: The SHORT ON STORAGE flag appears as SOS in the SHOW POOL
SUMMARY display in Figure 73 on page 121.
TOTAL PAGES
is the total number of pages in the storage pool (or extent) assigned in the
database directory.
Note: The application server calculates pages based on 57 pages per
cylinder, rather than the actual number of pages per cylinder of the
DASD.
NO. OF PAGES USED
is the number of pages within the storage pool (or extent) that have been
acquired for use as data, header, or index pages.
NO. OF FREE PAGES
is the number of pages within the storage pool (or extent) that are not
currently being used.
NO. OF RESV PAGES
is the number of reserved pages in the storage pool. A minimum of 20
pages is always reserved. See the DB2 Server for VSE & VM Diagnosis Guide
and Reference for a description of reserved pages. Reserved pages apply
only to the total number of pages in the storage pool.
% USED
is the percentage of pages in the storage pool (or extent) that are currently
being used.
TOTAL
is the column total of all information presented in the various columns.
FREE AREAS
is a column of information about deleted dbextents. This information
includes the following:
NUMBER OF DELETED DBEXTENTS
is the total number of dbextents that have been deleted and are
available for reuse. When a deleted dbextent is reused it is no
longer included in this total.
EXTENT NO.
is the number identifying the deleted dbextent. As an example, the
value (2+) indicates that there is empty directory space between
extents 2 and 3. Since there is no dbextent number associated with
this space, to reclaim it you must delete dbextent 2 or dbextent 3.
END is the amount of contiguous unmapped area at the end of the
database directory.
TOTAL PAGES
is the number of pages assigned in the database directory.
MAXIMUM NUMBER OF DBEXTENTS
is the largest number of individual dbextents you are allowed to
have. This number is defined when the database is generated.
SHOW PROC
122
Operation
▼
►► 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:
v
The name of the stored procedure
v
The authorization ID allowed to run the procedure. If this field is blank, any
user can run the procedure.
v
The name of the load module or phase associated with the stored procedure.
This field may contain blanks, if the stored procedure request is queued waiting
for the START PROC command.
v
The name of the stored procedure server group in which the procedure is
running.
v
The status of the stored procedure. The possible status values are:
1. STARTED: where requests for the stored procedure can be processed
2. STOP-QUE: where requests for the stored procedure are queued
3. STOP-REJ: where requests for the stored procedure are rejected
v
Whether the stored procedure is running in the default server group. Note that
this information is displayed only if the stored procedure can run in the default
server group, that is, if the column DEFSERV in SYSTEM.SYSROUTINES is ’Y’
or NULL for that procedure.
v
The number of agents that are currently running the stored procedure
v
The maximum number of agents that have ever been running the procedure
concurrently, since the database manager was started
v
The number of agents that are currently waiting to run the stored procedure
v
The maximum number of agents that have ever been waiting to run the stored
procedure, since the database manager was started
v
The number of times an SQL CALL statement timed out while waiting for a
server to be assigned for the stored procedure
v
The number of times the stored procedure has terminated abnormally
procedure-name Identifies the procedure to be displayed. An asterisk (’*’) contained
in the last position of a procedure names acts as a wildcard. For
example, ’ABC*’ indicates that the command should be applied to
all stored procedures whose names begin with ’ABC’.
authid
The authorization ID for this stored procedure. This field is used
for procedures that can be run only from one authorization ID. If
no authorization ID is specified, information is displayed for all
procedures with the name specified.
Chapter 3. Operating the Application Server
123
Displays information for all stored procedures that are in the cache.
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 MYPROC that is accessible only by authorization ID
USER1.
The following example shows the output from the SHOW PROC command.
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.
Figure 75. A Typical SHOW PROC Display on a VM Operator Console (Part
1
of
2)
124
Operation
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.
Figure 75. A Typical SHOW PROC Display on a VM Operator Console (Part
2
of
2)
SHOW PSERVER
▼
►► 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:
v The name of the stored procedure server
Chapter 3. Operating the Application Server
125
v The name of the group in which it resides
v The status of the server. The possible values:
STARTING
The START PSERVER command is in progress.
STARTED
The stored procedure server is running.
STOPPED
The stored procedure server has not been started, or has been stopped.
STOPPING
A STOP PSERVER command has been issued, but the server has not
stopped yet because a stored procedure is executing.
v If a stored procedure is currently running in that server, the name of the stored
procedure.
v The authorization ID that is allowed to run the stored procedure. If this field is
blank, any user can run the stored procedure.
Note: This command can be issued from the operator console or from ISQL.
GROUP
Indicates that any names that are specified are server group names, rather
than individual server names. This enables the database administrator to
show information for all servers in a given group with a single command.
name The name of the stored procedure server, or of the stored procedure server
group, for which information is to be displayed.
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’.
Shows information for all stored procedure servers. Note that ’SHOW
PSERVER GROUP *’ and SHOW PSERVER *’ have the same result - all
stored procedure server information will be displayed.
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.
126
Operation
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.
Figure 76. A Typical SHOW PSERVER Display on a VM Operator Console
SHOW SQLDBGEN
►► SHOW SQLDBGEN
►◄
The SHOW SQLDBGEN command displays the current database configuration.
Chapter 3. Operating the Application Server
127
AR
MSG F4
AR 015
1I40I READY
F4 004
ARI0062A SQLDS :
F4-004
Enter a DB2 Server for VSE operator
command.
4 show
sqldbgen
F4 004
F4 004
The current database definitions
are:
F4 004
CUREXTNT=2
F4 004
MAXPOOLS=32
F4 004
MAXEXTNT=64
F4 004
MAXDBSPC=1000
F4 004
F4 004
Non-recoverable storage pools:
F4 004
POOL
2
NOLOG
F4 004
F4 004
Storage pool assignments:
F4 004
Dbextent
Storage Pool
F4 004
1
1
F4 004
2
2
F4 004
F4 004
Current DBSPACES:
F4 004
Type
Pages
Pool
F4 004
PUBLIC
12800
1
F4 004
PUBLIC
2048
1
F4 004
PUBLIC
8192
1
F4 004
PUBLIC
1024
1
F4 004
PUBLIC
512
1
F4 004
PUBLIC
512
1
F4 004
PUBLIC
512
2
F4 004
PUBLIC
512
2
F4 004
PRIVATE
128
2
F4 004
PRIVATE
512
2
F4 004
PRIVATE
128
2
F4 004
F4 004
Internal DBSPACES:
F4 004
INTERNAL
80
1024 1
F4 004
F4 004
Figure 77. A Typical SHOW SQLDBGEN Display on a VSE Operator Console
128
Operation
show sqldbgen
The current DB definitions are:
CUREXTNT=2
MAXPOOLS=32
MAXEXTNT=64
MAXDBSPC=1000
Non-recoverable storage pools:
POOL
2
NOLOG
Storage pool assignments:
Dbextent
Storage Pool
1
1
2
2
Current DBSPACES:
Type
Pages
Pool
PUBLIC
12800
1
PUBLIC
2048
1
PUBLIC
8192
1
PUBLIC
1024
1
PUBLIC
512
1
PUBLIC
512
1
PUBLIC
512
2
PUBLIC
512
2
PRIVATE
128
2
PRIVATE
512
2
PRIVATE
128
2
Internal DBSPACES:
INTERNAL
80
1024 1
Figure 78. A Typical SHOW SQLDBGEN Display on a VM Operator Console or ISQL
Terminal
The SHOW SQLDBGEN columns are defined as follows:
CUREXTNT
The number of dbextents currently defined. This is the current value of
CUREXTNT from the database directory.
MAXPOOLS
The maximum number of storage pools as established at database
generation time. A storage pool is made up of one or more database
extents (DBEXTENTs). This is the value of MAXPOOLS from the resid
SQLDBGEN file.
MAXEXTNT
The maximum number of database extents (dbextents) as established at
database generation time. Dbextents are the physical media on which the
database manager stores the data. This is the value of MAXEXTNT from
the resid SQLDBGEN file.
It is possible to reach the maximum physical page limit before reaching the
maximum number of dbextents. See “Total number of physical pages” on
page 83 for more information. For information on what to do if you reach
the maximum physical page limit, refer to the DB2 Server for VSE & VM
Database Administration manual.
MAXDBSPC
The maximum number of dbspaces as established at database generation
time. MAXDBSPC includes public, private, and internal dbspaces. In the
resid SQLDBGEN file, this is the value of MAXDBSPC.
Chapter 3. Operating the Application Server
129
Non-recoverable storage pools
A list of storage pool(s) which are nonrecoverable. Existing storage pools
which are not listed have been defined with the default LOG option.
Storage pool assignments
The current assignment of dbextents to storage pools.
Current DBSPACES
The current public and private dbspaces, the number of pages defined for
each, and to which storage pool each dbspace is assigned.
Internal DBSPACES
The current number of equal size internal dbspaces defined to the
database.
SHOW STORAGE
►► 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.
130
Operation
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 VM
Chapter 3. Operating the Application Server
131
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.
Figure 80. SHOW STORAGE Display on VSE
The SHOW STORAGE display fields are described below.
Header line
indicates the date and time that the SHOW STORAGE command started
processing.
AGENT
is the agent number or the agent identifier. PROTOTYPE is listed here
because it has storage properties similar to an agent.
LOC is the location of storage, which can reside either above or below the 16MB
boundary. Storage that may reside anywhere above or below the 16MB
boundary is indicated by the letter A. Storage that is restricted to below
the 16MB boundary is indicated by the letter B.
ALLOCATED
is the current amount of storage set aside for the indicated user or for the
system.
IN USE
is the total working storage currently being used by the agent or agents.
FREE is the total working storage allocated that is not currently being used. It is
likely that this free storage is not contiguous.
HIGHSTOR
refers to the maximum amount of storage that has been allocated to this
point in time for each indicated user (or for the system). This storage value
will reset to the current allocated value when the RESET HIGHSTOR
command is entered.
132
Operation
USERID
is the user ID attributed to the agent when the HIGHSTOR value was set.
SUMMARY
is the heading that precedes the summary information for the SHOW
STORAGE display. All totals displayed are approximate.
QUEUES
the summary values of all the working storage queues.
Note: Some storage, such as storage used for local variables in SQL/DS
code, is not included in these numbers.
PACKAGES
is the summary of the storage queues maintained for packages.
SYSTEM
is the total of all system storage requests. It can contain both direct system
storage requests and storage added to the working storage queues.
SHOW SYSTEM
►► SHOW SYSTEM
►◄
The SHOW SYSTEM command displays much of the data described in the other
SHOW commands.
v Date and time are displayed.
v The output of each of the following commands is displayed: SHOW DBEXTENT,
SHOW LOG, SHOW CONNECT ACTIVE, and SHOW LOCK MATRIX.
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” on page 152 for more information about forceable agents.
The following examples show the output from the SHOW SYSTEM command.
Chapter 3. Operating the Application Server
133
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
Active Log: LOGDSK1
F4
004
Inactive Log: ALTLGD1 - is not archived
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 VSE
134
Operation
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:
Active Log: LOGDSK1
Inactive Log: ALTLGD1 - has not been archived
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.
Figure 82. SHOW SYSTEM Display on VM
SHOW USERS (Valid for VM only)
►► 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:
v The number of users connected to the database machine.
Chapter 3. Operating the Application Server
135
v The number of users who are using an agent structure.
The lines of output have these meanings:
n USER(S) ARE CONNECTED TO SQL/DS
is the number (n) of users connected to the database machine.
n SQL/DS USER(S) ARE ACTIVE
is the number (n) of users who are using an agent structure.
n SQL/DS USER(S) ARE WAITING
is the number (n) of users connected to the database virtual
machine and waiting to use an agent structure.
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” on page 51.
n SQL/DS USER(S) ARE INACTIVE
is the number (n) of users connected to the database machine,
neither using an agent structure nor waiting to use an agent
structure.
User ID
is the user’s VM user ID or user ID in another non-VM
environment.
SQL ID
is the user’s SQL authorization ID.
n SQL/DS AGENTS ARE AVAILABLE
is the difference in value between the value specified by the DB2
Server for VM NCUSERS initialization parameter and the number
of users currently using an agent structure.
n SQL/DS USER CONNECTIONS ARE AVAILABLE
is the number (n) of connections available to any new users who
want to connect to the database machine.
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.
136
Operation