Improved concurrency in Db2 12 for ALTER TABLE with cached dynamic SQL
Paul_McWilliams 110000JT36 Visits (2912)
With all the focus on Db2 12 continuous delivery—with the associated function levels and appl
PH00637 makes an internal change to Db2 that affects how DDL (specifically ALTER TABLE statements) interacts with cached dynamic SQL. This change is aimed at increasing the chances that DDL will succeed when cached dynamic SQL statements are executing at the same time that the DDL is executed. This was a very targeted change that was aimed at resolving various failures related to SQLCODE -904, with REASON 00E70081, TYPE 1202 received on the DDL statement.
This APAR was a Db2 12 Continuous Delivery deliverable, but was not tied to a function level because dual-pathing the code (to support something that works one way before a Function Level is activated, and another way after a function level is activated, requires that we dual path the code) was not feasible in this performance sensitive path that involves things like caching, locking, and dependency management. Also, this code does not require activation on all members of a Parallel Sysplex at one time. To get full benefit, the code must be applied to all members. However, because each member has its own global statement cache, the new code in PH00637 can co-exist with the older code, and invalidation of the cache occurs independently on each member.
While the changes this APAR has introduced will not help all DDL statements, the improvement for ALTER TABLE ADD COLUMN is dramatic. Here is an example of the before-and-after results we saw in the lab:
The table shows that before PH00637, we had a significant percentage of DDL failures when we tried to execute it at the same time applications were using SQL against those same objects. Then, after PH00637 was applied, we were able complete the DDL the first time, with no failures.
One thing to note here is that after PH00637 is applied, we did see fewer SQL statements complete during the testing period (10 minutes was used in all cases). This is expected. When the DDL succeeds, SQL is removed from the statement cache and PREPARE is serialized until after the DDL completes. Once the DDL is complete, the PREPARE will execute (this will be a FULL PREPARE) and applications can continue processing.
We encourage you to take a look at the full PH00637 closing text to understand more about how this APAR might affect you.
For more about other Db2 12 APARs that are not part of function levels, see Enhancements in recent Db2 12 APARs.
Chris Crone is a Distinguished Engineer for Db2 for z/OS development, and Randy Nakagawa is a Software Developer for Db2 for z/OS.