Testing the CLI connectivity with the database

To connect local applications and tools to your Db2 database, you need to configure your environment.

Before you begin

Ensure that you have completed these prerequisite tasks before testing your connection to your Db2® database:
  • Install the IBM® Data Server Driver Package for your operating system.
  • Decide whether you are using Transport Layer Security (TLS) to connect to your database or not.
  • Collect database details and connect credentials, including the host name of your server and your database user ID and password.

Procedure

  1. From the command prompt, issue the db2cli validate command without adding entries to the driver configuration file, db2dsdriver.cfg:
     db2dsdriver.cfg: db2cli validate -connstring “<ODBC Connection String>”
    where ODBC Connection String is specified in the format defined by the InConnectionString argument in the SQLDriverConnect API function.
  2. Add entries to the driver configuration file, db2dsdriver.cfg, for your database.

    The configuration steps are different depending on whether you want to connect to your database by using TLS or not:

    Without TLS

    To connect your applications and tools to your database without using TLS, enter the following commands in a command shell on Linux operating systems, at the Windows command prompt, or in a Db2 command window:
    db2cli writecfg add -database SAMPLEDB -host hostname -port 50000
    db2cli writecfg add -dsn alias -database SAMPLEDB -host hostname -port 50000
    Where:
    • hostname is the host name of your server.
    • alias is an alias that you choose. The alias cannot be the same as the database name, SAMPLEDB. If you want to have spaces in the alias, surround the alias with double quotation marks.

    With TLS

    To connect your applications and tools to your database by using TLS, enter the following commands in a command shell on Linux operating systems, at the Windows command prompt, or in a Db2 command window:
    db2cli writecfg add -database SAMPLEDB -host hostname -port 50001 
    db2cli writecfg add -dsn alias -database SAMPLEDB -host hostname -port 50001 
    db2cli writecfg add -database SAMPLEDB -host hostname -port 50001 -parameter "SecurityTransportMode=SSL" 
    db2cli writecfg add -database SAMPLEDB -host hostname -port 50001 -parameter "SSLServerCertificate=SampleSSLCert.crt" 
    Where:
    • hostname is the host name of your server.
    • alias is an alias that you choose. The alias cannot be the same as the database name, SAMPLEDB. If you want to have spaces in the alias, surround the alias with double quotation marks.
    • SampleSSLCert.crt specifies the fully qualified name of a self-signed server certificate or a certificate authority (CA) certificate.
    Note: There is no need to configure the SSLServerCertificate keyword for a connection to the Bluemix database.
  3. Test connecting by issuing the db2cli validate command from the command prompt:
    db2cli validate -dsn alias -connect -user userid -passwd password
    Where:
    • alias is an alias you created with the db2cli writecfg command.
    • userid is your Db2 user ID.
    • password is your Db2 password.
  4. [Optional] To be able to connect local ODBC applications and tools to your database, register the DSN with the ODBC driver manager:
    Run the following command from a command line:
    db2cli registerdsn -add -dsn  alias
    Where, alias is an alias you created with the db2cli writecfg command. By default, the DSN is created as a user DSN.

Example

The following examples show the command syntax and output from running the db2cli validate command with three different connection string values. These commands are run without adding entries to the driver configuration file, db2dsdriver.cfg (see Step 1)
db2cli validate -connstring “DATABASE=SAMPLE=SAMPLE;HOSTNAME=test.ibm.com;PORT=50000;UID=db2user;PWD=db2pwd”
db2cli validate -connstring “DATABASE=SAMPLE;HOSTNAME=test.ibm.com;PORT=50000;AutoCommit=1;CurrentSchema=OWNER1” -connect -user db2user -passwd db2pwd
db2cli validate -connstring “DATABASE=SAMPLE;HOSTNAME=test.ibm.com;PORT=50000;UID=db2user;PWD=db2pwd;Protocol=TCPIP;Security=SSL;SSLServerCertificate=C:\myservercert.arm”