IBM Db2 Event Store REST API (1.0.0)

You can use the REST protocols to incorporate IBM® Db2® Event Store features in your custom application. Use the IBM Db2 Event Store REST protocol to monitor your database. However, to add data to the tables in your database, you must use one of the methods to insert rows in a table in the OLTP API.

REST API

IBM Db2 Event Store runs on either the IBM Watson™ Studio Local or the IBM Cloud Pak for Data platform. This means that your use of the REST API will differ based on which platform you are currently running.

The structure of the REST URL looks like:
https://${REST_SERVER_ENDPOINT}:443/${SERVICE_IDENTIFIER}/${REQUESTED_CONTENT}

While the {REQUESTED_CONTENT} remains the same for IBM Watson Studio Local and IBM Cloud Pak for Data deployments, the REST_SERVER_ENDPOINT and SERVICE_IDENTIFIER are different.

For the following examples, the REQUESTED_CONTENT in the sample REST URLs is: 
oltp/keystore
IBM Watson Studio Local deployment
  • Finding the {REST_SERVER_ENDPOINT}
    • This is the Db2 Event Store host IP, this can be identified by checking the service IP of the kubernetes service name: ibm-nginx-svc
    • A sample command to check the service IP of the ibm-nginx-svc service:
      kubectl get svc ibm-nginx-svc -n dsx
  • Finding the {SERVICE_IDENTIFIER}
    In a IBM Watson Studio Local deployment, the {SERVICE_IDENTIFIER} is 
    /com/ibm/event/api/v1/
    • A sample REST URL:
      https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/keystore
    • The {SERVICE_IDENTIFIER} is a specific section from the REST URL. From the above example, it would be:
      /com/ibm/event/api/v1/
IBM Cloud Pak for Data deployment
  • Finding the {REST_SERVER_ENDPOINT}
    • Users can get the REST_SERVER_ENDPOINT by running the following command:
      oc get route ${NAMESPACE}-cpd -o jsonpath={.spec.host}
  • Finding the {SERVICE_IDENTIFIER}
    • The structure of the {SERVICE_IDENTIFIER} looks like: 
      icp4data-databases/${DEPLOYMENT_ID}/${NAMESPACE}/com/ibm/event/api/v1/
    • A sample REST URL:
      https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/keystore
      • Where the parameters in the URL are:
        • NAMESPACE = zen
        • DEPLOYMENT_ID = db2eventstore-1589227472411
        • SERVICE_IDENTIFIER = icp4data-databases/db2eventstore-1589227472411/zen

Version: 1.0.0

