What are the benefits of new features of DB2 V9.7?

We all know that the DB2 V9.7 database has introduced a series of new features. The emergence of these new features makes IT easier for customers to save IT costs. Specific features include compression enhancement, pureXML enhancement, usability enhancement, monitoring enhancement, and workload management enhancement.

Improves security, performance, application development, SQL pl language support, SQL compatibility, high availability, backup, log, elasticity, and recovery.

This article focuses on introducing the new feature "currently implemented". This feature significantly reduces lock waits and the frequency of deadlocks at the isolation level of cursor stability. By using the "currently implemented" CS isolation level, you can effectively improve the database performance in a high-throughput transaction processing environment.

Starting from DB2 V9.7, DB2 allows scanning to avoid the use of Row-level locks when it is able to explicitly obtain data or the "implemented" version of the page. If you cannot know whether the index or row record is in real time, scan uses the traditional locking method. Uncommitted inserted rows are directly identified in row-level locks, allowing "currently implemented" scanning to ignore or skip this row.

Introduction

Since IBM DB2 V9.7, DB2 has introduced a series of new features that allow customers to easily save IT costs. Specifically, compression is enhanced by compressing and supporting XML data, temporary tables, indexes, and data replication source tables, which further reduces storage requirements and improves I/O efficiency, improves fast access to disk data.) pureXML is enhanced by the pureXML function, so that XML data can be deployed and analyzed in the data warehouse;

Currently, XML can be used in table partitions, MDC tables, temporary tables, user functions, and partitioned database environments.) ease of use is enhanced, which reduces the total cost of ownership (TCO, reduces the impact of executing system management tasks on the system, and extends the autonomy features released in earlier versions). Enhanced monitoring allows you to monitor the DB2 environment more flexibly and at a higher granularity) server Load balancer enhances the priority of ongoing activities, integrates with the Linux Server Load balancer WLM, and improves the I/O priority control in the Service Class Buffer Pool.

New thresholds such as AGGSQLTEMPSPACE, CPUTIME, CPUTIMEINSC, SQLROWSREAD, and SQLROWSREADINSC are added, which improves the time-based threshold ACTIVITYTOTALTIME and CONNECTIONIDLETIME granularity. Enhanced security can better protect sensitive data) performance Improvement: by introducing "currently implemented", scanning and sharing, creating partition indexes on table partitions, and storing embedded LOB files in tables at the stability isolation level of the cursor access speed, increased data concurrency;

The DB2 optimizer enhances DB2 performance through access plan reuse and Statement concentrator support) and application development improves the management of database objects by using alter table to rename column names, by introducing many new functions such as TRUNCATE statements, creating temporary tables, and public synonyms, SQL programming and stored procedure development are simplified and improved) SQL pl language support and enhanced SQL compatibility can be easily migrated from ORACLE Applications to the DB2 environment) and improve availability, backup, log, elasticity, and recovery.

The focus of this article is to introduce the new feature currently committed semantics, which will be referred to as CC in the future. The notable feature of this new feature is the Cursor stability in the Cursor stability and CS in the future) at the isolation level, the lock wait and the occurrence frequency of deadlocks can be significantly reduced.

In versions earlier than DB2 V9.7, when we use the default isolation level of the cursor stability isolation level), we generally only lock the row currently referenced by the cursor that the transaction declares and opens, that is to say, the transaction generally only locks the current row, and does not lock records other than the current row; the lock obtained by the transaction remains valid until the cursor is relocated or the transaction ends. If the cursor is relocated, the lock on the original row is released and the lock on the row currently referenced by the cursor is obtained.

If a transaction modifies any row it retrieves, other transactions cannot update or delete the row before the transaction ends, even if the cursor is no longer in the updated or deleted row. NOTE: If only the row is retrieved, the current row is usually locked. If the row to be retrieved is updated or deleted, the modified row is also locked, even if the pointer moves to another row, the row is locked. The lock on the modified row will prevent other applications from reading the row until the lock on the modified row is removed and the modified row is implemented.) Other applications can read the row.

First, let's take a look at the blocking of read and write operations in ORACLE at the Snapshot isolation level, as shown in table 1, when a read operation encounters a read operation, a read operation encounters a write operation, and a write operation encounters a read operation, the write operation is blocked:

Blocking at the ORACLE Snapshot isolation level

First appeared workload \ then appeared workload read workload write workload

Read workload is not blocked)

Write workload is not blocked) is blocked)

