IBM Support

How to improve performance of fetching data from JSON-based RESTful data sources?

How To


Summary

Two items can improve the performance well to fetch data from JSON-based RESTful data source via Autonomous REST Connector.
1. Update value of "InsensitiveResultSetBufferSize" property in connection string;
2. Filtering the fetching data in RESTful data source remotely

Objective

Improving performance of fetching data from JSON-based RESTful data sources

Environment

​​​​​​AIX; Linux

Steps

1.  Update value of "InsensitiveResultSetBufferSize" property in connection string
Autonomous REST Connector converts SQL statement to RESTful call for fetching data back from the data sources, and maps the data to tables or views in the driver.
The property "InsensitiveResultSetBufferSize" determines the amount of memory that is used by the driver to cache insensitive result set data. See more here.
The property value can be updated in the connection string to make it caching data as much as possible.
The example:
CREATE SERVER LOGMONITOR type autorest wrapper JDBC options (
DRIVER_PACKAGE '/home/inst1/sqllib/federation/jdbc/lib/FOautorest.jar', 
driver_class 'com.ibm.fluidquery.jdbc.autorest.AutoRESTDriver', 
url 'jdbc:ibm:autorest:Config=/opt/LogMonitor/cfg/connect.rest;createMap=forceNew;InsensitiveResultSetBufferSize=4096;')
2.  Filtering the fetching data in RESTful data source remotely
When the RESTful data source supports filter parameters in its RESTful call, the operators in SQL statement can be converted and passed to the RESTful call with proper configuration. It can reduce the fetching data in RESTful data source remotely.
Following example describes relationships between SQL statement, AutoREST Connector configuration file, and RESTful API.
A> The example of RESTful definition:
  RESTful API:
     https://xxx.edu/cs/examm/getStudents
  Parameters:
      lowScore:  The minimum score for searching students
      highScore: The maximum score for searching students

  It searches the dataset of students whose score is in the scope [lowScore, highScore]

  Example of RESTful call:
      https://xxx.edu/cs/examm/getStudents?lowScore=75&highScore=100
B> Define the AutoREST Connector configuration file(connect.rest in current example) for RESTful API in A>
Attention please:
  The comments(#1 and #2) below are used for explanation only; 
  Please remove all the comments in your configuration file.

{
  ......

  "tab1":{

    #1. define the fields of student object returned from RESTful call
    "id":integer,
    "name":varchar(100)",
    "score":smallint,
    ......

    #2. define the mapping between operator in SQL and filter parameter in RESTful API
    "low": {
     "#type":"integer",
     "#default":60,
     "#ge":"lowSCore"
    },
    "high": {
     "#type":"integer",
     "#default":80,
     "#le":"highSCore"
    }
  }
}

In this example, a virtual column 'low' is defined in the AutoREST Connector configuration file, and when there is a 'greater than or equal to' ('ge') operator is calculated against this column in the SQL issued towards the RESTful data source at Federation side, the Autonomous REST Connector will covert the 'greater than or equal to' operator to the RESTful API 'getStudent' with the defined 'lowSCore' parameter and the value for this operator.

C>. Create server, user mapping, nickname, and run SQL that is converted to be RESTful call
CREATE SERVER EXAMMONITOR type autorest wrapper JDBC options (
DRIVER_PACKAGE '/home/inst1/sqllib/federation/jdbc/lib/FOautorest.jar', 
driver_class 'com.ibm.fluidquery.jdbc.autorest.AutoRESTDriver', 
url 'jdbc:ibm:autorest:Config=/opt/ExamMonitor/connect.rest;createMap=forceNew;InsensitiveResultSetBufferSize=4096');

 -- You can ignore the "create user" step below if the RESTful call doesn't need authentication.
Create user mapping for user server EXAMMONITOR options ( REMOTE_AUTHID 'user', REMOTE_PASSWORD 'password'); 

Create nickname nck1 for  EXAMMONITOR.tab1;

Run SQL:
    select * from nck1 where low >= 70 and high <= 90
  The SQL will be converted to be:
    https://xxx.edu/cs/examm/getStudents?lowScore=70&highScore=90
The supported operators are:
Operator in SQL statement  
Syntax in AutoREST Connector configuration file
Result in RESTful call
      = "#eq":"a" 
"x=y" in WHERE clause of SQL statement is rendered "a=y"
 It issues a RESTful call with parameter "a=y" to filter the fetching result
!= or <> "#ne":"a" 
"x!=y" in WHERE clause of SQL statement is rendered "a=y"
It issues a RESTful call "a=y" with parameter to filter the fetching result
< "#lt":"a" 
"x<y" in WHERE clause of SQL statement is rendered "a=y"
It issues a RESTful call "a=y" with parameter to filter the fetching result
<= "#le":"a" 
"x<=y" in WHERE clause of SQL statement is rendered "a=y"
It issues a RESTful call with parameter a=y to filter the fetching result
>  "#gt":"a" 
"x>y" in WHERE clause of SQL statement is rendered "a=y"
It issues a RESTful call with parameter "a=y" to filter the fetching result
>= "#ge":"a" 
"x>=y" in WHERE clause of SQL statement is rendered "a=y"
It issues a RESTful call with parameter "a=y" to filter the fetching result
in "#in":"a" 
"x in (1,2,3)" in the WHERE clause of SQL statement is rendered "a=1,2,3"
It issues a RESTful call with parameter "a=1,2,3" to filter the fetching result

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"11.5.0","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 July 2020

UID

ibm16212112

Page Feedback