SAND CDBMS SQL Reference Guide
ALTER DOMAIN

The ALTER DOMAIN statement can be used to attach a text analytics dictionary to a BLOB domain, or remove one.

Required Privileges

To alter a domain, the user authorization must possess DBA privileges.

Syntax

BLOB domain name
Is the name of the BLOB domain that will have either a dictionary attached or removed.

dictionary domain name
Is the name of the previously-defined VARCHAR(4056) domain that will be used as the dictionary.

SYSTEM."_SYNDICT"
Specifying this system dictionary with a built-in thesaurus (instead of the user-defined dictionary domain name) enables concept (synonym) searches.

ENGLISH_STEM
Specifying this optional argument enables stem searches.

Description

Before an ALTER DOMAIN statement can be executed, an exclusive connection to the database must be secured. This is accomplished via the following command (DBA privileges are necessary):

SET TRANSACTION ISOLATION LEVEL EXCLUSIVE [ IMMEDIATE ];

The ALTER DOMAIN...ADD DICTIONARY command links a BLOB domain with an existing dictionary, which enables text analytics functionality on columns defined on that domain. One form of dictionary is simply a user-created SAND domain with the data type VARCHAR(4056) and a BINARY_UPCASE collation. When a dictionary of this type is associated with a BLOB domain, all of the standard text analytics options (not including concept/synonym searches) are available for any column defined on the BLOB domain. This standard text analytics is enabled using the ALTER DOMAIN...ADD DICTIONARY dictionary command.

Another type of dictionary is the pre-defined SYSTEM."_SYNDICT" dictionary with a built-in thesaurus, which is used for concept, or synonym, searches. When a text search of this type is executed, the search will look for all synonyms of the search word, as well as the search word itself. The special thesaurus/dictionary is attached to a BLOB domain via the ALTER DOMAIN...ADD DICTIONARY SYSTEM."_SYNDICT" command.

Note that a BLOB domain can be associated with only one type of dictionary at a time, whether it is a user-defined dictionary or the SYSTEM."_SYNDICT" dictionary.

To dissociate a dictionary from a BLOB domain, use the ALTER DOMAIN...DROP DICTIONARY command.

To change dictionaries for a BLOB domain, first execute ALTER DOMAIN...DROP DICTIONARY to remove the original dictionary, then execute ALTER DOMAIN...ADD DICTIONARY to link the new dictionary.

When an ALTER DOMAIN command concludes, it executes an implicit COMMIT, which ends the transaction initiated by the SET TRANSACTION ISOLATION LEVEL EXCLUSIVE command.

Examples

In the following set of SQL commands, a new, exclusive transaction is established at the beginning and a BLOB domain (dblob) is defined. Another domain (dict1) is created to act as the dictionary, and we then associate it with the dblob domain through the ALTER DOMAIN command (with the ENGLISH_STEM search option enabled). Note that the SET TRANSACTION statement is required before the ALTER DOMAIN command can be issued.

SET TRANSACTION ISOLATION LEVEL EXCLUSIVE IMMEDIATE;
CREATE DOMAIN s1.dblob BLOB;
CREATE DOMAIN s1.dict1 VARCHAR(4056) COLLATE BINARY_UPCASE;
ALTER DOMAIN s1.dblob ADD DICTIONARY s1.dict1 ENGLISH_STEM;

Once we have created a table with a column defined on the dblob domain and populated the column with values, we can perform several kinds of text analytics against this column. We cannot, however, execute a concept/synonym search. To do this type of search, we need to change dictionaries for the dblob domain, accomplished in the following manner:

SET TRANSACTION ISOLATION LEVEL EXCLUSIVE IMMEDIATE;
ALTER DOMAIN s1.dblob DROP DICTIONARY;
SET TRANSACTION ISOLATION LEVEL EXCLUSIVE IMMEDIATE;
ALTER DOMAIN s1.dblob ADD DICTIONARY SYSTEM."_SYNDICT";

After dropping the existing dictionary and linking the "_SYNDICT" dictionary to the dblob domain, we can now perform concept searches against columns defined on this domain. Note that a SET TRANSACTION command to gain an exclusive database connection is required before both ALTER DOMAIN statements, since each ALTER DOMAIN command concludes with an implicit COMMIT that ends the exclusive transaction.