In-memory database

In-memory databases rely primarily on main memory for storage. They are faster than disk-based databases due to the following:

  • In-memory optimization algorithms are simpler and execute fewer CPU instructions.
  • Eliminate data access seek time.
  • More predictable than more traditional disk-based databases.

However, because in-memory databases store data on volatile memory devices, stored information is lost when the device powers down or is reset.

Web Dashboard enables you to work with in-memory databases using support for the H2 Database Management System. For details, visit www.h2database.com.

This information describes how to create an in-memory database using Web Dashboard and how to populate it with data. When your in-memory database is ready, you can use it as the basis for reports and dashboards, as you would any other type of database.

Because in-memory databases rely on main memory, make sure you have the necessary memory available for Web Dashboard.

Follow these high-level steps to create an in-memory database:

  1. Create an H2 in-memory database—the source of the in-memory database
  2. Populate an H2 in-memory database— populate the in-memory database.

Create an H2 in-memory database

Creating an H2 in-memory database is a straightforward process: You create a Web Dashboard World object and as soon as you initiate the first connection to the database using that World object, your in-memory database is created. Depending on your settings, your database will exist until the last connection to it is closed (the default behavior,) or as long the Web Dashboard is running.

You create the World object that corresponds to your in-memory database like you would any other World object, except you must select the H2 JDBC driver and modify the URL parameter to indicate that Web Dashboard can create an in-memory database. See Create a world . Use the following details when you set up a World object.

  1.  To create an in-memory database, use the following values:

  2. Field Name Description
    JDBC driver Select the H2 JDBC driver from the drop down.
    URL

    Enter an URL for an in-memory database using the following syntax:

    jdbc:h2:mem:<databaseName>

    where <databaseName> is the name of the in-memory database you want to create. This URL is for a public in-memory database, which means the database can be accessed using one or more simultaneous connections.

    Using this basic URL, your database will run until the last connection to it is closed.

    For example, assume you have created a World object with the URL specifying an in-memory database. You save the object and select the Test connection button in the toolbar. For testing, Web Dashboard will initiate a connection, and because this is the first connection, it will create the database. As soon as the connection is successful, the test is finished, and the connection is closed. Since this is the only connection to the database, when it is closed the database is deleted.

    To keep the database running for as long as the application runs, you can use the option, DB_CLOSE_DELAY. Use the following syntax:

    jdbc:h2:mem:<databaseName>;DB_CLOSE_DELAY=<time>

    where <time> is the length of time (in seconds) to keep the database open after the last connection to is was closed. The default value is 0 and will allow the database to close when the last connection is closed. To allow the database to stay open indefinitely, specify value of -1.

    For example, to set up a database called test that will stay open when the last connection is closed, use the following URL:

    jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

    For a detailed list of options see www.h2database.com

    User name and Password Specify the user name and password required to log in to the database.
  3. Create a data dictionary using the procedure provided in Build a data dictionary. Creating a source data dictionary allows you select a subset of the database defined in the source world.
    1. On the Description tab, use the following values:
    2. Field Name Description
      Name and Description Provide a name and description of the data dictionary you will use as the source of data for your in-memory database. Note that the value you provide for the Description field is displayed when the data dictionary is included in a list.
      Type

      Select SQL.

      This indicates you want to use a SQL query to retrieve data from your source world. You enter the SQL query statement on the SQL Query tab.

      World Select the source world that was created.
    3. On the SQL Query tab, enter an SQL statement that will choose the data with which you want to populate your in-memory database. You can write this statement to select any or all of the data in your source world. For example, you could select some combination of data from multiple tables in your source world as an aggregate.
  4. Click the Save icon.

Create a persistent H2 database

To create a persistent H2 database you have to set up a World object to enable the connection to the database. See Create a world for procedural information and use the following details setting up a World object.

Field Name Description
JDBC driver Select the H2 JDBC driver from the drop down.
URL

Enter an URL for an in-memory database using the following syntax:

jdbc:h2:[file:]<path><databaseName>

The prefix file is optional. If you use no path or only a relative path, then the current working directory is used as a starting point. The case sensitivity of the path and database name depend on the operating system. However, it is recommended that you use lowercase letters only. The database name must be at least three characters long. To point to the user home directory, use ~/, as in: jdbc:h2:~/test.

In order to have the same performance as with the in-memory database, you can set up a cache size. This setting can be appended to the database URL:

jdbc:h2:test;CACHE_SIZE=8192

