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 a list of supplied collations, see “Supplied and recommended collations”.

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”.
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
```
Edit the custom collation file.

Open the collation file (in this case mycustomcol.col) in a text editor.
Change the collation label.
```
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 Sybase IQ 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.
Change the collation definition.

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

For information on the collation file contents and format, see “Collation internals”.
Convert the file to a SQL script.

You do this using the dbcollat command-line utility using the -d switch.

For example, the following command line creates the mycustom.SQL file from the mycustomcol.col collation file:
```
dbcollat -d mycustomcol.col mycustom.SQL
```
Add the SQL script to the script in your installation.

The script used when creating databases is held in the scripts subdirectory of your Sybase IQ installation directory. Append the contents of mycustom.SQL to end of custom.SQL.

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

Stop and restart the database server in order for it to recognize the new collations and insert them into system tables SYSCOLLATION and SYSCOLLATIONMAPPINGS.