Querying the SQL List

Function

This API is used to query the real-time SQL list.

Calling Method

For details, see Calling APIs.

URI

POST /v2/{project_id}/clusters/{cluster_id}/dms/queries

**Table 1** Path Parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition Project ID. To obtain the value, see Obtaining a Project ID. Constraints N/A Range N/A Default Value N/A
cluster_id	Yes	String	Definition Cluster ID. For details about how to obtain the value, see Obtaining the Cluster ID. Constraints The value must be a valid DWS cluster ID. Range It is a 36-digit UUID. Default Value N/A

Request Parameters

**Table 2** Request body parameters
Parameter	Mandatory	Type	Description
cluster_id	Yes	String	Definition Cluster ID. For details about how to obtain the value, see Obtaining the Cluster ID. Constraints The value must be a valid DWS cluster ID. Range It is a 36-digit UUID. Default Value N/A
project_id	Yes	String	Definition Project ID. To obtain the value, see Obtaining a Project ID. Constraints N/A Range N/A Default Value N/A
offset	Yes	String	Definition Offset. The query starts from the offset. Generally, the value is the number of pages minus 1. Range Greater than or equal to 0
limit	Yes	String	Definition Number of records displayed on each page. Range Greater than 0
conditions	Yes	Array of ListQueriesCondition objects	Definition Search criteria. Range N/A
order_by	No	String	Definition Sorting field. Range N/A
target	Yes	String	Definition Its fixed value is db_queries. Range N/A

**Table 3** ListQueriesCondition
Parameter	Mandatory	Type	Description
field	Yes	String	Definition Field name. Range systemQuery: whether to hide system queries userName: username applicationName: application name dbname: database name resourcePool: resource pool queryStatus: query status enqueue: queue status lane: lane instName: instance name pid: process ID blockTime: blocking time duration: duration minCpuTime: minimum CPU time maxCpuTime: maximum CPU time totalCpuTime: total CPU time cpuSkewPercent: CPU skew rate spillInfo: spill information minSpillSize: minimum spill size maxSpillSize: maximum spill size averageSpillSize: average spill size spillSkewPercent: spill skew rate queryBand: query label jobName: job name jobInst: job instance clientHostname: host name of the client clientPort: client port waiting: waiting state estimateTotalTime: estimated total time estimateLeftTime: estimated remaining time controlGroup: control group minPeakMemory: minimum peak memory maxPeakMemory: maximum peak memory averagePeakMemory: average peak memory memorySkewPercent: memory skew rate estimateMemory: estimated memory minDnTime: minimum DN time maxDnTime: maximum DN time averageDnTime: average DN time dntimeSkewPercent: DN time skew rate warning: warning averagePeakIops: average peak IOPS iopsSkewPercent: IOPS skew rate wlmStatus: WLM status wlmAttrib: WLM attribute
value	Yes	String	Definition Field value. Range N/A
operator	Yes	String	Definition Comparison mode. Range String parameters: =, !=, like, not like, and <> Integer type: =, !=, >, <, >=, <=, and <> Boolean type: = and !=

Response Parameters

Status code: 200

**Table 4** Response body parameters
Parameter	Type	Description
code	Integer	Definition Response code. Range N/A
msg	String	Definition Response information. Range N/A
data	ListQueriesData object	Definition Response data. Range N/A
count	Integer	Definition Total number of records. Range N/A

**Table 5** ListQueriesData
Parameter	Type	Description
queries	Array of ListQueriesDto objects	Definition Queried data list. Range N/A
status	ListQueriesStatus object	Definition Statistics. Range N/A

