DB2 11 for z/OS: The Unsung Heroes

Introduction

DB2 11 for z/OS became generally available in October 2013, and a huge amount of information is available on the many new features and functions (I’ll be writing a round-up blog entry later in January to provide a handy reference to these). While some of the DB2 11 enhancements have grabbed a lot of limelight (step forward – again – CPU savings, transparent archiving, extended log addressing) there are several others that could have a major impact on many DB2 customers but have had far fewer column inches devoted to them.

This article aims to redress the balance and highlight a few of the lesser-known DB2 11 features that could still end up making their way into your personal list of top 10 favourites. They may not be as glamorous as their better-known counterparts, but like the supporting cast of any good movie they could end up making a big difference to the overall result.

PKREL_COMMIT

DB2 10 for z/OS introduced High Performance DBATs, allowing distributed threads to use RELEASE(DEALLOCATE) behaviour in order to reduce the CPU costs associated with constantly freeing and reallocating resources for high-volume transactions. Recognising that this performance boost would come at the cost of reduced concurrency, IBM provided the -MODIFY DDF PKGREL(COMMIT) command to allow the DBA to temporarily override the RELEASE setting for each package and enforce RELEASE(COMMIT) behaviour so that BIND/DDL/utility processes could be run. Once the maintenance operation was complete, the -MODIFY DDF PKGREL(BNDOPT) command could be issued to instruct DB2 to once again honour the RELEASE setting specified for each package. In this way, DBAs and system administrators could easily “break in” to high-volume distributed workloads in order to complete essential maintenance/housekeeping activities.

This works well for distributed workloads, but high-volume OLTP applications running locally (such as traditional CICS and IMS transactions) still had to choose between higher performance / lower concurrency with RELEASE(DEALLOCATE), or lower performance and higher concurrency with RELEASE(COMMIT). DB2 11 introduces a new subsystem parameter called PKGREL _COMMIT which aims to redress this problem by allowing BIND/DDL/utility processes to acquire the necessary locks even if a high-volume RELEASE(DEALLOCATE) transaction is running. If PKGREL_COMMIT is set to YES (the DB2 11 default) DB2 will detect when a BIND/DDL/utility process is waiting on a lock for a RELEASE(DEALLOCATE) package and will implicitly de-allocate/release that package at the next COMMIT or ROLLBACK.

Like the high performance DBAT solution introduced in DB2 10, PKGREL_COMMIT makes it possible to enjoy the best of both worlds, allowing high-volume local OLTP packages to be bound with RELEASE(DEALLOCATE) for performance while also allowing critical housekeeping/maintenance activities to break in when necessary. This should allow much wider use of RELEASE(DEALLOCATE) for high-volume transactions, providing a quick and easy performance boost with no application changes needed. Of all the DB2 11 enhancements, one ESP customer that I interviewed named this feature as the one likely to have the most immediate and beneficial impact within their production environment.

Application Compatibility

One of the major issues facing DBAs and systems programmers trying to upgrade their DB2 systems to a newer release is dealing with "incompatible changes:" changes in DB2's behaviour which could impact the behaviour of applications. Examples include new DB2 reserved words, changes to SQL return codes, deprecated functions and differences in the way DB2 formats returned data. Previous releases of DB2 required extensive work to identify which applications needed to be changed, conduct the necessary remedial work and then test the amended applications. As every single application had to be compatible with the new release before New Function Mode could be enabled, long delays could occur with just one delinquent application stopping all of the others taking advantage of the new functionality available in NFM (see Figure 1 below).

Figure 1 - Handling Incompatible Change in Previous DB2 Releases

DB2 11 includes two enhancements to address this issue. First, new instrumentation data allows DBAs and systems programmers to more easily identify specific packages that contain incompatible SQL or XML statements while in Conversion Mode. IFCID 366 was first introduced in the maintenance stream to assist with some DB2 10 incompatibilities, but has been significantly extended in DB2 11 to report on all instances of incompatible SQL and XML when each statement is executed.  IFCID 376 is new in DB2 11 and provides a rolled-up equivalent, cutting just one record for each unique incompatible SQL or XML statement that is executed.

The new instrumentation obviously helps when it comes to identifying which applications need remedial work done, but what about the problems caused by having to actually undertake all of that work before NFM? Unfortunately there's no silver bullet that will allow you to completely avoid making the changes, but DB2 11 provides a massive help in terms of when those changes have to be implemented. The new APPLCOMPAT BIND option (or the equivalent APPLICATION COMPATIBILITY special register for dynamic SQL) allows the DBA/developer to specify which set of release behaviours DB2 should use for each package. In DB2 11 Conversion Mode, this has to be set (explicitly for each package or implicitly via a ZPARM default) to V10R1, indicating that DB2 should treat the package as if it is running under DB2 10 (as you would hope in Conversion Mode). The interesting bit comes on entry to New Function Mode, as all of those packages will continue to run in DB2 10 compatibility mode until they are explicitly rebound with APPLCOMPAT(V11R1) or the default DSNZPARM is changed. This makes it possible to move to New Function Mode much more rapidly, then enable new release functionality on a package-by-package basis when any necessary remedial work (as identified by IFCID 366/376) has been completed. Such remedial work can be scheduled at a convenient time for each application (e.g. as part of a planned maintenance release) without impacting the overall implementation timescales for the new release.

Figure 2 below shows how the new feature can be used at each stage of the upgrade process.

Figure 2 - Release Compatibility throughout the Upgrade Process

