DB2 Total Sorts - overflows – improve performance

Sorts are part of processing queries , but excessive sorts , especially in an OLTP databases can kill performance. Sorts hit IO,CPU and lead to slow response time. A typical DB2 Sort occurs if a sql statement uses an ORDER BY but can’t find a relevant index. Sort Overflows represent the amount of sorts overflowing to disk . A high percentage could indicate performance issues. Increasing SORTHEAP is the quickest way of improving this situation , but is probably masking underlying problems. A more in depth approach will require an analysis of the sql code and supporting indexes. To obtain... Read more →

DB2 Tuning Toolkit – db2exfmt

db2exfmt - Explain Table Format Command - gives information needed to tune the query .One of the DBA objectives is to decrease the query cost. Decreased query cost equals quicker user response. Decreasing the query cost – improves overall performance. Read the post - DB2 Tuning Toolkit – db2expln - to analyse the accesspath for a specific query. Read the post - DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis - for index recommnedations A syntax and process example: Step 1 – Create the source file Put a query in a input file called index.sql. This is just a... Read more →

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

One of the DBA objectives is to decrease the query cost. Decreasing the query cost – improves overall performance. The db2advis recommends indexes for a query. Read the post - DB2 Tuning Toolkit – db2expln - to analyse the accesspath for a specific query. A syntax and process example: Step 1 – Create the source file Put a query in a input file called index.sql. This is just a sample query select SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10), substr(name,1,30), SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard, sequential_pages, density, iid, uniquerule, stats_time, colnames from sysibm.sysindexes a ORDER BY tbcreator, TBNAME, NAME; Step 2 Execute the db2advis command db2advis -d... Read more →

DB2 Query Tuning – db2expln

Db2expln analyses the accesspath for a specific DB2 query. Use db2expln for DB2 query tuning. Once you've identified a slow running query causing a bottleneck - use this process to analyse the access path. This might be part of of an overall Performace Tuning process A syntax example: db2expln -d MYDB -f myquery.sql -z \; -g -o explain.txt The db2explain output for my query is Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "DB2MY" Statement: select SUBSTR(TBNAME, 1,... Read more →

Database Tuning for complex sql queries

Question: I manage a large DB2 data warehouse. The datawarehouse supports multiple ETL processes . Sometimes , some SQL queries fail. The large SQL queries are attempting to extract a large amount of data and are complex. What DB2 parameters can I consider changing to fix this problem? Answer: Firstly , ensure the indexes are maintained correctly. Are statistics up to date? Are fragmentation levels low? Are REORG on tables running regularly? For large complex queries , I normally look at the SQL statement heap (STMTHEAP) , default application heap (APPLHEAPSZ), and the package cache size (PCKCACHESZ) The DB2 STMTHEAP... Read more →