Adding a New Index to a DBS-Based Service

All CygNet DBS-based services (for example, ACS, AUD, DDS, FAC, GRP, MSS, etc.) require a Data Dictionary Language (.ddl) file to define, among other things, the database indexes. Certain indexes are provided by default but others can be added to account for customer-specific implementations. If a customer uses, for example, Facility Attribute 10 for a particularly useful identifying property that is referenced often, an index on that attribute might be wise.

Proper use of these custom indexes can provide orders-of-magnitude performance improvements.

The nature of which properties should have indexes and what data to store with each will vary per installation and is not addressed in this document.

Step 1: Define New Index in DDL File

All default indexes are defined in the related service’s DDL file. While this file can be modified directly to add new indexes, we do not recommend this path. Instead there is support for adding new indexes through a separate index file. This file will not exist by default so it must be created manually. For example, for the FAC service, this additional index file must be named FACAPPL.DDL. The name of this file can be found at the bottom of the DDL file for each service. This file must be located in the related service’s directory, for example, the Services\FAC directory for the Facility service.

Index Syntax

An index is defined using the syntax shown below. This example exists by default in FAC.DDL and creates an index on Facility Attribute 0:

DEFINE INDEX ID=F0_INDEX, NAME=FAC_F0_INDEX, VERSION=1, RECORD_ID_BYTE=7, PREFIX=F0, DESCR="Facility Attribute0 (FacAttr0, FacSite, FacServ, FacId, FacType)", ITEM=(fac_hdr.fac_grp.FAttr0), ITEM=(fac_hdr.fac_grp.FacSite), ITEM=(fac_hdr.fac_grp.FacServ), ITEM=(fac_hdr.fac_grp.FacId), ITEM=(fac_hdr.fac_grp.FacType);

This definition creates an index on a particular attribute, assigns it some unique identifiers, and specifies which attributes are saved with this index (in this case, Site, Service, Facility ID, and Facility Type). The index elements are described in the following table.

Attribute	Description
ID	String. Index identifier. Must be unique for service. Maximum length of 8 characters.
NAME	String. Describes the index, usually using the service type and prefix.
VERSION	Integer. Usually 1, unless changing existing index.
RECORD_ID_BYTE	Integer. Must be unique.
PREFIX	String. Must be unique. Maximum length of 2 characters.
DESCR	String (with quotation marks). Describes the index attribute and the properties associated with the index attribute.
ITEM	One or many properties associated with this index. The syntax uses the names for groups and attributes defined in the main DDL file. Multiple ITEM lines are separated with commas. The last ITEM line ends with a semicolon.

Index Size

The total size of an index cannot exceed 99 bytes. Therefore, the size of the index property, plus each of its associated properties must add up to less than 100 bytes. In addition to the user-defined items, the index entry always contains a two byte prefix (F0 in the example above), one termination byte, and a key that can range from 10 to 34 bytes. The size of the key depends on the key level.

To determine how many user-defined bytes can be included in an index, deduct the total of the key size, prefix, and termination bytes from 99. The resulting number results in the amount of user-defined bytes available.

For example, in the FAC_F0_INDEX above, the key is 10 bytes (level 1), plus the 2 byte prefix and the 1 termination byte add up to 13 non user-defined bytes. The index uses a total of 66 user-defined bytes, although there is room for a total of 86 user-defined bytes (99 bytes - 13 non user-defined bytes), so the limit has not been exceeded.

User-Defined Bytes
Attribute	Size (bytes)
Facility Attribute 0	20
Facility Site	8
Facility Service	8
Facility ID	20
Facility Type	10
	66 bytes

Non User-Defined Bytes
Index Prefix	2
Termination	1
Key Size	10
	13 bytes
Total Index Size	79 bytes

These size limitations inform best practices for creating indexes. If the index is created on a property with a large size, that will limit the number (and size) of the associated properties with that index.

Key Level

There are four key levels as shown in the following table.

Name	Level	Size (bytes)
QueueKey	1	10
L2Key	2	18
L3Key	3	26
L4Key	4	34

The key level is determined by the highest level of any segment in the DDL file from which the index includes data. The DE_INDEX example below contains data from the dat_hdr and udc_hdr segments.

DEFINE INDEX ID=DE_INDEX, NAME=DDS_DE_INDEX, VERSION=5, RECORD_ID_BYTE=10, PREFIX=DE, DESCR="Data Group Elements", ITEM=(dat_hdr.dat_grp.GrpType), ITEM=(udc_hdr.udc_grp.DataEid), ITEM=(udc_hdr.udc_grp.FacId), ITEM=(udc_hdr.udc_grp.UDC), ITEM=(udc_hdr.udc_grp.UdcFlr);

In the Segments section of the DDS.DDL file, the dat_hdr segment is level 2 and the udc_hdr segment is level 3, so the key level is 3 and the key size used in determining the index size is 26 bytes.

DEFINE SEGMENT ID=dat_hdr, NAME=DDS_DATA_GROUP_HEADER, DESCR="Data Group Header Segment", VERSION=5, LEVEL=2, RECORD_ID_BYTE=3, GROUP=hdr, GROUP=upd_grp, GROUP=dat_grp;   DEFINE SEGMENT ID=udc_hdr, NAME=DDS_DATA_ELEMENT_HEADER, DESCR="Data Element Header Segment", VERSION=5, LEVEL=3, RECORD_ID_BYTE=4, GROUP=hdr, GROUP=upd_grp, GROUP=udc_grp;

Step 2: Reindex Database File

In order to use the new indexes that have been added to the service, you must stop the service and reindex the database file. This is done via the ReIndexESE (ReIndexEse.exe) command-line utility that can be found in your CygNet\Utilities directory. The syntax for ReIndexEse.exe is:

ReIndexEse.exe c=<path to service config file>

Example

ReIndexEse c=C:\CygNet\Services\FAC\Fac.cfg

If any of the above requirements on property length or ID uniqueness are violated, ReIndexEse will return errors. If no errors are detected, the database file will be reindexed. Now the service can be restarted and the new index used immediately.