DB2 database修改字符集（思路和oracle一样）

来源：互联网发布：淘宝直通车神图编辑：程序博客网时间：2024/05/21 15:03

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.nls.doc%2Fdoc%2Ft0024033.html

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

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.
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.
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.
Edit the unidb.ddl script:
1. Change all occurrences of the database name to the new Unicode database name:
```
   CONNECT TO UNIDB
```
2. Increase the column lengths for character columns in your tables. 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.
3. 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
```
Recreate your database structure by running the DDL script that you edited:
```
   db2 -tvf unidb.ddl
```
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.

0 0