Introduction

Data Virtualization for Cloud Pak for Data integrates data sources across multiple types and locations and turns all this data into one logical data view.

The Data Virtualization REST API connects to your service deployment, so you can manage your virtual data, data sources, and user roles. Additionally, by using the Data Virtualization REST API, you can virtualize and publish data to the default catalog in Watson Knowledge Catalog.

Authentication

To authenticate to the API, you use the /preauth/signin method to specify your Data Virtualization service credentials.

JWT authentication

You can configure Data Virtualization to accept JSON Web Tokens (JWT) as an authentication mechanism for the service.

A JWT is a set of JSON claims that are signed, encrypted, or both, and are encoded into a web safe form. For more information, see Authentication methods.

The Data Virtualization REST API uses the jwt-auth-user-payload as a header parameter for authentication. This parameter is provided automatically by the service proxy. You must not specify any value.

Endpoint URL

The Data Virtualization API endpoint URL is based on your IBM Cloud Pak deployment URL.

Data Virtualization provides two different endpoint URLs, depending on the method that you are using. Ensure that you use the correct endpoint URL for the method that you want to use.

https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v1/{method}
https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/{method}

When you call the API, add the path for each method to form the complete API endpoint for your request. For example, if your instance is deployed at https://www.example.com:31843, you can access the APIs at https://www.example.com:31843/icp4data-databases/dv/{namespace}/dvapiserver/v1/{method} or https://www.example.com:31843/icp4data-databases/dv/{namespace}/dvapiserver/v2/{method}.

Example

curl -H "cache-control: no-cache" -X {request_method} "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v1/{method}"

or

curl -H "cache-control: no-cache" -X {request_method} "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/{method}"

Disabling SSL verification

The Data Virtualization service uses Secure Sockets Layer (SSL) (or Transport Layer Security (TLS)) for secure connections between the client and server. The connection is verified against the local certificate store to ensure authentication, integrity, and confidentiality.

If you use a self-signed certificate, you need to disable SSL verification to make a successful connection.

Enabling SSL verification is highly recommended. Disabling SSL jeopardizes the security of the connection and data. Disable SSL only if necessary, and take steps to enable SSL as soon as possible.

To disable SSL verification for a curl request, use the --insecure (-k) option with the request.

Example that disables SSL verification

curl -k -X {request_method} -H "cache-control: no-cache" "{url}/icp4data-databases/dv/{namespace}/dvapiserver/v2/{method}"

Error handling

Data Virtualization uses standard HTTP response codes to indicate whether a method completed successfully. HTTP response codes in the 2xx range indicate success. A response in the 4xx range is some sort of failure, and a response in the 5xx range usually indicates an internal system error that cannot be resolved by the user. Response codes are listed with the method.

ErrorResponse

Name Description
code{: .parameter-name .required}
string
An identifier of the response.
message{: .parameter-name .required}
string
An explanation of the problem.

Methods

Log in to Data Virtualization

Contains the credentials required to log in to the Data Virtualization service.

POST /preauth/signin

Request

Specify your username and password to log in to the Data Virtualization service.

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"username\":\"DV-user\",\"password\":\"myPassword\"}" "https://{cpd_cluster_host}{:port}/v1/preauth/signin"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Get all service nodes

Returns all the service nodes.

GET /v2/agent_nodes

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/agent_nodes"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "agent_node_array": {
        "agent_class": "F",
        "dscount": "0",
        "hostname": "dv-0.dv.tns.svc.cluster.local",
        "is_docker": "false",
        "node_description": "Not specified",
        "node_name": "AdminNode",
        "os_user": "bigsql",
        "port": "6414"
      }
    }

Add data source connection

Adds a data source connection to the Data Virtualization service.

POST /v2/datasource_connection

Request

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"datasource_type\":\"Db2\",\"name\":\"Db2\",\"origin_country\":\"us\",\"properties\":{\"database\":\"TPDCS\",\"host\":\"{hostname}\", \"password\":\"myPassword\", \"port\":\"50000\", \"ssl\":\"true\", \"username\":\"DV-user\"}}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/datasource_connection"

