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

fig1-1

 

 

 

 

 

 

 

 

From DB2 V9.5 in addition to the inspect utility and offline table reorg, you can now build a dictionary during LOAD INSERTLOAD REPLACEREDISTRIBUTE DATABASE PARTITION GROUPINSERT, 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 : 

tablepartition

 

 

 

 

Here is quick example of creating table paritioning :

CREATE LARGE TABLESPACE ord_2008_Q1 PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14  BUFFERPOOL ibmdefaultbp ;  
CREATE LARGE TABLESPACE ord_2008_Q2 PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14  BUFFERPOOL ibmdefaultbp ;
CREATE LARGE TABLESPACE ord_2008_Q3 PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14  BUFFERPOOL ibmdefaultbp ;
EXCLUSIVE IN ord_2008_Q1,
  

CREATE TABLE orders(id INT, shipdate DATE, …)
PARTITION BY RANGE(shipdate)
(
PARTITION 1q08 STARTING ‘1/1/2008’ EXCLUSIVE IN ord_2008_Q1,
PARTITION 2q08 STARTING ‘4/1/2008’ EXCLUSIVE IN ord_2008_Q2,
PARTITION 3q08 STARTING ‘7/1/2008′ EXCLUSIVE IN ord_2008_Q3,
PARTITION 4q08 STARTING ’10/1/2008′ ENDING AT ‘12/31/2008’ EXCLUSIVE IN ord_2008_Q4
)

You can roll-in and roll-out partition when required for example, when you need to add another partition for the Q1 2009, you can simply roll-in new partition by

CREATE LARGE TABLESPACE ord_2009_Q1 PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14  BUFFERPOOL ibmdefaultbp ;

ALTER TABLE orders ADD PARTITION 1q09  STARTING ‘1/1/2009′ ENDING AT  ’04/1/2009’  EXCLUSIVE IN ord_2009_Q1;

Similarly you can roll-out a partition by using the DETACH command.

table-partion-rolled in

 

 

 

 

 

 

 

These are just glimpses of the numerous features IBM brings to DB2 V9.x.  For detailed information on the features available on Db2 V9.x visit the IBM site.


About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2 and tagged , , , , , , , , , , , , , , , , , , . Bookmark the permalink.