Full steam ahead with DB2 LUW versions !

Did you know that IBM is ending official support for DB2 V8.2 on April 30th, 2009. Yep thats 15 days away and getting extended support will cost you an arm and a leg !  Lot of customers I know are unhappy because lot of third party applications do not certify DB2 9.x as a support backend database. The company I work for has a few of those. So we ended up with getting extended support for few of those servers and the rest we migrated them to DB2 V9.5 .  DB2 V9.5 is pretty sweet. It has the long awaited thread based connection and handles memory management pretty well. You can run DB2 pretty much on auto pilot, but I highly recommend not to run on “self tuning” mode in critical production system.  I believe human brains with experience are much superior to db2’s optimizer.  

DB2V9.5 takes the compression to the next level.  IBM has improved the way DB2 builds the compression dictionary. In DB2 V9, there was only two ways to build the compression dictionary :  One of which was to run an offline reorg with reset dictionary option that build the compression dictionary and the other was to run an INSPECT with rowcompestimate command. No rows were compressed unless either of these utilities was run. This required tables to be offline for the duration of the utilities. DB2 V9.5 improved the situation,  IBM introduced the concept of Automatic Dictionary Creation (ADC).  Research showed, a good compression dictionary can be build from sampling, remember I said good not perfect. ADC is based on data sampling.   In DB2 V9.5  ADC will start building a compression dictionary automatically when  when the size of the table exceeds a threshold of approximately 2 MB and there is sufficient user data (at least 700KB) contained within the table once the threshold size is reached.

Once the dictionary has been created and automatically inserted into the table, all future population of data is subject to being compressed.  The concept of ADC is illustrated in the below diagram

 

 

 

 

 

 

 

 

From DB2 V9.5 in addition to the inspect utility and offline table reorg, you can now build a dictionary during LOAD INSERT, LOAD REPLACE, REDISTRIBUTE DATABASE PARTITION GROUP, INSERT, or UPDATE as long as the COMPRESS table attribute is turned on and compression threshold is met.  DB2 9.5 also includes a new table UDF SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO and a new administrative view SYSIBMADM.ADMINTABCOMPRESSINFO that can give detailed information regarding the compression of each table that is turned on for compression.

DB2 Role based security is yet another features DB2 V9.5 brings to the table.  Role based security is one of the features that was missing in DB2. All its competition : Oracle and MS SQL Server had role based security for a while. Well IBM satisfies that requirement. Roles simplify the administration and management of privileges by offering a capability equivalent to groups but without the same restrictions. Using new SQL commands you can grant and revoke authorities to a role and assign users to those roles.  Roles are very similar to groups, but where groups had to be defined to the operating system or security system, a role is strictly a DB2 object.

DB2 High Availability Disaster Recovery (HADR) got some improvement in DB2 V9.5 too.  IBM introduced configuration utility called db2haicu. db2haicu has an interactive command-line interface. db2haicu collects configuration information about your cluster, database instance, and machines by asking a series of questions and by directly investigating your system. Once the configuration information collected, db2haicu then creates an abstract model of your clustered environment called the cluster domain.

Recovery Object Management is yet another feature I love in Db2 V9.5.  We all know how annoying it was to manage the old backups, archive logs, load copy images.  There is now an option in DB2 to automatically manage these and delete the obsolete ones. The DB2 database manager automatically prunes the database history file of entries that exceed the number specified in the num_db_backups configuration parameter and are older than the date specified by the rec_his_retentn configuration parameter. If you set the new configuration parameter auto_del_rec_obj to ON, the database manager will also delete backup images, load copy images, and log files associated with any history file entries that it automatically prunes. Once auto_del_rec_obj is enabled, the system will only perform this maintenance when both the num_db_backups and rec_his_retentn values are exceeded.

DB2 Partitioning is a feature DB2 has been carrying for a while. Pre DB2 V9 had database partition commonly called as DPF . In this architecture the database instance is split into multiple nodes. This  paid feature extends the capability of DB2 9 into the parallel, multi-partition environment, improving the performance and the scalability of very large databases. This allows very complex queries to execute much faster. DB2 Enterprise 9 with DPF is an ideal solution for managing data warehousing and data mining environments, but it can also be used for large online transaction processing (OLTP) workloads. DPF can be logical or physical. In case of logical partition, the partitions reside on the same server and takes advantage if the symmetric multiprocess (SMP) where the CPU, disk and memory are shared among all the partitions. This is called share everything architecture.  Physical partition on the other hand is designed to have each partition one separate physical servers where none of the resources are shared and hence is called share nothing architecture.  In DPF, each node is its own instance, but one of the instance is the primary instance. It owns the other instances on the database partitioning facility. 

With DB2 9.x , IBM introduced one more level to partitioning, called table partitioning. This feature was already available in Db2 Z/os, they now extended that to LUW as well. The table partitioning feature provides a way of creating a table where each range of the data in the table is stored separately. Each partition in your partitioned table is stored in a storage object that is also referred to as a data partition or a range. Each data partition can be in the same table space, separate table spaces, or a combination of both. A commonly used partitioning scheme is the date, where you may decide to clump together data in data partitions such as by year or month.  One of the benefits of table partition is the improved query performance. The DB2 optimizer is data partition aware. Therefore, during query execution, only the relevant data partitions are scanned. Eliminating the need to scan data partitions that are not impacted by the query can result in improved performance.  Below diagram illustrates it further : 

 

 

 

 

Here is quick example of creating table paritioning :