IBM DB2 utilities Export, Import and Load overview with example
Export/Import/Load supports following file formats:
DEL - Delimited ASCII, uses special character delimiters to separate column values.
ASC - Non-delimited ASCII, create flat text files with aligned column data.
PC/IXF - PC version of the Integration Exchange Format (IXF), contains an external representation of the internal table.
WSF - Worksheet format
CURSOR - A cursor declared against an SQL query. only supported by the load utility.
- Connect to Sample database
[db2inst1@ibmdb2 ~]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
- export employee table
db2 => export to exp_emp1.del of del select * from employee
SQL3104N The Export utility is beginning to export data to file
"exp_emp1.del".
SQL3105N The Export utility has finished exporting "42" rows.
Number of rows exported: 42
mkdir lobs
- export table with lob column
db2 => export to exp_emp2.del of del lobs to /home/db2inst1/lobs select * from emp_resume;
SQL3104N The Export utility is beginning to export data to file
"exp_emp2.del".
SQL3105N The Export utility has finished exporting "8" rows.
Number of rows exported: 8
- format of export file with lob column
[db2inst1@ibmdb2 ~]$ more exp_emp2.del
"000130","ascii","exp_emp2.del.001.lob.0.1257/"
"000130","html","exp_emp2.del.001.lob.1257.2415/"
"000140","ascii","exp_emp2.del.001.lob.3672.1261/"
"000140","html","exp_emp2.del.001.lob.4933.2440/"
"000150","ascii","exp_emp2.del.001.lob.7373.1308/"
"000150","html","exp_emp2.del.001.lob.8681.2467/"
"000190","ascii","exp_emp2.del.001.lob.11148.1237/"
"000190","html","exp_emp2.del.001.lob.12385.2402/"
- create new table for import
db2 => create table employee_cpy like employee;
DB20000I The SQL command completed successfully.
- import syntax
import from import-file of file-type
[lobs from directory]
[xml from directory]
[method options]
[xml options]
[concurrency control]
[restart and commit options]
[warnings and message options]
{insert | insert_update | replace | replace_create | create } into target
- import from employee table to employee_cpy
db2 => import from /home/db2inst1/exp_emp1.del of del insert into employee_cpy;
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/exp_emp1.del".
SQL3110N The utility has completed processing. "42" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "42".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "42" rows were processed from the input file. "42" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 42
Number of rows skipped = 0
Number of rows inserted = 42
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 42
- adding constraint to new table
[db2inst1@ibmdb2 ~]$ db2 "alter table employee_cpy add constraint empcp_pk primary key(empno)"
DB20000I The SQL command completed successfully.
- rerunning the same import again
[db2inst1@ibmdb2 ~]$ db2 "import from /home/db2inst1/exp_emp1.del of del insert into employee_cpy"
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/exp_emp1.del".
SQL3148W A row from the input file was not inserted into the table. SQLCODE
"-803" was returned.
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DB2INST1.EMPLOYEE_CPY" from having duplicate values for the index key.
SQLSTATE=23505
..
..
SQL3110N The utility has completed processing. "42" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "42".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "42" rows were processed from the input file. "0" rows were
successfully inserted into the table. "42" rows were rejected.
Number of rows read = 42
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 42
Number of rows committed = 42
- DB2 Import Options
INSERT - Data is inserted into the target table.
INSERT_UPDATE - Data is inserted into the target table. if matching primary keys, row is updated with the new data.
REPLACE - The target table is first truncated, then data is inserted.
REPLACE_CREATE - If target table already exists, it is truncated. If it does not exist, it is first created.
CREATE - first create the target table before inserting the data.
- insert and update to avoid above situation
[db2inst1@ibmdb2 ~]$ db2 "import from /home/db2inst1/exp_emp1.del of del allow write access warningcount 10 notimeout messages msg_exp_emp1.txt insert_update into employee_cpy"
Number of rows read = 42
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 42
Number of rows rejected = 0
Number of rows committed = 42
- Another example of export import
[db2inst1@ibmdb2 ~]$ db2 export to exp_dept.del of del messages msg_exp_dept_del.log "select * from department"
Number of rows exported: 14
- export to ixf
[db2inst1@ibmdb2 ~]$ db2 export to exp_dept.ixf of ixf messages msg_exp_dept_ixf.log "select * from department"
Number of rows exported: 14
- create new table with above dump
[db2inst1@ibmdb2 ~]$ db2 import from exp_dept.ixf of ixf messages msg_imp_ixf_dept.log "create into department_cpy"
Number of rows read = 14
Number of rows skipped = 0
Number of rows inserted = 14
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 14
SQL3107W There is at least one warning message in the message file.
- lets see message file (index does not get created)
[db2inst1@ibmdb2 ~]$ cat msg_imp_ixf_dept.log
SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date
"20161015", and time "112837".
SQL3153N The T record in the PC/IXF file has name "exp_dept.ixf", qualifier
"", and source " ".
SQL3109N The utility is beginning to load data from file "exp_dept.ixf".
SQL3187W An error occurred while creating an index. SQLCODE "-601" was
returned.
SQL0601N The name of the object to be created is identical to the existing
name "DB2INST1.PK_DEPARTMENT" of type "INDEX". SQLSTATE=42710
SQL3189N The previous message refers to index "DB2INST1.PK_DEPARTMENT" with
columns "+DEPTNO ".
SQL3187W An error occurred while creating an index. SQLCODE "-601" was
returned.
SQL0601N The name of the object to be created is identical to the existing
name "DB2INST1.XDEPT2" of type "INDEX". SQLSTATE=42710
SQL3189N The previous message refers to index "DB2INST1.XDEPT2" with columns
"+MGRNO ".
SQL3187W An error occurred while creating an index. SQLCODE "-601" was
returned.
SQL0601N The name of the object to be created is identical to the existing
name "DB2INST1.XDEPT3" of type "INDEX". SQLSTATE=42710
SQL3189N The previous message refers to index "DB2INST1.XDEPT3" with columns
"+ADMRDEPT ".
SQL3110N The utility has completed processing. "14" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "14".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "14" rows were processed from the input file. "14" rows were
successfully inserted into the table. "0" rows were rejected.
- loading with db2 load command
▪ The load utility works directly with the underlying storage for the DB2 database.
▪ It bypasses much of the relational engine of DB2 and some of the rules which is imposed on an insert, update, or delete statement or on import.
▪ Faster than the import utility when moving large amounts of data, because the load utility writes formatted pages directly into the database.
▪ The key values are sorted and the index is built after the data has been loaded.
▪ The load utility checks for uniqueness and computes generated column values, but all other constraints must be checked using SET INTEGRITY.
[db2inst1@ibmdb2 ~]$ db2 "load from exp_dept.ixf of ixf messages msg_load_ixf_dept.log replace into department_cpy"
Number of rows read = 14
Number of rows skipped = 0
Number of rows loaded = 14
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 14
- lets see load message file
[db2inst1@ibmdb2 ~]$ cat msg_load_ixf_dept.log
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/exp_dept.ixf".
SQL3500W The utility is beginning the "LOAD" phase at time "10/15/2016
11:43:52.910985".
SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date
"20161015", and time "112837".
SQL3153N The T record in the PC/IXF file has name "exp_dept.ixf", qualifier
"", and source " ".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "14" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "14".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "10/15/2016
11:43:52.975863".
Number of rows read = 14
Number of rows skipped = 0
Number of rows loaded = 14
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 14
Export/Import/Load supports following file formats:
DEL - Delimited ASCII, uses special character delimiters to separate column values.
ASC - Non-delimited ASCII, create flat text files with aligned column data.
PC/IXF - PC version of the Integration Exchange Format (IXF), contains an external representation of the internal table.
WSF - Worksheet format
CURSOR - A cursor declared against an SQL query. only supported by the load utility.
- Connect to Sample database
[db2inst1@ibmdb2 ~]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
- export employee table
db2 => export to exp_emp1.del of del select * from employee
SQL3104N The Export utility is beginning to export data to file
"exp_emp1.del".
SQL3105N The Export utility has finished exporting "42" rows.
Number of rows exported: 42
mkdir lobs
- export table with lob column
db2 => export to exp_emp2.del of del lobs to /home/db2inst1/lobs select * from emp_resume;
SQL3104N The Export utility is beginning to export data to file
"exp_emp2.del".
SQL3105N The Export utility has finished exporting "8" rows.
Number of rows exported: 8
- format of export file with lob column
[db2inst1@ibmdb2 ~]$ more exp_emp2.del
"000130","ascii","exp_emp2.del.001.lob.0.1257/"
"000130","html","exp_emp2.del.001.lob.1257.2415/"
"000140","ascii","exp_emp2.del.001.lob.3672.1261/"
"000140","html","exp_emp2.del.001.lob.4933.2440/"
"000150","ascii","exp_emp2.del.001.lob.7373.1308/"
"000150","html","exp_emp2.del.001.lob.8681.2467/"
"000190","ascii","exp_emp2.del.001.lob.11148.1237/"
"000190","html","exp_emp2.del.001.lob.12385.2402/"
- create new table for import
db2 => create table employee_cpy like employee;
DB20000I The SQL command completed successfully.
- import syntax
import from import-file of file-type
[lobs from directory]
[xml from directory]
[method options]
[xml options]
[concurrency control]
[restart and commit options]
[warnings and message options]
{insert | insert_update | replace | replace_create | create } into target
- import from employee table to employee_cpy
db2 => import from /home/db2inst1/exp_emp1.del of del insert into employee_cpy;
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/exp_emp1.del".
SQL3110N The utility has completed processing. "42" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "42".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "42" rows were processed from the input file. "42" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 42
Number of rows skipped = 0
Number of rows inserted = 42
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 42
- adding constraint to new table
[db2inst1@ibmdb2 ~]$ db2 "alter table employee_cpy add constraint empcp_pk primary key(empno)"
DB20000I The SQL command completed successfully.
- rerunning the same import again
[db2inst1@ibmdb2 ~]$ db2 "import from /home/db2inst1/exp_emp1.del of del insert into employee_cpy"
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/exp_emp1.del".
SQL3148W A row from the input file was not inserted into the table. SQLCODE
"-803" was returned.
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DB2INST1.EMPLOYEE_CPY" from having duplicate values for the index key.
SQLSTATE=23505
..
..
SQL3110N The utility has completed processing. "42" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "42".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "42" rows were processed from the input file. "0" rows were
successfully inserted into the table. "42" rows were rejected.
Number of rows read = 42
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 42
Number of rows committed = 42
- DB2 Import Options
INSERT - Data is inserted into the target table.
INSERT_UPDATE - Data is inserted into the target table. if matching primary keys, row is updated with the new data.
REPLACE - The target table is first truncated, then data is inserted.
REPLACE_CREATE - If target table already exists, it is truncated. If it does not exist, it is first created.
CREATE - first create the target table before inserting the data.
- insert and update to avoid above situation
[db2inst1@ibmdb2 ~]$ db2 "import from /home/db2inst1/exp_emp1.del of del allow write access warningcount 10 notimeout messages msg_exp_emp1.txt insert_update into employee_cpy"
Number of rows read = 42
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 42
Number of rows rejected = 0
Number of rows committed = 42
- Another example of export import
[db2inst1@ibmdb2 ~]$ db2 export to exp_dept.del of del messages msg_exp_dept_del.log "select * from department"
Number of rows exported: 14
- export to ixf
[db2inst1@ibmdb2 ~]$ db2 export to exp_dept.ixf of ixf messages msg_exp_dept_ixf.log "select * from department"
Number of rows exported: 14
- create new table with above dump
[db2inst1@ibmdb2 ~]$ db2 import from exp_dept.ixf of ixf messages msg_imp_ixf_dept.log "create into department_cpy"
Number of rows read = 14
Number of rows skipped = 0
Number of rows inserted = 14
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 14
SQL3107W There is at least one warning message in the message file.
- lets see message file (index does not get created)
[db2inst1@ibmdb2 ~]$ cat msg_imp_ixf_dept.log
SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date
"20161015", and time "112837".
SQL3153N The T record in the PC/IXF file has name "exp_dept.ixf", qualifier
"", and source " ".
SQL3109N The utility is beginning to load data from file "exp_dept.ixf".
SQL3187W An error occurred while creating an index. SQLCODE "-601" was
returned.
SQL0601N The name of the object to be created is identical to the existing
name "DB2INST1.PK_DEPARTMENT" of type "INDEX". SQLSTATE=42710
SQL3189N The previous message refers to index "DB2INST1.PK_DEPARTMENT" with
columns "+DEPTNO ".
SQL3187W An error occurred while creating an index. SQLCODE "-601" was
returned.
SQL0601N The name of the object to be created is identical to the existing
name "DB2INST1.XDEPT2" of type "INDEX". SQLSTATE=42710
SQL3189N The previous message refers to index "DB2INST1.XDEPT2" with columns
"+MGRNO ".
SQL3187W An error occurred while creating an index. SQLCODE "-601" was
returned.
SQL0601N The name of the object to be created is identical to the existing
name "DB2INST1.XDEPT3" of type "INDEX". SQLSTATE=42710
SQL3189N The previous message refers to index "DB2INST1.XDEPT3" with columns
"+ADMRDEPT ".
SQL3110N The utility has completed processing. "14" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "14".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "14" rows were processed from the input file. "14" rows were
successfully inserted into the table. "0" rows were rejected.
- loading with db2 load command
▪ The load utility works directly with the underlying storage for the DB2 database.
▪ It bypasses much of the relational engine of DB2 and some of the rules which is imposed on an insert, update, or delete statement or on import.
▪ Faster than the import utility when moving large amounts of data, because the load utility writes formatted pages directly into the database.
▪ The key values are sorted and the index is built after the data has been loaded.
▪ The load utility checks for uniqueness and computes generated column values, but all other constraints must be checked using SET INTEGRITY.
[db2inst1@ibmdb2 ~]$ db2 "load from exp_dept.ixf of ixf messages msg_load_ixf_dept.log replace into department_cpy"
Number of rows read = 14
Number of rows skipped = 0
Number of rows loaded = 14
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 14
- lets see load message file
[db2inst1@ibmdb2 ~]$ cat msg_load_ixf_dept.log
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/exp_dept.ixf".
SQL3500W The utility is beginning the "LOAD" phase at time "10/15/2016
11:43:52.910985".
SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date
"20161015", and time "112837".
SQL3153N The T record in the PC/IXF file has name "exp_dept.ixf", qualifier
"", and source " ".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "14" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "14".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "10/15/2016
11:43:52.975863".
Number of rows read = 14
Number of rows skipped = 0
Number of rows loaded = 14
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 14
No comments:
Post a Comment
Leave a Reply...