Connecting to IBM tools

Prerequisites

It is advised that you update DataStage to the most recent version so that you can take advantage of external tables to load your data into Db2 Warehouse on Cloud.

If you don’t already have a data server client that is installed, download and install the IBM Data Server Client that is appropriate for your client machine’s operating system: IBM Data Server Client.

To make connections with the SSL protocol, download and install the 32-bit GSKit V8. Click the OS tab that is appropriate for your client machine’s operating system: GSKit V8 - Install, Uninstall and Upgrade instructions. For the following operating systems, ensure that you add the GSKit installation directory path to the OS-specific path environment variable:

• AIX®: LIBPATH
  • /usr/opt/ibm/gsk8/lib
• Linux: LD_LIBRARY_PATH
  • /usr/local/ibm/gsk8/lib
• UNIX: LD_LIBRARY_PATH
  • /opt/ibm/gsk8/lib
• Windows: PATH
  • <installation_directory>\gsk8\bin
  • <installation_directory>\gsk8\lib

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

• To create a connection with SSL, complete the following steps:

  1. Open a command line or terminal and make a new directory in the DataStage system to store the SSL certificate and key files.

  # /home/db2inst2> mkdir SSL

  # /home/db2inst2> cd SSL

  2. In the Db2 Warehouse on Cloud web console, download the SSL certificate from the Connect your applications to the database page.

  a. From the main menu, click Connect.

  b. Click Connection with SSL, and then click the SSL certificate (1 KB) link.

  c. Save the DigiCertGlobalRootCA.crt certificate into the SSL directory that you made in step 1.

  3. Create a client keystore database in the DataStage system by using the gsk8capicmd_64 utility.

  # /home/db2inst2/SSL> gsk8capicmd_64 -keydb -create -db <keystore_db.kdb> -pw <ks_db_password> -stash

  where <keystore_db.kdb> represents the client keystore database and <ks_db_password> represents the password for the client keystore database.

  4. Add the certificate to the client keystore database.

  # /home/db2inst2/SSL> gsk8capicmd_64 -cert -add -db <keystore_db.kdb> -pw <ks_db_password> -label BLUDB_SSL -file DigiCertGlobalRootCA.crt

  where <keystore_db.kdb> represents the client keystore database and <ks_db_password> represents the password for the client keystore database.

  5. Configure the Db2 client on the DataStage server.

  a. Update the SSL configuration parameters in the database manager.

  # /home/db2inst2> db2 update dbm cfg using SSL_CLNT_KEYDB /home/db2inst2/SSL/<keystore_db.kdb>

  where <keystore_db.kdb> represents the client keystore database.

  # /home/db2inst2> db2 update dbm cfg using SSL_CLNT_STASH /home/db2inst2/SSL/<keystore_db.sth>

  where <keystore_db.sth> represents the client keystore database password stash.

  b. Catalog the target node with the SSL security option and then the BLUDB database at that target node.

  # /home/db2inst2> db2 catalog tcpip node <node_name> remote <IP_addr_of_BLUDB_database_server> server 50001 security SSL

  where <node_name> represents your name for the target node and <IP_addr_of_BLUDB_database_server> represents the IP address of the BLUDB database server,

  # /home/db2inst2> db2 catalog db BLUDB as <db_alias> at node <node_name>

  where <db_alias> is your name for the Db2 Warehouse on Cloud database.

  6. Add read and execute permissions on the files in the SSL directory for everyone. The DataStage user who runs the jobs needs to access these files to make SSL connections to the Db2 database.

  # /home/db2inst2/SSL> chmod 655 /home/db2inst2/SSL/*

  7. Test the SSL connection in one of the following ways:

  • Test the connection by using CLP. Issue the following command to connect to the Db2 Warehouse on Cloud database:

    db2 connect to <db_alias> user <user_id>

    where <db_alias> is your name for the Db2 Warehouse on Cloud database and <user_id> is your Db2 Warehouse on Cloud user ID. You are prompted to enter your password.

  • Test the connection by using CLI. Issue the following command to connect to the Db2 Warehouse on Cloud database:

    db2cli validate -dsn <alias> -connect -user <user_id> -passwd <password>

    where <alias> is an alias that you created by using the db2cli writecfg command, <user_id> is your Db2 Warehouse on Cloud user ID, and <password> is your Db2 Warehouse on Cloud password.

