Introduction
Data Management Console for Cloud Pak for Data is a browser-based tool that helps you administer, monitor, manage and optimize the performance of IBM Db2 AESE, Db2 Warehouse, Db2 BIGSQL, Data Virtualization.
The Data Management Console REST API connects to your Db2 service, so you can administer, monitor, manage and optimize the performance of IBM Db2 databases.
Authentication
To authenticate to the API, you pass an access token in an Authorization header. The token is associated with a user name. To generate an access token, call the Get authorization token method.
Generating an access token. The response includes a token property.
Replace {cpd_cluster_host} and {port} with the details for the service instance. Replace {username} and {password} with your IBM Cloud Pak for Data credentials.
curl -k -X POST -d "{\"username\":\"{username}\",\"password\":\"{password}\"}" "https://{cpd_cluster_host}{:port}/icp4d-api/v1/authorize"
Service endpoint
The service endpoint is based on your IBM Cloud Pak deployment URL and Data Management Console instance name.
https://{cpd_cluster_host}{:port}/icp4data-addons/{dmc-instance}/zen/dbapi/v4/{method}
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-addons/DMC-13845673250/zen/dbapi/v4/{method}.
Example
curl -H "Authorization: Bearer {token}" -X {request_method} "https://{cpd_cluster_host}{:port}/icp4data-addons/{dmc-instance}/zen/dbapi/v4/{method}"
Methods
Get top consumer metrics.
Returns top consumer for 20 metrics, including their basic information, max value, avg value without max, and its rank.
GET /metrics/applications/top_consumer
Request
Custom Headers
Name of the database we want to get information from.
Query Parameters
field to define the working mode of the monitor, historical mode or real time mode.
Allowable values: [
real_time]A boolean parameter describing whether to query for system-level information. If this parameter is set to true, the return will contain information about the resource consumption of the database and the DMC system
The number value that specifies the maximum amount of data to be returned in a response.
Default:
100The field to the starting position of return records. Limit and offset should be set together.
Default:
0
curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "X-IBM-DMC-DB-Profile: {Database Profile Name}}" -H "authorization: Bearer {AUTH_TOKEN}" "https://{HOSTNAME}/dbapi/v4/metrics/applications/top_consumer?include_sys=SOME_STRING_VALUE&limit=100&offset=0&monitor_type=real_time"
Response
Collection of top consumer list.
The number of statements.
A list of top consumer models.
Status Code
Returns 20 metrics information for top consumers.
Current user does not have permission to access this database.
Database profile not found.
Error payload.
{ "count": 1, "resources": [ { "timestamp": 1629252136567, "metric_name": "Memory", "suffix": "mem", "application_handle": 54923, "uow_id": 1, "resource_value": 128, "avg_wo_max": 0, "resource_value_ui": "128 KB", "avg_wo_max_ui": "--", "application_name": "UC_MYMON", "session_auth_id": "DB2INST1", "workload_name": "CONSOLE_WORKLOAD", "application_handle_rank": "1" } ] }
Returns performance schemas
Returns performance of schemas. If there are multiple collections. They’ll been aggregated averagely.
GET /metrics/table_performance/schemas
Request
Custom Headers
Database profile name.
Query Parameters
The field to define the working mode of the monitor, historical mode or real time mode.
Allowable values: [
real_time,historical]The start time of the query. if in real time mode please give a value of current time.
The end time of the query. if in real time mode please give a value of current time.
The field to set if include system tables in the return list.
Default:
falseThe field to set the amount of return records.
Default:
100The field to set the starting position of return records.
Default:
0The field use to sort the returned schemas. The name of the field is in the response. To reverse the sort order, prefix the parameter value with a minus sign (-).
curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "X-IBM-DMC-DB-Profile: {Database Profile Name}}" -H "authorization: Bearer {AUTH_TOKEN}" "https://{HOSTNAME}/dbapi/v4/metrics/table_performance/schemas?include_sys=SOME_STRING_VALUE&limit=100&offset=0&monitor_type=real_time&start=0&end=0"
Response
a list of schemas returns from monitoring database.
The counts of statements.
Schema level performance information.
Status Code
Returns performance of tables.
Current user does not have permission to access this database.
Database profile not found.
Error payload
{ "count": 1, "resources": [ { "hit_ratio": 100, "accesses_min": 4056739, "table_schema": "IBMCONSOLE", "table_scans_per_min": 921, "rows_read_per_min": 4053050, "rows_inserted_per_min": 313, "rows_updated_per_min": 0, "rows_deleted_per_min": 3376, "logical_reads_per_min": 25321, "physical_reads_per_min": 0, "rows_read_versus_accessed_per_min": 99.90906489177638, "data_object_l_pages_per_min": 0, "index_object_l_pages_per_min": 0, "xda_object_l_pages_per_min": 0, "lob_object_l_pages_per_min": 0, "long_object_l_pages_per_min": 0 } ] }
Returns performance table
Returns performance of table. If there are multiple collections. They’ll been aggregated averagely.
GET /metrics/table_performance/tables
Request
Custom Headers
Database profile name.
Query Parameters
field to define the working mode of the monitor, historical mode or real time mode.
Allowable values: [
real_time,historical]The start time of the query. if in real time mode please give a value of current time.
The end time of the query. if in real time mode please give a value of current time.
A field to set if include system tables in the return list.
Default:
falseA field to set the amount of return records.
Default:
100A field to set the starting position of return records.
Default:
0Used to sort by the given field. The name of given field can be found from the response. Plus + or - before the given filed to denote by ASC or DESC. By default if not specified it's by ASC. Note: only support one field each time.
curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "X-IBM-DMC-DB-Profile: {Database Profile Name}}" -H "authorization: Bearer {AUTH_TOKEN}" "https://{HOSTNAME}/dbapi/v4/metrics/table_performance/tables?include_sys=SOME_STRING_VALUE&limit=100&offset=0&monitor_type=real_time&start=0&end=0"
Response
A list of tables.
a list of tables that meets the query criteria.
Table level performance information.
Status Code
Returns performance of tables
Current user does not have permission to access this database.
Database profile not found.
Error payload.
{ "count": 1, "resources": [ { "tab_type": "USER_TABLE", "tbsp_name": "TS4CONSOLE", "hit_ratio": 0, "accesses_min": 0, "table_schema": "IBMCONSOLE", "table_name": "throughputMember", "table_scans_per_min": 0, "rows_read_per_min": 0, "rows_inserted_per_min": 0, "rows_updated_per_min": 0, "rows_deleted_per_min": 0, "logical_reads_per_min": 0, "physical_reads_per_min": 0, "rows_read_versus_accessed_per_min": 0, "data_object_l_pages_per_min": 0, "index_object_l_pages_per_min": 0, "xda_object_l_pages_per_min": 0, "lob_object_l_pages_per_min": 0, "long_object_l_pages_per_min": 0 } ] }
Returns a list of event monitor activities
Returns a list of event monitor activities.
GET /metrics/statements/event_monitor_activity
Request
Custom Headers
The name of database profile.
Query Parameters
The field to define the working mode of the monitor, historical mode or real time mode.
Allowable values: [
real_time,historical]The start time of the query. if in real time mode please give a value of current time.
The end time of the query. if in real time mode please give a value of current time.
The column name to group by the given field. The name of given field can be found from the response. Note: only support one field each time.
Allowable values: [
sql_hash_id,session_auth_id,address,appl_name,workload_name,service_superclass_name,activity_type]The Boolean value to check average data needed or summary data needed. By default, false. Note: This option should be provided along with "grouping" option.
The Field to set if include system statements in the return list.
Default:
falseThe amount of return records. Limit and offest should be set together.
Default:
100The starting position of return records. Limit and offest should be set together.
Default:
0The filter condition of IP address.
The filter condition of statement text.
The filter condition of application_name.
The filter condition of session_auth_id.
Used to sort by the given field. The name of given field can be found from the response. Plus + or - before the given filed to denote by ASC or DESC. By default if not specified it's by ASC. Note: only support one field each time.
curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "X-IBM-DMC-DB-Profile: {Database Profile Name}}" -H "authorization: Bearer {AUTH_TOKEN}" "https://{HOSTNAME}/dbapi/v4/metrics/statements/event_monitor_activity?include_sys=false&start=1546272000000&end=1546272300000&limit=100&offset=0&address=SOME_STRING_VALUE&sql_text=SOME_STRING_VALUE&appl_name=SOME_STRING_VALUE&session_auth_id=SOME_STRING_VALUE&sort=-service_subclass_name&monitor_type=real_time"
Response
a list of event monitor activity.
counts of event monitor activities.
Event monitor activity.
Status Code
Returns event monitor activities with the specific profile name.
Current user does not have permission to access this database.
Database profile not found.
Error payload.
{ "count": 1, "resources": [ { "appl_id": "1", "uow_id": 0, "activity_id": 0, "sql_hash_id": 0, "address": "127.0.0.1", "appl_name": "throughputMember", "workload_name": "ablewly", "executable_id": "1", "service_superclass_name": "class", "service_subclass_name": "a", "partial_record": 0, "sql_code": 0, "sql_warn": "warn", "prep_time": 0, "time_started": 0, "time_completed": 0, "session_auth_id": 0, "stmt_exec_time": 0, "total_cpu_time": 0, "activity_type": "1", "wlm_queue_time_total": 0, "rows_read": 0, "rows_returned": 0, "total_act_wait_time": 0, "lock_wait_time": 0, "rows_read_per_returned": 0, "query_cost_estimate": 0, "direct_reads": 0, "direct_writes": 0, "coord_partition_num": 0, "member": 0, "fed_wait_time": 0, "fed_rows_deleted": 0, "fed_rows_inserted": 0, "fed_rows_updated": 0, "fed_rows_read": 0, "fed_waits_total": 0, "sql_text": "select * from ...", "agents_top": 0, "num_executions": 0 } ] }
Returns an event monitor activity detail
Returns an event monitor activity detail.
GET /metrics/statements/event_monitor_activity/{application_handle}/{uow_id}/{activity_id}Request
Custom Headers
The name of database profile.
Path Parameters
A system-wide unique ID for the application.
The unit of work identifier.
A system-wide unique ID for the activity.
Query Parameters
The start time of the query. if in real time mode please give a value of current time.
The end time of the query. if in real time mode please give a value of current time.
curl -k -X POST -H "cache-control: no-cache" -H "content-type: application/json" -H "X-IBM-DMC-DB-Profile: {Database Profile Name}}" -H "authorization: Bearer {AUTH_TOKEN}" "https://{HOSTNAME}/dbapi/v4/metrics/statements/evmon_activity/{application_handle}/uow/{uow_id}/activity/{activity_id}?start=1546272000000&end=1546272300000"
Response
event monitor activities details.
An input argument of type VARCHAR(64) that uniquely identifies the application that issued the activity whose section is to be explained.
An input argument of type INTEGER specifying the unit of work identifier for the activity whose section is to be explained. Unit of work ID is only unique within a given application.
An input argument of type INTEGER specifying the identifier of the activity whose section is to be explained. Activity ID is only unique within a unit of work.
The unique ID of the SQL contained in the current statement.
The time at which the activity described by this activity record began executing.
The time at which the activity described by this activity record end executing.
The coordinator partition of the unit of work or activity.
Time in milliseconds required to convert an SQL statement from text form to an executable form (if the activity is an SQL statement; otherwise, the value is 0).
The time spent waiting on a WLM queuing threshold. This value is given in milliseconds.
The type of the activity.
Estimated cost for a query, as determined by the SQL compiler.
The name of workload.
An opaque binary token generated on the data server that uniquely identifies the SQL statement section that was executed.
The name of a service superclass.
The name of a service subclass.
A unique routine identifier.
This element shows the isolation value in effect for the statement while it was being run.
Actual runtime degree of intra partition parallelism.
The name of the application running at the client, as known to the database or DB2 Connect server.
The current authorization ID for the session being used by this application.
The data passed to the target database for logging and diagnostic purposes, if the sqleseti API was issued in this connection. The current value of the CLIENT_ACCTNG special register for this activity.
The client user ID generated by a transaction manager and provided to the server, if the sqleseti API is used. The current value of the CLIENT_USERID special register for this activity.
Identifies the client's system or workstation (for example CICS® EITERMID), if the sqleseti API was issued in this connection. The current value of the CLIENT_WRKSTNNAME special register for this activity.
IP address from which the connection was initiated.
The unique id of SQL.
The numeric identifier for the database member from which the data was retrieved for this result record.
The string value of warning message about the SQL.
The total time spent executing this statement by all agents on this member. The value is given in milliseconds.
The total amount of CPU time used while within DB2. Represents total of both user and system CPU time. This value is given in microseconds.
The total number of times a routine was invoked.
The total number of sorts that have been executed.
The amount of time an agent spends waiting for log records to be flushed to disk. The value is given in milliseconds.
The total amount of data via the FCM communications layer. This value is reported in bytes..
The number of times a threshold was violated.
The number of rows read from the table.
The number of rows returned from the table.
The number of rows modified from the table.
Rows read per returned.
Percent of time waited on locks.
Percent of log disk wait time.
Percent of log buffer wait time.
Percent of sort wait time.
Percent of other wait time.
Percent of total section sort processing time.
Percent of total routine user code processing time.
Percent of other process time.
Percent of workload manager total queue time.
The number of logical reads.
The number of physical reads.
The number of write writes that use the buffer pool.
The number of read operations that do not use the buffer pool.
The number of write operations that do not use the buffer pool.
Buffer pool index logical reads.
Buffer pool XDA data logical reads.
Buffer pool data logical reads.
Buffer pool column logical reads.
Buffer pool temp logical reads.
Number of lock escalations.
Number of lock timeouts.
The total number of times that applications or connections waited for locks.
Time spent by a federation server.
Rows deleted by a federation system.
Rows inserted by a federation system.
Rows updated by a federation system.
Rows read by a federation system.
Total number of execution times for a federation server.
The unique ID for query statement.
The unique ID for query plan.
Query semantic compilation environment ID.
Query uses WLM admission control resource actuals.
The effective query degree of parallelism for this activity.
Sort share heap high watermark.
This monitor element records time spent waiting for the client to send its next request. The value is given in milliseconds.
The number of sorts that have requested heaps after thesort heap threshold has been exceeded.
The total number of hash joins that were throttled backby the sort memory throttling algorithm.
The number of columnar vector memory consumers that requested memory after the sort heap threshold was exceeded.
Total buffer pool physical read time.
Total buffer pool physical write time.
The Time waited for prefetch.
The number of temp reads
TP monitor client application name
Execution time for statement by coordinator agent.
The number of Agents Working on a Statement.
The number of Agents Created.
The text of the SQL statement.
Status Code
Returns event monitor activity details with the specific profile name and specific activity id.
Current user does not have permission to access this database.
Database profile not found.
Error payload.
{ "appl_id": "string", "uow_id": 0, "activity_id": 0, "sql_hash_id": 0, "time_started": 0, "time_completed": 0, "coord_partition_num": 0, "prep_time": 0, "wlm_queue_time_total": 0, "activity_type": "string", "query_cost_estimate": 0, "workload_name": "string", "executable_id": "string", "service_superclass_name": "string", "service_subclass_name": "string", "routine_id": 0, "stmt_isolation": 0, "query_actual_degree": 0, "appl_name": "string", "session_auth_id": "string", "tpmon_acc_str": "string", "tpmon_client_userid": "string", "tpmon_client_wkstn": "string", "address": "string", "sql_code": 0, "member": 0, "sql_warn": "string", "stmt_exec_time": 0, "total_cpu_time": 0, "total_routine_invocations": 0, "total_sorts": 0, "log_disk_waits_total": 0, "fcm_volume_kb": 0, "thresh_violations": 0, "rows_read": 0, "rows_returned": 0, "rows_modified": 0, "rows_read_per_returned": 0, "lock_wait_time_percent": 0, "log_disk_wait_time_percent": 0, "log_buffer_wait_time_percent": 0, "sort_wait_time_percent": 0, "other_wait_time_percent": 0, "total_section_sort_proc_time_percent": 0, "total_routine_user_code_proc_time_percent": 0, "other_process_time_percent": 0, "wlm_queue_time_total_percent": 0, "logical_reads": 0, "physical_reads": 0, "pool_writes": 0, "direct_reads": 0, "direct_writes": 0, "pool_index_l_reads": 0, "pool_xda_l_reads": 0, "pool_data_l_reads": 0, "pool_col_l_reads": 0, "pool_temp_l_reads": 0, "lock_escals": 0, "lock_timeouts": 0, "lock_waits": 0, "fed_wait_time": 0, "fed_rows_deleted": 0, "fed_rows_inserted": 0, "fed_rows_updated": 0, "fed_rows_read": 0, "fed_waits_total": 0, "stmt_id": "string", "plan_id": "string", "semantic_env_id": "string", "adm_resource_actuals": 0, "effective_query_degree": 0, "sort_shrheap_top": 0, "client_idle_wait_time": 0, "post_threshold_sorts": 0, "post_shrthreshold_hash_joins": 0, "post_threshold_col_vector_consumers": 0, "pool_read_time": 0, "pool_write_time": 0, "prefetch_wait_time": 0, "temp_reads": 0, "tpmon_client_app": "string", "coord_stmt_exec_time": 0, "num_agents": 0, "agents_top": 0, "sql_text": "string" }