Saturday, June 20, 2009

DB2 9.7 is now available!

DB2 9.7 is now available! To download the latest DB2 Express-C 9.7, click here.

DB2 Express-C 9.7 includes a number of improvements and enhancements, too long to provide a complete list here. Other editions of DB2 include even more improvements. You can review the "What's new overview" section of the DB2 Information Center for details. However, these are the ones I like:

1) Locking enhancements
With the new Currently Committed (CC) semantics of the CS isolation level, writers (i.e: UPDATE) will not block readers (i.e SELECT). Readers will read the currently committed value prior to the update operation. This will improve concurrency and reduce deadlocks.

For example, say you have a table T1 with the following contents:

Raul Chong
Jin Xie

Now say an application AppA issues this statement, but does not commit:
update T1 set lastname = 'Smith' where firstname = 'Raul'

Next, application AppB issues this statement:
select lastname from T1 where firstname = 'Raul' with CS

Prior to DB2 9.7, this last statement would hang because it would wait for the exclusive lock held by the update statement of AppA (the writer) to be released. With DB2 9.7 and currently committed enabled (the default for new databases), the statement would return the currently committed value which was Chong.

2) Performance enhancements
For dynamic SQL, DB2 9.7 introduces the 'statement concentrator' feature, which should improve the performance of programs developed in languages such as Ruby and PHP. As its name suggests, the statement concentrator will look for SQL statements that look exactly the same but where the parameter value is different, and will 'concentrate' those statements into one. For example:

SELECT name FROM employee WHERE empid = 10
SELECT name FROM employee WHERE empid = 20
SELECT name FROM employee WHERE empid = 30

will be concentrated into:
SELECT name FROM employee WHERE empid = ?

The question mark (?) represents a parameter marker. At runtime, DB2 will supply the value of 10, 20 and 30 for this particular example. This will provide a huge performance boost. In prior releases, DB2 would treat each statement as unique, and would compile and execute each statement separately. However, the access plan for all these statement was likely the same, so it was a waste of resources to calculate it over and over again. With statement concentrator DB2 will compile the statement only once to obtain the access plan for the statement, and then it will reuse it.

Other performance improvements is the ability to store small LOBs inlined with the data rows. This improves performance for these LOBs as they can be access through the bufferpool.

3) Application development enhancements
A large number of improvements is now available to application developers:

Triggers and UDFs now have enhanced SQL PL support. Before DB2 9.7 they only supported inline SQL PL which is a small subset of SQL PL.

For SQL PL stored procedures, default parameter values and assignment of parameter values by name have been included. In addition, new Boolean, cursor, row and array data types are now supported.

ROWNUM and ROWID are supported

DB2 9.7 now supports the concept of a module (similar to the concept of 'Package' in Oracle). A module can bundle related database object definitions

Implicit casting between data types is now allowed. This will be useful for dynamic languages like PHP and Ruby. For example, this is now allowed:

create table t1 (col1 int)
select * from t1 where col1 = '42'

In the example, the string '42' can now be compared to the integer column col1.

There are many new functions for string, date manipulation and so on.

DB2 now supports Create Global Temporary Tables (CGTTs). These are temporary tables where the data is only available during the session. The difference between CGTTs and Declared Global Temporary Tables (DGTTs) is that in the case of CGTTs, the table definition is persisted in the DB2 catalog tables. So once a CGTT is created, it can be used by any session without having to create it again. The rows will be independent per session though.

Public aliases (a.k.a Public synonyms) for global object referencing have been added. Prior to DB2 9.7 all DB2 objects had a unique two-part name. The first part was the schema name, and the second part was the object name. By creating public synonyms, you can reference to objects without the schema name regardless of the user ID connected to the database. For example:

connect to sample user arfchong using mypsw
create public synonym raul for table arfchong.staff
select * from raul ## works OK
select * from arfchong.raul ## Error
connect to sample user db2admin using psw
select * from raul ## works OK

DB2 9.7 has relaxed its object dependency rules to allow for more flexible schema evolution. For example say you had this dependency:

View2 --> View1 --> Table1

If you wanted to change View1, you first had to drop View2 because it was dependant on View1. Now this is not required. DB2 will perform the revalidation of View2 automatically for you either immediately or when the view is used again. This can certainly help developers when testing their applications, and when they need to add columns, or make other changes to the database schema.

Other improvements in the schema evolution area are: Columns can be renamed, the REPLACE option has been added to several CREATE statements so there is no need to issue a drop statement therefore all privileges on the object remain. ALTER COLUMN can now use SET DATA TYPE for all types.

The Visual Studio add-ins have been enhanced

There have been JDBC and SQLJ enhancements to support some of the things mentioned earlier

The TRUNCATE SQL statement is now supported for fast deletes

New data types such as NUMBER, VARCHAR2, TIMESTAMP with a precision are now supported

Better support for Python-DB2 applications

pureXML enhancements such a support for Declared Global Temporary Tables is now available

4) Storage enhancements
Every time I teach DB2, I often get the question about "how much can DB2 store?". And almost on every release, I'm surprised about how our developers at the lab keep pushing the limits. For DB2 9.7, the size of large and temporary table spaces limits have been increased fourfold. For example, a 32k page large table space can now store 64TB. Previously it could 'only' store 16TB.

5) Security enhancements
In the past I used to compare SYSADM with 'God' because he could pretty much have access to everything in DB2. With DB2 9.7 the security model has been enhanced to allow for separation of duties. This minimizes the risk of data exposure and helps with government compliance requirements. There are several new authorities including DATAACCESS and ACCESSCTRL. These authorities can be given to users to access data, and grant access to data respectively. What this means is that a security administrator (SECADM) could revoke SYSADM or a DBADM these authorities, and therefore they would not be allowed to access any data!

6) Manageability enhancements
Now you have the ability to move tables online to a different table space. This means that while users are accessing a table, you can change where the table storage resides.

7) Installation enhancements
Now you can use the db2val command to verify if your DB2 installation is OK.

Many of these enhancements are described in the free online book "Getting Started with DB2 Express-C - 3rd Edition" which has been updated to DB2 9.7. The book will be available this June 26th!.

Enjoy the new DB2 9.7!!!

Cheers, Raul.


page failed while rendering :username said...

Looking forward for examples on some of the described features. The concepts of modules seems interesting.

On the pureXML side there are some things missing. See

Raul F. Chong said...

Thanks for the comment. Please note I'm focusing on enhancements for the DB2 Express-C edition. Other editions include more enhancements/improvements.

Cheers, Raul.

Ricardo K. Baba said...

Hi Raul,

It's Ricardo. I have read something about the new features of DB2 9.7 and I realised the DB2 Express-C doesn't support the PL/SQL as the major versions of DB2. Is there a posibility to see this feature in a near future in the DB2 Express-C?


Ricardo Baba.

Raul F. Chong said...

Hi Ricardo,

There are plans to provide this support in the future, but I cannot guarantee anything at this time.

Thanks for the comment. Send me a note to my work email account and let me know how this support would impact you.

Cheers, Raul.

adnan said...

Hi Raul,

just wanted to know that when we are using a sequence to generate values, we actually loose some of the values when the connection is refreshed. But this is not the case with Identity column. i want to ask that what is the use of using a sequece then, apart from the fact that sequence is an object hence it can be used globally.

Thanks & Regards,
Adnan Khan

Raul F. Chong said...

Hi Adnan,

Maybe you should create the sequence with the option NOCACHE, this way you will not 'lose' any numbers in the sequence. You can read more about it in the manuals:

For future technical questions you may have, I encourage you to use the free DB2 Express-C forum:

Cheers, Raul.