Db2 & Db2 on Cloud Technical Advocate Newsletter June 2019 | |||
Announcement - IBM Db2 Version 11.5 is Now Available!! We're excited to announce the General Availability of Db2 Version 11.5! Exciting new features and improvements include: External Table Support including Object Store support; SQL Compatibility including CREATE/DROP TABLE .. IF EXISTS; Oracle Compatibility; New Monitoring Metrics including aggregation across super classes, and data on SQL statement failures; Client Enhancements; Support for Compiled SQL PL scalar functions in DPF; Support for 4K sector sizes on disk drives; Built-in Spatial Support; Automatic Collection of Column Group Statistics; WLM Enhancements including simplified threshold setup and dropping of service classes; ETL optimizations for BLU Including including Vectorized Insert/Update, Optimized Batch Insert; BLU Compression Enhancements including Automatic Recompression and Vectorized Dictionary Create; LOB support with Columnar Tables; Performance Enhancements for Columnar Queries including boosts for sorts and correlated subqueries; pureScale enhancements including table free space management, performance with Range Partitioned tables, and cross-member currently committed semantics; Enhanced Security for pureScale; and more....Also, now available as a Technical Preview: Advanced Log Space Management; Faster DB Startup; Machine Learning Optimizer; Block Chain Federation Wrapper; Schema Level Authorization; Db2 Augmented Data Explorer; Replication capability to support Columnar tables. What's New in Version 11.5: https://ibm.co/2RDfri9 Db2 11.5 Download Page: https://ibm.co/2RC6Dca Db2 11.5 on DockerHub: https://hub.docker.com/r/ Db2 11.5 Client Drivers Download Page: https://www-01.ibm.com/ Announcement - IBM Db2 Event Store 2.0 is Now Available! Db2 Event Store 2.0 is a major upgrade from previous versions bringing exciting new features in support of the Fast Data needs of our clients. Highlights of what's new: Powered by the IBM Common SQL Engine adds the industry's most sophisticated SQL engine to Fast Data workloads, including common SQL across the entire Db2 Family, and Improved query performance. Queries up to 50x faster with improved query optimization and parallel query execution. Multi-tiered caching of synopsis and data pages; Enhanced Time series and Geospatial support, Rich functional support through IBM Research library; Easier to use, Integrated Backup and Restore, Enhanced problem determination tooling, JBDC/OBDC Standard Connectivity. Overview of Db2 Event Store 2.0: https://www.ibm.com/support/ Webinar - Db2 11.5! Breaking News from IBM Toronto Lab Kelly Rodger, IBM Senior Manager, walks us through the new and exciting release of Db2 for LUW! Db2 V11.5 has lots of new features and capabilities, license changes, and more. Watch the replay: https://www.dbisoftware.com/ New Offering - Db2 Warehouse on Cloud Flex Plans The set of available Db2 Warehouse on Cloud plans is expanding, offering you new choices of cloud infrastructure and plan size. First, Db2 Warehouse on Cloud can now be deployed on the Amazon Web Services public cloud. There are two Flex offerings to choose from: Second, we've introduced a new Db2 Warehouse on Cloud Flex One plan on IBM Cloud. Like Flex and Flex Performance, it delivers independent scaling of storage and compute and self-service backup and restore, but in a smaller starter configuration that you can scale as your needs grow. Configurations begin at 40GB disk storage and 6 cores. For more information, see:https://www.ibm.com/cloud/ Webinar - Db2 Problem Determination - a 3 HOUR Class! He's Back! Pavel Sustr, IBM Senior Manager, returns to give us a THREE HOUR class on Db2 LUW Problem Determination. Wow! Watch the replay: https://www.dbisoftware.com/ Announcement - IBM Db2 on Cloud for AWS IBM has announced the launch of a Technical Preview for customers looking to run a fully managed Db2 database on Amazon AWS. It will be available for deployment in early July, but is available for purchase now via the HDMP Monthly Subscription. This allows customers to launch a fully managed Db2 Advanced (formerly AESE) database totally hassle-free. Patching, security and backups are all managed for you. https://developer.ibm.com/ New Offering - IBM Db2 Developer-C (no license fee) Now Available on the Amazon marketplace The IBM Db2 AMI (Amazon Machine Image) for Developer-C is now publicly listed and available on the AWS marketplace. The AMI allows users to launch and connect to the readily made available database and will be able to leverage Db2 developer-C functionalities to the core. The AMI is based on Db2 Developer-C v11.1.4.4 and is AWS free-tier eligible. In other words, users with an AWS trial account can launch the AMI on EC2 micro instances for free. For non-free tier configurations, users will pay for AWS resources only. https://aws.amazon.com/ Announcement - IBM Db2 on Cloud Introduces Point-in-Time Restore and Improves Cross-Regional Backups IBM Db2 on Cloud now makes it simple to restore to an exact point in time, via self-service. Working with backups is a critical feature to ensure you can rapidly get your database back to how you’d like it. Capabilities include: Standard daily encrypted backups with log shipping; Cloud Object Storage; Time travel queries and Db2 tools. For details, see: https://www.ibm.com/cloud/ Conference & Summits - International Db2 Users Group Conferences (North America and Europe) Over the years, IDUG has become known for hosting dynamic conferences packed with the cutting-edge content you need. This year will be no exception. Be sure to make your plans to attend. With great sessions, keynote speakers, workshops, and more, you won't want to miss this event.IDUG Seminar in Sao Paulo, Brazil, August 20, 2019: https://www.idug.org/p/cm/ld/ IDUG Seminar in Mexico City, Mexico, August 22, 2019: https://www.idug.org/p/cm/ld/ IDUG Conference Australia, Sept 12-13, 2019 in Melbourne, and Sept 17-18 in Canberra: https://www.idug.org/au IDUG & IBM Data Tech Summit, Toronto, ON, Canada, Sept 23-24, 2019: https://www.idug.org/ IDUG & IBM Data Tech Summit, San Jose, CA, USA, Oct 2-4 2019: https://www.idug.org/p/cm/ld/ IDUG Conference Europe, Rotterdam, Netherlands, Oct 20-24,:https://www.idug.org/p/ Blog - Increasing data accessibility through lock avoidance via across-member Currently Committed semantics in Db2 pureScale In Db2 pureScale environments Version 11.1 and prior, support concurrently committed semantics was limited, a CS isolation row reader could bypass an in-flight row updater and retrieve the currently committed version of a record from the recovery log stream only when the row reader and row updater (lock holder) resided on the same member. Starting in Version 11.5, a CS isolation row reader in a Db2 pureScale environment is capable of retrieving the currently committed version of a record was when either the row reader and row updater (lock holder) resided on the same member, or on different members.http://thinkingdb2.blogspot. Blog - Installing Db2 the Easy Way: Docker Ian Bjorhovde -- As DBAs, one of the tasks that we do on a fairly regular basis is install new Db2 code on the database server. Although it has become routine for me, installing new code on a server can be surprisingly complex. Docker provides a simple alternative.https://www.idug.org/p/bl/et/ Survey - Lab Advocate Engagement Survey If you haven't already, please help us improve the lab advocate program by completing the lab advocate engagement survey. http://bit.ly/2019_Db2_lab_ ______________________________ Roadmaps - Db2 Development Roadmaps are Now Accessible to Everyone Curious about the feature and function committed to upcoming versions of Db2? You can now explore live development roadmaps for Db2 and the IBM Analytics family of products. This content is live and subject to change.https://ibm-analytics- Db2 Community - Follow the Db2 Developer and Administrator Community Share. Solve. Do More. https://developer.ibm.com/ End of Support (EOS) - For Db2 Version 10.5 is April 30, 2020 Effective April 30, 2020, IBM will withdraw support for Db2 Version 10.5. Extended support will be available beyond this date. https://ibm.co/2NCGTNi Follow-us! - Stay up to date on the latest news from the Db2 team IBM Support Community: (http://ow.ly/rPsM30fHnwI) IBM Db2 Twitter: (https://twitter.com/IBM_ developerWorks Answers (forum): https://developer.ibm.com/ Thoughts from Db2 Support (blog): https://www.ibm.com/ Db2 Technical Advocacy Wiki: https://www.ibm.com/ | |||
Thursday 27 June 2019
Db2 and Db2-on-Cloud - Technical Advocate Newsletter – June 2019
Monday 3 June 2019
Increasing data accessibility through lock avoidance via across-member Currently Committed semantics in Db2 pureScale
- David Sciaraffa, Software Engineering Manager – IBM Db2
In typical application environments, many concurrently running applications will attempt to read, insert, or update database data simultaneously. Access to database data must be isolated from potential concurrency conflicts, so that operations are predictable with and without the presence of other concurrently executing applications. “Isolation” is a capstone of A.C.I.D. compliant database systems.
The degree to which an application can see the data changes of other concurrently running applications is determined by its isolation level. Some isolation levels (like ‘repeatable read’ - RR) are very restrictive, others (like ‘uncommitted read’ – UR) are very liberal, while the most common (‘cursor stability’ – CS) strikes a good balance for most applications.
Db2 achieves isolation through multiple methodologies whose complexity is mostly outside the scope of this blog. The most basic methodology comes in the form of database locks. The read, insert, or update of a data row by an application may result in the database manager acquiring and/or holding a lock on the row, depending on the configured isolation level.
Focusing on the most common type of isolation (‘cursor stability’ – CS), when reading a row of data an application using this isolation level desires to read the most recently committed version of the row (ignoring any in-flight changes on the row by other uncommitted transactions). An application reading data with CS isolation may obtain a row lock on the row before processing it to ensure that only committed data is passed back to the user. The row lock is released after reading the row data.
In early versions of Db2, prior to the implementation of currently committed semantics, a row reader using CS isolation must wait for an in-flight application updating a row to commit, before acquiring the lock on the row in order to process it. This could be a significant bottleneck in high concurrency workloads.
With the implementation of currently committed semantics, a row reader using CS isolation is able to read the currently committed version of a row, bypassing any in-flight updates on that row.
Consider the following diagram depicting a row read application in lock-wait due to a concurrent row updater, with no currently committed semantics enabled:
With currently committed semantics enabled, a row reader application can now access the currently committed version of the row, bypassing the lock held by the row updater. This is achieved by retrieving the currently committed row directly from the recovery log-stream. Information about where to find the currently committed version of the row within the recovery log stream is maintained with the lock-request-control-block (LRB) for the row, as depicted:
In Db2 pureScale environments Version 11.1 and prior, support concurrently committed semantics was limited, a CS isolation row reader could bypass an in-flight row updater and retrieve the currently committed version of a record from the recovery log stream only when the row reader and row updater (lock holder) resided on the same member. When the row updater was not on the same member as the row reader, the row reader would default to a lock wait condition, as depicted here:
The enhancement is enabled by default starting in Version 11.5.
I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.
In typical application environments, many concurrently running applications will attempt to read, insert, or update database data simultaneously. Access to database data must be isolated from potential concurrency conflicts, so that operations are predictable with and without the presence of other concurrently executing applications. “Isolation” is a capstone of A.C.I.D. compliant database systems.
The degree to which an application can see the data changes of other concurrently running applications is determined by its isolation level. Some isolation levels (like ‘repeatable read’ - RR) are very restrictive, others (like ‘uncommitted read’ – UR) are very liberal, while the most common (‘cursor stability’ – CS) strikes a good balance for most applications.
Db2 achieves isolation through multiple methodologies whose complexity is mostly outside the scope of this blog. The most basic methodology comes in the form of database locks. The read, insert, or update of a data row by an application may result in the database manager acquiring and/or holding a lock on the row, depending on the configured isolation level.
Focusing on the most common type of isolation (‘cursor stability’ – CS), when reading a row of data an application using this isolation level desires to read the most recently committed version of the row (ignoring any in-flight changes on the row by other uncommitted transactions). An application reading data with CS isolation may obtain a row lock on the row before processing it to ensure that only committed data is passed back to the user. The row lock is released after reading the row data.
In early versions of Db2, prior to the implementation of currently committed semantics, a row reader using CS isolation must wait for an in-flight application updating a row to commit, before acquiring the lock on the row in order to process it. This could be a significant bottleneck in high concurrency workloads.
With the implementation of currently committed semantics, a row reader using CS isolation is able to read the currently committed version of a row, bypassing any in-flight updates on that row.
Consider the following diagram depicting a row read application in lock-wait due to a concurrent row updater, with no currently committed semantics enabled:
[1] A user (App1) requests to update a row on table Tab1.
[2] App1 is granted a row lock on this row in exclusive (X) mode.
[3] App1 updates the row in the table, and a log-record is written into the recovery log stream (this log-record contains the original version of the row). App1 has not yet committed its unit-of-work, so this row change is still in-flight.
[4] Another user (App2) requests to read this same row (CS isolation is inferred, but not depicted).
[5] App2 must acquire a row lock on this row to ensure to read the currently committed version of the row. It cannot acquire the row lock because an uncommitted in-flight transaction. App2 must wait until App1 committed and release the row lock, before proceeding.
With currently committed semantics enabled, a row reader application can now access the currently committed version of the row, bypassing the lock held by the row updater. This is achieved by retrieving the currently committed row directly from the recovery log-stream. Information about where to find the currently committed version of the row within the recovery log stream is maintained with the lock-request-control-block (LRB) for the row, as depicted:
[1] A user (App1) requests to update a row on table Tab1.
[2] App1 is granted a row lock on this row in exclusive (X) mode.
[3] App1 updates the row in the table, and a log-record is written into the recovery log stream (this log-record contains the original version of the row). A pointer to the original version of the row in the recovery log stream is stored within the row lock information structure (LRB). App1 has not yet committed its unit-of-work, so this row change is still in-flight.
[4] Another user (App2) requests to read this same row (CS isolation is inferred, but not depicted).
[5] App2 must acquire a row lock on this row to essure to read the currently committed version of the row. It cannot acquire the row lock because the uncommitted in-flight transaction (App1).
[6] App2 uses the pointer to the original (currently committed) version of the row within the row lock information to retrieve the row from the recovery log stream.
In Db2 pureScale environments Version 11.1 and prior, support concurrently committed semantics was limited, a CS isolation row reader could bypass an in-flight row updater and retrieve the currently committed version of a record from the recovery log stream only when the row reader and row updater (lock holder) resided on the same member. When the row updater was not on the same member as the row reader, the row reader would default to a lock wait condition, as depicted here:
[1] A user (App1) on pureScale member1 requests to update a row on table Tab1.Starting in Version 11.5, a CS isolation row reader in a Db2 pureScale environment is capable of retrieving the currently committed version of a record was when either the row reader and row updater (lock holder) resided on the same member, or on different members, as depicted here:
[2] App1 is granted a row lock on this row in exclusive (X) mode. This lock is effective in both the member local lock manager, as well as the global lock manager.
[3] App1 updates the row in the table, and a log-record is written into the member-local recovery log stream (this log-record contains the original version of the row). A pointer to the original version of the row in the recovery log stream is stored within the row lock information structure (LRB). App1 has not yet committed its unit-of-work, so this row change is still in-flight.
[4] Another user (App2) on pureScale member2 requests to read this same row (CS isolation is inferred, but not depicted).
[5] App2 must acquire a row lock on this row to ensure to read the currently committed version of the row. It cannot acquire the row lock because an uncommitted in-flight transaction.
[6] App2 cannot utilize the pointer to the original (currently committed) version of the row within the row lock information to retrieve the row, because this information only exists on member1. App2 must wait until App1 commits and releases the row lock, before proceeding.
[1] A user (App1) on pureScale member1 requests to update a row on table Tab1.
[2] App1 is granted a row lock on this row in exclusive (X) mode. This lock is effective in both the member local lock manager, as well as the global lock manager.
[3] App1 updates the row in the table, and a log-record is written into the member-local recovery log stream (this log-record contains the original version of the row). A pointer to the original version of the row in the recovery log stream is stored within the row lock information structure (LRB). App1 has not yet committed its unit-of-work, so this row change is still inflight.
[4] Another user (App2) on pureScale member2 requests to read this same row (CS isolation is inferred, but not depicted).
[5] App2 must acquire a row lock on this row, in order to ensure to read the currently committed version of the row. It cannot acquire the row lock because an uncommitted in-flight transaction.
[6] App2 cannot utilize the pointer to the original (currently) version of the row within the row lock information to retrieve the row, because this information only exists on member1.
[7] App2 communicates with Member1 (via an RPC request) to retrieve the original (currently committed) version of the row using the row lock information on member1. The row is returned back to member2.
The enhancement is enabled by default starting in Version 11.5.
I hope this information is useful. If you have any thoughts or questions, feel free to submit a comment below.
Subscribe to:
Posts (Atom)