![\"fig1-1\"](\"https:\/\/techsatwork.com\/blog\/wp-content\/uploads\/2009\/04\/fig1-1.png\" "\\"Concept")
<\/a><\/p>\n\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
\u00c2\u00a0<\/p>\n
From DB2 V9.5 in addition to the inspect utility and offline\u00c2\u00a0 DB2 Role based security is yet another features DB2 V9.5 brings to the table. \u00c2\u00a0Role 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.\u00c2\u00a0Roles simplify the administration and management of privileges by offering a capability equivalent to groups but without the same restrictions.\u00c2\u00a0Using new SQL commands you can grant and revoke authorities to a role and assign users to those roles.\u00c2\u00a0 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.<\/p>\n DB2 High Availability Disaster Recovery (HADR) got some improvement in DB2 V9.5 too. \u00c2\u00a0IBM introduced configuration utility called db2haicu.\u00c2\u00a0db2haicu 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.<\/p>\n Recovery Object Management is yet another feature I love in Db2 V9.5. \u00c2\u00a0We all know how annoying it was to manage the old backups, archive logs, load copy images. \u00c2\u00a0There is now an option in DB2 to automatically manage these and delete the obsolete ones.\u00c2\u00a0The DB2 database manager automatically prunes the database history file of entries that exceed the number specified in the\u00c2\u00a0num_db_backups<\/span>\u00c2\u00a0configuration parameter and are older than the date specified by the\u00c2\u00a0rec_his_retentn<\/span>\u00c2\u00a0configuration parameter. If you set the new configuration parameter\u00c2\u00a0auto_del_rec_obj<\/span>\u00c2\u00a0to\u00c2\u00a0ON<\/span>, 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\u00c2\u00a0auto_del_rec_obj<\/span>\u00c2\u00a0is enabled, the system will only perform this maintenance when both the\u00c2\u00a0num_db_backups<\/span>\u00c2\u00a0and\u00c2\u00a0rec_his_retentn<\/span>\u00c2\u00a0values are exceeded.<\/p>\n 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\u00c2\u00a0\u00c2\u00a0paid feature\u00c2\u00a0extends the capability of DB2 9 into the parallel,\u00c2\u00a0multi-partition environment, improving the performance and the scalability of very\u00c2\u00a0large databases. This allows very complex queries to execute much faster. DB2\u00c2\u00a0Enterprise 9 with DPF is an ideal solution for managing data warehousing and\u00c2\u00a0data mining environments, but it can also be used for large online transaction\u00c2\u00a0processing (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. \u00c2\u00a0Physical partition on the other hand is designed to have each partition one\u00c2\u00a0separate\u00c2\u00a0physical servers where none of the resources are shared and hence is called share nothing architecture. \u00c2\u00a0In 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.\u00c2\u00a0<\/p>\n 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.\u00c2\u00a0The table partitioning <\/em><\/span>feature provides a way of creating a table where each\u00c2\u00a0range of the data in the table is stored separately.\u00c2\u00a0Each partition in your partitioned table is stored in a storage object that is also\u00c2\u00a0referred to as a data partition or a range<\/em><\/span>. Each data partition can be in the same\u00c2\u00a0table space, separate table spaces, or a combination of both.\u00c2\u00a0A commonly used partitioning scheme is the date, where you may decide to clump together data in data partitions such as by year or month. \u00c2\u00a0One of the benefits of table partition is the improved query performance.\u00c2\u00a0The 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. \u00c2\u00a0Below diagram illustrates it further :\u00c2\u00a0<\/p>\ntable reorg<\/code>, you can now build a dictionary during\u00c2\u00a0LOAD INSERT<\/code>,\u00c2\u00a0LOAD REPLACE<\/code>,\u00c2\u00a0REDISTRIBUTE DATABASE PARTITION GROUP<\/code>,\u00c2\u00a0INSERT<\/code>, or\u00c2\u00a0UPDATE<\/code>\u00c2\u00a0as long as the\u00c2\u00a0COMPRESS\u00c2\u00a0<\/code>table attribute is turned on and compression threshold is met. \u00c2\u00a0DB2 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.<\/p>\n