Local Database Management

The Management tab is used to define and manage Local Database table indexes and to compact the Local Database.

Managing indexes

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and the use of more storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records. A unique index does not allow entry of duplicate values in the columns, thereby guaranteeing that no two rows of a table are the same.

Defining a Local Database index

To define a Local Database index, follow these steps:

  1. From the Workbench left hand pane, expand the appropriate node.
  2. Click Local Database.
  3. Click on Management tab.
    The Management tab appears, any previously defined indexes are displayed. For example:

  4. Click New.
    The New Index window appears:

  5. Enter the parameters for the new index as follows:
    ParameterDescription
    NameThe unique name of the index. An index name can be up to 64 characters in length and can include letters, numbers, and the underscore character. You will not be able to type invalid characters. For example, spaces are not allowed
    TableThe list of the Local Database tables on this node. Select the table for the new index.
    When a table is selected, its columns are displayed in the section below the table name.
    Column selectionThe list of columns for the selected table.
    Select one or more columns for the index. Multiple selections can be made using Ctrl-click, Shift-click, and Ctrl-A.
    Allow Duplicate ValuesAn indication of whether the columns in the index will allow duplicate values or not.
    When this check box is selected, the index will allow entry of duplicate values in the columns.
    When the check box is cleared, the index will not allow entry of duplicate values in the columns. This will guarantee that no two rows of a table are the same.
  6. When done with the parameter entry, click Create.
  7. If additional indexes need to be defined for the table, repeat steps 4 - 6.

Deleting a Local Database index

A Local Database table index can be deleted any time it is determined it is not needed.

To delete a Local Database index, follow these steps:

  1. From the Management tab, select the index to delete.
  2. Click Delete.
    Alternatively, you can also right click the index to display its pop-up menu and select the Delete option.
    A confirmation dialogue will be displayed.
  3. Click Yes to proceed.

The index will be deleted from the Local Database and removed from the list of indexes on the Management tab.

Compacting the Local Database

The Local Database can be periodically compacted to reduce the size and fragmentation of the data files on the node. This function should be used during a period of limited or no application execution so that the processing does not impact normal application processing and performance. To compact the Local Database, follow these steps:

  1. During a period of limited or no application execution, click the Compact Database button in the top right of the tab.
    A confirmation dialogue will be displayed.
  2. Click Yes to proceed.
    A completion message will be displayed when the processing has completed.

If an on demand compact is not optimal, it is possible to have deviceWISE do this incrementally as it is running. This will ensure the database is as compact as possible. The downside of this is a very small performance degradation. To enable this, add to a user property file the following entry: database.app.auto_vacuum=FULL

Relocating the Local Database

You will be able to relocate the local database in case if you want to store the data in a different location (in a different drive). In order to do it, you need to overwrite deviceWISE Transaction Engine/Server default settings, as follows:

  1. Create a file named dwconfig.local.properties in the dwjava directory. As an example, when referencing a different directory location for the deviceWISE database that holds the local database files, you create a file named dwconfig.local.properties in the dwjava directory with the following entry. DW_LOCALDB_PATH= <newDataLocation>

There must be a directory named localdb off of the d: directory on the drive in which deviceWISE is installed.