Introduction Use the Db2 Warehouse as a Service API to access data, view and create database objects, administer, and monitor your Db2 Warehouse as a Service service. Root URL The context root for the Db2 Warehouse as a Service API is /dbapi/v4/. 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 accesstoken. An access token can be obtained with the /auth/tokens endpoint, and it is used by Db2 Warehouse as a Service to identify who you are. Some api methods for example: /schemas and /sqljobs also support direct access to the database using HTTP headers. For example: X-DB-Userid:databaseuser, X-DB-Password:databasepassword, X-DB-URL:jdbc:db2://host:port/database:property for Db2 data sources add dataservertype DB2LUW, DB2Z in property, if dataservertype key for Db2 data source is not specified, the default is DB2LUW. Db2 Warehouse as a Service 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.
| Method | Path | Summary |
|---|---|---|
| POST | /auth/tokens | Request a new access token |
| GET | /auth/token/publickey | Returns public key of Json Web Token |
| DELETE | /auth/token/logout | Perform logout operation for a token |
| POST | /auth_policies | Creates a new authentication policy **ADMIN ONLY** |
| GET | /auth_policies | Lists all authentication policies **ADMIN ONLY** |
| GET | /auth_policies/{id} | Returns an authentication policy **ADMIN ONLY** |
| PUT | /auth_policies/{id} | Updates an authentication policy **ADMIN ONLY** |
| DELETE | /auth_policies/{id} | Deletes an existing policy **ADMIN ONLY** |
| PUT | /auth/password | Sets a new password using dswebToken |
| POST | /auth/reset | Requests a password reset based on user's email |
| GET | /backups | Get a list of backups |
| POST | /backups/restore | Restore |
| POST | /backups/backup | Create a backup |
| DELETE | /backups/{id} | Delete a backup |
| GET | /backups/setting | Get backup setting |
| POST | /backups/setting | Update backup setting |
| POST | /load_jobs_ET | Creates a data load job, load uses external table technology |
| GET | /load_jobs | Lists all data load jobs, load uses Db2 load utility technology |
| POST | /load_jobs | Creates a data load job |
| GET | /load_jobs/{id} | Returns details about a load job including its progress |
| DELETE | /load_jobs/{id} | Removes load job from history |
| GET | /load_jobs/{id}/log | Dowloads log file for a data load job |
| GET | /schemas | Lists all schemas in the database |
| GET | /schemas/{schema_name} | Returns schema information |
| GET | /schemas/{schema_name}/tables | Lists tables in a schema |
| GET | /schemas/{schema_name}/tables/{table_name} | Returns information about a table |
| DELETE | /schemas/{schema_name}/tables/{table_name}/data | Deletes all table data |
| GET | /admin/schemas/obj_type/{obj_type} | Query the list of object schemas |
| POST | /admin/schemas | Create schema |
| DELETE | /admin/schemas | Drops an empty schema |
| GET | /admin/privileges | Get single object privilege |
| POST | /admin/privileges | Privilege list of selected objects |
| PUT | /admin/privileges | Grant or revoke privileges |
| POST | /admin/schemas/ddl | Generate schema DDL |
| PUT | /admin/tables | Create a new table |
| DELETE | /admin/tables | Drop table |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/definition | Get table definition |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/data | Query table data |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/columns/{column_name}/properties | Get column detail properties |
| GET | /admin/tables/meta/datatype | Get the metadata type |
| POST | /admin/tables/ddl | Generate table DDL |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/distribution_keys | Get distributin keys |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/distributions/properties | Get data distributin property |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/partitions/expressions | Get partition expressions |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/partitions | Get data partitions |
| GET | /admin/schemas/{schema_name}/views/{view_name}/properties | Query view detail properties |
| DELETE | /admin/views | Drop views |
| GET | /admin/schemas/{schema_name}/views/{view_name}/data | Query view data |
| GET | /admin/schemas/{schema_name}/views/{view_name}/definition | Get View definition |
| POST | /admin/views/ddl | Generate view DDL |
| GET | /admin/schemas/{schema_name}/{obj_type}/{object_name}/dependencies | Get the dependencies of the object |
| GET | /admin/tablespaces/{tablespace}/dependencies | Get the dependencies of the tablespace |
| GET | /admin/bufferpools/{bufferpool_name}/properties | Query bufferpool detail properties |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/constraints/{constraint_name}/properties | Query constraint detail properties |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/indexes/{index_name}/properties | Query index detail properties |
| GET | /admin/schemas/{schema_name}/triggers/{trigger_name}/properties | Query trigger detail properties |
| GET | /admin/schemas/{schema_name}/mqts/{mqt_name}/properties | Query MQT detail properties |
| GET | /admin/schemas/{schema_name}/functions/{specific_name}/properties | Query UDF detail properties |
| GET | /admin/tablespaces/{table_space}/properties | Query tablespace detail properties |
| GET | /admin/schemas/{schema_name}/procedures/{specific_name}/properties | Query procedure detail properties |
| GET | /admin/schemas/{schema_name}/sequences/{sequence_name}/properties | Query sequence detail properties |
| GET | /admin/schemas/{schema_name}/packages/{package_name}/properties | Query package detail properties |
| GET | /admin/schemas/{schema_name}/udts/{type_name}/properties | Query UDT detail properties |
| GET | /admin/schemas/{schema_name}/aliases/{alias_name}/properties | Query alias detail properties |
| GET | /admin/schemas/{schema_name}/tables/{table_name}/properties | Get table detail properties |
| PUT | /admin/aliases | Create alias |
| DELETE | /admin/aliases | Drop alias |
| POST | /admin/aliases/ddl | Generate Alias DDL |
| GET | /admin/schemas/{schema_name}/aliases/{alias_name}/data | Query alias data |
| POST | /admin/mqts/ddl | Get the SQL statement template for creating MQT table |
| DELETE | /admin/mqts | Delete mqt tables |
| GET | /admin/schemas/{schema_name}/mqts/{mqt_name}/definition | Get the definition of MQT table |
| GET | /admin/schemas/{schema_name}/mqts/{mqt_name}/data | Get the data of specified mqt table |
| GET | /admin/privileges/authentications | Get authentications through the authid filter |
| GET | /admin/privileges/{authid_type}/{authid}/{obj_type} | Get the privieles of auth id |
| POST | /admin/privileges/dcl | Get SQL statement of grant or revoke privileges |
| POST | /admin/privileges/roles | Add new role |
| PUT | /admin/privileges/roles | Grant or revoke the privilges of role |
| DELETE | /admin/privileges/roles/{role_name} | Delete one role |
| GET | /admin/privileges/roles/{authid_type}/{authid} | Get the membership of authid |
| POST | /admin/privileges/roles/dcl | Get SQL statement of grant or revoke role |
| POST | /admin/privileges/multiple_objtypes/dcl | Get SQL statement for grant or revoke privileges |
| PUT | /admin/privileges/multiple_objtypes | Grant or revole privileges for multiple objects and objects type |
| POST | /admin/privileges/multiple_roles/dcl | Get the SQL statement for grant or revoke multiple roles |
| PUT | /admin/privileges/multiple_roles | Grant or revoke multiple roles |
| POST | /admin/workloads/ddl | Generate workload DDL |
| GET | /admin/workloads | Lists workloads |
| DELETE | /admin/sequences | Drop sequences |
| DELETE | /admin/procedures | Drop procedures |
| GET | /admin/schemas/{schema_name}/procedures/{specific_name}/parameters | Retrieve parameters for procedure |
| POST | /admin/procedures/ddl | Generate create procedure template |
| DELETE | /admin/functions | Drop functions |
| GET | /admin/schemas/{schema_name}/functions/{specific_name}/parameters | Retrieve parameters for specific udf |
| POST | /admin/functions/ddl | Generate create function template |
| DELETE | /admin/udts | Drop User-defined Types |
| GET | /admin/schemas/{schema_name}/udts/{udt_name}/definition | Retrieve row definition for User-defined Type object |
| POST | /admin/udts/ddl | Generate Create User-defined Types template |
| GET | /admin/tablespaces | Query the list of tablespace |
| DELETE | /admin/tablespaces | Drop tablespaces |
| GET | /admin/tablespaces/{tablespace}/iosetting | Get the I/O settings of the tablespace |
| GET | /admin/tablespaces/{tablespace}/size | Get the tablespace size |
| POST | /explain/explain_model | Generate visual explain model for a given sql |
| GET | /explain/explain_model/{sessionId}/diagram/nodes | Get visual explain nodes information |
| POST | /explain/explain_model/{sessionId}/diagram/layout | Generate visual explain layout |
| GET | /explain/explain_model/{sessionId}/diagram/nodes/{nodeId}/description | Get the data of the selected node |
| GET | /explain/explain_model/search/{sessionId}/{namePattern} | Search node based on the key word and predefined rule |
| POST | /explain/explain_model/search/{sessionId} | Search node based on the key word and predefined rule |
| GET | /home/{path} | Returns metadata of an item in the user's home storage |
| DELETE | /home/{path} | Deletes a file in the user's home storage |
| GET | /home_content/{path} | Download file from user's home storage |
| POST | /home_content/{path} | Uploads a file to the user's home storage |
| GET | /platform-keys/configurations | Retrieves current configuration |
| POST | /platform-keys/configurations | Migrate key |
| GET | /platform-keys/instances | Returns Key Protect instance information |
| GET | /platform-keys/instances/{instance_id}/region/{region_id}/keys | Returns keys for given Key Protect instance |
| GET | /monitor | Returns overall status of system components |
| GET | /monitor/connections | Lists active database connections **DB ADMIN ONLY** |
| DELETE | /monitor/connections/{application_handle} | Terminates a database connection **DB ADMIN ONLY** |
| GET | /monitor/storage | Returns current storage usage **DB ADMIN ONLY** |
| GET | /monitor/storage/history | Returns storage usage history **DB ADMIN ONLY** |
| GET | /monitor/storage/by_schema | Storage utilization by schema **DB ADMIN ONLY** |
| GET | /monitor/storage/by_schema/{schema_name} | Storage utilization of a schema **DB ADMIN ONLY** |
| GET | /metrics/statements_count | Return the number of statements for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/response_time_histograms | Return the histogram of response time for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/average_response_time | Return the average response time for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/response_time | Return the list of average response time for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/average_statements_rate | Return the average number of statements executed every minutes for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/statements_rate | Return numbers of statements executed every minutes for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/average_rows_read | Return the average number of rows read(rows/min) for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/rows_read | Return the numbers of rows read(rows/min) for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/max_connections_count | Return the maximum number of concurrent connections for a specified time frame **DB ADMIN ONLY** |
| GET | /metrics/connections_count | Return maximum numbers of concurrent connections for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/max_lockwaits_rate | Return the maximum lockwaits per minute for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/lockwaits_rate | Return the maximum lockwaits per minute for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/max_log_space | Return the maximum usage percentage of logs for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/log_space | Return the maximum usage percentages of logs for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/max_cpu | Return the maximum percent of node CPU usage for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/cpu_summary | Return the percent of node CPU usage for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/max_memory | Return the maximum percent of node memory usage for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/memory_summary | Return the percent of node memory usage for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/max_storage | Return the maximum usage percent of storage for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/storage | Return the usage percent of storage for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/timespent | Return the time spent in each operation for a specified time frame. **DB ADMIN ONLY** |
| GET | /metrics/schemas/{tabschema}/tables/{tabname}/storage/timeseries | Return a table storage usage in a time range **DB ADMIN ONLY** |
| GET | /metrics/tables/storage | Returns a list for tables storage **DB ADMIN ONLY** |
| GET | /metrics/schemas/{tabschema}/tables/{tabname}/storage | Returns a table storage **DB ADMIN ONLY** |
| GET | /metrics/storage/schemas | Returns a list for schemas storage **DB ADMIN ONLY** |
| GET | /metrics/storage/schemas/{tabschema}/timeseries | Return a schema used storage in a time range **DB ADMIN ONLY** |
| GET | /metrics/storage/schemas/{tabschema} | Returns a schema storage **DB ADMIN ONLY** |
| POST | /metrics/tables/storage/collector | Runs a tables storage collection **DB ADMIN ONLY** |
| GET | /metrics/statements/inflight_executions | Returns a list of inflight executions |
| GET | /metrics/statements/inflight_executions/current/list | Returns a list of current inflight executions |
| GET | /metrics/applications/{application_handle}/uow/{uow_id}/inflight_executions/{activity_id} | Returns a infight execution detail |
| DELETE | /metrics/applications/{application_handle}/uow/{uow_id}/inflight_executions/{activity_id} | Terminates a statement **DB ADMIN ONLY** |
| GET | /metrics/applications/connections | Returns a list of connections |
| GET | /metrics/applications/connections/current/list | Returns a list of current connections |
| GET | /metrics/applications/connections/{application_handle} | Returns a connection detail |
| DELETE | /metrics/applications/connections/{application_handle} | Terminates a database connection **DB ADMIN ONLY** |
| GET | /metrics/applications/connections/{application_handle}/timeseries | Returns a list of timeseries of a connection |
| GET | /metrics/schemas | Returns performance metrics for schemas **DB ADMIN ONLY** |
| GET | /metrics/schemas/current/list | Returns performance metrics for schemas **DB ADMIN ONLY** |
| GET | /metrics/schemas/{schema_name} | Returns performance metrics for one schema **DB ADMIN ONLY** |
| GET | /metrics/schemas/{schema_name}/members | Returns performance metrics on all members for one schema **DB ADMIN ONLY** |
| GET | /metrics/schemas/{schema_name}/timeseries | Returns performance metrics timeseries for one schema **DB ADMIN ONLY** |
| GET | /metrics/tables | Returns performance of tables **DB ADMIN ONLY** |
| GET | /metrics/tables/current/list | Returns current performance of tables **DB ADMIN ONLY** |
| GET | /metrics/schemas/{schema_name}/tables/{table_name} | Returns performance of one table **DB ADMIN ONLY** |
| GET | /metrics/schemas/{schema_name}/tables/{table_name}/members | Returns performance on all members for one table **DB ADMIN ONLY** |
| GET | /metrics/schemas/{schema_name}/tables/{table_name}/timeseries | Returns performance timeseries for one table **DB ADMIN ONLY** |
| GET | /metrics/tableperformance | Returns performance of tables and schemas **DB ADMIN ONLY** |
| GET | /metrics/tableperformance/current/list | Returns current performance of tables and schemas **DB ADMIN ONLY** |
| POST | /sql_jobs | Executes SQL statements |
| GET | /sql_jobs/{id} | Fetches partial results of a SQL job execution |
| POST | /sql_query_export | Returns the results of a SQL query to CSV |
| PUT | /manage/scaling/add | Create a scaling request |
| DELETE | /manage/scaling/cancel/{id} | Cancel a pending scaling request |
| GET | /manage/scaling/last_status | Get the last scaling request status |
| GET | /manage/scaling/list | Get the complete list of scaling requests |
| GET | /manage/scaling/resources | Get supported scaling ranges and current allocated resources |
| GET | /manage/scaling/history | View scaling history saved in console |
| GET | /settings | Returns the system's current settings |
| PUT | /settings | Updates the system settings **ADMIN ONLY** |
| GET | /tasks/status | Get tasks status |
| POST | /aspera | Generates a new Aspera token |
| DELETE | /aspera/{token} | Deletes an Aspera token |
| POST | /schema_discovery | Analyzes CSV data to return a list of data types of its values |
| GET | /users | Returns the list of users |
| POST | /users | Creates a new user. **ADMIN ONLY** |
| GET | /users/{id} | Get a specific user by ID |
| PUT | /users/{id} | Updates an existing user |
| DELETE | /users/{id} | Deletes an existing user **ADMIN ONLY** |
| PUT | /users/{id}/lock | Locks a user account indefinitely. **ADMIN ONLY** |
| PUT | /users/{id}/unlock | Unlocks a user account **ADMIN ONLY** |
| GET | /users/{id}/connection_privileges | Get a specific user's connection privileges |
| PUT | /users/{id}/connection_privileges | Updates an existing user's connection privileges **ADMIN ONLY** |