Blogs

This article explores the settings that take advantage of the Db2 for z/OS continuous delivery features when dynamic SQL applications are using a JDBC type 4 connection. There are many variations in the ways you can configure the driver and server, and so this may not be a comprehensive description, but can hopefully guide you as you establish you own policy for managing multiple applications and various server function levels. The intention here is to give a customer deployment view for an environment with many applications.

Continuous Delivery and Db2 12 for z/OS

It is best if this is not the first article you read concerning Db2 12 for z/OS and continuous delivery. You should first become completely familiar with not only continuous delivery, but also application compatibility. The best resources are:

Exploring IBM Db2 for z/OS Continuous Delivery Redpaper by Chris Crone

IDUG Blog: DB2 CONTINUOUS DELIVERY – SAFELY MANAGING CHANGE by Chris Crone

Db2 12 Migration and Continuous Delivery by David Simpson

Db2 12 and the IBM Data Server Drivers with FL 501 and Above by Chris Crone

One thing to note here is that continuous delivery of new functions will be delivered in Db2 12 for z/OS by means of function levels. Once installed and activated, a new function level is then made available on an application by application basis by means of an application compatibility setting. Currently available function levels include:

• V10R1 – version 10 compatibility
• V11R1 – version 11 compatibility
• V12R1M500 – version 12 base new functionality
• V12R1M501 – compatibility with version 12 function level 1
• V12R1M502 – compatibility with version 12 function level 2
• V12R1M503 – compatibility with version 12 function level 3, which as of this time is the highest function level available

IBM Data Server Driver

The Data Server Driver for JDBC and SQLJ is generally upward compatible with the next version of Db2, and perhaps even more so, but only if the driver uses no new features. So, whichever driver level you were using to connect to Db2 11 for z/OS should work. However, it is strongly advised that you upgrade your IBM Data Server Driver, especially if you wish to take advantage of new features and functionality. To begin taking advantage of DB2 12 for z/OS function level M501 and above you need your IBM Data Server Driver to be at 3.72.30 for JDBC 3.0 (JDBC 3.0 forward functionality is deprecated so I’m assuming compatibility through V12R1M500) and 4.22.42 for JDBC 4.0. This corresponds to Db2 v11.1 M2 FP2. As of this time, I strongly recommend the latest available driver Db2 v11.1 M4 FP4 as several fixes have been applied.

Typical JDBC Type 4 Connection Definitions to Db2 for z/OS

For many years I have seen a proliferation of Java applications of all sorts connecting and processing data on Db2 for z/OS. In almost every case thus far the connection is quite simple, for example:

jdbc:db2://my.datacerter1.com:446/DB2A:user=bob;password=rady;retrieveMessagesFromServerOnGetMessage=true;

This provides a default connection to Db2 for z/OS, which then uses a set of packages in the “NULLID” collection, beginning with “SYS*”. The “NULLID” collection generally contains all the packages for the various remote clients that connect to Db2 for z/OS. Now, there are many packages in the “NULLID” collection, and which ones are used by a particular collection depend upon additional connection properties being set. However, in most situations the additional properties are not being utilized by application developers. There is an application compatibility associated with these “NULLID” packages, which was introduced in Db2 11 for z/OS, and upon migration to Db2 12 for z/OS should be set to the value it was in Db2 11 for z/OS, whether that be V10R1, V11R1, or the empty string (equal to V10R1).

At many customer sites there are multiple applications connecting to Db2 for z/OS all using the same connection string to provide an enterprise-wide universal generic connection defintion. In some cases, this could be hundreds of applications all using the “NULLID” packages. I have even seen situations in which the BIND parameters were changed for the basic “NULLID” packages because an application wanted to use a specific feature of Db2, but didn’t want to set the connection property in their application.

What Changes in V12?