In versions earlier than DB2 V9.7, the read and write operations are blocked when the cursor stability isolation level is used, as shown in table 2, when a READ operation encounters a READ operation, it will not be blocked; When a READ operation encounters a write operation, it may be blocked for read only read operations will not block the write operation, for update, the write operation will be blocked due to the U Lock on the row); when the write operation encounters a read operation, it will be blocked, when a write operation encounters a write operation, the operation is blocked.

Blocking with CS isolation level in versions earlier than DB2 V9.7

First appeared workload \ then appeared workload read workload write workload

Read workload is not blocked) Possible

Write workload is blocked)

In DB2 V9.7, the read and write operations when the "currently implemented" cursor stability isolation level is enabled are blocked, as shown in table 3, it can be seen that there are significant improvements compared with earlier versions of DB2. When read Operations encounter read operations, read Operations encounter write operations, and write operations encounter read operations, no congestion occurs, the write operation is blocked only when it encounters a write operation:

Blocking of CS isolation levels enabled for "currently implemented" in DB2 V9.7

First appeared workload \ then appeared workload read workload write workload

Read workload is not blocked)

Write workload is not blocked) is blocked)

In DB2 V9.7, when the new feature "currently implemented" is enabled at the cursor stability isolation level, a read operation does not need to wait for the change to be implemented before returning the value, instead, it directly returns the value before the row is not changed, that is, the result value that has been implemented currently, ignoring any possible unimplemented operations ). However, it should be noted that there are exceptions in updatable cursors: If a row is updated based on its own content, the results of the current implementation cannot be returned immediately.

When row-level locks are used at the stability isolation level of the cursor but "currently implemented" is not enabled), lock timeout and deadlocks may occur, especially those applications that are not specifically designed to prevent these issues. Some high-throughput database applications cannot tolerate lock waits during transaction processing, and some applications cannot tolerate uncommitted data, but still need not block read operation transactions.

Enabling "currently implemented" at the CS isolation level can effectively improve the database performance in a high-throughput transaction processing environment. In these environments, too many lock waits cannot be tolerated. by enabling the CS isolation level of "currently implemented", you can effectively reduce timeout and deadlocks. When "currently implemented" is enabled, only the implemented data will be returned. As in the previous example, the read operation does not need to wait for the update operation to release the row-Level Lock, the read operation will directly return the data of the "currently executed" version, that is, the value before the first write operation ).

Since "currently implemented" is a new feature of DB2 V9.7, many customers do not know how to use it. This article will focus on the new features and related concepts of "currently implemented" in DB2 V9.7, it also helps you understand and improve it based on actual examples.

Current Working Principle of Currently Committed has been implemented.

Starting from DB2 V9.7, DB2 uses full lock to avoid full lock avoidance techniques) technology. When it is clear that it can obtain data or the "implemented" version of the page, scan is allowed to avoid the use of Row-level locks. If you cannot know whether the index or row record has been stored in real time, the scan will be changed to the traditional locking method. DB2 adds a new feedback mechanism in row-level locks to identify which "log records" describe the first modification of the row from the first modification of the row to obtain the data value before the modification, that is, the implemented version of the row). When a lock conflict occurs, the lock manager will use this feedback mechanism to directly return these log record numbers.

This feedback result will be used for a currently implemented scan to be used from the log buffer or activity log files) access the "currently implemented" version of the row, that is, the result value before the first update ). Uncommitted inserted rows are directly identified in row-level locks, allowing "currently implemented" scanning to ignore or skip this row.

As shown in figure 1, the emp table has five records. The insert operations on the second and fourth rows have been completed, and the log records describing the insert operations have been stored in the database archived using TSM, for details, see the red section in the lower-right corner of "1"; the third row is being updated and has not been implemented). The log records for this row are in the active log file on the disk, this log record describes the first change of the third line, as shown in the yellow section on the right;

The fifth row is being inserted but has not yet been inserted). The log records that record this row are in the active log file on the disk. This log records the first insertion of the fifth row, 1. the yellow part on the right is shown. The first line is being deleted and has not been implemented.) The log records recorded for this row are in the log buffer zone, this log records the first change in the first line,

For details, see the green section on the top right of Figure 1. The Locklist section in the middle of Figure 1 indicates the lock manager. In the lock manager, the first, third, and fifth rows are in the X lock status, the log records corresponding to these rows are also in the lock manager. This is the new feedback mechanism for Row-level locks in DB2 V9.7 to identify which "log records" describe the first modification of the row.

When a lock conflict occurs, the lock manager will use this feedback mechanism to directly return these log record numbers, as shown in the Black Arrow. When other applications attempt to read the first or third row, the "implemented" version data of the row is directly returned from the log buffer or log file. The row is ignored or skipped for the unsubmitted fifth line.