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?

• Lennart - Slava Ukraini: 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)
• Apeksha: I am able to connect via telnet using same host and port.
• Mark Barinstein: 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?
• Apeksha: @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
• Mark Barinstein: 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?

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

• Apeksha: 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
• kkuduk: I've added SSL part to answer above.
• Apeksha: 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: 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: I used command ------ $ getent services db2c_coru1db2 --------db2c_coru1db2 50020/tcp **it provides it port as 50020/tcp