Overview

Skill Level: Any Skill Level

This recipe provides a guide for configuring Information Server DataStage to connect to dashDB as a source or target within a job design.

Ingredients

This recipe provides connectiviting to dashDB using the DB2 CLI.  JDBC/ODBC connectivity is also possible to configure, but is not as performant as using the DB2 CLI along with the DB2 Connector.

  1. Information Server 11.5 or later
  2. dashDB connection (either dashDB local or dashDB)

Step-by-step

  1. Select a DB2 Client Package

    The first step is to download and install a DB2 client onto the DataStage Engine tier.  DB2 Client packages can be download directly from dashDB console, or if you wish to have access to a DB2 CLP (Command Line Processor), download the client directly from DB2.  dashDB recommends using a 11.1 client or later, so download/use the latest

    To access all features within the DB2 CLI, download and install the “IBM Data Server Runtime Client” as this will provide a DB2 CLP. 

    Other packages of the DB2 client such as “IBM Data Server Driver for ODBC and CLI” can be used, but the DB2 CLI provides only a subset of functionality, with a few limitations.  Notably, with this particular package, no DB2 CLP is provided and connections must be configured using the db2dsdriver.cfg configuration file instead of DB2 Catalog Entries.

    This receipe will focus on using the IBM Data Server Runtime Client and configuring connections to dashDB through Catalog entries.  See the DB2 or dashDB documentation for additional instructions if you wish to connect using db2dsdriver.cfg

  2. Install DB2 Client Package

    Once downloaded, extract the package:

    pwd
    /home/dsadm
    ls -l v11.1.1fp1a_linuxx64_rtcl.tar.gz
    -rw-r--r-- 1 dsadm dstage 313347799 Apr 28 13:19 v11.1.1fp1a_linuxx64_rtcl.tar.gz
    tar xvzf v11.1.1fp1a_linuxx64_rtcl.tar.gz
    cd rtcl
    ./db2_install

     Once the DB2 Client package is installed, you’ll have a sqllib directory such as /home/dsadm/sqllib

    The /home/dsadm/sqllib/db2profile can be sourced to setup the appropriate environment variables to use DB2.  You can also add a line to the /opt/IBM/InformationServer/Server/DSEngine/dsenv file so DataStage picks up the environment for DB2.

    To source the db2profile, execute:

    . /home/dsadm/db2profile

    You can add the same command to your dsenv file. Remember to restart the DataStage Engine and ASBNode Agent for the environment changes to take affect.

  3. Installing/Configuring IBM GSKit

    If you choose not to connect via SSL, you can skip this step and proceed to step 5 “DB2 Connection without SSL”

    Connecting via the CLI using SSL means that the CLI will need to trust the certificate signed/served by the dashDB server. The certificate can be downloaded from dashDB console. Download the SSL certificate from the Connect your applications to the database page.

    From the main menu, click Connect.
    Click Connection with SSL, and then click the SSL certificate link.

    Move the SSL certificate to a directory on the DataStage Engine. i.e. /home/dsadm/SSL

    In order to configure a SSL connection IBM GSKit (Global Secutiy Kit) must be installed and configured.

    Information Server DataStage also uses a version of GSKit, so care must be taken to ensure that all components installed on the DataStage Engine tier are using the same version of IBM GSKit.

    For this receipe, we will be downloading, installing, and using a version of GSKit that is compatible with the DB2 11.1 client, 8.0.50.57.

     

  4. DB2 Connection with SSL

    If you choose not to connect via SSL, you can skip this step and proceed to “Connection without SSL”

     

    Once you have downloaded GSKit for your Engine tier platform, execute the following commands:

    /opt/IBM/DB2/gskit/bin/gsk8capicmd_64 -keydb -create -db /home/[user]/dashDB.kdb -pw [password] -stash
    /opt/IBM/DB2/gskit/bin/gsk8capicmd_64 -cert -add -db /home/[user]/dashDB.kdb -pw [password] -label BLUDB_SSL -file DigiCertGlobalRootCA.crt
    db2 update dbm cfg using SSL_CLNT_KEYDB /home/[user]/dashDB/dashDB.kdb
    db2 update dbm cfg using SSL_CLNT_STASH /home/[user]/dashDB/dashDB.sth
    db2 catalog tcpip node dashdb remote dashdb-entry-yp-dal09-10.services.dal.bluemix.net server 50001 security ssl
    db2 catalog database BLUDB as dashdb at node dashdb

    Where:

    [password] = password
    [user] = Username, such as dsadm

  5. DB2 Connection without SSL

    Execute the following commands to catalog dashDB without SSL:

    db2 catalog tcpip node dashdb remote dashdb-[region].bluemix.net server 50000
    db2 catalog database BLUDB as dashdb at node dashdb
  6. Test Connection from DB2 CLP

    Execute the following command to test the DB2 catalog entry from the DB2 CLP (Cmmand Line Processor)

    db2 connect to dashdb user [user] 

    You will be prompted to enter your password.

    db2 list tables

    To validate you can list tables in the database.

    where:

    dashdb = the catalog entry you created in Steps #4 or #5

    [user] = username for dashDB

     

    Once you confirm connectivity to dashDB via the DB2 Client CLP, you are ready to configure the DB2 Connector within DataStage to connect to dashDB.

  7. Configure DB2 Connector

    The DB2 Connector can now be configured as a source or target to connect to dashDB.  Fill in the connection details to point the DB2 Connector at your DB2 client catalog entry.

     

    This screenshot shows the DB2 Connector being configured as a target stage for dashDB.  Note that all fields, database, username, password, etc can be parameterized for more reusable and maintainable job logic.

    DB2 Connector Properties

Join The Discussion