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.
Before you begin
You must upgrade your DB2 server
to DB2 Version 9.7.
Procedure
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:
- Set the cur_commit configuration
parameter to ON by issuing the following statement:
UPDATE DB CFG FOR database-name
USING cur_commit ON
- 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
- 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.
What to do next
If you upgraded from DB2 Version
9.1 or earlier, adopt functionality introduced in DB2 Version 9.5 in your database applications
and routines. See Enabling new DB2 Version
9.5 functionality in database applications and routines in
the Migration Guide (Version 9.5) for details.