DB2 11 only supports compatibility for DB2 10 applications (i.e. -1 release) but future releases will support two previous releases (so DB2 11+1 will support both DB2 10 and DB2 11 applications). This means that you can defer changes for a while but if you leave them too long you'll be back to the old problem of having to wait for applications to make themselves compatible before moving to the new release. Used appropriately and with some discipline, the application compatibility feature has the potential to greatly reduce the amount of time taken to reach NFM in a given release, allowing new features to be adopted more rapidly so the business can begin to benefit from them.

RACF Exit Enhancements

Many DB2 sites take advantage of the Access Control Authorisation exit (DSNX@XAC) to interface to an external security product such as RACF, rather than use DB2's built-in security model. This has several advantages, including the ability for RACF administrators to handle DB2 resources in the same way as they do for others, wildcarding of authorities, persistence of authorisations when dropping and recreating objects and several others.

Unfortunately, use of the external security exit can also cause some complications. There are several specific areas where the behaviour of the external exit differs from the way DB2 internal security is handled (e.g. the OWNER keyword is not honoured when performing BIND/REBIND/autobind, RACF uses the invoker for authorisation checking instead). In addition, DB2 caches the results it obtains from the exit for performance reasons and this can lead to inconsistent behaviour (e.g. if a change is made within RACF but the previous authorities are still cached within DB2).

In order to address these issues a few important enhancements have been made to the external security exit within DB2 11. Owner authorisation checking is now supported for BIND/REBIND/autobind, DYNAMICRULES(BIND) behaviour is consistent with internal DB2 authorisation, and a mechanism has been put in place to allow DB2 to know when an authorisation change has been made within RACF (via an appropriate ENF signal) and refresh the relevant cache so that it remains consistent. New DSNZPARMs (AUTHEXIT_CHECK and AUTHEXIT_CACHEREFRESH) govern whether DB2 uses the old or the new behaviours.

Many DB2 sites are unable to take advantage of external security for compliance reasons, and in some cases this is due to the limitations outlined above. The DB2 11 enhancements will go a long way to addressing these issues and allowing a new generation of DBAs and systems programmers to be free of the need to manage internal DB2 authorisation.

64-Bit Java Stored Procedures

The popularity of Java as a mainstream enterprise application development language has continued to increase in recent years, and many DB2 customers use it as their strategic option. DB2’s Java stored procedure support has steadily evolved and improved, but even DB2 10 had some fairly major limitations: the Java Virtual Machines (JVMs) instantiated in the stored procedure address space were limited to 31-bit addressability, with each stored procedure TCB requiring its own JVM. The considerable memory footprint of each of these TCBs meant that most sites had to limit NUMTCB to 3-5 for Java stored procedure address spaces, resulting in many address spaces being spawned by WLM for higher-volume applications. This can also create a significant overhead due to the need to keep starting multiple JVMs.

Figure 3 - 64-Bit JVM Support

As shown in Figure 3 above, DB2 11 introduces support for multi-threaded 64-bit JVMs. The new approach still uses one TCB for each executing stored procedure, but these all execute in just one multi-threaded 64-bit JVM for each stored procedure address space, thereby eliminating the overheads of starting up multiple JVMs and reducing the overall storage footprint for a given workload. This also allows for significantly greater scalability, with NUMTCB values of 25 or more observed during internal IBM testing.

Although some minor changes are required to the application environment definition (and possibly the stored procedures if they invoke native methods via JNI calls), this change significantly improves the performance and scalability of Java stored procedures, narrowing the gap between Java and other language options.

DSMAX Increase

DB2 11 doubles the maximum number of open datasets per DB2 subsystem (governed by the DXMAX subsystem parameter) from 100,000 to 200,000. This huge increase has been made possible by the extensive work that was done in DB2 10 to move most of the remaining DBM1 data structures above the 2GB bar (although the maximum DSMAX value that can be used in any given environment is still dependent on virtual storage availability). This capability has also been retrofitted to DB2 10, via APAR PM88166.

Although this sounds like a fairly minor enhancement, it could have significant implications for DB2 customers who make extensive use of range partitioning. DB2 supports up to 4096 partitions per table/tablespace, and if you decide to also partition a couple of indexes for partition independence you can easily find a single table/index combination exceeding 10,000 datasets. Add to that the impact of recent enhancements that spawn auxiliary objects such as clone tables, history tables for system temporal applications and transparent archive tables and the situation gets even worse. Being able to double the number of open datasets could significantly reduce ongoing delays due to dataset open/close processing (although it could also have interesting implications for DB2 shutdown times when so many datasets need to be closed and de-allocated).

Summary

This article discussed a few of the lesser-known DB2 11 enhancements which could still have a big impact on your environment. There are plenty more, so a detailed examination of the DB2 11 What’s New? manual (GC19-4068-02) or the DB2 11 for z/OS Technical Overview Redbook (SG24-8180-00) is highly recommended.

Julian is a Principal Consultant and Director at Triton Consulting, the UK's leading IBM Information Management specialists. He is a highly experienced DB2 consultant, using IBM's Information Management products for over 25 years in a variety of roles including application programming, database administration, technical architecture, performance tuning and systems programming. Julian is an IBM Gold Consultant, an IBM Red Book author, IBM Champion and a past President of IDUG. He currently serves on the IDUG Content Committee.

1 Like
Recent Stories
An Introduction to IBM Data Studio

The Basics of SQL Performance and Performance Tuning

Index Decluttering Opportunities in DB2 for Linux, UNIX, and Windows