DB2V8 Data Migration

DB2 Data Migration

Version: v8.1

Task: Take data from production to test environment

Problems encountered during processing:

1, Character set problem. The source database code page is GBK, and the target Database code page is uft-8

Processing method: Format conversion when exporting or importing

Note: Because in the GBK library, a Chinese character accounts for two bytes, and uft-8 inside, a Chinese character station three bytes, when the import target library, there is a field exceeding the length of the alarm error

Export:

DB2 "Export to XXX.IXF of IXF select * from XXX"

DB2 "Export to XXX.IXF of IXF modified by codepage=1208 select * FROM XXX"--Automatically enlarges the field length of the field type char and varchar in the table structure by one times

DB2 "Export to Xxx.del of Del Modified by codepage=1208 SELECT * from XXX"

Import:

DB2 "Load from XXX.IXF of ixf replace into XXX nonrecoverable"

DB2 "Load from Xxx.del to del replace into XXX nonrecoverable"

Note: When the load is imported, if the length field is very long, it will take a lot of time to print out the extra-long records and the large amount of data. For this kind of error, this time the processing method is to expand the table field length first, and then import the data. To view details of the load import, you can use the DB2 list utilities Show Detail

2, change the field type problem

Db2v8 seemingly can not directly modify, can only first delete the field and then add the field, helpless, the field above the index .... Only big strokes, rebuilding the table.

For security, first back up the table structure of the original environment

Method One: DB2 "Export to TBNAME.IXF of IXF select * from Tbnamewhere 1=2"

Method Two: db2look-d dbname-e-T Tbname-o tbname.sql

Modify the SQL statement in method Two, increase the length of the field, and then execute DB2-TVF tbname.sql to re-build the table.

In addition: would like to test the environment of the database to delete, directly the production of the database of all objects DDL back, in the test environment to restore, due to time, and did not do, afraid of bad, the next time you re-do a recovery, there is no relevant script, but also hope that the experience of the comrades to enlighten, a lot of guidance

An empty database was built temporarily

DB2 Create dbname using CODEPAGE=1208 territory CN

Create a buffer pool

DB2 Create Bufferpool bp16k size pagesize 16384

CREATE TABLE Space

DB2 Create tablespace tpname pagesize 16384 managed by database using (file, '/dd/ddd ' size) bufferpool bp16k

DB2V8 Data Migration