Thanks for visiting
our DB2 tips page.

This page gets more hits than any other on our site.

Be famous.
Please share a tip with your DB2 peers.

DB2 UDB Tuning Tips & Tricks

In our never-ending pursuit of optimizing DB2 UDB Performance and helping our clients and customers succeed with DB2, we are collecting tuning tips and tricks below.

If you would like to Add a contribution to this compiliation, please do so. (Tips provided should apply to UNIX, NT, OS/2, or Linux platforms.)

The tips and tricks provided herein are provided without warranty of any kind. Individual results and circumstances may vary. See legal for details.

We hope you will find something here that helps you succeed with DB2!


If your SQL queries against large datamart/datawarehouses are very slow check that 'multipage_alloc' is on. If it off then when you run queries that use sort/work files, the SMS controlled files will be extend a page at a time (and locked while doing so). Get rid of all threads and run db2empfa. UDB will now allocate its extents a block at a time. with default settings he will now allocate a 32 page block at a time.
Anthony Brown <tony_brown@cgu.com.au>
Melbourne, Vic Australia - Thursday, June 26, 2003 at 00:42:47 (EDT)
Don't confuse in allocating bufferpool to your DB2 database. Allocate 70% of the memory to bufferpool if you have dedicated database server. If you have default tablespaces SYSCATSPACE, TEMPSPACE1, USERSPACE1. Then, create 3 different individual bufferpool of three different sizes. Bufferpool for USERSPACE1 will have bigger size, then for TEMPSPACE1, and SYSCATSPACE will have least size. Then, allocate those bufferpools to tablespaces.
Manoj Kumar <mthakur@telegenisys.com>
Pune, MH INDIA - Monday, December 16, 2002 at 16:05:10 (EST)
If you turn on intra_parrallelism you must also set max_querydegree (-1 let system decide(same as any) and a fixed number 1-32767. intra_parallel needs to be set to (-1 let system decide, yes(1), no*(0)). If max_querydegree is left at the default it will still only use 1 agent. You must also make sure your not at higher than 60% cpu on avg before turning it on, it will cause a spike in CPU usage
David Quigley <david.p.quigley@accenture>
New Castle, DE USA - Monday, November 18, 2002 at 07:53:21 (EST)
Don't panic when get "Load pending" or "delete pending" status of any of the tablespace when your db2move breaks abruptly. Use the following steps to bring the tablespace normal: 1. get the table name "db2 list tablespace show detail" this out put will help you get the tableid then............................."select tabname from syscat.tables where tablesid=tabid" This will give you the table name. Now issue a load command as follows: "db2 load from /dev/null of del replace into tablename"...............now the load will be successfull and the tablespace will comeback to normal state. Now..you can issue db2move command again.
Shivakumar <ibmshiva@yahoo.com>
Pune, INDIA - Wednesday, September 25, 2002 at 06:22:32 (EDT)
When ever you encounter a tcp/ip communication error while connecting to UDB which is on a UNIX box from GUI (means... from control-centre, command center, client configuration assistant...or even from another UNIX box) these are probable reasons and fixes. 1. First make sure this is not the problem. in db2 get dbm cfg look for svcename and go to /etc/services file and check that is the same as ex: DB2_mvidev1c 2270/tcp #DB2 for CAE clients Make sure that you are using the same port and name ...in our case 2270. 2. If that is from control center ... Try this db2admin start 3. Check this db2set -all DB2COMM this should return db2set -all DB2COMM [i] TCPIP if this is not set please update the DB2COMM variable like this db2set -i mvidev1 DB2COMM=TCPIP where mvidev1 is the instance name 4. To take these things to be effective please do a db2stop and db2start. 5. The cause may be because of the bad etc/services file. To verify this do this. vi /etc/services In this file there should not be any tabs in between . For this you have to do a octal dump od -c xxx.yyy(the file name). get rid of the tabs Cheers... Shashi Mannepalli
shashi b mannepalli <shashitech@yahoo.com>
VIENNA, VA USA - Thursday, May 30, 2002 at 13:28:02 (EDT)
When you accidentally create a table like this where the schema is in lower case(You can do this from db2look output)...db2 create table "test "."emp". the only only way you can drop the table is like this db2 'drop table "test".emp' Cheers.. Shashi B Mannepalli
shashi b mannepalli <shashitech@yahoo.com>
vienna, va USA - Thursday, May 30, 2002 at 10:55:18 (EDT)
The variable DB2_OVERRIDE_BPF is the variable that specifies the size of the buffer pool, in pages, to be created at database activation, and is used only in case of failures during database activation, resulting from memory constraints. 32 Bits Operating Systems have limitations concerning the size of bufferpools that can be used by DB2. So, if you overestimate that size and cannot bring up the database, use this variable.
FZimmermann <paranix@yahoo.com.br>
Rio de Janeiro, RJ BRAZIL - Thursday, May 23, 2002 at 16:36:52 (EDT)
When running DB2 UDB on Windows 2000 Advanced Server or Datacenter Server DB2 UDB can address 3 GB of real memory, but in order to do so you must enable Windows 4GB Tuning by adding the "/3GB" switch to the Windows boot.ini file.
Chris Fierros <chris@tendigit.com>
Canton, MI USA - Wednesday, April 24, 2002 at 22:17:39 (EDT)
Assuming you are backing up to local DASD (AIX), here's a way to NOT have to hard-code multiple directories in the backup command: dirlist=`find $bd/bkup* | grep -v $DBN | awk 'BEGIN { ORS="";OFS="" } { print OFS$1; OFS="," }'` db2 "backup db $DBN $onoff to $dirlist without prompting" In this example, the var $bd would contain some path, like /db2backup. Under /db2backup you would have multiple directories ranging in names from bkup1 thru bkup30. The var $dirlist is populated at runtime and would result in the backup utility writing to 30 files.
Rick Smith <ras@satx.net>
Austin, TX USA - Monday, April 15, 2002 at 18:32:21 (EDT)
a question not a tip when o run a cics transaction i call a db2 program and get and -805 error i have recompile and new copy and still get the same error any suggestions
dennis vest <dvest1@csc.com>
san diego, ca USA - Thursday, April 04, 2002 at 12:09:03 (EST)
time out problem
orhan <orhan.ergun@esc.com.tr>
Istanbul, turkey TURKEY - Saturday, March 30, 2002 at 14:32:43 (EST)
If you over-size bufferpools (i.e. - make the sum total greater than available memory) and db2 won't start, there is an answer short of re-installing and restoring. The start up routines will use a registry variable called DB2OVERIDEBP, or something like that, it's in the manual, to size bp's for startup. I think that's worth some fries at least. - Jim
Jim-Bob <jscleve@aol.com>
Alexandria, VA USA - Sunday, March 17, 2002 at 01:23:07 (EST)
The DB2 for Linux, UNIX, and Windows package cache is used to store static and dynamic SQL statements. For static statements this avoids catalog access, and for dynamic statements a prepare can be avoided which results in less CPU consumption and better performance. However, if the package cache overflows, DB2 borrows memory from locklist or dbheap. This can cause unnecessary lock escalations, performance degradation, and memory shortages in other heap areas. Use monitor element pkg_cache_sz_top to help determine if the size of the package cache needs to be increased to avoid overflowing. Pkg_cache_sz_top indicates the maximum amount of memory used by package cache. You can determine the minimum size of package cache for your workload by using the following formula: Maximum package cache size / 4096 This will give you the minimum number of pages to allocate for package cache to avoid overflow. In summary, efficient use of package cache can help increase overall database performance, but monitor it and avoid overflows if possible.
Phil Gunning <pgunning@database-guys.com>
Sinking Spring, Pa USA - Sunday, January 20, 2002 at 20:52:40 (EST)
When you are designing and creating Bufferpools always,always be sure that the page*pagesize value is always < the available system memory. ie . your system is able to allocate so much free memory for the Bufferpool when the database starts up. For eg, if you create a bufefrpool with page=5000 and pagesize=32768, and if your system memory is < 168MB, then you can see that your database will ot start up when the DB2 instance is restarted. This is a very serious problem and will leave no options to get the database back to operation unless and untill proper backup policies were in place.
Deepesh Joseph <deepesh@myiris.com>
Mumbai, India - Monday, December 31, 2001 at 00:33:00 (EST)
We had a DB2/390 Unload utility reading from a DB2/NT database over a 100Mb LAN. The Unload took 37 hours. Network tracing showed an IP packet with one row leaving DB2/NT and an acknowledgement coming back to DB2/NT (synchronous processing with an acknowldgement for each row/packet). Come to find out that the Unload utility was bound 'CURRENTDATA YES' which means 'blocking no'. The Unload utility was then bound with blocking and the Unload duration dropped to 3 hours. Network traffic showed many 1500 byte packets from DB2/NT with an occasional acknowledgement returning from DB2/390.
Kelly Kilhoffer <kelly.kilhoffer@countrycompanies.com>
Bloomington, IL USA - Friday, September 28, 2001 at 15:56:29 (EDT)
The folks at IBM provide a formula for setting the appropriate MON_HEAP_SZ. It is as follows: (number of monitoring applications + 1) * (number of databases * (800 + (# of tables accessed * 20 ) + ((number of applications connected + 1) * (200 + (number of table spaces * 100))))) / 4096.
Jim Sexton <jsexton@database-guys.com>
Hornell, NY USA - Monday, September 17, 2001 at 09:42:03 (EDT)
You can control the initial settings for the DEGREE of parallelism and QUERY OPTIMIZATION level on a NT client using the ODBC interface to access a DB2 database. Configure the DB2DEGREE and DB2OPTIMIZATION settings under CLI/ODBC SETTINGS - ADVANCED SETTINGS under DATA SOURCES ODBC in the CONTROL PANEL. DB2 CAE will issue the commands to set the values immediately after connect. I use this to give a small number of DSS users a performance boost by enabling PARALLELISM for them, but not everyone else. Of course the database server has to be configured to allow parallelism and have hardware which benefits from parallelism....
Kelly Kilhoffer <kelly.kilhoffer@countrycompanies.com>
Bloomington, IL USA - Friday, September 07, 2001 at 18:58:43 (EDT)
For special situations, increasing the database MINCOMMIT setting can improve performance. We had 10 programs each pulling data off MQ series queues and INSERTing the data into DB2 tables. INSERT...COMMIT...INSERT...COMMIT. Increasing MINCOMMIT setting yielded 30-40% faster application throughput. MINCOMMIT may also result in one second "waits" if the database server is not being pounded by multiple applications committing frequently....
Kelly Kilhoffer <kelly.kilhoffer@countrycompanies.com>
Bloomington, IL USA - Monday, August 20, 2001 at 16:42:32 (EDT)
Please note that the extent size can not be changed /altered. The only way to change the extent size is:............(1)Extract all the data from the tablespace (2) DROP teh tablespace (3) Recreate the tablespace with the NEW EXTENT SIZE (4) Create all the objects (5) Load the extracted data. If we do not specify the extent size, DB2 will use the default value specified in db cfg parameter called DFT_EXTENT_SZ which is 32.
Shivakumar C <techshiva@indiatimes.com>
BANGALORE, INDIA - Saturday, August 18, 2001 at 02:00:40 (EDT)
If you want DB2 to perform parallel I/O for a tablespace having a single container, set the registry variable "DB2_PARALLEL_IO" to either * for all tablespaces OR the specific tablespace names seperated by commas.
Shivakumar <techshiva@indiatimes.com>
BANGALORE, INDIA - Thursday, August 09, 2001 at 06:42:21 (EDT)
When deleting all data from a large table, performance can be significantly enhanced by using "ALTER TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE" rather than a simple delete. There are non-trivial issues to read up on before you do this on a production system, but a delete of 10 gig of data runs in less than 3 seconds on DB2/NT!
Kelly Kilhoffer <kelly.kilhoffer@countrycompanies.com>
Bloomington, IL USA - Tuesday, August 07, 2001 at 17:10:10 (EDT)
Just a correction to my previous TIP. When joining the tables having different page size, DB2 optimizer will try to choose the TEMPORARY tablespace which has the largest pagesize.
Shivakumar <cshiva@india.com>
BANGALORE, INDIA - Tuesday, July 31, 2001 at 00:45:15 (EDT)
If you create a tablespace with pagesize greater than 4KB, then assign it to a buffer pool that has the same page size. When joining the tables having different page size, DB2 optimizer will try to choose the tablespace which has the largest pagesize.
Shivakumar <cshiva@india.com>
BANGALORE, INDIA - Tuesday, July 31, 2001 at 00:42:28 (EDT)
BACKUP & RESTORE :- Always, try to make use of the PARALLEISM, BUFFER & 'with BUFFERS' parameters during BACKUP and RESTORE when you have multiple CPU's, Multiple tablespaces, containers in defferent devices & database in different partition etc... These parameters will help in improving the performance of BACKUP & RESTORE utilities by using parallel I/O.
Shivakumar C <cshiva@india.com>
BANGALORE, INDIA - Friday, July 27, 2001 at 01:44:11 (EDT)
Use RUNSTATS utility whenever the following occurs. (1) After loading the data (2) After creating indexes (3) When the table has been re-organized (4) When lots of updation/deletion happens Rebound any packages accessing these tables against which you run RUNSTATS.
Shivakumar <cshiva@india.com>
BANGALORE, INDIA - Wednesday, July 25, 2001 at 06:45:04 (EDT)
If you have tables which are very large and very frequently accessed/updated, try to put them in a dedicated tablespace and a dedicated bufferpool. Use the dedicated bufferpool for indexes.
Shivakumar <cshiva@india.com>
BANGALORE, INDIA - Wednesday, July 25, 2001 at 05:51:29 (EDT)
Make sure that the application codepage and the database codepage are the same. If the codepage of database and application are different, then the codepage conversion takes place and may leads to performance issues.
Shivakumar <cshiva@india.com>
BANGALORE, INDIA - Tuesday, July 24, 2001 at 00:59:28 (EDT)
What are the parameters/ attributies have to set for fine tuning of db2 server
sarma <ebox101@yahoo.com>
ap India - Monday, July 23, 2001 at 08:59:05 (EDT)
Never order a beverage on an airplane with an open laptop. But seriously. I made use of a tip today I've seen in other forums: instead of running a DELETE FROM xxxx in order to empty a table, run LOAD with the REPLACE option from an empty file.
Jim-Bob <jscleve@aol.com>
Alexandria, VA USA - Friday, July 20, 2001 at 22:22:32 (EDT)
When running DB2 UDB on Windows NT, make sure that Windows NT is optimized to function as "application server" rather than as a "file server". Which in by default NT is configured to maximize throughput for file maintenance. By doing so affecting database server's performance. --Where to config: Control Panel/Network/Services/Server/Properties -then choose radio buttong "maximize throughput for Network Applications"
Alex de Guzman <alexdeguzman@consultant.com>
Philippines - Thursday, December 21, 2000 at 02:47:47 (EST)
For DMS tablespaces, make certain that the containers are all the same size. The worst possible scenario would be a handful of small containers and one huge container. As the tablespace fills, containers are written to round-robbin. Once the handful of small containers are filled, only the large container remains... When DB2 sends parallel I/O requests via NUM_IOSERVERS and NUM_IOCLEANERS to the tablespace, the disk contention on the large container device can be devastating.

Plan your containers and tablespace definitions carefully.

Hey, I know there are a lot of smart DB2 UDB UNIX, NT, OS/2, and Linux people out there! How about a tip? I've got a pile of McDonald's gift certificates here, so let's call this food for thought. Add your tip here, I'll send you a Big Mac... while supplies last... must be US or Canada resident... must like Big Macs or know someone who does. Cheers, Scott
Scott Hayes <topguy@database-guys.com>
Spencerport, NY USA - Thursday, August 24, 2000 at 00:24:54 (EDT)


Be sure to check out the bufferpool tuning article at http://www.database-guys.com/bpsjart.htm

The YL&A DB2 Performance Journal also has a number of valuable articles on DB2 performance, including and article about optimizing DB2 UDB OLTP performance. Learn more at http://www.ylassoc.com/
Scott Hayes <topguy@database-guys.com>
Spencerport, NY USA - Friday, June 16, 2000 at 13:51:07 (EDT)


If, after running the LOAD utility, you find you have CHECK PENDING and you are running DB2 UDB V6.1, a new feature allows you to turn off the pending state without running an actual check:

db2 "SET INTEGRITY FOR t1 IMMEDIATE CHECKED"

Use this new feature with Caution. Regards, Scott
Scott Hayes <topguy@database-guys.com>
Rochester, NY USA - Wednesday, September 29, 1999 at 23:46:38 (EDT)


You can change the command line processor (CLP) locking isolation level by using the command:

change isolation to ( CS , RR , UR , RS , NC )

By using isolation UR, it would be possible for SQL or the EXPORT utility to use dirty, or uncommitted, reads.

I first heard of this on the DB2 listserver, DB2-L, and thought it was a slick trick, so I posted it here for future reference.

Best regards,
Scott

Scott Hayes <topguy@database-guys.com>
Spencerport, NY USA - Friday, September 10, 1999 at 11:00:04 (EDT)


When migrating a database from version 1.x the migrated database's value for LOCKTIMEOUT is -1. As Scott mentioned in an earlier tip, this parameter was not available in version 1.x and is set to -1 for backward compatability. However, if your applications were originally developed with DB2 version 1.x, be sure that you do some testing before changing the value of LOCKTIMEOUT in production. This is important because the SQLCODE for a LOCKTIMEOUT condition (SQL0911N) is the same as that for a more rare DEADLOCK condition and developers could not have anticipated that IBM would re-use the SQL return code SQL0911N. The way to distinquish between a DEADLOCK and a LOCKTIMEOUT is by examining the reason-code. A reason code of 2 indicates DEADLOCK (it takes two to tango). A reason code of 68 indicates a LOCKTIMEOUT condition. Well as if that was not enough, new and improved with v6.1 and support for DB2 Data Links comes reason code 72!
Chris Fierros <chris.fierros@tendigit.com>
Canton, MI USA - Sunday, June 13, 1999 at 17:57:20 (EDT)
When posting contributions to this forum, you can include HTML tags to improve formatting. Links to other sites will be scrutinized and may be removed if deemed inappropriate by Database-GUYS Inc.

After adding a contribution, you may need to click the re-load button on your browser to see your text.

Cheers, Scott
Scott Hayes <topguy@database-guys.com>
Rochester, NY USA - Sunday, May 02, 1999 at 17:43:18 (EDT)


When you create a new database, the default value for LOCKTIMEOUT is -1, meaning that there will be no lock timeouts. This default has roots way back to version 1.1 when there was no lock timeout mechanism.

Be sure to set the value of LOCKTIMEOUT to a value greater than 10 or more, or you could have serious locking problems on your hands. A number of clients have described this to me as "DB2 is hung".

Hope this helps, Scott
Scott Hayes <topguy@database-guys.com>
Rochester, NY USA - Sunday, May 02, 1999 at 17:38:02 (EDT)


Be sure to monitor "Database Files Closed" using bufferpool or tablespace snapshots. The default value for MAXFILOP is often only adequate for small-medium databases. If you find that files are being closed, increase the database configuration value for MAXFILOP.

db2 "get snapshot for bufferpools on sample"

Look for: Database files closed = 0

Hope this helps, Scott
Scott Hayes <topguy@database-guys.com>
Rochester, NY USA - Wednesday, April 28, 1999 at 02:18:18 (EDT)


If you have an SMP processor, make sure intra-partition parallelism is enabled by setting INTRA_PARALLEL = YES. Often folks create instances but forget to set this important parameter. You can check the current setting by issuing the command:

db2 get dbm cfg

You can update the value using the command:

db2 update dbm cfg using intra_parallel yes

Hope this helps, Scott
Scott Hayes <topguy@database-guys.com>
Rochester, NY USA - Wednesday, April 28, 1999 at 02:05:42 (EDT)


Home | Contact Us | Support | Top 10 Tips | Customer Feedback | Careers | Downloads
Live! Monitor Our Database!   |   Live! Monitor Our Tables!

Copyright © 2003 DGI. All rights reserved. Privacy and Legal details.