Introduction
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.
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
Creates a new authentication policy **ADMIN ONLY**
Creates a new authentication policy which can be used to control password parameters.
POST /auth_policies
Request
Authentication policy
Policy ID.
A display name for the policy.
Number of previously used passwords kept in the history. When a user changes its password, the new password is rejected if it is found in the history. If set to 0 no password history is maintained.
Number of days a password is valid for. After this period, login attempts will be rejected and the users will be required to change their password. If set to 0 passwords will never expire.
Number of failed logins before the user account is locked. If set to 0 the account is not locked and any number of consecutive invalid login attempts are allowed.
Number of minutes an account will be locked after too many failed login attempts. After this period the account will be automatically unlocked. It must be a number greater than zero. This parameter has no effect if failed_login_attempts is set to zero.
Minimum number of characters for passwords
Lists all authentication policies **ADMIN ONLY**
Returns a list of authentication policies.
GET /auth_policies
Response
Policy defining user's password rules.
Policy ID.
A display name for the policy.
Number of previously used passwords kept in the history. When a user changes its password, the new password is rejected if it is found in the history. If set to 0 no password history is maintained.
Number of days a password is valid for. After this period, login attempts will be rejected and the users will be required to change their password. If set to 0 passwords will never expire.
Number of failed logins before the user account is locked. If set to 0 the account is not locked and any number of consecutive invalid login attempts are allowed.
Number of minutes an account will be locked after too many failed login attempts. After this period the account will be automatically unlocked. It must be a number greater than zero. This parameter has no effect if failed_login_attempts is set to zero.
Minimum number of characters for passwords
Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
List of authentication policies
Only administrators can execute this operation
Error payload
No Sample Response
Returns an authentication policy **ADMIN ONLY**
Returns an authentication policy.
GET /auth_policies/{id}
Response
Policy defining user's password rules.
Policy ID.
A display name for the policy.
Number of previously used passwords kept in the history. When a user changes its password, the new password is rejected if it is found in the history. If set to 0 no password history is maintained.
Number of days a password is valid for. After this period, login attempts will be rejected and the users will be required to change their password. If set to 0 passwords will never expire.
Number of failed logins before the user account is locked. If set to 0 the account is not locked and any number of consecutive invalid login attempts are allowed.
Number of minutes an account will be locked after too many failed login attempts. After this period the account will be automatically unlocked. It must be a number greater than zero. This parameter has no effect if failed_login_attempts is set to zero.
Minimum number of characters for passwords
Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
Authentication policy
Only administrators can execute this operation
Policy not found
Error payload
No Sample Response
Updates an authentication policy **ADMIN ONLY**
Updates an authentication policy.
PUT /auth_policies/{id}
Request
Path Parameters
policy ID
Authentication policy
Policy ID.
A display name for the policy.
Number of previously used passwords kept in the history. When a user changes its password, the new password is rejected if it is found in the history. If set to 0 no password history is maintained.
Number of days a password is valid for. After this period, login attempts will be rejected and the users will be required to change their password. If set to 0 passwords will never expire.
Number of failed logins before the user account is locked. If set to 0 the account is not locked and any number of consecutive invalid login attempts are allowed.
Number of minutes an account will be locked after too many failed login attempts. After this period the account will be automatically unlocked. It must be a number greater than zero. This parameter has no effect if failed_login_attempts is set to zero.
Minimum number of characters for passwords
Response
Policy defining user's password rules.
Policy ID.
A display name for the policy.
Number of previously used passwords kept in the history. When a user changes its password, the new password is rejected if it is found in the history. If set to 0 no password history is maintained.
Number of days a password is valid for. After this period, login attempts will be rejected and the users will be required to change their password. If set to 0 passwords will never expire.
Number of failed logins before the user account is locked. If set to 0 the account is not locked and any number of consecutive invalid login attempts are allowed.
Number of minutes an account will be locked after too many failed login attempts. After this period the account will be automatically unlocked. It must be a number greater than zero. This parameter has no effect if failed_login_attempts is set to zero.
Minimum number of characters for passwords
Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
Authentication policy
Only administrators can execute this operation
Policy not found
Error payload
No Sample Response
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
Response
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.
The table name
The schema name
User provided comments
Timestamp when statistics were last collected for the table. It can be empty for newly created tables.
Number of rows in the table
Storage size in kilobytes used by table data
Storage size in kilobytes allocated for the table. It is greater or equal to the storage in use. For instance, when rows are deleted storage might remain allocated for the table until a REORG operation reclaims the unused space.
Date when the table data was last read, inserted or updated
stats
It can assume values
Status Code
table info
Error payload
No Sample Response
Returns information about a table
Return information about a table.
GET /schemas/{schema_name}/tables/{table_name}
Response
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.
The table name
The schema name
User provided comments
Timestamp when statistics were last collected for the table. It can be empty for newly created tables.
Number of rows in the table
Storage size in kilobytes used by table data
Storage size in kilobytes allocated for the table. It is greater or equal to the storage in use. For instance, when rows are deleted storage might remain allocated for the table until a REORG operation reclaims the unused space.
Date when the table data was last read, inserted or updated
stats
It can assume values
Status Code
Table information
Table not found
Error payload
No Sample Response
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
Lists all data load jobs, load uses Db2 load utility technology
Lists all data load jobs for the user.
GET /load_jobs
Response
Information about a data load job
Load job ID
User ID of who requested the load
Execution status of a data load job
Data load request
Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
Data load jobs
Error payload
No Sample Response
Request
Data load job details
Type of data source. It can take the values 'SERVER', 'S3', or 'SOFTLAYER'.
Allowable values: [
SERVER
,S3
,SOFTLAYER
]Schema name where the target table is located
Table name where data should be loaded
Modifies how the source data should be interpreted
If set to 'INSERT' data is appended to the existing table data. If set to 'REPLACE' the table data is replaced with the data being loaded.
Allowable values: [
INSERT
,REPLACE
]Default:
INSERT
Maximum number of rows to be loaded. If set to zero or not specified, all rows from the source data are loaded. Not supported in multi-partitioned (MPP) Db2 Warehouse on Cloud environments.
Default:
0
Maximum number of warnings that are tolerated before failing the load operation. A warning is generated for each row that is not loaded correctly.
Default:
1000
When set to execute, the source data is analyzed to discover the data types of each column. Then the target table is created using the columns and data types found. Currently, this option is only allowed when load_source is set to SERVER.
Required when load_type is set to "S3" or "SOFTLAYER". It specifies how to located the data to be loaded from a cloud storage service.
Required when load_type is set to "SERVER". It specifies how to source filein the server.
Required when load_type is set to "STREAM".
Response
Confirmation of load job created
Load job ID
User ID of who created the job
Describes the source of the data being loaded
If set to 'INSERT' data is appended to the existing table data. If set to 'REPLACE' the table data is replaced with the data being loaded. The default is 'INSERT'.
Target database name
Schema name where the target table is located
Table name where data will be loaded to
Job overall status
Status Code
load jobs.
Error payload
No Sample Response
Returns details about a load job including its progress
Returns details about a load job including its progress.
GET /load_jobs/{id}
Response
Information about a data load job
Load job ID
User ID of who requested the load
Execution status of a data load job
Data load request
Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
Data load job
Not authorized to access load job
Job not found
Error payload
No Sample Response
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
SQL script and execution options
The SQL script to be executed
Maximum number of rows that will be fetched for each result set.
SQL statement terminator. A character that is used to mark the end of a SQL statement when the provided SQL script contains multiple statements.
If 'yes', the job stops executing at the first statement that returns an error. If 'no', the job continues executing if one or more statements returns an error.
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}
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}
Response
Describes a file or folder.
Relative URL path for this file or folder.
Size in bytes. The size of a folder is the sum of the files directly under it, excluding subfolders.
List of files and folders
Relative URL path for this file or folder.
Size in bytes. The size of a folder will be -1.
contents
Status Code
Metadata about a file or folder
File or folder not found
Error payload
No Sample Response
Download file from user's home storage
Download file from user's home storage.
GET /home_content/{path}
Request
Custom Headers
Allowable values: [
application/json
,application/octet-stream
]
Path Parameters
File path
Response
Describes a file or folder.
Relative URL path for this file or folder.
Size in bytes. The size of a folder is the sum of the files directly under it, excluding subfolders.
List of files and folders
Relative URL path for this file or folder.
Size in bytes. The size of a folder will be -1.
contents
Status Code
Metadata about a file or folder
File not found
Error payload
No Sample Response
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}
Response
Describes a file or folder.
Relative URL path for this file or folder.
Size in bytes. The size of a folder is the sum of the files directly under it, excluding subfolders.
List of files and folders
Relative URL path for this file or folder.
Size in bytes. The size of a folder will be -1.
contents
Status Code
Files successfully uploaded
Error payload
No Sample Response
Returns overall status of system components **ADMIN ONLY**
Returns overall status of system components.
GET /monitor
Response
Services status
Status of the database service.
Possible values: [
online
,offline
]Status of the authentication service.
Possible values: [
online
,offline
]Warnings or errors reported while checking status of services.
Status Code
System status
Operation is only available to administrators
Error payload
No Sample Response
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
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}
Response
Schema name
Amount of disk space logically allocated for row-organized data in all tables in the schema, reported in kilobytes.
Amount of disk space logically allocated for the indexes defined in all tables in the schema, reported in kilobytes.
Amount of disk space logically allocated for long field data in all tables in the schema, reported in kilobytes.
Amount of disk space logically allocated for LOB data in all tables in the schema, reported in kilobytes.
Amount of disk space logically allocated for XML data in all tables in the schema, reported in kilobytes.
Amount of disk space logically allocated for column-organized data in all tables in the schema, reported in kilobytes.
Total amount of logically allocated disk space for all tables in the schema, reported in kilobytes.
Amount of disk space physically allocated in all tables in the schema, reported in kilobytes.
Amount of disk space physically allocated for the indexes defined in all tables in the schema, reported in kilobytes.
Amount of disk space physically allocated for long field data in all tables in the schema, reported in kilobytes.
Amount of disk space logically allocated for LOB data in all tables in the schema, reported in kilobytes.
Amount of disk space physically allocated for XML data in all tables in the schema, reported in kilobytes.
Amount of disk space physically allocated for column-organized data in all tables in the schema, reported in kilobytes.
Total amount of physically allocated disk space for all tables in the schema, reported in kilobytes.
Status Code
Storage usage for a schema
Operation is only available to administrators
Schema does not exist
Error payload
No Sample Response
Response
A collection of system settings.
Product name
Host name or IP address of the database server
Port number for non-secured database connections
Port number for secured database connections
Accepts values 'yes' and 'no'. When set to 'yes' clients can only connect to the database server using secured connections
URL to access the web console
Number of physical nodes in the database cluster
Subscription plan
The environment where the service is running
Name of the environment. For instance, cloud provider such as SoftLayer
Environment which can vary based on type of environment.
Name of the service
For cloud environment, it describes the Geo location where the service is running
details
environment
Status Code
system settings
Error payload
No Sample Response
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
System settings
Accepts values 'yes' and 'no'. When set to 'yes' clients can only connect to the database server using secured connections
Response
A collection of system settings.
Product name
Host name or IP address of the database server
Port number for non-secured database connections
Port number for secured database connections
Accepts values 'yes' and 'no'. When set to 'yes' clients can only connect to the database server using secured connections
URL to access the web console
Number of physical nodes in the database cluster
Subscription plan
The environment where the service is running
Name of the environment. For instance, cloud provider such as SoftLayer
Environment which can vary based on type of environment.
Name of the service
For cloud environment, it describes the Geo location where the service is running
details
environment
Status Code
system settings
Error payload
No Sample Response
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
Data to analyze
Single character can be used as column delimiter. The character can also be specified using the format 0xJJ, where JJ is the hexadecimal representation of the character. Valid delimiters can be hexadecimal values from 0x00 to 0x7F, except for binary zero (0x00), line-feed (0x0A), carriage return (0x0D), space (0x20), and decimal point (0x2E).
Accepts values 'yes' and 'no'. The value 'yes' indicates the first row is a header with the column names and should be ignored whe analyzing the data. The value 'no' indicates all rows will be considered for analysis.
The CSV data to analyze. It should be in json array string format, such as ['Title, Year, Producer','City of God,2002, Katia Lund','Rain,1292,Christine Jeffs','2001: A Space Odyssey,1969, Stanley Kubrick'].
Creates a new user. **ADMIN ONLY**
Creates a new user. This operation is only available to system administrators.
POST /users
Request
User information
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Allowable values: [
bluuser
,bluadmin
]The user's email address
The user's ID. It must be between 1 and 12 characters and may only contain letters a-z (lower-case), numbers 0-9, and the special characters '#', '@', or '$'. It may not be "guests", "admins", "users", "local", "idax", "public", "ibm", "dsweb", "sqlj", "root" or "gopher". It cannot begin with "bluadmin", "db2inst1", "nullid", "ibm", "sql" or "sys".
The user's password. It must have a minimum of twelve characters. It must contain at least one each of upper-case letters, lower-case letters, numbers, and special characters.
If set to 'yes', it indicates the user account is locked, which disallows the user from logging into the system
Allowable values: [
yes
,no
]Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Response
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Possible values: [
bluuser
,bluadmin
]The user's email address
The user's ID. It must be between 1 and 12 characters and may only contain letters a-z (lower-case), numbers 0-9, and the special characters '#', '@', or '$'. It may not be "guests", "admins", "users", "local", "idax", "public", "ibm", "dsweb", "sqlj", "root" or "gopher". It cannot begin with "bluadmin", "db2inst1", "nullid", "ibm", "sql" or "sys".
The user's password. It must have a minimum of twelve characters. It must contain at least one each of upper-case letters, lower-case letters, numbers, and special characters.
If set to 'yes', it indicates the user account is locked, which disallows the user from logging into the system
Possible values: [
yes
,no
]Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
User response
Invalid parameters or user already exists
Operation is only available to administrators
Error payload
No Sample Response
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}
Response
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Possible values: [
bluuser
,bluadmin
]The user's email address
The user's ID. It must be between 1 and 12 characters and may only contain letters a-z (lower-case), numbers 0-9, and the special characters '#', '@', or '$'. It may not be "guests", "admins", "users", "local", "idax", "public", "ibm", "dsweb", "sqlj", "root" or "gopher". It cannot begin with "bluadmin", "db2inst1", "nullid", "ibm", "sql" or "sys".
The user's password. It must have a minimum of twelve characters. It must contain at least one each of upper-case letters, lower-case letters, numbers, and special characters.
If set to 'yes', it indicates the user account is locked, which disallows the user from logging into the system
Possible values: [
yes
,no
]Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
User response
Access to this user is not allowed
The user does not exist
Error payload
No Sample Response
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 of the user to be updated
User information
The user's ID
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Allowable values: [
bluadmin
,bluuser
]The user's email address
Current password must be provided when user updates his own profile.
New password
Response
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Possible values: [
bluuser
,bluadmin
]The user's email address
The user's ID. It must be between 1 and 12 characters and may only contain letters a-z (lower-case), numbers 0-9, and the special characters '#', '@', or '$'. It may not be "guests", "admins", "users", "local", "idax", "public", "ibm", "dsweb", "sqlj", "root" or "gopher". It cannot begin with "bluadmin", "db2inst1", "nullid", "ibm", "sql" or "sys".
The user's password. It must have a minimum of twelve characters. It must contain at least one each of upper-case letters, lower-case letters, numbers, and special characters.
If set to 'yes', it indicates the user account is locked, which disallows the user from logging into the system
Possible values: [
yes
,no
]Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
User response
Access to this user is not allowed
The user does not exist
Error payload
No Sample Response
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
Response
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Possible values: [
bluuser
,bluadmin
]The user's email address
The user's ID. It must be between 1 and 12 characters and may only contain letters a-z (lower-case), numbers 0-9, and the special characters '#', '@', or '$'. It may not be "guests", "admins", "users", "local", "idax", "public", "ibm", "dsweb", "sqlj", "root" or "gopher". It cannot begin with "bluadmin", "db2inst1", "nullid", "ibm", "sql" or "sys".
The user's password. It must have a minimum of twelve characters. It must contain at least one each of upper-case letters, lower-case letters, numbers, and special characters.
If set to 'yes', it indicates the user account is locked, which disallows the user from logging into the system
Possible values: [
yes
,no
]Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
User response
Cannot edit this user
The user does not exist
Error payload
No Sample Response
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
Response
The user's full name
The user's role. Administrators have the
bluadmin
role. Regular users have thebluuser
role.Possible values: [
bluuser
,bluadmin
]The user's email address
The user's ID. It must be between 1 and 12 characters and may only contain letters a-z (lower-case), numbers 0-9, and the special characters '#', '@', or '$'. It may not be "guests", "admins", "users", "local", "idax", "public", "ibm", "dsweb", "sqlj", "root" or "gopher". It cannot begin with "bluadmin", "db2inst1", "nullid", "ibm", "sql" or "sys".
The user's password. It must have a minimum of twelve characters. It must contain at least one each of upper-case letters, lower-case letters, numbers, and special characters.
If set to 'yes', it indicates the user account is locked, which disallows the user from logging into the system
Possible values: [
yes
,no
]Meta information of an resource. Metadata is ready-only and values are auto-generated by the system.
Status Code
User response
Cannot edit this user
The user does not exist
Error payload
No Sample Response
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
Response
The user's ID
If 'yes', database connections will only be allowed from the list of authorized hosts. If 'no', database connections will be allowed from anywhere.
A list of IP addresses or hostnames in which the user can connect from.
Status Code
User's connection privileges
Access to this user's connection privileges is not allowed
The user does not exist
Error payload
No Sample Response
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 of the user who's information is to be updated
Connection privilege information
The user's ID
If 'yes', database connections will only be allowed from the list of authorized hosts. If 'no', database connections will be allowed from anywhere.
A list of IP addresses or hostnames in which the user can connect from.
Response
The user's ID
If 'yes', database connections will only be allowed from the list of authorized hosts. If 'no', database connections will be allowed from anywhere.
A list of IP addresses or hostnames in which the user can connect from.
Status Code
Updated user connection privileges
Access to this user's connection privileges is not allowed
The user does not exist
Error payload