Check out the lab tests on the new OVRDBF feature that lets you override the REUSEDLT(*YES) attribute of a physical file or table and effectively use the behavior of REUSEDLT(*NO).
In a previous TechTip, I introduced a new Override with Database File (OVRDBF) command option that allows an application that requires high velocity inserts to temporarily override the REUSEDLT(*YES) attribute of a physical file or table and effectively use the behavior of REUSEDLT(*NO). In this second part, I will explain the results of lab-testing this new feature and some of the performance considerations when inserting rows into a table. I will also explain how you can obtain this new feature and start benefiting from it in your shop.
Testing the OVRDBF REUSEDLT(*NO) Option
Tests performed in the lab with a JDBC application clearly revealed the benefit of the OVRDBF REUSEDLT(*NO). The tests were executed in a medium and a large LPAR configuration (4 and 16 processors, respectively) with IBM i Version 6.1. Each LPAR had an average of 5 GB of memory and 16 disk arms per processor. The tests were also executed with i5/OS V5R4 with similar results.
Single-Job Writes
Figure 1 shows the execution time (in seconds) of a single job inserting 4 million rows into a table with 33 fields (character and numeric) and a record length of 295 bytes. The table had 4 million valid rows, 4 million deleted rows, and no indexes. By overriding the REUSEDLT(*YES) attribute of the table, the new rows were inserted at the end of the table, and the job benefited from DB2-level row blocking.
Figure 1: This test shows the execution time of a single job inserting 4 million rows into a table with 33 fields and a record length of 295 bytes. (Click images to enlarge.)
In Figure 2, the scenario is the same, but the table had 15 indexes and the SMP parallel degree was set to *MAX. In this case, the indexes on the table were maintained in parallel. The performance improvement by using the override was on average 70 percent in the LPAR with 4 processors and 85 percent in the LPAR with 16 processors. The tests were executed under laboratory conditions, and your performance will vary.
Figure 2: This test was similar to the test in Figure 1, but the table had 15 indexes and the SMP parallel degree was set to *MAX.
Multiple Jobs and Concurrent Writes
Figure 3 shows the average execution time (in seconds) for 2, 4, 8, and 16 concurrent jobs inserting a total of 18 million rows into the same table with deleted rows present and no indexes. By overriding the REUSEDLT(*YES) attribute of the table on each one of the concurrent jobs, the new rows were inserted at the end of the table and the jobs benefited from DB2-level row blocking.
Figure 3: In this test, multiple jobs insert 18 million rows into a table with deleted rows and no indexes.
From the graph, we can conclude the following:
- The performance improvement by using the override compared to REUSEDLT(*YES) was on average 40 percent on the LPAR with 4 processors and 70 percent on the LPAR with 16 processors.
- The performance by using the override with only 4 processors was similar or even better than REUSEDLT(*YES) with 16 processors. That is, this new option allows you to achieve the same or better performance with only 25 percent of the processors.
In Figure 4, the scenario is the same, but the table had 15 indexes and the SMP parallel degree was set to *MAX. In general, the more jobs we used to do the inserts concurrently, the more benefit was realized from the Enable Concurrent Write (ECW). However, the highest performance boost came as a result of DB2 maintaining the indexes in parallel when using the override.
Figure 4: This test is similar to the Figure 3 test, but the table had 15 indexes and the SMP parallel degree was set to *MAX.
From the graph we can conclude the following:
- The performance improvement by using the override compared to REUSEDLT(*YES) was on average 40 percent on the LPAR with 4 processors and 60 percent on the LPAR with 16 processors.
- On an LPAR with 4 processors and using REUSEDLT(*YES) only, you could achieve the best performance by multi-threading your application and increasing the number of processors by 400 percent or…
- You could achieve the same performance simply by overriding the REUSEDLT(*YES) attribute of the table and effectively using the behavior of REUSEDLT(*NO) without adding extra processors to the partition.
Considerations
The value of system-managed access-path protection (SMAPP) was set to *NONE during the lab tests. The purpose of SMAPP is to reduce the amount of time it takes to restart the system or vary on an independent disk pool, after an abnormal end. Take into consideration that SMAPP has some effect on processor performance. The system periodically examines access path exposure and estimates how long it would take to rebuild all the exposed access paths. If the rebuild time exceeds your target recovery times for access paths, the system selects additional access paths for protection. The lower the target recovery time you specify for access paths, the greater this effect may be. Additional tests were executed with the value of SMAPP set to 50 in the LPAR with 4 processors. The performance improvement by using the override compared to REUSEDLT(*YES) was on average 25 percent. However, the average execution time by using the override when the value of SMAPP on the system was set to 50 was 125 percent longer than using the override when the value of SMAPP was set to *NONE.
Obtaining and Using the New OVRDBF REUSEDLT(*NO) Option
The OVRDBF command has been available for a long time. The REUSEDLT option in the command is provided as a custom modification to i5/OS V5R4 and IBM i 6.1 and is available only through an IBM Lab Services contract on a billable basis. It is distributed via a PTF and is enabled via a software key provided by Lab Services. On request, Lab Services may provide a 30-day trial enablement.
For more information, go to the IBM Systems and Technology Group Lab Services and Training Web page (see the "Related Web Sites" section in this article) and click on the "Contact now" link on the right. Fill out the form so an Opportunity Manager may contact you. Make sure you type "OVRDBF REUSEDLT(*NO) option" in the "Short description" field of the form. After you enter an agreement with STG Lab Services, an email with the access code and the enablement library will be sent to you.
Once the new command option is enabled on your system, you can temporarily override the REUSEDLT(*YES) attribute of a physical file or table by running the following command:
OVRDBF FILE(filename) OVRSCOPE(*JOB) REUSEDLT(*NO)
Summary
If your application inserts large volumes of rows into DB2 for i tables, the new OVRDBF REUSEDLT(*NO) option can overcome the major drawbacks of using deleted rows by inserting new rows at the end of the table, thus allowing your application to benefit from DB2-level row blocking and Parallel Index Maintenance. Furthermore, with SMP and Parallel Index Maintenance, it may no longer be necessary to follow the recommendation of dropping the indexes, performing the inserts, and rebuilding the indexes upon completion of the process that inserts a very large number of rows.
Moreover, if the tables in your database experience a high degree of concurrent insert (or write) activity, your application could also benefit from Enable Concurrent Write (ECW) to ultimately deliver the best performance in your application or batch processing environment.
Related Web Sites
For information on how to contact IBM Systems and Technology Group Lab Services and Training, refer to its Web page:
http://www-03.ibm.com/systems/services/labservices
For information about blocked inserts at the application level and the algorithm used to reclaim the deleted data in a physical file or table, refer to the IBM Information Center:
http://www.ibm.com/eserver/iseries/infocenter
For information about Enable Concurrent Writes (ECW), refer to section 5.3.6 in the Striving for Optimal Journal Performance Redbook:
http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf
For information about DB2 Symmetric Multiprocessing and Parallel Index Maintenance, refer to the following white papers:
http://www-03.ibm.com/servers/enable/site/education/abstracts/4aea_abs.html
http://www-03.ibm.com/servers/enable/site/bi/strategy/index.html
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online