This option sets the size of the cache in KB for the current database. The default value is 16384 (16 MB). The value is rounded to the next higher power of two. Depending on the virtual machine, the actual memory required may be higher.

This setting is persistent and affects all connections, as there is only one cache per database. This setting only affects the database engine (the server in a client/server environment). It has no effect on in-memory databases.

For a detailed list of options, see www.h2database.com.

User name and Password Specify the user name and password required to log in to the database.

Create a data dictionary

Creating a source data dictionary allows you select a subset of the database defined in the source world. Create a data dictionary using the procedure provided in Build a data dictionary, and use the following details.

  1. On the Description tab, use the following values:
    Field NameDescription
    Name and Description

    Provide a name and description of the data dictionary you will use as the source of data for your in-memory database.

    Note  The value you provide for the Description field is displayed when the data dictionary is included in a list.
    Type

    Select SQL.

    This indicates you want to use a SQL query to retrieve data from your source world. You enter the SQL query statement on the SQL Query tab.

    World Select the source world you created above.
  2. On the SQL Query tab, enter an SQL statement that will select the data with which you want to populate your in-memory database. You can write this statement to select any or all of the data in your source world. For example, you could select some combination of data from multiple tables in your source world as an aggregate.
  3. Click the Save icon.

Populate an H2 in-memory database

Web Dashboard enables you to populate in-memory databases using the Task object. This object will use a Data Dictionary to fetch data from a source database identified by a World, and will populate the target in-memory database.

At this point, you have three objects defined:

  • source World
  • source Data dictionary
  • target World (pointing at your in-memory database)

Next, create a Task object to populate your target database with the data from the source database. You must create a Task Group first, as every Task must belong to a Task Group. Task Groups are logical objects grouping Tasks.

Create a task to populate in-memory database

To use a task to take data from your source World, create a table in your destination World, and put the data in the table.

  1. On the Main menu, click Task > Task. A list of existing tasks displays.
  2. Click the Add button icon green plus sign to create a new task. A new document opens and the Description tab displays.
  3. Specify values for these fields on the Description tab:
  4. Field Name Description
    Name and Description Provide a name and description of the task you will use to populate your in-memory database. Note that the value you provide for the Description field is displayed when the task is included in a list.
    Task Type

    Select Datamart.

    This type of task reads data from a source, applies a transformation if necessary, and then stores the result at a specified destination. You specify a source and destination on the Execution tab.

    Task Group Specify a task group you want your new task to be associated with.
    Start date and End date Specify a date range during which you want your task to be executed. If you do not enter values for these fields, Sentinel will execute the task indefinitely.
    Frequency

    Specify how often you want your task to be executed.

    You can specify Frequency in terms of minutes, hours, days, weeks, or months.

    CRON expression (Optional) Enter a CRON expression to indicate when the task should be executed.
  5. Click the Execution tab and specify values for the following fields.
  6. Field Name Description
    Source Select Query. This will enable you to select the SQL-type Data Dictionary you created earlier in this process.
    Transformation

    Select None.

    Destination

    Select Table.

    This indicates that the output from this task will be a table. You will provide more specific information about the destination table on the Settings tab.

  7. Click the Settings tab and specify values for the following fields:
  8. Field Name Description
    Source - SQL Query Select the name of the source data dictionary you defined above. As this data dictionary is SQL-based , the query used to define this data dictionary will also be used to retrieve data from your source world.
    Transformation None is displayed as you did not choose to perform any transformation in the Execution tab.
    Destination - World

    Select the destination world you defined above. This world is defined as an in-memory database.

    Destination - Table Enter the name of the table you want to populate with data from your source world. This table will reside in the destination world.
    Insert type

    Select Insert.

    This is the operation Sentinel will use to populate your destination table.

    Clear first Select this check box to ensure that if the table you specified above already exists, it will be cleared of data before this task populates it as the destination table.
    Create table if needed Select this check box to ensure that if the table does not exist, it will be created before Sentinel attempts to populate it.
  9. Click the Save icon.

Execute the task

Use the Task Manager to execute the task to create the in-memory database.

  1. Go to Task >Task Manager.
  2. Click the Stop button to stop all tasks.
  3. Click the Refresh button. The new task group and tasks are displayed.
  4. Click the Start button to restart the tasks.
  5. While the tasks are executing, you can click the Refresh button and mouse over your task to see its status.
  6. When task is finished, check the My Documents folder for your new table.

When your table is successfully created, you can access it using a report.

Related Links