Creating the DSX connection to Db2 Warehouse

DSX Local can access data in Db2 Warehouse by creating a data connection in the DSX Local user interface. Anyone with credentials to Db2 Warehouse can create a connection and later add the connection to a project. All users of the project can then use the connection to load data to or from Db2 Warehouse.

Before you begin

If you plan to use SSL for a Db2 connection that uses a self-signed certificate or a certificate signed by a local certificate authority (CA), you need to import the SSL certificate to the Spark truststore:
  1. Export the self-signed certificate or local CA certificate to a file.
  2. Import the certificate to the Spark truststore and mark it as trusted. You can do this step with a script, /wdp/utils/importCertToTruststore.sh, which is provided in the master nodes of the cluster. The following command runs the script: 
    importCertToTruststore.sh certificatefile
    where certificatefile is the fully qualified path to the file with the certificate that you exported in the previous step.
  3. Restart the kernels for any notebooks that are open to ensure the latest version of the truststore is accessed.

About this task

You need one connection for each table, file, or query that you want people to access. When you create a connection, you are actually creating a global connection. This connection is available to be used in any of your projects. When you add a connection to your project, you are making a copy of the global connection and adding that copy to your project. If you delete a connection from your project, it has no effect on the original global connection. That connection remains available to be added to other projects.

To create the DSX connection to Db2 Warehouse:

Procedure

  1. From the main menu in the top corner of DSX Local, click Connections.
  2. On the Connections page, click the Create Connection button.
  3. In the New Connection window, specify the details for the connection:
    • Provide a name and description.
    • Select the Db2 server type.
    • Specify the connection information. Typically, you need to provide information like the host, port number, file or table name, user name, and password. Your connection can collect data using either an SQL table or an SQL query.
    • If you provide a query that contains an expression in the SELECT list, be sure to provide a column alias name for the expression. For example, in the following query, the as SALARY clause is needed to provide a column alias name for the expression:
      SELECT WORKDEPT, AVG(SALARY) as SALARY from db2inst1.emp group by workdept
    • If you plan to use SSL, be sure to click the Use SSL check box.
  4. Click Create.

Results

The Db2 Warehouse connection appears on the Connections page and is ready for you to add to one or more projects.