Checking the instance peaks

June 1, 2009

If you want to check the peak usage of memory on any instance use the following db2 “select * from table (sysproc.admin_get_dbp_mem_usage(-1) ) as t” moreDBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM PEAK_PARTITION_MEM————– ——————– ——————— ——————– 0 1597186048 447676416 450101248 DBPARTITIONNUM::The database partition number from which memory usage statistics is retrieved.MAX_PARTITION_MEM::The maximum amount of instance memory (in bytes) allowed [...]

Read the full article →

Important Admin views

April 17, 2009

REORG—->> SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME, SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA, REORG_PHASE, SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE, REORG_STATUS, REORG_COMPLETION, DBPARTITIONNUM FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM LOCK WAIT—>>>SELECT AGENT_ID, LOCK_MODE, LOCK_OBJECT_TYPE, AGENT_ID_HOLDING_LK, LOCK_MODE_REQUESTED FROM SYSIBMADM.SNAPLOCKWAIT WHERE DBPARTITIONNUM = 0 BPHIT RATIO ——>>> SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,14) AS BP_NAME, TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT, DBPARTITIONNUM FROM [...]

Read the full article →

Monitoring STMM changes

March 25, 2009

How to monitor the activity of STMM??STMM logs and db2diag.log provides information about the changes that happen through STMM.db2diag.log gives the information about the changes in the SORTHEAP,BUFFERPOOLS,LOCKLIST,PACKAGECACHE. Monitoring bufferpool changes on the diaglog:db2diag -g “message:=Altering bufferpool” db2diag.log Monitoring the configuration changes by STMM:db2diag -g “changeevent:=CFG DB” db2diag.log Interpreting the STMM logs: Interpreting the STMM [...]

Read the full article →

STMM internals

March 9, 2009

OS limitations:For Linux servers,prior to V9.5 setting DATABASE_MEMORY to automatic is not allowed.So essentially sharing of memory between OS and database was not allowed.However with V9.5 DATABASE_MEMORY can be set to automatic when INSTANCE_MEMORY is set to a static value.STMM Controller:How does STMM know where to take and where to give?This process is controlled by [...]

Read the full article →

Temporary tablespace and space limitations

March 6, 2009

System temporary tablespaces are used for sorts and joins.They are supposed to release the space after the operation , but there is a registry variable DB2_SMS_TRUNC_TMPTABLE_THRESH that controls the number of extents that can be left out after the operation.If it is set to zero then all the extents are deleted after the sort/join operation

Read the full article →

db2ilist issue after upgrade

December 19, 2008

If you upgrade some of your instances to V9.5 and the remaining stay at 8.2,You might see this issue.db2ilist lists the instances that are at the current instance level.Lets suppose you have db2inst1,db2inst2 and db2inst3 and only db2inst3 is upgraded to V9.5 and remaining are at 8.2.If you are attached to db2inst3 and if you [...]

Read the full article →

SQL0444N after fixpack upgrade

December 9, 2008

Have you ever noticed SQL0444N reason code 4 after migration or a fixpack upgrade?If so here is the solution. Please check if a link for db2clifn.a exists under /sqllib/function like below: lrwxrwxrwx 1 root db2iadm 46 2008-12-08 22:33 db2clifn.a -> /opt/ibm/db2/V9.5/fixpack2/function/db2clifn.a If it does not exist you should run db2iupdt again to fix the broken [...]

Read the full article →

Fixpack upgrades on V9.5

October 10, 2008

Starting from V9.5 db2iupdt is automated after fixpack install.Also the binding of packages happen during the first connection after the upgrade.But the major change that I observed is with alternative fixpacks.Consider a situation where you have 2 instances serving two different applications on a server.If you want to maintain one instance at V9.5 fixpack0 and [...]

Read the full article →

REDIRECTED RESTORE ISSUE

April 5, 2008

If you are restoring into a different alias using into clause, you should use the original database name in db2 restore continue statement.If you use the alias you will recieve the error:DB21080E No previous RESTORE DATABASE command with REDIRECT option was issuedfor this database alias, or the information about that command is lost.Refer to the [...]

Read the full article →

LOGINDEXBUILD in HADR environment

April 2, 2008

LOGINDEXBUILD parameter should be set in HADR environments.If this is OFF in HADR environments,index creation and reorgs will not be completely logged which will delay the failover process.The failover process is delayed because the index building occurs at the time of failover.

Read the full article →