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 or database:
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---------------------+
'-DYNAMIC SQL--+----------------+-'
'-WRITE TO FILE--'
>--------------------------------------------------------------><
| 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 clients Instance name = smith Number of nodes in DB2 instance = 0 Database manager status = Active Product name = Product identification = Service level = Sort heap allocated = 0 Post threshold sorts = 0 Piped sorts requested = 0 Piped sorts accepted = 0 Start Database Manager timestamp = 02-25-1999 13:26:53.126518 Last reset timestamp = Snapshot timestamp = 02-25-1999 13:45:42.257720 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 = 3 High water mark for agents waiting for a token = 0 Agents registered = 3 Agents waiting for a token = 0 Idle agents = 1 Committed private Memory (Bytes) = 3670016 Buffer Pool Activity Information (BUFFERPOOL) = ON 02-25-1999 13:32:14 Lock Information (LOCK) = ON 02-25-1999 13:32:40 Sorting Information (SORT) = ON 02-25-1999 13:32:40 SQL Statement Information (STATEMENT) = ON 02-25-1999 13:32:14 Table Activity Information (TABLE) = ON 02-25-1999 13:32:40 Unit of Work Information (UOW) = ON 02-25-1999 13:32:14 Agents assigned from pool = 2 Agents created from empty pool = 3 Agents stolen from another application = 0 High water mark for coordinating agents = 3 Max agents overflow = 0 Hash joins after heap threshold exceeded = 0 Total number of gateway connections = 0 Current number of gateway connections = 0 Gateway connections waiting for host reply = 0 Gateway connections waiting for client reply = 0 Gateway inactive connection pool agents = 0 Gateway connection pool agents stolen = 0
The following is typical output resulting from a request for database information:
Database Snapshot Database name = SAMPLE Database path = /home/smith/smith/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 = 02-25-1999 13:31:33.886214 Last reset timestamp = Last backup timestamp = Snapshot timestamp = 02-25-1999 13:40:08.337902 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 = 1 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in use (Bytes) = 432 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 = 316084 Buffer pool data logical reads = 1 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 LSN Gap cleaner triggers = 0 Dirty page steal cleaner triggers = 0 Dirty page threshold cleaner triggers = 0 Time waited for prefetch (ms) = 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 Database files closed = 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 Commit statements attempted = 2 Rollback statements attempted = 0 Dynamic statements attempted = 10 Static statements attempted = 2 Failed statement operations = 0 Select SQL statements executed = 2 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 = 1 Internal rollbacks = 0 Internal rollbacks due to deadlock = 0 Rows deleted = 0 Rows inserted = 0 Rows updated = 0 Rows selected = 16 Binds/precompiles attempted = 0 Log space available to the database (Bytes)= 0 Log space used by the database (Bytes) = 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 Appl id holding the oldest transaction = 0 Package cache lookups = 2 Package cache inserts = 1 Package cache overflows = 0 Package cache high water mark (Bytes) = 108757 Application section lookups = 10 Application section inserts = 1 Catalog cache lookups = 1 Catalog cache inserts = 1 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 DCS database name = DCSDB Host database name = GILROY First database connect timestamp = 02-25-1999 17:00:05.003421 Most recent elapsed time to connect = 0.001200 Most recent elapsed connection duration = 3.443780 Host response time (sec.ms) = 0.000320 Last reset timestamp = Number of SQL statements attempted = 12 Commit statements attempted = 6 Rollback statements attempted = 2 Failed statement operations = 4 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 Gateway communication errors to host = 0 Timestamp of last communication error = None High water mark for gateway connections = 1 Rows selected = 0 Outbound bytes sent = 0 Outbound bytes received = 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 = 3
Application status = UOW Waiting
Status change time = 02-25-1999 13:33:41.446676
Application code page = 819
Application country code = 1
DUOW correlation token = *LOCAL.smith.990225183133
Application name = db2bp
Application ID = *LOCAL.smith.990225183133
Sequence number = 0001
Connection request start timestamp = 02-25-1999 13:31:33.886214
Connect request completion timestamp = 02-25-1999 13:31:34.434114
Application idle time = 6 minutes and 42 seconds
Authorization ID = SMITH
Client login ID = smith
Configuration NNAME of client =
Client database manager product ID = SQL06000
Process ID of client application = 27918
Platform of client application = AIX
Communication protocol of client = Local Client
Outbound communication address =
Outbound communication protocol = APPC
Inbound communication address =
Database name = SAMPLE
Database path = /home/smith/smith/NODE0000/SQL00001/
Client database alias = sample
Input database alias =
Last reset timestamp =
Snapshot timestamp = 02-25-1999 13:40:23.773540
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct IMPLICIT_SCHEMA authority
Indirect SYSADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect IMPLICIT_SCHEMA authority
Coordinating node number = 0
Current node number = 0
Coordinator agent process or thread ID = 26160
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 = 1
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 = 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 = 1
Buffer pool data physical reads = 0
Buffer pool data writes = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool index writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Time waited for prefetch (ms) = 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 SQL requests since last commit = 5
Commit statements = 2
Rollback statements = 0
Dynamic SQL statements attempted = 10
Static SQL statements attempted = 2
Failed statement operations = 0
Select SQL statements executed = 2
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 = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 16
Rows read = 25
Rows written = 0
UOW log space used (Bytes) = 0
Previous UOW completion timestamp = 02-25-1999 13:31:34.434114
Elapsed time of last completed uow (sec.ms)= 0.919533380
UOW start timestamp = 02-25-1999 13:33:41.392167
UOW stop timestamp =
UOW completion status =
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 2
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 0.100000
Total System CPU Time used by agent (s) = 0.020000
Package cache lookups = 2
Package cache inserts = 1
Application section lookups = 10
Application section inserts = 1
Catalog cache lookups = 1
Catalog cache inserts = 1
Catalog cache overflows = 0
Catalog cache heap full = 0
Most recent operation = Select
Most recent operation start timestamp = 02-25-1999 13:33:41.394260
Most recent operation stop timestamp = 02-25-1999 13:33:41.446740
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 = Dynamic SQL Statement
Statement = Select
Section number = 201
Application creator = NULLID
Package name = SQLC28A4
Cursor name = SQLCUR201
Statement node number = 0
Statement start timestamp = 02-25-1999 13:33:41.394260
Statement stop timestamp = 02-25-1999 13:33:41.446740
Elapsed time of last completed stmt(sec.ms)= 0.000000
Total user CPU time = 0.000000
Total system CPU time = 0.000000
SQL compiler cost estimate in timerons = 30
SQL compiler cardinality estimate = 47
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 8
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Number of subsections = 0
Dynamic SQL statement text:
select * from org
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
The following is typical output resulting from a request for dynamic SQL information:
Dynamic SQL Snapshot Result Database name = SAMPLE Database path = /home/smith/smith/NODE0000/SQL00001/ Number of executions = 2 Number of compilations = 1 Worst preparation time (ms) = 126 Best preparation time (ms) = 126 Rows deleted = 0 Rows inserted = 0 Rows read = 24 Rows updated = 0 Rows written = 0 Statement sorts = 0 Total execution time (sec.ms) = 0.060226 Total system cpu time (sec.ms) = 0 Total user cpu time (sec.ms) = 0 Statement text = select * from org
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