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

 

Db2 12 has been available for over 2 years and many customers have migrated to Db2 12 Function level 500.  Function Level (FL) 500 enables all the new Db2 12 base capability and is equivalent to what we used to call New Function Mode (NFM) in Db2 11 and below.  Now that customers are on FL 500, they are starting to look at FL 501 and beyond (FL 503 just became available on September 21s – see https://ibm.biz/BdYeWz for further information on FL 503).

There are no IBM Data Server Driver changes required to exploit Db2 12 FL 500.  However, changes are needed at the client, and potentially at the server, to exploit FL 501 and above.

The Db2 documentation on FL 501 - https://ibm.biz/BdYbBE and IBM Redbook on Continuous Delivery - http://www.redbooks.ibm.com/abstracts/redp5469.html?Open have a great deal of information on activating FL 501, and the requirements to exploit FL 501 and above.

In this Blog – we’d like to try to distill the information already available into the actions that you may want to take, or need to take, to move beyond FL 500.

First, let us say that if you don’t want applications to exploit FL 501 (or above) – then no changes are necessary to any application (including IBM Data Server Driver applications) when activating FL 501 or above.  Note that we said don’t want applications to exploit – there may be enhancements that are enabled by a FL that are not related to applications (such as Db2 AI for z/OS in FL 503) that may be a reason for activating a FL. 

Function level activation, and application enablement to exploit a FL (using APPLCOMPAT) are separate to ensure applications remain stable across function levels.  Activating a FL does not expose applications to changes introduced by a FL.  In general, you must also REBIND a package with a specific FL to expose that application to changes introduced in a FL.  This separation enables applications to be developed, tested, and deployed at a specific APPLCOMPAT level, and remain stable, across functions levels, until the application needs to take advantage of new capability that requires a new APPLCOMPAT level.

Customers with applications using the IBM Data Server Driver packages have many questions about how function levels and APPLCOMPAT work with the “nullid” packages, and what changes are required to enable JDBC and CLI applications to exploit FL 501 and above.  At a minimum – the following IBM Data Server Driver clients and specifying a client property (note the spellings) are required to exploit FL 501 and above:

  • IBM Data Server Driver for JDBC and SQLJ: Versions 3.72 and 4.22, or later
    • Property - clientApplcompat = V12R1M500[1]
  • Other IBM data server clients and drivers: Db2 for Linux, UNIX, and Windows, Version 11.1 Modification 2 Fix Pack 2, or later
    • Property - ClientApplCompat = V12R1M5001

Data Server Driver clients at these levels have an enhancement that enables them to send the collection-id of the packages they wish to use on the initial connection flow.  Specification of the client applcompat[2] property activates this capability. 

Db2 12 at FL 501 and above requires that the collection-id of the packages that the IBM Data Server Drivers will use be sent on the initial connection flow for ‘NULLID’ packages with an APPLCOMPAT of V12R1M501 or above.  If the APPLCOMPAT of the ‘NULLID’ packages is less than V12R1M501 (and the collection id of the packages is not sent – because client applcompat was not sent), then the package will be loaded, if it is at V12R1M501 (or above), SQLCODE -30025.

The recommended best practice for adopting a function level includes binding a new set of ‘NULLID’ packages using DSNTIJLR - https://ibm.biz/BdYbxF.  Db2 recommends this approach because this enables applications that use the existing ‘NULLID’ packages to remain stable.  Applications that want to exploit new capability can be migrated to these new packages by setting the clientApplcompat property and a package collection property that corresponds to the APPLCOMPAT of the FL that the application wishes to exploit. 

For example for a JDBC application to use LISTAGG (delievered in FL 501), a client would set:

  • clientApplcompat = V12R1M500[3]
  • currentPackageSet = NULLID_V12R1M501
  • On the server side, you would use DSNTIJLR to bind collection-id ‘NULLID_V12R1M501’ with APPLCOMPAT(V12R1M501).

You might ask – why all the complexity?  Can’t I just rebind my default ‘NULLID’ packages with the current APPLCOMPAT every time I change the FL and have my applications pick up all the changes?  The short answer to that is YES, however doing so will expose those applications to any incompatible changes introduced in that FL.  

The recommended best practice is to have a set (in a Collection) of ‘NULLID’ packages at each FL you adopt so that applications that need new capability can change to exploit that capability, while existing applications remain stable.  If you REBIND the default ‘NULLID’ packages to another APPLCOMPAT level, you may break applications.  Applications running fine could be exposed to an incompatible change if the APPLCOMPAT is increased, or could stop working if the APPLCOMPAT is decreased.  At some point, you will likely need multiple collections to avoid breaking one or more applications.  It is better to do it out of design, rather than necessity.

Many customers have asked if they have to change the client applcompat property every time they change function levels?  The answer is No. Changing the client applcompat value will not be necessary until there is a client enhancement that requires corresponding server changes.  A minimum client applcompat value would be required to support the new feature, and would be clearly documented when such a requirement was necessary.

There are several ways to set the ClientApplCompat and packageset values for the Non-Java driver:

  1. Add clientApplcompat and currentPackageSet parameters in global section of db2dsdriver.cfg file.
  2. Add clientApplcompat and currentPackageSet parameters in database section of db2dsdriver.cfg file.
  3. In an application, specify clientApplcompat and currentPackageSet keywords in connection string of connect API such as SQLDriverConnect function(CLI).
  4. In an application set connection attributes SQL_ATTR_CLIENT_APPLCOMPAT and SQL_ATTR_CURRENT_PACKAGE_SET using SQLSetConnectAttr function(CLI).

 

For the Java driver[4], you can set the clientApplcompat and packageset values using the following mechanisms:

  1. In an application, specify clientApplcompat and currentPackageSet properties in connection url for DriverManager getConnection method. The properties can also be set using java.util.Properties values in the info parameter of DriverManager getConnection method.

In an application, use setCurrentPackageset and setClientAppcompat methods of DB2BaseDataSource class to set the desired values.

Example Steps, and documentation links for activating new function level in the Non-java driver:

Set ClientApplCompat db2dsdriver.cfg keyword to specify the function level(V12R1M500) and CurrentPackageSet keyword to specify the collection used:

  • ClientApplCompat – More info can be found here- https://ibm.biz/BdYQj6
    • <parameter name="ClientApplCompat" value="V12R1M502"/>
  • CurrentPackageSet – More info can be found here - https://ibm.biz/BdYQjU
    • <parameter name="CurrentPackageSet" value="SRCCOLID"/>

Customers can run the commands below from a shell(unix/linux) or command  window (windows) which will add the parameters at the global section of db2dsdriver.cfg file to enable the specific DB2 for z/OS function level for odbc. Please substitute the right functionlvl and collection id as per your environment before running the command.

Add the parameters to global section of db2dsdriver.cfg.

  • db2cli writecfg add -parameter "ClientApplCompat=V12R1M500”
  • db2cli writecfg add -parameter "CurrentPackageSet=NULLID_V12R1M500"

Add the parameters to the database section of db2dsdriver.cfg.

  • db2cli writecfg add -dsn SAMPLE -database SAMPLE -host hostname -port 5021
  • db2cli writecfg add -database SAMPLE -host hostname -port 5021 -parameter "ClientApplCompat=V12R1M500"
  • db2cli writecfg add -database SAMPLE -host hostname -port 5021 -parameter "CurrentPackageSet=NULLID_V12R1M500"

To ensure that the parameters are added correctly, run: 

db2cli validate -dsn SAMPLE

Another option is to specify the parameters in connection string of the application.

SQLAllocenv 1[5]

SQLAllocconnect 1 1

sqldriverconnect 1 0 "database=SAMPLE;hostname=myhost;port=5021;uid=myuid;pwd=mypwd;protocol=tcpip;CurrentPackageSet=NULLID_V12R1M500;ClientApplcompat=V12R1M500;" -3 255 SQL_DRIVER_NOPROMPT

SQLAllocStmt 1 1

SQLExecDirect 1 "SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1" -3

fetch 1

 

Finally, you can also set the parameters using set connection attribute api in the application.

 

SQLAllocenv 14

SQLAllocconnect 1 1

SQLSetConnectAttr 1 SQL_ATTR_CLIENT_APPLCOMPAT V12R1M500 9

SQLSetConnectAttr 1 SQL_ATTR_CURRENT_PACKAGE_SET NULLID_V12R1M500 16

SQLDriverConnect 1 0 "database=SAMPLE;hostname=myhost;port=5021;uid=myuid;pwd=mypwd;protocol=tcpip;" -3 255 SQL_DRIVER_NOPROMPT

SQLAllocStmt 1 1

SQLExecDirect 1 "SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1" -3

fetch 1

 

For Java appplications – you can use these example steps, and documentation links for activating new function via APPLCOMPAT in the java driver:

IBM data server driver configuration keywords - https://ibm.biz/BdY3Ni

 

DB2BaseDataSource interface provides setter methods - https://ibm.biz/BdY3Nq

com.ibm.db2.jcc.DB2BaseDataSource.clientApplcompat

 (IBM Data Server Driver for JDBC and SQLJ type 4 connectivity)

com.ibm.db2.jcc.DB2BaseDataSource.currentPackageSet

You can use these specify the parameters in the following manner:

  1. Specify the parameters in connection url of DriverManager class.

String conString = "jdbc:db2://myhost:5021/SAMPLE:currentPackageSet=NULLID_V12R1M500;clientApplcompat=V12R1M500;";

Connection con = DriverManager.getConnection(conString,"myuid","mypwd");

 

  1. Specify the parameters using the setXXX methods of DB2BaseDataSource interface.

 

DB2SimpleDataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource();

ds.setClientApplcompat("V12R1M500");

ds.setDatabaseName("SAMPLE");

ds.setServerName("myhost");

ds.setPortNumber(5021);

ds.setCurrentPackageSet("NULLID_V12R1M500");

ds.setUser("myuid");

ds.setPassword("mypwd");

Connection con = ds.getConnection();

 

In Summary, we have covered the reasons why new clients, as well as configuration of those clients, is needed to exploit Db2 Continuous Delivery capability.  We have also given pointers and examples on how to configure the dataserver drivers in order to move forward.  We hope that you have found this information useful and together with Dan Luksetich’s blog https://www.idug.org/p/bl/ar/blogaid=803 giving a user perspective of this topic, you hopefully have enough information to move forward in your shop.

 

 

Chris Crone and Sarbinder Kallar

 

[1] Please note that V12R1M500 is the minimum value that may be specified.  If the APPLCOMPAT of the “NULLID” packages is V12R1M501, then the clientApplcompat may be V12R1M501 also.  Similarly, if the APPLCOMPAT of the NULLID packages is V12R1M50x, the clientApplcompat may be V12R1M50x.  The two basic requirements are:

  • clientApplcompat must be at least V12R1M500
  • clientApplcompat cannot exceed the APPLCOMPAT of the “NULLID” packages

[2] Please Note that when not specifically talking about a keyword , I will use client applcompat to avoid confusion since the ODBC/CLI and JDBC drivers spell this property differently.

[3] This value must be at lease V12R1M500, but could also be V12R1M501.

[4] When using type 2 JDBC connection from workstation, db2dsdriver.cfg can be used to set the keywords.

[5] This code snippet can be saved in a file and run from Db2 command line using db2cli utility e.g. \> db2cli < test.txt

2 Likes
Recent Stories
Validating JSON Documents

Db2 for z/OS JSON SQL APIs and Native REST Services

The Digital Technical Engagement pages, a self-service way of learning