DB2 database creation and table ixf File Export Import example, db2ixf

DB2 database creation and table ixf File Export Import example, db2ixf

1. Create a database

[Db2inst1 @ localhost ~] $ Db2set db2codepage = 1208 # Set Encoding

[Db2inst1 @ localhost ~] $ Db2 create database wms automatic storage yes using codeset UTF-8territory cn pagesize 32768 # create wms database

// Specify the db2 database installation directory

Db2 create db xxdb on/app/dbdata using CODEPAGE "GBK" country "Zh_CN"

2. Export the ixf file of the table (for example, exportReportData. sh)

ToDir =/home/cpsinst/bup/

Tab = "Comment comment UNTECK_DICT_ENTRY UNTECK_DICT_TYPE comment UNTECK_MENU UNTECK_OPERATION UNTECK_ORGANIZATION UNTECK_PRIMARYKEY UNTECK_RESOURCE UNTECK_ROLE UNTECK_ROLE_RESOURCE UNTECK_USER comment"

Db2 connect to cpsdb

Db2 set schema = 'ossuser'
For tabTemp in $ tab
Do

Echo $ tabTemp

Db2 "export to $ {toDir}/$ {tabTemp}. IXF of IXF select * from $ {tabTemp }"

Done

3. Import the ixf file of the table

Db2 connect to myetldb;
Db2 set schema = odsuser;
Db2 import from ETL_BUSI_TYPE.IXF of ixf modified by forcecreate commitcount 10000 replace_create INTO ETL_BUSI_TYPE
Db2 import from ETL_BUSI_TYPE_TOSEND.IXF of ixf modified by forcecreate commitcount 10000 replace_create INTO ETL_BUSI_TYPE_TOSEND
Db2 import from ETL_CONTACT_PEPOLE.IXF of ixf modified by forcecreate commitcount 10000 replace_create INTO ETL_CONTACT_PEPOLE

How to export only the table definitions from the ixf file instead of the table data in the ixf file in the DB2 database?

You can add a ROWCOUNT to specify the number of imported rows. For example
Import from aa. ixf of ixf rowcount 1 create into aa IN DATA IDX IN IDX

In this way, only one row is imported, but all table definitions are written to the new table. I don't know if ROWCOUNT can be imported if it is specified as 0. It seems to be a row. You can try it.

What is the command for importing and exporting database tables in DB2? Why is ixf always used for exporting and files always ended with del? Which of the following prawns gives you advice?

This format is defined by the Export Statement.

Export
Db2 export to [file name]. ixf of ixf select * from [Table name]
Or
Db2 export to [file name]. del of del select * from [Table name]

There are two import methods: import and load. I will only introduce import.
Db2 import from [file name]. ixf of ixf insert into [Table name]
Db2 import from [file name]. del of del insert into [Table name]

There are many options in the command. Read the tutorial for yourself.