DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
Changes in application development support, new functionality, discontinued functionality, and deprecated functionality might impact your database applications, scripts and tools after you upgrade them to DB2® Version 9.7.
A complete list of supported operating systems is available at Installation requirements for DB2 database products. If your current version of operating system is unsupported, you must upgrade it before you install DB2 Version 9.7.
In UNIX operating systems, only 64-bit kernels are supported. Your 32-bit instances are upgraded to DB2 Version 9.7 64-bit instances.
If you upgrade to the latest version of your operating system or you install a 64-bit kernel, rebuild all database applications and external routines after you upgrade to DB2 Version 9.7 so that they use the new runtime libraries in the operating system.
Development software support has also changed. To improve performance and avoid technical support issues, rebuild your applications with the latest version of your development software. Review the changes in support for development software requirements
The IBM® Data Server Driver for JDBC and SQLJ includes the db2jcc.jar class file for applications that use JDBC 3.0 methods or earlier and the db2jcc4.jar class file for applications that use JDBC 4.0 or later methods, and JDBC 3.0 or earlier methods. The JDBC 4.0 java.sql.DatabaseMetaData.getDriverName method returns the IBM Data Server Driver for JDBC and SQLJ name instead of the IBM DB2 JDBC Universal Driver Architecture name. To manage the behavioral differences between the driver that supports JDBC 4.0 or later in Version 9.7 and previous releases of this driver, upgrade Java™ applications that use IBM Data Server Driver for JDBC and SQLJ. See Upgrading Java applications that use IBM Data Server Driver for JDBC and SQLJ for details.
The DB2 JDBC Type 2 driver has been deprecated since DB2 Version 9.1. You should modify your Java applications and external routines to use the IBM Data Server Driver for JDBC and SQLJ with type 2 connections. To manage the behavioral differences between the driver that supports JDBC 3.0 in Version 9.7 and the DB2 JDBC Type 2 driver, upgrade your Java applications that use DB2 JDBC Type 2 driver. See Upgrading Java applications that use DB2 JDBC Type 2 driver for details.
See Java software support for DB2 products for details about the versions of IBM Data Server Driver for JDBC and SQLJ that are delivered with every DB2 database product version and fix packs.
CLI applications, DB2 CLP interface, and .Net Data Provider clients support Secure Sockets Layer (SSL). The IBM Global Security Kit (GSKit) provides encryption services for the Secure Sockets Layer (SSL) support. Refer to Configuring Secure Sockets Layer (SSL) support in non-Java DB2 clients for details about how to enable SSL in a client including how to download and install the GSKit.
For upgraded databases, the INLINE LENGTH default value is the maximum size of the LOB descriptor for the corresponding LOB column. LOB data is inlined when the length of the LOB data plus the overhead is less than the INLINE LENGTH value. Therefore, if the LOB data length plus the overhead is less than the LOB descriptor size for the LOB column, the LOB data is implicitly inlined in a table row after the database upgrade.
If you make extensive use of LOBs in your database applications, you can increase performance for SQL statements that access the LOB data by increasing INLINE LENGTH to an adequate value because no additional I/O is required to access the LOB data when it is inlined in a table row. Refer to Adopting new DB2 Version 9.7 functionality in database applications and routines for details.
After upgrading to DB2 Version 9.7, the XQuery string data type is used for values of elements or attributes that are not cast in an XQuery expression. Type annotations in existing XML documents that you validated are no longer used to do implicit casting. If you validate new XML documents to insert them in an XML data type column, these XML documents are stored without type annotations.
For XQuery expressions that depend on data types based on type annotations from validated XML documents, you need to explicitly cast elements and attributes in all XQuery expressions from validated XML documents. Without explicit type casting, XQuery expressions that used implicit casting or casting to other types will fail after the upgrade.
xquery
let $x1 := db2-fn:sqlquery("SELECT xmlcol FROM xq.sch1
WHERE DOCID=1")/prodA/sale/xs:integer(price),
$x2 := db2-fn:sqlquery("SELECT xmlcol FROM xq.sch2
WHERE DOCID=1")//xs:integer(clearance)
return (<b1>{ $x1 > $x2 }</b1>)xquery
for $i in
(db2-fn:sqlquery("SELECT xmlcol FROM xq.basicSchemaTypes
WHERE DOCID=1")//date)[1]
return if (xs:date($i) eq xs:date('1967-08-13'))
then $i else (<notOK1/>)xquery
for $datetime in
(db2-fn:xmlcolumn('XQ.BASICSCHEMATYPES.XMLCOL')//dateTime)[1]
cast as xs:dateTime
return $datetime cast as xs:gDayReview the changes to SQL statements in DB2 Version 9.7 to determine if you have applications and scripts that are impacted by these changes and how to manage these changes. Introduction of new functionality such as an untyped NULL keyword in expressions and a DEFAULT keyword in procedure parameters requires that you modify your applications to adapt to these changes.
After database upgrade to DB2 Version 9.7, the system catalog views under the SYSCAT schema remain compatible with catalog views that you defined in DB2 Version 9.1. However, there are new columns, increases in column length, or columns with changed data types in some of the system catalog views.
SQL administrative routines include changes such as new parameters and new columns returned. Also, some routines are replaced with system-defined administrative routines and views. In addition, all of the system-defined table functions with names that start with SNAPSHOT_ have been deprecated since DB2 Version 9.1.
For new databases created in DB2 Version 9.7, the cur_commit configuration parameter is set to ON so that currently committed semantics is enabled on cursor stability scans. Under the new currently committed semantics, only committed data is returned, as was the case previously with the cursor isolation level, but now a read operation does not wait for a write operation to release row locks. A returned result set operating under cursor stability isolation level might be different than in previous releases. Refer to Adopting new Version 9.7 functionality in upgraded databases for details on enabling currently committed behavior.
The optimizer now calculates execution plans for star join queries using different cardinality estimates than in previous releases. With these new estimates, the optimizer might choose a better execution plan for certain queries to improve their performance and stability.
The MQT matching process now considers additional situations that can result in the optimizer choosing a different execution plan for queries that match an MQT. In upgraded databases, you could experience improvements in queries matching GROUP BY MQTs that use the DISTINCT clause and queries that use DATE predicates right after the upgrade without any actions on your part. However, exploiting these features further and exploiting other improvements such as use of view MQTs or optimization guidelines to force the optimizer to choose a specific MQT, require implementation after the upgrade. Refer to Adopting new DB2 Version 9.7 functionality in database applications and routines for details on how to use these new features.
The optimizer now pushes down relational predicates (for filters and XPath extractions) into XQuery query blocks. Therefore, enabling early data filtering and better potential index usage. In partitioned database environments, early data filtering potentially reduces the amount of data transfer between partitions. Consequently, you will notice new query access paths, improved performance, and reduced memory usage for combined SQL/XQuery queries. See Compiler rewrite example: Predicate pushdown for combined SQL/XQuery statements
Scan sharing is introduced in DB2 Version 9.7 to allow a scan to read the buffer pool pages of another scan. This behavior increases concurrency, reduces query response times, and increases system throughput without requiring hardware upgrades. The SQL compiler determines eligibility for scan sharing automatically. At run time, an eligible scan may or may not participate in sharing, based on considerations in effect that were not known at compile time. See Scan sharing
The optimizer now calculates more realistic CPU costs of passing data from one database agent to another in partitioned database environments. This will enable the optimizer to consistently select plans that minimize CPU costs associated with data transmission, thus improving the performance of some SQL statements. Refer to Optimizer's cost model improved for queries in partitioned database environment for details.
Rebind any statically bound packages after upgrade to take advantage of optimizer improvements.
When you upgrade a database, all packages for user applications and routines are placed into an invalid state. Packages are also placed into an invalid state if they depend on database objects that you dropped, such as tables, views, aliases, indexes, triggers, referential constraints, and table check constraints. If you drop a UDF, your package is placed into an inoperative state.
Although invalid packages are automatically rebound by the database manager the first time that an application needs to access them, rebind your database packages to control when rebinding occurs and resolve any possible issues. See the Optimizer enhancements section for additional advantages of manually rebinding your database packages.
On Linux and UNIX operating systems excluding Linux on x86, DB2 Version 9.7 requires 64-bit kernels and supports only 64-bit instances. Therefore, when you upgrade to DB2 Version 9.7, your DB2 UDB Version 8 32-bit instances are upgraded to 64-bit instances. See 32-bit and 64-bit DB2 server support changes for details.
| Application | Operating system | Upgrading from | Embedded shared library paths |
|---|---|---|---|
| 32-bit | 32-bit or 64-bit (Linux and UNIX operating systems) | 32-bit instance | $INSTHOME/sqllib/lib1 |
| 64-bit | 64-bit (Linux and UNIX operating systems) | 64-bit instance | $INSTHOME/sqllib/lib2 |
| 32-bit | Windows | 32-bit instance | DB2PATH\lib |
| 32-bit | Windows | 64-bit instance | DB2PATH\lib\Win32 |
| 64-bit | Windows | 64-bit instance | DB2PATH\lib |
| Environment variable and Operating system | Application | Variable value |
|---|---|---|
|
32-bit | INSTHOME/sqllib/lib321 |
|
64-bit | INSTHOME/sqllib/lib64 |
| LIB (Windows operating systems) | 32-bit application running on a 64-bit instance | DB2PATH\lib\Win322 |
| LIB (Windows operating systems) | 32-bit or 64-bit | DB2PATH\lib |
These environment variables specify additional locations where DB2 shared libraries can be loaded at application runtime, allowing your application to run after you upgrade to DB2 Version 9.7 if you did not specify the correct shared library path.
In general, the DB2 server behavior is compatible between releases. However, there are changes in behavior to support new functionality or improve the performance of existing functionality. Review DB2 server behavior changes to determine the impact of these behavior changes on your applications.
After upgrading your DB2 server, compare your registry variable and configuration parameter values to your values before upgrade, and change any values according to the needs of your applications.
Your applications can use pre-Version 9.7 clients to access databases in DB2 Version 9.7 servers. However, your applications are restricted to the functionality available for that client. Review Upgrade essentials for clients to learn details about client connectivity and to identify changes in support that can impact your DB2 clients.
Starting with DB2 Version 9.7, WORF has been discontinued. You can replace WORF with a new feature within IBM Data Studio that allows you to create Web services without writing document access definition extension (DADX) files. See Upgrading Web applications that were developed for the Web Object Runtime Framework (WORF) for details on how to upgrade you existing Web applications.
DB2 EAS has been discontinued and the DB2WebServices application is not available in DB2 Version 9.7. Use Data Studio to re-create your Web services and redeploy your Web services after upgrading to DB2 Version 9.7. If you have Web applications that were developed for WORF, see the previous section for details about how to migrate these Web applications.
The DB2 administration tools have been deprecated and the DB2 Web Tools have been discontinued since DB2 Version 9.5. Alternatively, install Data Studio and use the Data Source Explorer to perform database administration tasks. See Database administration from the Data Source Explorer for details. Also, visit the Data Studio product page at http://www.ibm.com/software/data/studio/ for details about product offerings and downloads.