DB2 Connection TCP/IP Error

[mathiasyeremiaaryadi]

Hi, I have a Golang App that connected to the DB2 by using the go_ibm_db library with below connection string: Hostname=%s;Database=%s;Port=%s;UID=%s;PWD=%s;Protocol=TCPIP

When I deployed the Golang App, I have this error: SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "<IP DB2 database>". Communication function detecting the error: "recv". Protocol specific error code(s): "110", "*", "*". SQLSTATE=08001

These are the current condition:

1. The Golang App is on the Docker and deployed to Kubernetes
2. Between the Kubernetes and the DB2, they are on the different cluster and server
3. I have whitelisted or opened the firewall between the Kubernetes router to the DB2 and DB2 to the Kubernetes load balancer

Step that I have produced:

1. Tried to telnet/ping from the Kubernetes worker to the DB2 IP and it is connected
2. Checked the DB2_COMM registry and set to TCPIP
3. Checked the db2diag and there was no activity log detected on the db2diag when the Golang App attempted to connect, I can suspect that the connection couldn't even reach the DB2

In summary, the telnet/ping is connected on the Kubernetes worker terminal, however the Golang App can't reach the DB2.

[bimalkjha]

@mathiasyeremiaaryadi Please share output of below commands from db2 server system:

db2level
db2 connect to <dbname> user <dbuser> using <dbpasswd>
db2 terminate
db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

From client system, where go_ibm_db is installed:

export PATH=/home/uname/go/src/github.com/ibmdb/clidriver/bin:$PATH
export LD_LIBRARY_PATH=/home/uname/go/src/github.com/ibmdb/clidriver/lib:$LD_LIBRARY_PATH
db2level
db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

Replace /home/uname/go/src/github.com/ibmdb/clidriver in above export commands with actual path of clidriver in your system. Thanks.

[mathiasyeremiaaryadi]

@bimalkjha Hi, thanks for the reply, from the client system I use the Docker and I wrote the Docker setup as follows:

FROM <golang image> as builder

RUN GOCACHE=OFF

COPY . $GOPATH/src/bitbucket.co.id/app/
WORKDIR $GOPATH/src/bitbucket.co.id/app/

COPY . .

RUN tar -xzf driver/linuxx64_odbc_cli.tar.gz -C /

ENV IBM_DB_HOME "/clidriver"
ENV CGO_CFLAGS "-I$IBM_DB_HOME/include"
ENV CGO_LDFLAGS "-L$IBM_DB_HOME/lib"
ENV LD_LIBRARY_PATH "/clidriver/lib"

RUN CGO_ENABLED=1 GOOS=linux GOARCH=amd64 go build -mod=vendor -a -o /go/bin/app .

FROM <golang image>

COPY --from=builder /go/bin/app /go/bin/app
COPY --from=builder /go/src/bitbucket.co.id/app/timezone/ /go/bin/timezone/
COPY --from=builder /go/src/bitbucket.co.id/app/helm /go/bin/helm
COPY --from=builder /clidriver /clidriver

WORKDIR /go/bin/
RUN cp -r -f timezone/Asia/Jakarta /etc/localtime && echo "Asia/Jakarta" > /etc/timezone && date

ENV IBM_DB_HOME "/clidriver"
ENV CGO_CFLAGS "-I$IBM_DB_HOME/include"
ENV CGO_LDFLAGS "-L$IBM_DB_HOME/lib"
ENV LD_LIBRARY_PATH "/clidriver/lib"

ENTRYPOINT `["/go/bin/app"]`

Maybe you can review my DB2 setup on the Docker, and I put the clidriver on the outer path level of the Golang application, here the clidriver position before I put it on the Docker:

/

main.go

usecase

repository

delivery

config

clidriver

other app files

Then here the clidriver position after I put it on the Docker:

For the DB2, I will give the output as soon I run the command which you suggested.

Thanks

[bimalkjha]

@mathiasyeremiaaryadi Please share output of below commands from client system where go_ibm_db is installed:

export PATH=/clidriver/bin:$PATH
db2level
db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

Thanks.

[mathiasyeremiaaryadi]

@bimalkjha Here are the command output that you suggested before:

On DB2 Client

db2level

db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

On the Golang App

db2level

db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

Thanks.

[bimalkjha]

@mathiasyeremiaaryadi Thanks for sharing the output of db2cli validate commands from both systems. As you can see in the last screenshot, the validate command is returning SQL30081N error same as you reported in first post. Since, /clidriver is unable to connect to Db2 server on given port, go app is throwing error. It has nothing to do with the go driver rather looks some docker setup issue which is not allowing request from your container to go outside the container. Run ping <hostname> command from go app terminal and see, are you able to ping the host or not? If host is not pingable, then it could be the issue.

