IBM Support

Why does a JDBC application get -4499 when connecting to DB2 for AS/400 v5r2 and above through DB2 connect gateway?

Question & Answer


Question

Why does a JDBC get -4499 with "invalid DDM parameter" and db2diag.log shows "Invalid SQL statement length" in a WAS => DB2 connect Gateway => DB2 for AS/400 environment?

Answer

It is a limitation when using JCC driver to connect to DB2 for AS/400 v5r2 (or above) via a DB2 Connect Gateway. The JCC driver can directly communicate to DB2 for AS/400 without using DB2 connect gateway, however there are known to be issues when the connection is via DB2 connect gateway.

Here is an example JCC trace for "invalid DDM parameter":
-----
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 [jcc] BEGIN TRACE_DIAGNOSTICS
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4DE0 2020202020202020 2020202020202020 ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 [jcc][Thread:WebContainer : 3][SQLException@4fece456] java.sql.SQLException
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4DF0 2020202020202020 2020202020202020 ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 [jcc][Thread:WebContainer : 3][SQLException@4fece456] SQL state = 58017
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4E00 0020202020202020 2020202020202020 . ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 [jcc][Thread:WebContainer : 3][SQLException@4fece456] Error code = -4499
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4E10 2020202020003100 2020202020202020 .1. ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 [jcc][Thread:WebContainer : 3][SQLException@4fece456] Message = [jcc][t4][20107][11277][3.65.97] DDM パラメーターの値がサポートされていません。 DDM パラメーターのコード・ポイントにサポートされない値があります: 0x2414。
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4E20 2020202020202020 2020202020202020 ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 入力ホスト変数が、サーバーのサポートする範囲内にない可能性があります。 ERRORCODE=-4499, SQLSTATE=58017
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4E30 2020202020202020 2020202020202020 ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 [jcc][Thread:WebContainer : 3][SQLException@4fece456] Stack trace follows
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4E40 2020202020202020 2020202020202020 ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 com.ibm.db2.jcc.am.DisconnectNonTransientException: [jcc][t4][20107][11277][3.65.97] DDM パラメーターの値がサポートされていません。 DDM パラメーターのコード・ポイントにサポートされない値があります: 0x2414。
[14/10/03 14:19:24:563 JST] 000000bc logwriter 3 [jcc][t4] 4E50 2020202020202020 2020202020202020 ................
[14/10/03 14:19:24:563 JST] 000000b6 logwriter 3 入力ホスト変数が、サーバーのサポートする範囲内にない可能性があります。 ERRORCODE=-4499, SQLSTATE=58017
-----


Note that, while the message text may be different for different locales, the ERRORCODE, SQLSTATE and the code [11277] in the stack trace shall remain the same.

Here is an example db2diag.log containing "Invalid SQL statement length".
-----
2014-10-02-18.13.42.106000+540 E284129F828 LEVEL: Error
PID : 1360 TID : 312 PROC : db2syscs.exe
INSTANCE: DB2INST1 NODE : 000 DB : SAMPLEDB
APPHDL : 0-365 APPID:
AUTHID : DB2INST1 HOSTNAME:
EDUID : 312 EDUNAME: db2agentg (SAMPLEDB)
FUNCTION: DB2 UDB, DRDA Application Server, sqljsParseSqlSttGrpNOC, probe:419
MESSAGE : DIA5000C A DRDA AS token "RECOVERABLE" was detected. The diagnostic
data returned is (SRVDGN): "FUNCTION ID = 0050 , PROBE POINT = 0419 ,
TRACE POINT = 0015 , SUBCODE1 = FFFFFFFF804B0095, SUBCODE2 =
00000000000000BE, SUBCODE3 = 00000000000000BE, ERROR MSG = Parser:
Invalid SQL statement length".

2014-10-02-18.13.42.152000+540 I284959F676 LEVEL: Error
PID : 1360 TID : 312 PROC : db2syscs.exe
INSTANCE: DB2INST1 NODE : 000 DB : SAMPLEDB
APPHDL : 0-365 APPID:
AUTHID : DB2INST1 HOSTNAME:
EDUID : 312 EDUNAME: db2agentg (SAMPLEDB)
FUNCTION: DB2 UDB, DRDA Application Server, sqljsParseSqlSttGrpNOC, probe:15
MESSAGE : ZRC=0x804B0095=-2142568299=SQLJS_VALNSP "VALUE ERROR"
DATA #1 : DDM Object, PD_TYPE_DDM_OBJECT, 16 bytes

data length : 190
codepoint : 0x2414 ( SQLSTT )
length : 194 ( COMPLETE )

2014-10-02-18.13.42.184000+540 I285637F171 LEVEL: Error
PID:1360 TID:312 NODE:000 Title: **** DRDA ASCB ****
Dump File:D:\DB2PROFS\DB2INST1\1360.312.000.dump.bin

-----

It may be observed that the problematic SQL statement can be run successfully via CLP but not WAS/JDBC application. Also, a small JDBC program that employs direct Type-4 connectivity to DB2 for AS/400 and executes the same SQL that fails here can be used to confirm that the direct Type-4 connection does not suffer this error.

Solution to this issue is that the application can be configured to connect directly to DB2 for AS/400 using Type-4 driver rather than going via DB2 Connect gateway. On WAS, the steps to configure a regular Type-4 datasource need to be followed where the hostname, DRDA port number and Database name of the DB2 for AS/400 is provided instead of DB2 Connect Gateway. It should be ensured that there are no firewall or network settings to prevent WAS machine from opening TCP/IP connections to the AS/400. Connectivity may be quickly tested by creating a new datasource with the above settings and using WAS's Test Connection option. If it works, an existing DataSource configuration can be modified to match the same settings.

For licensing to this situation. Users do not need to purchase DB2 Connect PE, and that he just needs to copy the license file, db2jcc_license_cisuz.jar from the DB2 Connect Unlimited Edition server over to the WAS server. This approach will require user to copy the license jar to every JCC client that wants to connect to AS/400.

Note:
This behavior might be changed without notice in the future. We can confirm whether this technote is valid or not by confirming JCC trace and db2daig.log file.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - JDBC - Type 2","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
07 December 2022

UID

swg21691000