Document Type | Technical Information
Category | Migration
Applicable Product Versions | 6FS07, 6FS07PS, 7FS02, 7FS02PS
Document Number | TMITI002
Overview
When configuring a database, you need to decide the DB NAME (SID), data file path, and character set. Especially for the character set, since it is difficult to change after the database is created, it is necessary to configure it considering the user environment and requirements during initial installation.
This explains how to switch the character set from MSWIN949 to UTF8 and the tasks for each step.
Method
Sequence for changing character set from MSWIN949 to UTF8
- When changing from the existing 2Byte (MSWIN949) to 3Byte (UTF-8), it is necessary to increase the column size of existing tables.
- Adjust the size of the existing database.
- Backup the existing database (tbexport) - The server locale and DB character set must be the same.
- Create a new database (create the database with the desired character set).
- Import the backed-up data into the new database.
1. Check existing Tibero Character Set
- NLS_CHARACTERSET: MSWIN949
- NLS_NCHAR_CHARACTERSET: UTF16
SQL> col name for a25 SQL> col value for a15 SQL> col COMMENT_STR for a60 SQL> set linesize 200 SQL> select * from database_properties; NAME VALUE COMMENT_STR ------------------------- --------------- ------------------------------------------------------------ DFLT_PERM_TS USR Name of default permanent tablespace DFLT_TEMP_TS TEMP Name of default temporary tablespace DFLT_UNDO_TS0 UNDO Name of default undo tablespace NLS_CHARACTERSET MSWIN949 NLS_NCHAR_CHARACTERSET UTF16 DB_NAME tibero database name 6 rows selected.
2. Change Character Set in OS
Check LANG of OS account
$ locale | grep LANG LANG=en_US.UTF-8
Check available LANG options
$ locale -a | grep en_US.utf8 en_US.utf8
Temporarily change LANG if needed
$ LANG=en_US.utf8
Permanent change
$ vi ~/.bash_profile export LANG=en_US.utf8 -- Apply $ source ~/.bash_profile
3. Set Character Set on Terminal
The character encoding of the terminal in use must also be set to UTF-8. (e.g., Putty, Terminal settings)
4. Set Character Set in tbdns.tbr file
PATH: $TB_HOME/client/config/tbdsn.tbr
$ vi $TB_HOME/client/config/tbdsn.tbr
tibero=(
(INSTANCE=(HOST=localhost)
(PORT=8629)
(DB_NAME=tibero)
)
)
tibero_SP=(
(INSTANCE=(HOST=localhost)
(PORT=8630)
(DB_NAME=tibero)
)
)
TB_NLS_LANG=UTF85. Increase column size of existing tables
- If tablespace usage is over 65%, increase size by 1.5 times after checking tablespace.
- VARCHAR/CHAR: Increase existing column size by 1.5 to 2 times
Use the script below to check queries that increase column size and apply them to tables.
$ tbsql tibero/tmax
tbSQL 6
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Connected to Tibero.
SQL> SELECT 'ALTER TABLE '|| table_name ||' MODIFY('|| LOWER(column_name) ||' VARCHAR2('|| LEAST(CEIL(data_length*1.5/10) * 10, 4000) ||'));' AS sqltext
FROM USER_TAB_COLUMNS
WHERE data_type LIKE 'VARCHAR%'
AND data_length > 1
AND data_length < 4000;
SQLTEXT -- Automatically increased by 1.5 ~ 2 times
--------------------------------------------------------------------------------
ALTER TABLE TEST MODIFY(name VARCHAR2(20));
ALTER TABLE TEST MODIFY(address VARCHAR2(60));
ALTER TABLE TMAXKOREAN MODIFY(name VARCHAR2(20));
ALTER TABLE TMAXKOREAN MODIFY(address VARCHAR2(150));
ALTER TABLE TMAXKOREAN MODIFY(gender VARCHAR2(20));
5 rows selected.NoteMSWIN949 is 2Byte, UTF-8 is up to 3Byte, so column sizes are changed to prevent data loss.
Check table status before applying queries
SQL> desc TMAXKOREAN COLUMN_NAME TYPE CONSTRAINT ---------------------------------------- ------------------ -------------------- NAME VARCHAR(10) ADDRESS VARCHAR(100) POST NUMBER(6) NUM NUMBER(4) GENDER VARCHAR(10) SQL> ALTER TABLE TEST MODIFY(name VARCHAR2(20)); SQL> ALTER TABLE TEST MODIFY(address VARCHAR2(60)); SQL> ALTER TABLE TMAXKOREAN MODIFY(name VARCHAR2(20)); SQL> ALTER TABLE TMAXKOREAN MODIFY(address VARCHAR2(150)); SQL> ALTER TABLE TMAXKOREAN MODIFY(gender VARCHAR2(20)); -- Check table status after applying queries SQL> desc TMAXKOREAN COLUMN_NAME TYPE CONSTRAINT ---------------------------------------- ------------------ -------------------- NAME VARCHAR(20) ADDRESS VARCHAR(150) POST NUMBER(6) NUM NUMBER(4) GENDER VARCHAR(20)
6. Backup data with tbexport (existing character set - mswin949)
$ tbexport username=sys password=tibero sid=tibero file=/work/bak_data.dat log=/work/bak_data.log full=y
7. Create database with new Character Set
(1) Create database (UTF8) after creating a new server
(2) Create database (UTF8) as multi-instance on the same server
8. Data recovery (transfer data to newly created database)
-- Create user & grant privileges before tbimport
SQL> create user {user name} identified by {user password} ;
User '{user name}' created.
-- Grant privileges
SQL> grant connect, resource, dba to {user name};Perform tbimport
$ tbimport username=sys password=tibero sid=tibero2 file=/work/test.dat USER=TIBERO IGNORE=y
Note