Implementation of importing DB2 data to LINUX on AIX

Source: Internet
Author: User
Tags rtrim

The following describes how to import and export DB2 data to and from the LINUX system. If you are interested in importing DB2 data, take a look.

1) operations on the AIX system:

1) First, run the db2look command to obtain the DDL script of the database object:
Db2look-d SAMPLE-z DB2INST1-e-o sample. ddl-I db2inst1-w db2inst1

2) use the text editor to edit the generated sample. ddl: Creates table and index script statements, foreign key constraint statements, and trigger creation statements separately, and creates three DDL scripts:
Sample_tabs.ddl
Sample_foriegnkeys.ddl
Sample_triggers.ddl

3) use the following SQL statement to generate a script to export all data: exort. SQL
Db2 "select 'export to '| rtrim (tabname) | '. ixf of ixf select * from '| rtrim (tabname) |'; 'from syscat. tables where tabschema = 'db2inst1' "> export. SQL

4) edit the generated export. SQL, remove the header and tail information, and only retain the necessary export command.

5) use the following SQL statement to generate a script to import all data. We use the LOAD command and must use it)

Db2 "select 'Load from' | rtrim (tabname) | '. ixf of ixf insert into '| rtrim (tabname) |'; 'from syscat. tables where tabschema = 'db2inst1' "> load. SQL

6) edit the generated load. SQL, remove the head and tail information, only keep the necessary load command. search the tables in the sample_tabs.ddl file that contain the self-incrementing fields (the tables that contain the fields defined by generated always as identity), and load. the load command of the table containing the auto-increment field in SQL is added to the modified by identityoverride statement between of ixf and insert, for example, load from MYTABLE. ixf of ixf modified by identityoverride insert into MYTABLE ;)

Note: The modified by identityoverride in the load command ensures that the values of the auto-increment fields in DB2 data are consistent with those in the original database.

7) use the db2-tvf export. SQL command to export data from all tables.

2) Copy sample_tabs.ddl, sample_foriegnkeys.ddl, sample_triggers.ddl, load. SQL, and all exported. ixf data files to the LINUX server.

3) operations on LINUX:

1) Use the create database Command to CREATE a database sample, CREATE necessary tablespace, and configure necessary DATABASE parameters.

2) connect to the SAMPLE database and use the sample_tabs.dd script file to create a table (db2-tvf sample_tabs.ddl ).

3) Go to the directory where the. ixf data file is stored and use db2-tvf load. SQL DB2 data.

4) use the sample_foriegnkeys.ddl and sample_triggers.ddl script files to create foreign key constraints and triggers.

Finally, do not forget to run the runstats command on each table. You can generate the runstats script by referring to the above method to generate the export and load command script, and then run it.

Use of the DB2 auto-incrementing field IDENTITY

Introduction to DB2 datetime Data Types

Introduction to DB2 numeric data types

Four precautions for DB2 Data Import

Policy Selection for DB2 environment variable Management

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.