The "Holey Inserts" Function

The "Holey Inserts" Function
To optimize the performance of multiple jobs concurrently adding records to a physical file, you need to use a separate, output-only file description and specify that it’s to use sequential processing and output blocking. This will help the job run better, but I guarantee you’ll encounter processing bottlenecks when a lot of concurrent jobs (10 to 100 or more) are adding records to the same file in a multiple batch job environment. Once the number of jobs increases past a certain point, the CPU usage and throughput doesn’t increase as much as expected. In fact, at a certain number of jobs, the total CPU usage flattens out and each additional job causes all jobs to do less work.
This drop-off occurs due to SLIC database processing serialization occurring while the output record is being built. The database functions must validate that each new record can be added to the file. If the output blocking is high (as it should be — as close to 128 K as you can get) and many jobs are adding records at once (as there should be), the validation processing becomes a bottleneck.
Fortunately, a database function in V4R4 called holey inserts lets multiple concurrent jobs add records concurrently without running into the contention caused by serialization. The formal name for holey inserts is ENCWT (Enable Concurrent Writes), but holey inserts is more descriptive of the function’s methodology (Figure 2).


How to Enable "Holey Inserts"
To optimize database add-record processing on V4R4 or later, you do three things:
1. Call an OS/400 database program to enable holey inserts.
2. IPL your system to turn it on.
3. Use a separate, output-only file description and specify SEQONLY *YES and output blocking.
The green-screen command-line call sequence to enable holey inserts is CALL QDBENCWT '1'; you’d enter CALL QDBENCWT '0' to disable it. Note that calling this database program enables concurrent adds for all database files in the system. A database physical file can benefit from the holey inserts algorithm only if it was created with (or modified using CHGPF to have) the REUSEDLT (Reuse Deleted Records) attribute specified as *YES.
The primary downside of holey inserts is that if you’re journaling, some of the journal records’ ordinal numbers (e.g., count) can be out of sequence. This shouldn’t be a problem with high-availability software.
Holey inserts lets multiple jobs concurrently add records to the same database file even if another job is already adding to the file. The database calculates the new record’s insertion point for the subsequent jobs and lets them continue processing. If some records fail the validation tests, the database won’t let them be put into the file. In this case, the database inserts a deleted record (logically, a hole in the file) in place of the rejected record — thus, the name holey inserts.
If the holey inserts feature isn’t turned on, no deleted record is inserted in place of the rejected record. As a result, any subsequent records added to the file are moved up one record position in the file. Because of the "fuzziness" of the next job’s beginning insertion point, the *SLIC Data Base functions prevent subsequent jobs from doing their add processing until the current job finishes its add processing.
The advantage of using holey inserts is that it lets multiple jobs concurrently use the CPU. This may not gain much performance on a single CPU system because the validation processing function could be highly CPU intensive and, consequently, there may not be much CPU left for other jobs during validation. However, if you have an n-way processor, it lets the other concurrent jobs adding to the file use the other CPUs and moves the data into the file much faster.