db2Dean’s 2012 IOD Highlights
28 November 2012
While I had to walk around a bit to make the acquaintance of a fine feathered friend because there were no entertainment celebrities there at the conference this year, I was able to learn lots of great new stuff at the IOD sessions. There was tons of information and I’ll give you a summary of what I learned along with many random tips and tricks. If you attend the conference as you should have, then you can download many of the presentations from the Conference SmartSite. As time has gone on many more have been added, so try again if something you wanted was not available on the site when you first got back. I look forward to seeing you and my new friend again at next year’s conference! Also please add anything else you though was really interesting to my db2Dean Facebook Page or to the “Message Board” section of my page.
Of course some of the biggest news at IOD this year was about the newly released DB2 version 10.1. I discussed a number of facets of the new release in my DB2 10 article, but I learned a number of new items at the conference. Some of these features are in all editions and some are only in certain editions. To see if the feature that interests you is in your favorite edition see the Functionality by Edition page. Here are some of the more interesting ones:
IBM Data Studio
With the release of DB2 v10.1 there is no more Control Center and you should start using the IBM Data Studio. Nearly all functions of control center are now in Data Studio plus it has several others such as a procedure builder and debugger and a feature to generate scripts to change database schema while preserving data. For a summary of the tool, please see my 2011 IOD Summary article or see my Data Studio Web Console article to see how to do health monitoring and task management. A number of improvements have been made with the release of Data Studio 3.1.1 including:
Storage Groups and Online Tablespace Movement
DB2/LUW 10.1 introduced storage groups to the automatic storage facility. This allows you to group different sets of directories into named storage groups and to build your tablespaces on the group of your choosing. Generally different storage groups will be built on different file systems. Further with the ALTER TABLESPACE command you can move your tablespace from one storage group to another with no outage. DB2 moves the data in the tablespace in a very similar manner to how it redistributes extents when you add a new container to a tablespace. These new features allow you do some interesting things. The primary one is to allow you to have a Multi-Temperature database, meaning that some of your data is hot and used a lot and other data is warm or cold. In this scenario you would want to put your hot data which is generally your newest data on your fastest storage and put cooler data on less expensive storage. In this case you would create a hot storage group on your fastest storage, maybe using Solid State Devices (SSD), and build other storage groups on file systems from other storage media. If you partition your tables, you can put the table partitions with the newest data into the tablespaces built on the fast storage group and then move those partitions to less expensive storage with the ALTER TABLESPACE command as time passes. In addition to multi-temperature databases the storage groups facilitate other things as well. For example, if you have multiple applications running on one database, you may want to put the tables for each application into a different storage group which will prevent one application from saturating the I/O of another, assuming your storage groups are built on different physical disks. It is also just useful if you get new storage added to your server – you can just create a new storage group on the new file systems and use the ALTER TABLESPACE to move them to the new storage. Use the MON_GET_ REBALANCE_STATUS() table function to view the status of the rebalance.
Optim Query Capture and Replay is a tool that can capture all of the SQL being executed against a database and play it back to the same database or a different one. When replaying the SQL you can speed them up or slow them down which is great for performance testing. This facility not only lets you see how the load did on a different database, but can generate reports to tell you how the speed compares between the capture and the replay scenarios. That is great for trying out new hardware or verifying tuning configuration changes before actually implementing them in production to see if the effect is good or bad and by what factor. The reports can also show differences in return codes so that you can verify that you copied all objects, grants, etc before actually moving a database.
Optim Archiving and Test Data Management recently released version 9.1. New features in this tool include:
Guardium Database Activity Monitoring has been significantly improved with the release of version 9.1. This application allows you to centrally monitor who is accessing your databases, alerts you to violations of security policies, alerts when abnormal patterns of activity is taking place, and can disconnect rogue users. Among many other things, Guardium 9.1 added direct support of Hadoop systems and iSeries databases. Further, if you want to monitor a data source for which Guardium does not provide an agent, Guardium provides a facility called Universal Feed that helps you write and integrate your own agent. As with all previous releases, improvements were made in the efficiency of the agent, making it even more efficient. When monitoring DB2/LUW v10.1 and above you don’t need to install the agent, because it is now built into the database. You just need to turn it on with the DBM parameter COMM_EXIT_LIST parameter. V9.1 introduces load balancing among the collector servers using the Guardium Grid that also provides high availability. It also introduces a dashboard to allow you to monitor and tune your collectors as a group.
I hope that you found at least a few pieces of information in this article to be new and useful. I hope to see you at the conference next year.