Db2 for z/OS and its ecosystem

 View Only

Supporting down-level Db2 data server drivers with Db2 profile tables

By SARBINDER KALLAR posted 16 days ago

  

The IBM Db2 drivers, such as IBM Data Server Driver for JDBC and SQLJ and IBM Data Server driver for ODBC and CLI, use the packages in the NULLID collection by default.

If the packages in NULLID collection are bound with APPLCOMPAT(V12R1M501) or higher, the drivers must be at the level shipped with Db2 11.1 M1 FP1 (JCC04220/JCC03720/SQL11011) or later. Otherwise, the down-level drivers begin failing with SQLCODE -30025 with reason-code 0600.

Until these drivers can be upgraded, one solution is to change the collection used by the Java drivers, such as by using the currentPackageSet driver property. However, a more seamless option, which only requires changes at the Db2 for z/OS server, is to use the Db2 profiles tables.

Specifically, you can use the Db2 profile table rows with the SPECIAL_REGISTER keyword to switch the down-level driver connections to a package collection that is bound with APPLCOMPAT(V12R1M500).

Step 1: Create the client packages at APPLCOMPAT(V12R1M500)

Create packages for IBM Data Server Driver under collection NULLID_V12R1M500 using bind option APPLCOMPAT(V12R1M500).

For example, you might issue the following command from the command line or USS to run the DB2BINDER utility:

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

Another alternative is to issue the following BIND command from the Db2 for Linux, Unix, and Windows command line processor:

db2 bind '%DB2PATH%\bnd\@ddcsmvs.lst' blocking all sqlerror continue grant public keepdynamic yes action replace collection NULLID_V12R1M500 generic \"APPLCOMPAT V12R1M500\"

Yet another alternative is to run the DSNTIJLR sample job.

Step 2: Set up the profiles for the down-level drivers

Populate profile tables for down-level java drivers such that when the drivers connect to Db2, collection is seamlessly switched to NULLID_V12R1M500 and the down-level drivers no longer receive SQLCODE -30025 with reason-code 0600.

For example, you can issue the following INSERT statements:

INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (300,'JCC030*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (300,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (350,'JCC035*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (350,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (360,'JCC036*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (360,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (371,'JCC0371*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (371,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (400,'JCC040*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (400,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (410,'JCC041*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (410,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (421,'JCC0421*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES (421,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (900,'SQL09*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES(900,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID, PRDID, PROFILE_ENABLED) VALUES (1000,'SQL10*','Y')#
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES  (PROFILEID,KEYWORDS,ATTRIBUTE1) VALUES(1000,'SPECIAL_REGISTER', 'SET CURRENT PACKAGE PATH=NULLID_V12R1M500')#                                                                                                                                               
COMMIT#  
--START PROFILE               
CALL SYSPROC.ADMIN_COMMAND_DB2('-START PROFILE',15,'SYC','DB2A',?,?,?,?,?,?,?,?)#   
COMMIT#
--VERIFY PROFILES APPLIED SUCCESSFULLY
SELECT PROFILEID,
CASE WHEN STATUS LIKE 'ACCEPTED%' THEN 'SUCCESS' ELSE 'FAILURE' END 
FROM SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY 
WHERE PROFILEID IN (300,350,360,371,400,410,421)#

                                    

Step 3: Start the profiles

You can issue the START PROFILE command to start the newly defined profiles. In a data sharing group, start the profiles on every member that the application can access.

 

Step 4: Verify that the solution is working

The next connection from a down-level driver should be successful.

You can also verify the collection in use, by issuing the following query:

SELECT CURRENT PACKAGE PATH CPP, GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') COLLID FROM SYSIBM.SYSDUMMY1#

 

The result should look like this:

 

CPP

COLLID

NULLID_V12R1M500

NULLID_V12R1M500

 

0 comments
19 views

Permalink