Connecting to IBM Db2 Warehouse with the command-line processor (CLP) or Call Level Interface (CLI)

You can create an SSL or non-SSL connection between IBM® Data Server Client Version 11.1 and the Db2® Warehouse database by using the command line processor (CLP) or Call Level Interface (CLI).

Before you begin

Obtain Db2 Warehouse client container that applies to your machine’s operating system. The most recent version of IBM Data Server Client package and other support tools are always included inside. Using this version is recommended as it includes latest features and fixes, and guarantees correct operation of the tools; see Deploying the IBM Db2 Warehouse client container (Linux). Alternatively, you can also download and install the package as described in IBM Data Server Client, but some features might not work.

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 you can connect to your database, you must carry out the following steps:

Verify prerequisites, including installing driver packages, configuring your local environment, and downloading SSL certificates (if needed)
Collect connect information, including database details such as host name and port numbers, and connect credentials such as user ID and password

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.

About this task

Complete the set of steps that applies to the type of connection that you want to create.

Procedure

Use one of the following approaches:

To create a connection by using SSL, complete the following steps:
1. 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.
2. Create a client key database and a stash file by using the GSKCapiCmd tool. For example, the following command creates a client key database called dashclient.kdb and a stash file 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.
3. 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
```
4. 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
```
5. Catalog the Db2 Warehouse 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 security ssl
```
  where:
  - node_name is your name for the node.
  - Db2_Warehouse_IP_address is the IP address of the Db2 Warehouse server.
  - port 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.
6. Catalog the remote Db2 Warehouse 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 database.
7. Test the SSL connection in one of the following ways:
  - Test the connection using CLP by issuing the following command to connect to the Db2 Warehouse database:
```
db2 connect to db_alias user user_id
```
    where user_id is your Db2 Warehouse user ID. You are prompted to enter your password.
  - Test the connection using CLI by issuing the following command to connect to the Db2 Warehouse 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 user ID, and password is your Db2 Warehouse password.
To create a connection without using SSL, complete the following steps:
1. Catalog the Db2 Warehouse 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
```
  where:
  - node_name is your name for the node.
  - Db2_Warehouse_IP_address is the IP address of the Db2 Warehouse server.
  - port is the port that is used to connect to Db2 Warehouse without using an SSL connection. If you are using the default port, specify 50000.
2. Catalog the remote Db2 Warehouse 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 database.
3. Test the non-SSL connection in one of the following ways:
  - Test the connection using CLP by issuing the following command to connect to the Db2 Warehouse database:
```
db2 connect to db_alias user user_id
```
    where user_id is your Db2 Warehouse user ID. You are prompted to enter your password.
  - Test the connection using CLI by issuing the following command to connect to the Db2 Warehouse 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 user ID, and password is your Db2 Warehouse password.