Overview:
This article shows how to manually setup Secure Socket Layer (SSL) on the Big SQL HA second head node and enable JDBC automatic client reroute connection via SSL. The following procedure assumes Big SQL installed with no second head node added yet.

Procedure:

  1. On the Big SQL first head node (first_head_node), configure SSL. As the bigsql user, run the following:
    • Setup environment variables
      1. export PATH=$PATH:/home/bigsql/sqllib/gskit/bin
      2. export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/bigsql/sqllib/gskit/bin
    • Create the server keystore and certificate
      1. mkdir ~/bigsql_server_SSL
      2. cd ~/bigsql_server_SSL
      3. gsk8capicmd_64 -keydb -create -db “bigsqldb.kdb” -pw “bigsqldb” -stash
      4. gsk8capicmd_64 -cert -create -db “bigsqldb.kdb” -pw “bigsqldb” -label “biginsights” -dn “CN=`hostname -f`” -default_cert yes
      5. gsk8capicmd_64 -cert -extract -db “bigsqldb.kdb” -pw “bigsqldb” -label “biginsights” -target “bigsqldb.arm” -format ascii -fips
      6. keytool -genkey -alias biginsights -keystore KeyStore.jks -storepass bigsql -noprompt
    • Configure the DB2 environment
      1. db2 update dbm cfg using SSL_SVR_KEYDB $HOME/bigsql_server_SSL/bigsqldb.kdb
      2. db2 update dbm cfg using SSL_SVR_STASH $HOME/bigsql_server_SSL/bigsqldb.sth
      3. db2 update dbm cfg using SSL_SVR_LABEL biginsights
      4. db2 update dbm cfg using svcename null
      5. db2 update dbm cfg using SSL_SVCENAME 51000
      6. db2set db2comm=SSL
    • Restart Big SQL
      1. $BIGSQL_HOME/bin/bigsql stop
      2. $BIGSQL_HOME/bin/bigsql start
  2. Add the Big SQL second head node (second_head_node) via the Ambari UI. Instruction to do this is in the blog
    “Enabling and Using Big SQL High Availability in Ambari”
  3. On the Big SQL second head node (second_head_node), configure SSL. As the bigsql user, run the following:
    • Copy the certificates from the first_head_node to the second_head_node
      1. mkdir ~/bigsql_server_SSL
      2. cd ~/bigsql_server_SSL
      3. scp bigsql@first_head_node:/home/bigsql/bigsql_server_SSL/* ~/bigsql_server_SSL
      4. $BIGSQL_HOME/bin/bigsql stop
      5. $BIGSQL_HOME/bin/bigsql start
  4. On the client DB2 instance, generate client SSL certificates. As client instance user, run the following:
    • Create a client-side keystore database and add the downloaded client key certificate to the keystore
      1. mkdir ~/bigsql_client_SSL
      2. cd ~/bigsql_client_SSL
      3. copy over the BigSQL head node file ~/bigsql_server_SSL/bigsqldb.arm into ~/bigsql_client_SSL
      4. gsk8capicmd_64 -keydb -create -db “keyclient.kdb” -pw “bigsqldb” -stash
      5. gsk8capicmd_64 -cert -add -db “keyclient.kdb” -pw “bigsqldb” -label “bigsqlclt” -file bigsqldb.arm -format ascii -fips -trust enable
      6. keytool -genkey -alias biginsights -keystore truststore.jks -storepass changeit -noprompt
      7. keytool -import -trustcacerts -alias <head node -file ./bigsqldb.arm -keystore ./truststore.jks -storepass changeit -noprompt
    • Configure the DB2 environment
      1. db2 update dbm cfg using SSL_CLNT_KEYDB $HOME/bigsql_client_SSL/keyclient.kdb
      2. db2 update dbm cfg using SSL_CLNT_STASH $HOME/bigsql_client_SSL/keyclient.sth
  5. To use IBM JCC SSL connection and enable automatic client reroute, ensure the following are set.
    1. In the connection URL, ensure the following settings.
      • Server = first_head_node
      • Database = bigsql
      • PortNumber = 51000
      • property sslConnection = true

      e.g. jdbc:db2://first_head_node:50001/bigsql:user=user_name;password=password;sslConnection=true;

    2. In the IBM JCC application, set the following:
      • javax.net.ssl.trustStore = $HOME/bigsql_client_SSL/truststore.jks
      • javax.net.ssl.trustStorePassword = changeit
      • clientRerouteAlternateServerName = second_head_node
      • clientRerouteAlternatePortNumber = 51000
      • enableSeamlessFailover = yes
      • blockingReadConnectionTimeout = 1200

Join The Discussion

Your email address will not be published. Required fields are marked *