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 pass an access token in an Authorization header. The token is associated with a user name, for example: admin. To generate an access token, call the Get authorization token method.

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} -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v1/{method}"

or

curl -H "cache-control: no-cache" -X {request_method} -H "Authorization: Bearer {token}" "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" -H "Authorization: Bearer {token}" "{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

Get all service nodes

Returns all the service nodes.

GET /dvapiserver/v2/agent_nodes

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "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"
      }
    }

Get data source connections

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

GET /dvapiserver/v2/datasource/connections

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer $token" "https://{HOSTNAME}:{PORT_NUMBER}/icp4data-databases/dv/{namespace}/dvapiserver/v2/datasource/connections"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

Example responses
  • {
      "datasource_connections": [
        {
          "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/"
            }
          ]
        }
      ]
    }

Add data source connection

Adds a data source connection to the Data Virtualization service.

POST /dvapiserver/v2/datasource/connections

Request

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

Response

Status Code

  • Created

  • Bad Request

  • Unauthorized

  • Conflict

  • Internal Server Error

No Sample Response

This method does not specify any sample responses.

Delete data source connection

Deletes a data source connection from the Data Virtualization service.

DELETE /dvapiserver/v2/datasource/connections/{connection_id}

Request

Path Parameters

  • The connection identifier for the platform.

    Example: 75e4d01b-7417-4abc-b267-8ffb393fb970

Query Parameters

  • The identifier of the connection for the Data Virtualization.

    Example: DB210013

  • curl -k -X DELETE -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer $token" "https://{HOSTNAME}:{PORT_NUMBER}/icp4data-databases/dv/{namespace}/dvapiserver/v2/datasource/connections/75e4d01b-7417-4abc-b267-8ffb393fb970?cid=DB210013"

Response

Status Code

  • Success

  • Bad Request

  • Unauthorized

  • Internal Server Error

No Sample Response

This method does not specify any sample responses.

Get data source nodes

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

GET /dvapiserver/v1/datasource_nodes

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "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 /dvapiserver/v2/discover_datasource

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "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 /dvapiserver/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" -H "Authorization: Bearer {token}" -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 /dvapiserver/v2/privileges/users

Request

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" -d "\[{\"table_name\":\"EMPLOYEE\",\"table_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 /dvapiserver/v2/privileges/users/{authid}

Request

Path Parameters

  • The identifier of the authorization.

    Example: PUBLIC

Query Parameters

  • The schema of the virtual object.

    Example: USER999

  • The name of the virtual object.

    Example: EMPLOYEE

  • curl -k -X DELETE -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/users/{authid}?table_schema={table_schema}&table_name={table_name}"

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 /dvapiserver/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" -H "Authorization: Bearer {token}" -d "{\"table_name\":\"EMPLOYEE\",\"table_schema\":\"USER999\",\"role_name\":\"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 /dvapiserver/v2/privileges/roles/{role_name}

Request

Path Parameters

  • The role to revoke from the user.

    Example: DV_ENGINEER

Query Parameters

  • The schema of the virtual object.

    Example: USER999

  • The name of the virtual object.

    Example: EMPLOYEE

  • curl -k -X DELETE -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/roles/{role_name}?table_schema={table_schema}&table_name={table_name}"

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 /dvapiserver/v1/privileges/objects/role/{rolename}

Request

Path Parameters

  • The name of the role.

    Example: User

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "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": {}
    }

Create remote table for data files in the remote connector

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

POST /dvapiserver/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" -H "Authorization: Bearer {token}" -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 /dvapiserver/v2/virtualization/tables

Request

request body

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" -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 /dvapiserver/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" -H "Authorization: Bearer {token}" "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 /dvapiserver/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" -H "Authorization: Bearer {token}" "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 /dv-caching/api/v1/caches

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dv-caching/api/v1/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 /dv-caching/api/v1/caches/{id}

Request

Path Parameters

  • The ID of the cache to be listed

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dv-caching/api/v1/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 /dv-caching/api/v1/caches/storage

Request

No Request Parameters

This method does not accept any request parameters.

  • curl -k -X GET -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dv-caching/api/v1/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 /dvapiserver/v2/integration/catalog/publish

Request

  • curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" -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"
        }
      ]
    }

Turn policy enforcement status on or off

Turns policy enforcement status on or off.

PUT /dvapiserver/v2/security/policy/status

Request

Query Parameters

  • Set the status of policy enforcement.

    Example: enabled

  • curl -k -X PUT -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer $token" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/security/policy/status?status=enabled"

Response

Status Code

  • Success

  • Unauthorized

  • Internal Server Error

No Sample Response

This method does not specify any sample responses.

Get policy enforcement status

Get policy enforcement status, return enabled or disabled

GET /dvapiserver/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" -H "content-type: application/json" -H "X-Db-Profile: $url_encoded_crn" -H "Authorization: Bearer $token" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/security/policy/status"

Response

Status Code

  • Success

  • Unauthorized

  • Internal Server Error

No Sample Response

This method does not specify any sample responses.