DB2 Version 5 includes the following performance, capacity, and memory enhancements:
The performance has been improved for queries that use columns which are key columns of different indexes over the same table. DB2 uses dynamic bitmap technology to efficiently combine multiple indexes.
The performance of queries involving "star joins" has been improved. Star queries are characterized as multi-way joins between several small dimension tables and a large fact table. DB2's new star join algorithm exploits dynamic bitmaps to join a large fact table with a series of relatively small dimension tables, minimizing data I/O.
The aim of the Global SQL Cache is to minimize the amount of catalog access required for sections of static SQL statements and to maximize the sharing of sections for dynamic DML statements by eliminating many of the previous restrictions. This is done by establishing a global cache shared by all agents connected to the same database or partition of a database (in the case of DB2 Universal Database Extended Enterprise Edition), in which sections for both static and dynamic SQL statements will be placed. This global cache acts as a public repository, or library, for different sections being used on the database at any given time.
Enhancements have been made to speed up restart which means that database crash recovery is now faster. This enhancement also applies to rollforward recovery.
Two enhancements have been made to the buffer manager: multiple buffer pools and extended storage.
You can now create multiple buffer pools of various sizes (number of pages) and assign table spaces to them using the CREATE BUFFERPOOL SQL statement. This provides the database administrator greater control of the data in memory.
In addition, support now exists for very large physical memory (64-bit memory support). DB2 exploits 64-bit systems and 32-bit systems capable of supporting greater than 4 GB of real memory.
See the Administration Guide for details.
In addition to SMP exploitation of load, LOAD performance for DEL and ASC data has been improved for the simple column types char and int. Other performance improvements have been made for other data formats and column types.
LOAD now has a new MODIFIED BY option called FASTPARSE which provides further performance improvements. See the Command Reference for details.
The amount of traffic between a DB2 client and a DB2 server (including host databases through DB2 Connect) has been reduced. Use of the new deferred prepare on/off option provides a performance enhancement when accessing DB2 Universal Databases as well as DRDA databases running DB2 Connect to minimize network communication. It can benefit both DB2 Universal Database and DB2 Connect.
Deferred prepare provides a performance enhancement when accessing DB2 and DRDA databases by combining the SQL PREPARE statement flow with the associated OPEN, DESCRIBE, or EXECUTE statement flow to minimize the inter-process or network flow.
When deferred prepare is enabled, DB2 defers sending out SQL PREPARE statements until the associated OPEN, DESCRIBE, or EXECUTE statement is issued by the application. A PREPARE that is not eligible for deferral can be sent immediately, and have its cursor opened at the same time. PREPARE statements that normally cannot be deferred or pre-OPEN can also be optimized if the appropriate option is set in the SET CLIENT API or precompiler option.