Script file format

• All statements are separated by a semicolon.

• A leading hash sign (#) indicates a comment.

• A quote character in a quoted string must be escaped with a backslash.

• Data set, item and index item names can be written in quotes or quotes can be omitted.

Statement syntax

• CONNECT
• EXIT

• CREATE USER
• CHANGE USER
• DROP USER

• CHANGE PASSWORD

• RENAME DATABASE

• DATABASE

• CREATE GROUP
• DROP GROUP

• GRANT and REVOKE user privileges
• GRANT and REVOKE group privileges
• GRANT and REVOKE member privileges
• GRANT and REVOKE data set privileges

• CREATE PROPERTY
• CHANGE PROPERTY
• DROP PROPERTY

• TurboIMAGE compatible database properties

• CHANGE DATABASE

• CREATE ITEM
• CHANGE ITEM
• DROP ITEM
• ORDER ITEMS

• CREATE IITEM
• CHANGE IITEM
• DROP IITEM

• CREATE SET
• CHANGE SET
• DROP SET

• CREATE or CHANGE SET clauses
  • NAME
  • TYPE
  • ADD ITEM
  • DELETE ITEM
  • ADD INDEX
  • DELETE INDEX
  • ADD PATH
  • CHANGE PATH
  • DELETE PATH
  • SET|UNSET ENCRYPTED
  • CHANGE ITEM ... SET|UNSET ENCRYPTED
  • SET|UNSET INDEXED

• UPGRADE DATABASE

• CREATE ENCRYPTION KEY
• CHANGE ALL ENCRYPTION KEYS
• DELETE ALL ENCRYPTION KEYS

• SET / UNSET ITEM ACCESS
• DELETE ALL ITEM ACCESS

• CREATE FTS PARSER CONFIG
• CHANGE FTS PARSER CONFIG
• DELETE FTS PARSER CONFIG

• CREATE FTS EXCLUSION LIST
• CHANGE FTS EXCLUSION LIST
• DELETE FTS EXCLUSION LIST

• CREATE FTS INDEX
• CHANGE FTS INDEX
• DELETE FTS INDEX

• DELETE ALL FTS

CONNECT

CONNECT TO "server" [AS "user" [PASSWORD "password"]];
CONNECT AS "user" [PASSWORD "password"];

Specifies the server to contact and the user and password to logon to the server.

Server is specified with a [host][:service] syntax where host defaults to the local system and service defaults to the "eloqdb" service name. Alternatively, a server may be defined from the command line (using the -h option), or the EQ_DBSERVER environment variable may be used.

User and password can be a reference to a file if prepended by "file:". User and password may also be defined by either command line options or the EQ_DBUSER and EQ_DBPASSWORD environment variables.

Notes:

• In a script, the CONNECT TO statement is optional and can be used to override the defaults.

Examples:

CONNECT TO "server";
CONNECT TO "server:eloqdb";

Legacy syntax (deprecated, do not use in new scripts):

CONNECT "server[:service]";
LOGON "user" [PASSWORD "password"];

EXIT

EXIT;

Exit parser, the rest of the script file will be ignored.

CREATE USER

CREATE USER "user" 
  [PASSWORD "password"] 
  [PRIVILEGES [user-priv-list]];

Create a new user with the specified password, if any.

The PRIVILEGES clause, if included, defines the privileges associated with the user. Multiple privileges can be specified, separated by a comma.

The following user privileges are available:

DBA

The user has server administration privileges.

CONNECT

The user is allowed to connect the server. This is implied if a user has the DBA privilege.

UADMIN

The user is allowed to administrate user accounts.

OPERATOR

The user has operator capabilities.

Notes:

• The UADMIN privilege is required to maintain database users.
• The CONNECT privilege is granted by default if the PRIVILEGES clause is not present.

Example:

CREATE USER "mike" PASSWORD "secret" PRIVILEGES CONNECT;

CHANGE USER

CHANGE USER "user"
  [NAME "name"]
  [PASSWORD "password"]
  [PRIVILEGES [user-priv-list]];

Changes a user. If the NAME clause is present, it specifies a new user name. If the PASSWORD clause is present, it specifies the new password or removes the password if empty (""). If the PRIVILEGES clause is specified, it defines the privileges associated with the user; if empty, all privileges will be reset.

Notes:

• The UADMIN privilege is required to maintain database users.

Example:

CHANGE USER "mike" PASSWORD "sesame";

DROP USER

DROP USER "user1" [,"user2"];

Removes the user and all references.

Notes:

• The UADMIN privilege is required to maintain database users.

Example:

DROP USER "mike","marc";

CHANGE PASSWORD

CHANGE PASSWORD [FOR USER "user"] TO "password";

Change the password (or remove if empty). If the user is not specified, it defaults to the currently logged in user.

Notes:

• The UADMIN privilege is required to maintain database users.

Examples:

CHANGE PASSWORD FOR USER "mike" TO "sesame";

CONNECT AS "mike" PASSWORD "secret";
CHANGE PASSWORD TO "sesame";

Legacy syntax (deprecated, do not use in new scripts):

ALTER PASSWORD FOR USER "user" TO "password";

RENAME DATABASE

RENAME DATABASE ["Name"] TO "NewName";

Renames a database. If the previous name is not specified the current database is used.

Notes:

• The current implementation allows renaming while the database is in use. The change will become effective when the database is no longer used, i.e. the last reference to it has been closed.

Example:

RENAME DATABASE "db" TO "db2";

DATABASE

DATABASE "database";

Specifies the database context.

Notes:

• The Eloquence database server can hold more than one database.
• For database specific statements, the database on which they should operate must be specified.
• If the script restructures the database, only one DATABASE statement may be present per script.

Example:

DATABASE "db";

CREATE GROUP

CREATE GROUP "group" 
  [PRIVILEGES [group-priv-list]];

Create a new group in the current database. If the PRIVILEGES clause is specified, it defines the privileges associated with the group (could be empty).

Group capabilities which are not data set specific are specified by group privileges. Multiple privileges can be specified, separated by a comma.

The following group privileges are available:

DADMIN

Group members have administration privileges for this database (this is implied for users which have the DBA privilege).

DBPRIV

Group members are allowed to assign database specific privileges.

Notes:

• The Eloquence database uses groups to manage database specific privileges.
• A database context must be defined before managing database groups.
• The DBPRIV privilege is required to maintain database groups.
• When a user is associated with a group, it will gain all capabilities granted to the group.
• A user can be a member of up to 8 groups per database.

Example:

CREATE GROUP "users";

CHANGE GROUP

CHANGE GROUP "group"
  [NAME "name"]
  [PRIVILEGES [group-priv-list]];

Changes a group. If the NAME clause is present, it specifies the new group name. If the PRIVILEGES clause is specified, it defines the privileges associated with the user (could be empty to reset all privileges).

Notes:

• A database context must be defined before managing database groups.
• The DBPRIV privilege is required to maintain database groups.

Example:

CHANGE GROUP "uers" NAME "users";

DROP GROUP

DROP GROUP "group" [,"group" ...];

Removes the group and all references.

Notes:

• A database context must be defined before managing database groups.
• The DBPRIV privilege is required to maintain database groups.

Example:

DROP GROUP "users";

GRANT and REVOKE user privileges

GRANT user-priv-list 
  TO {ALL|"user" [,"user" ...]};

REVOKE user-priv-list 
  FROM {ALL|"user" [,"user" ...]};

Grant or revoke privileges to or from a user.

Multiple privileges can be specified, separated by a comma. The following user privileges are available:

DBA

The user has server administration privileges.

CONNECT

The user is allowed to connect the server. This is implied if a user has the DBA privilege.

UADMIN

The user is allowed to administrate user accounts.

OPERATOR

The user has operator capabilities.

Either a list of user names, separated by comma, or the keyword ALL or PUBLIC can be specified to indicate all users.

Notes:

• The UADMIN privilege is required to maintain database users.
• The CONNECT privilege is granted by default if the PRIVILEGES clause is not present.

Example:

REVOKE CONNECT FROM ALL;
GRANT CONNECT TO "mike","marc";

This will disallow all users except "mike" and "marc" to connect the database server.

Legacy syntax (deprecated, do not use in new scripts):

GRANT user-priv-list 
  TO {PUBLIC|"user" [,"user" ...]};

REVOKE user-priv-list 
  FROM {PUBLIC|"user" [,"user" ...]};

GRANT and REVOKE group privileges

GRANT group-priv-list 
  TO {ALL|"group" [,"group" ...]};

REVOKE group-priv-list 
  FROM {ALL|"group" [,"group" ...]};

Grant or revoke privileges to or from a group.

Group capabilities which are not data set specific are specified by group privileges. Multiple privileges can be specified, separated by a comma.

The following group privileges are available:

DADMIN

Group members have administration privileges for this database (this is implied for users which have the DBA privilege).

DBPRIV

Group members are allowed to assign database specific privileges.

Either a list of group names, separated by comma, or the keyword ALL can be specified to indicate all groups (user "dba" and "public" are excluded).

Notes:

• A database context must be defined before managing database groups.
• The DBPRIV privilege is required to maintain database groups.
• When a user is associated with a group, it will gain all capabilities granted to the group.

Example:

REVOKE DBPRIV FROM "users";
GRANT DBPRIV,DADMIN TO "dba";

Legacy syntax (deprecated, do not use in new scripts):

GRANT group-priv-list 
  TO {PUBLIC|"group" [,"group" ...]};

REVOKE group-priv-list 
  FROM {PUBLIC|"group" [,"group" ...]};

GRANT and REVOKE member privileges

GRANT "group" [,"group" ...] 
  TO {ALL|"user" [,"user" ...]};

REVOKE "group" [,"group" ...] 
  FROM {ALL|"user" [,"user" ...]};

Grant or revoke privileges assigned to a group to or from a user.

A list of group names can be specified, separated by comma.

A list of user names can be specified, separated by comma, or the keyword ALL or PUBLIC can be used to indicate all users.

Notes:

• A database context must be defined before managing database groups.
• The DBPRIV privilege is required to maintain database groups.
• When a user is associated with a group, it will gain all capabilities granted to the group.
• A user can be a member of up to 8 groups per database.

Example:

GRANT "users" TO "mike","marc";

This will make the users "mike" and "marc" members of the group "users".

Legacy syntax (deprecated, do not use in new scripts):

GRANT "group" [,"group" ...] 
  TO {PUBLIC|"user" [,"user" ...]};

REVOKE "group" [,"group" ...] 
  FROM {PUBLIC|"user" [,"user" ...]};

GRANT and REVOKE data set privileges

GRANT {ALL PRIVILEGES|dataset-priv-list}
  ON {ALL|dataset-list} 
  TO {ALL|"group" [,"group" ...]};

REVOKE {ALL PRIVILEGES|dataset-priv-list}
  ON {ALL|dataset-list} 
  FROM {ALL|"group" [,"group" ...]};

Grant or revoke data set access privileges to or from a group. Access privileges include READ, WRITE and ERASE. Multiple privileges can be specified, separated by a comma.

If ALL is specified as the data set name, this will apply to all data sets. If PUBLIC or ALL is specified as the group name, this will apply to all groups.

Notes:

• A database context must be defined before managing data set privileges.
• The DBPRIV privilege is required to maintain data set privileges.

Example:

REVOKE ALL PRIVILEGES ON ALL FROM "users";
GRANT WRITE ON "CUSTOMERS","PARTS" TO "users";
GRANT READ ON ALL TO "users";
GRANT ERASE ON "HISTORY" TO "priv";

This will provide read access on all data sets to all members of the group "users" and write access to the data sets CUSTOMERS and PARTS. Members of the group "priv" are allowed to erase the data set HISTORY.

Legacy syntax (deprecated, do not use in new scripts):

GRANT {ALL PRIVILEGES|dataset-priv-list}
  ON {ALL|dataset-list} 
  TO {PUBLIC|"group" [,"group" ...]};

REVOKE {ALL PRIVILEGES|dataset-priv-list}
  ON {ALL|dataset-list} 
  FROM {PUBLIC|"group" [,"group" ...]};

CREATE PROPERTY

CREATE PROPERTY "key" 
  VALUE "value"
  [FLAGS [property-flag-list]];

Create a database property. The VALUE clause specifies the associated value. The FLAGS clause can be used to specify additional flags for this property, including SYSTEM and IMAGE3K.

Notes:

• A database context must be defined before managing database properties.
• The DADMIN privilege is required to maintain database properties.

CHANGE PROPERTY

CHANGE PROPERTY "key"
  VALUE "value"
  [FLAGS [property-flag-list]];

Change a database property. The VALUE clause specifies the new associated value. The FLAGS clause can be used to modify flags (or remove all flags if empty) for this property.

Notes:

• A database context must be defined before managing database properties.
• The DADMIN privilege is required to maintain database properties.

DROP PROPERTY

DROP PROPERTY "key" [,"key" ...];

Delete the database property. A comma separated list of property keys can be specified.

Notes:

• A database context must be defined before managing database properties.
• The DADMIN privilege is required to maintain database properties.

TurboIMAGE compatible database properties

SET LANGUAGE = n;

Set the TurboIMAGE LANGUAGE property to n where n is a numerical value.

Notes:

• The value of the LANGUAGE property is returned by DBINFO mode 901.
• This statement sets the HP3K_LANG database property.

SET CIUPDATE = mode;

Set the TurboIMAGE CIUPDATE property to mode where mode is DISALLOWED, ALLOWED or ON.
The default value is ALLOWED.

Notes:

• The value of the CIUPDATE property is used by DBUPDATE and returned by DBINFO mode 502.
• This statement sets the HP3K_CIUPDATE database property.

SET BTREEMODE1 = mode;

Set the TurboIMAGE BTREEMODE1 property to mode where mode is ON or OFF.
The default value is ON.

Notes:

• The value of the BTREEMODE1 property is used by DBFIND and returned by DBINFO mode 113.
• This statement sets the HP3K_BTREEMODE1 database property.

SET WILDCARD = "c";

Set the TurboIMAGE WILDCARD property to c where c is a valid ASCII character.
The default value is @.

Notes:

• The value of the WILDCARD property is used by DBFIND and returned by DBINFO mode 113.
• This statement sets the HP3K_WILDCARD database property.

SET SUBSYSTEM = mode;

Set the TurboIMAGE SUBSYSTEM property to mode where mode is NONE, READ or RW.
The default value is RW.

Notes:

• The value of the SUBSYSTEM property is returned by DBINFO mode 501.
• This statement sets the HP3K_SUBSYSTEM database property.

CHANGE DATABASE

CHANGE DATABASE
  [NAME NewDatabaseName ;]
  [LANGUAGE "collseq" ;]

CHANGE DATABASE {
   ...
}

Change database settings. If the NAME clause is present, is specifies the new database name. If the LANGUAGE clause is present, it specifies the new default collating sequence for the database. Multiple item definitions can be enclosed in curly braces ({}).

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• Chaning the default collating sequence changes all indexes using the previous default collating sequence. This will case rebuild of all indexes for master sets.

Examples:

CHANGE DATABASE
 LANGUAGE "german@nofold";

CREATE ITEM

CREATE ITEM
   Name, [count] type [(format)] [{BEFORE|AFTER} ItemName];

CREATE ITEM {
   ...
}

Create new item. Multiple item definitions can be enclosed in curly braces ({}).

The BEFORE or AFTER clause may be used to specify the position for the new item. By default, new items are appended to the existing list of items.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.

Examples:

CREATE ITEM
 matchcode,x10;

CREATE ITEM {
 description1,X20;
 description2,X20;
}

CHANGE ITEM

CHANGE ITEM ItemName
  [NAME NewItemName ;]
  [TYPE [count] type [(format)] ;]
  [TYPE (format) ;]
  [FORMAT format ;]
  [ORDER {BEFORE|AFTER} ItemName ;]
  [[IN SetName] SET ENCRYPTED ;]
  [[IN SetName] UNSET ENCRYPTED ;]

CHANGE ITEM ItemName {
  ...
}

Change item definition. If the NAME clause is present, is specifies the new item name. If the TYPE clause is present, it specifies the new item type or the format number. Multiple item definitions can be enclosed in curly braces ({}).
The SET or UNSET ENCRYPTED clause is used to designate an item as encrypted or unencrypted. If a set name is specified, only the given set is affected. Otherwise all data sets holding the item are affected.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• The search item of a manual master set can not be modified in a way which could lead to duplicates. For example, shortening a search item in a manual master from X20 to X10 is not allowed.

Examples:

CHANGE ITEM matchcod
 NAME matchcode;

CHANGE ITEM descr1 {
 NAME description1;
 TYPE x20;
}

DROP ITEM

DROP ITEM ItemName [,ItemName ...];

Delete a list of item definitions.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• Deleting an item will fail if it is still used in a data set or index item.

Example:

DROP ITEM matchcode;

ORDER ITEMS

ORDER ITEMS BY NAME;

The ORDER ITEMS BY NAME statement may be used to order items alphabetically.

Notes:

• Ordering existing items may require an updated server version.
• The DADMIN privilege is required to modify a database schema.

CREATE IITEM

CREATE IITEM 
  [iItemName [(options)] = ItemName[:[start.]length] [, ...]] ;

CREATE IITEM {
  [iItemName [(options)] = ItemName[:[start.]length] [, ...]] ;
  ...
}

Create new index item. Multiple item definitions can be enclosed in curly braces ({}).

The option EMPTY specifies to not create index entries for default item values.

The option CI specifies to create the index as case-insensitive index.

The start and length options for an item may specify a starting position into an item and the size of the index segment different from the item.
The syntax variant item[length] or item[start:length] may be used to specify the starting position or length.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• New index items are appended to the existing list of index items.

Examples:

CREATE IITEM
 imatchcode = matchcode;

CREATE IITEM {
 imatchcode = matchcode;
 idescription = description1:10, description2:10;
}

CREATE IITEM
 match(EMPTY,CI) = name:1.8;

CHANGE IITEM

CHANGE IITEM iItemName
  [NAME NewiItemName]
  [TYPE ItemName[:[start.]length] [, ...]];
  [FLAGS [iitem_options]];

CHANGE IITEM iItemName {
  ...
}

Change index item definition. Multiple item definitions can be enclosed in curly braces ({}). If the NAME clause is specified, the index item name is changed. If the TYPE clause is present, the index item definition is replaced. The FLAGS clause allows to set/reset iitem options. Currently, only the EMPTY and CI options are valid. If iitem_options are omitted the options are reset for this index.

Note:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• The order of index items cannot be changed.
• Any indexes using the specified index item will be rebuilt.

Example:

CHANGE IITEM match {
  TYPE name:2.8;
  FLAGS CI;
}

DROP IITEM

DROP IITEM iItemName [, ...];

Delete a list of index item definitions.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• Deleting an index item will fail if it is still used in a data set.

Example:

DROP IITEM imatchcode;

CREATE SET

CREATE SET SetName, SetType {
  [ADD ITEM ... ;]
  [ADD INDEX ... ;]
  [ADD PATH ... ;]
  [SET|UNSET ENCRYPTED;]
  [CHANGE ITEM ... SET|UNSET ENCRYPTED;]
  [SET|UNSET INDEXED;]
}

Create new data set. SetType is one of AUTOMATIC (A), MANUAL (M) or DETAIL (D). Specifying curly braces ({}) allows multiple change clauses.

Please see below for the syntax of the ADD ITEM, ADD INDEX, ADD PATH, SET|UNSET ENCRYPTED, CHANGE ITEM and SET|UNSET INDEXED clauses.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• New data sets are appended to the existing list of data sets. The exception are master sets which are inserted before the first path.

Example:

CREATE SET customers, M {
 ADD ITEM custno(*),matchcode,name1,name2;
 ADD INDEX imatchcode;
}

CHANGE SET

CHANGE SET SetName {
  [NAME ... ;]
  [TYPE ... ;]
  [ADD ITEM ... ;]
  [DELETE ITEM ... ;]
  [ADD INDEX ... ;]
  [DELETE INDEX ... ;]
  [ADD PATH ... ;]
  [CHANGE PATH ...;]
  [DELETE PATH ... ;]
  [SET|UNSET ENCRYPTED;]
  [CHANGE ITEM ... SET|UNSET ENCRYPTED;]
  [SET|UNSET INDEXED;]

}

Change data set definition. Specifying curly braces ({}) allows multiple change clauses.

Please see below for the syntax of the NAME, TYPE, ADD ITEM, DELETE ITEM, ADD INDEX, DELETE INDEX, ADD PATH, CHANGE PATH, DELETE PATH and SET|UNSET ENCRYPTED, CHANGE ITEM and SET|UNSET INDEXED clauses.

Notes:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• The type of a data set can currently not be changed.
• The order of data sets cannot be changed.

Examples:

CHANGE SET custmers
 NAME customers;

CHANGE SET customers {
 ADD ITEM matchcode;
 ADD INDEX imatchcode;
}

DROP SET

DROP SET SetName [, ...];

Delete the specified data sets from the database. Any paths or indexes are deleted automatically.

Note:

• A database context must be defined before modifying a database schema.
• The DADMIN privilege is required to modify a database schema.
• If a path is deleted the related data set must be reorganized.

Example:

DROP SET customers,parts;

CREATE or CHANGE SET clauses

• NAME
• TYPE
• ADD ITEM
• DELETE ITEM
• ADD INDEX
• DELETE INDEX
• ADD PATH
• CHANGE PATH
• DELETE PATH
• SET|UNSET ENCRYPTED
• CHANGE ITEM ... SET|UNSET ENCRYPTED
• SET|UNSET INDEXED

The NAME clause is used to rename a data set.

Syntax:

NAME NewSetName;

Change data set name to NewSetName.

Example:

CHANGE SET custmrs
 NAME customers;

The TYPE clause is used to change a data set type.

Syntax:

TYPE M[ANUAL]|A[UTOMATIC];

Change data set type to specified type.

Example:

CHANGE SET auto
 TYPE manual;

Notes:

• It is supported to switch between manual master and automatic master type.

The ADD ITEM clause is used to add an item to a data set.

Syntax:

ADD ITEM 
  [BEFORE|AFTER ItemName]
  ItemName[([!]MasterSetName[(SortItemName)])] [, ...];

ADD ITEM 
  [BEFORE|AFTER ItemName]
  ItemName[(*)] [, ...];

Add one or multiple items to the data set. For a detail set a path can be specified as well.
When creating a master set, the search item can optionally be specified with a (*) if it is different from the first item.

The BEFORE or AFTER clause may be used to specify the position for the new item. By default, new items are appended to the existing list of items.

Notes:

• The dataset must be reorganized.
• If a path is specified any related set must be reorganized.

Examples:

CHANGE SET orders
 ADD ITEM orderno(!id(orderdate));

CREATE SET customers
 ADD ITEM custname, custno(*);

CHANGE SET customers
 ADD ITEM AFTER custno
  name, matchcode;

The DELETE ITEM clause is used to delete an item from a data set.

Syntax:

DELETE ITEM ItemName [, ...];

Delete one or multiple items from data set.

Note:

• The dataset must be reorganized.

Example:

CHANGE SET customers
 DELETE ITEM comment;

The ADD INDEX clause is used to add an index to a data set.

Syntax:

ADD INDEX iItemName [/"collseq"];

Add index to data set. If a collating sequence is specified, it is used to define the ordering of any string segment in the index.

Note:

• The dataset must be reorganized if the first index is added.

Example:

CHANGE SET customers
 ADD INDEX imatchcode / "german";

The DELETE INDEX clause is used to delete an index from a data set.

Syntax:

DELETE INDEX iItemName;

Delete index from data set.

Note:

• The dataset must be reorganized if the last index is deleted.

Example:

CHANGE SET customers
 DELETE INDEX imatchcode;

The ADD PATH clause is used to add a new path to a data set.

Syntax:

ADD PATH ItemName([!]SetName[(SortItemName)]);

Add path to detail data set, optionally specifying a sort item and primary path designator.

If the primary path designator (!) is specified, the new path is used as the primary path. If a sort item is specified the chain is ordered by this item and the subsequent items in the entry.

Notes:

• This requires to reorganize both the detail set and the related master set.
• To change a path, you may either use the CHANGE PATH clause or delete the path first (using DELETE PATH) and add it again with the new specification.

Examples:

CHANGE SET orders
 ADD PATH orderno(!id(orderdate));

CHANGE SET orders {
 DELETE PATH orderno(id);
 ADD PATH orderno(!id(orderdate));
}

The CHANGE PATH clause is used to change an existing path.

Syntax:

CHANGE PATH ItemName([!]SetName[(SortItemName)]);

Change path in detail data set on the specified search item. Optionally specifying a sort item and primary path designator.

If the primary path designator (!) is specified, the new path is used as the primary path. If a sort item is specified the chain is ordered by this item and the subsequent items in the entry.

Notes:

• Specifying a different master set or primary path designator is not supported. Only the search item may be changed (or removed).
  This requires to reorganize both the detail set and the related master set.

Example:

CHANGE SET orders {
 ADD PATH orderno(!id);
 CHANGE PATH orderno(id(orderdate));
}

The DELETE PATH clause is used to delete a path from a data set.

Syntax:

DELETE PATH ItemName[(SetName)];

Delete path from detail data set. Can only be used with a detail set.
The optional SetName must refer to a master set, if specified.

Note:

• This requires to reorganize both the detail set and the related master set.

Example:

CHANGE SET orders
 DELETE PATH orderno(id);

The SET ENCRYPTED clause is used to designate all fields in a set as encrypted.

Syntax:

SET ENCRYPTED;

Notes:

• Encryption status is maintained for each field (item in a data set). When adding a field to a data set, it defaults to unencrypted.

The UNSET ENCRYPTED clause is used to designate all fields in a set as unencrypted.

Syntax:

UNSET ENCRYPTED;

The CHANGE ITEM ... SET|UNSET ENCRYPTED clause is used to designate selected fields in a set as (un)encrypted.

Syntax:

CHANGE ITEM ItemName SET ENCRYPTED;
CHANGE ITEM ItemName UNSET ENCRYPTED;

Designate specified item as encrypted or unencrypted.

Notes:

• Changing the encryption requires the data set and any indexes the item is used in.

The SET and UNSET clauses are used to change the INDEXED flag for a master set.

Syntax:

SET INDEXED;
UNSET INDEXED;

This is used by the TurboIMAGE compatibility option to determine if BTREEMODE1 applies to a master data set.

Example:

CHANGE SET customers
 SET INDEXED;

UPGRADE DATABASE

Syntax:

UPGRADE DATABASE;

This is a pre-requisite to use item masking functions, database encryption, or FTS indexing.

Notes:

• The UPDGRADE DATABASE statement must be executed after a DATABASE statement and before any structural changes are defined.

CREATE ENCRYPTION KEY

Syntax:

CREATE ENCRYPTION KEY [ ("type") ] USING [MASTER KEY] "master key";

Type specifies the data encryption algorithm associated with this key. The default encryption algorithm is AES.

Supported encryption types are:

• AES - AES 128 bit
• AES128 - AES 128 bit
• AES256 - AES 256 bit

The master key argument specifies the checksum of the master key that is used to encrypt the data encryption key. The master key must have been submitted to the server before.
The keywords "MASTER KEY" are optional and may be omitted.

Example:

CREATE ENCRYPTION KEY("AES256")
  USING MASTER KEY "330d6de453136a93fd99c1638fd645fd";

This creates a new data encryption key with the encryption algorithm AES256 and assigns it to a master key.

CHANGE ALL ENCRYPTION KEYS

Syntax:

CHANGE ALL ENCRYPTION KEYS USING [MASTER KEY] "master key";

The master key argument specifies the checksum of the master key that is used to encrypt the data encryption key. Both the current and the new master key must have been submitted to the server before.
The keywords "MASTER KEY" are optional and may be omitted.

DELETE ALL ENCRYPTION KEYS

Syntax:

DELETE ALL ENCRYPTION KEYS;

Notes:

• Encryption keys may only deleted when no item in the database is encrypted. When encryption is used, deleting data encryption keys is not possible. This statement is intended to be used to remove encryption (and any data encryption keys) from a database. To retire a data encryption key "dbctl dbkeyupdate" is used.
• Deleting data encryption keys must be performed in a separate dbutil session from removing encryption from an item (and the resulting restructuring).

SET / UNSET ITEM ACCESS

Syntax:

SET ITEM ACCESS ON ItemName [ IN SetName ]
  TO MASKED [ MaskFunction ] [,NOUPDATE] [ FOR GroupList ] ;

SET ITEM ACCESS ON ItemName [ IN SetName ]
  TO ALLOWED [,NOUPDATE] [ FOR GroupList ] ;

UNSET ITEM ACCESS ON ItemName [ IN SetName ]
  [ FOR { GroupList | ALL } ] ;

ItemName specifies the item name (optionally enclosed in quotes). SetName specifies the set name (optionally enclosed in quotes).

MaskFunction is one of

"cover(x,m,n)"

m >=0 and n >= 0 and x = some character (e.g. *)

"part(x,m,n)"

m >=0 and n >= 0 and x = some character

"left(x,n)"

x = some character

"right(x,n)"

x = some character

"set(text)"

text = some string (e.g. -is set-)

If a SetName is specified, the item access rule is specific to this data set. If not specified, it applies as a default to all data sets that use this item.

DELETE ALL ITEM ACCESS;

Examples:

SET ITEM ACCESS ON credit-card-no
  TO MASKED "set(available)", NOUPDATE;
SET ITEM ACCESS ON credit-card-no
  TO MASKED "cover(*,0,4)" FOR "agents";
SET ITEM ACCESS ON credit-card-no
  TO ALLOWED FOR "billing";

Also see the document on item masking for details.

FTS parser config

An FTS parser config defines how the FTS indexing subsystem parses text fields for splitting them into separate words, multi-part words or numbers.

Syntax:

CREATE FTS PARSER CONFIG id
  {NSEP|CSEP|MULTI|DEC} = "string" [, ...];

CHANGE FTS PARSER CONFIG id
  {NSEP|CSEP|MULTI|DEC} = "string" [, ...];

DELETE FTS PARSER CONFIG id;

Id is an integer number that identifies the parser configuration. It may be prepended with the letter "P". The parser configuration P0 (or 0) specifies the default parser configuration.

The available parser setting are

• NSEP - separator exception list
• CSEP - retained separator list
• MULTI - multi separators
• DEC - decimal point and grouping

Examples:

change fts parser config P0  nsep = "_";

create fts parser config P1  nsep = "_", multi = "-/+";

Also see the documents on FTS indexes for details.

FTS exclusion list

An FTS exclusion list defines keywords that the FTS indexing subsystem ignores (i.e. does not add to the dictionary) when parsing text fields for splitting them into separate words, multi-part words or numbers.

Syntax:

{CREATE|CHANGE} FTS EXCLUSION LIST id
 FROM "file name";

{CREATE|CHANGE} FTS EXCLUSION LIST id
 {ADD|DELETE} string [,...] [...];

DELETE FTS EXCLUSION LIST id;

Id is an integer number that identifies the exclusion list. It may be prepended with the letter "E". The exclusion list E0 (or 0) specifies the default exclusion list.

An exclude file may be referenced for being uploaded to the database catalog. As an alternative, individual words may be added to or deleted from an exclusion list.

Examples:

create fts exclusion list E0
  from "my-stop-words.txt";

change fts exclusion list E1
  add "this", "that"
  delete "another";

Also see the documents on FTS indexes for details.

FTS index field

Syntax:

CREATE FTS INDEX [field_name] in set_name
 ON item[:[start.]length] [, ...]
 [option OP [,...];

CHANGE FTS INDEX field_name in set_name
 [NAME field_name]
 [ON item[:[start.]length] [, ...]]

DELETE FTS INDEX field_name in set_name;

set_name specifies the name of a data set.

field_name may be used to specify an alias name for the FTS field. For simple FTS fields (using a single item), the item name is used to identify the FTS field if no alias name was specified. A field_name must be used for any composite FTS fields (fields concatenated from more than a single item).

The ON clause is used to specify one or more items to be indexed in this field. Optionally, a starting position in the item and a different item size may be specified.
The syntax variant item[length] or item[start:length] may be used to specify the starting position or length.

Available FTS field options are

• NP - no parse
• NT - no translate ("uppercase")
• NE - no exclusion
• NM - no multi
• SX - SOUNDEX support
• MIN=# - min word length (default 2)
• MAX=# - max word length (default 12)
• EXCL=id|E# - exclusion list
• PCFG=id|P# - parser config
• GROUP=id|G# - field group
• INDX=id|I# - field index pool
• LINK=mset - specify master set
• PATH=item - specify chain for master
• AGG - keywords are aggregated by master

Examples:

create fts index in CUSTOMERS on CITY option GROUP=1;
create fts index in CUSTOMERS on ZIP  option GROUP=1;

create fts index in ORDER-DETAIL
 on LINE-COMMENT option GROUP=1,path=ORDER-NO,agg;

Also see the documents on FTS indexes for details.

DELETE ALL FTS

Syntax:

DELETE ALL FTS;

This deletes any FTS fields, FTS parser configs and FTS exclusion lists.

Also see the documents on FTS indexes for details.