**Table 6** ListQueriesDto
Parameter	Type	Description
virtual_cluster_id	Integer	Definition Virtual cluster ID. Range N/A
ctime	Long	Definition Collection time. Range N/A
pid	String	Definition Session ID. Range N/A
inst_name	String	Definition Instance name. Range N/A
waiting	Boolean	Definition Whether the backend is currently waiting on a lock. If yes, the value is true. Range N/A
enqueue	String	Definition Resource status. Range N/A
warning	String	Definition Warnings and warnings related to SQL self-diagnosis tuning. Range N/A
query	String	Definition Query statement. Range N/A
lane	String	Definition Fast or slow lane. Range fast: fast lane slow: slow lane.
db_name	String	Definition Database name. Range N/A
priority	String	Definition Priority of a task in the resource pool. Range 1: highest 2: high 4: medium 8: low
query_id	String	Definition Internal query ID used for statement execution. Range N/A
query_band	String	Definition Job type, which can be set using the GUC parameter query_band. The default value is an empty string. Range N/A
job_name	String	Definition The value is obtained from the query_band field. The position is 0. Range N/A
job_inst	String	Definition The value is obtained from the query_band field. The position is 1. Range N/A
user_name	String	Definition Username used for connecting to the backend. Range N/A
application_name	String	Definition Name of the application that is connected to the backend. Range N/A
client_address	String	Definition IP address of the client connected to the backend. Range N/A
client_hostname	String	Definition Host name of the client. Range N/A
client_port	String	Definition TCP port number used by a client to communicate with the backend. Range N/A
start_time	Long	Definition Time when the statement starts to be executed. Range N/A
block_time	Long	Definition Block time before the statement is executed, in ms Range N/A
duration	Long	Definition Duration that a statement has been executed, in ms. Range N/A
estimate_total_time	Long	Definition Estimated execution time of a statement, in ms. Range N/A
estimate_left_time	Long	Definition Estimated remaining time of statement execution, in ms. Range N/A
resource_pool	String	Definition Resource pool used by the user. Range N/A
control_group	String	Definition Cgroup used by the statement. Range N/A
min_peak_memory	Integer	Definition Minimum memory peak of a statement across all DNs, in MB. Range N/A
max_peak_memory	Integer	Definition Maximum memory peak of a statement across all DNs, in MB. Range N/A
average_peak_memory	Integer	Definition Average memory usage during statement execution, in MB. Range N/A
memory_skew_percent	Integer	Definition Memory usage skew of a statement among DNs. Range N/A
estimate_memory	Integer	Definition Estimated memory used by a statement, in MB. Range N/A
spill_info	String	Definition Statement spill information on all DNs. Range N/A
min_spill_size	Integer	Definition Minimum spilled data among all DNs when a spill occurs, in MB. The default value is 0. Range N/A
max_spill_size	Integer	Definition Maximum spilled data among all DNs when a spill occurs, in MB. The default value is 0. Range N/A
average_spill_size	Integer	Definition Average spilled data among all DNs when a spill occurs, in MB. The default value is 0. Range N/A
spill_skew_percent	Integer	Definition DN spill skew when a spill occurs. Range N/A
min_dn_time	Long	Definition Minimum execution time of a statement across all DNs, in ms. Range N/A
max_dn_time	Long	Definition Maximum execution time of a statement across all DNs, in ms. Range N/A
average_dn_time	Long	Definition Average execution time of a statement across all DNs, in ms. Range N/A
dntime_skew_percent	Integer	Definition Execution time skew of a statement among DNs. Range N/A
min_cpu_time	Long	Definition Minimum CPU time of a statement across all DNs, in ms. Range N/A
max_cpu_time	Long	Definition Maximum CPU time of a statement across all DNs, in ms. Range N/A
total_cpu_time	Long	Definition Total CPU time of a statement across all DNs, in ms. Range N/A
cpu_skew_percent	Integer	Definition CPU time skew of a statement among DNs. Range N/A
average_peak_iops	Integer	Definition Average IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. Range N/A
iops_skew_percent	Integer	Definition I/O skew of a statement among DNs. Range N/A
max_peak_iops	Integer	Definition Maximum IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. Range N/A
min_peak_iops	Integer	Definition Minimum IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. Range N/A
query_plan	String	Definition Query plan. Range N/A
query_status	String	Definition: Real-time running status of the current query statement. Range: active: A query is being executed in the background. idle: The background is waiting for a new client command. idle in transaction: The background is in a transaction, but the query cannot be performed currently. idle in transaction (aborted): This state is similar to idle in transaction except that a statement in a transaction causes an error. fastpath function call: A fast-path function is being executed in the background. disabled: This state is reported if track_activities is disabled in the background.
wlm_status	String	Definition Status of the current query statement in the resource pool. Range pending running finished aborted active unknown
wlm_attrib	String	Definition Statement attribute. Range ordinary simple complicated internal
system_query	Boolean	Definition Whether the query is a system query. Range N/A
backend_start	Long	Definition Time when this process was started, that is, when the client connected to the server. Range N/A
elapsed_time	Long	Definition Execution time so far. Range N/A
curr_xact_start	Long	Definition Time when the current transaction was started (NULL if no transactions are active) If the current query is the first of its transaction, this column is equal to the query_start column. Range N/A
state_change	Long	Definition Time when the last status was changed. Range N/A
query_start	Long	Definition Time when the statement starts to be executed. Range N/A
query_elapsed_time	Long	Definition Actual execution duration of the statement, in seconds. Range N/A

