Changing the database collation

You can change your database collation from one collation to another by performing an unload and reload into the collation of choice. Collations are chosen at database creation time and cannot be changed without rebuilding the database.

Prerequisites

You must have the SELECT ANY TABLE and SERVER OPERATOR system privileges.

By default, you must have the SELECT ANY TABLE system privilege to execute an UNLOAD statement. The required privileges can be changed by using the -gl database server option.

Task

Start the database:
```
START databasefile 
```
Determine the character set for the existing database:
```
SELECT DB_PROPERTY( 'CharSet' );
```
For early versions of SAP Sybase IQ, this property may not exist. The character set is also implied by the collation name. For example, collation 1252LATIN1 uses code page 1252.

The character set in the existing database should be the same as, or compatible with, the operating system and client character sets. If it is not, it is an excellent reason to rebuild the database, but requires great care in the rebuild process.

In particular, if you have been using a database with collation 850LATIN1 with earlier versions of SAP Sybase IQ that either did not support character set conversion (versions 5 and earlier) or disabled it by default (versions 6 and 7), and if your client applications were normal Windows applications, you may have code page 1252 character data in your database that is interpreting data to be in code page 850.
Determine the character set for the data in the existing database:
```
UNLOAD TABLE mytable TO 'mytable-data-in-utf8.dat' ENCODING 'UTF-8';
```
View the results in a text editor.

If accented data is correct, then the character data in the database matches the Windows ANSI code page, which for English and other Western European languages is code page 1252. If the data appears correct in a DOS-based editor, then the character data matches the Windows OEM code page, which is likely 437 or 850.
Unload the database.

If the data character set is incompatible with the database character set, it is critical that the data is unloaded without character set conversion. Depending on the version of SAP Sybase IQ being used, you can use the internal unload feature of dbunload, or manually unload the data using the UNLOAD TABLE statement.
Create the new database, specifying the collations and character sets you want to use, and set the DBA user ID and password to DBA and sql:
```
iqinit -dba DBA,sql -z 1252LATIN1 c:\newdatabase.db 
```
Stop the old database server and start the new database server:
```
iqsrv16 -n new-server c:\newdatabase.db 
```
Load the data into the new database.
If the unloaded data and schema (reload.sql) match the character set of the computer used to perform the reload, you can use the external reload option of dbunload. The data is automatically converted to the correct character set for the database. For example:
```
dbunload -ix c:\databasefile
```
If the data's encoding does not match the character set of the database, and you are loading the data using LOAD TABLE statements (internal reload), you must use the ENCODING clause; the database server does not, by default, perform character set conversion for data loaded using LOAD TABLE statements.

If the data's encoding does not match the code page of the computer on which you are working, and you are loading using INPUT statements (external reload), you must use the ENCODING clause; otherwise, the database server assumes that the data is in the computer's native character set.

The database is recreated with the new collation.