With Db2 12 for z/OS comes a shift in responsibility as to what level of application compatibility will be used for an application. While the concept of application compatibility existed in Db2 11 for z/OS, it was generally controlled by system programmers and DBAs for the “NULLID” packages. Moving forward it will be the responsibility of application developers, along with DBAs and system programmers beginning with application compatibility level M501. This control comes in the form of the APPLCOMPAT column of the SYSIBM.SYSPACKAGE catalog table. Currently it can be set using BIND parameter APPLCOMPAT, or if you are using The DB2Binder utility you specify APPLCOMPAT in the –bindoptions option. The values you can set APPLCOMPAT to are exactly the same as function levels mentioned in the “Continuous Delivery and Db2 12 for z/OS” section above. You can also REBIND existing packages to increase or decrease the application compatibility. However, as you will learn any such changes must be carefully coordinated and tested, especially for packages in the “NULLID” collection.

So, what does this mean for our JDBC type 4 dynamic applications? Well, it could mean nothing or it could mean everything! When you first migrate to Db2 12 for z/OS, hopefully you are making no changes to your “NULLID” packages and you have the system wide function level is at V12R1M100. As you test and gain confidence in sustaining normal Db2 operations, you may decide to activate a new function level, and choose to move to function level V12R1M500 (via the –ACTIVATE NEW FUNCTION LEVEL command) as your system wide setting. If, for example, the application compatibility level of your “NULLID” packages are set to V11R1 then your JDBC type 4 applications are still governed by V11R1 rules. If you were to REBIND those packages to APPLCOMPAT V12R1M500 all of your JDBC type 4 applications would suddenly be at DB2 12 New Function mode!

It is when you go to activate new function V12R1M501 that the opportunity presents itself to make the switch in responsibilities! One thing for sure is that when you get to V12R1M501 at the system level then it is time to start paying attention to application compatibility! If you were to do nothing with your JDBC type 4 applications and simply REBIND the “NULLID” packages to APPLCOMPAT V12R1M501 the connections to the data server will basically stop working! Therefore, if any of your JDBC type 4 applications wish to take advantage of any new Db2 for z/OS features at or above function level M501, you must coordinate any new function level setting with the application compatibility for that application with the IBM Data Server Driver for specific clients. This is controlled via two JDBC connection properties:

• clientApplcompat – The minimum client application compatibility that the client will use to talk to the data server
• currentPackageSet – The collection id of the set of JDBC connection packages that the client will use to talk to the data server

Please take note of the spelling of these two properties, especially the capitalization. IBM documents them a number of different ways, but these spellings here are correct for JDBC (ODBC and CLI will be different). What this means is that application developers, database administrators, and possibly Db2 system programmers, need to be involved to control the application compatibility at the individual application level. More on this in the coming sections.

What is Application Compatibility at a IBM Data Server Driver Level?

If we are going to have to set the application compatibility level for our JDBC type 4 dynamic applications, then what exactly are we telling the Db2 12 for z/OS server? The answer is that we are stating that the application would like to use a specific package collection for its communication with the server, and that the application compatibility (APPLCOMPAT) level of the packages in that collection will be used to determine the application compatibility of the application. The clientApplcompat is nothing more than a switch with causes the driver to flow a collection name as part of the connection. Then the server loads the packages for that collection id, and sends the APPLCOMPAT level from SYSIBM.SYSPACKAGES back to the client. If no corresponding currentPackageSet is specified, then “NULLID” collection is used. To reiterate, it is the APPLCOMPAT level of the packages on the data server that dictates the application compatibility of the specific application, regardless of the clientApplcompat value as long as it’s a valid value that is at or below the APPLCOMPAT value of the packages. Let’s look at an example. The “Customer” application has decided they need to use the LISTAGG function in their next release. Up to this point they have been using the default Db2 connection definition. When they attempted to use LISTAGG they received a SQLCODE -4700 since they attempted to use a new function that is not available (“NULLID” collection has been rebound at APPLCOMPAT V12R1M500). So, a brand-new set of IBM Data Server Driver packages are set up in a new collection called “COLLIDV12R1M501” with an APPLCOMPAT setting of V12R1M501. IBM provides two sets of JCL, DSNTIJLC and DSNTIJLR, to provide for binds and rebinds of collections to a new APPLCOMPAT level. The connection string is then modified for their application to look something like this:

