Use asynchronous queries

Use asynchronous queries when a system or network performance is affected by querying a large group of data or long running queries.

1. Submit asynchronous queries to the target system and retrieve a Query ID Send a POST request to the /system/<SYSTEM_NAME>/queries endpoint. In the following example, the request includes:

   • SELECT * FROM DBC.DBCInfo: The query to the system with the alias <SYSTEM_NAME>.
   • 'format': 'OBJECT': The format for response.
   • 'spooled_result_set': True: The indication that the request is asynchronous.

   Request

   ## Run async query .
   
   url = 'https://<QS_HOSTNAME>:1443/systems/<SYSTEM_NAME>/queries'
   
   payload = {
     'query': 'SELECT * FROM DBC.DBCInfo;',
     'format': 'OBJECT',
     'spooled_result_set': True
   }
   
   payload_json = json.dumps(payload)
   response = requests.request('POST', url, headers=headers, data=payload_json, verify=False)
   
   print(response.text)
   

   Response

   {"id":1366025}
   

2. Get query details using the ID retrieved from Step 1

   Send a GET request to the /system/<SYSTEM_NAME>/queries/<queryID> endpoint, replacing <queryID> with the ID retrieved from Step 1.

   The request returns the details of the specific query, including queryState, queueOrder, queueDuration, and so on. For a complete list of the response fields and their descriptions, see Submitting SQL statements.

   Request

   ## response for async query .
   
   url = 'https://<QS_HOSTNAME>:1443/systems/<SYSTEM_NAME>/queries/1366025'
   
   payload_json = json.dumps(payload)
   response = requests.request('GET', url, headers=headers, verify=False)
   
   print(response.text)
   

   Response

   {
     "queryId":1366025,
     "query":"SELECT * FROM DBC.DBCInfo;",
     "batch":false,
     "system":"testsystem",
     "user":"dbc",
     "session":1366015,
     "queryState":"RESULT_SET_READY",
     "queueOrder":0,
     "queueDuration":6,
     "queryDuration":9,
     "statusCode":200,
     "resultSets":{
   
     },
     "counts":{
   
     },
     "exceptions":{
   
     },
     "outParams":{
   
     }
   }
   

3. View resultset for asynchronous query

   Send a GET request to the /system/<SYSTEM_NAME>/queries/<queryID>/results endpoint, replacing <queryID> with the ID retrieved from Step 1.

   The request returns an array of the result sets and update counts produced by the submitted query.

   Request

   url = 'https://<QS_HOSTNAME>:1443/systems/<SYSTEM_NAME>/queries/1366025/results'
   
   payload_json = json.dumps(payload)
   response = requests.request('GET', url, headers=headers, verify=False)
   
   print(response.text)
   

   Response

   {
     "queueDuration":6,
     "queryDuration":9,
     "results":[
       {
         "resultSet":true,
         "data":[
           {
             "InfoKey":"LANGUAGE SUPPORT MODE",
             "InfoData":"Standard"
           },
           {
             "InfoKey":"RELEASE",
             "InfoData":"15.10.07.02"
           },
           {
             "InfoKey":"VERSION",
             "InfoData":"15.10.07.02"
           }
         ],
         "rowCount":3,
         "rowLimitExceeded":false
       }
     ]
   }