**Table 7** ListQueriesStatus
Parameter	Type	Description
average_query_waiting_time	Double	Definition Average query wait time. Range N/A
average_time_consumption_of_queries	Double	Definition Average query duration. Range N/A
average_time_consumption_of_sessions	Double	Definition Average session duration. Range N/A
queries_count	Long	Definition Number of queries. Range N/A
session_count	Long	Definition Number of sessions. Range N/A

Example Requests

POST https://{Endpoint}/v2/{project_id}/clusters/{cluster_id}/dms/queries

{
  "offset" : "0",
  "limit" : "1",
  "cluster_id" : "6aa82ba1-fc97-4838-9b61-3e8da13e2fe9",
  "project_id" : "g6aa82ba1fc9748389b613e8da13e2fe9",
  "conditions" : [ {
    "field" : "userName",
    "value" : "dbadmin",
    "operator" : "="
  }, {
    "field" : "applicationName",
    "value" : "app",
    "operator" : "<>"
  }, {
    "field" : "dbName",
    "value" : "dbadmin",
    "operator" : "="
  }, {
    "field" : "resourcePool",
    "value" : "pool",
    "operator" : "<>"
  }, {
    "field" : "queryStatus",
    "value" : "",
    "operator" : "="
  }, {
    "field" : "enqueue",
    "value" : "",
    "operator" : "<>"
  }, {
    "field" : "lane",
    "value" : "",
    "operator" : "="
  }, {
    "field" : "instName",
    "value" : "cn-1-1",
    "operator" : "<>"
  }, {
    "field" : "pid",
    "value" : "33",
    "operator" : "="
  }, {
    "field" : "blockTime",
    "value" : "1",
    "operator" : "="
  }, {
    "field" : "duration",
    "value" : "2",
    "operator" : "<>"
  }, {
    "field" : "minCpuTime",
    "value" : "3",
    "operator" : ">"
  }, {
    "field" : "maxCpuTime",
    "value" : "4",
    "operator" : "<"
  }, {
    "field" : "totalCpuTime",
    "value" : "5",
    "operator" : ">="
  }, {
    "field" : "cpuSkewPercent",
    "value" : "6",
    "operator" : "<="
  }, {
    "field" : "spillInfo",
    "value" : "DN spill information",
    "operator" : "="
  }, {
    "field" : "minSpillSize",
    "value" : "7",
    "operator" : "<>"
  }, {
    "field" : "maxSpillSize",
    "value" : "8",
    "operator" : ">"
  }, {
    "field" : "averageSpillSize",
    "value" : "9",
    "operator" : "<"
  }, {
    "field" : "spillSkewPercent",
    "value" : "10",
    "operator" : ">="
  }, {
    "field" : "queryBand",
    "value" : "Job type",
    "operator" : "<>"
  }, {
    "field" : "jobName",
    "value" : "Task name",
    "operator" : "="
  }, {
    "field" : "jobInst",
    "value" : "Task instance",
    "operator" : "<>"
  }, {
    "field" : "clientHostname",
    "value" : "Server name",
    "operator" : "="
  }, {
    "field" : "clientPort",
    "value" : "TCP port",
    "operator" : "<>"
  }, {
    "field" : "waiting",
    "value" : "Waiting or not",
    "operator" : "="
  }, {
    "field" : "estimateTotalTime",
    "value" : "11",
    "operator" : "="
  }, {
    "field" : "estimateLeftTime",
    "value" : "12",
    "operator" : "<>"
  }, {
    "field" : "controlGroup",
    "value" : "cgroup",
    "operator" : "like"
  }, {
    "field" : "minPeakMemory",
    "value" : "13",
    "operator" : "="
  }, {
    "field" : "maxPeakMemory",
    "value" : "14",
    "operator" : "<>"
  }, {
    "field" : "averagePeakMemory",
    "value" : "15",
    "operator" : ">"
  }, {
    "field" : "memorySkewPercent",
    "value" : "16",
    "operator" : "<"
  }, {
    "field" : "estimateMemory",
    "value" : "17",
    "operator" : ">="
  }, {
    "field" : "minDnTime",
    "value" : "18",
    "operator" : "<="
  }, {
    "field" : "maxDnTime",
    "value" : "19",
    "operator" : "="
  }, {
    "field" : "averageDnTime",
    "value" : "20",
    "operator" : "<>"
  }, {
    "field" : "dntimeSkewPercent",
    "value" : "21",
    "operator" : ">"
  }, {
    "field" : "warning",
    "value" : "Alarm",
    "operator" : "="
  }, {
    "field" : "averagePeakIops",
    "value" : "22",
    "operator" : "<>"
  }, {
    "field" : "iopsSkewPercent",
    "value" : "23",
    "operator" : ">"
  }, {
    "field" : "wlmStatus",
    "value" : "Statement status",
    "operator" : "="
  }, {
    "field" : "wlmAttrib",
    "value" : "Statement attribute",
    "operator" : "not like"
  } ],
  "order_by" : "duration asc",
  "target" : "db_queries"
}

