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:
FIRSTNAME LASTNAME
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:
a)
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.
b)
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.
c)
ROWNUM and ROWID are supported
d)
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
e)
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.
f)
There are many new functions for string, date manipulation and so on.
g)
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.
g)
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
h)
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.
i)
The Visual Studio add-ins have been enhanced
j)
There have been JDBC and SQLJ enhancements to support some of the things mentioned earlier
k)
The TRUNCATE SQL statement is now supported for fast deletes
l)
New data types such as NUMBER, VARCHAR2, TIMESTAMP with a precision are now supported
m)
Better support for Python-DB2 applications
n)
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.
More details are available in the Information Center.
Regards,
Leons Petrazickis
DB2 Express-C Community Team