• To create a connection without SSL, catalog the target Db2 Warehouse on Cloud database by completing the following steps:

  1. Catalog the target Db2 Warehouse on Cloud node so that client applications can connect to it. Run the following CLP commands:

  db2 catalog tcpip node <node_name> remote <IP_address_of_BLUDB_database_server> server <port_number_of_BLUDB_database>

  where <node_name> represents your name for the node, <IP_address_of_BLUDB_database_server> represents the IP address of the BLUDB database server, and <port_number_of_BLUDB_database> represents the port number of the BLUDB database.

  2. Catalog the remote Db2 Warehouse on Cloud database so that client applications can connect to it. Run the following command:

  db2 catalog db BLUDB as <db_alias> at node <node_name>

  where <db_alias> represents your name for the Db2 Warehouse on Cloud database and <node_name> represents your name for the node.

  3. Test the non-SSL connection in one of the following ways:

  - Test the connection by using CLP. Issue the following command to connect to the Db2 Warehouse on Cloud database:
  
    `db2 connect to <db_alias> user <user_id>`
  
    where `<db_alias>` is your name for the Db2 Warehouse on Cloud database and `<user_id>` is your Db2 Warehouse on Cloud user ID. You are prompted to enter your password.
  
    `db2 list tables`
  
  - Test the connection by using CLI. Issue the following command to connect to the Db2 Warehouse on Cloud database:
  
    `db2cli validate -dsn <alias> -connect -user <user_id> -passwd <password>`
  
    where `<alias>` is an alias that you created by using the **db2cli writecfg** command, `<user_id>` is your Db2 Warehouse on Cloud user ID, and `<password>` is your Db2 Warehouse on Cloud password.
  

  4. Use the connection information that you collected beforehand to define a connection in the DataStage client. On the Parameters tab, you must select the DB2 Connector for the Connect using Staging Type field.

  For details about defining a connection in DataStage, see the following DataStage documentation topics:

  • Creating a data connection object manually
  • Configuring access to Db2 databases

Overview

Ideally, when you connect IBM InfoSphere Data Replication to Db2 Warehouse on Cloud, IBM InfoSphere Data Replication is in the same IBM Cloud Data Center as Db2 Warehouse on Cloud or is colocated with Db2 Warehouse on Cloud. IBM InfoSphere Data Replication connects from a local server to the remote Db2 Warehouse on Cloud instance.

When you use Db2 Warehouse on Cloud as a connection target, the performance of IBM InfoSphere Data Replication partly depends on the bandwidth of the network that separates its target engine from the Db2 Warehouse on Cloud instance. Physical distance also affects performance: ideally, IBM InfoSphere Data Replication is as close as possible to the Db2 Warehouse on Cloud instance. Network topology also affects performance. For example, ideally, the IBM InfoSphere Data Replication target engine runs on a VM in the same VPN (security domain) as the target instance. The fewer the network nodes (for example, firewalls or routers) to traverse, the better.

Prerequisites

If you intend to connect by using the SSL protocol, download and install GSKit V8. See GSKit V8 - Install, Uninstall and Upgrade instructions. Click the operating system tab that applies to your client machine’s operating system. If you are installing the GSKit on a Windows computer, ensure that you specify the GSKit installation directory path (<installation_directory>\gsk8\bin) for the PATH environment variable.

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

If you intend to connect by using the SSL protocol, download the DigiCertGlobalRootCA.crt SSL certificate from the web console to a directory on the client machine. To download the certificate, click Connection > Connection Information and then click the Connection with SSL tab.

Procedure

