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 other versions please visit on my DB2 v9.1, DB2 v9.5, and DB2 v10.1 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.




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:



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

Page size

Version 9.5 table space limit

Version 9.7 table space limit

4 KB

2 TB

8 TB

8 KB

4 TB

16 TB

16 KB

8 TB

32 TB

32 KB

16 TB

64 TB


Distribution Map Supports Larger Warehouses:  In DB2 Version 9.7, the distribution map has grown from 4096 (4 KB) entries to 32,768 (32 K) entries.


New Database Partition Servers Available Immediately:  In Version 9.7, you can use the START DATABASE MANAGER command to add new database partition servers to a multi-partition database environment without having to stop and restart the instance. 


Backup, Recovery and High Availability


Maximum db2diag.log Size:  You can now set a maximum amount of space that the db2diag.log and the InstName.nfy files can consume on your system.  This can prevent outages due to filling the file system.  This is accomplished by using the new “diagsize” DBM CFG parameter.


Performance and Tuning


Scan Sharing:  With this feature when a query is scanning through a large amount of data and a second query starts that uses the same pages, the second query will use the pages fetched by the first query as they enter the bufferpool and continue to follow the first query’s pages as they are retrieved.  When the first query completes, the second query will go back and fetch the pages that left the buffer pool before it started.  This does not change DB2’s previous behavior of subsequent queries being able to use pages already in the bufferpool.  It only affects large queries that scan through so many pages that existing pages in the bufferpool must be overwritten quickly as new pages are fetched.


Reduce Locking for CS Isolation Level:  Almost all DB2 databases use the default Cursor Stability (CS) isolation level.  Until now this has always meant that if one application changes a row and another application tries to read that row before the first commits it, the second will wait for the commit.  You can now set the “currently committed semantics” of the CS isolation level that tells DB2 that when the second transaction tries to read a locked row, that it will get an image of what the row looked like before the change.  It gets that information from the transaction logs.  The behavior observed by the application will be very similar to Oracle’s default locking, but without all of the overhead of rollback segments.  This isolation is set with the “cur_commit” DB CFG parameter.  Databases upgraded from previous versions will use the traditional behavior of CS unless you specifically enable the new one.  Please see chapter 7 of the What’s New manual before using this feature because there are a lot of other things you will need to know to use currently committed.


More Efficient Queries with Literals:  DB2 has always made good use of resources by not needing to recompile new queries that are identical to queries already in the package cache.  It now considers queries that are the same except for literals in the predicate (where clause) to also be identical.  This is especially useful in OLTP workloads that execute lots of queries with different predicate literals because it can significantly reduce the amount of prepare time.


Small LOBs Stored in-line:  You can now allow “smaller” Large OBjects (LOBs) to be physically stored with the rest of the columns in your table.  In this case “smaller” means that the LOB and the rest of the columns in the row will fit within your page size.  This can improve performance if you access the LOB columns frequently because you can get the regular and LOB columns with one physical I/O rather than two.  In-line LOB’s will also be kept in the bufferpool which can increase performance, but it could also cause problems.  You will need to test that.


New SQL Accessible Monitor Objects:  Several new table functions have been introduced that allow you even more access to your system monitoring data through SQL.  These new objects are “light weight” and consume fewer system resources than a snapshot.  To make the most of these new functions, you need to set new DBM parameters monitor switches.  Please see chapter 5 of the What’s New manual for details of the functions and DBM monitor switches.


Partitioned Indexes:  Partitioned tables can now have partitioned indexes.  This can have big performance benefits, especially when rolling new partitions into- or old partitions out of- a partitioned table.


WLM Enhancements:  Several enhancements have been made to the workload management feature of DB2/LUW.  New features include activity-based thresholds, bufferpool priority, and integration with Linux workload management.  There are also several other features.  All of them can be seen in chapter 9 of the What’s New manual.




Rename Columns:  You can now change he names of columns in a table using the ALTER TABLE command.


