The persistent struggle to improve application and database performance involves constantly analyzing different aspects of your environment, workload, and configuration. There are many different aspects that interact within your environment that impact overall performance. Database and application I/O performance is one of most important aspects to analyze because it can have a tremendous positive and negative impact on the elapsed time your application requires and the CPU it consumes.
The following three items are the primary aspects to analyze when tuning your database and application I/O performance.
- Document daily DB2 I/O activity of each system and database object. Processing workloads change based on the business demand, promotions, and seasonal cycles. Document the I/O activity of each of the DB2 database objects to understand the different object’s relationships with the business processing. Current figures are vital to show improvement progress with any tuning efforts. Having historical DB2 I/O activity figures related to the database objects will help you understand the current I/O capacity requirements and help you project them into the future.
- Determine DB2 I/O device contention peaks. Getting storage device information is very difficult as storage automation is enabled across many z/OS and LUW environments. If possible document how many physical devices are in each of your DB2 database storage group definitions. Also, research whether these devices are set up using any RAID technology and, if so, what level of RAID technology is being used. Once these storage device details are established, drill down into each of the storage devices to understand what DB2 system and database objects are on each device. Cross referencing the device with the DB2 system and database object’s I/O activity information exposes where the device contention exists in your environment.
- Prioritize and separate DB2 I/O characteristics across applications object types. Next research and understand the different Read and Write requirements of the environment workload. Spreading, evenly distributing, or prioritizing the DB2 I/O activity across the storage devices is the first step toward I/O optimization. Configuring any storage device cache capabilities to leverage and minimize I/Os against the most active DB2 system and application database items can help reduce the I/O rate. Separating random versus sequential I/O access patterns can help leverage the cache and sometimes improve I/O rates.
These are only three ways to begin improving the I/O performance for your DB2 z/OS and DB2 for LUW environments. Gather the DB2 system and database object lists and begin getting your I/O statistics reported on a regular basis to improve your I/O performance and your DB2 application performance.
____________________________________________________
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, and President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.
_______________________________________________________
Sign up for the IDUG DB2 Technical Conference in Phoenix, Arizona this May 12-16th.
- Sign up for dinner with me during the IDUG Dine Around Dinner Thursday night at IDUG.
- Also plan on attending my presentation 1221-F04 “Big Data Disaster Recovery Performance,” Tuesday 4:30 at the IDUG conference in Phoenix.
- The IDUG European conference is still accepting presentation abstracts to be potentially picked for the IDUG EU conference in Prague, Czech Republic November 9-14th.
For more details on any of these items go to www.idug.org.
Leave a Reply