Introduction

Last updated: 2018-07-27

Use the Db2 on Cloud API to access data, view and create database objects, administer, and monitor your Db2 on Cloud service.

Attention: This is the DEPRECATED Version 3 of the Db2 on Cloud REST API documentation. To view the latest version, click here.

Root URL

The context root for the Db2 on Cloud API is /dbapi/v3/.

Error handling

This API uses standard HTTP response codes to indicate whether a method completed successfully. A 200 response indicates success. A 400 type response is some sort of failure.

Security

Every request must include the Authorization HTTP header with the value Bearer [access_token]. An access token can be obtained with the /auth/tokens endpoint, and it is used by Db2 on Cloud to identify who you are. Db2 on Cloud negotiates SSL connections using the TLS v1.2 protocol.

If you are using the latest version of cURL, protocol negotiation will
happen automatically using TLS v1.2.
If you are using an older version of cURL, you will need to specify the
`--tlsv1.2` option in your cURL commands.

Related APIs

You can also use the Db2 on Cloud Scaling REST API to schedule, delete or view the status of a scale operation of your Db2 on Cloud instance. See IBM Db2 on Cloud Scaling REST API.

Methods

Request a new access token

Authenticates the user credentials and returns an access token that can be used when invoking the operations.

POST /auth/tokens

Request

Request Body

Required*
UserCredentials

User credentials

Response

Response Body

AuthenticationToken

Token generated after successful authentication.

Status Code

  • 200

    Authentication token

  • 401

    Invalid credentials

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Creates a new authentication policy **ADMIN ONLY**

Creates a new authentication policy which can be used to control password parameters.

POST /auth_policies

Request

Request Body

Required*
AuthenticationPolicyIN

Authentication policy

Response

Response Body

AuthenticationPolicies

Collection of authentication policies

Status Code

  • 201

    Authentication policy created

  • 403

    Only administrators can execute this operation

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Lists all authentication policies **ADMIN ONLY**

Returns a list of authentication policies.

GET /auth_policies

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

AuthenticationPolicyOUT[]

Policy defining user's password rules.

Status Code

  • 200

    List of authentication policies

  • 403

    Only administrators can execute this operation

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns an authentication policy **ADMIN ONLY**

Returns an authentication policy.

GET /auth_policies/{id}

Request

Path Parameters

  • id
    Required*
    string

    policy ID

Response

Response Body

AuthenticationPolicyOUT

Policy defining user's password rules.

Status Code

  • 200

    Authentication policy

  • 403

    Only administrators can execute this operation

  • 404

    Policy not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Updates an authentication policy **ADMIN ONLY**

Updates an authentication policy.

PUT /auth_policies/{id}

Request

Path Parameters

  • id
    Required*
    string

    policy ID

Request Body

Required*
AuthenticationPolicyIN

Authentication policy

Response

Response Body

AuthenticationPolicyOUT

Policy defining user's password rules.

Status Code

  • 200

    Authentication policy

  • 403

    Only administrators can execute this operation

  • 404

    Policy not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Deletes an existing policy **ADMIN ONLY**

Deletes an existing policy. Only administratos can delete policies.

DELETE /auth_policies/{id}

Request

Path Parameters

  • id
    Required*
    string

    ID of the policy to be deleted.

Response

Status Code

  • 200

    Policy deleted

  • 403

    Removal of this policy is not allowed

  • 404

    The policy does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Sets a new password using dswebToken

Sets a new password using the dswebToken obtained from /auth/reset.

PUT /auth/password

Request

Request Body

Required*
UserPasswordReset

New password and dswebToken

Response

Response Body

UserPasswordResetResponse

Status Code

  • 200

    Password changed

  • 400

    Invalid or missing new password

  • 403

    If dswebToken is missing or invalid

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Requests a password reset based on user's email

An email is sent with the password reset link and code. To set a new password, the user can open the link on a web browser, or use the /auth/password endpoint providing the dswebToken. The dswebToken is valid for 12 hours.

