Wouldn’t it be nice to do some Java testing without writing a line of code?
For the following to work, it is necessary that db2jcc.jar and db2jcc_license_cu.jar are on the CLASSPATH. If you login as db2 instance user, these jar files should be in the CLASSPATH automatically. You can check the following.
db2psc@node01:~> echo $CLASSPATH /home/db2psc/sqllib/java/db2java.zip:/home/db2psc/sqllib/function: /home/db2psc/sqllib/java/db2jcc_license_cu.jar :/home/db2psc/sqllib/tools/clpplus.jar:/home/db2psc/sqllib/tools/jline-0.9.93.jar: /home/db2psc/sqllib/java/db2jcc.jar:.
Check version of the DB2 JCC driver
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -version IBM DB2 JDBC Universal Driver Architecture 3.67.26
When there are multiple JCC drivers in your application directory and someone asks you which version of JCC driver your application is using, here is the easy way to figure this out.
For example: If directory /home/db2psc/sqllib/java contains db2jcc.jar and you need to find out the version of this specific driver, use this command.
db2psc@node01:~> java -cp /home/db2psc/sqllib/java/db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version IBM DB2 JDBC Universal Driver Architecture 3.67.26
Test Type -2 connection to DB2 using Java
Connect using database name that is already cataloged. For example, PSDB in my case.
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2:PSDB -user db2psc -password password [jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2:PSDB -user db2psc -password ******** [jcc][10516][13709]Test Connection Successful. DB product version = SQL10052 DB product name = DB2/LINUXX8664 DB URL = jdbc:db2:PSDB DB Drivername = IBM DB2 JDBC Universal Driver Architecture DB OS Name = Linux
Test type -4 connection to DB2 using Java
Connect using either host name or IP address of the DB2 server, port number and database name (type – 4) . For example, I am going to use 10.77.19.12, 50001 and PSDB in my case. When using type -4, we are making a direct connection to the DB2 server without requiring db2 client unlike Type-2 which does require a DB2 client.
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://10.77.19.12:50001/PSDB
-user db2psc -password password
[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://10.77.19.12:50001/PSDB
-user db2psc -password ********
[jcc][10516][13709]Test Connection Successful.
DB product version = SQL10052
DB product name = DB2/LINUXX8664
DB URL = jdbc:db2://10.77.19.12:50001/PSDB
DB Drivername = IBM DB2 JDBC Universal Driver Architecture
DB OS Name = Linux
Test Client Reroute
There are 2 ways through which we can make client reroute work. 1. – By using DB2 server UPDATE ALTERNATE SERVER command for Type-2 or CLI applications or 2. By using connection properties for a data source through the java program. My personal choice is to use the client properties than to use the first method. The reason I like second method is that I have a much better control as what I am doing than the first method.
Please note: For type-2 Java or CLI applications (non-Java), it is now preferable to use db2dsdriver.cfg file to define alternate servers along with many other properties. Even though UPDATE ALTERNATE SERVER is still supported, it is a primitive (old) method.
Test Client Reroute
At db2 server, update alternate server information. Assume that alternate server is either a HADR pair or a pureScale member, another DB2 server which is exact copy using replication or another db2 server using flash copy.
db2psc@node01:~/> db2 update alternate server for db psdb using hostname 10.77.19.13 port 50001 db2psc@node01:~> db2 list db directory System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = PSDB Database name = PSDB Node name = PSNODE Database release level = 10.00 Comment = Directory entry type = Remote Catalog database partition number = -1 Alternate server hostname = 10.77.19.13 Alternate server port number = 50001
Stop DB2 in host 10.77.19.12.
Connect to DB2 server using CLI. Notice that the actual connection was made to the node03 which is 10.77.19.13.
db2psc@node01:~> db2 connect to psdb user db2psc using password
Database Connection Information Database server = DB2/LINUXX8664 10.5.2 SQL authorization ID = DB2PSC Local database alias = PSDB db2psc@node01:~> db2 get connection state Database Connection State Connection state = Connectable and Connected Connection mode = SHARE Local database alias = PSDB Database name = PSDB Hostname = 10.77.19.13 Service name = 50001
Connect to the DB2 server using a type-2 Java connection.
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2:PSDB
-user db2psc -password password
[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2:PSDB
-user db2psc -password ********
[jcc][10516][13709]Test Connection Successful.
DB product version = SQL10052
DB product name = DB2/LINUXX8664
DB URL = jdbc:db2:PSDB
DB Drivername = IBM DB2 JDBC Universal Driver Architecture
DB OS Name = Linux
Even if DB2 server is down on node02, connection still succeeded as it connected to node03 automatically since we had defined ALTERNATE SERVER. Please remember that we did the type-2 connection. Now, let us try with Type-4 connection.
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://10.77.19.12:50001/PSDB
-user db2psc -password password
[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://10.77.19.12:50001/PSDB
-user db2psc -password ********
[jcc][10512][13714]Failed to create connection.
SQLCODE: -4499
SQLSTATE: 08001
Message: [jcc][t4][2043][11550][3.67.26] Exception java.net.ConnectException:
Error opening socket to server /10.77.19.12 on port 50,001
with message: Connection refused. ERRORCODE=-4499, SQLSTATE=08001
It is obvious that the error -4499 is about connection refused obviously since db2 is down. So, connection failed since Java client does not know about the alternate servers as type-2 did. Let us now define the alternate server for the java client.
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -url 'jdbc:db2://10.77.19.12:50001/PSDB:
clientRerouteAlternateServerName=10.77.19.13;clientRerouteAlternatePortNumber=50001;'
-user db2psc -password password
[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://10.77.19.12:50001/PSDB:
clientRerouteAlternateServerName=10.77.19.13;clientRerouteAlternatePortNumber=50001;
-user db2psc -password ********
[jcc][10516][13709]Test Connection Successful.
DB product version = SQL10052
DB product name = DB2/LINUXX8664
DB URL = jdbc:db2://10.77.19.13:50001/PSDB
DB Drivername = IBM DB2 JDBC Universal Driver Architecture
DB OS Name = Linux
High Availability for Stand Alone DB2
Now, the most interesting part of client re-route for DB2 which has no HADR or pureScale or any other alternate server. Consider this: You have a standalone DB2 and it goes down for any reason either due to a crash or some one doing db2_kill or kill -9 db2sysc process and clients which were connected to DB2 looses connections and application fails.
Assuming that you have either Tivoli System Automation enabled for stand alone DB2 (Which I will explain sometime in another post as how to do that) or you have DB2 fault monitor up and running. Or, you have DB2 Active / Passive using TSA and when such a failover is happening, what happens to clients – They all fail and the application developers sometime write extra logic to retry connections. All that is not necessary as JCC can handle this. Let us take this example:
db2psc@node01:~> java com.ibm.db2.jcc.DB2Jcc -url 'jdbc:db2://10.77.19.12:50001/PSDB: clientRerouteAlternateServerName=10.77.19.12;clientRerouteAlternatePortNumber=50001; maxRetriesForClientReroute=5;retryIntervalForClientReroute=60;' -user db2psc -password password [jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://10.77.19.12:50001/PSDB: clientRerouteAlternateServerName=10.77.19.12;clientRerouteAlternatePortNumber=50001; maxRetriesForClientReroute=5;retryIntervalForClientReroute=60; -user db2psc -password ********
Now, we know that we stopped DB2 on node02 but we specified clientRerouteAlternateServerName=10.77.19.12 which is same as the node02. And, we have maxRetriesForClientReroute=5 and retryIntervalForClientReroute=60. The above command will wait for 5×60=300 seconds before throwing -4499 error. If during this time, DB2 comes back again, the connection would succeed.
By using above approach, you can build a high availability for your java application by using client reroute driver properties as shown above. The time interval of 300 seconds was taken as an example of active/passive DB2 using either HACMP or TSA and in those situations, client would just wait maximum for 5 minutes before it throws -4499 error.
Now, what happens if DB2 server goes down in the middle of the transaction, the JCC driver will wait for up to 300 seconds and connect again to the same server if it is able to come back within 300 seconds and client will not receive -4499 error. However, the client will receive -4498 error saying that the connection was reestablished but the current transaction was rolled back. In such a case, the Java application should have the retry logic for the transaction. Please note: No retry logic for the connection but the retry for the transaction if application receives error -4498. The equivalent error for CLI applications (non-Java) is SQL30108N error.