0

I am trying to connect Oracle SQL Developer with DB2 Database.

  1. I have added db2jcc4.jar, db2jcc.jar and db2jcc_license_cu.jar Libraries in "Third Party JDBC Driver"
  2. DB2 tabs appears
  3. Entered Username and connection Details.

While testing connection, I encounterd below error.

Status : Failure -Test failed: [jcc][t4][2034][11148][4.24.92] Execution failed due to a distribution protocol error that caused deallocation of the conversation. A DRDA Data Stream Syntax Error was detected. Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

Versions:

db2level: v11.1

./java com.ibm.db2.jcc.DB2Jcc -version

IBM DB2 JDBC Universal Driver Architecture 3.72.44

Jars installed:

echo $CLASSPATH

/db2/coru1db2/home/coru1db2/sqllib/java/db2java.zip:
/db2/coru1db2/home/coru1db2/sqllib/java/sqlj.zip:
/db2/coru1db2/home/coru1db2/sqllib/function:
/db2/coru1db2/home/coru1db2/sqllib/java/db2jcc_license_cu.jar:
/db2/coru1db2/home/coru1db2/sqllib/tools/clpplus.jar:
/db2/coru1db2/home/coru1db2/sqllib/tools/jline-0.9.93.jar:
/db2/coru1db2/home/coru1db2/sqllib/java/db2jcc.jar:

Can someone help me in resolving it?

  • What port is Db2 listening to? If you specified that port, check that there's not an fw in the way (try for example telnet host port) – Lennart Sep 20 '19 at 8:52
  • I am able to connect via telnet using same host and port. – Apeksha Sep 20 '19 at 9:07
  • What’s the result of the following call? ./java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://myhost:myport/MYDB -user myuser -password mypassword called from the command line with the CLASSPATH setting provided? – Mark Barinstein Sep 21 '19 at 15:52
  • @Mark Barinstein PFA for the result of the call: [jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://myhost:myport/DBNAME -user UserName -password ******** [jcc][10512][13714]Failed to create connection. SQLCODE: -4499 SQLSTATE: 58009 Message: [jcc][t4][2034][11148][3.72.44] Execution failed due to a distribution protocol error that caused deallocation of the conversation. A DRDA Data Stream Syntax Error was detected. Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009 IBM DB2 JDBC Universal Driver Architecture(c) Copyright IBM Corporation 2003 – Apeksha Sep 23 '19 at 5:43
  • What's the output of the following commands on the db2 server? db2 get dbm cfg | grep -i svcename and db2set DB2COMM. What port do you use in the connection url? – Mark Barinstein Sep 23 '19 at 6:47
1

An error like this might be returned if you will try to connect to a port which is actually not the port DB2 server listens on. E.g. if I would, by mistake, try to connect to port 22 (ssh) I will get the same error:

$ java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password passw0rd

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password ********
[jcc][10512][13714]Failed to create connection.
  SQLCODE: -4499
  SQLSTATE: 58009
  Message: [jcc][t4][2034][11148][3.72.52] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

You can check the port Db2 listens on with:

 db2 get dbm cfg | grep SVCENAME
 TCP/IP Service name                          (SVCENAME) = 60111

Db2 will listen on this port if you have DB2COMM=TCPIP set. On there server side you can verify that e.g. with lsof:

$ lsof -p <pid_of_db2sysc> -iTCP -sTCP:LISTEN -a

(all sockets in listening state and -a to AND selections) e.g.

$ lsof -p 10661  -iTCP -sTCP:LISTEN -a
COMMAND   PID    USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
db2sysc 10661 db2v111    3u  IPv4 39727390      0t0  TCP *:60111 (LISTEN)

If the server listens only for SSL connections (only SSL_SVCENAME set in dbm cfg) then you can test that connection to Db2's SSL port does work e.g. with OpenSSL client:

$ openssl s_client -connect localhost:61111
CONNECTED(00000003)
depth=0 C = CA, ST = ON, L = myLocation, O = myOrganization, OU = myOrganizationUnit, CN = DB2INSTANCE
verify error:num=18:self signed certificate
verify return:1
depth=0 C = CA, ST = ON, L = myLocation, O = myOrganization, OU = myOrganizationUnit, CN = DB2INSTANCE
verify return:1

If it is OK then test from Java:

~/sqllib/java/jdk64/bin/java -cp ~/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true\; -user db2v111 -password XXXX


[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true; -user db2v111 -password ********


[jcc][10512][13714]Failed to create connection.
  SQLCODE: -4499
  SQLSTATE: 08001
  Message: [jcc][t4][2030][11211][4.25.1301] A communication error occurred during operations on the connection's underlying socket, socket input stream, 
or socket output stream.  Error location: Reply.fill() - socketInputStream.read (-1).  Message: com.ibm.jsse2.util.h: PKIX path building failed: java.security.cert.CertPathBuilderException: unable to find valid certification path to requested target. ERRORCODE=-4499, SQLSTATE=08001

not quite there - you need also sslCertLocation that points to certificate of the database:

~/sqllib/java/jdk64/bin/java -cp ~/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true;sslCertLocation=/home/db2v111/db2ssl/mydbserver.arm;" -user db2v111 -password XXXX


[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true;sslCertLocation=/home/db2v111/db2ssl/mydbserver.arm; -user db2v111 -password ********


[jcc][10516][13709]Test Connection Successful.

DB product version = SQL110144
DB product name = DB2/LINUXX8664
DB URL = jdbc:db2://127.0.0.1:61111/SAMPLE
DB Drivername = IBM Data Server Driver for JDBC and SQLJ
DB OS Name = Linux
| improve this answer | |
  • Hi, output of above command is: $ db2 get dbm cfg | grep SVCENAME TCP/IP Service name (SVCENAME) = SSL service name (SSL_SVCENAME) = db2c_coru1db2 i.e no value for TCP/IP Service name – Apeksha Sep 24 '19 at 8:14
  • I've added SSL part to answer above. – kkuduk Sep 24 '19 at 10:40
  • Hi, can you let me know whats 61111 in your example? -url jdbc:db2://127.0.0.1:61111/ ********* I am getting invalid URL syntax error ******************./java -cp /db2/home/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:db2c_coru1db2/SAMPLE:sslConnection=true\;sslCertLocation=/db2/coru1db2/ssl/mydbserver.arm -user coru1db2 -password ********* – Apeksha Sep 26 '19 at 4:10
  • if I am using------------- ./java -cp /db2/coru1db2/home/coru1db2/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:22/core01db -user fitusr1 -password ********------------errror----------> SQLCODE: -4499 SQLSTATE: 58009 Message: [jcc][t4][2034][11148][4.24.92] Execution failed due to a distribution protocol error that caused deallocation of the conversation. A DRDA Data Stream Syntax Error was detected. Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009 – Apeksha Sep 26 '19 at 4:21
  • I used command ------ $ getent services db2c_coru1db2 --------db2c_coru1db2 50020/tcp **it provides it port as 50020/tcp – Apeksha Sep 26 '19 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.