Adding a database-catalog pair

A database is one of the data sources that you can register and use in IBM® watsonx.data. A catalog defines the schemas and metadata for a data source.

watsonx.data Developer edition

watsonx.data on Red Hat® OpenShift®

watsonx.data SaaS on AWS

About this task

IBM supports the following database types:
  • IBM Db2: IBM Db2 is a database that contains relational data.
  • IBM Netezza: Netezza Performance Server is a platform for high-performance data warehousing and analytics.
  • Apache Kafka: Apache Kafka is a distributed event streaming platform. Connect to an Apache Kafka real-time processing server to write and to read Streams of events from and into topics.
  • MongoDB: MongoDB is a distributed database that stores data in JSON-like documents.
  • MySQL: MySQL is an open source relational database management system.
  • PostgreSQL: PostgreSQL is an open source and customizable object-relational database.
  • SQL Server: Microsoft SQL Server is a relational database management system.
  • Teradata: Teradata is a relational database management system.
  • Elasticsearch: Elasticsearch is a NoSQL database that stores data in an unstructured manner.
  • Snowflake: Snowflake is a cloud-hosted relational database for building data warehouse.
  • SingleStore: SingleStore is a relational database management system that is designed for data-intensive applications.
  • SAP HANA: SAP HANA is a column-oriented, in-memory relational database.
  • IBM Data Virtualization Manager for z/OS: IBM Data Virtualization Manager for z/OS provides virtual, integrated views of data residing on IBM Z. It enables users and applications to have read or write access to IBM Z data in place, without having to move, replicate, or transform the data.
  • Amazon Redshift: Amazon Redshift uses SQL to analyze structured and semi-structured data across data warehouses, operational databases, and data lakes. It uses AWS-designed hardware and machine learning to deliver the best price performance at any scale.
  • Informix: Informix is an enterprise class Object Relational Database Management System (ORDBMS). It is extensible and provides extreme transaction performance and features to support both OLTP and Data Warehouse database services.
  • Oracle: Oracle allows querying and creating tables in an external Oracle database.
  • Prometheus: Prometheus allows reading Prometheus metrics as tables in Trino by using the Prometheus HTTP API mechanism.
  • Custom: You can now use the custom database feature for databases that Presto supports but are not listed in IBM watsonx.data supported databases.

Procedure

  1. Log in to watsonx.data console.
  2. From the navigation menu, select Infrastructure Manager.
  3. To define and connect a database, click Add component and select Add database.
  4. In the Add database window, select a database from the Database type drop-down list. The list includes the following database types:

    For version 1.1.4:

  5. Based on the database type selected, configure the database details.
    IBM Db2
    If you select IBM Db2 from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
      Note: Select IBM Db2 from the Database Type drop-down list to add IBM Watson Query.

      You can now query the nicknames that are created in Db2 and the virtualized tables from Watson Query instances.

    IBM Netezza
    If you select IBM Netezza from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
      Note: For a database type as Netezza, select the version 11.2.2.x.
    MongoDB
    If you select MongoDB from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your authentication database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname. You can add multiple host information. To add, click the Add icon. A new row appears for adding hostname and port. Enter the details.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    SingleStore
    If you select SingleStore from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Teradata
    If you select Teradata from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      SSL Connection To establish a secure connection, do the following steps:

      i. Select the SSL Connection checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    SAP HANA
    If you select SAP HANA from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      SSL Connection To establish a secure connection, do the following steps:

      i. Select the SSL Connection checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      SAP HANA driver.tar Drag a file or click to upload your own driver for this database type. The maximum file size that is allowed is 100 MB.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Apache Kafka
    If you select Apache Kafka from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname. You can add multiple host information. To add, click the Add icon. A new row appears for adding hostname and port. Enter the details.
      Port Enter the port number.
      SASL connection Enable the Simple Authentication Security Layer (SASL) to include an authentication mechanism. If you enable SASL, specify the username and API key.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
      Add topics

      (For version 1.1.4)

      		 You can add topics after you create the database.

      i. Go to the Infrastructure manager.

      ii. Select the Apache Kafka database.

      iii. Click Add topics option.

      iv. Upload .json definition files. You can either drag the files or use the Click to upload option. Topic names are determined from the definition files.

      v. Use the Edit option to view and edit the topic files.
    MySQL
    If you select MySQL from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Elasticsearch
    If you select Elasticsearch from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      SSL Connection To establish a secure connection, do the following steps:

      i. Select the SSL Connection checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    PostgreSQL
    If you select PostgreSQL from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Validate Certificate Enable it to validate whether the SSL certificate that is returned by the host is trusted or not.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    SQL Server
    If you select SQL Server from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Test connection Click the Test connection link to test the database connection. If the database connection is successful, a success message appears.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Snowflake
    If you select Snowflake from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Username Enter the username.
      Password Enter the password.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    IBM Data Virtualization Manager for z/OS
    If you select IBM Data Virtualization Manager for z/OS from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Validate Certificate Enable it to validate whether the SSL certificate that the host returns is trusted or not.
      Hostname in SSL certificate Provide the hostname in SSL certificate.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Amazon Redshift
    If you select Amazon Redshift from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      SSL connection To establish a secure connection, do the following steps:

      i. Select the SSL connection checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Validate Certificate Enable it to validate whether the SSL certificate that the host returns is trusted or not.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Informix
    If you select Informix from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Informix server Enter the Informix server name.
      SSL connection To establish a secure connection, do the following steps:

      i. Select the SSL connection checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Oracle
    If you select Oracle from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Username Enter the username.
      Password Enter the password.
      Connection mode Select the connection mode.
      Connection mode value Enter the connection mode value.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Prometheus
    If you select Prometheus from the Database type drop-down list, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Field Description
      Database name Enter the name of your database.
      Display name Enter the database name to be displayed on the screen.
      Hostname Enter the hostname.
      Port Enter the port number.
      Port is SSL enabled To establish a secure connection, do the following steps:

      i. Select the Port is SSL enabled checkbox. The Upload SSL certificate (.pem, .crt or .cer) link is enabled.

      ii. Click the Upload SSL certificate (.pem, .crt or .cer) link.

      iii. Browse the SSL certificate and upload.

      Verify hostname Enable it to verify the hostname in the SSL certificate.
      Catalog name Enter the name of the catalog. This catalog is automatically associated with your database.
    Custom
    If you select Custom from the Database type, configure the following details:
    1. In the Add database window, enter the following details:
      Note: Two databases with the same name cannot be added.
      Note: Custom databases do not support SSL configuration properties.

      Custom database feature can be used for databases that Presto supports but are not listed in IBM watsonx.data supported databases.

      For more information, see Custom database feature

  6. Click Add.