Wednesday, July 31, 2019

Db2 for z/OS - Talking About APPLCOMPAT (Part 2)

Last month, I posted to this blog part 1 of a 2-part entry on the APPLCOMPAT option of the Db2 for z/OS BIND PACKAGE command. In that entry I covered several aspects of APPLCOMPAT. In this part 2 piece, I will focus on one particular matter pertaining to APPLCOMPAT - namely, the management of this option for packages associated with the IBM Data Server Driver (or its predecessor, Db2 Connect) in a Db2 12 for z/OS environment.

The IBM Data Server Driver is used by client application programs that issue SQL statements, typically in the form of non-DBMS-specific interfaces such as JDBC and ODBC and ADO.NET, that target a Db2 for z/OS system to which the application has a TCP/IP connection. The Data Server Driver packages belong, by default, to a collection called NULLID, and in that collection the packages have a certain APPLCOMPAT value. That APPLCOMPAT value determines the SQL functionality that is available to application programs that use the Data Server Driver packages in the NULLID collection. Let's say that function level V12R1M500 is active on a Db2 12 system. That M500 function level of Db2 12 is analogous to what was called new-function mode for Db2 versions that came before Db2 12, in that it makes available to programs a whole boat-load of new features and functions. But are those functions - such as piece-wise DELETE and query result set pagination - really available to DRDA requester applications (i.e., applications accessing Db2 via the IBM Data Server Driver and Db2's distributed data facility, aka DDF)? They are NOT available to those applications if the APPLCOMPAT value of the IBM Data Server Driver packages being used is below V12R1M500. [It's true that a dynamic SQL-issuing application could change the in-effect application compatibility level by changing the value of the CURRENT APPLICATION COMPATIBILITY special register, but starting with a Db2 12 system for which the activated function level is V12R1M500 or higher, a program can't set CURRENT APPLICATION COMPATIBILITY to a value greater than the APPLCOMPAT value of the package associated with the program.]

