check JDBC connection about streamsx.jdbc

[anouri]

Corrections have been checked in develop branch.

[anouri]

The JDBC toolkit has been improved with a new feature to check the JDBC connection.
The JDBCRun operator has now a new optional parameter (checkConnetion) to check the JDBC connection.
When this parameter is “true” the operator starts at begin a thread to check the connection periodic and in case of any communication problem it sends a code and a string to the SPL application.
It is possible from SPL application to abort the Streams PE or send a notification to other systems.

[anouri]

JDBCConnection tests

The following test have been performed on the customer environments:

• Successfully created JDBCConnection project using StreamsStudio

• Test 1: connecting to a running DB2 instance w/ JdBCRUN_INSERT table created
• program runs to completion inserting records 0 -> 19
• works as expected

• Test 2: DB2 not up and running (or if host/port are incorrect)
• tries to connect 3 times, then aborts

• Test 3: create an ssh tunnel from streams server to DB2 server and rerun Test 1 (this will test that we have a working DB2 connection over the tunnel)
• from Streams servert : ssh -L 7466:localhost:50000 DB2Server
• jdbcUrl: jdbc:db2://localhost:7466/DBNAME:retrieveMessagesFromServerOnGetMessage=true;
• works as expected

• Test 4: modify program to sleep 6s between inserts (all inserts take 2 min) -- this gives me time to block connections during the following tests
• used: block(6.0) in DataGen
• works as expected

• Test 5: still using ssh tunnel to DB2 Server, we'll now suspend the ssh tunnel
• kill -19 suspends the ssh tunnel and kill -18 continues it as expected
• however, checkConnection doesn't appear to work (waited >360s)
• What is this testing? Does a suspended ssh tunnel act like a blocked port
(probably no -- see test 6)?
In this case if you cal a part of ssh connection, the DB2 JDBC deriver doesn’t get any information about a not valid communication.

Is the OS still accepting connections for the tunnel (yes -- see test 6) ?

• Test 6: still using ssh tunnel, kill the tunnel completely. This should answer the last question of Test 5.
• upon killing the connection, the JDBC driver detects the communication error on the socket. the JDBC checkConnection tries 3 times to reconnect, then aborts
• works as expected

• Test 7: actually shut db2 down while the test is running (not over the ssh tunnel)
• requires exclusive access to allstack2, operator to shutdown db2
• I strongly suspect this will result in the same result as test 6 since the checkConnection attempt will fail
• login as instance owner on DB2 server for example
ssh db2inst1@DB2Server
db2stop force
In this test the checkConnection feature get invalid connection and this will be submitted to the SPL application.

• Test 8: connect via ssh tunnel, let inserts complete, and then suspend/kill the connection while checkConnection is continually checking. Simulating what happens during an idle period
• part 1 -- susp/resume has the same issue as test 5. The connection does not fail or timeout and continues once the tunnel is continued
• part 2 -- if the tunnel is killed, then we get the same result as test 6. 3 attempts to connect and then an abort
A timeout has been implemented to check valid connection.

[anouri]

The JDBCRun operator has now a Timeout to check the JDBC connection.

For DB2 it is strongly recommended to use the latest JDBC drive.
https://www-01.ibm.com/support/docview.wss?uid=swg21385217

How to create a ssh tunnel to access to the DB2 database :

     ssh -f db2inst1@your-db2-server -L 7466:localhost:50000 -N

It asks you about the DB2 password.
Enter your DB2 instance owner password (in this example the password for db2inst1)

In this command.
-f                   Requests ssh to go to background just before command execution. 
db2inst1        is the DB2 instance owner
db2-server     is your DB2 database server
50000            is the DB2 default port

Check the ssh tunnel

netstat -an | grep -iw 7466
tcp        0      0 127.0.0.1:7466          0.0.0.0:*               LISTEN     
tcp6       0      0 ::1:7466                :::*                    LISTEN  

Make and start an spl application with checkConnection : true;
and
jdbcUrl", "jdbc:db2://localhost:7466/YOURDBNAME:retrieveMessagesFromServerOnGetMessage=true;"

During the test check out the ssh-tunnel process id with this command:

ps -elf | grep 7466 | grep -v 'grep' | awk -F" " '{print $4}'

And

   kill -19 ssh-tunnel-process-id

In this case the JDBCRun operator get a message from JDBC driver (invalid connection) and it tries to create new connection with reconnectionPolicy parameters.
In case of failed connection, it submits the invalid connection code and message via 2. Output port to the SPL application.

If you perform this command during the reconnection procedure.

   kill -18 ssh-tunnel-process-id

The JDBCRun operator creates a new JDBC connection and goes ahead.

Here is a short description about the SIGSTOP (kill -19) and SIGCOUNT (kill -18) from Wikipedia:

When SIGSTOP is sent to a process, the usual behavior is to pause that process in its current state. The process will only resume execution if it is sent the SIGCONT signal. SIGSTOP and SIGCONT are used for job control in the Unix shell, among other purposes. SIGSTOP cannot be caught or ignored.

When SIGSTOP or SIGTSTP is sent to a process, the usual behavior is to pause that process in its current state. The process will only resume execution if it is sent the SIGCONT signal. SIGSTOP and SIGCONT are used for job control in the Unix shell, among other purposes.

[anouri]

Feedback from customer:
Thank you. Confirmed last night that this does work if the application/tunnel is suspended.

[anouri]

The correction delivered in streamsx.jdbc toolkit version 1.4.0
https://github.com/IBMStreams/streamsx.jdbc/releases/tag/v1.4.0