There are some very nice much needed availability features in Db2 13. In this article, I would like to concentrate on two areas of enhancement. Firstly, the ability to safely remove existing active log data datasets, especially in a data sharing environment from the configuration in the Boot Strap Data Set (BSDS). Secondly, a set of new features to reduce the impact and increase the likelihood of success of SQL DDL operations in a 24×7 always on production environment where there are no quiet periods.
Let me introduce and discuss the online removal of an active log dataset. Let me start by discussing the motivation and use case. There are a number of reasons why an installation would like to remove a pair of active log datasets:
- Implement DFSMS dataset encryption of active log datasets
- Periodic change of encryption key used for DFSMS dataset encryption of active log datasets
- Replace older smaller size active log datasets (and add in new larger size active log datasets)
- Number of active logs has reached the maximum limit of 93 pairs and active logs must be reallocated with larger size.
So why is this new feature required in Db2? Several installations have tried outside the control of Db2 to remove (and delete) an active log dataset using their available tools. There is an added complication in a data sharing environment that although an active log dataset is owned by a specific Db2 member and only that member can write to that dataset, other members of data sharing group may have the dataset opened for read access (e.g., replication data capture). An active log dataset should not be removed until the subject log has been archived. Therefore, there is a risk of operational errors taking place with resultant disruption and sadly even possible data loss.
Db2 13 solves this issue by providing an enhancement to perform online removal of active log datasets from the configuration in the BSDS without taking an outage and without incurring operational risk. There is a new –SET LOG REMOVELOG command which will result in one of the following conditions:
- Successful removal of active log dataset from the BSDS configuration, if the subject active log dataset is not part of any log read or write process
- Active log dataset put into REMOVAL PENDING status, if it is currently in use for write processing and/or there is an log read process
- Command fails as REMOVAL PROHOBITED, if the subject active log is the next to be used after the next log switch.
Let me say a few more words about REMOVAL PENDING status. Firstly, the condition cannot be turned off or cancelled. Secondly, the subject active log dataset will not be used for further log read or write processing. Thirdly, the condition can only be cleared by reissuing the -SET LOG REMOVELOG command and for it be successful.
You can use the Db2 -DISPLAY LOG with new DETAIL option to monitor the status of an active log dataset. Another useful command is: D GRS,RES=(*,dsname,*). This command can be used to determine if the subject active log dataset is in use.
Switching topics, Db2 13 now provides user controls to improve the ability for SQL DDL operations to succeed and/or reduce application service impact:
- New special register CURRENT LOCK TIMEOUT
- New global variable DFEADLOCK_RESOLUTION_PRIORITY
- Enhancement to system monitor profiles, for both local and remote applications, to set the above mentioned special register and/or global variable and to be able to temporarily switch running application processes from RELEASE(DEALLOCATE) to RELEASE(COMMIT) run time behaviour.
So, what is the problem to be solved? There are long standing issues with SQL DDL operations breaking in and succeeding in a true 24 x7 continuous availability environment where there are no quiet periods. Firstly, SQL DDL operations not succeeding (failing) after a long wait period. Secondly, and worse still, the impact of new incoming transactions queuing up behind the SQL DDL operations which may take a long time break in to succeed or ultimately fail with timeout. An aggravating factor is that there is a single system wide value for IRLM Resource Timeout Interval (ZPARM IRLMRWT) and often this value is set very high (e.g., 60 seconds) to avoid “tsunami” of transaction timeouts during unplanned HyperSwap events. Existing operational circumventions are not effective all the time in allowing SQL DDL to break in and succeed. One of the techniques is to switch SQL packages from RELEASE(DEALOCATE) to RELEASE(COMMIT) run time behaviour. This involves switching off CICS and IMS/TM thread reuse, and HP-DBATs for remote connections.
To be fair there are several possible reasons for a failing SQL DDL operation, but a common cause is that the SQL DDL operation is involved in deadlock with application processing and Db2 choose the SQL DDL work to be the deadlock victim.
Db2 13 now provides solutions to limit the time period when new incoming transactions are queuing behind SQL DDL operations waiting to break in and to reduce the possibility of SQL DDL operations becoming deadlock victims. Firstly, the new CURRENT LOCK TIMEOUT special register which can have a value between 1– 32767 seconds and which overrides the system value (IRLMRWT). A low value will limit the time period when new incoming transactions are queuing behind SQL DDL operations. So for typical SQL DDL operations, CURRENT LOCK TIMEOUT would need to be set to 5 seconds or less to avoid disruption to application transaction processing. From a practical view point, a minimum value of only about 5 seconds can be achieved in a data sharing environment because timeout is a function of deadlock processing and multiple deadlock cycles are involved. Secondly, the new DEADLOCK_RESOLUTION_PRIORITY global variable which can have a value between 0 – 255 and which represents the relative weighting value and influences which process is likely to be the deadlock victim. The higher the value for running the SQL DDL workload under, the less likely that lock requests that are requested by SQL DDL operations are denied when the SQL DDL operation is involved in a deadlock situation. So for SQL DDL operations, the recommendation is to run with a very high value for DEADLOCK_RESOLUTION_PRIORITY. Thirdly, enhancements to Db2 system monitor profiles to be able to set the CURRENT LOCK TIMEOUT, DEADLOCK_RESOLUTION_PRIORITY, and to set the RELEASE_PACKAGE keyword to switch executing application processes from RELEASE(DEALLOCATE) to RELEASE(COMMIT) at the next commit. Most importantly, system monitor profiles have been enhanced so that these new capabilities can be used by local (CICS, batch, etc) applications as well as for remote connections using High Performance DBATs.
That is all for now. Thank you for reading this article. Please look out for other articles about Db2 13 for z/OS.