Db2 Export, Import & Load Utilities

In this article we will see and learn about Db2 Export, Import and Load utilities.

Lets see these utilities overview with examples:

EXPORT Utility

This utility is used to export table's data. And data can be exported into four different file formats:

1. DEL - Delimited ASCII, uses special character delimiters to separate column values.

2. ASC - Non-delimited ASCII, create flat text files with aligned column data.

3. PC/IXF - PC version of the Integration Exchange Format (IXF), contains an external representation of the internal table.

4. WSF - Worksheet format

Below example is to export the file as DEL format , by default it is separate by common separate.

db2 export to /tmp/syscattables.del of del messages tmp/export_del_syscattables.log "select * from syscat.tables"

Below example is to export the file as binary format.

db2 export to /tmp/syscattables.ixf of ixf messages /tmp/export_ixf_syscattables.log "select * from syscat.tables"

NOTE : The SQL statement must be in quote, otherwise, it will get the “SQL3022N An SQL error "-104" occurred”

IMPORT Utility

This utility inserts data from an input file into a table or a view. The utility performs inserts as if it was executing INSERT statements.

• Data can be imported into four different file formats: 1. DEL - Delimited ASCII, uses special character delimiters to separate column values. 2. ASC - Non-delimited ASCII, create flat text files with aligned column data. 3. PC/IXF - PC version of the Integration Exchange Format (IXF), contains an external representation of the internal table. 4. WSF - Worksheet format.

• Only the binary format of the data ( IXF) can use CREATE INTO and REPLACE_CREATE to create the table during the import time. However, if the sourcing table has index , the index may create fail because the import would try to create it with the same name as source table.

• REPLACE: this would delete all existing data before insert it.

Below example is to import the IXF data and create the table.

db2 import from /tmp/syscattables.ixf of ixf messages /tmp/import_ixf_syscattables.log "create into tables2"

The data looks good but index does not get created and the error is showing in the message file.

Below example is to import the del data with REPLACE

db2 import from /tmp/syscattables.del of del messages /tmp/import_del_syscattables.log "replace into tables2"

LOAD Utility

Use the LOAD utility to load one or more tables of a table space. If you are loading a large number of rows, use this LOAD utility rather than inserting the rows by using the INSERT statement.

• The load utility is faster than the import utility because it writes formatted pages directly to the database not running the individual insert as import .

• Before using the LOAD utility, make sure that you complete all of the prerequisite activities for your situation.

• Access Control to Load Utility

  • SYSADM

  • DBADM

• The load operation maintains:

  • unique constrains.

  • Range constrains for partition tables

  • generated columns

  • LBAC security rules.

• For other constrains, the table is placed in the SET INTERGRITY PENDING at beginning of the load. After the load, the SET INTERGRITY must be used to take the table out of pending state.

• If the db2 server and the load directory is not on the same host, add the client keyword after the load : ex db2 load client ….

• data can be exported into fivedifferent file formats:

1. DEL - Delimited ASCII, uses special character delimiters to separate column values.

2. ASC - Non-delimited ASCII, create flat text files with aligned column data.

3. PC/IXF - PC version of the Integration Exchange Format (IXF), contains an external representation of the internal table.

4. WSF - Worksheet format

5. CURSOR - A cursor declared against an SQL query and only supported by the load utility.

• The Important Modes on Load Utility

  1. INSERT : The INSERT mode, just append the new rows in the Table. It will not DELETE any rows.

  2. REPLACE : It DELETEs all the rows, and inserts new rows in Table. The definition of Table and Indexes will not be changed.

  3. RESTART : It restarts from the previously committed point. That means, where it stopped previously.

  4. TERMINATE : It Terminates till the point where it initially started. It brings all the objects and indexes to normal. The LOAD TERMINATE issues on LOAD REPLACE, then it keeps in a state that, Table data truncates. That means, you can see empty Tables. The LOAD TERMINATE will not remove backup pending state. The LOAD TERMINATE issues on LOAD INSERT, The Table will retain all its original rows. That means the Table will be as before when you start LOAD INSERT.

  5. DEFFERRED : It means the indexes will not build during a load operation. But, these can be build using REBUILD mode.

COPY-pending status

The LOAD utility places a table space in the COPY-pending state if you load with LOG NO, which you might do to save space in the log. If you end the COPY job before it is finished, the table space is still in COPY-pending status.

When you use REORG or LOAD REPLACE with the COPYDDN keyword, a full image copy data set (SHRLEVEL REF) is created during the execution of the REORG or LOAD utility. This full image copy is known as an inline copy. The table space is not left in COPY-pending state regardless of which LOG option is specified for the utility.

REBUILD-pending status

The LOAD utility places all the index spaces for a table space in the REBUILD-pending status if you end the job (by using -TERM UTILITY) before it completes the INDEXVAL phase. It places the table space itself in RECOVER-pending status if you end the job before it completes the RELOAD phase.

CHECK-pending status

The LOAD utility places a table space in the CHECK-pending status if its referential or check integrity is in doubt. Because of this restriction, use of the CHECK DATA utility is recommended. That utility locates and, optionally, removes invalid data. If the CHECK DATA utility removes invalid data, the remaining data satisfies all referential and table check constraints, and the CHECK-pending restriction is lifted. LOAD does not set the CHECK-pending status for informational referential constraints.

Below example is to load IXF data and replace existing data.

db2 load from /tmp/syscattables.ixf of ixf messages /tmp/load_ixf_syscattables.log replace into tables2

Below example is to load IXF data and insert into the table.

db2 load from /tmp/syscattables.ixf of ixf messages /tmp/load_ixf_syscattables.log insert  into tables2

Below example is to load DEL data and insert into the table.

db2 load from /tmp/syscattables.del of del messages /tmp/load_del_syscattables.log insert  into tables2

That's all in this post. If you liked this blog and interested in knowing more about IBM Db2. Please Like, Follow, Share & Subscribe to imjhachandan.com