Troubleshooting
Problem
This document discusses performance considerations for highly concurrent database insert activity.
Resolving The Problem
The performance of an application written to allow a high level of concurrent inserts depends on multiple factors, including disk performance, number of access paths to maintain, and so on. This document describes several features and considerations to achieve the best performance:
The articles found at the following URLs provide detailed information:
The articles found at the following URLs provide detailed information:
An excerpt follows of what the two articles above cover:
| o | Application-Level Blocked INSERT: Using a blocked INSERT statement, you can insert multiple rows into a table with a single INSERT statement. |
| o | DB2-Level Row Blocking: To improve performance, the SQL runtime attempts to retrieve and insert rows from the database manager a block at a time whenever possible. |
| o | Parallel Index Maintenance using Db2 Symmetric Multiprocessing (SMP): With SMP enabled, blocked INSERT or WRITE operations can benefit, because the database engine maintains each index in parallel. |
| o | Enable Concurrent Write (ECW, Holey Inserts): This function overcomes the contention caused by database processing serialization when multiple concurrent jobs add rows to the same database table. |
In addition to the two articles, additional notes and considerations follow:
| o | Methods to have the application performed blocked inserts are as follows: - Use SEQONLY on the OVRDBF command, OVRDBF FILE(file_name) OVRSCOPE(*JOB) SEQONLY(*YES nnnnnn) See Blocking, Sequential Only, and the Effect for additional information. Additional coding may be required,depending on the HLL being used. - Use SQL blocked INSERT |
| o | Minimize the number of access paths over the table targeted for the insert operations. Fewer access paths to maintain means less work. |
| o | Use parallel index maintenance |
| Preallocate the file to avoid delays while the system automatically extends the file. Most database files and tables are created with ALLOCATE(*NO) and allow the system to allocate the storage space as needed. Delays may be seen in a highly concurrent environment when the system needs to extend the storage space. These delays, although typically fairly short and rare, can be avoided by manually allocating the storage at some time when performance is not critical. The command to use is as follows: CHGPF FILE(LIB/FILE) SIZE(row-to-allocate) ALLOCATE(*YES) A RGZPFM or clear of the file is required for this change to take effect. Otherwise, the space will be allocated the next time the system needs to extend the file. |
|
| o | Enable Concurrent Write support. - Striving for Optimal Journal Performance has a good explanation of concurrent write support. - Issue this command from the command line with no parameters to see the current setting: CALL QDBENCWT - Issue this command from the command line to enable concurrent write support on the next IPL: CALL QDBENCWT '1' - Issue this command from the command line to disable concurrent write support on the next IPL: CALL QDBENCWT '0' You can only benefit from this database function if your database table allows the reuse of deleted records. Refer to the CRTPF and CHGPF commands for the parameter REUSEDLT(*YES)" |
| o | If the file reuses deleted records, you should consider using OVRDBF REUSEDLT(*NO). |
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
N1010952
Was this topic helpful?
Document Information
Modified date:
28 April 2022
UID
nas8N1010952