Watson Query REST API | IBM Cloud API Docs

Introduction

Last updated: 2021-06-23

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

The Watson Query REST API connects to your service deployment, so you can manage your virtual data, data sources, and user roles. Additionally, by using the Watson Query REST API, you can virtualize and publish data to the default catalog in IBM 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 Watson Query API endpoint URL is based on your IBM Cloud Pak deployment URL.

Watson Query provides two different endpoint URLs that you can use depending on the method that you are using. If the method indicates that it uses the Data Management Console (DMC) URL, use the second endpoint URL.

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

https://{cpd_cluster_host}{:port}/icp4data-addons/{dmc-instance}/{namespace}/dbapi/{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 and your Data Management Console instance name is DMC-13845673250, you can access the APIs at https://www.example.com:31843/icp4data-databases/dv/{namespace}/dvapiserver/{method} or https://www.example.com:31843/icp4data-addons/DMC-13845673250/{namespace}/dbapi/{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/{method}"

or

curl -H "x-db-profile: {dv-instance}" -H "X-Namespace: {dv-namespace}" -H "Authorization: Bearer {token}" -X {request_method} "https://{cpd_cluster_host}{:port}/icp4data-addons/{dmc-instance}/{namespace}/dbapi/{method}"

Disabling SSL verification

The Watson Query 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

Watson Query 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.

Watson Query docs
Cloud Pak for Data APIs and SDKs

Returns all the service nodes.

GET /v2/agent_nodes

Request

No Request Parameters

This method does not accept any request parameters.

Example request

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

Response Body

agentNodesResponseV2

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

{
  "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"
  }
}

Deletes a data source connection from the Watson Query service.

DELETE /v2/datasource/connections/{connection_id}

Request

Path Parameters

connection_id
Required*
string
The connection identifier for the platform.

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

Query Parameters

cid
string
The connection identifier for Watson Query.

Example: DB210013

Example request

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

204
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

No Sample Response

This method does not specify any sample responses.

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

GET /v2/datasource/connections

Request

No Request Parameters

This method does not accept any request parameters.

Example request

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

Response Body

DatasourceConnectionsList

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

{
  "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/"
        }
      ]
    }
  ]
}

Adds a data source connection to the Watson Query service.

POST /v2/datasource/connections

Request

Request Body

Required*

PostDatasourceConnectionParameters

Example 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

Response Body

PostDatasourceConnection

Status Code

201
Created
400
Bad Request
401
Unauthorized
409
Conflict
500
Internal Server Error

No Sample Response

This method does not specify any sample responses.

Automatically discovers the data sources that can be connected to Watson Query.

GET /v2/discover_datasource

Request

No Request Parameters

This method does not accept any request parameters.

Example request

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

Response Body

discoverDatasourceResponseV2

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

{
  "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"
    }
  ]
}

Generates a script to install connectors on remote data sources.

GET /v2/endpoint_script

Request

Query Parameters

admin_host
Required*
string
Specifies the external hostname of the dv-engine service component.
admin_port
string
Specifies the external port of the dv-engine service component.

Default: 443
use_ssl
boolean
Specifies whether to use SSL connection for the remote data source.

Default: true
platform
string
Specifies the operating system of the remote data source.

Default: Linux
file_type
string
Specifies the type of the installation package.

Default: tar
java_home
string
Specifies the directory where Java is installed on the remote data source.

Default: /opt/ibm/java
install_path
string
Specifies the directory where you want to install the remote connector.

Default: /opt/ibm/dv
node_name
string
Specifies the service node.
node_desc
string
Specifies the service node description.
data_port
integer
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

Example request

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

Response Body

successResponseScript

Status Code

200
Success
400
Bad Request
401
Unauthorized
404
Not found
422
Unprocessable Entity
500
Internal Server Error

Example responses

{ "script": "string" }

Grants a user access to a specific virtual object.

POST /v2/privileges/users