1. Choose one of the following approaches to make your connection:

   • To create a connection with SSL, complete the following steps:

     a. Issue the following command:

     cd /<ssl_directory_name>/ssl

     where /<ssl_directory_name>/ssl is the path to the directory into which you downloaded the DigiCertGlobalRootCA.crt SSL certificate.

     b. Create a client key database and a stash file by using the GSKCapiCmd tool. For example, the following command creates a client key database that is called dashclient.kdb and a stash file that is called dashclient.sth:

     gsk8capicmd_64 -keydb -create -db "dashclient.kdb" -pw "passw0rdpw0" -stash

     where passw0rdpw0 is a password. The -stash option creates a stash file in the same path as that of the client key database, with a file extension of .sth. At connection time, GSKit uses the stash file to obtain the password to the client key database.

     c. Add the certificate to the client key database. For example, the following gsk8capicmd command imports the certificate from the /<ssl_directory_name>/ssl/DigiCertGlobalRootCA.crt file into the client key database called dashclient.kdb:

     gsk8capicmd_64 -cert -add -db "dashclient.kdb" -pw "passw0rdpw0" -label "DigiCert" -file "/<ssl_directory_name>/ssl/DigiCertGlobalRootCA.crt" -format ascii -fips

     d. Update the values of the SSL_CLNT_KEYDB and SSL_CLNT_STASH database manager configuration parameters on the client to specify the client key database and the stash file. Examples follow:

     db2 update dbm cfg using SSL_CLNT_KEYDB /<ssl_directory_name>/ssl/dashclient.kdb

     db2 update dbm cfg using SSL_CLNT_STASH /<ssl_directory_name>/ssl/dashclient.sth

     e. Catalog the Db2 Warehouse on Cloud node so that client applications can connect to it. Issue the following command:

     db2 catalog tcpip node <node_name> remote <Db2_Warehouse_IP_address> server <port_number> security ssl

     where:

     <node_name> is your name for the node.

     <Db2_Warehouse_IP_address> is the IP address of the Db2 Warehouse on Cloud server.

     <port_number> is the port that is used to connect to Db2 Warehouse by using an SSL connection. If you are using the default port, specify 50001.

     f. Catalog the remote Db2 Warehouse on Cloud database so that client applications can connect to it. Issue the following command:

     db2 catalog database bludb as <db_alias> at node <node_name>

     where db_alias is your name for the Db2 Warehouse on Cloud database.

     g. Test the SSL connection in one of the following ways:

     • Test the connection by using CLP by issuing the following command to connect to the Db2 Warehouse on Cloud database:

       db2 connect to <db_alias> user <user_id>

       where <user_id> is your Db2 Warehouse on Cloud user ID. You are prompted to enter your password.

     • Test the connection by using CLI by issuing the following command to connect to the Db2 Warehouse on Cloud database:

       db2cli validate -dsn <alias> -connect -user <user_id> -passwd <password>

       where <alias> is a DSN alias that you created by using the db2cli writecfg command, <user_id> is your Db2 Warehouse on Cloud user ID, and <password> is your Db2 Warehouse on Cloud database password.

   • To create a connection without SSL, complete the following steps:

     a. Catalog the Db2 Warehouse on Cloud node so that client applications can connect to it. Issue the following command:

     db2 catalog tcpip node <node_name> remote <Db2_Warehouse_IP_address> server <port_number>

     where:

     <node_name> is your name for the node.

     <Db2_Warehouse_IP_address> is the IP address of the Db2 Warehouse on Cloud server.

     <port_number> is the port that is used to connect to Db2 Warehouse on Cloud without using an SSL connection. If you are using the default port, specify 50000.

     b. Catalog the remote Db2 Warehouse on Cloud database so that client applications can connect to it. Issue the following command:

     db2 catalog database bludb as <db_alias> at node <node_name>

     where <db_alias> is your name for the Db2 Warehouse on Cloud database.

     c. Test the non-SSL connection in one of the following ways:

     • Test the connection by using CLP by issuing the following command to connect to the Db2 Warehouse on Cloud database:

       db2 connect to <db_alias> user <user_id>

       where <user_id> is your Db2 Warehouse on Cloud user ID. You are prompted to enter your password.

     • Test the connection by using CLI by issuing the following command to connect to the Db2 Warehouse on Cloud database:

       db2cli validate -dsn <alias> -connect -user <user_id> -passwd <password>

       where <alias> is a DSN alias that you created by using the db2cli writecfg command, <user_id> is your Db2 Warehouse on Cloud user ID, and <password> is your Db2 Warehouse on Cloud password.

2. Launch the InfoSphere Data Replication configuration tool and perform the following steps. The values that are shown in the screen captures are examples.

   a. Add a source instance to point to your source database by using the Instance Configuration tab:

   

   b. Add a target instance to point to your target Db2 database by using the Instance Configuration tab. If you are not using IBM InfoSphere Data Replication 11.3.3.3-50 or later, do not select the Specify refresh loader path check box.

   

   c. Start each instance:

   

3. Launch the InfoSphere Data Replication management console and use Access Manager to complete the following steps:

   a. Create a datastore to connect to your source instance by using the Datastore tab. Because a Db2 database was not originally supported as a source database, you must provide user and password information for the source database by clicking Connection Parameters.

   

   b. Create a datastore to connect to your target instance by using the Datastore tab. You must provide user and password information by clicking Connection Parameters.

   

   c. If the user (for example, admin) that connects to the Access Server does not exist, create that user:

   

   d. Click the Access Manager tab.

   e. On the Datastore Management tab, assign the user to both the source and target datastores by right-clicking each datastore and then clicking Assign User. Ensure that the credentials for accessing each instance are correct.

   

