There are various data movement options available. The following table provides an overview of the data movement tools and utilities available to you. Use this table as a guide to help you determine which data movement options might best suit your needs.
Utility name | Load utility |
Purpose | To efficiently move large quantities of data into newly created tables, or into tables that already contain data. |
Cross platform compatible | Yes |
Best practice usage | This utility is best suited to situations where performance is your primary concern. This utility can be used as an alternative to the import utility. It is faster then the import utility because it writes formatted pages directly into the database rather than using SQL INSERTS. In addition, the load utility allows you the option to not log the data or use the COPY option to save a copy of the loaded data. Load operations can fully exploit resources, such as CPUs and memory on SMP and MPP environments. |
References | Loading data |
Utility name | db2move |
---|---|
Purpose | Using the db2move utility with the COPY option, allows you to copy schema templates (with or without data) from a source database to a target database or move an entire schema from a source database to a target database. Using the db2move utility with the IMPORT or EXPORT option facilitates the movement of a large numbers of tables between DB2® databases. |
Cross platform compatible | Yes |
Best practice usage | When used with the COPY option, the source and the target database must be different. The COPY option is useful in making schema templates. Use the IMPORT or EXPORT option for cloning databases when there is no support for cross-platform backup and restore operations. The IMPORT and EXPORT options are used in conjunction with the db2look command. |
References |
Utility name | Import utility |
---|---|
Purpose | To insert data from an external file into a table, hierarchy, view, or nickname |
Cross platform compatible | Yes |
Best practice usage | The import utility can be
a good alternative to the load utility in the following situations:
|
References | Importing data |
Utility name | Export utility |
---|---|
Purpose | To export data from a database to one of several external file formats. The data can then be imported or loaded at a later time. |
Cross platform compatible | Yes |
Best practice usage | This utility is best suited in situations where you want to store data in an external file, to either process it further or move data to another table. High Performance Unload (HPU) is an alternative, however, it must be purchased separately. Export supports XML columns. |
References | Exporting data |
Utility name | ADMIN_COPY_SCHEMA procedure |
---|---|
Purpose | Allows you to make a copy of all the objects in a single schema and re-create those objects in a new schema. This copy operation can be performed with or without data, within a database. |
Cross platform compatible | Yes |
Best practice usage | This utility is useful for
making schema templates. It is also useful if you want to experiment
with a schema (for example, try out new indexes) without impacting
the source schema's behavior. The key differences between the ADMIN_COPY_SCHEMA
procedure and the db2move utility are:
|
References |
Utility name | ADMIN_MOVE_TABLE procedure |
---|---|
Purpose | Allows you to move the data in a table to a new table object of the same name (but with possibly different storage characteristics) while the data remains online and available for access. |
Cross platform compatible | Yes |
Best practice usage | This utility automates the
process of moving table data to a new table object while allowing
the data to remain online for select, insert, update, and delete access.
You can also generate a compression dictionary when a table is moved.
|
References | ADMIN_MOVE_TABLE procedure - Move an online table |
Utility name | Restore utility with the REDIRECT option and the GENERATE SCRIPT option |
---|---|
Purpose | To copy an entire database from one system to another using a script from an existing backup image. |
Cross platform compatible | Limited. See References |
Best practice usage | This utility is best suited in situations where a backup image exists. |
References |
Utility name | db2relocatedb - Relocate database command |
---|---|
Purpose | To rename a database, or relocate a database or part of a database to the same system or a different system. |
Cross platform compatible | No |
Best practice usage |
|
References | db2relocatedb - Relocate database command |
Utility name | Split mirror |
---|---|
Purpose | To create a clone, standby, or backup database. |
Cross platform compatible | No |
Best practice usage |
|
Considerations |
|
References | High availability through online split mirror and suspended I/O support |