/db2whrest/v1/sql_query: POST

Gets data from a database 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?<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.

Synopsis of the request URL

curl -k -H 'Authorization: Bearer <token> https://<spectrum_discover_host>/db2whrest/v1/
sql_query -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 the data from a database with an SQL query in a POST command. The SQL query is sent in the POST message body. The output format is plain text.
    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 -X POST -d"select * from datasrv04 where owner='root'"
    3. The following response is returned. The output is in plain text format:
      0,filesys1,INDEX,SCAN,MO1,,Spectrum Scale,filesys1.university.edu,3067343,root,root,
-rw-r--r--,root,9,10,/filesys1/cellranger-2.0.0/refdata-cellranger-ercc92-1.2.0/star/,
sjdbList.out.tab,tab,resdnt,NA,2016-11-14 19:40:10,2017-06-02 21:30:26,
2017-06-02 21:30:26,2018-07-24 16:32:47,system,0,1,filesys1.university.edufilesys13067343,
,,,,,,,,,,,,,,,
1,filesys1,INDEX,SCAN,MO1,,Spectrum Scale,filesys1.university.edu,3067333,root,root,
-rw-r--r--,root,9,10,/filesys1/cellranger-2.0.0/refdata-cellranger-ercc92-1.2.0/star/,
chrLength.txt,txt,resdnt,NA,2016-11-14 19:40:10,2017-06-02 23:28:21,2017-06-02 21:30:26,
2018-07-24 16:32:47,system,412,1,filesys1.university.edufilesys13067333,
,,,,,,,,,,,,,,,,
...
      In the following code block, the information from the response is curtailed so that it is easier to read. Only the first 14 columns are shown here. You can see that both rows seem to have root as the owner:
      0,filesys1,INDEX,SCAN,MO1,,Spectrum Scale,filesys1.university.edu,3067343,root,root,
-rw-r--r--,root,9,10,/filesys1/. . . .
1,filesys1,INDEX,SCAN,MO1,,Spectrum Scale,filesys1.university.edu,3067333,root,root,
-rw-r--r--,root,9,10,/filesys1/. . . .
...
  2. The following example shows how to get the data from a database 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 SQL record in a file named sql.dat.
    2. Submit the request. 
      curl -k -H 'Authorization: Bearer <token> https://<spectrum_discover_host>/db2whrest/v1/
sql_query -X POST -d@"sql.dat" -H "accept: application/json"
    3. The following response is returned. The output is in JSON format:
      [{"filesystem":"filesys1","operation":"INDEX","source":"SCAN","revision":"MO1","site":"",
"platform":"Spectrum Scale","cluster":"filesys1.university.edu","inode":3067112,"owner":
"root","group":"root","permissions":"-r--r--r--","fileset":"root","uid":9,"gid":10,"path":
"\/filesys1\/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":
"filesys1.university.edufilesys13067112","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":"filesys1","operation":"INDEX","source":"SCAN","revision":"MO1","site":"",
"platform":"Spectrum Scale","cluster":"filesys1.university.edu","inode":3067092,"owner":
"root","group":"root","permissions":"-r--r--r--","fileset":"root","uid":9,"gid":10,
"path":"\/filesys1\/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":
"filesys1.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},
...