Request

Request Body

Required*

object

Example 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

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

No Sample Response

This method does not specify any sample responses.

Revokes user access to the virtualized table.

DELETE /v2/privileges/users/{authid}

Request

Path Parameters

authid
Required*
string
The Watson Query user name. If this value is Public, this API revokes access privilege from all Watson Query users.

Example: PUBLIC

Query Parameters

table_name
Required*
string
The virtualized table's name.

Example: EMPLOYEE
table_schema
Required*
string
The virtualized table's schema name.

Example: dv_ibmid_060000s4y5

Example request

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/privileges/users/PUBLIC?table_schema=dv_ibmid_060000s4y5&table_name=EMPLOYEE"

Response

Status Code

204
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

No Sample Response

This method does not specify any sample responses.

Grants a user role access to a specific virtual object.

POST /v2/privileges/roles

Request

Request Body

Required*

object

Grants a role to manage virtual objects.

Example 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\",\"role_name\":\"DV_ENGINEER\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/privileges/roles"

Response

Response Body

successResponseScript

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

{
  "_messageCode_": "200",
  "message": "Success"
}

Revokes roles for a virtualized table.

DELETE /v2/privileges/roles/{role_name}

Request

Path Parameters

role_name
Required*
string
The Watson Query role. Values can be DV_ADMIN, DV_ENGINEER, DV_STEWARD, or DV_WORKER, which correspond to MANAGER, ENGINEER, STEWARD, and USER roles in the user interface.

Example: DV_ENGINEER

Query Parameters

table_name
Required*
string
The virtualized table's name.

Example: EMPLOYEE
table_schema
Required*
string
The virtualized table's schema name.

Example: dv_ibmid_060000s4y5

Example request

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/privileges/roles/DV_ENGINEER?table_schema=dv_ibmid_060000s4y5&table_name=EMPLOYEE"

Response

Status Code

204
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

No Sample Response

This method does not specify any sample responses.

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

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

Request

Path Parameters

rolename
Required*
string
The name of the role.

Example: User

Example request

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

Response Body

ObjectsForRoleResponse

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

{ "objects": {} }

Enforcement of data protection rules is enabled automatically when an IBM Knowledge Catalog instance is provisioned.

PUT /v2/security/policy/status

Request

Query Parameters

status
Required*
string
Sets the status of the policy enforcement.

Example: enabled

Example request

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

Response

Response Body

Status Code

200
Success
401
Unauthorized
500
Internal Server Error

Example responses

{ "status": "enabled" }

Gets the policy enforcement status. The function returns enabled or disabled depending on the status.

GET /v2/security/policy/status

Request

No Request Parameters

This method does not accept any request parameters.

Example request

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/security/policy/status"

Response

Response Body

Status Code

200
Success
401
Unauthorized
500
Internal Server Error

Example responses

{ "status": "enabled" }

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

POST /v2/virtualize/files

Request

Request Body

Required*

object

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

Example request

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

Response Body

successResponse

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

{ "message": "message" }

Transforms a given data source table into a virtual table.

POST /v2/virtualization/tables

Request

Request Body

Required*

object

request body

Example request

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/virtualization/tables"

Response

Response Body

successResponse

Status Code

201
Created
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

{ "message": "string" }

Removes the specified table. You must specify the schema, name, and type. You must use the Data Management Console (DMC) endpoint URL.

POST /v4/dv/delete_virtualize_task

Request

Custom Headers

x-db-profile
Required*
string
The dv instance.

Example: dv-1683880621681965
X-Namespace
Required*
string
The namespace that the dv instance is in.

Example: cpd47x

Request Body

Required*

object

Example request

curl -k -X POST "https://{cpd_cluster_host}{:port}/icp4data-addons/{dmc-instance}/{namespace}/dbapi/v4/dv/delete_virtualize_task" -H "Authorization: Bearer {token}" -H "x-db-profile: {dv_instance}" -H "X-Namespace: {dv_namespace}" -H "Content-Type: application/json" -d "{\"delete_assets\": [{\"virtual_schema\": \"string\",\"virtual_name\": \"string\",\"type\": \"table\"}]}"