POST /auth/reset

Request

Request Body

Required*
UserPasswordResetRequest

User's email address and ID

Response

Response Body

UserPasswordResetResponse

Status Code

  • 202

    Request accepted

  • 400

    Invalid or missing email address and userId

  • 403

    The user name and email address do not match.

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Lists all schemas in the database

Returns a list of schemas.

GET /schemas

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

Schemas[]

Collection of schemas

Status Code

  • 200

    List of schemas

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Creates a new schema **ADMIN ONLY**

Creates a new schema. Only administrators can create schemas directly. Regular users can create new schemas indirectly by creating a new table or other database object and specifying the name of the new schema where the object will be placed.

POST /schemas

Request

Request Body

Required*
Schema

schema information

Response

Response Body

Schema

Status Code

  • 201

    Schema created

  • 400

    Invalid parameters or schema already exists

  • 403

    Only administrators can create schemas directly

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns schema information

Returns schema information.

GET /schemas/{schema_name}

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

Response

Response Body

Schema

Status Code

  • 200

    Schema information

  • 404

    Schema not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Drops an empty schema

Drops an empty schema. An error is returned if the schema contains any objects.

DELETE /schemas/{schema_name}

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

Response

Response Body

object

Status Code

  • 200

    Success

  • 400

    Invalid parameters or schema is not empty

  • 403

    Not authorized to drop the schema

  • 404

    Schema not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Lists tables in a schema

Returns the list of tables in a schema.

GET /schemas/{schema_name}/tables

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

Response

Response Body

Tables

Collection of tables

Status Code

  • 200

    List of tables

  • 404

    Database or schema not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Creates a new table

Creates a new table.

POST /schemas/{schema_name}/tables

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

Request Body

Required*
TableCreate

table definition

Response

Response Body

TableInfo

Includes table definition and statistics. Note that statistics such as row count and size are not updated real time. Check the stats_timestamp value to know how current that information is.

Status Code

  • 201

    table info

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns information about a table

Return information about a table.

GET /schemas/{schema_name}/tables/{table_name}

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

  • table_name
    Required*
    string

    table name

Response

Response Body

TableInfo

Includes table definition and statistics. Note that statistics such as row count and size are not updated real time. Check the stats_timestamp value to know how current that information is.

Status Code

  • 200

    Table information

  • 404

    Table not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Drops a table

Drops a table.

DELETE /schemas/{schema_name}/tables/{table_name}

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

  • table_name
    Required*
    string

    table name

Response

Response Body

object

Status Code

  • 200

    success

  • 404

    Table not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns table data

Fetches the table data up to a maximum of 100,000 rows. Currently it's not possible to retrieve data from tables that contain CLOB, BLOB or DBCLOB values.

GET /schemas/{schema_name}/tables/{table_name}/data

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

  • table_name
    Required*
    string

    table name

Query Parameters

  • _limit
    integer

    Maximum number of rows to fetch. If ommited, it assumes the value 1000. Valid range is between 1 and 100,000.

    Default: 1000

Response

Response Body

TableData

Status Code

  • 200

    table data

  • 400

    Invalid parameters or unsupported column data type

  • 403

    Not authorized to access table data

  • 404

    Table not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Deletes all table data

Deletes all table data using a TRUNCATE operation.

DELETE /schemas/{schema_name}/tables/{table_name}/data

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

  • table_name
    Required*
    string

    table name

Response

Response Body

object

Status Code

  • 200

    success

  • 403

    Not authorized to access table data

  • 404

    Table not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Lists all data load jobs, load uses Db2 load utility technology

Lists all data load jobs for the user.

GET /load_jobs

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

DataLoadJobInfo[]

Information about a data load job

Status Code

  • 200

    Data load jobs

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Creates a data load job

Creates a data load job

POST /load_jobs

Request

Request Body

Required*
DataLoadJobRequest

Data load job details

Response

