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--------------------------+
+-ALL APPLICATIONS-----------------------+
+-ALL BUFFERPOOLS------------------------+
+-APPLICATION--+-APPLID--appl-id---+-----+
| +-AGENTID--agentid--+ |
+-FCM FOR ALL NODES----------------------+
+-LOCKS FOR APPLICATION--appl-id---------+
+--+-ALL-----------+-ON--database-alias--+
+-+-DATABASE-+--+
| +-DB-------+ |
+-APPLICATIONS--+
+-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/SQL00011/ Input database alias = SAMPLE Database status = Active Catalog node number = 0 Catalog network node name = Operating system running at database server= AIX Location of the database = Remote Locks held currently = 7 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in use (Bytes) = 1400 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Current applications waiting on locks = 0 Lock Timeouts = 0 Total sort heap allocated = 0 Total sorts = 3 Total sort time (ms) = 1 Sort overflows = 0 Active sorts = 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 = 32 Buffer pool data physical reads = 13 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) = 364 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 = 34 Direct writes = 0 Direct read requests = 4 Direct write requests = 0 Direct reads elapsed time (ms) = 1 Direct write elapsed time (ms) = 0 Database files closed = 0 Commit statements attempted = 1 Rollback statements attempted = 0 Dynamic statements attempted = 13 Static statements attempted = 1 Failed statement operations = 0 Select SQL statements executed = 1 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 = 8 Binds/precompiles attempted = 0 First database connect timestamp = 04-04-1997 14:29:55.197659 Last reset timestamp = Last backup timestamp = Snapshot timestamp = 04-04-1997 14:32:14.151875 High water mark for connections = 1 High water mark for database heap = 652811 Application connects = 1 Secondary connects total = 0 Applications connected currently = 1 Appls. executing in db manager currently = 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 = 1 Package cache inserts = 1 Application section lookups = 13 Application section inserts = 1 Catalog cache lookups = 2 Catalog cache inserts = 2 Catalog cache overflows = 0 Catalog cache heap full = 0 Agents associated with appls. = 1 Maximum agents associated with appls. = 1 Maximum coordinating agents = 0 Agents waiting on locks = 0
The following is typical output resulting from a request for application information (by specifying either an application ID, an agent ID, all applications, or all applications on a database):
Application Snapshot
Application handle = 5
Application status = UOW Waiting
Status change time = 04-04-1997 14:31:46.930243
Application code page = 850
Application country code = 1
DUOW correlation token = *LOCAL.user1.970404192955
Application name = db2bp_32
Application ID = *LOCAL.user1.970404192956
Sequence number = 0001
Connection request start timestamp = 04-04-1997 14:29:55.197659
Connect request completion timestamp = 04-04-1997 14:29:55.726359
Application idle time = 28 seconds
Authorization ID = USER1
Execution ID = user1
Configuration NNAME of client =
Client database manager product ID = SQL03010
Process ID of client application = 18660
Platform of client application = AIX
Communication protocol of client = Local Client
Database name = SAMPLE
Database path = /home/user1/user1/NODE0000/SQL00011/
Client database alias = sample
Input database alias = SAMPLE
Last reset timestamp =
Snapshot timestamp = 04-04-1997 14:32:14.151875
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 = 75340
Agents working for the application = 1
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Static
Locks held by application = 7
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 = 3
Total sort time (ms) = 1
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 = 32
Buffer pool data physical reads = 13
Buffer pool data writes = 0
Buffer pool index logical reads = 55
Buffer pool index physical reads = 23
Buffer pool index writes = 0
Total buffer pool read time (ms) = 364
Total buffer pool write time (ms) = 0
Time waited for prefetch (ms) = 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 SQL requests since last commit = 13
Commit statements = 1
Rollback statements = 0
Dynamic SQL statements attempted = 13
Static SQL statements attempted = 1
Failed statement operations = 0
Select SQL statements executed = 1
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 = 8
Rows read = 53
Rows written = 0
UOW log space used (Bytes) = 0
Previous UOW completion timestamp = 04-04-1997 14:29:55.728025
UOW start timestamp = 04-04-1997 14:31:46.580691
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 = 1
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 0.070000
Total System CPU Time used by agent (s) = 0.020000
Package cache lookups = 1
Package cache inserts = 1
Application section lookups = 13
Application section inserts = 1
Catalog cache lookups = 0
Catalog cache inserts = 0
Catalog cache overflows = 0
Catalog cache heap full = 0
Most recent operation = Close
Cursor name = SQLCUR201
Most recent operation start timestamp = 04-04-1997 14:31:46.859493
Most recent operation stop timestamp = 04-04-1997 14:31:46.930287
Statement type = Dynamic SQL Statement
Statement = Select
Section number =
Application creator =
Package name =
Cursor name = SQLCUR201
Statement node number = 0
Statement start timestamp = 04-04-1997 14:31:46.859493
Statement stop timestamp = 04-04-1997 14:31:46.930287
Total user CPU time = 0.000000
Total system CPU time = 0.000000
SQL compiler cost estimate in timerons = 9355
SQL compiler cardinality estimate = 1600
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 3
Total sort time = 1
Sort overflows = 0
Rows read = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Number of subsections = 1
Dynamic SQL statement text =
SELECT DEPTNAME, DEPTNUMB, MANAGER, NAME FROM ORG, STAFF
WHERE DEPTNUMB = DEPT AND MANAGER = ID ORDER BY DEPTNAME
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