Response

Status Code

  • Created

  • Bad Request

  • Unauthorized

  • Conflict

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "201",
      "message": "Created"
    }

Delete data source connection

Deletes a data source connection from the Data Virtualization service.

DELETE /v2/datasource_connection

Request

Specifies the data source connection to be deleted.

  • curl -k -X DELETE -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"connection_id\":\"5e4d01b-7417-4abc-b267-8ffb393fb970\",\"cid\":\"DB210013\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/datasource_connection"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Get data source nodes

Gets all data source nodes that are connected to the service.

GET /v2/datasource_nodes

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/datasource_nodes"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "datasource_nodes_array": [
        {
          "agent_class": "F",
          "dscount": "0",
          "hostname": "dv-0.dv.tns.svc.cluster.local",
          "is_docker": "N",
          "node_name": "AdminNode",
          "node_description": "Not specified",
          "port": "6414",
          "os_user": "bigsql",
          "data_sources": [
            {
              "cid": "MSSQL10000",
              "dbname": "mssql2014db1",
              "connection_id": "75e4d01b-7417-4abc-b267-8ffb393fb970",
              "srchostname": "example.ibm.com",
              "srcport": "1433",
              "srctype": "MSSQLServer",
              "status": "string",
              "usr": "DV-user",
              "uri": "example.ibm.com:1433/"
            }
          ]
        }
      ]
    }

Discover data sources

Automatically discovers the data sources that can be connected to Data Virtualization.

GET /v2/discover_datasource

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/discover_datasource"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "datasource_array": [
        {
          "db_hostname": "halon1",
          "is_configured": "0",
          "is_connected": "0",
          "is_discovered": "1",
          "node_description": "This is my example node description",
          "node_name": "halon1:6421",
          "port": "50000",
          "src_type": "DB2"
        }
      ]
    }

Generate installation script for remote connectors

Generates a script to install connectors on remote data sources.

GET /v2/endpoint_script

Request

Query Parameters

  • Specifies the external hostname of the dv-engine service component.

  • Specifies the external port of the dv-engine service component.

    Default: 443

  • Specifies whether to use SSL connection for the remote data source.

    Default: true

  • Specifies the operating system of the remote data source.

    Default: Linux

  • Specifies the type of the installation package.

    Default: tar

  • Specifies the directory where Java is installed on the remote data source.

    Default: /opt/ibm/java

  • Specifies the directory where you want to install the remote connector.

    Default: /opt/ibm/dv

  • Specifies the service node.

  • Specifies the service node description.

  • Specifies the node port that the connector will use on the remote data source. Each connector that you install on the remote data source can use a different node port.

    Default: 6414

  • curl -k -X GET -H "cache-control: no-cache" -d "{\"admin_host\":\"{EXTERNAL_HOSTNAME}\",\"admin_port\":\"443\",\"use_ssl\":\"true\",\"platform\":\"Linux\",\"file_type\":\"TAR\",\"java_home\":\"/opt/ibm/java\",\"install_path\":\"/opt/ibm/dv\",\"node_name\":\"dv-node\",\"node_desc\":\"my DV node\",\"data_port\":\"6414\"\}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/endpoint_script"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Not found

  • Unprocessable Entity

  • Internal Server Error

Example responses
  • {
      "script": "string"
    }

Grant user access

Grants a user access to a specific virtual object.

POST /v2/privileges/users

Request

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "\[{\"object_name\":\"EMPLOYEE\",\"object_schema\":\"USER999\", \"authid\":\"PUBLIC\"}]" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/users"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Revoke user acccess

Revoke user access to virtual object.

DELETE /v2/privileges/users

Request

  • curl -k -X DELETE -H "cache-control: no-cache" -H "content-type: application/json" -d "\[{\"object_name\":\"EMPLOYEE\",\"object_schema\":\"USER999\", \"authid\":\"PUBLIC\"}]" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/users"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Grant user role

