Working with integrated IBM Db2 Event Store databases

If you created a IBM® Db2® Event Store database deployment in your IBM Cloud Pak for Data cluster, you can access the integrated database console to create tables and to monitor the database. To load data into the database, you can use the IBM Db2 Event Store batch insert APIs, a notebook, JDBC, and CSV files.

When your database is running, you can complete the following tasks:

Accessing the database

All Cloud Pak for Data users can access the database.

To access the database:
  1. From the navigation menu, select Data > Databases.
  2. Open the actions menu for the database that you want to access and select Open database.

Accessing sample notebooks

IBM Db2 Event Store includes sample notebooks that you can download from GitHub. The sample notebooks are a great way to get started with IBM Db2 Event Store APIs.

To access the notebooks:
  1. From the navigation menu, select Data > Databases.
  2. Open the actions menu for the database that you want to connect to and select Details.
  3. From the actions menu on the Database details page, select one of the following notebooks:
    • Notebook: IBM Db2 Event Store API

      This notebook includes examples that use the IBM Db2 Event Store Scala client interface to create a database and a table. It also shows how to insert and query data in IBM Db2 Event Store by using Spark SQL.

    • Notebook: Event Store ML scoring python

      This notebook includes examples that use the IBM Db2 Event Store Scala client interface to create a database and a table. It also shows how to insert and query data in IBM Db2 Event Store by using Spark SQL. In addition, this notebook shows you how to build and deploy a machine learning model using data from the IBM Db2 Event Store database.

  4. To use the sample notebooks in Cloud Pak for Data:
    1. In GitHub, open the Code tab and download the repository.
    2. Upload the sample notebook ipynb files to a project in Cloud Pak for Data. Note that Watson Studio is required to run notebooks.

Monitoring the database

You can use the integrated monitoring console to ensure that the database is healthy.

To monitor the database:

  1. Access the database.
  2. Select Monitor to open the console.
From the console, you can see the following metrics:
Metric Description
Data flow overview Indicates the following information:
  • The rate at which data is ingested into the database from a data source.
  • The rate at which data is persisted from the database log to the shared storage.
  • The overall health of the nodes in the cluster.
Resource usage Indicates the resource usage of the database in terms of CPU, memory, storage, and log space. Additionally, this table indicates the number of open database connections.
Aggregated ingest rate Indicates the rate at which the streaming data is ingested into the database log files (before it is persisted to shared storage). The rate from each node is displayed and aggregated to give the rate for the entire database.
Aggregated shared storage rate Indicates the rate at which the streaming data is persisted to shared storage. The rate from each node is displayed and aggregated to give the rate for the entire database.

When the data is persisted to shared storage, duplicate entries are removed, associated indexes are generated, and the data is written to the highly compressed open Apache Parquet format.

Creating tables

Important: After you create a table, you cannot edit it. You must drop the table and recreate it. For more information, see the appropriate API guides in Develop applications for IBM Db2 Event Store.

You can create tables by using the following methods:

  • From the Cloud Pak for Data web console.
  • From a standalone program or notebook by using one of the Db2 Event Store APIs.
  • By using the eventstoreUtils macro.
  • Remotely by using a remote Db2 client connected to the database.

To create a table from the web console:

  1. Access the database
  2. Open the Menu and select Tables.
  3. Click add table.
  4. On the Name tab, specify the name of the table.
  5. On the Columns tab, specify the columns that you want to include in the table.

    For each column, specify the name of the column, the data type, and whether the column can have a null value.

  6. On the Keys tab, click add key. Specify the primary keys for the table.

    A primary key is a value that is unique for each entry. It can be a single value, such as a transaction number, or a combination of values, such as a customer ID and a time stamp. To prevent duplication, the database ensures that only a single version of each primary key exists in the table.

    Restriction: The primary keys cannot be strings.
  7. Specify the key on which to shard the table.

    The IBM Db2 Event Store database tables are partitioned into micro-partitions or shards. The number of micro-partitions is determined at database creation time based on the number of available nodes. The assignment of micro-partitions to nodes is dynamic, and can change depending on the availability of nodes.

Loading data

You can ingest data into IBM Db2 Event Store in multiple ways: the Db2 Event Store client APIs (which can be invoked from a standalone program or a notebook), by using a remote connection from a standard Db2 client and loading from CSV files, or from within the cluster from a notebook that is running within the Watson Studio service or from CSVs by using the information that is provided in the CSV loading page description.

For more information about connecting, see Connecting to Db2 Event Store.

You must use a notebook or the IBM Db2 Event Store client APIs to insert data into your database. For more information, see the following API guides:

Running an SQL query

You must use a notebook or the IBM Db2 Event Store API to run an SQL query against the database. For more information, see the appropriate API guide:

Also see these topics:

Deleting a database

A database administrator can delete a database.

To delete a database:

  1. Sign in to the Cloud Pak for Data web client.
  2. From the navigation menu, select Data > Databases.
  3. Open the actions menu for the database that you want to access, and select Delete.
Important: If you used dynamic provisioning and the reclaim policy on the storage class is set to Retain, the data in the database is preserved even if the database is deleted. If the reclaim policy is set to Recycle or Delete the data is also deleted when the database is deleted.