Example Responses

Status code: 200

{
  "code" : 0,
  "msg" : "OK",
  "count" : 1,
  "data" : {
    "queries" : [ {
      "ctime" : 1699062726000,
      "pid" : "140535026615872",
      "waiting" : false,
      "duration" : 0,
      "enqueue" : "",
      "warning" : "",
      "query" : "WLM fetch collect info from data nodes",
      "lane" : "fast",
      "priority" : null,
      "virtual_cluster_id" : 0,
      "inst_name" : "cn_5002",
      "db_name" : "postgres",
      "query_id" : "145522562959854219",
      "query_band" : "",
      "job_name" : "",
      "job_inst" : "",
      "user_name" : "Ruby",
      "application_name" : "workload",
      "client_address" : "",
      "client_hostname" : "",
      "client_port" : "",
      "start_time" : 0,
      "block_time" : 0,
      "estimate_total_time" : 0,
      "estimate_left_time" : 0,
      "resource_pool" : "default_pool",
      "control_group" : "",
      "min_peak_memory" : 0,
      "max_peak_memory" : 0,
      "average_peak_memory" : 0,
      "memory_skew_percent" : 0,
      "estimate_memory" : 0,
      "spill_info" : "",
      "min_spill_size" : 0,
      "max_spill_size" : 0,
      "average_spill_size" : 0,
      "spill_skew_percent" : 0,
      "min_dn_time" : 0,
      "max_dn_time" : 0,
      "average_dn_time" : 0,
      "dntime_skew_percent" : 0,
      "min_cpu_time" : 0,
      "max_cpu_time" : 0,
      "total_cpu_time" : 0,
      "cpu_skew_percent" : 0,
      "average_peak_iops" : 0,
      "iops_skew_percent" : 0,
      "max_peak_iops" : 0,
      "min_peak_iops" : 0,
      "query_plan" : null,
      "query_status" : "active",
      "wlm_status" : "active",
      "wlm_attrib" : "ordinary",
      "system_query" : true,
      "backend_start" : 1698998138,
      "elapsed_time" : 64585,
      "curr_xact_start" : 1699062726,
      "state_change" : 1698998142,
      "query_start" : 1698998142,
      "query_elapsed_time" : -1
    } ],
    "status" : {
      "session_count" : 19,
      "average_time_consumption_of_sessions" : 51297.58,
      "queries_count" : 19,
      "average_time_consumption_of_queries" : 48799.8,
      "average_query_waiting_time" : 0
    }
  }
}

Status Codes

Status Code	Description
200	Query succeeded.
400	Request error.
401	Authentication failed.
403	You do not have required permissions.
404	No resources found.
500	Internal server error.
503	Service unavailable.

Parent Topic: Host Monitoring

Previous topic: Host Monitoring

Next topic: Querying Table Skew Rate or Dirty Page Rate

Querying the SQL List

Function

Calling Method

URI

Request Parameters

Response Parameters

Example Requests

Example Responses

Status Codes

What is your overall rating for this page?