Grants a user role access to a specific virtual object.

POST /v2/privileges/roles

Request

Grants a role to manage virtual objects.

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"object_name\":\"EMPLOYEE\",\"object_schema\":\"USER999\",\"role_to_grant\":\"DV_ENGINEER\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/roles"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Delete role

Revokes a role for a virtual object.

DELETE /v2/privileges/roles

Request

Revokes a role from a user for a virtual object.

  • curl -k -X DELETE -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"object_name\":\"EMPLOYEE\",\"object_schema\":\"USER999\",\"role_to_revoke\":\"DV_ENGINEER\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/roles"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Get objects by role

Retrieves the list of virtual objects that have a specific role.

GET /v1/privileges/objects/role/{rolename}

Request

Path Parameters

  • The name of the role.

    Example: User

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v1/privileges/objects/role/{rolename}"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "objects": {}
    }

Enable policy enforcement

Determines whether Watson Knowledge Catalog policies are enabled and enforced in Data Virtualization.

GET /v2/security/policy/on

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/security/policy/on"

Response

Status Code

  • Success

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "status": "enabled"
    }

Disable policy enforcement

Determines whether Watson Knowledge Catalog policies are disabled in Data Virtualization.

GET /v2/security/policy/off

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/security/policy/off"

Response

Status Code

  • Success

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "status": "disabled"
    }

Get status of policy enforcement

Retrieves the status of the Watson Knowledge Catalog policy enforcement.

GET /v2/security/policy/status

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/security/policy/status"

Response

Status Code

  • Success

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "status": "string"
    }

Create remote table for data files in the remote connector

Creates a remote table for data files in the remote connector.

POST /v2/virtualization/files

Request

Creates a remote table for data files on the remote connectors.

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"column_names\":\"{COLUMN_NAMES}\",\"column_types\":\"{COLUMN_DATA_TYPES}\",\"file_path\":\"/home/data.csv\",\"is_ignore_case\":\"false\",\"is_replace\":\"false\",\"node_name\":\"AdminNode\",\"options\":\"COLNAMES=true\",\"virtual_name\":\"Tab1\",\"virtual_schema\":\"USER999\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/virtualize/files"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "message": "message"
    }

Virtualize table

Transforms a given data source table into a virtual table.

POST /v2/virtualization/tables

Request

request body

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"is_included_columns\":\"Y, Y, N\",\"replace\":\false\,\"source_name\":\"Tab1\",\"source_table_def\":\[{\"column_name\":\"Column1\",\"column_type\":\"INTEGER\"}],\"sources\":\"DB210001:\"Hjq1\"\",\"virtual_name\":\"Tab1\",\"virtual_schema\":\"USER999\",\"virtual_table_def\":\[{\"column_name\":\"Column1\",\"column_type\":\"INTEGER\"}]}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/virtualize/tables"

Response

Status Code

  • Created

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "message": "string"
    }

Delete table

Removes the specified table. You must specify the schema and table name.

DELETE /v2/mydata/tables/{schema_name}/{table_name}

Request

Path Parameters

  • The schema of table to be deleted

  • The name of table to be deleted

  • curl -k -X DELETE -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/mydata/tables/{schema_name}/{table_name}"

Response

Status Code

  • Success

  • Bad Request

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

Delete view

Removes the specified table. You must specify the schema and view name.

DELETE /v2/mydata/views/{schema_name}/{view_name}

Request

Path Parameters

  • The schema of the view to be deleted.

  • The name of the view to be deleted.

  • curl -k -X DELETE -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/mydata/views/{schema_name}/{view_name}"

Response

Status Code

  • Success

  • Bad Request

  • Internal Server Error

Example responses
  • {
      "_messageCode_": "200",
      "message": "Success"
    }

List caches

List all active, inactive and deleted caches in Data Virtualization

