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.
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.
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
- This is the Db2 Event Store host IP, this can be
identified by checking the service IP of the kubernetes service name:
- 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/
- A sample REST
URL:
- Finding the
- 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}
- Users can get the
- Finding the
{SERVICE_IDENTIFIER}
- The structure of the
{SERVICE_IDENTIFIER}
looks like:icp4data-databases/${DEPLOYMENT_ID}/${NAMESPACE}/com/ibm/event/api/v1/
-
The
NAMESPACE
is the Kubernetes Namespace (also known as the OpenShift project name) which was used during the Db2 Event Store installation. -
The
DEPLOYMENT_ID
can be obtained on the Database details page in the IBM Cloud Pak for Data UI console after the deployment of the database. For more information, refer to Identifying the deployment ID of Db2 Event Store in a IBM Cloud Pak for Data deployment.
-
- 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
- Where the parameters in the URL are:
- The structure of the
- Finding the
Version: 1.0.0
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.
- 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, andpassword
represents the password for the specified user.Tip: If you have thejq
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'`
- Extract the
accessToken
from the returned JSON:{ "username":"joeuser", "role":"User", "uid":"1003", "accessToken":"eyJhbGc...1AjT_w", "_messageCode_":"success", "message":"success" }
- Set the value for the bearer token so that you can use the
bearerToken
variable in place of the value that is specified for theaccessToken
: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
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
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
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
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"
GET /monitoring/table/rates
- 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"