/db2whrest/v1/sql_query_async: POST

Gets data from a database asynchronously with an SQL query in a POST command. You can use any standard SQL query. You must include the SQL query in the message body of the POST command.

To send the query as a parameter of the URL, see /db2whrest/v1/sql_query_async?<sql>: GET.

The following table shows which roles can access this REST API endpoint:
Table 1. Access by role
Data admin Data user Collection Admin Admin Service user
✓ ✓1 ✓1 Χ Χ
1The search is restricted to documents that are tagged with collections to which the user ID has a datauser role assigned.

The asynchronous endpoints are useful alternatives in situations where the operation takes a long time to complete or has a negative effect on the overall performance of the system. For more information, see the topic Asynchronous endpoints.

Synopsis of the request URL

curl -k -H 'Authorization: Bearer <token> https://<spectrum_discover_host>/db2whrest/v1/sql_query_async -X POST -d@<sql.dat>

Supported request types and response formats

Supported request types:
  • POST
Supported response formats:
  • CSV
  • JSON

Examples

  1. The following example shows how to get data from a database asynchronously with an SQL query in a POST command. The SQL query is sent in the POST message body. The output format is JSON.
    1. Write the record to be added to the database into a file named sql.dat.
    2. Submit the request:
      curl -k -H 'Authorization: Bearer <token>' https://<spectrum_discover_host>/db2whrest/v1/sql_query_async -X POST -d"select * from metaocean where owner='root'" -H "accept: application/json"
      The synchronous response to the request contains information about the asynchronously running task. The following is an example. For more information, see the topic /db2whrest/v1/sql_query_async?<sql>: GET.
      * HTTP 1.0, assume close after body
< HTTP/1.0 202 ACCEPTED
< Content-Type: text/html; charset=utf-8
< Content-Length: 28
< Location: https://<spectrum_discover_host>/db2whrest/v1/task_status/3e40cfeb-efc9-4091-8bc8-
b194ff49d728
< Server: Werkzeug/0.14.1 Python/3.4.5
< Date: Thu, 15 Feb 2018 20:37:10 GMT
    3. Later, poll the URL that is provided in the Location field of the synchronous response header in Step (b):
      curl -k -H 'Authorization: Bearer <token>' https://<spectrum_discover_host>/db2whrest/v1/task_status/
25b2df40-8f0c-4e32-a2f9-999ff3b18ada
    4. When the operation is complete, the endpoint displays the rows of data that are returned in response to the SQL query in the request. In the following example only the first two rows of output are shown. The output format is JSON:
      [{"filesystem":"reflib","operation":"INDEX","source":"SCAN","revision":"MO1","site":"",
"platform":"Spectrum Scale","cluster":"reflib.university.edu","inode":3067112,"owner":
"root","group":"root","permissions":"-r--r--r--","fileset":"root","uid":9,"gid":10,
"path":"\/reflib\/blast_db\/","filename":"other_genomic.75.tar.gz","filetype":"gz",
"migstatus":"resdnt","migloc":"NA","mtime":"2014-12-31T06:00:00.000Z","atime":
"2016-09-26T16:17:14.000Z","ctime":"2016-09-22T17:46:21.000Z","inserttime":
"2018-07-24T16:32:59.000Z","tier":"system","size":929541048,"qpart":0,"fkey":
"reflib.university.edureflib3067112","project":null,"department":null,"backup":
null,"tag4":null,"tag5":null,"tag6":null,"tag7":null,"tag8":null,"tag9":null,"tag10":
null,"tag11":null,"tag12":null,"tag13":null,"tag14":null,"tag15":null,"tag16":null},

{"filesystem":"reflib","operation":"INDEX","source":"SCAN","revision":"MO1","site":"",
"platform":"Spectrum Scale","cluster":"reflib.university.edu","inode":3067092,"owner":
"root","group":"root","permissions":"-r--r--r--","fileset":"root","uid":9,"gid":10,
"path":"\/reflib\/blast_db\/","filename":"other_genomic.65.tar.gz","filetype":"gz",
"migstatus":"resdnt","migloc":"NA","mtime":"2014-12-31T06:00:00.000Z","atime":
"2016-09-26T16:17:07.000Z","ctime":"2016-09-22T17:24:09.000Z","inserttime":
"2018-07-24T16:32:59.000Z","tier":"system","size":933683784,"qpart":0,"fkey":
"reflib.university.edureflib3067092","project":null,"department":null,"backup":
null,"tag4":null,"tag5":null,"tag6":null,"tag7":null,"tag8":null,"tag9":null,"tag10":
null,"tag11":null,"tag12":null,"tag13":null,"tag14":null,"tag15":null,"tag16":null},
...
]