Response Body

DataLoadJobCreated[]

Confirmation of load job created

Status Code

  • 201

    load jobs.

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns details about a load job including its progress

Returns details about a load job including its progress.

GET /load_jobs/{id}

Request

Path Parameters

  • id
    Required*
    string

    Load job ID

Response

Response Body

DataLoadJobInfo

Information about a data load job

Status Code

  • 200

    Data load job

  • 403

    Not authorized to access load job

  • 404

    Job not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Removes load job from history

Removes a data load job from history. This operation has no effect on the data already loaded. In-progress jobs cannot be deleted.

DELETE /load_jobs/{id}

Request

Path Parameters

  • id
    Required*
    string

    Load job ID

Response

Response Body

object

Status Code

  • 200

    Load job deleted

  • 403

    Not authorized to delete load job

  • 404

    Job not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Dowloads log file for a data load job

Downloads log file for a data load job

GET /load_jobs/{id}/log

Request

Path Parameters

  • id
    Required*
    string

    Load job ID

Response

Response Body

DataLoadJobs

Collection of data load jobs

Status Code

  • 200

    Log file

  • 403

    Not authorized to access load log

  • 404

    Log not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Executes SQL statements

Executes one or more SQL statements as a background job. This endpoint returns a job ID that can be used to retrieve the results.

POST /sql_jobs

Request

Request Body

Required*
SQLJobCreate

SQL script and execution options

Response

Response Body

SQLJobSummary

Status Code

  • 201

    SQL execution job

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Fetches partial results of a SQL job execution

Returns the current status of a SQL job execution along with any results of SQL statements that have already been executed. Clients are supposed to poll this endpoint until the status returned is either 'completed', which indicates all SQL statements have completed executing, or 'failed', which indicates the job failed to execute and therefore is considered terminated. The returned list of results is not cumulative. That means, results that were fetched in a previous call will not be returned again, only new results (i.e. that were not fetched yet) will be included. For example, assuming a job with 10 SQL statements, the first call returns status "running" and 6 results, the second call returns status "running" and an empty list of results, a third call status "completed" and 4 results. Any subsequent calls would return status "completed" and an empty list of results.

GET /sql_jobs/{id}

Request

Path Parameters

  • id
    Required*
    string

    ID of the SQL execution job

Response

Response Body

SQLJobResults

Contains the results of executing the SQL statements associated with a SQL execution job

Status Code

  • 200

    Result of a SQL job

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns the results of a SQL query to CSV

Executes the specified SQL query and returns the data as a CSV file. The amount of data returned is limited to 100,000 rows.

POST /sql_query_export

Request

Request Body

Required*
SQLStatement

The SQL query to be executed

Response

Response Body

FileFolderMetadataList

Collection of files or folders metadata

Status Code

  • 200

    CSV file containing query results

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns metadata of an item in the user's home storage

Returns metadata of an item files and folders stored in the user's home storage.

GET /home/{path}

Request

Path Parameters

  • path
    Required*
    string

    Path of a file or folder

Response

Response Body

FileFolderMetadata

Describes a file or folder.

Status Code

  • 200

    Metadata about a file or folder

  • 404

    File or folder not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Deletes a file in the user's home storage

Deletes a file in the user's home storage

DELETE /home/{path}

Request

Path Parameters

  • path
    Required*
    string

    File path

Response

Response Body

object

Status Code

  • 200

    File deleted

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Download file from user's home storage

Download file from user's home storage.

GET /home_content/{path}

Request

Custom Headers

  • Accept
    string

    Allowable values: [application/json,application/octet-stream]

Path Parameters

  • path
    Required*
    string

    File path

Response

Response Body

FileFolderMetadata

Describes a file or folder.

Status Code

  • 200

    Metadata about a file or folder

  • 404

    File not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Uploads a file to the user's home storage

Uploads a file to a folder under the user's home storage. The target folder is automatically created if it does not exit.

POST /home_content/{path}

