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!
INDEXES
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.
DB2RELOCATEDB
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.
HIGH AVAILABILITY AND DISASTER RECOVERY (HADR)
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.
NEW REGISTRY VARIABLE
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.
LARGE OBJECTS
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.
LASTUSED
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.
ONE LAST MORSEL
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
