Top 10 DB2 Posts for 2011

The Top 10 viewed DBA-DB2.com posts for 2011 1. DB2 Database code set 2. DIAGLEVEL db2 3. DBA personality Traits 4. POC 5. Database Tuning overview – Storage Tuning 6. DB2 HADR and HADR_PEER_WINDOW settings 7. 10x DBA 8. Database size in DB2 9. DB2 manager status and get snapshot for dbm 10. Data Center Migration , Database Servers and data migration planning Related Posts Top 10 SQL Server Posts for 2011 Author: Jack Vamvas(http://www.dba-db2.com) Read more →

DB2 Tuning Tables

Find bottlenecks in DB2 performance is part of maintaining good response times. Here's a useful approach to locating slow query response times. Focusing on scans 1) Run a “user level” workload and check “get snapshot for database” . Add “Rollback statements attempted” and “Commit statements attempted” results in “Number of Transactions attempted” 2) Multiply the Number of Transactions Attempted by 5. Call it “Transaction Threshold” 3) Based on the same workload execute “get snapshot for tables” , look for tables where “Rows Read” is greater than the Transaction Threshold. There is probability these tables require some tuning Analyse these tables... Read more →

DB2 MAXFILOP

While troubleshooting a DB2 performance problem for Websphere Portal 6.1.0 using DB2 9.5 Enterprise Edition , one of the DB2 tuning recommendations from the Portal Tuning guide was to set MAXFILOP = 512 or higher MAXFILOP is the Maximum database files open per application configuration parameter. What level to set it? The MAXFILOP parameter defines the maximum file handles that can be opened by a database agent. If the number is to small , a CPU overhead is incurred. To view the existing MAXFILOP value do a : db2 get db cfg for WMMDB | grep 'MAXFILOP' One method of... Read more →

DB2 DB2_RR_TO_RS environment variable

The Environment Variable DB2_RR_TO_RS enables\disables the Repeatable Read attribute. I's enabled for DB2 performance purposes DB2 registry parameter DB2_RR_TO_RS is used to: Set a default isolation level for user tables to Read Stability (RS) isolation level. The RS level is not dissimilar to the Repeatable Read (RR) , with one difference – a page or row retrieved remains locked to the UOW end , but if another application can insert a value , that will be read if your process access the row a second time . Reduce locking the next key for inserted or changed rows Reduce deadlocks when... Read more →

DB2 SQL2033N and TSM reason code 106

When executing a DB2 backup command , an error message appeared db2 backup db MyDB online use TSM include logs SQL2033N An error occurred while accessing TSM during the processing of a database utility. TSM reason code: "106". Accoring to the ibm infocentre The specified file is being used by another process. You tried to read from or write to a file that is currently being used by another process. Restart the DB2 instance and this should release the resources . Ensure operational requirements are met and applications are checked post restart. Read More DB2 - SQL2033N and TSM reason... Read more →