User's Guide

Part VI. SQL Anywhere Reference

Chapter 39. SQL Anywhere Components

With the Collation utility, you can extract a collation (sorting sequence) from the SYS.SYSCOLLATION system table of a database into a file suitable for creating a database using a custom collation.

The file produced by the Collation utility can be modified and used with Sybase Central or the -z option of DBINIT to create a new database with a custom collation.

Ensure that the label is changed on the line that looks like:

     Collation label (name)

Otherwise, the collation cannot be used to create a database.

If you wish to create a custom collation but have not yet created a database, you should extract a collation from the sample database provided with SQL Anywhere.

For more information on custom collating sequences, see the chapter "Database Collations".

You can access the Collation utility in the following ways:

• From the ISQL Database Tools window.
• From the ISQL DBTOOL statement.
• From the system command line, using the DBCOLLAT command-line utility.

Extracting a collation in the ISQL Database Tools window

To use the Collation utility from the ISQL Database Tools window:

1. Select Database Tools from the Window menu.
2. Click Extract Collation from Database on the Tools list.
3. For a running database, enter a database name and server name (if more than one is running). For a database file, enter the filename (with path) and optionally a start line to specify command-line switches for the database engine or SQL Anywhere Client.
4. Click Extract, and select from the options displayed in the dialog.
5. Click OK to extract the collation file from the database.

Extracting a collation using the DBTOOL statement

Syntax

The syntax to access the Collation utility from the ISQL DBTOOL statement is as follows:

     DBTOOL UNLOAD COLLATION [ name ] TO filename

     ... USING connection-string

     ... [ EMPTY MAPPINGS ] [ HEX | HEXADECIMAL ] [ NOCONFIRM ]

The DBCOLLAT command-line utility

Syntax

     dbcollat[switches] output-file

Windows 3.x syntax

     dbcollw [switches] output-file

Switch	Description
-c"keyword=value; ..."	Supply database connection parameters
-e	Include empty mappings
-ofilename	Output log messages to file
-q	Quiet mode --- do not print messages
-x	Use hex for extended characters (7F-FF)
-y	Replace file without confirmation
-zcol-seq	Specify collating sequence label

  For more information about the command-line switches, see "Collation utility options".

Collation utility options

Connection parameters (-c ) For a description of the connection parameters, see "Database connection parameters". If the connection parameters are not specified, connection paremeters from the SQLCONNECT environment variable are used, if set. The user ID must have DBA authority.

For example, the following statement extracts a collation file from the sademo database running on the sample_server server, connecting as user ID DBA with password SQL:

     dbcollat -c "eng=sample_server;dbn=sademo;uid=dba;pwd=sql" c:\sample\col

Include empty mappings (-e ) Normally, collations don't specify the actual value that a character is to sort to. Instead, each line of the collation sorts to one position higher than the previous line. However, older collations have gaps between some sort positions. Normally, the Collation utility skips the gaps and writes the next line with an explicit sort-position. This option causes the Collation utility to write empty mappings (consisting of just a colon (:)) for each line in the gap.

Output log messages to file (-o ) Redirect the log messages from the Collation utility to a named file.

Operate quietly (-q ) Do not display messages on a window. This option is available only from the command-line utility.

Use hexadecimal for extended characters [7F to FF] (-x ) Extended single-byte characters (whose value is greater than hex 7F) may or may not appear correctly on your screen, depending on whether or not the code page in use on your computer is the same as the code page being used in the collation you are extracting. This option causes the Collation utility to write all characters at hex 7F or above as a two-digit hexadecimal number, in the form:

     \xdd

(For example, \x80, \xFE). Normally, only characters from hex 00 to hex 1F, hex 7F and hex FF are written in hexadecimal form.

Operate without confirming actions (-y ) Without this option, you are prompted to confirm replacing an existing collation file.

Specify collating sequence label (-z ) Specify the label of the collation to be extracted. The names of the collation sequences can be found in the collation_label column of the SYS.SYSCOLLATION table. If this option is not specified, then the Collation utility extracts the collation being used by the database.