25
Thu, Apr
1 New Articles
  1. You are here:  
  2. Home
  3. Analytics & Cognitive
  4. DB2
  5. TechTip: Improve Performance When Writing to DB2 for i Tables, Part II

TechTip: Improve Performance When Writing to DB2 for i Tables, Part II

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

 

090409EchevesteOVRDBFFigure1 

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.

 

090409EchevesteOVRDBFFigure2

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. 

 

090409EchevesteOVRDBFFigure3 

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.

 

090409EchevesteOVRDBFFigure4 

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

 

 

 

Fernando Echeveste

Fernando Echeveste is an advisory software engineer for IBM in Rochester, Minnesota. He's a member of the IBM DB2 for i Lab Services consulting team. He has been in the computing field since the early 1990s.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: