Saturday 31 March 2018

Faster rollback processing of very large transactions in Db2 v11.1.3.3

- David Sciaraffa, Software Development Manager – IBM Db2


In database management systems, it is common for periodic batch workloads to perform large quantities of insert, update, or delete operations. These are often referred to as the ‘nightly batch job’. These large batch processing operations can put pressure on database management resources.

In Db2, to satisfy the Durability property of ACID compliance, all insert/update/delete (i/u/d) of data rows are also recorded into the database recovery log files. This log file data may be required later to recover from system failure, interrupt, or for replication purposes. For large batch operations, log file data often spans many log files.

Diagram 1) depicts how large batch operations can generate log records that span multiple log files:

image


Note: Db2 recovery log files exploit Direct I/O and bypass the file system cache in order to optimize write performance. This is imperative given the performance sensitivity of writing log file data, and useful since there is minimal read activity of the recovery log files during database runtime.

As a common best practice, large batch operations should be divided into smaller batches, with explicit COMMIT operations performed after each small batch, to avoid filling the Db2 active log space, and to lessen the amount of work to undo or redo in case of an interrupt or failure. Nevertheless, large batch operations often exist, and can be interrupted, either intentionally by the user or unintentionally as the result of being forced off the database by workload management enforcement settings.

When large batch operations are interrupted or fail, potentially large quantities of data row changes must be rolled back or undone. This requires reading through all the transaction log data that was recorded into the recovery log files since the beginning of the batch job.  Since Db2 recovery log files use Direct I/O during database runtime (not crash-recovery), Db2 does not benefit from file system prefetching and caching when reading back these log-records.

Starting in Db2 Version 11.1.3.3, there is a significant performance improvement for rollback processing during database runtime for large units of work, through the autonomous use of filesystem caching (buffered I/O) when reading recovery log file data.

By autonomously re-opening log files with file system caching (buffered I/O), reading log data from these files benefits from the buffering of the file system cache.

Diagram 2) depicts how rollback processing of large units of work may benefit from buffered I/O when reading transaction log data back, in order to undo data row changes:

image


Internal tests showed significant improvements (up to a 3x!) in rollback processing time for very large transactions.

image


This improvement is available by default starting in v11.1.3.3, no configuration or registry settings need to be enabled.



I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.

Monday 26 March 2018

Db2 and Db2-on-Cloud - Technical Advocate Newsletter – March 2018

image_thumb2


Event - Relive 'Think 2018' Conference
The visionary speakers and experts at Think 2018 have shared their solutions for some of the world’s most daunting challenges. And now you can access their technical prowess and profound insights anytime.
https://ibm.co/2GrrGLE


Announcement - Db2 11.1 Mod Pack 3 is Available for Download
Summary of the modification pack: https://ibm.co/2tSYbby
List of high impact & pervasive (HIPER) fixes (APARs): https://ibm.co/2ze6H7L
List of all fixes (APARs) included in this fixpack: http://www-01.ibm.com/support/docview.wss?uid=swg21995889
Fixpack download page: https://ibm.co/2IUa895


Announcement - Introducing IBM Cloud Private for Data
IBM Cloud Private for Data is an engineered solution for data science, data engineering and application building, with no assembly required. It provides seamless access to data across on-premises and all clouds; a cloud-native data architecture, behind the firewall; and data ingestion rates of up to 250 billion events per day. What Kubernetes solved for application developers (dependency management, portability, etc), IBM Cloud Private for Data will solve for data developers and speed the journey to AI. For details: https://ibm.co/2pG6VSz


Anniversary -
Celebrating 25 years of Db2!
March 2018 is the 25th anniversary of the Db2 relational database product on Linux UNIX and Windows! Few tech products in the world grow to this kind of success. The world runs on the back of Db2 — stock markets, investment banks, hospitals, insurance firms, pension funds, manufacturers, retailers, railways, shipping and logistics companies, airlines, schools, and governments. A talented team of business leaders, strategists, engineers, researchers and innovators, support staff, and sales professionals has made it happen. Today we celebrate and tip our hats to a remarkable technology and the team that created it.
https://ibm.co/2DWmMAL


Webinar -
Favorite Tuning Tips & Tricks for Db2
SELECTIVITY, OPTGUIDELINES, Runstats, Indexes, Bufferpool Design, REORGs, monitoring advice, and more!! No wonder Joe Geller won Best User Speaker at IDUG Lisbon 2017! Joe's presentation is full of terrific advice and tips! Watch and learn...
http://bit.ly/2GpHpL9


Walkthrough - Db2 on Cloud - Free Tier
IBM Db2 on Cloud is built for robust performance and provides a high availability option with a 99.99% up-time SLA. Scale up or down as needed and leverage rolling security updates for peace of mind. Db2 on Clound is a fully managed service; scale CPU up or down and pay for what you use on a daily basis. You can also scale CPU and storage independently. Enables you to move to the cloud with Db2 and Oracle PL/SQL compatibility. Offers support for numerous data connectivity methods and types. Helps you comply with data protection laws. Includes at-rest database encryption and SSL connections. High availability plans provide seamless rolling security updates.
https://www.ibm.com/cloud/db2-on-cloud


Conference - 2018 North America - International Db2 User Group Tech Conference
The premier North America Db2 Conference will take place on April 29 - May 3, 2018 in Philadelphia, Pennsylvania. Make your plans to attend and experience the latest in Db2 technologies, networking opportunities and the technical content you need to be successful.
The 2018 event offers new educational opportunities and more training than ever before! Attendees will experience: Five days of education sessions; Half and full-day workshops; More than 100 one-hour technical sessions; Three expert panels on z/OS, LUW & Application Development.
And much more!
http://www.idug.org/na2018

__________________________________________________________________________


Support Community - Follow the IBM Support Community

Check out how IBM is improving your Db2 support experience!
http://ow.ly/rPsM30fHnwI
Wiki - Db2 Development-Advocate Wiki

The Db2 Development Advocate Wiki is a publically accessible wiki with information about the development advocacy program, and forums for Q&A about the program. You'll also find a list of historic newsletter content.
https://www.ibm.com/developerworks/community/groups/community/Db2_Technical_Advocacy_Program


Follow-us! - Stay up to date on the latest news from the Db2 team
Db2 Support Twitter channel: @AskIBMAnalytics (https://twitter.com/AskIBMAnalytics)
Facebook page: @DB2Community (https://www.facebook.com/DB2community/)
AskIBMAnalytics Facebook channel: @AskIBMAnalytics (https://www.facebook.com/AskIBMAnalytics/)
developerWorks Answers (forum): https://developer.ibm.com/answers/topics/
Thoughts from Db2 Support (blog): https://www.ibm.com/developerworks/community/blogs/IMSupport?lang=en
Db2 Technical Advocacy Wiki: https://www.ibm.com/developerworks/community/groups/community/Db2_Technical_Advocacy_Program

Translate