Question & Answer
Question
How can I change the DB2 database code page, territory code or collation?
Cause
You cannot change the database code page, territory code, and collation once the database is created.
Answer
Converting non-Unicode databases to Unicode
There are some cases where you might need to convert an existing non-Unicode database to a Unicode database.
About this task
The following steps illustrate how to convert an existing non-Unicode database to a Unicode database:
Before you begin
You must have enough free disk space to export the data from the non-Unicode database. Also, if you are not reusing the existing table spaces, you will need enough free disk space to create new table spaces for the data.
Procedure
1. Export your data using the db2move command:
cd <export-dir>
db2move sample export
where <export-dir> is the directory to which you want to export your data and SAMPLE is the existing database name.
2. Generate a DDL script for your existing database using the db2look command:
db2look -d sample -e -o unidb.ddl -l -x -f
where SAMPLE is the existing database name and unidb.ddl is the file name for the generated DDL script. The -l option generates DDL for user defined table spaces, database partition groups and buffer pools, the -x option generates authorization DDL, and the -f option generates an update command for database configuration parameters.
3. Create the Unicode database:
CREATE DATABASE UNIDB COLLATE USING SYSTEM_codepage_territory
where UNIDB is the name of the Unicode database and SYSTEM_codepage_territory is a language-aware collation based on the weight table used for collating your non-Unicode data. This ensures that the data in the new Unicode database will be sorted in the same order.
4. Edit the unidb.ddl script:
Change all occurrences of the database name to the new Unicode database name:
CONNECT TO UNIDB
Increase the column lengths for character columns in your tables. (See Related ULR below.)
When characters are converted to Unicode, there may be an expansion in the number of bytes. It is recommended that you increase the length of the character columns to compensate for this expansion.
To keep the existing database, you must also change the file name specification for table spaces in the unidb.ddl file. Otherwise, you can drop the existing database and use the same table space files:
DROP DATABASE SAMPLE
5. Recreate your database structure by running the DDL script that you edited:
db2 -tvf unidb.ddl
6. Import your data into the new Unicode database using the db2move command:
cd <export-dir>
db2move unidb import
where <export-dir> is the directory where you exported your data and UNIDB is the Unicode database name.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21987640