… OR REPLACE:  The “OR REPLACE” syntax has been added to most CREATE statements – “CREATE OR REPLACE”.  You can now use this option for tables, indexes, views, functions, triggers, stored procedures and many more.  This is great when you have a long DDL script that had a few bugs where most objects got created and some didn’t, but you don’t care – you just want to correct the errors and run the script again from the top without other changes.


Alter more data types:  You now have more options for which column data types can be changed to other data types in the ALTER TABLE command. .  All of them can be seen in chapter 11 of the What’s New manual.


TRUNCATE TABLE:  You can now delete all of the rows in a table very quickly.  The rows deleted with the truncate command are not physically deleted or logged, so the transaction is extremely fast.




 New and Changed Security Roles:  SYSADM no longer gets DBADM authority by default on new databases and a number of other administrative groups have been added including WLMADM and SQLADM.  Please see chapter 10 of the What’s New manual for more information.


Longer Passwords Allowed:  DB2 now allows you to use passwords as long as those supported by the operating systems of the database server.


LDAP and SSL improvements:  Please see chapter 10 of the What’s New manual for more information.




XML in DPF Partitions and Database Objects:   Previous to v9.7 XML could not be used in a number of DB2 features.  Now it can be used with Range/Table partitions and in Database Partition Facility (DPF) databases.  Tables with XML columns can also now be used in views and Materialized Query Tables (MQTs), Multi Dimensional Clusters (MDCs) and in declared temporary tables.


Other XML:  Several other features that improve performance and make tables with XML columns easier to use have been added.  Please see chapter 4 of the What’s New manual for details.


pureXML is FREE!:  The pure XML feature of DB2 is free in v9.7 in all editions.  It has also been free since 10 February 2009 in DB2 v9.5, but you have to use fix pack 3b or higher. 


Application Development


Many New Functions:  Lots and lots of new scalar functions have been added like the DECODE and NVL commands.  Please see chapter 11 of the What’s New manual for details.


Python and Visual Studio:  Several features have been added that improve the experience of developing Python, Visual Studio, .NET and other applications.  Several new samples have also been added.  Please see chapter 11 of the What’s New manual for details.


No Longer Available
The following features are no longer supported in DB2 v9.7 and in many cases these discontinued features will prevent a successful upgrade of databases from a previous version.  Please See Chapter 18 in the What’s New manual for a complete list of discontinued features and ideas on how to change them before upgrading.


Type-1 Indexes:  All type-1 indexes will be marked invalid on databases upgraded to DB2 v9.7.  It would be a good idea to convert them prior to upgrading.  All indexes created in versions earlier than v8 will be type-1 unless you have converted them.  Indexes created in v8 and later may be type-1. 

DPF on 32-bit Windows:  Partitioned databases can no longer be run on this platform.

XML Extender:  The pureXML feature is now free.



Functionality gets marked as deprecated when a specific function or feature is supported in the current release but might be removed in a future release. In some cases, it might be advisable to plan to discontinue the use of deprecated functionality.  Here is a list of some of the deprecated features that struck me as important.  Please See Chapter 17 in the What’s New manual for a complete list.


  • Control Center:  This is the first step in replacing Control Center with Data Studio.  Please see my Data Studio Overview article for more information on that tool.
  • DAS:  DB2 Administrative Server
  • DB2 Governor: 
  • Query Patroller: 
  • Health Monitor:  It is being replaced by the Data Studio Administrative console.  Please see my Data Studio Overview article for more information on that tool.
  • WFS Format for Load and Export: 
  • Java SDK 1.4.2: 
  • Some Event Monitor Options: 





Configuration:  There are several new DB, DBM, Registry and Environment 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.


System Views and Tables:  Many system views and tables have changed in v9.7.  See Chapter 16 in the What’s New manual for a complete list.





Please note that not all new, changed and removed features are listed on this page.  My goal was to summarize the most useful ones.  I left out many that have only very specific uses or only affected select operating systems like Solaris or AIX.  In case any of these specific cases not list affect you I will again recommend reviewing the What’s New manual.



HOME | Search