Connection Error while connecting SQL Developer with DB2

Question

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?

Answer 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