ASA Database Administration Guide
  International Languages and Character Sets
    International language and character set tasks

Creating a custom collation

If none of the supplied collations meet your needs, you can modify a supplied collation to create a custom collation. You can then use this custom collation when creating a database.

For more information about supplied collations, see Choosing collations.

To create a custom collation

1. Decide on a starting collation    You should choose a collation as close as possible to the one you want to create as a starting point for your custom collation.

   For a listing of recommended collations, see Understanding collations. Alternatively, run dbinit with the -l (lower case L) option:

   dbinit -l

2. Create a custom collation file    You do this using the Collation utility. The output is a collation file.

   For example, the following statement extracts the 1252LATIN1 collation into a file named mycustomcol.col:

   dbcollat -z 1252LATIN1 mycustomcol.col

3. Edit the custom collation file    Open the collation file (in this case mycustomcol.col) in a text editor.

4. Change the collation label    Make the changes you wish in the custom collation file to define the collation label. For example, if you wanted to make a custom version of the 1252LATIN1 collation, you could change the Collation line from:

   Collation 1252LATIN1 (Code Page 1252, Windows Latin 1, Western) (cp1252) (dictionary_iso_1) (nocase_iso_1) (Cp1252)

   to

   Collation MyOrdering (Code Page 1252, My Company Ordering) (cp1252) (dictionary_iso_1) (nocase_iso_1) (Cp1252)

   The other entries on this line relate the Adaptive Server Anywhere collation label to the names that Java and the Sybase TDS interface give to the same collation information. If you are not using these interfaces you do not need to alter these entries.

   The Collation line takes the following form:

   Collation collation_label (collation_name) (ase_charset) (ase_so_sensitive) (ase_so_insensitive) (java_charset)

   where character set label (ase_charset) and the two sort-order labels (ase_so_sensitive and ase_so_insensitive) state which Open Client character set and sort order is the closest to the current collation. The java_charset label is the closest character set known to Java.

   You should edit this line to provide a new label. The label you need to change is the collation_label: in the example in step two, it is 1252LATIN1.

5. Change the collation definition    Make the changes you wish in the custom collation file to define your new collation.

   For more information about the collation file contents and format, see Collation internals.

6. Convert the file to a SQL script    You do this using the dbcollat utility using the -d option.

   For example, the following command creates the mycustom.SQL file from the mycustomcol.col collation file:

   dbcollat -d mycustomcol.col mycustom.SQL

7. Add the SQL script to the script in your installation    The script used when creating databases is held in the scripts subdirectory of your Adaptive Server Anywhere installation directory. Append the contents of mycustom.SQL to the end of custom.SQL.

   The new collation is now in place, and can be used when creating a database.