You can use the REST protocols to complete the following tasks:
Important: POST and DELETE /oltp/*commands, such as POST /oltp/database and DELET /oltp/table cannot complete if any of the nodes in the cluster are inactive. Other operations might also be impacted if any of the nodes are inactive.

If you notice that a command failed because one or more nodes are inactive, wait for the nodes to be automatically restarted before you run the command again.

REST API calls automatically time out after 30 minutes.

Security

Every request to IBM Db2 Event Store must include the authorization HTTP header with the value Bearer access_token.

You can retrieve a bearer token from the user management service. Each bearer token lasts 13 hours.

To retrieve a bearer token:
  1. Submit the GET /preauth/validateAuth command. For example:
    curl -k -X GET https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/v1/preauth/validateAuth -u joeuser:password

    In this example, zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com represents the host name for the master node, joeuser represents a user that exists in IBM Db2 Event Store, and password represents the password for the specified user.

    Tip: If you have the jq program installed, you can run the following command to automatically extract the bearer token:
    bearerToken=`curl -k -X GET https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/v1/preauth/validateAuth 
-u joeuser:password | jq -r '.accessToken'`
  2. Extract the accessToken from the returned JSON:
    {
 "username":"joeuser",
 "role":"User",
 "uid":"1003",
 "accessToken":"eyJhbGc...1AjT_w",
 "_messageCode_":"success",
 "message":"success"
}
  3. Set the value for the bearer token so that you can use the bearerToken variable in place of the value that is specified for the accessToken:
    bearerToken=eyJhbGc...1AjT_w
    After you set the bearer token to your access token, your HTTP header should include:
    "authorization: Bearer $bearerToken"

Manage the connection endpoints

The following APIs will return the connection endpoints that can be used to set up the connection to the target Db2 Event Store instance from JDBC, Scala, Python, and Java applications.

However, the endpoints returned are only accessible by the applications running in the same cluster where the target Db2 Event Store instance is deployed. For external connections from applications outside of the cluster, refer to Enabling external connections from applications using a load balancer.

GET oltp/jdbc_endpoint

Retrieves the connection endpoint of the target instance for JDBC connections.

Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \ 
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/jdbc_endpoint"

GET oltp/scala_endpoint

Retrieves the connection endpoint of the target instance for connections from Scala, Python, and Java applications.

Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \ 
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/scala_endpoint"

Manage the SSL keystore

GET oltp/keystore

Retrieves the SSL keystore from the cluster. Only available with the default keystore.

Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \ 
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/keystore"

GET oltp/keystore_password

Retrieves the SSL keystore password from the cluster. Only available with the default keystore.

Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \ 
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/keystore_password"

GET oltp/certificate

Retrieves the public SSL certificate from the cluster. Only available with the default public SSL certificate.

Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \ 
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/certificate"

Manage databases

GET /oltp/database

Retrieves the properties of the specified database.

Parameters
Parameter Description
databaseName String

The name of the database for which you want properties.

databaseName=EVENTDB
Responses
Code Description
ES380

Database information successfully retrieved

Sample response:

{
   "code":"ES380",
   "message":"Database information successfully retrieved",
   "data":{
      "nbNode":3,
      "nbShard":0,
      "dataStore":"glusterFS",
      "nbActiveConnections":4
   }
}
ES381

Database information failed to successfully retrieve

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/database?databaseName=EVENTDB"

GET /oltp/databases

Returns a list of all of the databases in IBM Db2 Event Store.

Parameters
No parameters
Responses
Code Description
ES350

List of all databases successfully retrieved

{
   "code": "String", 
   "message": "String",
   "data": [
     {
       "name": "String"
     }
   ]
}
ES351

List of all databases failed to retrieve

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/databases"

Create and manage tables

POST /oltp/table

Creates a new table in a database.

You must include the table definition as a JSON string in the HTTP request body. The definition specifies the structure of the table that you want to create, including column names and types, the primary keys, the sharding keys, and so on.

See Create a table with an index for more information about the rules for creating a table.

Parameters
Parameter Description
tableName String

The name to use for the new table.

tableName=TESTTABLE
databaseName String

The name of the database in which you want to create the table.

databaseName=EVENTDB
Responses
Code Description
ES320

Table created successfully

{
   "code": "String", 
   "message": "String"
}
ES321

Table could not be successfully created

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X POST -H "Content-Type: application/json" -H "authorization: Bearer $bearerToken" \
--data "{\"columnDefinition\": [{\"name\": \"i\", \"dataType\": \"Date\", \"nullable\": false}, \
{\"name\": \"j\", \"dataType\": \"Real\", \"nullable\": false}, \
{\"name\": \"k\", \"dataType\": \"Double\", \"nullable\": false}, \
{\"name\": \"l\", \"dataType\": \"TimeStamp\", \"nullable\": false}, \
{\"name\": \"m\", \"dataType\": \"String\", \"nullable\": false}, \
{\"name\": \"n\", \"dataType\": \"Date\", \"nullable\": false}, \
{\"name\": \"o\", \"dataType\": \"Real\", \"nullable\": false}], \
\"constraints\":[{\"name\": \"c1\", \
\"constraintType\": \"primaryKey\", \"columns\": [\"j\", \"i\"]},{\"name\": \"c2\", \
\"constraintType\": \"shardingKey\", \"columns\": [\"j\", \"i\"]}], \
\"indexes\":[{\"name\": \"index3\", \"equal\": [\"j\", \"i\"], \"include\": [\"k\"]}]}" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/table?tableName=TEST_IDXTABLE6&databaseName=EVENTDB"

DELETE /oltp/table

Deletes the specified table.

Parameters
Parameter Description
tableName String
The name of the table you want to delete.
tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES330

Table dropped successfully

{
   "code": "String", 
   "message": "String"
}
ES331

Table could not be successfully dropped

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X DELETE -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/table?tableName=TESTTABLE&databaseName=EVENTDB"

GET /oltp/table

Retrieves the properties of the specified table. This API returns the table name together with the schema name, for example:
DB2INST1.TAB1
where DB2INST1 is schema name used by Db2 Event Store and TAB1 is table name.
Parameters
Parameter Description
tableName String

The name of the table for which you want properties.

tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES370

Table information successfully retrieved

Sample response:

{
   "code":"ES370",
   "message":"Table information successfully retrieved",
   "data":{
      "groupID":0,
      "tableGroupName":"sys_TEST_IDXTABLE6",
      "id":0,
      "numberShards":36
   }
}
ES371

Table information failed to successfully retrieved

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/table?tableName=TESTTABLE&databaseName=EVENTDB"

GET /oltp/table/definition

Retrieves the definition for a table.

The table definition includes information about the columns that make up the table and the table constraints (the primary key, sharding key, and index)

Parameters
Parameter Description
tableName String
The name of the table for which you want the table definition.
tableName=TESTTABLE
databaseName String
The name of the database where the table exists.
databaseName=EVENTDB
Responses
Code Description
ES362

Table definition successfully retrieved

Sample return:

{
   "code":"ES362",
   "message":"Table definition successfully retrieved",
   "data":{
      "columnDefinition":[
         {
            "name":"n",
            "dataType":"Date",
            "nullable":false
         },
         {
            "name":"k",
            "dataType":"Double",
            "nullable":false
         },
         {
            "name":"j",
            "dataType":"Real",
            "nullable":false
         },
         {
            "name":"m",
            "dataType":"String",
            "nullable":false
         },
         {
            "name":"i",
            "dataType":"Date",
            "nullable":false
         },
         {
            "name":"l",
            "dataType":"TimeStamp",
            "nullable":false
         },
         {
            "name":"o",
            "dataType":"Real",
            "nullable":false
         }
      ],
      "constraints":[
         {
            "name":"primaryKey",
            "constraintType":"primaryKey",
            "columns":[
               "j",
               "i"
            ]
         },
         {
            "name":"shardingKey",
            "constraintType":"shardingKey",
            "columns":[
               "j",
               "i"
            ]
         }
      ],
  
{
  
      "indexes":[
         {
            "name":"index3",
            "equal":[
               "i",
               "j"
            ],
            "include":[
               "k"
            ]
         }
      ]
   }
}
ES363

Table definition failed to successfully retrieve

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/table/definition?tableName=TEST_IDXTABLE6&databaseName=EVENTDB"

GET /oltp/table/columns

Retrieves the list of column definitions for the specified table.

Parameters
Parameter Description
tableName String
The name of the table for which you want the column definitions.
tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES360

List of columns successfully retrieved

Sample response:

{
   "code":"ES360",
   "message":"List of columns successfully retrieved",
   "data":[
      {
         "nullable":false,
         "name":"n",
         "shardingKey":false,
         "primaryKey":false,
         "dataType":"Date"
      },
      {
         "nullable":false,
         "name":"k",
         "shardingKey":false,
         "primaryKey":false,
         "dataType":"Double"
      },
      {
         "nullable":false,
         "name":"j",
         "shardingKey":true,
         "primaryKey":true,
         "dataType":"Real"
      },
      {
         "nullable":false,
         "name":"m",
         "shardingKey":false,
         "primaryKey":false,
         "dataType":"String"
      },
      {
         "nullable":false,
         "name":"i",
         "shardingKey":true,
         "primaryKey":true,
         "dataType":"Date"
      },
      {
         "nullable":false,
         "name":"l",
         "shardingKey":false,
         "primaryKey":false,
         "dataType":"TimeStamp"
      },
      {
         "nullable":false,
         "name":"o",
         "shardingKey":false,
         "primaryKey":false,
         "dataType":"Real"
      }
   ]
}
ES361

List of columns failed to successfully retrieve

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/table/columns?tableName=TEST_IDXTABLE6&databaseName=EVENTDB"

GET /oltp/table/indexes

Retrieves the index information for the specified table.

Parameters
Parameter Description
tableName String

The name of the table for which you want the index information.

tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES372

List of indexes successfully retrieved

Sample response:

{
   "code":"ES372",
   "message":"List of indexes successfully retrieved",
   "data":[
      {
         "name":"index3",
         "equal":[
            "i",
            "j"
         ],
         "include":[
            "k"
         ]
      }
   ]
}
ES373

List of indexes failed to successfully retrieve

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/table/indexes?tableName=TEST_IDXTABLE6&databaseName=EVENTDB"

GET /oltp/tables

Retrieves a list of all of the tables in the specified database. This API returns the table name together with the schema name, for example:
DB2INST1.TAB1
where DB2INST1 is schema name used by Db2 Event Store and TAB1 is table name.
Parameters
Parameter Description
databaseName String

The name of the database for which you want to retrieve the list of tables.

databaseName=EVENTDB
Responses
Code Description
ES340

List of all tables successfully retrieved

Sample response:

{
   "code":"ES340",
   "message":"List of all tables successfully retrieved",
   "data":[
      {
         "name":"TEST_IDXTABLE6"
      }
   ]
}
ES341

List of all tables failed to retrieve

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/oltp/tables?databaseName=EVENTDB"

Monitor IBM Db2 Event Store

GET /monitoring/groom_rate

Retrieves the rate at which data is persisted to shared storage for the specified table.

Parameters
Parameter Description
tableName String

The name of the table for which you want to retrieve the information.

tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES500

Table groom rate successfully calculated

Sample response:

{
   "code":"ES500",
   "message":"Table groom rate successfully calculated",
   "rates":[
      {
         "rate":[
            {
               "node":"9.87.654.321:1101",
               "timestamp":1513207517000,
               "value":0
            },
            {
               "node":"9.87.654.322:1101",
               "timestamp":1513207517000,
               "value":0
            },
            {
               "node":"9.87.654.323:1101",
               "timestamp":1513207516000,
               "value":0
            }
         ],
         "count":0,
         "last":0,
         "tableName":"TEST_IDXTABLE6",
         "rateType":"groom_rate",
         "average":0
      }
   ],
   "health":{
      "nodes":[

      ],
      "overall_health":0,
      "spark":{
         "active":0,
         "previous":0
      },
      "nodes_health":[

      ]
   }
}
ES501

Table groom rate could not be calculated

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/monitoring/groom_rate?databaseName=EVENTDB&tableName=TEST_IDXTABLE6"

GET /monitoring/insert_rate

Retrieves the rate at which data is inserted into the specified table, specifically into the log zone.

Parameters
Parameter Description
tableName String

The name of the table for which you want to retrieve the information.

tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES510

Table insert rate successfully calculated

Sample response:

{
   "code":"ES510",
   "message":"Table insert rate successfully calculated",
   "rates":[
      {
         "rate":[
            {
               "node":"9.87.654.321:1101",
               "timestamp":1513132978000,
               "value":0
            },
            {
               "node":"9.87.654.322:1101",
               "timestamp":1513132978000,
               "value":0
            },
            {
               "node":"9.87.654.323:1101",
               "timestamp":1513132978000,
               "value":0
            }
         ],
         "count":0,
         "last":0,
         "tableName":"TEST_IDXTABLE6",
         "rateType":"insert_rate",
         "average":0
      }
   ],
   "health":{
      "nodes":[

      ],
      "overall_health":0,
      "spark":{
         "active":0,
         "previous":0
      },
      "nodes_health":[

      ]
   }
}
ES511

Table insert rate could not be calculated

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/monitoring/insert_rate?databaseName=EVENTDB&tableName=TEST_IDXTABLE6"

GET /monitoring/overview

Restriction: This command is intended for administrators. If you are not an administrator, some of the values in the response might be blank or set at 0.

Retrieves information about the overall health of the IBM Db2 Event Store application.

Parameters
No parameters.
Responses
Code Description
ES570

Database overview information successfully calculated

{
   "code":"ES570",
   "message":"Overall health information calculated successfully",
   "rates":[
      {
         "rate":[
            {
               "node":"9.87.654.321:1101",
               "timestamp":1513193787000,
               "value":0
            },
            {
               "node":"9.87.654.322:1101",
               "timestamp":1513193787000,
               "value":0
            },
            {
               "node":"9.87.654.323:1101",
               "timestamp":1513193787000,
               "value":0
            }
         ],
         "count":0,
         "last":0,
         "tableName":"TEST_IDXTABLE6",
         "rateType":"groom_rate",
         "average":0
      },
      {
         "rate":[
            {
               "node":"9.87.654.321:1101",
               "timestamp":1513193787000,
               "value":0
            },
            {
               "node":"9.87.654.322:1101",
               "timestamp":1513193787000,
               "value":0
            },
            {
               "node":"9.87.654.323:1101",
               "timestamp":1513193787000,
               "value":0
            }
         ],
         "count":0,
         "last":0,
         "tableName":"TEST_IDXTABLE6",
         "rateType":"insert_rate",
         "average":0
      }
   ],
   "health":{
      "nodes":[
         {
            "node_id":"9.87.654.321:1101",
            "status_color":"green",
            "status_code":0,
            "groomed":0,
            "ingest":0,
            "alerts":[
               {
                  "name":"HighMemory",
                  "value":0.0
               }
            ],
  
 "capacity":41
         },
         {
            "node_id":"172.16.200.121:1101",
            "status_color":"green",
            "status_code":0,
            "groomed":0,
            "ingest":0,
            "alerts":[
               {
                  "name":"HighMemory",
                  "value":0.0
               }
            ],
            "capacity":36
         },
{
            "node_id":"9.30.116.205:1101",
            "status_color":"green",
            "status_code":0,
            "groomed":0,
            "ingest":0,
            "alerts":[
               {
                  "name":"HighMemory",
                  "value":0.0
               }
            ],
            "capacity":39
         }
      ],
  "overall_health":41,
      "spark":{
         "active":0,
         "previous":0
      },
"nodes_health":[
         {
            "timestamp":1513193787477,
            "status_color":"green",
            "rateType":"openedConnection",
            "rateName":"Opened Connections",
            "value":1.0
         },
         {
            "timestamp":1513193783594,
            "status_color":"green",
            "alert_type":"HighCPU",
            "rateType":"ratioCpu",
            "rateName":"CPU",
            "unit":"%",
            "value":5.0
         },
         {
            "timestamp":1513193783594,
            "status_color":"green",
            "alert_type":"HighMemory",
            "rateType":"ratioMemory",
            "rateName":"Memory",
            "unit":"%",
            "value":38.75
         },
  
 {
            "timestamp":1513193787000,
            "status_color":"green",
            "alert_type":"HighGroomingPressure",
            "rateType":"ratioGroominMemory",
            "rateName":"Log Buffer Memory",
            "unit":"%",
            "value":0.0
         },
         {
            "timestamp":1513193785713,
            "status_color":"green",
            "rateType":"dataComputeIOLatency",
            "rateName":"I/O Wait Time",
            "unit":"ms",
            "value":6.2625
         },
 {
            "timestamp":1513193783594,
            "status_color":"green",
            "alert_type":"HighSSDStorage",
            "rateType":"ratioDataComputeDisk",
            "rateName":"Local Storage",
            "unit":"%",
            "value":7.0
         },
         {
            "timestamp":1513193783594,
            "status_color":"green",
            "alert_type":"HighSharedStorage",
            "rateType":"ratioSharedStorage",
            "rateName":"Shared Storage",
            "unit":"%",
            "value":7.0
         },
         {
            "timestamp":1513193783594,
            "status_color":"green",
            "alert_type":"HighComputeStorage",
            "rateType":"ratioComputeDisk",
            "rateName":"Compute Storage",
            "unit":"%",
            "value":7.0
         }
      ]
   }
}
ES571

Database overview information could not be calculated

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/monitoring/overview?databaseName=EVENTDB"

GET /monitoring/node

Restriction: This command is intended for administrators. If you are not an administrator, some of the values in the response might be blank or set at 0.

Retrieves information about the specified node.

Parameters
Parameter Description
node_id String

The ID of the node for which you want to retrieve the information.

For Db2 Event Store version 2.0.1.4 or later:
  • The value you need for this should be the hostname of the worker node followed by the port number.
  • You can obtain the node_id by running the /monitoring/overview command and extracting the ID from the node field or by running oc get nodes and under the NAME column obtain the DNS name of the worker node.
For Db2 Event Store version 2.0.1.3 or earlier:
  • The value you need for this should be the endpoint IP of the node followed by the port number.
  • You can obtain the node_id by running the /monitoring/overview command and extracting the ID from the node field.
Responses
Code Description
ES550

Node health information calculated successfully

Sample response:
{
   "metrics":[
      {
         "timestamp":1513194907729,
         "status_color":"green",
         "rateType":"openedConnection",
         "rateName":"Opened Connections",
         "value":1.0
      },
      {
         "timestamp":1513194904007,
         "status_color":"green",
         "alert_type":"HighCPU",
         "rateType":"ratioCpu",
         "rateName":"CPU",
         "unit":"%",
         "value":6.0
      },
      {
         "timestamp":1513194904007,
         "status_color":"green",
         "alert_type":"HighMemory",
         "rateType":"ratioMemory",
         "rateName":"Memory",
         "unit":"%",
         "value":41.0
      },
      {
         "timestamp":1513194907000,
         "status_color":"green",
         "alert_type":"HighGroomingPressure",
         "rateType":"ratioGroominMemory",
         "rateName":"Log Buffer Memory",
         "unit":"%",
         "value":0.0
      },
      {
         "timestamp":1513194906355,
         "status_color":"green",
         "rateType":"dataComputeIOLatency",
         "rateName":"I/O Wait Time",
         "unit":"ms",
         "value":5.89
      },
  
 {
         "timestamp":1513194904007,
         "status_color":"green",
         "alert_type":"HighSSDStorage",
         "rateType":"ratioDataComputeDisk",
         "rateName":"Local Storage",
         "unit":"%",
         "value":4.0
      },
      {
         "timestamp":1513194904007,
         "status_color":"green",
         "alert_type":"HighSharedStorage",
         "rateType":"ratioSharedStorage",
         "rateName":"Shared Storage",
         "unit":"%",
         "value":4.0
      },
      {
         "timestamp":1513194904007,
         "status_color":"green",
         "alert_type":"HighComputeStorage",
         "rateType":"ratioComputeDisk",
         "rateName":"Compute Storage",
         "unit":"%",
         "value":4.0
      }
   ]
}
ES551

Node health information could not be successfully calculated

{
   "code": "String", 
   "message": "String"
}
Sample call
Sample call for Db2 Event Store version 2.0.1.4 or later
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/monitoring/node?node_id=worker1.eventstore.cp.fyre.ibm.com:1101"
Sample call for Db2 Event Store version 2.0.1.3 or earlier
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/monitoring/node?node_id=9.87.654.322:1101"

GET /monitoring/table/rates

Retrieves the following information for the specified table:
  • The rate at which data is inserted into the specified table, specifically into the log zone. (This is the same information that is provided by the GET /monitoring/insert_rate method.)
  • The rate at which data is persisted to shared storage for the specified table. (This is the same information that is provided by the GET /monitoring/groom_rate method.)
Parameters
Parameter Description
tableName String

The name of the table for which you want the rate information.

tableName=TESTTABLE
databaseName String

The name of the database where the table exists.

databaseName=EVENTDB
Responses
Code Description
ES520

Node health information calculated successfully

Sample response:

{
   "code":"ES520",
   "message":"Table rates successfully calculated",
   "rates":[
      {
         "rate":[
            {
               "node":"9.87.654.321:1101",
               "timestamp":1513208318000,
               "value":0
            },
            {
               "node":"9.87.654.322:1101",
               "timestamp":1513208318000,
               "value":0
            },
            {
               "node":"9.87.654.323:1101",
               "timestamp":1513208318000,
               "value":0
            }
         ],
         "count":0,
         "last":0,
         "tableName":"TEST_IDXTABLE6",
         "rateType":"groom_rate",
         "average":0
      },
      {
         "rate":[
            {
               "node":"9.87.654.321:1101",
               "timestamp":1513208318000,
               "value":0
            },
            {
               "node":"9.87.654.322:1101",
               "timestamp":1513208318000,
               "value":0
            },
            {
               "node":"9.87.654.323:1101",
               "timestamp":1513208318000,
               "value":0
            }
         ],
         "count":0,
         "last":0,
         "tableName":"TEST_IDXTABLE6",
         "rateType":"insert_rate",
         "average":0
      }
   ]
}
ES521

Node health information could not be successfully calculated

{
   "code": "String", 
   "message": "String"
}
Sample call
curl -k -i -X GET -H "authorization: Bearer $bearerToken" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/monitoring/table/rates?tableName=TESTTABLE&databaseName=EVENTDB"

Run an SQL query

POST /spark/sql

Run a Spark SQL query against an IBM Db2 Event Store database.

Include the SQL query statement as a JSON string in the HTTP request body.

Parameters
Parameter Description
databaseName String

The name of the database to run the query against.

databaseName=EVENTDB
tableName String

The name of the table to run the query against.

tableName=TESTTABLE
format String

The format that you want the results returned as. Specify either json or csv.

format=json|csv
separator String

Use this option only if you specified format=csv

Specify the character that you want to use as a separator between each value in the returned CSV file:
  • If you want to use a comma as the separator, you don't need to specify the separator parameter. The comma is the default value.
  • If you specify a character other than a comma, ensure that the character is specified as a URL escape character. 
separator=character
header Boolean

Use this option only if you specified format=csv

Specify whether you want to include a header row with a column heading for each column of data:
  • true - Use this value if you want to generate a header row in the resulting CSV.
  • false - Use this value if you don't want to generate a header row in the resulting CSV.
header=true|false
compress Boolean

Use this option only if you specified format=csv

Specify how you want the resulting files to be returned:
  • true - Returns a compressed GZIP file that includes the resulting CSV file or files.
  • false - Returns the resulting CSV file or files as uncompressed files.
compress=true|false
readOption String

To quickly ingest large amounts of data, IBM Db2 Event Store writes data to a log before it is persisted to the shared storage (where data is available to external query engines). When data is transitioned from the log to the shared storage, an index is created and duplicate data is removed.

After all data in the log file has been written to the shared storage, IBM Db2 Event Store can return a consistent snapshot of the data. However, if you are running a query on data as it is added, the consistent snapshot might not reflect the most up-to-date data.

To strike a balance between returning data immediately and returning a consistent snapshot of the data, you can specify the isolation level that you want to run the query against. By default, IBM Db2 Event Store runs queries against the SnapshotAny isolation level.

Specify the isolation level that you want to run the query against:
  • SnapshotNow - This value returns a consistent snapshot of data that exists at the time query is run.

    To ensure that the query results include a consistent snapshot of all of the data that was inserted before the query was run, this option waits to take a snapshot until any data in the log is persisted to the shared storage. This means that you might encounter a short delay before results are returned.

  • SnapshotAny - This is the default value. This value returns the most recent consistent snapshot in the shared storage without waiting for all of the data in the log to be persisted to the shared storage.

    This snapshot might not include the most up-to-date data if there is still data in the log that needs to be persisted to the shared storage.

  • SnapshotNone - This value returns a view that contains the data from the most recent consistent snapshot and any data that is in the log. This means that the view contains all the available data, but that it might also contain duplicates.
readOption=SnapshotNow|SnapshotAny|SnapshotNone
Responses
Code Description
ES400

SparkSQL query was correctly executed

{
   "code":"String",
   "message":"String",
   "data": '{http://jsonlines.org/}'
}
ES401

SparkSQL failed to execute

{
   "code": "String", 
   "message": "String"
}
Sample call

For example, to submit a query and have the results returned as a JSON string, run:

curl -k -i -X POST -H "Content-Type: application/json" -H "authorization: Bearer $bearerToken" \ 
--data "{\"sql\": \"select * from TEST_IDXTABLE6\"}" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/spark/sql?databaseName=EVENTDB&tableName=TEST_IDXTABLE6&format=json"

To submit a query and have the results returned as a CSV file with a header that uses | as the escape character, run:

curl -k -i -X POST -H "Content-Type: application/json" -H "authorization: Bearer $bearerToken" \
--data "{\"sql\": \"select * from TEST_IDXTABLE6\"}" \
"https://zen-cpd-zen.apps.cpd-test-lb-1.fyre.ibm.com/icp4data-databases/db2eventstore-1589227472411/zen/com/ibm/event/api/v1/spark/sql?databaseName=EVENTDB&tableName=TEST_IDXTABLE6&format=csv&separator=%7C&header=true"