Bookmark and Share
RSS

Recent Posts

DB2 12 Catalog Availability Improvements

April 25, 2017

During the early releases of DB2, enterprises (specifically DBAs) would restrict dynamic SQL from running in their production OLTP environments. This choice stemmed primarily from the likelihood of ad hoc OLAP queries adversely impacting the performance of OLTP applications. Of course, over the next 20-plus years, this issue has been largely mitigated by the widespread adoption of dynamic SQL. Dynamic SQL continues to be a focus of DB2 development, and DB2 12 features an important enhancement in the area of catalog availability.

When an application issues a dynamic SQL statement, it must be prepared for execution. During the prepare process, in addition to acquiring read claims on some catalog table spaces and related indexes, DB2 acquires a DBD lock on the catalog. In previous DB2 releases, the transaction released the DBD lock and the read claims when the application issued a COMMIT. However, if the application went for an extended period without issuing a COMMIT statement, the execution of CATMAINT would be blocked. (CATMAINT, as you likely know, provides for catalog maintenance during upgrades.) In addition, the lack of COMMIT impacted running online REORGs as well as executing DDL (changes made to create, alter and drop DB2 objects).

DB2 12 manages the DBD locks as well as read claims against catalog objects, bringing greater availability. Locks and claims are freed once PREPARE statement execution is completed. This makes it easier to run CATMAINT, DDL and online REORGs of catalog objects.

Posted April 25, 2017 | Permalink

comments powered by Disqus