The protocol specific error code 110 in the error message SQL30081N says that client keeps waiting to receive response from server but did not get any connection reply from server and hence connection timeout happened.

We can increase the timeout value and try if issue is due to slow response of server. You can add ConnectTImeout=120; in the connection string of go test file or try using below commands:

db2cli writecfg add -parameter "ConnectTimeout=120"
db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

Here, 120 is the timeout value in seconds. You can increase its value and try. Thanks.

[mathiasyeremiaaryadi]

@bimalkjha Thanks for the great insight. So if I can pull out from your solution, I can suspect that the issue happened on the Docker container and caused my Golang App DB2 connection can't go outside the container to reach the DB2. Then I have several question regarding your steps:

1. Which host should I ping ? is it the Golang App itself that inside the container ? or the DB2 host ?
2. For the ConnectTimeout parameter, so I can try to add it on my Golang App my connection string like this one Hostname=%s;Database=%s;Port=%s;UID=%s;PWD=%s;Protocol=TCPIP;ConnectionTimeout=120? Or the one you mentioned that is on the go test file? Where I can locate the go test file ?

Thanks.

[vmathur12]

@mathiasyeremiaaryadi You need ping to hostname (host name of your database server) which you are providing in the following command.

db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect

db2cli writecfg add -parameter "ConnectTimeout=120" - This command will create a "db2dsdriver.cfg" file in /clidriver/cfg and add parameter ConnectionTimeout. Thanks

[mathiasyeremiaaryadi]

@vmathur12 @bimalkjha Here are the ping result from the Golang App that you asked, using 3 approaches: nmap, telnet, & nc, the result seems connected. I haven't implemented the Timeout yet.

[bimalkjha]

@mathiasyeremiaaryadi

1. Which host should I ping ? is it the Golang App itself that inside the container ? or the DB2 host ?

A=> We need output of ping host.com command. Replace host.com with the name of your db2 host name as used in connection string. In the above shared screen shot, we can read this message - Note: Host seems down. ... Nmap done: 1 IP address (0 hosts up).... setup_target: failed to dermine route to 0.0.195.82. It tells that your host is not pingable causing SQL30081N error.

2. For the ConnectTimeout parameter, so I can try to add it on my Golang App my connection string like this one Hostname=%s;Database=%s;Port=%s;UID=%s;PWD=%s;Protocol=TCPIP;ConnectionTimeout=120? Or the one you mentioned that is on the go test file? Where I can locate the go test file ?

A=> Either you add ConnectTimeout in the connection string like Hostname=%s;Database=%s;Port=%s;UID=%s;PWD=%s;Protocol=TCPIP;ConnectTimeout=120 and then run your golang app, OR run db2cli writecfg add -parameter "ConnectTimeout=120" to write connectTimeout value to the internal configuration file of go_ibm_db driver. If you are opting to run db2cli writecfg ... command, then no need to add connectTImeout in connection string and existing connection string should work. We have both options to specify connection time out. You can choose either. Note that the keyword name is ConnectTimeout and not ConnectionTimeout, so use the right keyword in connection string or db2cli writecfg command, whichever you are using. Thanks.

[mathiasyeremiaaryadi]

@bimalkjha Okay, but why the other approahces like telnet and nc yielded a success connection ?

In addition, I have implemented the ConnectTimeout parameter on my configuration but the same error still exist.

Thanks

[bimalkjha]

I don't know difference between nc, nmap and ping. Better to check documentation about it online, but in the nmap output too we can see Host seems down message. Since, host is unreachable, go_ibm_db fails to connect and connectTimeout will not have any affect. Thanks.

[mathiasyeremiaaryadi]

@bimalkjha In summary, the issue occurred because on the container network which blocked the Golang App DB2 connection and it is not the DB2 or the driver problem. Is is possible I might missed the some of the host IP on the firewall rule?

[bimalkjha]

@mathiasyeremiaaryadi Yes, your understanding is correct. It is not a Db2 or driver problem.

The host ip is only one which is the IP Address of the system on which Db2 server is installed. Since, you shared the output of db2cli validate -database "<dbname>:<hostname>:<port>" - user <dbuser> -passwd <dbpass> -connect command from Db2 server system and we can see the result of this validate command is SUCCESS on Db2 server system, your hostname is correct.

I don't have idea about how you can set the firewall rule, please refer documentation or correct team about that. You can know the ip address of the hostname used in validate command by running ifconfig command on non-windows system and ipconfig command on windows system. Check for IPv4 Address which is the IP address for that host. Thanks.