CHMT requires a text file containing a mapping between collations used for conversion.

chmt --help // help message 

Executing CHMT:

chmt --datadir=/absolute/path/to/data/directory [other parameters]

The logs detail information about every considered table found in a specified datadir. Each conversion finishes with [NOT NEEDED], [PASS] or [FAILED] status.

Each conversion directive is stored in one line of file:

collation_from_name;collation_from_id;collation_to_name;collation_to_id 

For example:

big5_chinese_ci;1;binary;63 

where both fields containing names are only informative (all conversions will be done using only IDs).

Example collations-conversion-file (with conversion directives described above) can be obtained by running the following SQL:

use information_schema; 
select a.collation_name a_n, a.id a_id, b.collation_name b_n, b.id b_id from information_schema.collations a, information_schema.collations b, character_sets c where substr(a.collation_name, 1,  locate('_',a.collation_name)-1)=c.character_set_name and substr(a.collation_name, 1, locate('_',a.collation_name)) = substr(b.collation_name, 1, locate('_',b.collation_name)) and b.collation_name like '%bin' and c.maxlen=1 UNION select a.collation_name a_n, a.id a_id, 'binary' b_n, 63 b_id from information_schema.collations a, character_sets c where ((substr(a.collation_name, 1,  locate('_',a.collation_name)-1)=c.character_set_name) or (locate('_',a.collation_name)=0)) and (c.maxlen>1 or c.character_set_name='binary') order by a_id into outfile '/some/path/my_collations.txt' fields terminated by ';';

The Hyperstage DomainExpert improves data compression and the performance of import, queries, and export. The DomainExpert allows you to define the composition of data, particularly columns. The database then uses this information to optimize the storage of the data and to reduce query processing time.

DomainExpert metadata is maintained in the system tables of the database sys_hyperstage and should be managed only with the use of stored procedures.

CREATE_RULE(id, rule, comment)
UPDATE_RULE(id, rule)
CHANGE_RULE_COMMENT(id, comment)
DELETE_RULE(id)

CALL SYS_HYPERSTAGE.CREATE_RULE('EMAIL', '%s@%s', 'Rule for email addresses');

SELECT * FROM SYS_HYPERSTAGE.DECOMPOSITION_DICTIONARY;

The stored procedure SET_DECOMPOSITION_RULE(database, table, column, id) from the database sys_hyperstage supports the assignment of rules to particular columns from the Hyperstage tables. For example, to apply the predefined IPv4 rule to column IP in the table CONNECTION from the database NETWORK, run the following command:

CALL SYS_HYPERSTAGE.SET_DECOMPOSITION_RULE('NETWORK', 'CONNECTION', 'IP', 'IPv4');

The decomposition rules can be applied only to columns of string types that are not lookup columns:

• CHAR
• VARCHAR
• BINARY
• VARBINARY
• TEXT
• TINYTEXT

Assigning a rule to a column of another type or to a lookup column is ignored.

You cannot set multiple rules on the same column. If the SET_DECOMPOSITION_RULE procedure is called for a column with an already assigned rule, the previous rule is replaced with the new rule.

To see the current decomposition rules for a particular table, use the SHOW_DECOMPOSITION procedure. For example:

CALL SYS_HYPERSTAGE.SHOW_DECOMPOSITION('NETWORK', 'CONNECTION');

If a rule is assigned to a column, you cannot change or delete the rule from the DECOMPOSITION_DICTIONARY system table.

After decomposition rules are assigned to columns, the rules are automatically applied to any new data coming to the tables containing these rules when using the following standard DML commands:

• LOAD DATA
• INSERT
• UPDATE

If a rule is assigned to a column, instead of storing whole values, each value inserted into the column is decomposed into the parts matching the subsequent occurrences of %s and %d in the rule and the parts are compressed and stored in separate subcollections. Each subcollection corresponds to one occurrence of %s or %d in the rule.

A value inserted into a column with a decomposition defined does not have to match the rule. Such non-matching values are inserted into a separate subcollection. This subcollection of outliers is compressed and stored independently of other subcollections.

You can obtain the accuracy of decomposition rules by setting the ControlMessages parameter in the brighthouse.ini file to 2 (or higher):

ControlMessages = 2

If the parameter is set on each COMMIT for each column, Hyperstage reports the number of outliers among the committed values (from INSERTs and LOADs). For example:

2011-05-25 16:59:03 Decomposition of ./network/connection.ip left 15 outliers.

Hyperstage also reports the change in the number of outliers for the updated values (from UPDATEs), for example:

2011-05-25 16:59:46 The number of outliers increased by 2 after update(-s)
on network.connection.ip
2011-05-25 17:00:03 The number of outliers reduced by 3 after update(-s) 
on network.connection.ip

Note: Applying a decomposition rule DOES NOT always result in better compression ratio and time. A decomposition rule may result in a worse compression ratio or load and slower queries. To ensure decomposition improves performance. You can compare load time, compression ratio, and query time when loading the same data to a table with a decomposition rule defined and to a table without decomposition.

A rule for a column can be changed or deleted during the life of the table using the following stored procedures:

SET_DECOMPOSITION_RULE(database, table, column, id)
DELETE_DECOMPOSITION_RULE(database, table, column)

The change applies only to new data. The old data remains decomposed with the previously used rules. If the rule for a column is deleted, new values are stored without decomposition.

If a value is updated to a new value with an UPDATE command, then for the new value Hyperstage uses the original rule used to decompose the old value. The currently assigned rules are not used for UPDATEs.