Using profile tables to control which Db2 for z/OS application compatibility levels to use for specific data server client applications

Profiles can be used to control which client applications use features that are associated with a specific Db2 for z/OS application compatibility level. This capability allows client applications that do not need to use new features to continue to connect to a Db2 for z/OS server at an earlier application compatibility level.

1. On the client operating system, bind the client driver packages into two collections:
   • One collection with the default collection name NULLID, and with the APPLCOMPAT option set to V12R1M500. If you have already bound the client driver packages into collection NULLID with APPLCOMPAT set to V12R1M500, you do not need to bind them again.
   • Another collection with a different name, such as NULLID_NF, and with the APPLCOMPAT option set to V12R1M501.
   • For the IBM® Data Server Driver for JDBC and SQLJ, follow these steps to bind the driver packages:
     • If you have not already done so, invoke the DB2Binder utility with a control statement like this one to create a collection with the default name NULLID, and with application compatibility set to V12R1M500:

       java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://sys1.svl.ibm.com:5021/STLEC1 \
       -user user -password password \
       -bindoptions "APPLCOMPAT V12R1M500" -action REPLACE

     • Invoke the DB2Binder utility with a control statement like this one to create a collection named NULLID_NF, with application compatibility set to V12R1M501:

       java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://sys1.svl.ibm.com:5021/STLEC1 \
       -collection NULLID_NF \
       -user user -password password \
       -bindoptions "APPLCOMPAT V12R1M501" -action REPLACE

   • For the IBM Data Server Driver for ODBC and CLI, follow these steps to bind the driver packages:
     • If CLI/ODBC configuration keyword OnlyUseBigPackages=1, you do not need to bind the driver packages.
     • If CLI/ODBC configuration keyword OnlyUseBigPackages=0, you need to bind small packages with application compatibility set to V12R1M501:

       db2 bind '%DB2PATH%\bnd\@ddcsmvs.lst' blocking all sqlerror continue \
       grant public action replace collection NULLID_NF \
       generic \"APPLCOMPAT V12R1M501\"

2. In Db2 for z/OS, create a profile for client application ACCTG_APP501 by inserting rows into tables SYSIBM.DSN_PROFILE_TABLE and SYSIBM.DSN_PROFILE_ATTRIBUTES. The profile directs Db2 to use the driver with packages in collection NULLID_NF when application ACCTG_APP501 runs. Because the driver packages in the NULLID_NF collection are bound with option APPLCOMPAT V12R1M501, ACCTG_APP501 can use capabilities that are available at application compatibility level V12R1M501.

   INSERT INTO SYSIBM.DSN_PROFILE_TABLE 
    (PROFILEID, CLIENT_APPLNAME, PROFILE_ENABLED) 
    VALUES (1002, 'ACCTG_APP501', 'Y');
   INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
    (PROFILEID, KEYWORDS, ATTRIBUTE1)             
    VALUES (1002, 'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_NF');

   Important: Although PKGNAME can be used as a filtering category for profile table rows that use the 'SPECIAL_REGISTER' value for KEYWORDS, when client drivers are used, you should not use PKGNAME alone or in combination with COLLID.
3. On Db2 for z/OS, issue the -START PROFILE command to load the updated profile tables into memory.

SELECT CURRENT APPLICATION COMPATIBILITY,
 GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') 
 FROM SYSIBM.SYSDUMMY1

String currApplcompat, appCollection;
Connection con;
Statement stmt;
ResultSet rs;
…
stmt = con.createStatement();        // Create a Statement object       
rs = stmt.executeQuery("SELECT CURRENT APPLICATION COMPATIBILITY," +
" GETVARIABLE('SYSIBM.PACKAGE_SCHEMA')" + 
" FROM SYSIBM.SYSDUMMY1");           // Get the result table from the query
while (rs.next()) {                  // Position the cursor 
 currApplcompat = rs.getString(1);   // Retrieve the application compatibility
 System.out.println("APPLCOMPAT = " + currApplcompat);
                                     // Print the application compatibility
 appCollection = rs.getString(1);    // Retrieve the collection name
 System.out.println("COLLID = " + appCollection);
                                     // Print the collection name