db2Dean’s 2008 IOD Highlights
21 November 2008
I attended the 2008 IBM Information On Demand Conference the last week of October. I learned a lot and had some fun too. In this article, I will highlight some of the interesting things that I learned or was reminded of while there. The first general session featured Martin Short who is still pretty funny. Oh, yes Ambuj Goyal, the General Manager of our data management business spoke too. I enjoyed seeing many old friends and new friends at the beach/Halloween party held on Tuesday night. As you can see to the left I was dressed as my true self when I was there. Please note that there is no original information here from me in this note. All information here is either taken directly from the presentations that I attended or is paraphrased.
There were several sessions on our new Data Studio initiative. If you were not aware, Data Studio includes a number of new products as well as a platform to integrate existing products. The new products include includes pureQuery and pureQuery Runtime which allow you to develop Java applications more efficiently and make existing SQL run with fewer database resources. It also includes a free base edition that has a number of good features for both the DBA and developer such as content assist. Content assist does things like help you write a query where you can press ctrl+space to pick the columns that you want instead of typing them into the query. It also allows existing products work together more easily through Eclipse Shell Sharing. For example if you are shell sharing Rational Data Architect and Data Studio Administrator (A.K.A Change Management Expert) you can edit your model in RDA and have DSA generate the script to change the physical database that preserves the data in the changed tables along with the alias, triggers, and grants. There are a ton more things that it does and I hope to write an article on this initiative in the future. In the mean time you can read more about it and down load a free copy of the base edition here.
I attended a session on our new “ DB2 Best Practices’ web site. In this job I frequently get a lot of questions about how best to design a database, how best to use SAN and NAS storage and Automatic Storage, database monitoring and tuning, database server virtualization and HADR. These and a number of other papers can be found on this new page. Papers like these often become obsolete after a while, but the team managing this intends to keep them updated as well as adding additional papers. They also plan to create a Wiki to allow feedback that will be incorporated into these papers. So when ever you are designing anything new like a new database or adding a major like compression you should review the relevant best practice paper. They are also good to review to keep your self current for ongoing tuning and just to know that things like the DB2_PARALLEL_IO registry parameter should still be used. The Best Practices site is on my Favorite Links page or you can visit it directly from here by clicking here.
Many new sample scripts are now available so that you do not have to reinvent the wheel for many activities. Samples for coding DB2 database applications have been created for many languages. There are many new samples available also for database administration in v9.5. Many of these samples are useful for older versions of the database as well. Most of the samples for application coding are on the web along with some of the database administration samples.
Rational Data Architect
The RDA presentation that I attended showed us how to do modeling with RDA, but repeating that would be out of the scope of this document. Here I will share some of the interesting tips that you might not find elsewhere:
Ease of Use Features
I attended the session that described a number of new features that can make your life easier. Here a few of he best ones:
The biggest architectural change in DB2 in v9.5 on Linux and Unix, is that DB2 now runs as one threaded process on all platforms instead of multiple processes. It has always run as a threaded process on Windows. Each of the processes that you used to see, including agents, now run as threads in one process. This generally improves performance because context switching between threads is less expensive than between processes. Also there is only one set of file descriptors for the process that are shared between threads. You should note that this architecture can cause you to run out of memory on 32 bit operating systems when upgrading databases that are close to consuming most of the memory allowed in one process by the 32 bit OS.
You can get information on from UNIX and Linux about the individual threads. Here is an AIX example:
ps –fu db2inst1 ** Get the PID of the instance process. Use the name of your instance if it is not “db2inst1”
ps –lLfp 123 ** Show the processes for PID 123 (Use your PID returned from the “ps –fu” command
You can also see several of the threads by connecting to the database and issuing the command “db2pd –edus”
Also due to the threaded model, some configuration parameters are no longer used because they are irrelevant in a threaded model. Also several additional parameters can be configured while the database is on line and additional parameters can be set to automatic.
You can prevent a rouge transaction from keeping your transaction logs tied up with the following two parameters by setting limits on log resources used by any one transaction.
It is usually a good idea to use alternate page cleaning on for OLTP systems to keep a steady amount of page cleaning going in the background.
db2set DB2_USE_ALTERNATE_PAGE_CLEANING = ON
Direct I/O should be used to give you the best I/O performance. In DB2 v9.5 this is the default for most operating systems for new tablespaces, but not on all OS’s. You can see which ones at this link:
Don’t forget that turning on the Self Tuning Memory Manager and setting most or all memory related parameters to automatic will generally give you very good performance with very little effort. The new INSTANCE_MEMORY DBM configuration parameter can be used to set an upper limit on the total amount of memory used by a DB2 instance
The “Application Group” concept goes away in DB2 v9.5. Good riddance! I never really understood it anyway.
You can use any of the following commands to see how much memory your instance is using:
db2 get dbm cfg show detail | grep INST
db2 select "* from table (sysproc.admin_get_dbp_mem_usage(-1)) as t"
· Estimate compression ratios using:
o For v9.5
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(‘schema’, ‘table_name’, REPORT | ESTIMATE)) AS T
o For v9.1
DB2 INSPECT ROWCOMPESTIMATE TABLE NAME <tn> RESULTS KEEP <fn>
“Dictionary created if COMPRESS attribute for table is YES”
o For v8.2 and before:
There is a tool that I can send you that that you can run against v8.2 databases that would estimate the compression that you would get if you upgraded to v9.x with compression and used the feature. Send me a note at firstname.lastname@example.org and I’ll send you the tool.
· In DB2 v9.5 you want to use the default (AUTOMATIC) setting in almost all cases for: NUM_IOCLEANERS, NUM_IOSERVERS, and PREFETCHSIZE
There is always a ton of great information at this conference. I hope to see you there next year!