DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
Collects status information and formats the output for the user. The information returned represents a snapshot of the database manager operational status at the time the command was issued.
In a partitioned database environment, this command can be invoked from any database partition defined in the db2nodes.cfg file. It acts only on that database partition.
Instance. If there is no instance attachment, a default instance attachment is created.
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 DBPARTITIONNUMS----------------------------+ +-LOCKS FOR APPLICATION--+-APPLID--appl-id------+--------+ | '-AGENTID--appl-handle-' | +-ALL REMOTE_DATABASES-----------------------------------+ +-ALL REMOTE_APPLICATIONS--------------------------------+ +---DYNAMIC SQL--ON--database-alias--+---------------+---+ | '-WRITE TO FILE ' | '-+-ALL-------------------+--ON--database-alias----------' +-+-----+--+-DATABASE-+-+ | '-DCS-' '-DB-------' | +-+-----+--APPLICATIONS-+ | '-DCS-' | +-TABLES----------------+ +-TABLESPACES-----------+ +-LOCKS-----------------+ +-BUFFERPOOLS-----------+ +-REMOTE_DATABASES------+ '-REMOTE_APPLICATIONS---' >--+----------------------------------------+------------------>< +-AT DBPARTITIONNUM--db-partition-number-+ '-GLOBAL---------------------------------'
The monitor switches must be turned on in order to collect some statistics.
get snapshot for database manager Database Manager Snapshot
Node name =
Node type = Enterprise Server Edition with local and remote clients
Instance name = DB2
Number of database partitions in DB2 instance = 1
Database manager status = Active
Product name = DB2 v9.5.0.535
Service level = s070101 (NT32)
Private Sort heap allocated = 0
Private Sort heap high water mark = 0
Post threshold sorts = Not Collected
Piped sorts requested = 0
Piped sorts accepted = 0
Start Database Manager timestamp = 01/10/2007 15:18:36.241035
Last reset timestamp =
Snapshot timestamp = 01/10/2007 15:28:26.989789
Remote connections to db manager = 3
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 = 0
Agents registered = 8
Idle agents = 0
Committed private Memory (Bytes) = 8912896
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = OFF
Lock Information (LOCK) = ON 01/10/2007 15:22:43.145437
Sorting Information (SORT) = OFF
SQL Statement Information (STATEMENT) = OFF
Table Activity Information (TABLE) = OFF
Take Timestamp Information (TIMESTAMP) = ON 01/10/2007 15:18:36.241035
Unit of Work Information (UOW) = OFF
Agents assigned from pool = 3
Agents created from empty pool = 11
Agents stolen from another application = 0
High water mark for coordinating agents = 9
Hash joins after heap threshold exceeded = 0
OLAP functions 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 request = 0
Gateway connection pool agents stolen = 0
Node FCM information corresponds to = 1
Free FCM buffers = 13425
Total FCM buffers = 13425
Free FCM buffers low water mark = 13420
Maximum number of FCM buffers = 28640
Free FCM channels = 8036
Total FCM channels = 8055
Free FCM channels low water mark = 8036
Maximum number of FCM channels = 28640
Number of FCM nodes = 3
Node Total Buffers Total Buffers Connection
Number Sent Received Status
----------- ------------------ ------------------ -----------------
1 1 1 Active
2 1 0 Active
10 1 0 Active
Node FCM information corresponds to = 2
Free FCM buffers = 13425
Total FCM buffers = 13425
Free FCM buffers low water mark = 13420
Maximum number of FCM buffers = 28640
Free FCM channels = 8036
Total FCM channels = 8055
Free FCM channels low water mark = 8036
Maximum number of FCM channels = 28640
Number of FCM nodes = 3
Node Total Buffers Total Buffers Connection
Number Sent Received Status
----------- ------------------ ------------------ -----------------
1 0 1 Active
2 1 1 Active
10 0 0 Active
Node FCM information corresponds to = 10
Free FCM buffers = 13425
Total FCM buffers = 13425
Free FCM buffers low water mark = 13420
Maximum number of FCM buffers = 28640
Free FCM channels = 8036
Total FCM channels = 8055
Free FCM channels low water mark = 8036
Maximum number of FCM channels = 28640
Number of FCM nodes = 3
Node Total Buffers Total Buffers Connection
Number Sent Received Status
----------- ------------------ ------------------ -----------------
1 0 1 Active
2 0 0 Active
10 1 1 Active
Memory usage for database manager:
Node number = 0
Memory Pool Type = Other Memory
Current size (bytes) = 11534336
High water mark (bytes) = 11599872
Configured size (bytes) = 34275328
Node number = 0
Memory Pool Type = Database Monitor Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 327680
Node number = 0
Memory Pool Type = FCMBP Heap
Current size (bytes) = 655360
High water mark (bytes) = 655360
Configured size (bytes) = 851968 get snapshot for application agentid 29 Application Snapshot
Application handle = 29
Application status = Lock-wait
Status change time = Not Collected
Application code page = 819
Application country/region code = 1
DUOW correlation token = *LOCAL.jwr.070222182152
Application name = db2bp
Application ID = *LOCAL.jwr.070222182152
Sequence number = 00001
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = 02/22/2007 13:21:52.587168
Connect request completion timestamp = 02/22/2007 13:21:53.291779
Application idle time =
CONNECT Authorization ID = JWR
Client login ID = jwr
Configuration NNAME of client = gilera
Client database manager product ID = SQL09050
Process ID of client application = 843852
Platform of client application = AIX 64BIT
Communication protocol of client = Local Client
Inbound communication address = *LOCAL.jwr
Database name = SAMPLE
Database path = /home/jwr/jwr/NODE0000/SQL00001/
Client database alias = SAMPLE
Input database alias =
Last reset timestamp =
Snapshot timestamp = 02/22/2007 13:22:39.766300
Authorization level granted =
User authority:
DBADM authority
CREATETAB authority
BINDADD authority
CONNECT authority
CREATE_NOT_FENC authority
LOAD authority
IMPLICIT_SCHEMA authority
CREATE_EXT_RT authority
QUIESCE_CONN authority
Group authority:
SYSADM authority
CREATETAB authority
BINDADD authority
CONNECT authority
IMPLICIT_SCHEMA authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 1801
Current Workload ID = 1
Agents stolen = 0
Agents waiting on locks = 1
Maximum associated agents = 1
Priority at which application agents work = 0
Priority type = Dynamic
Lock timeout (seconds) = -1
Locks held by application = 4
Lock waits since connect = 1
Time application waited on locks (ms) = 20268
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = Not Collected
Total sorts = 0
Total sort time (ms) = Not Collected
Total sort overflows = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary 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 temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool index writes = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Buffer pool xda writes = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds) = Not Collected
Time waited for prefetch (ms) = Not Collected
Unread prefetch pages = 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 = 3
Commit statements = 0
Rollback statements = 0
Dynamic SQL statements attempted = 3
Static SQL statements attempted = 0
Failed statement operations = 0
Select SQL statements executed = 1
Xquery statements executed = 0
Update/Insert/Delete statements executed = 0
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 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 = 0
Rows read = 95
Rows written = 0
UOW log space used (Bytes) = Not Collected
Previous UOW completion timestamp = Not Collected
Elapsed time of last completed uow (sec.ms) = Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status = Not Collected
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 = 1
Open local cursors with blocking = 1
Total User CPU Time used by agent (s) = 0.019150
Total System CPU Time used by agent (s) = 0.001795
Host execution elapsed time = 0.012850
Package cache lookups = 2
Package cache inserts = 1
Application section lookups = 3
Application section inserts = 1
Catalog cache lookups = 11
Catalog cache inserts = 8
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 0
Total shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 0
Total private overflows = 0
Total private section inserts = 0
Total private section lookups = 0
Most recent operation = Fetch
Cursor name = SQLCUR201
Most recent operation start timestamp = 02/22/2007 13:22:19.497439
Most recent operation stop timestamp =
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
Number of OLAP functions = 0
Number of OLAP function overflows = 0
Statement type = Dynamic SQL Statement
Statement = Fetch
Section number = 201
Application creator = NULLID
Package name = SQLC2G11
Consistency Token = AAAAANBX
Package Version ID =
Cursor name = SQLCUR201
Statement database partition number = 0
Statement start timestamp = 02/22/2007 13:22:19.497439
Statement stop timestamp =
Elapsed time of last completed stmt(sec.ms) = 0.000289
Total Statement user CPU time = 0.002172
Total Statement system CPU time = 0.001348
SQL compiler cost estimate in timerons = 14
SQL compiler cardinality estimate = 57
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 = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Blocking cursor = YES
Dynamic SQL statement text:
select * from org
Agent process/thread ID = 1801
Memory usage for application:
Memory Pool Type = Application Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Agent process/thread ID = 1801
Agent Lock timeout (seconds) = -1
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 589824
High water mark (bytes) = 786432
Configured size (bytes) = 34359738368
ID of agent holding lock = 34
Application ID holding lock = *LOCAL.jwr.070222182158
Lock name = 0x0002000E000000000000000054
Lock attributes = 0x00000000
Release flags = 0x00000001
Lock object type = Table
Lock mode = Exclusive Lock (X)
Lock mode requested = Intention Share Lock (IS)
Name of tablespace holding lock = USERSPACE1
Schema of table holding lock = JWR
Name of table holding lock = ORG
Data Partition Id of table holding lock = 0
Lock wait start timestamp = 02/22/2007 13:22:19.497833get snapshot for all databases Database Snapshot
Database name = SAMPLE
Database path = C:\DB2\NODE0000\SQL00001\
Input database alias =
Database status = Active
Catalog database partition number = 0
Catalog network node name =
Operating system running at database server= NT
Location of the database = Local
First database connect timestamp = 06/21/2007 14:46:49.771064
Last reset timestamp =
Last backup timestamp =
Snapshot timestamp = 06/21/2007 14:51:50.235993
Number of automatic storage paths = 1
Automatic storage path = C:
Node number = 0
High water mark for connections = 6
Application connects = 4
Secondary connects total = 4
Applications connected currently = 1
Appls. executing in db manager currently = 0
Agents associated with applications = 5
Maximum agents associated with applications= 6
Maximum coordinating agents = 6
Number of Threshold Violations = 0
Locks held currently = 0
Lock waits = 0
Time database waited on locks (ms) = Not Collected
Lock list memory in use (Bytes) = 2256
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 0
Post threshold sorts (shared memory) = Not Collected
Total sorts = 0
Total sort time (ms) = Not Collected
Sort overflows = 0
Active sorts = 0
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary 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
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary 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
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Buffer pool xda writes = Not Collected
Asynchronous pool xda page reads = Not Collected
Asynchronous pool xda page writes = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds)= Not Collected
Total elapsed asynchronous read time = Not Collected
Total elapsed asynchronous write time = Not Collected
Asynchronous data read requests = Not Collected
Asynchronous index read requests = Not Collected
Asynchronous xda read requests = Not Collected
No victim buffers available = 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
Unread prefetch pages = 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
Vectored IOs = Not Collected
Pages from vectored IOs = Not Collected
Block IOs = Not Collected
Pages from block IOs = Not Collected
Host execution elapsed time = Not Collected
Commit statements attempted = 0
Rollback statements attempted = 0
Dynamic statements attempted = 6
Static statements attempted = 3
Failed statement operations = 0
Select SQL statements executed = 0
Xquery statements executed = 0
Update/Insert/Delete statements executed = 0
DDL statements executed = 0
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 6
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Number of MDC table blocks pending cleanup = 0
Rows deleted = 0
Rows inserted = 0
Rows updated = 0
Rows selected = 0
Rows read = 98
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 20400000
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 read time (sec.ns) = 0.000000004
Log pages written = 0
Log write time (sec.ns) = 0.000000004
Number write log IOs = 0
Number read log IOs = 0
Number partial page log IOs = 0
Number log buffer full = 0
Log data found in buffer = 0
Appl id holding the oldest transaction = 93
Log to be redone for recovery (Bytes) = 0
Log accounted for by dirty pages (Bytes) = 0
Node number = 0
File number of first active log = 0
File number of last active log = 2
File number of current active log = 0
File number of log being archived = Not applicable
Package cache lookups = 6
Package cache inserts = 0
Package cache overflows = 0
Package cache high water mark (Bytes) = 196608
Application section lookups = 6
Application section inserts = 0
Catalog cache lookups = 37
Catalog cache inserts = 10
Catalog cache overflows = 0
Catalog cache high water mark = 65536
Catalog cache statistics size = 0
Workspace Information
Shared high water mark = 0
Corresponding shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 0
Corresponding private overflows = 0
Total private section inserts = 0
Total private section lookups = 0
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Post threshold hash joins (shared memory) = 0
Active hash joins = 0
Number of OLAP functions = 0
Number of OLAP function overflows = 0
Active OLAP functions = 0
Statistic fabrications = Not Collected
Synchronous runstats = Not Collected
Asynchronous runstats = Not Collected
Total statistic fabrication time (milliseconds) = Not Collected
Total synchronous runstats time (milliseconds) = Not Collected
Memory usage for database:
Node number = 0
Memory Pool Type = Backup/Restore/Util Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 20512768
Node number = 0
Memory Pool Type = Package Cache Heap
Current size (bytes) = 196608
High water mark (bytes) = 196608
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Other Memory
Current size (bytes) = 131072
High water mark (bytes) = 131072
Configured size (bytes) = 20971520
Node number = 0
Memory Pool Type = Catalog Cache Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = 1
Current size (bytes) = 2424832
High water mark (bytes) = 2424832
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 32k buffer pool
Current size (bytes) = 851968
High water mark (bytes) = 851968
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 16k buffer pool
Current size (bytes) = 589824
High water mark (bytes) = 589824
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 8k buffer pool
Current size (bytes) = 458752
High water mark (bytes) = 458752
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Buffer Pool Heap
Secondary ID = System 4k buffer pool
Current size (bytes) = 393216
High water mark (bytes) = 393216
Configured size (bytes) = 402653184
Node number = 0
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 0
High water mark (bytes) = 0
Configured size (bytes) = 20512768
Node number = 0
Memory Pool Type = Lock Manager Heap
Current size (bytes) = 327680
High water mark (bytes) = 327680
Configured size (bytes) = 393216
Node number = 0
Memory Pool Type = Database Heap
Current size (bytes) = 10551296
High water mark (bytes) = 10551296
Configured size (bytes) = 12582912
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 97
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 96
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 95
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 94
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Application Heap
Secondary ID = 93
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 1048576
Node number = 0
Memory Pool Type = Applications Shared Heap
Current size (bytes) = 65536
High water mark (bytes) = 65536
Configured size (bytes) = 20512768User authority represents all authorizations and roles granted to the user, and Group authority represents all authorizations and roles granted to the group.
get snapshot for application agentid 765 get snapshot for dynamic sql on sampleget snapshot for fcm for all dbpartitionnums FCM Snapshot
Node FCM information corresponds to = 1
Free FCM buffers = 10740
Total FCM buffers = 10740
Free FCM buffers low water mark = 10740
Maximum number of FCM buffers = 28640
Free FCM channels = 6265
Total FCM channels = 6265
Free FCM channels low water mark = 6265
Maximum number of FCM channels = 28640
Snapshot timestamp = 02/17/2010 15:54:57.094901
Number of FCM nodes = 3
Node Total Buffers Total Buffers Connection
Number Sent Received Status
----------- ------------------ ------------------ -----------------
1 2 2 Active
2 1 1 Active
10 1 1 ActiveHowever, if a REORG TABLE is being performed or has been performed during this period, some information is returned although some fields are not displayed. For a partitioned table, information for each reorganized data partition is returned.
In the above example, the High water mark for connections value is 6, and the Applications connected currently value is 9.
The new registry variable in Version 9.5, DB2_SYSTEM_MONITOR_SETTINGS impacts the behavior of monitoring the CPU usage on Linux. If you need to use the method of reading CPU usage that returns both system and user CPU usage times on Linux, perform one of the following actions.
On Linux on RHEL4 and SLES9:db2set DB2_SYSTEM_MONITOR_SETTINGS=DISABLE_CPU_USAGE:FALSEdb2set DB2_SYSTEM_MONITOR_SETTINGS=OLD_CPU_USAGE:TRUE