Collects database manager status information and returns it to a user-allocated data buffer. The information returned represents a snapshot of the database manager operational status at the time the command was issued.
Scope
This command can be invoked from any node in the db2nodes.cfg file. It acts only on that node or partition.
Authorization
One of the following:
Required Connection
Instance. To obtain a snapshot of a remote instance, it is necessary to first attach to that instance.
>>-GET SNAPSHOT FOR--------------------------------------------->
>-----+-+-DATABASE MANAGER-+------------------------------+----><
| +-DB MANAGER-------+ |
| '-DBM--------------' |
+-ALL--+-----+--DATABASES---------------------------+
| '-DCS-' |
+-ALL--+-----+--APPLICATIONS------------------------+
| '-DCS-' |
+-ALL BUFFERPOOLS-----------------------------------+
+-+-----+--APPLICATION----+-APPLID--appl-id-------+-+
| '-DCS-' '-AGENTID--appl-handle--' |
+-FCM FOR ALL NODES---------------------------------+
+-LOCKS FOR APPLICATION--+-APPLID--appl-id-------+--+
| '-AGENTID--appl-handle--' |
'--+-ALL--------------------+---ON--database-alias--'
+-+-----+--+-DATABASE-+--+
| '-DCS-' '-DB-------' |
+-+-----+--APPLICATIONS--+
| '-DCS-' |
+-TABLES-----------------+
+-TABLESPACES------------+
+-LOCKS------------------+
'-BUFFERPOOLS------------'
|
| Note: | The monitor switches must be turned on to get some statistics (see UPDATE MONITOR SWITCHES). |
Command Parameters
Examples
In the following sample output listings, some of the information may not be available, depending on whether or not the appropriate database system monitor recording switch is turned on (see UPDATE MONITOR SWITCHES). If the information is unavailable, Not Collected appears in the output.
For more information about the fields displayed in the following output listings, see the System Monitor Guide and Reference.
The following is typical output resulting from a request for database manager information:
Database Manager Snapshot Node type = Database Server with local and remote clients Instance name = user1 Database manager status = Active Product name = Product identification = Service level = Sort heap allocated = 0 Post threshold sorts = 0 Piped sorts requested = 3 Piped sorts accepted = 3 Start Database Manager timestamp = 04-04-1997 10:54:32.357375 Last reset timestamp = 04-04-1997 14:28:54.799017 Snapshot timestamp = 04-06-1997 14:27:19.996209 Remote connections to db manager = 0 Remote connections executing in db manager = 0 Local connections = 1 Local connections executing in db manager = 0 Active local databases = 1 High water mark for agents registered = 2 High water mark for agents waiting for a token = 1 Agents registered = 2 Agents waiting for a token = 0 Idle agents = 0 Committed private Memory (Bytes) = 12435456 Buffer Pool Activity Information (BUFFERPOOL) = ON 04-04-1997 14:29:42 Lock Information (LOCK) = ON 04-04-1997 14:29:42 Sorting Information (SORT) = ON 04-04-1997 14:29:42 SQL Statement Information (STATEMENT) = ON 04-04-1997 14:29:42 Table Activity Information (TABLE) = ON 04-04-1997 14:29:42 Unit of Work Information (UOW) = ON 04-04-1997 14:29:42 Agents assigned from pool = 5 Agents created from empty pool = 2 Agents stolen from another application = 0 High water mark for coordinating agents = 2 Max agents overflow = 0
The following is typical output resulting from a request for database information:
Database Snapshot Database name = SAMPLE Database path = /home/user1/user1/NODE0000/SQL00001/ Input database alias = Database status = Active Catalog node number = 0 Catalog network node name = Operating system running at database server= AIX Location of the database = Local First database connect timestamp = 05-15-1998 17:38:50.721558 Last reset timestamp = Last backup timestamp = Snapshot timestamp = 05-15-1998 17:42:50.788090 High water mark for connections = 1 Application connects = 1 Secondary connects total = 0 Applications connected currently = 1 Appls. executing in db manager currently = 0 Agents associated with applications = 1 Maximum agents associated with applications= 1 Maximum coordinating agents = 1 Locks held currently = 0 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in use (Bytes) = 900 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Agents currently waiting on locks = 0 Lock Timeouts = 0 Total sort heap allocated = 0 Total sorts = 0 Total sort time (ms) = 0 Sort overflows = 0 Active sorts = 0 High water mark for database heap = 341313 Buffer pool data logical reads = Not Collected Buffer pool data physical reads = Not Collected Asynchronous pool data page reads = Not Collected Buffer pool data writes = Not Collected Asynchronous pool data page writes = Not Collected Buffer pool index logical reads = Not Collected Buffer pool index physical reads = Not Collected Asynchronous pool index page reads = Not Collected Buffer pool index writes = Not Collected Asynchronous pool index page writes = Not Collected Total buffer pool read time (ms) = Not Collected Total buffer pool write time (ms) = Not Collected Total elapsed asynchronous read time = Not Collected Total elapsed asynchronous write time = Not Collected Asynchronous read requests = Not Collected LSN Gap cleaner triggers = Not Collected Dirty page steal cleaner triggers = Not Collected Dirty page threshold cleaner triggers = Not Collected Time waited for prefetch (ms) = Not Collected Direct reads = Not Collected Direct writes = Not Collected Direct read requests = Not Collected Direct write requests = Not Collected Direct reads elapsed time (ms) = Not Collected Direct write elapsed time (ms) = Not Collected Database files closed = Not Collected Data pages copied to extended storage = Not Collected Index pages copied to extended storage = Not Collected Data pages copied from extended storage = Not Collected Index pages copied from extended storage = Not Collected Commit statements attempted = 4 Rollback statements attempted = 0 Dynamic statements attempted = 66 Static statements attempted = 4 Failed statement operations = 0 Select SQL statements executed = 3 Update/Insert/Delete statements executed = 0 DDL statements executed = 0 Internal automatic rebinds = 0 Internal rows deleted = 0 Internal rows inserted = 0 Internal rows updated = 0 Internal commits = 0 Internal rollbacks = 0 Internal rollbacks due to deadlock = 0 Rows deleted = 0 Rows inserted = 0 Rows updated = 0 Rows selected = 51 Binds/precompiles attempted = 0 Maximum secondary log space used (Bytes) = 0 Maximum total log space used (Bytes) = 0 Secondary logs allocated currently = 0 Log pages read = 0 Log pages written = 0 Package cache lookups = 3 Package cache inserts = 2 Application section lookups = 66 Application section inserts = 3 Catalog cache lookups = 2 Catalog cache inserts = 2 Catalog cache overflows = 0 Catalog cache heap full = 0 Number of hash joins = 0 Number of hash loops = 0 Number of hash join overflows = 0 Number of small hash join overflows = 0
The following is typical output resulting from a request for DCS database information:
DCS Database Snapshot Database name = DCSDB First database connect timestamp = 05-21-1998 16:28:39.517919 Last reset timestamp = Number of SQL statements attempted = 2 Commit statements attempted = 1 Rollback statements attempted = 0 Failed statement operations = 0 Total number of gateway connections = 0 Current number of gateway connections = 1 Gateway conn. waiting for host reply = 0 Gateway conn. waiting for client reply = 1 High water mark for gateway connections = 1 Rows selected = 0
The following is typical output resulting from a request for application information (by specifying either an application ID, an application handle, all applications, or all applications on a database):
Application Snapshot
Application handle = 1
Application status = UOW Waiting
Status change time = 05-15-1998 17:40:52.907292
Application code page = 819
Application country code = 1
DUOW correlation token = *LOCAL.user1.980515213850
Application name = db2bp
Application ID = *LOCAL.user1.980515213850
Sequence number = 0001
Connection request start timestamp = 05-15-1998 17:38:50.721558
Connect request completion timestamp = 05-15-1998 17:38:51.464622
Application idle time = 1 minute and 10 seconds
Authorization ID = USER1
Client login ID = user1
Configuration NNAME of client =
Client database manager product ID = SQL05020
Process ID of client application = 35544
Platform of client application = AIX
Communication protocol of client = Local Client
Outbound communication address =
Outbound communication protocol =
Inbound communication address =
Database name = SAMPLE
Database path = /home/user1/user1/NODE0000/SQL00001/
Client database alias = sample
Input database alias =
Last reset timestamp =
Snapshot timestamp = 05-15-1998 17:42:02.285846
The highest authority level granted =
Indirect SYSADM authority
Coordinating node number = 0
Current node number = 0
Coordinator agent process or thread ID = 20668
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic
Locks held by application = 0
Lock waits since connect = 0
Time application waited on locks (ms) = 0
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = 0
Total sorts = 0
Total sort time (ms) = 0
Total sort overflows = 0
Data pages copied to extended storage = Not Collected
Index pages copied to extended storage = Not Collected
Data pages copied from extended storage = Not Collected
Index pages copied from extended storage = Not Collected
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool data writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool index writes = Not Collected
Total buffer pool read time (ms) = Not Collected
Total buffer pool write time (ms) = Not Collected
Time waited for prefetch (ms) = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of SQL requests since last commit = 0
Commit statements = 4
Rollback statements = 0
Dynamic SQL statements attempted = 66
Static SQL statements attempted = 4
Failed statement operations = 0
Select SQL statements executed = 3
Update/Insert/Delete statements executed = 0
DDL statements executed = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 0
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 51
Rows read = 61
Rows written = 0
UOW log space used (Bytes) = 0
Previous UOW completion timestamp = 05-15-1998 17:39:51.885139
UOW start timestamp = 05-15-1998 17:40:52.859618
UOW stop timestamp = 05-15-1998 17:40:52.907427
UOW completion status = Committed - Commit Statement
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 3
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 0.160000
Total System CPU Time used by agent (s) = 0.020000
Package cache lookups = 3
Package cache inserts = 2
Application section lookups = 66
Application section inserts = 3
Catalog cache lookups = 2
Catalog cache inserts = 2
Catalog cache overflows = 0
Catalog cache heap full = 0
Most recent operation = Static Commit
Section number = 0
Application creator = NULLID
Package name = SQLC28A0
Most recent operation start timestamp = 05-15-1998 17:40:52.906685
Most recent operation stop timestamp = 05-15-1998 17:40:52.907382
Agents associated with the application = 1
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Static SQL Statement
Statement = Static Commit
Section number = 0
Application creator = NULLID
Package name = SQLC28A0
Cursor name =
Statement node number = 0
Statement start timestamp = 05-15-1998 17:40:52.906685
Statement stop timestamp = 05-15-1998 17:40:52.907382
Total user CPU time = 0.000000
Total system CPU time = 0.000000
SQL compiler cost estimate in timerons = 0
SQL compiler cardinality estimate = 0
Degree of parallelism requested = 1
Number of agents working on statement = 0
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Number of subsections = 0
The following is typical output resulting from a request for DCS application information (by specifying either a DCS application ID, a DCS application handle, all DCS applications, or all DCS applications on a database):
DCS Application Snapshot Client application ID = 09151251.04D6.980521202839 Sequence number = 0001 Authorization ID = NEWTON Application name = db2bp Application handle = 0 Application status = waiting for request Status change time = 05-21-1998 16:35:27.670354 Client DB alias = MVSDB Client node = antman Client release level = SQL05020 Client platform = AIX Client protocol = TCP/IP Client codepage = 819 Process ID of client application = 35754 Client login ID = user1 Host application ID = G9151251.G4D7.980521202840 Sequence number = 0000 Host DB name = GILROY Host release level = DSN05011 Host CCSID = 500 Outbound communication address = 9.21.21.92 5021 Outbound communication protocol = TCP/IP Inbound communication address = 9.31.12.34 334 First database connect timestamp = 05-21-1998 16:28:39.517919 Time spent on gateway processing = 0.334215 Last reset timestamp = Rows selected = 0 Number of SQL statements attempted = 2 Failed statement operations = 0 Commit statements = 1 Rollback statements = 0 Inbound bytes received = 392 Outbound bytes sent = 136 Outbound bytes received = 178 Inbound bytes sent = 190 Number of open cursors = 0 Application idle time = 53 seconds UOW completion status = Committed - Commit Statement Previous UOW completion timestamp = UOW start timestamp = 05-21-1998 16:35:27.252375 UOW stop timestamp = 05-21-1998 16:35:27.670290 Inbound bytes received for UOW = 180 Outbound bytes sent for UOW = 136 Outbound bytes received for UOW = 178 Inbound bytes sent for UOW = 190 Most recent operation = Static Commit Most recent operation start timestamp = 05-21-1998 16:35:27.284183 Most recent operation stop timestamp = 05-21-1998 16:35:27.670290 Statement = Static Commit Section number = 0 Application creator = NULLID Package name = SQLC28A0 SQL compiler cost estimate in timerons = 0 SQL compiler cardinality estimate = 0 Statement start timestamp = 05-21-1998 16:35:27.284183 Statement stop timestamp = 05-21-1998 16:35:27.670290 Rows fetched = 0 Time spent on gateway processing = 0.333740 Inbound bytes received for statement = 0 Outbound bytes sent for statement = 10 Outbound bytes received for statement = 54 Inbound bytes sent for statement = 0
The following is typical output resulting from a request for buffer pool information:
Bufferpool Snapshot Bufferpool name = IBMDEFAULTBP Database name = SAMPLE Database path = /home/user1/user1/NODE0000/SQL00011/ Input database alias = SAMPLE Buffer pool data logical reads = 32 Buffer pool data physical reads = 13 Buffer pool data writes = 0 Buffer pool index logical reads = 55 Buffer pool index physical reads = 23 Total buffer pool read time (ms) = 364 Total buffer pool write time (ms) = 0 Database files closed = 0 Asynchronous pool data page reads = 0 Asynchronous pool data page writes = 0 Buffer pool index writes = 0 Asynchronous pool index page reads = 0 Asynchronous pool index page writes = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 34 Direct writes = 0 Direct read requests = 4 Direct write requests = 0 Direct reads elapsed time (ms) = 1 Direct write elapsed time (ms) = 0 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0
The following is typical output resulting from a request for table information:
Table Snapshot First database connect timestamp = 04-04-1997 14:29:55.197659 Last reset timestamp = Snapshot timestamp = 04-04-1997 14:32:14.151875 Database name = SAMPLE Database path = /home/user1/user1/NODE0000/SQL00011/ Input database alias = SAMPLE Number of accessed tables = 6 Table Schema Table Name Table Type Rows Written Rows Read Overflows -------------------- -------------------- -------------------- ------------ ---------- ---------- USER1 STAFF User 0 35 0 USER1 ORG User 0 8 0 SYSIBM SYSTABLES Catalog 0 2 0 SYSIBM SYSTABLESPACES Catalog 0 3 0 SYSIBM SYSPLAN Catalog 0 1 0 SYSIBM SYSDBAUTH Catalog 0 3 0
The following is typical output resulting from a request for table space information:
Tablespace Snapshot First database connect timestamp = 04-04-1997 14:29:55.197659 Last reset timestamp = Snapshot timestamp = 04-04-1997 14:32:14.151875 Database name = SAMPLE Database path = /home/user1/user1/NODE0000/SQL00011/ Input database alias = SAMPLE Number of accessed tablespaces = 3 Tablespace name = SYSCATSPACE Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 26 Buffer pool data physical reads = 11 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 55 Buffer pool index physical reads = 23 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 342 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 34 Direct writes = 0 Direct read requests = 4 Direct write requests = 0 Direct reads elapsed time (ms) = 1 Direct write elapsed time (ms) = 0 Number of files closed = 0 Tablespace name = TEMPSPACE1 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 0 Buffer pool data physical reads = 0 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 0 Buffer pool index physical reads = 0 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 0 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Number of files closed = 0 Tablespace name = USERSPACE1 Data pages copied to extended storage = 0 Index pages copied to extended storage = 0 Data pages copied from extended storage = 0 Index pages copied from extended storage = 0 Buffer pool data logical reads = 6 Buffer pool data physical reads = 2 Asynchronous pool data page reads = 0 Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index logical reads = 0 Buffer pool index physical reads = 0 Asynchronous pool index page reads = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 Total buffer pool read time (ms) = 22 Total buffer pool write time (ms) = 0 Total elapsed asynchronous read time = 0 Total elapsed asynchronous write time = 0 Asynchronous read requests = 0 Direct reads = 0 Direct writes = 0 Direct read requests = 0 Direct write requests = 0 Direct reads elapsed time (ms) = 0 Direct write elapsed time (ms) = 0 Number of files closed = 0
The following is typical output resulting from a request for lock information:
Database Lock Snapshot Database name = SAMPLE Database path = /home/user1/user1/NODE0000/SQL00011/ Input database alias = SAMPLE Locks held = 7 Applications currently connected = 1 Applications currently waiting on locks = 0 Snapshot timestamp = 04-04-1997 14:32:14.151875 Application handle = 5 Application ID = *LOCAL.user1.970404192956 Sequence number = 0001 Application name = db2bp_32 Authorization ID = USER1 Application status = UOW Waiting Status change time = Application code page = 850 Locks held = 7 Total wait time (ms) = 0 Object Name Object Type Tablespace Name Table Schema Table Name Mode Status ----------- --------------- -------------------- -------------------- -------------------- ---- ---------- 1545 Row SYSCATSPACE SYSIBM SYSTABLES NS Granted 1544 Row SYSCATSPACE SYSIBM SYSTABLES NS Granted 2 Table SYSCATSPACE SYSIBM SYSTABLES IS Granted 27 Table SYSCATSPACE SYSIBM SYSTABLESPACES S Granted 257 Row SYSCATSPACE SYSIBM SYSPLAN S Granted 7 Table SYSCATSPACE SYSIBM SYSPLAN IS Granted 0 Internal S Granted
Usage Notes
To obtain a snapshot from a remote instance (or a different local instance), it is necessary to first attach to that instance. If an alias for a database residing at a different instance is specified, an error message is returned.
To obtain some statistics, it is necessary that the database system monitor switches are turned on.
No data is returned following a request for table information if any of the following is true:
See Also