This Db2 for z/OS News from the Lab blog entry was originally published on 2019-05-09.

By Jim Pickel and Paul McWilliams.

In Db2 12, APAR PH08482 simplifies the process of activating new function levels without impacting existing JDBC, CLI, and .net applications, and it possibly reduces the coordination effort required between Db2 administrators and application programmers, for activating new application compatibility (APPLCOMPAT) levels. With this APAR applied, Db2 12 assumes that the IBM Data Server Drivers at the following levels or higher fully support all new SQL capabilities:

• IBM Data Server Driver for JDBC and SQLJ Versions 3.72 and 4.22, or later
• IBM Data Server Driver for ODBC and CLI Version 11.1 Modification 2 Fix Pack 1 or later
• Db2 for Linux, UNIX, and Windows, Version 11.1 Modification 2 Fix Pack 2, or later

Specifically, this APAR makes the clientApplcompat property optional for applications that use the IBM Data Server Drivers (different spellings and capitalization are used in various drivers; however, for readability this post uses only the correct spelling for Java drivers.) Previously, Db2 12 required such applications to specify the clientApplcompat property if the 'NULLID' packages at the server were bound with APPLCOMPAT(V12R1M501) or higher. Otherwise, the connection failed with SQLCODE -30025, possibly with message DSNL076I.

By making the clientApplcompat property optional, this APAR also enables customers to continue to use the Db2 Connect Gateways with 'NULLID' packages that are bound at APPLCOMPAT(V12R1M501) or higher.

Although Db2 no longer requires applications to specify the clientApplcompat property, it remains a useful way for application developers to ensure that the server always returns consistent results to their applications. By specifying this property, the application can guarantee that that the server either returns a result that is consistent with 'NULLID' packages bound at or above the specified APPLCOMPAT level. Otherwise, it fails completely with SQLCODE -30025. Applications that do not specify this property accept possibly inconsistent results from the server.

The recommended best practice in Db2 12 is still to bind a separate set of 'NULLID' packages at the server at the corresponding APPLCOMPAT level for each new function level that you choose to adopt. (DBAs can bind copies of the driver packages by running job DSNTIJLC.) With this approach, existing applications that use the 'NULLID' packages can remain stable. Meanwhile, the specific applications that need to exploit new capabilities can do so by specifying the currentPackageSet property to specify a set of 'NULLID' packages to use, which are bound with the APPLCOMPAT for the higher function level.

For example, assume that a JDBC application uses the new LISTAGG built-in function, which was introduced by function level 501. On the server side, the DBA can use the DSNTIJLC job to bind a new set of packages with collection-id 'NULLID_V12R1M501' and APPLCOMPAT(V12R1M501). Then, the JDBC application programmer can specify currentPackageSet=NULLID_V12R1M501, and clientApplcompat=V12R1M501, to ensure that the application uses the same NULLID packages that were used to develop, test, and deploy the application.

You might wonder why DBAs cannot just always rebind a single default 'NULLID' packages at the higher APPLCOMPAT level every time that they activate a new function level? They can do that, but doing so exposes every application that relies on the 'NULLID' packages to any incompatible changes every time that the DBA rebinds at the APPLCOMPAT level for a new function level! Similarly, if the DBA must reduce the APPLCOMPAT level of the default 'NULLID' packages for any reason, applications that depend on the higher level might stop working. For these reasons, you're bound to find that you eventually have no choice but to run multiple 'NULLID' package collections. It's better to do so with a well-considered design than in haste because of necessity!

For more information about the recommended approach for managing 'NULLID' packages in continuous delivery, see Chris Crone's post over in the IDUG blog: "Db2 12 and the IBM Data Server Drivers with FL 501 and Above"

Jim Pickel is an STSM for Db2 for z/OS, IBM Cloud, and Cognitive Software. Paul McWilliams is a technical writer for Db2 for z/OS.