New Features of DB2/LUW v9.7
23 June 2009
On Friday, 19 June 2009, the latest version of “DB2 on Linux, UNIX and Windows” (DB2/LUW) was released. The biggest change to this release was the SQL Compatibility feature that allows many applications and scripts written for Oracle databases to now be easily be run with DB2 databases. Even though the SQL Compatibility feature is very important and is seeing the most hype, there are many other features that will help you on a daily basis. I will devote the majority of this article to those features, by providing a summery of the ones that I feel will be the most useful to the most people. Before upgrading your existing databases, you should also take a close look at the deprecated and discontinued features that I have listed at the end of this article.
To see a complete list of new, changed and discontinued features with more details of each please see the What’s New section of the DB2 v9.7 Information Center. Here are some other relevant links:
You can also review the Announcement Letter for v9.7 to see all of the features and various editions that can be used. We are also having some upcoming on line events that you can attend. Please see my calls page for more details. Please see the DB2 Upgrade Portal for an extensive library of materials that will make your upgrade to DB2 v9.7 and other versions very easy. If you would like to see my take on earlier versions please visit on my DB2 v9.1 and DB2 v9.5 articles.
SQL Compatibility Feature (Oracle Compatibility)
This feature is especially useful for those companies who write commercial software that needs to run on multiple databases and also for those tired of asking “How High?” when a certain database vendor says JUMP! This new version of DB2/LUW not only allows you to use objects in DB2 that look like Oracle objects such as tables with “VARCHAR2” data types and commands like “DECODE” or “NVL” but it can also behave like Oracle. For example this version introduces a new isolation level semantic called “CURRENTLY COMMITTED” that allows DB2 locking to generally work as Oracle does and this means that your applications can work with both databases without different logic. We have also introduced a new interface called “CLP Plus” that is useful for running scripts written for another vendor’s interface. All of these things are done natively -- not with emulation or translation so that you can get excellent performance. You can read more about all of the features that make it easy to run Oracle database applications on DB2 at the SQL Compatibility Link.
Miscellaneous
Compression of Indexes, Temp Tables and LOBs: In addition to compressing table data you can also now compress indexes, temporary tables, Large OBjects (LOBs) and all XML documents. This can save considerable space and in many cases improve performance. A new command is available that can be run on non-compressed indexes to see if index compression will save space. It is called ADMIN_GET_INDEX_COMPRESS_INFO. Starting in V9.7, compressed databases can be replicated using IBM’s replication tools.
Convert Existing Databases to Automatic Storage: With v9.7 you can now convert your database and DMS Tablespaces to Automatic Storage. This can be done with either the ALTER TABLESPACE or a redirected restore.
Reclaim Unused Space from Automatic Storage Tablespaces: You can now reclaim unused space in automatic storage tablespaces. This is somewhat involved, so please see the What’s New manual for details.
Add Paths to Automatic Storage Tablespaces: You can now add new paths to Automatic Storage tablespaces and DB2 will rebalance them for you.
New and Updated Administrative Views:
Storage Monitoring: SNAPSTORAGE_PATHS, SNAP_GET_STORAGE_PATHS_V97, SNAPTBSP_PART, SNAP_GET_TBSP_PART_V97
ADMIN_MOVE_TABLE Stored Procedure: This stored procedure allows you to move a table to a new table object, possibly with the same name, while maintaining access to the old table while the move is being done. This function can be used to build a new optimal compression dictionary for a table or to move it to a different tablespace.
Larger “Large” and “Temporary” tablespaces