MINCOMMIT * (log space used, on average, by a transaction) |
db2 -v reset monitor for database db_name |
db2 -v get snapshot for database on db_name |
Last reset timestamp = 06-12-2001 14:51:43.786876 Snapshot timestamp = 06-12-2001 14:56:27.787088 Commit statements attempted = 1011 Rollback statements attempted = 10 Log space used by the database (Bytes) = 3990 |
NumOfTransPerSecond = totalTransactions / totalElapsedTime |
db2 -v reset monitor for database db_name. |
db2 -v get snapshot for database on db2_name. |
LogSpaceUsedPerTrans = log_space_used / totalTransactions |
db2 -v update db cfg for DB_NAME using MINCOMMIT a_number db2 -v terminate |
db2 -v update db cfg for DB_NAME using MAXAPPLS a_number db2 -v terminate |
SQL1040N The maximum number of applications is already connected to the database. SQLSTATE=57030 |
db2 -v update db cfg for DB_NAME using NUM_IOCLEANERS a_number db2 -v terminate |
Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 LSN Gap cleaner triggers = 0 Dirty page steal cleaner triggers = 0 Dirty page threshold cleaner triggers = 0 |
Dirty page steal cleaner triggers / (Dirty page steal cleaner triggers + Dirty page threshold cleaner triggers + LSN Gap cleaner triggers) |
db2 -v update db cfg for DB_NAME using NUM_IOSERVERS a_number db2 -v terminate |
db2 -v update dbm cfg using MAXAGENTS a_value db2 -v update dbm cfg using NUM_POOLAGENTS b_value db2 -v update dbm cfg using NUM_INITAGENTS c_value db2 -v terminate |
db2 -v get snapshot for database manager |
High water mark for agents registered = 4 High water mark for agents waiting for a token = 0 Agents registered = 4 Agents waiting for a token = 0 Idle agents = 0 Agents assigned from pool = 5 Agents created from empty pool = 4 Agents stolen from another application = 0 High water mark for coordinating agents = 4 Max agents overflow = 0 |
db2 -v update db cfg for DB_NAME using LOCKLIST a_number db2 -v update db cfg for DB_NAME using MAXLOCKS b_number db2 -v update db cfg for DB_NAME using LOCKTIMEOUT c_number db2 -v terminate |
db2 -v update monitor switches using lock on db2 -v terminate |
db2 -v get snapshot for database on DB_NAME |
Locks held currently = 0 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in use (Bytes) = 504 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Agents currently waiting on locks = 0 Lock Timeouts = 0 Internal rollbacks due to deadlock = 0 |
db2 -v update database cfg for DB_NAME using LOGBUFSZ 256 db2 -v terminate |
Log pages read = 0 Log pages written = 12644 |
db2 -v update db cfg for DB_NAME using applheapsz 256 db2 -v terminate |
-- SORTHEAP should be changed for individual database -- db2 -v update db cfg for DB_NAME using SORTHEAP a_value -- SHEAPTHRES is a database manager parameter -- db2 -v update dbm cfg using SHEAPTHRES b_value db2 -v terminate |
db2 -v update monitor switches using sort on |
db2 -v get snapshot for database on DBNAME |
Total sort heap allocated = 0 Total sorts = 1 Total sort time (ms) = 0 Sort overflows = 0 Active sorts = 0 Commit statements attempted = 1 Rollback statements attempted = 0 Dynamic statements attempted = 4 Static statements attempted = 1 Binds/precompiles attempted = 0 |
SortsPerTransaction = (Total Sorts) / (Commit statements attempted + Rollback statements attempted) PercentSortOverflow = (Sort overflows * 100 ) / (Total sorts) |
db2 -v connect to DB_NAME db2 -v select * from syscat.bufferpools db2 -v alter bufferpool IBMDEFAULTBP size -1 db2 -v connect reset db2 -v update db cfg for dbname using BUFFPAGE bigger_value db2 -v terminate |
db2 -v connect to DB_NAME db2 -v SELECT * from SYSCAT.BUFFERPOOLS db2 -v connect reset db2 -v terminate |
db2 -v update monitor switches using bufferpool on db2 -v get monitor switches db2 -v reset monitor all -- run your application -- db2 -v get snapshot for all databases > snap.out db2 -v get snapshot for dbm >> snap.out db2 -v get snapshot for all bufferpools >> snap.out db2 -v reset monitor all db2 -v terminate |
-- Related lines from a sample of bufferpool snapshots -- Buffer pool data logical reads = 702033 Buffer pool data physical reads = 0 Buffer pool data writes = 414 Buffer pool index logical reads = 168255 Buffer pool index physical reads = 0 |
(1 - ((buffer pool data physical reads + buffer pool index physical reads) / (buffer pool data logical reads + pool index logical reads)) ) * 100% |
db2 -v connect to DB_NAME db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" db2 -v reorgchk update statistics on table all db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" db2 -v terminate |
db2 -v runstats on table TAB_NAME and indexes all |
db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" |