jdbc:db2://my.datacerter1.com:446/DB2A:user=bob;password=rady; clientApplcompat=V12R1M501; currentPackageSet=COLLIDV12R1M501;retrieveMessagesFromServerOnGetMessage=true;

Upon connection the clientApplcompat setting will cause the package set “COLLIDV12R1M501” to flow to the data server, which will use the packages in that collection to process SQL for that connection. Since the APPLCOMPAT setting of the packages in collection “COLLIDV12R1M501” are V12R1M501 that will be the application compatibility level of the “Customer” application and they can now code the LISTAGG function. All other applications are unaffected since they are still using collection “NULLID” by default and have not established a clientApplcompat property setting.

Special Registers

It should be noted that setting the driver properties clientApplcompat and currentPackageSet does not impact the Db2 special registers CURRENT APPLICATION COMPATIBILITY and CURRENT PACKAGESET. The CURRENT APPLICATION COMPATIBILITY special register is determined by the APPLCOMPAT setting for the package, and the CURRENT PACKAGESET value is the empty string. Setting the CURRENT PACKAGESET once connected has no impact on the special register value returned to the application, although the statement is successful. Also, if the application changes the collection used via setting the CURRENT PACKAGESET special register, the application will then get the application compatibility of the packages in that collection if the collection exists. The CURRENT APPLICATION COMPATIBILITY special register will reflect that change. Upon commit, however, the collection in use can be reset dependent upon the connection to Db2 and how the threads are pooled and reused. Therefore, it is not advised to use the CURRENT PACKAGESET special register to change the collection used.

Setting the CURRENT APPLICATION COMPATIBILITY special register will fail if the function level it is being set to is greater than the function level supported by the packages in the collection. Setting it to a lower level will limit the functionality of any statements issued to that lower level.

Setting Driver Properties

IBM Data Server Driver properties can be set in the connection string, and this may be good enough for most applications. However, in enterprises with large application development staff, or in situations where this is significant automation of application development or application assembly, alternatives to setting the properties may be needed. The Db2 Java application development documentation seems to state, although vaguely, that IBM Data Server Driver properties can also be set in several different ways. These include a DB2JccConfiguration.properties file or a JAR file that contains a DB2JccConfiguration.properties. In a modern application development environment, applications can be deployed in a variety of different ways:

• On premise web server
• Batch or online services
• Openshift on AWS
• Many others

Application components are stored in shared libraries with version control, and assembled with technologies such as Git, Maven, or Jenkins. In order to effectively manage what will become essential a library of IBM Data Server Driver versions and corresponding application compatibility levels, then making these selectable via something like Maven is important. That is why managing these combinations of properties in a file (clientApplcompat and currentPackageSet) could be important. In testing this we have only been successful in setting currentPackageSet property in a DB2JccConfiguration.properties file. IBM has indicated that the clientApplcompat will be available to be set in a DB2JccConfiguration.properties file as of Db2 v11.1 M4 FP4, which is available as of November 27, 2018. There is no word on exactly what properties, if any, can be set in a JAR file, and apparently the documentation is inaccurate in this regard. So, the proper way to manage and deploy these settings on an application by application basis are still being investigated by development staffs I’ve been involved with. Another possibility is that datasource parameters could be injected into the Jenkins (or similar) pipeline as part of deployment, but this is also still being investigated by our development staff.

To emphasize the point on setting properties…application developers don’t necessarily care what database they are deploying. The know the requirements and the data, and in their deployment is included everything they may need; web server, open source components, enterprise shared code, custom code, drivers. They are going to care even less about a database service level, and so making the setting of these properties as seamless as possible in these situations is important.

Possible Driver/Server Configurations

