DB2 V10 for z/OS

This section shows the performance changes that are applied to the version 10 of DB2 for z/OS.

Hash Access to Individual Table Rows

Hash access allows DB2 to directly access a single row in a table and avoid scanning the table or index for a matching equal predicate. This access path requires only one I/O operation to retrieve a row from a table. Hash access reduces CPU workload, but requires more disk space to function properly. Tables with hash access can require up to twice as much disk space as tables that use only indexes or table scans.

Improved LOB and XML Processing

DB2 version 10 for z/OS reduces LOB and XML materialization into buffers significantly for local and distributed processing. This improvement reduces virtual storage consumption and therefore improves system stability. Class 2 CPU time is also reduced. Whether materialization is reduced and by how much it is reduced depends on the type of operation and the quantity and size of the LOB and XML data.

LOB Data

You can use the INLINE_LOB_LENGTH subsystem parameter or the INLINE LENGTH clause with a CREATE TABLE or CREATE DISTINCT TYPE statement to specify that DB2 store a compatible portion of LOB data in the base table space with the other non-LOB data. You can expect processing for an inline CLOB to compare to that for a VARCHAR column in terms of CPU usage and elapsed time. Locking utilities and utility-related commands do not acquire or hold the UTSERIAL lock. The UTSERIAL lock is replaced by more granular locks to reduce contention.

Scalability

This release of DB2 for z/OS substantially increases the amount of virtual storage, while reducing storage monitoring. These improvements and others result in reduced cost, improved productivity, and easier management and growth of DB2.

Seamless Integration of XML Data and Relational Data

XML document storage

This release of DB2 for z/OS provides fully integrated storage of XML data in the DB2 database system, which lets your client applications access and manage the XML data by leveraging DB2 functionality. The XML column data type is provided for storing XML data in DB2 tables. Most SQL statements support the XML data type. As a result, you can perform many common database operations with XML data, such as creating tables with XML columns, adding XML columns to existing tables, creating indexes over XML columns, creating triggers on tables with XML columns, and inserting, updating, or deleting XML documents. A decomposition stored procedure is also provided. With this stored procedure, you can extract data items from an XML document and store those data items in columns of relational tables.

XML document retrieval

You can use SQL to retrieve entire documents from XML columns in a way that is similar to retrieving data from any other type of column. When you need to retrieve portions of documents, you can specify XPath expressions, through SQL with XML extensions (SQL/XML).

Features Considered for Temenos Transact

Support for multiple versions of XML documents

Version 10 supports multiple versions of an XML document, so that fetching a row in a work file with an XML column would successfully retrieve the version of the XML document that corresponds to the time when the row was inserted into the work file.

This feature is aimed at enhancing the parallel access of the table. After testing, it is identified that this feature is an overhead for scaling and hence not adapted.

Support for updating part of an XML document

Version 10 supports the ability to update part of an XML document. To update part of an XML document in an XML column, you can use the SQL UPDATE statement with the XMLMODIFY built-in scalar function.

The XMLMODIFY function specifies a basic updating expression that you can use to insert nodes, delete nodes, replace nodes, or replace the values of nodes in XML documents that are stored in XML columns.

Currently, it is identified that this feature is useful to Temenos Transact but is not implemented.

Support for DEFINE(NO) for LOB and XML table spaces

Version 10 supports deferring the physical creation of LOB and XML table spaces and their associated indexes to improve space management and application install time. This feature enables you to create LOB and XML table spaces and dependent index spaces with the DEFINE(NO) option. By specifying DEFINE(NO), the physical creation of underlying VSAM data sets is deferred until the first INSERT or LOAD operation. The undefined LOB or XML table spaces and dependent index spaces still have a DB2 catalog entry, but these objects are considered to be empty when accessed by SELECT or FETCH operations.

The initial time taken for creating the database consisting of 18000+ tables was huge. After adapting this approach, the database creation time was substantially reduced.

New IFCIDs in Version 10

This release of DB2 for z/OS includes new IFCIDs, these are used to monitor the database in more detail and administer them efficiently. All of the below are currently available to Temenos Transact usage. The same is tested in benchmarking activity. Along these same lines, there are few changes to the existing IFCIDs, which were adapted.