Adopting new DB2 Version 9.7 functionality in database applications and routines

After upgrading to DB2® Version 9.7, enhance the functionality and improve the performance of your database applications by adopting new DB2 Version 9.7 functionality.

For applications that access upgraded databases, perform any of the following steps to adopt the specified DB2 Version 9.7 functionality:

• Use optimization guidelines or view MQTs to improve MQT matching. Try this new functionality in a testing environment before you implement it in your production environment.
  • Use the new MQTENFORCE element for optimization guidelines to choose an MQT regardless of its cost estimate. See Optimization guidelines for MQT matching
  • Use a View MQT to create an MQT on views containing a complex query. Any queries on the view containing a complex query can be matched to the View MQT. In previous releases, a query on a view with a construct such as OUTER JOIN or UNION ALL could not be matched to an MQT.
  
  
• Enable statement concentrator to improve performance for dynamic SQL statements that are similar. The database server modifies these statements so that they share the same access plan. See Statement concentrator reduces compilation overhead.
  The following example shows how to enable statement concentrator at the data server level:

  UPDATE DB CFG FOR dbname 
         USING stmt_conc LITERALS

  After you enable statement concentrator, the following statements share the same access plan:

  SELECT FIRSTNME,LASTNAME FROM EMPLOYEE WHERE EMPNO='000020'
  and 
  SELECT FIRSTNME,LASTNAME FROM EMPLOYEE WHERE EMPNO='000070'

  You can also enable this functionality at the application level using the statementConcentrator Connection or DataSource property or the setDBStatementConcentrator method in JDBC. See DB2Connection interface for details.

  
  
• If the value of the pckcachesz database configuration parameter is close to the upper limit in pre-Version 97 releases running on 64-bit operating systems, tune this parameter or set to AUTOMATIC to enable self tuning. In Version 9.7 the upper limit for this parameter has been increased to 2 147 483 646.

  Having enough memory to cache the sections for static or dynamic SQL or XQuery statements might improve performance, especially when you issue the same statement multiple times from an application.

• If you want to increase concurrency for the cursor stability isolation level or you are migrating Oracle applications, enable currently committed behavior. To enable this behavior at the database level, perform the following steps:
  1. Set the cur_commit configuration parameter to ON by issuing the following statement:

     UPDATE DB CFG FOR database-name
            USING cur_commit ON

  2. Increase the locklist parameter to twice its value by issuing the following statement:

     UPDATE DB CFG FOR database-name
            USING locklist pre-upgrade-value*2

  3. For applications with high-volume transaction processing, you might need to increase log space or log buffer size.
  
  
• If your application requires a temporary table that remains defined after the end of a session or you are migrating Oracle applications, use created temporary tables (CGTTs) which differ from declared temporary tables (DGTTs) in that their definitions are stored in the system catalog and shared by multiple connections. The data stored in an instance of a CGTT is only available to the session that inserted it.

  See Creating and connecting to created temporary tables for details on how to use CGTTs.

  
  
• If you are migrating Oracle applications, use new compatible scalar functions such as DAYNAME, DECFLOAT_FORMAT, INITCAP, INSTR, MONTHNAME, LOCATE_IN_STRING, LPAD, RPAD, TO_CLOB, TO_NUMBER, or TO_TIMESTAMP to simplify the migration process.
  
• If you use the DECOMPOSE XML DOCUMENT command or call the system-defined procedures to decompose XML documents, use the new DECOMPOSE XML DOCUMENTS command to decompose multiple XML documents with one invocation only. This command does not require serialization of the XML documents and decomposes XML documents stored in a binary or XML columns.

  See Multiple XML document decomposition examples for details on how to use the DECOMPOSE XML DOCUMENTS command.