OK, so it would be a good idea, in a Db2 12 environment for which function level V12R1M500 had been activated, to have the IBM Data Server Driver packages in the NULLID collection bound with an APPLCOMPAT value of V12R1M500. That way, DRDA requester programs will be able to use the rich SQL functionality available at the M500 function level (I am thinking here of DRDA requester programs that do not have all of their "table-touching" SQL statements issued by Db2 stored procedures - the SQL functionality available to a stored procedure is determined by the APPLCOMPAT value of the stored procedure's package). But what about functionality beyond the V12R1M500 function level? Suppose, for example, that a DRDA requester program needs to issue a query with the LISTAGG function introduced with function level V12R1M501? In that case, you'd need the IBM Data Server Driver packages to be bound with APPLCOMPAT(V12R1M501) or higher. The question is: should you bind the packages in the NULLID collection with APPLCOMPAT(V12R1M501) or higher?

My answer to that question is, "No." Once I had my NULLID packages bound with APPLCOMPAT(V12R1M500), which I'd likely do not long after activating function level V12R1M500 for the Db2 system, I'd leave them at that application compatibility level. Here's why: if you keep rebinding the NULLID packages with ever-higher APPLCOMPAT values as you activate ever-higher function levels for a Db2 12 system, the result would be that DRDA requester programs would, by default, use higher and higher levels of Db2 application compatibility. What's wrong with that? Well, it could cause a DRDA requester application to "stumble into" a SQL incompatibility problem (as I mentioned in part 1 of this 2-part entry, a SQL incompatibility is a situation in which the same SQL statement operating on the same data yields a different result). Suppose, for example, that function level M508 introduces a SQL incompatibility (this is theoretical - as of the date of my posting this blog entry the latest available function level is M505). If you activate function level V12R1M508 for your Db2 12 system and thereafter rebind the NULLID packages with APPLCOMPAT(V12R1M508), it means that DRDA requester programs will by default be executing with the application compatibility level at V12R1M508. Suppose the (theoretical) SQL incompatibility ends up "biting" a DRDA requester application, causing it to fail? The big problem here is that the developers who worked on that application were blindsided by the SQL incompatibility. They didn't know that their program was going to execute with application compatibility set to V12R1M508 - that just happened because the NULLID packages (the IBM Data Server Driver packages that are used by default by DRDA requester applications) were bound with APPLCOMPAT(V12R1M508). No one likes unpleasant surprises.

OK, so you keep the NULLID packages with APPLCOMPAT(V12R1M500), as I'm recommending. How are we supposed to let developers of DRDA requester applications use SQL functionality introduced with function levels beyond M500? Here's how: with alternate IBM Data Server Driver package collections (meaning, collections with names other than NULLID) to which the NULLID packages have been BIND COPY-ed with a desired APPLCOMPAT value that is greater than V12R1M500. And how do you point a DRDA requester application to such an alternate IBM Data Server Driver collection? That can be done on the client side by way of a data source property, but the way that I see as being better (in that it doesn't introduce a hassle for client-side developers or application server administrators) is to use the Db2 profile tables to automatically point a particular DDF-using application to the desired alternate IBM Data Server Driver package collection (this is done via a profile attribute that sets the value of the CURRENT PACKAGE PATH special register to the name of the alternate IBM Data Server Driver package collection, as described in an entry I posted last year to this blog).

Here's how this scenario might play out in your environment, with a developer named Steve communicating a need to a Db2 for z/OS DBA named Sarah (again, I'm using a theoretical M508 future function level):

Steve: Hey, Sarah. Your team told us to let you know if we need Db2 SQL functionality beyond what's available at the M500 function level. I think I have that need. I want to use the XYZ built-in Db2 function for a new client-server application that will access Db2 for z/OS-based data, and it seems that the function isn't available with M500 because I get an error in our development environment when I try to use it.

Sarah: Yeah, function XYZ was delivered with function level M508, and our default function level for Db2 client-server applications is M500. We activated function level M508 for our Db2 for z/OS systems a few months ago, and we can get your application set up to work at that application compatibility level, no problem. All I need from you is a way to identify your client-server application, because we'll make a Db2-side change that will key off of that identifier. It could be the auth ID that the application uses to connect to the Db2 system, or the IP addresses of the servers on which the application will run, or a workstation name provided by the application, or...

Steve: Auth ID would make the most sense. This particular application on which we're working will connect to the Db2 system using auth ID 1234.

Sarah: OK, great. We'll get a Db2 profile set up for that auth ID, and that profile will enable your application to use M508 functionality. By the way, the M508 function level introduced a SQL incompatibility: if you provide a timestamp value as the argument for the ABC built-in Db2 function, the result at the M508 application compatibility level is thus-and-such, whereas before M508 the result would be so-and-so. Would that SQL incompatibility impact the application on which you're working?

Steve: No, we aren't using the ABC built-in function - but thanks for that heads-up.

Sarah: Sure thing. We should have the new Db2-side profile ready for you in the development system by end-of-day tomorrow - we'll notify you when that work is done. You should then be able to use the Db2 XYZ function with no problem.

Steve: Thanks!

OK, get it? Several very good things happened here. First, Steve let Sarah know about a new Db2 SQL function that his team wants to use - it's always nice when the Db2 team gets word about new SQL functionality that developers want to use. Second, Sarah, by way of a Db2 profile, will quickly enable Steve's team to use the desired new SQL functionality without having to do anything special (such as change a data source property) on the client side. Finally, this exchange gave Sarah the opportunity to inform Steve of a SQL incompatibility introduced with (the theoretical) M508 function level of Db2 12. It turned out that this SQL incompatibility was not going to impact the application on which Steve is working, but if that had not been the case - if the SQL incompatibility were going to be problematic for the new application - then knowledge of the incompatibility would give Steve and his teammates a chance to work around it by way of a code change.

I hope that this blog entry has provided you with what you'll see as being a reasonable way of managing APPLCOMPAT values beyond V12R1M500 for the IBM Data Server Driver packages in a Db2 12 for z/OS environment.

One more thing: for a time, starting last year, we (IBM) required DRDA client-side programmers to appropriately set the value of a client-side parameter called clientApplCompat if use of SQL functionality beyond the V12R1M500 function level were desired. Db2 teams at a number of sites were not keen on that requirement, as they want to make working with Db2 for z/OS-based data as hassle-free as possible for their application development colleagues. IBM Db2 for z/OS development heard the voice of the customer, and responded with APAR PH08482, the fix for which removed the need to set clientApplCompat in order to use Db2 12 SQL functionality beyond the M500 function level.


  1. Hi,
    Have a question. In some cases, the bind of a package present in NULLID collection happens implicitly when a thread from distributed side access any DB2 tables i believe from a new ip address or server. Please correct me if i am wrong.
    What would happen in this case? Which APPLCOMPAT would it use? zparm ?


    1. I am pretty certain that what you have stated is not the case - that would not be good from a Db2 client-server scalabilty perspective. You can verify that the NULLID packages are not frequently rebound (implicitly or otherwise) by checking the value in the BINDTIME column for the NULLID packages in the SYSIBM.SYSPACKAGE catalog table.

      Whenever ANY package is rebound, if the package does not already have an APPLCOMPAT value (i.e., if the value of the APPLCOMPAT column for the package's row in SYSIBM.SYSPACKAGE is blank), Db2 will use for the package the value specified for APPLCOMPAT in ZPARM.


  2. Hi -

    So that is all fine for now, but surely at some point we will need to rebind the connection packages with a higher level APPLCOMPAT - aren't we just kicking the can down the road by leaving them at FL500? For us the real issue is that we have customers that use Db2 Connect and we have no good way of identifying what version of the drivers are being used and who is using them, so when we are ultimately forced into binding the connection packages at a higher APPLCOMPAT we can't proactively address users of outdated driver packages...


    Margaret Lusk

    1. Hello, Margaret. I'll make a couple of points here.

      First, there's the question of the "right" value for APPLCOMPAT for the IBM Data Server Driver / Db2 Connect packages in the NULLID collection. I've seen two approaches in this area. One approach is to leave the APPLCOMPAT value for those packages at V12R1M500 (once the packages have been bound or rebound so as to have that APPLCOMPAT value), and wait for an application team (people working on what will be, from a Db2 for z/OS perspective, a DRDA requester application) to say that they need SQL functionality beyond that provided by Db2 12 function level 500. At that time, the NULLID packages can be rebound with the desired APPLCOMPAT value, or an alternate collection of the IBM Data Server Driver packages, bound with the desired APPLCOMPAT value, can be set up for the application in question. The other approach involves proactively rebinding the NULLID packages with higher APPLCOMPAT values as higher Db2 12 function levels are activated for the system. That latter approach can be good in terms of staying in front of developers' needs, but it does introduce the possibility that a SQL incompatibility could be introduced, possibly negatively impacting some existing programs. In my experience, that SQL incompatibility-related risk is pretty low, but I can't say that it's zero. In the end, a Db2 team just needs to figure out what's best for the organization: either make the latest SQL functionality available by default to developers of DRDA requester applications by going to higher APPLCOMPAT values for the NULLID packages, or try to eliminate SQL incompatibility risk by "freezing" SQL behavior via a not-changing APPLCOMPAT value for the NULLID packages.

      Second point: you should be able to determine the IBM Db2 driver versions in use by DRDA requesters, by examining the output of the Db2 for z/OS command -DISPLAY LOCATION. Use information in the PRDID field of the output for a remote location. For example, if you see SQL11010 in the PRDID field for a remote system, that indicates a V11.1 client (the 1101 part) that is using the IBM Data Server for CLI and ODBC. If you see JCC04220, that's a client using Version 4.22 of the IBM Data Server Driver for JDBC (4.0) and SQLJ, and that corresponds to a V11.1 client (information on relating JDBC driver versions to Db2 client versions is on this Web page:


  3. Hi,
    I've understood to leave the NULLID packages at applcompat level V12R1M500 and create an additional set of packages e. g. in a collection named NULLID_V12R1M505 for applications that want to use new SQL functionality.

    However, a new version of Db2 has already been announced: Db2 Apollo (V13?)
    I suppose a new application compat level will come along with it, maybe V13R1M500.
    How will you move your client applications to the new version?
    In the same way, that people have to ask for a new appl compat?

    Wouldn't it be better, to check all the SQL incompatiblities, correct them and move the NULLID packages to the new application compatibility? For applications that can't be changed, to leave them at an older level of application compatibility via DSN_PROFILE_TABLES (e. g. collection: NULLID_V12R1Mxxx).


    1. Hello, Renate.

      The approach that you have suggested for managing APPLCOMPAT for the NULLID packages is quite reasonable. In my experience, I have seen two main schools of thought with regard to this matter. Some Db2 for z/OS DBA teams choose to get the NULLID packages to an APPLCOMPAT value of V12R1M500 and then leave them at that level, with "one-off" collections of these packages, with higher APPLCOMPAT values, established as needed for applications that need more-advanced SQL functionality. Other Db2 DBA teams have taken a different approach (similar to the one you have described): as higher and higher Db2 12 function levels are activated for a subsystem (or data sharing group), they will rebind the NULLID packages to have the corresponding APPLCOMPAT value, and have "one-off" collections of the packages, with lower APPLCOMPAT values, established as needed for applications that would otherwise have SQL incompatibility problems with the APPLCOMPAT value of the NULLID packages (a blog entry on using the Db2 profile tables to direct selected applications to alternate collections of the NULLID packages can be viewed at

      Both of these approaches are fine, as far as I'm concerned - I recommend going the way that makes sense for your organization.

      Note that SQL incompatibilities introduced by a new version of Db2 for z/OS, or by a new function level within a version, are generally 1) quite few and far between, and 2) likely to impact few, if any, of your application programs.