Testing DB2 Snapshot Monitoring

  1. Connect to a DB2 database. db2 command:

    => connect to sample user <dbusername> using <password>

  2. Attach to a DB2 instance. db2 command:

    => attach to <db2instancename> user <dbusername> using <dbusername>

  3. Check if monitor switches are on. db2 command:

    => get monitor switches

    Monitor Recording Switches

    Switch list for node 0

    Buffer Pool Activity Information (BUFFERPOOL) = ON 02-06-2002
    18:27:48.722132
    Lock Information (LOCK) = ON 02-06-2002
    18:28:00.095212
    Sorting Information (SORT) = ON 02-06-2002
    18:28:12.263183
    SQL Statement Information (STATEMENT) = ON 02-06-2002
    18:28:24.323446
    Table Activity Information (TABLE) = OFF
    Unit of Work Information (UOW) = OFF
  4. Turn on switches. db2 commands:

    => update monitor switches using bufferpool on

    => update monitor switches using lock on

    => update monitor switches using sort on

  5. Retrieve data from the DB2 UDB system monitor snapshot. db2 command:

    => get snapshot for all databases

    Database Snapshot:

    Database name = SAMPLE
    Database path = /home/db2inst1/db2inst1/
    NODE0000/SQL00001/
    Input database alias =
    Database status = Active
    Catalog node number = 0
    Catalog network node name =
    Operating system running at database server= LINUX
    Location of the database = Remote
    First database connect timestamp = 02-06-2002 18:01:31.198883
    Last reset timestamp =
    Last backup timestamp =
    Snapshot timestamp = 02-06-2002 18:37:39.254985
    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) = 792
    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
    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
    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
    Host execution elapsed time = 0.000000
    Commit statements attempted = 1
    Rollback statements attempted = 0
    Dynamic statements attempted = 0
    Static statements attempted = 1
    Failed statement operations = 0
    Select SQL statements executed = 0
    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 = 0
    Rows read = 9
    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 pages written = 0
    Appl id holding the oldest transaction = 0
    Package cache lookups = 0
    Package cache inserts = 0
    Package cache overflows = 0
    Package cache high water mark (Bytes) = 51824
    Application section lookups = 0
    Application section inserts = 0
    Catalog cache lookups = 0
    Catalog cache inserts = 0
    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