From a data server perspective, it is apparent that we need to manage the possible combinations of driver and server function levels. This can be done on any level of granularity, from as small as one per application to one for the entire enterprise. On one end of the spectrum imagine there was a CUSTOMER application. The application was developed using Db2 V12R1M501 and has IBM Data Server Driver properties clientApplcompat=V12R1M501; currentPackageSet=CUSTOMER. On the server a collection is copied from the “NULLID” collection and named “CUSTOMER” using the IBM supplied DSNTIJLC JCL. This gives complete flexibility to change the application compatibility (as long as it’s higher) of the CUSTOMER application simply be rebinding the packages in the “CUSTOMER” collection. So, we can upgrade the CUSTOMER application’s function level without impact to any other application. Of course, this means one collection for every application and could result in a management nightmare. On the other end of the spectrum there would only be one collection, “NULLID”. There would be considerable responsibility on all applications to set clientApplcompat in coordination with an upgrade to function level V12R1M501 and beyond, and corresponding REBIND of the “NULLID” packages to the same or higher. Then going forward, it would be “business as usual”, including new function levels via REBIND without having to update clientApplcompat, until a new function level requires a driver upgrade.

There is also a reasonable middle ground. Once the upgrade to Db2 12 for z/OS is complete, the “NULLID” packages can be rebound to function level V12R1M500 with no impact to existing applications that were bound with APPLCOMPAT V11R1 (testing would be required for “NULLID” package that were bound with APPLCOMPAT V10R1 before moving to V12R1M500). Then going forward there could be shared collections and driver settings (hopefully available via something like Maven) for the various function levels:

• “NULLID” collection at APPLCOMPAT V12R1M500, system-wide default never changes
• “COLLIDV12R1M501” collection at APPLCOMPAT V12R1M501,clientApplcompat=V12R1M501; currentPackageSet=COLLIDV12R1M501 (also clientApplcompat=V12R1M500 will work, but application compatibility will be at V12M1M501)
• “COLLIDV12R1M502” collection at APPLCOMPAT V12R1M502,clientApplcompat=V12R1M502; currentPackageSet=COLLIDV12R1M502 (also clientApplcompat=V12R1M501 or V12R1M501 will work, but application compatibility will be at V12M1M502)
• “COLLIDV12R1M503” collection at APPLCOMPAT V12R1M503,clientApplcompat=V12R1M503; currentPackageSet=COLLIDV12R1M503 (also clientApplcompat=V12R1M501, V12R1M502, or V12R1M503 will work, but application compatibility will be at V12M1M503)

Which Solution works Best?

Well, I don’t know. It, of course, depends upon your enterprise standards and practices, along with the application development team’s desire to utilize new database functionality.

• You could set the default collection of the latest function level. However, that will require your applications to set clientApplcompat once you reach function level V12R1M501. Then, moving forward would be much like migrating to a new version of Db2 in the past.
• You could simply leave the default collection at its pre-Db2 12 setting and simply continue on until a new function is needed.
• You could implement a strategy like the one which is laid out in the previous section of this paper, and then have a set plan for applications to pick one. If they wish an upgrade to a new application compatibility level, then they change their connection properties. This gives a decent level of flexibility, but you have then one collection per application compatibility level if you choose to adopt each function level (you could skip levels).
• You could create a collection for each application, which gives maximum flexibility, but could become a management issue if there are a large number of applications.

Other Data Server Driver Connections

This only covers JDBC type 4 connections to Db2 for dynamic SQL. For dynamic SQL using a JDBC type 2 connection it appears that the application compatibility level will be taken from the packages used regardless of whether or not clientApplcompat is set, at least for the default collection “NULLID”, which includes APPLCOMPAT V12R1M501 and greater. SQLJ applications will function at the level of their statically bound packages. 

Summary

Planning and coordinating application compatibility with Db2 continuous availability can impact application development and deployment going forward. Careful understanding and planning can make this transition easier, but you can’t ignore it unless application compatibility V12R1M500 is all you ever want.

Update! This has changed with APAR PH08482 which allows for a default function level for connections that do not specify it! More details can be found here.