New Features of DB2 v9.5
20 August 2008
By now you have probably heard of the major new features introduced in DB2 9.1 such as pureXML, Deep Compression, Self Tuning Memory Manager, Automatic Storage and Table Partitioning. DB2 9.5 introduced a Workload Manager and a whole raft of other new features that will be useful on a day to day basis. In this document I’ll present a brief summary of the v9.5 features that I think are most important. All of the new features including the ones listed here are listed in the What’s New manual for DB2 v9.5. I highly recommend looking it over for the full list of new features as well as for reading more about the features that I list here. If you would like to see my take on other versions please visit on my DB2 v9.1, DB2 v9.7, and DB2 v10.1 articles.
Table Compression without Reorg: You can now compress new rows coming into a table without reorganizing the table.
Improved Commands: The db2look and db2pd have several new options that are quite useful.
Increased Identifier Lengths: Several DB2 identifiers can now have longer names. For example, column names can now be 128 bytes long where before they could only be 30 bytes. The entire list of limits can bee seen here.
New Samples: Many new sample files have been added under the <instance_home>/sqllib/samples directory. Included are samples of compression, applications, and database roles.
Binds in Fix Packs: Starting in DB2 V9.5 when you install a new fix pack the binds that you used to have to run manually like db2ubnd.lst and db2cli.lst are run automatically on the local database. It’s about time! However, you still must connect to remote databases and perform the binds when you install DB2 Connect or upgraded DB2 clients.
Timestamp Auto Update: You can now tell DB2 to automatically update the timestamp in a row whenever it is inserted or changed. You can enable this on timestamp columns when creating or altering tables as follows:
alter table myschem.mytable
add update_ts timestamp not null
generated by default for each row on update as row change timestamp;
Backup, Recovery and High Availability
Auto Delete of Logs: i.e. Recovery Object Automation. Finally! You can configure DB2 to delete old archive logs, backup files and load copy images. You don’t have to maintain your own script to do this any longer!
Snapshot Backup and Restore: Until now when backing up or restoring database files you had to use operating system commands to copy files. Now DB2 can allow you to use commands that come with your storage hardware do copy files and this can be much faster. There are some prerequisites to enable this feature, so read about it before trying it.
ROLLFORWARD… TO END OF BACKUP: This new ROLLFORWARD command parameter allows you to roll forward through logs to the point in time when an online backup finished. Often for testing purposes you just want to restore an online backup somewhere and want to copy the minimum number transaction log files to get a good database. Before v9.5 you had to experiment a little to find just the right point in time corresponding to the end of the back to do a PIT recovery. This command will be especially useful when you used the “INCLUDE LOGS” option when creating the backup file.
HADR Auto Failover: DB2 v9.5 introduces the new “db2haicu” command that allows you to configure automatic failover to the surviving node when the primary fails. Before v9.5 you have to know how to configure Tivoli System Automation to automate the monitoring of the primary and failover. Now an easy to use DB2 interface does this for you.
HADR Peer Window: When you use this new DB CFG option you can reduce the chance of loosing committed transactions in a HADR cluster.
Performance and Tuning
Workload Manager: This is a major new feature and should be reviewed carefully in the Workload Manager Guide and Reference manual. This feature allows you to reserve particular system resources for particular workloads. The basic features are free with DB2 v9.5 and the more advanced features must be purchased.
Threaded Architecture on all Platforms: DB2 v9.5 is now a multithreaded application on all platforms. Now when you observe the processes of DB2 v9.5 on UNIX or Linux, you will only see one process per instance. Be careful when upgrading 32 bit instances to 9.5 as now the memory for all heaps and buffers and agents will be in one process and there are severe limits on the memory of individual 32 bit processes.
Memory Shared Between Databases: When the Self Tuning Memory Manager is running, you can now allow memory to be moved between databases automatically as relative workloads change on all platforms. In v9.1 this could only be done in Windows and AIX.
More Automatic Memory: Several additional memory configuration settings can now be set to automatic which lets DB2 manage those heaps and buffers for you for optimum performance.
Administrative Views: Snapshot information is now even easier to get. Everything that you can see in a snapshot can now bee seen in one of these views. There are even some admin views that calculate information for you like bufferpool hit ratio. See them all in the Administrative Routines and Views manual.
Capture Diagnostic Performance Data: The “db2fodc” command should be run to capture instance information whenever the database experiences a hang or severe performance degradation. This information can be sent to support to help diagnose the problem.
File System Caching Off: On AIX, Linux , Solaris and Windows Table space file system caching is now turned off by default on new tablespaces.
Real Time Statistics: You can now allow DB2 to collect statistics at query time when statistics do not exist or are too old.
Optimistic Locking: Locking performance has been improved for some applications.
MDC Rollout Faster: The performance of Multi-Dimensional Clusters perform better when using deferred index clean up option.
Database Roles: A role is a new object in DB2 v9.5. 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.
Trusted Context: In 3 tier environments applications can now send the id of the end users to the database. Before you could only see the userid that the application server used to connect to the database.
Audit Facility Improvements: The audit facility that allows you to record who has done what in your database has been around in DB2 for a long time. However, it was often a big drag on performance. DB2 9.5 largely fixes that and allows additional auditing as well.
Non-Root User Install: User’s without root authority can install DB2 and pass one file to the root user to complete the installation.
Load XML: The load utility can now load XML data.
XML in non-UNICODE DBs: You can now add XML tables in non-UNICODE databases. Before v9.5 if you wanted to use pureXML in your existing database that was not defined as Unicode, you had to export all of your data, drop the database and recreate a UNICODE database and load the data. This prevented a lot of older databases from using this great new feature.
XQuery Partial Updates: XQuery update expressions can allow portions of XML documents to be modified instead of always replacing the entire XML document,
Performance Enhancements: Several changes were made in v9.5 to improve the performance of XML related queries. One of these was to allow small XML documents to be kept with the rest of the row like other normal columns of data, thus allowing less I/O
IDE Libraries Included: Many PHP, Perl and Ruby objects plug-ins are now included in the DB2 Client where before you had to do a separate download. Also several DB2 and Informix add-ins have been improved for Visual Studio 2000.
JDBC and SQLJ: Support for JDBC and SQLJ has been enhanced.
Arrays for Stored Procedures: Array data types are now supported as input and output parameters for stored procedures.
Easier Porting from Other DBMS’s: Several new functions and data types have been added to DB2 like “DECODE” and “NVL” to make life easier for those used to developing for other databases to develop applications for DB2.
Global Variables in SQL Statements: Global Variables are now allowed.
New Decimal Floating Type: A new DECFLOAT Decimal Floating Point data type is introduced that allows exact decimal numbers with large precisions.
No Longer Available
WordWidth on new Instances: You can no longer run 32 bit instances on 64 bit operating systems so the –w option is no longer supported on instance related commands like db2icrt.
Configuration: There are several new configuration parameters. Also several parameters have new defaults or are being deprecated or discontinued. See Chapter 16 in the What’s New manual for a complete list.