What to do next

Define a subscription and perform data replication. For information, see:

• Loading data from InfoSphere Data Replication

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

1. In Data Studio, click All Databases > New Connection to a database.

2. On the Local tab, select DB2 for Linux, UNIX, and Windows as the database manager.

3. On the General tab, enter the following values:

   • Database: BLUDB
   • Host: The host name.
   • Port: For a connection without SSL, enter 50000. For a connection with SSL, enter 50001.
   • User name: The user name that you use to log in.
   • Password: The password that you use to log in.

4. For an SSL connection, click the Optional tab, and then click Add. For the sslConnection property, specify true.

5. (Optional): Click Test Connection to verify that the connection succeeded.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

To create a connection, complete the following steps:

1. Log in to your Data Server Manager web console.

2. In the Data Server Manager web console, go to Set Up > Database Connections.

3. Click the icon to add a database connection. On the Add Database Connection page under the Database Connection tab, enter the required information in the following fields:

   • Database connection name: The name must be unique to Data Server Manager
   • Data server type: From the drop-down menu, select DB2 for Linux, UNIX, and Windows
   • Database name: BLUDB
   • Host name: Enter the Db2 Warehouse on Cloud host name
   • Port number: For a connection without SSL, enter 50000. For a connection with SSL, enter 50001.
   • JDBC security: From the drop-down menu, select Clear text password
   • User ID: Your Db2 Warehouse on Cloud user ID
   • Password: Your Db2 Warehouse on Cloud password

4. For a connection with SSL, select the Advanced JDBC Properties tab. Enter the required information in the following fields:

   • Property: sslConnection
   • Value: true

   Click the Add button. Select the Database Connection tab.

5. Test the connection by clicking the Test Connection button. If the connection is successful, click OK.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

1. In the Data Source Explorer view of InfoSphere Data Architect, right-click Database Connections, then select New.

2. On the Local tab, select DB2 for Linux, UNIX, and Windows as the database manager.

3. On the General tab, enter the following values:

   • Database: BLUDB
   • Host: The host name that you collected beforehand.
   • Port: For a connection without SSL, enter 50000. For a connection with SSL, enter 50001.
   • User name: The user ID that you collected beforehand.
   • Password: The password that you collected beforehand.

4. For an SSL connection, click the Optional tab. Enter an sslConnection property and specify a value of true. Click Add.

5. (Optional): Click Test Connection to verify that the connection succeeded.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

To use CLPPlus, ensure that a software development kit (SDK) or a Java runtime environment (JRE) for Java Version 1.5.0 or later is installed on your computer and that environment variables are set as follows:

• The JAVA_HOME environment variable is set to the Java installation directory on your computer.
• The PATH environment variable setting includes the bin subdirectory of the Java installation directory on your computer.

Procedure

1. In a command shell on Linux operating systems, at the Windows command prompt, or in the DB2 command window on Windows operating systems, run the following commands:

   These commands create new entries in the driver configuration file (db2dsdriver.cfg) on your computer and set the connection attributes. You need to do this step only one time.

   • For a connection with SSL:

     db2cli writecfg add -database BLUDB -host <hostname> -port 50001 -parameter "SecurityTransportMode=SSL"

     db2cli writecfg add -dsn <alias> -database BLUDB -host <hostname> -port 50001

     where:

     • <hostname> is the host name of the server.
     • <alias> is an alias that you choose.

   • For a connection without SSL:

     db2cli writecfg add -database BLUDB -host <hostname> -port 50000

     db2cli writecfg add -dsn <alias> -database BLUDB -host <hostname> -port 50000

2. To start CLPPlus with a connection to a Db2 Warehouse on Cloud database that uses the entries in the db2dsdriver.cfg file, run the following command:

   • Windows environments:

     clpplus <userid>@<alias>

   • Linux environments:

     clpplus -nw <userid>@<alias>

     where:

     • <userid> is the user ID from the connect credentials you collected beforehand.
     • <alias> is the alias that you created with the db2cli writecfg command.

   Running this command opens a CLPPlus window.

3. In the CLPPlus window, enter your password. The database information is displayed, followed by an SQL prompt. Sample output follows:

   Hostname = 192.0.2.0
   Database server = DB2/LINUXX8664  SQL10054
   SQL authorization ID = smith
   Local database alias = BLUDB
   Port = 50001
   
   SQL>