Response

Response Body

object

Status Code

200
Success
400
Bad Request
500
Internal Server Error

Example responses

Status 200

{
  "response": {
    "value": {
      "delete_assets_result": {
        "delete_sucess": [
          {
            "virtual_schema": "schema1",
            "virtual_name": "table1"
          }
        ],
        "delete_failed": [
          {
            "virtual_schema": "schema2",
            "virtual_name": "table2",
            "message": "SQLExecute: {42704} [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"schema2.table2\" is an undefined name.  SQLSTATE=42704\n"
          }
        ]
      }
    }
  }
}

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

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

Request

Path Parameters

schema_name
Required*
string
The schema of table to be deleted.
table_name
Required*
string
The name of table to be deleted.

Example request

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

Response Body

successResponseScript

Status Code

200
Success
400
Bad Request
500
Internal Server Error

Example responses

Status 200

{
  "_messageCode_": "200",
  "message": "Success"
}

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

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

Request

Path Parameters

schema_name
Required*
string
The schema of the view to be deleted.
view_name
Required*
string
The name of the view to be deleted.

Example request

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

Response Body

successResponseScript

Status Code

200
Success
400
Bad Request
500
Internal Server Error

Example responses

Status 200

{
  "_messageCode_": "200",
  "message": "Success"
}

Create a new cache using the given SQL definition.

POST /v2/caching/caches

Request

Query Parameters

dry_run
boolean
Indicates whether to verify that the cache can be created using the SQL that was was supplied. If dry_run is set to true, the cache is not created, it is just validated.

Request Body

Required*

CachePrototype

A JSON body with the cache's SQL definition and a name for the cache.

Examples:

View

Example request

curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" -d "{\"name\":\"cache1\",\"query\":\"SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6\",\"refresh_schedule\":\"* * */5 * *\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/caching/caches"

Response

Response Body

Cache

A cache and its metadata.

Status Code

200
Cache has been created and will start to populate.
204
Cache SQL validated
400
Client side error
401
Unauthorized operation
500
Server side error

Example responses

Status 200

{
  "name": "cache1",
  "id": "DV20220920162021591055",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T16:20:21.497519Z",
  "last_modified_at": "2022-09-20T16:20:21.497519Z",
  "state": "populating",
  "size": 0,
  "cardinality": 0,
  "time_taken_for_refresh": 0,
  "refresh_count": 0,
  "hit_count": 0,
  "refresh_schedule": "* * */5 * *",
  "refresh_schedule_description": "every minute every 5 days"
}

Status 400

{
  "status_code": 400,
  "trace": "d50ea1bf-438a-42fb-84e2-f0b0fe95a6ac",
  "errors": [
    {
      "code": "",
      "message": "The request was invalid. Cache definition/query null is invalid",
      "more_info": ""
    }
  ]
}

Return the metadata of the specified cache ID.

GET /v2/caching/caches/{id}

Request

Path Parameters

id
Required*
string
The ID of the cache

Possible values: 20 ≤ length ≤ 25, Value must match regular expression ^DV\d+$
Example: DV20220920162021591055

Example request

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

Response

Response Body

Cache

A cache and its metadata.

Status Code

200
Successfully retrieved information about cache.
400
Client side error
401
Unauthorized operation
404
Object not found
500
Server side error

Example responses

Status 200

{
  "name": "cache1",
  "id": "DV20220920162021591055",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T16:20:21.497519Z",
  "last_modified_at": "2022-09-20T16:20:43.773141Z",
  "last_refreshed_at": "2022-09-20T16:20:34.592605Z",
  "state": "enabled",
  "size": 6,
  "cardinality": 5,
  "time_taken_for_refresh": 0,
  "refresh_count": 0,
  "hit_count": 0,
  "refresh_schedule": "* * */5 * *",
  "refresh_schedule_description": "every minute every 5 days"
}

Drop and delete the cache with the specified ID.

DELETE /v2/caching/caches/{id}

Request

Path Parameters

id
Required*
string
The ID of the cache to drop.

Possible values: 20 ≤ length ≤ 25, Value must match regular expression ^DV\d+$

Example request

curl -k -X DELETE -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/caching/caches/{id}"

Response

Response Body

Cache

A cache and its metadata.

Status Code

202
This request will change the cache state to deleting. When the cache has been deleted, its state will turn to deleted.
400
Client side error
401
Unauthorized operation
404
Object not found
409
Operation cannot be performed
500
Server side error

Example responses

Status 202

{
  "name": "cache2",
  "id": "DV20220920162021591055",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T16:20:21.497519Z",
  "last_modified_at": "2022-09-20T16:31:28.816238Z",
  "last_refreshed_at": "2022-09-20T16:20:34.592605Z",
  "state": "deleting",
  "size": 6,
  "cardinality": 5,
  "time_taken_for_refresh": 26443,
  "refresh_count": 1,
  "hit_count": 0,
  "refresh_schedule": "10 * * * *",
  "refresh_schedule_description": "every hour at minute 10"
}

Edit the name of the cache with the specific ID, the refresh schedule of the cache, or both.

PATCH /v2/caching/caches/{id}

Request

Path Parameters

id
Required*
string
The ID of the cache to edit.

Possible values: 20 ≤ length ≤ 25, Value must match regular expression ^DV\d+$

Request Body

Required*

CachePatch

A JSON body with the cache's new name, new refresh schedule, or both.

Examples:

View

Example request

curl -k -X PATCH -H "cache-control: no-cache" -H "content-type: application/json" -H "Authorization: Bearer {token}" -d "{\"name\":\"cache2\",\"refresh_schedule\":\"10 * * * *\"}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/caching/caches/{id}"

Response

Response Body

Cache

A cache and its metadata.

Status Code

200
Successfully edited cache
400
Client side error
401
Unauthorized operation
404
Object not found
500
Server side error

Example responses

Status 200

{
  "name": "cache2",
  "id": "DV20220920162021591055",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T16:20:21.497519Z",
  "last_modified_at": "2022-09-20T16:31:28.816238Z",
  "last_refreshed_at": "2022-09-20T16:20:34.592605Z",
  "state": "enabled",
  "size": 6,
  "cardinality": 5,
  "time_taken_for_refresh": 0,
  "refresh_count": 0,
  "hit_count": 0,
  "refresh_schedule": "10 * * * *",
  "refresh_schedule_description": "every hour at minute 10"
}

Enable an inactive cache with the specified ID.

POST /v2/caching/caches/{id}/enable

Request

Path Parameters

id
Required*
string
The ID of the cache to be enabled.

Possible values: 20 ≤ length ≤ 25, Value must match regular expression ^DV\d+$

Example request

curl -k -X POST -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/caching/caches/{id}/enable"

Response

Response Body

Cache

A cache and its metadata.

Status Code

202
This request will change the cache state to enabling. When the cache has been enabled, its state will turn to enabled.
400
Client side error
401
Unauthorized operation
404
Object not found
409
Operation cannot be performed
500
Server side error

Example responses

Status 202

{
  "name": "cache2",
  "id": "DV20220920162021591055",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T16:20:21.497519Z",
  "last_modified_at": "2022-09-20T16:31:28.816238Z",
  "last_refreshed_at": "2022-09-20T16:20:34.592605Z",
  "state": "enabling",
  "size": 6,
  "cardinality": 5,
  "time_taken_for_refresh": 0,
  "refresh_count": 0,
  "hit_count": 0,
  "refresh_schedule": "10 * * * *",
  "refresh_schedule_description": "every hour at minute 10"
}

Disable an active cache with the specified ID.

POST /v2/caching/caches/{id}/disable

Request

Path Parameters

id
Required*
string
The ID of the cache to disable.

Possible values: 20 ≤ length ≤ 25, Value must match regular expression ^DV\d+$

Example request

curl -k -X POST -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/caching/caches/{id}/disable"

Response

Response Body

Cache

A cache and its metadata.

Status Code

202
This request will change the cache state to disabling. When the cache has been disabled, its state will turn to disabled.
400
Client side error
401
Unauthorized operation
404
Object not found
409
Operation cannot be performed
500
Server side error

Example responses

Status 202

{
  "name": "cache2",
  "id": "DV20220920162021591055",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 6",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T16:20:21.497519Z",
  "last_modified_at": "2022-09-20T16:31:28.816238Z",
  "last_refreshed_at": "2022-09-20T16:20:34.592605Z",
  "state": "disabling",
  "size": 6,
  "cardinality": 5,
  "time_taken_for_refresh": 0,
  "refresh_count": 0,
  "hit_count": 0,
  "refresh_schedule": "10 * * * *",
  "refresh_schedule_description": "every hour at minute 10"
}

Refresh an active cache with the specified ID.

POST /v2/caching/caches/{id}/refresh

Request

Path Parameters

id
Required*
string
The ID of the cache to refresh.

Possible values: 20 ≤ length ≤ 25, Value must match regular expression ^DV\d+$

Example request

curl -k -X POST -H "cache-control: no-cache" -H "Authorization: Bearer {token}" "https://{cpd_cluster_host}{:port}/icp4data-databases/dv/{namespace}/dvapiserver/v2/caching/caches/{id}/refresh"

Response

Response Body

Cache

A cache and its metadata.

Status Code

202
To refresh a cache, its current state must be enabled. When the cache has been refreshed, its state will change to enabled.
400
Client side error
401
Unauthorized operation
404
Object not found
409
Operation cannot be performed
500
Server side error

Example responses

Status 202

{
  "name": "cache3",
  "id": "DV20220920191349389436",
  "query": "SELECT * FROM ADMIN.CUSTOMER WHERE C_CUSTOMER_SK < 7",
  "owner_id": "ADMIN",
  "created_at": "2022-09-20T19:13:49.28874Z",
  "last_modified_at": "2022-09-21T00:07:40.689536Z",
  "last_refreshed_at": "2022-09-21T00:07:18.403959Z",
  "last_used_at": "2022-09-21T00:02:24.962Z",
  "state": "refreshing",
  "size": 6,
  "cardinality": 6,
  "time_taken_for_refresh": 17553,
  "refresh_count": 22,
  "hit_count": 6,
  "refresh_schedule": "* * */5 * *",
  "refresh_schedule_description": "every minute every 5 days"
}

List all active, inactive, and deleted caches.

GET /dv-caching/api/v1/caches

Request

No Request Parameters

This method does not accept any request parameters.

Example request

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

Response Body

listCachesResponseV2

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

{
  "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 specific cache in Watson Query.

GET /dv-caching/api/v1/caches/{id}

Request

Path Parameters

id
Required*
string
The ID of the cache to be listed.

Example request

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

Response Body

listSingleCacheResponseV2

Status Code

200
Success
400
Bad Request
401
Unauthorized
404
Not found
500
Internal Server Error

Example responses

Status 200

{
  "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 total cache storage and used capacities for active and inactive caches in Watson Query.

GET /dv-caching/api/v1/caches/storage

Request

No Request Parameters

This method does not accept any request parameters.

Example request

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

Response Body

cacheStorageResponseV2

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

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

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

POST /v2/integration/catalog/publish

Request

Request Body

Required*

object

Example 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

Response Body

getAssignPublishResponse

Status Code

200
Success
400
Bad Request
401
Unauthorized
500
Internal Server Error

Example responses

Status 200

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

Publishes your virtual data to a catalog in IBM Knowledge Catalog asynchronously. You must use the Data Management Console (DMC) endpoint URL.

POST /v4/dv/catalog/publish

Request

Custom Headers

x-db-profile
Required*
string
The dv instance.

Example: dv-1683880621681965
X-Namespace
Required*
string
The namespace that the dv instance is in.

Example: cpd47x

Request Body

Required*

object

Example request

curl -k -X POST "https://{cpd_cluster_host}{:port}/icp4data-addons/{dmc-instance}/{namespace}/dbapi/v4/dv/catalog/publish" -H "Authorization: Bearer {token}" -H "x-db-profile: {dv_instance}" -H "X-Namespace: {dv_namespace}" -H "Content-Type: application/json" -d "{\"assets\":[{\"table\":\"customer_view\",\"schema\":\"ADMIN\"}],\"allow_duplicates\":false,\"catalog_id\":\"b5ce2ed2-d3ee-45af-8d1b-c2aa7dd6bd44\",\"catalog_name\":\"testcat\"}"

Response

Response Body

object

Status Code

200
Success indicates that the task was created successfully. Use the API Fetch publishing task results to fetch publishing task results.
400
Bad Request
500
Internal Server Error

Example responses

Status 200

{
  "message": "Create task successfully"
}

Fetch publishing task results of the specified virtualized object. You must use the Data Management Console (DMC) endpoint URL.

GET /v4/dv/published_assignment_status

Request

Query Parameters

virtual_schema
Required*
string
The schema of the virtual object.

Example: schema1
virtual_name
Required*
string
The name of the virtual object.

Example: table1
limit
integer
Number of returned task results.

Example: 3

Example request

curl -k -X GET -H "Authorization: Bearer {token}" -H "x-db-profile: {dv-instance}" -H "X-Namespace: {dv-namespace}" "https://{HOSTNAME}:{PORT_NUMBER}/icp4data-addons/{dmc-instance}/{namespace}/dbapi/v4/dv/published_assignment_status?schema=schema1&name=table1&type=Publish&limit=3"

Response

Response Body

getAssignPublishResponse

Status Code

200
Success
400
Bad Request
500
Internal Server Error

Example responses

Status 200

{
  "total": 1,
  "data": [
    {
      "name": "testcat",
      "status": "Success",
      "object_id": "d0e543dc-2775-4328-b5e4-cf61794a8354",
      "type": "Publish",
      "sub_type": "Catalog",
      "create_on": 1695307697670,
      "begin_time": 1695307680359,
      "creator_by": "DV_ADMIN",
      "message": "{\"duplicate_assets\":null,\"failed_assets\":null,\"published_assets\":[{\"schema_name\":\"schema1\",\"table_name\":\"table1\",\"wkc_asset_id\":\"daf76500-1e37-4ddc-9f20-080bb3722206\"}]}\n",
      "submit_time": 1695307680264
    }
  ]
}

Introduction

Authentication

Endpoint URL

Disabling SSL verification

Error handling

Related information

Methods

Get all service nodes

Request

No Request Parameters

Response

Response Body

agent_node_array

agent_class

dscount

hostname

is_docker

node_description

node_name

os_user

port

Status Code

200

400

401

500

Delete data source connection

Request

Path Parameters

connection_id

Query Parameters

cid

Response

Status Code

204

400

401

500

No Sample Response

Get data source connections

Request

No Request Parameters

Response

Response Body

datasource_connections

node_name

node_description

agent_class

hostname

port

os_user

is_docker

dscount

data_sources

cid

dbname

connection_id

srchostname

srcport

srctype

usr

uri

status

connection_name

Status Code

200

400

401

500

Add data source connection

Request

Request Body

datasource_type

name

origin_country

properties

access_token

account_name

api_key

auth_type