GET /v2/caches

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/caching/v2/caches"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "caches": [
        {
          "name": "ItemsCache",
          "id": "DV20210427043634532286",
          "query": "SELECT A0.\"I_ITEM_SK\" as COL3, A0.\"I_CATEGORY\" as COL2, A0.\"I_CATEGORY_ID\" as COL1 FROM  \"ADMIN\".\"ITEM\" as A0",
          "owner_id": "ADMIN",
          "type": "R",
          "created_timestamp": "2021-04-27 04:36:34.531234",
          "last_modified_timestamp": "2021-04-27 04:36:50.883267",
          "last_refresh_timestamp": "2021-04-27 05:00:20.729498",
          "last_used_timestamp": "2021-04-29 16:33:32.319953",
          "state": "Enabled",
          "size": "14171",
          "cardinality": "2880404",
          "time_taken_for_refresh": "20",
          "refresh_count": "1",
          "hit_count": "1",
          "refresh_schedule": "0 0 0/5 ? * * *",
          "refresh_schedule_desc": "every 5 hours at minute 0",
          "status_msg": ""
        },
        {
          "name": "TestCache_DELETED_20210406034223883688",
          "id": "DV20210406034041592922",
          "query": "select * from ADMIN.CUSTOMER",
          "owner_id": "ADMIN",
          "type": "U",
          "created_timestamp": "2021-04-06 03:40:41.590105",
          "last_modified_timestamp": "2021-04-06 03:42:23.97146",
          "last_refresh_timestamp": "2021-04-06 03:40:52.872691",
          "last_used_timestamp": "",
          "state": "Deleted",
          "size": "136",
          "cardinality": "100",
          "time_taken_for_refresh": "0",
          "refresh_count": "0",
          "hit_count": "0",
          "refresh_schedule": "",
          "refresh_schedule_desc": "",
          "status_msg": ""
        }
      ]
    }

List a cache

List a specific cache in Data Virtualization

GET /v2/caches/{id}

Request

Path Parameters

  • The ID of the cache to be listed

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/caching/v2/caches/{id}"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Not found

  • Internal Server Error

Example responses
  • {
      "name": "ItemsCache",
      "id": "DV20210427043634532286",
      "query": "SELECT A0.\"I_ITEM_SK\" as COL3, A0.\"I_CATEGORY\" as COL2, A0.\"I_CATEGORY_ID\" as COL1 FROM  \"ADMIN\".\"ITEM\" as A0",
      "owner_id": "ADMIN",
      "type": "R",
      "created_timestamp": "2021-04-27 04:36:34.531234",
      "last_modified_timestamp": "2021-04-27 04:36:50.883267",
      "last_refresh_timestamp": "2021-04-27 05:00:20.729498",
      "last_used_timestamp": "2021-04-29 16:33:32.319953",
      "state": "Enabled",
      "size": "14171",
      "cardinality": "2880404",
      "time_taken_for_refresh": "20",
      "refresh_count": "1",
      "hit_count": "1",
      "refresh_schedule": "0 0 0/5 ? * * *",
      "refresh_schedule_desc": "every 5 hours at minute 0",
      "status_msg": ""
    }

Fetch the cache storage

Fetch the total cache storage and used capacities for active and inactive caches in Data Virtualization

GET /v2/caches/storage

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/caching/v2/caches/storage"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "total_size": "100Gi",
      "enabled": {
        "size": 16918,
        "count": 4
      },
      "disabled": {
        "size": 0,
        "count": 0
      }
    }

Publish virtual data to the default catalog

Publishes your virtual data to the default catalog in Watson Knowledge Catalog.

POST /v2/integration/catalog/publish

Request

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -d "{\"allow_duplicates\":\"false\",\"assets\":\[\"schema\":\"USER999\",\"table\":\"Tab1\"]\}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/integration/catalog/publish"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "published_assets": {
        "schema_name": "USER999",
        "table_name": "customer",
        "wkc_asset_id": "37fa4a15-1071-4a20-bc9e-0283d3dfb6e1"
      },
      "duplicate_assets": [
        {
          "schema_name": "USER999",
          "table_name": "customer"
        }
      ]
    }