Results

You can now enter CLPPlus commands or SELECT statements and run scripts to work with the data in the database.

Examples

The following examples use a short script that retrieves rows from the sample table GOSALES.BRANCH. The script file is named cities.sql and is on the local Windows computer in the C:\temp directory. The cities.sql file contains the following text:

SET ECHO ON
SELECT branch_code, city from GOSALES.BRANCH;

BRANCH_CODE CITY
----------- --------------------------------------------------
          6 Paris
          7 Milano
          9 Amsterdam
         13 Hamburg
         14 München
         15 Kista
         17 Calgary
         18 Toronto
         19 Boston
         20 Seattle
         21 Los Angeles
         22 Miami
         23 Lyon
         24 Distrito Federal
         25 Tokyo
         26 Osaka City
         28 Melbourne
         29 Bilbao
         30 Sao Paulo
         31 Kuopio
         32 Seoul
         33 Singapore

BRANCH_CODE CITY
----------- --------------------------------------------------
         34 Shanghai
         35 London
         36 Birmingham
         37 Zürich
         38 Heverlee
         39 Wien
         40 Geneve

29 rows were retrieved.

To create a new connection in an IBM Watson project:

1. Click Add to project > Connection.

2. Choose a data source.

3. Enter the connection information required for your data source. Typically, you need to provide information like the host, port number, user name, and password.

4. You can discover assets from connections to Db2 Warehouse on Cloud, which gives you the ability to add all tables from the connection as data assets to a project. Select Discover data assets and choose a project.

5. Click Create. The connection appears on the Assets page.

Watch this video to see how to create a connection and add connected data to a project.

To create a new connection in a legacy project:

1. From your project's Assets page, click the Find and add data icon.

2. On the Connections pane, click Create Connection.

3. Enter a name and description and choose a Service Category:

   Data Service = IBM Cloud data service

   When you choose Data Service, your existing IBM Cloud data services appear in the Service Instance list.

4. Choose the service or database server from the list.

5. Enter the connection information:

   When you choose Data Service, your existing IBM Cloud data services appear in the Service Instance list.

6. Click Create. The connection is available to all legacy projects.

7. On the Connections pane, select the connection and click Apply.

To add an existing connection to a legacy project:

1. From your project's Assets page, click the Find and add data icon.

2. On the Connections pane, select the connection and click Apply.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

1. In SPSS Statistics, click File > Open Database > New Query.

2. In the Database Wizard, click Add ODBC data source.

3. Use the ODBC Data Source Administrator window to add an ODBC data source name (DSN) for the Db2 Warehouse on Cloud database:

   a. On the User DSN tab, click Add.

   b. On the Create New Data Source window, select the driver that is named IBM DB2 ODBC DRIVER and click Finish.

   Be sure that the driver that you select is not a driver with a similar name such as IBM DB2® ODBC DRIVER - DB2COPY. If the driver does not appear in the list, exit SPSS Statistics, then download and install the driver. See Driver package.

   c. In the ODBC IBM Driver - Add window, enter a data source name (usually the name of the database you are connecting to), and click Add.

   d. From the CLI/ODBC Settings window, on the Data Source tab, enter the user ID and password from the connection information that you collected beforehand.

   e. On the Advanced Settings page, for each of the following parameters, click Add to open the Add CLI Parameter window to begin the step, and click OK to return to the Advanced Settings page:

   • Select the Database parameter, then click OK to open the CLI/ODBC Settings window. In the Pending Value field, enter the database name from the connection information that you collected beforehand.

   • Select the Hostname parameter, then click OK to open the CLI/ODBC Settings window. In the Pending Value field, enter the host name from the connection information that you collected beforehand.

   • Select the Port parameter, then click OK to open the CLI/ODBC Settings window. In the Pending Value field, enter the port number from the connection information that you collected beforehand.

   • Select the Protocol parameter, then click OK to open the CLI/ODBC Settings window. Select TCP/IP.

   f. Click OK to return to the ODBC Data Source Administrator window.

   g. On the User DSN tab, select the data source name, then click Configure.

   h. On the CLI/ODBC Settings window, click Connect to test the connection.

   • If the connection is successful, click OK to return to the ODBC Data Source Administrator window, and then click OK to exit the window.

   • If the connection is not successful, note and correct any errors before you test the connection again.