Skip to main content

DB2

Summary​

This document covers the information to gather from IBM DB2 Cloud in order to configure a Qarbine data service. The data service will use the Qarbine DB2 driver. You can define multiple data services that access the same DB2 endpoint though with varying credentials. Once a data service is defined, you can manage which Qarbine principals have access to it and its associated data. A Qarbine administrator has visibility to all data services.

DB2 Cloud Configuration​

Overview​

The following information is needed to configure access to your DB2 instance:

  • host,
  • port,
  • SSL certificate,
  • schema,
  • database
  • user, and
  • password.

Obtaining the Information​

Sign on to the IBM portal and navigate to the DB2 console.

Click the highlighted option below.

  

If you do not have any service credential then create one by clicking the “New credential” button and following the prompts.

Expand the service credential of interest.

  

Below are the items to copy into a temporary location.

"db2": {
      "authentication": {
        "password": "ITQwFHfoo",
        "username": "rygfoo"
      },
      "certificate": {
        "certificate_base64": "LS0tLS1CRUdJTiBD…"
      },
     …
     "database": "bludb",
      …
      "hosts": [
        {
          "hostname": "1bbf73...databases.appdomain.cloud",
          "port": 32286
        }
      ],

General information on DB2 user management can be found at https://cloud.ibm.com/docs/Db2onCloud?topic=Db2onCloud-user_mgmt

Qarbine Configuration​

Compute Node Preparation​

Determine which compute node service endpoint you want to run this data access from. That URL will go into the Data Service’s Compute URL field. Its form is “https://domain:port/dispatch”. A sample is shown below.

  

The port number corresponds to a named service endpoint configured on the given target host. For example, the primary compute node usually is set to have a ‘main’ service. That service’s configuration is defined in the ˜./qarbine.service/config/service.main.json file. Inside that file the following driver entry is required

"drivers" :[
 . . .
"./driver/db2Driver.js"
]

The relevant configuration file name for non primary (main) Qarbine compute nodes is service.NAME.json. Remember to have well formed JSON syntax or a startup error is likely to occur. If you end up adding that entry then restart the service via the general command line syntax

pm2 restart <service>

For example,

pm2 restart main

or simply

pm2 restart all

Data Service Definition​

Open the Administration Tool.

Navigate to the Data Services tab.

  

A data service defines on what compute node a query will run by default along with the means to reach to target data. The latter includes which native driver to use along with settings corresponding to that driver. Multiple Data Sources can reference a single Data Service. The details of any one Data Service are thus maintained in one spot and not spread out all over the place in each Data Source. The latter is a maintenance and support nightmare.

To begin adding a data service click

  

On the right hand side enter a name and optionally a description.

  

Set the Compute URL field based on the identified compute node above. Its form is “https://domain:port/dispatch”. A sample is shown below.

  

Also choose the “DB2” driver.

  

The IBM DB2 Cloud information items gathered above are used to form the server template.

HOSTNAME=hostname;PORT=port;UID=rygfoo;PWD=ITQwFHfoo;

Below is an example.

  

You can reference environment variables using the syntax %NAME%. Any strings should be quoted and the key\value pairs separated by commas.

The server options of the data service has the form

schema=MySchema, base64Cert="LS0tLS1CRUdJTiBD…"

Below is an example.

  

The database is specified in the field shown below.

  

Test your settings by clicking on the toolbar image highlighted below.

  

The result should be similar to the following.

  

In the above example the prestoEngines node has been expanded.

Save the Data Service by clicking on the image highlighted below.

  

The data service will be known at the next log on time. Next, see the Presto query interaction and any tutorial for information on interacting with Watsonx.data from Qarbine.

Sample Data and Examples​

Overview​

The ˜/qarbine.service/sample/db2 folder contains these sample data sets:

  • sampleSales.sql and
  • jsonSales.sql.

Their contents are used in the tutorial and general query discussion documents.

The sales table’s definition is

CREATE TABLE Sales (
    SaleID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ProductName VARCHAR(50),
    Category VARCHAR(30),
    SaleDate DATE,
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    CustomerName VARCHAR(50),
    Region VARCHAR(30)
);

The jsonSales table’s definition is

CREATE TABLE jsonSales (
    sale_id INTEGER NOT NULL PRIMARY KEY,
    sale_date DATE,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    product_name VARCHAR(100),
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    product_details BLOB,
    total_amount DECIMAL(10,2)
);

A sample product_details JSON value is

{"brand": "TechPro", "model": "UltraBook X1","specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD",
 "screen": "15.6 inch 4K"}, 
"color": "Silver", "warranty": "2 years"}

Installing​

The data is inserted using SYSTOOLS.JSON2BSON and is best retrieved using SYSTOOLS.BSON2JSON. The letter converts the binary storage value into a JSON string. That value may then be converted into a JSON object using a Qarbine pragma convertToObject .

To install the sample data sign on to your IBM DB2 instance.

Next, navigate to the DB2 console.

Select the SQL option highlighted below.

  

The contents of sampleSales.sql and jsonSales.sql can then be copied and pasted into the query field and then run to populate the sample data sets.