Request

Path Parameters

  • path
    Required*
    string

    Target folder

Response

Response Body

FileFolderMetadata[]

Describes a file or folder.

Status Code

  • 201

    Files successfully uploaded

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns overall status of system components **ADMIN ONLY**

Returns overall status of system components.

GET /monitor

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

ServiceStatus[]

Services status

Status Code

  • 200

    System status

  • 403

    Operation is only available to administrators

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Lists active database connections **ADMIN ONLY**

Returns a list of active database connections.

GET /monitor/connections

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

DatabaseConnections

Collection of active database connections

Status Code

  • 200

    Active connections

  • 403

    Operation is only available to administrators

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Terminates a database connection **ADMIN ONLY**

Terminates an active database connection.

DELETE /monitor/connections/{application_handle}

Request

Path Parameters

  • application_handle
    Required*
    string

    Application handle name

Response

Response Body

object

Status Code

  • 200

    Connection terminated

  • 403

    Operation is only available to administrators

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns current storage usage

Returns current storage usage.

GET /monitor/storage

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

SystemStorage

Status Code

  • 200

    Storage usage

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns storage usage history

Returns a list of storage usage daily stats. Currenlty the history of the last seven days is returned.

GET /monitor/storage/history

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

SystemStorageList

Collection of system storage stats

Status Code

  • 200

    Storage usage history

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Storage utilization by schema **ADMIN ONLY**

Returns a list where each element represents a schema and its corresponding logical and physical sizes. Physical size is the amount of storage in disk allocated to objects in the schema. Logical size is the actual object size, which might be less than the physical size (e.g. in the case of a logical table truncation).

GET /monitor/storage/by_schema

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

DatabaseStorageSchemas

Collection of schema's storage usage data

Status Code

  • 200

    Storage usage by schema

  • 403

    Operation is only available to administrators

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Storage utilization of a schema **ADMIN ONLY**

Returns logical and physical sizes for one schema. Physical size is the amount of storage in disk allocated to objects in the schema. Logical size is the actual object size, which might be less than the physical size (e.g. in the case of a logical table truncation).

GET /monitor/storage/by_schema/{schema_name}

Request

Path Parameters

  • schema_name
    Required*
    string

    schema name

Response

Response Body

DatabaseStorageSchema[]

Status Code

  • 200

    Storage usage for a schema

  • 403

    Operation is only available to administrators

  • 404

    Schema does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns the system's current settings

Returns the system's current settings.

GET /settings

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

SystemSettings

A collection of system settings.

Status Code

  • 200

    system settings

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Updates the system settings **ADMIN ONLY**

The only setting that can be modified is database_ssl_connections_enforced. By setting database_ssl_connections_enforced to true database connection requests must be secured. If set to false both secured and unsecured database connections are accepted. This operation is only available to system administrators.

PUT /settings

Request

Request Body

Required*
SystemSettingsUpdate

System settings

Response

Response Body

SystemSettings

A collection of system settings.

Status Code

  • 200

    system settings

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Generates a new Aspera token

Aspera is a high-speed file transfer technology that can be used to speed up file uploads to Db2 Warehouse on Cloud. This endpoint generates a new token that must be used when uploading files with Aspera.

POST /aspera

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

AsperaToken

Token generated for Aspera

Status Code

  • 201

    Aspera token

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Deletes an Aspera token

Deletes an Aspera token. You should delete a token after the upload is complete. Optionally provide the name of a file that was uploaded using Aspera to prepare it for loading.

DELETE /aspera/{token}

Request

Path Parameters

  • token
    Required*
    string

    Aspera token

Query Parameters

  • filename
    string

    Name of file that was uploaded

Response

Response Body

object

Status Code

  • 200

    Success

  • 404

    Token or file not found

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Analyzes CSV data to return a list of data types of its values

Schema discovery analyzes data in CSV format and returns a list of suggested data types that can be used when creating a table to store the CSV data.

