7 IBM DB2 Tips Learned at IDUG

Thursday Jun 10th 2010 by Rebecca Bond

Rebecca Bond returns from an IDUG Conference with some short, compact knowledge bits that return big results. These quick tips and fixes make great additions to any database administrator's toolbox.

Rebecca Bond returns from an IDUG Conference with some short, compact knowledge bits that return big results. These quick tips and fixes make great additions to any database administrator's toolbox.

I just returned from an International DB2 Users Group (IDUG) conference and my brain is full of great IBM DB2 hints and tips. Sure, there were some big ideas presented there, DB2 pureScale for example, but there were also numerous opportunities to accumulate information in short, compact knowledge bits. I'm talking about small thought morsels, so tiny that each topic occupies no more than a few brain cells.

As database administrators universally know, small thoughts are best because storage is always at a premium and brain cell compression is painful and not nearly as effective as DB2 compression. But, just because these are small thoughts doesn't mean they're not valuable pieces and parts, especially if we add them to already acquired knowledge.

I'll share some of these tiny gems and let you decide if they're valuable to you or not. One caution, we will be jumping from thought to thought, just like we would if we were attending a virtual IDUG of our own making. Be prepared for some serious brain cell spinning!


Do you have partitioned tables that have been screaming for partitioned indexes? If so, here's a tidbit. Partitioned indexes are supported for partitioned tables in IBM DB2 9.7. Of course, non-partitioned indexes are still an option. If you are creating multiple indexes on a partitioned table and want to use both partitioned and non-partitioned indexes, that's possible too. Feel free to mix and match.

Table compression has been available since IBM DB2 9.1, but index compression is new in DB2 9.7. The default is that index compression is enabled on compressed tables, but disabled for uncompressed tables. Of course, defaults can be overridden.

Additionally, there are two new administrative table functions for indexes, ADMIN_GET_INDEX_INFO and ADMIN_GET_INDEX_COMPRESS_INFO to help us determine information on index size, partitioning or compression.


I am a big fan of db2relocatedb and use it frequently in large shops that want to physically move the database to a new location quickly, without having to do a backup/restore. I find it useful as a tool to facilitate standardization as well, since I can rename the database or switch the database to a different instance easily using this tool.

I was pleased to learn that DB2 9.7 FP1 provides some new keywords for the db2reocatedb database configuration parameters. The new keyword options are mirrorlogpath, failarchivepath,logarchmeth1, logarchmeth2, and overflowlogpath. This will make my steps to complete the database relocation even easier.

One word of caution, make sure you understand db2relocatedb before you use it since db2relocatedb actually changes internal database structures. See the link at the end of this article for more information on using db2relocatedb.


With DB2 9.7 FP1, READ operations are now possible on HADR Standby databases. This certainly will be welcomed by many organizations who want to make use of those databases for more than just HA and DR support. To me this sounds like a great solution for offloading reporting from the primary database. Of course, the standby database will still be ready to take over from the primary if an outage should occur and this added functionality does not affect the primary log replay that keeps the standby database in sync.

If you're using HADR, it's like you can get a new READ ONLY database for free and perhaps you will enhance performance on the primary database as well since you've offloaded some of the work. Try this. Tell your management that you can give them a free READ ONLY database and see what they say, and then ask for a raise.


Have you ever had a situation where a TCP connection to the database is unresponsive but just doesn't seem to realize it? Of course, you could mitigate this issue by changing the default TCP/IP keep alive setting for the system, but that would impact more than just DB2 and might not make your OS Administrators happy campers.

In DB2 9.7 FP1, a new registry variable, DB2TCP_CLIENT_KEEPALIVE_TIMEOUT allows the DBA to specify the maximum time (in seconds) before a connection is detected as no longer alive. Setting DB2TCP_CLIENT_KEEPALIVE_TIMEOUT to a value that is lower than the system default allows the detection of connection failures sooner but without impacting other TCP/IP traffic.


If you cringe when your application developers first approach you about using LOBS, there is some new information that can help ease the sting of implementing LOBS in your DB2 databases. One of the challenges in the past was that LOBS were not stored in the table with the rest of the data, but instead a descriptor or pointer was stored in the page that indicated where the actual LOB could be found. Increased I/O was one obvious issue.

With DB2 9.7, you have a new solution, known as LOB inlining, which may give you a performance boost for LOBS. Inline LOBS are stored within the formatted rows of pages and are then also eligible for row compression. There are some limits. The maximum size for an inlined LOB is 32669 bytes and that maximum value is dependent on page size. However, if you have a lot of small LOBs, this seems like a way to mitigate that cringe factor.


Do you ever have objects that you suspect are not being used, but you're afraid to drop them? DB2 9.7 FP1 gives us an option to check the "Last Referenced" information for Tables, MQTs, Table Partitions, Indexes and Packages. You can find the LASTUSED column in the SYSCAT.DATAPARTITIONS, SYSCAT.INDEXES, SYSCAT.PACKAGES and SYSCAT.TABLES views.

With a few queries, you can have an idea if that table or index you created 8 years ago still holds any value for anyone. Then, if your suspicions that the object is just a 'space waster' prove to be correct, you could make a plea to your change control team asking if it can be eliminated.


Of course, this isn't everything I learned at IDUG. With its wealth of learning opportunities via sessions, hands-on labs, user networking, vendor exhibitions and, of course, the awesome presentations, an IDUG conference is such a comprehensive educational opportunity that I could probably write a book based on all the knowledge I gained there--but, by the time I finished that book, IDUG 2011 (in Anaheim) would be here and I'd have to start a revision. Instead, I think I will just incorporate some of this newly acquired knowledge into my future blog posts, presentations and articles. Thanks IDUG Conference for all the great information!

I hope you enjoyed these Morsels and that your brain cells have plenty of storage to spare.

Additional Resources

IBM The db2relocatedb tool
IDUG The Worldwide DB2 User Community

» See All Articles by Columnist Rebecca Bond

Mobile Site | Full Site