75 ways to demystify DB2: #23: Techtip: Error SqlCode = -4700 returned to JCC applications after upgrading DB2/Z servers to V11 CM mode.

Body

Recently we are seeing lot of cases where applications using IBM DB2 JDBC drivers(db2jcc.jar/db2jcc4.jar)) start failing with SqlCode = -4700 after DB2/zOS server upgrade to V11 CM (Compatibility mode).

I wanted to clarify about the error and explain the background.

The error is returned when the application queries the special register CLIENT_CORR_TOKEN. This special register is not available in V11 CM and introduced in New Function Mode (NFM) only.

There are 2 cases:

Case 1:    JCC driver has an API getJccSpecialRegisterProperties() and it is called by Webpshere Application server to support Heterogenous Pooling.  getJccSpecialRegisterProperties API call  retrieves all the special registers from the DB2 server internally during the connection attempt.  Along with other special registers, it tried to get the CLIENT_CORR_TOKEN value and started getting the -4700 error. The issue is fixed in DB2 V10.5 Fix Pack 5 and above drivers via APAR: IT02975 .The APAR fix modified getJccSpecialRegisterProperties API not to call CLIENT_CORR_TOKEN when the DB2/Z server version is in Compatibility Mode.

The API used to call the following query internally:

select  CURRENT CLIENT_CORR_TOKEN, SESSION TIME ZONE, CURRENT CLIENT_ACCTNG, CURRENT CLIENT_APPLNAME, CURRENT
CLIENT_USERID, CURRENT CLIENT_WRKSTNNAME, CURRENT MAINTAINED  TABLE    TYPES FOR OPTIMIZATION, CURRENT PACKAGE PATH, CURRENT
REFRESH AGE, CURRENT SCHEMA, CURRENT DEGREE, CURRENT FUNCTION PATH, CURRENT SQLID, CURRENT LOCALE LC_CTYPE from  SYSIBM.SYSDUMMY1

So if you are using an older JCC driver, upgrade it to V10.5 FP5 & above drivers. You can download it from here.
 

Case 2:  If any application explicitly queries it using "Select CLIENT_CORR_TOKEN  from sysibm.sysdummy", DB2/Z server will throw the -4700 error and  its  expected since the special register CLIENT_CORR_TOKEN is not available in DB2/Z V11 CM servers. You have to fix the application not to query that special register.

Hope this helps.

Feel free to post your comments/feedback in the comment section.

Thank you!

Subbu Prabhu