POST /schema_discovery

Request

Request Body

Required*
SchemaDiscoveryCSVRequest

Data to analyze

Response

Response Body

SchemaDiscoveryResponse

Result of running schema discovery.

Status Code

  • 200

    Suggested table schema

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Returns the list of users

Administrators can retrieve the list of all users in the system. Regular users will receive a list containing only their own user profile.

GET /users

Request

No Request Parameters

This method does not accept any request parameters.

Response

Response Body

Users

Collection of users

Status Code

  • 200

    List of users

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Creates a new user. **ADMIN ONLY**

Creates a new user. This operation is only available to system administrators.

POST /users

Request

Request Body

Required*
User

User information

Response

Response Body

User

Status Code

  • 201

    User response

  • 400

    Invalid parameters or user already exists

  • 403

    Operation is only available to administrators

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Get a specific user by ID

Get a specific user by ID. System administrators may retrieve user information for any user. Regular users may only retrieve themselves.

GET /users/{id}

Request

Path Parameters

  • id
    Required*
    string

    ID of the user to be fetched

Response

Response Body

User

Status Code

  • 200

    User response

  • 403

    Access to this user is not allowed

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Updates an existing user

Updates an existing user. System administrators may update user information for any user. Regular users may only update themselves.

PUT /users/{id}

Request

Path Parameters

  • id
    Required*
    string

    ID of the user to be updated

Request Body

Required*
UserUpdate

User information

Response

Response Body

User

Status Code

  • 200

    User response

  • 403

    Access to this user is not allowed

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Deletes an existing user **ADMIN ONLY**

Deletes an existing user. Only administratos can delete users.

DELETE /users/{id}

Request

Path Parameters

  • id
    Required*
    string

    ID of the user to be deleted.

Response

Response Body

object

Status Code

  • 200

    User deleted

  • 403

    Removal of this user is not allowed

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Locks a user account indefinitely. **ADMIN ONLY**

Admin users can unlock users' accounts which have been locked out following repeated failed authentication attempts.

PUT /users/{id}/lock

Request

Path Parameters

  • id
    Required*
    string

    ID of the user who will be locked

Response

Response Body

User

Status Code

  • 200

    User response

  • 403

    Cannot edit this user

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Unlocks a user account **ADMIN ONLY**

Admin users can unlock users' accounts which have been locked out following repeated failed authentication attempts.

PUT /users/{id}/unlock

Request

Path Parameters

  • id
    Required*
    string

    ID of the user who will be unlocked

Response

Response Body

User

Status Code

  • 200

    User response

  • 403

    Cannot edit this user

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Get a specific user's connection privileges

Get a specific user's connection privileges. If restricted is set to 'yes', the user will only be allowed to make database connections from IP addresses or hostnames in the authorized_hosts list. System administrators may retrieve connection privilege information for any user. Regular users may only retrieve their own connection privileges.

GET /users/{id}/connection_privileges

Request

Path Parameters

  • id
    Required*
    string

    ID of the user who's information is to be fetched

Response

Response Body

UserConnectionPrivileges

Status Code

  • 200

    User's connection privileges

  • 403

    Access to this user's connection privileges is not allowed

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.

Updates an existing user's connection privileges **ADMIN ONLY**

Updates an existing user's connection privileges. If restricted is set to 'yes', the user will only be allowed to make database connections from IP addresses or hostnames in the authorized_hosts list. This operation is only available to system administrators. An administrator may set the privileges of all users other than themself.

PUT /users/{id}/connection_privileges

Request

Path Parameters

  • id
    Required*
    string

    ID of the user who's information is to be updated

Request Body

Required*
UserConnectionPrivileges

Connection privilege information

Response

Response Body

UserConnectionPrivileges

Status Code

  • 200

    Updated user connection privileges

  • 403

    Access to this user's connection privileges is not allowed

  • 404

    The user does not exist

  • default

    Error payload

No Sample Response

This method does not specify any sample responses.