There are various data movement options available in Db2®.
This topic provides an overview of the data movement tools, utilities,
stored procedures, and commands available to you.
Use these tables as a guide
to help you determine which data movement options might best suit
your needs.
Table 1. Load utility
| Method |
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 |
Table 2. Ingest utility
| Method |
Ingest utility |
| Purpose |
Streams data from files and pipes into Db2 target tables, while
still keeping those tables available. |
| Cross platform compatible |
Yes |
| Best practice usage |
This utility strikes a good
balance between performance and availability, but if the latter is
more important to you, then you should choose the ingest utility instead
of the load utility. Similar to the import utility, ingest is suitable
if the target tables are updatable views, range-clustered tables,
or nicknames; however, the ingest utility has superior performance. |
| References |
Ingesting
data |
Table 3. Import utility
| Method |
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:
- where the target table is a view
- the target table has constraints and you don't want the target table to be put in the Set
Integrity Pending state
- the target table has triggers and you want them fired
External tables can be used as an alternative to the import utility
|
| References |
Importing
data |
Table 4. Export utility
| Method |
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.
- External tables can be used as an alternative to the export utility
|
| References |
Exporting
data |
Table 5. db2move command
| Method |
db2move command |
| 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 |
|
Table 6. RESTORE command
| Method |
RESTORE command
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 |
|
Table 7. db2relocatedb command
| Method |
db2relocatedb 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 |
- This utility can be used for situations where a backup and restore
could be time consuming.
- This utility is an alternative to using backup and restore to
move or create copies of databases.
- It also provides a quick method of cloning a database for alternative
environments such as testing.
- It can be used to move table space containers to a new set of
storage devices
|
| References |
db2relocatedb - Relocate database command |
Table 8. ADMIN_COPY_SCHEMA procedure
| Method |
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:
- The ADMIN_COPY_SCHEMA procedure is used on a single database while
the db2move utility is used across databases
- The db2move utility fails when invoked if it
cannot create a physical object such as a table or index. The ADMIN_COPY_SCHEMA
procedure logs errors and continues.
- The ADMIN_COPY_SCHEMA procedure uses load from cursor to move
data from one schema to the other. The db2move utility
uses a remote load, similar to a load from cursor, which pulls in
the data from the source database.
|
| References |
Copying a schema |
Table 9. ADMIN_MOVE_TABLE procedure
| Method |
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.
- Avoid making multiple moves into same table space at the same
time.
- Run this procedure when activity on the table is low.
- Use a multi-step move operation. The INIT and COPY phases can
be called at any time. Execute the REPLAY phase multiple times in
order to keep the staging table size small, and then issue the SWAP
during a time of low activity on the table.
- Consider using an offline table move if you are working with tables
without unique indexes or tables with no index.
|
| References |
|
Table 10. Split mirror
| Method |
Split mirror |
| Purpose |
To create a clone, standby,
or backup database |
| Cross platform compatible |
No |
| Best practice usage |
- create a standby system in case of a primary failure to reduce
down time
- move backup operations away from a live production machine onto
a split database
- provides a quick method of cloning a database for alternate environments,
such as testing
|
| Considerations |
- only DMS table spaces can be backed up on the split version of
the database
- usually used in conjunction with some flashcopy technology provided
with storage systems
- an alternative is to issue a file copy once the database is suspended,
however this duplicates the amount of storage for the database
|
| References |
High availability through online split
mirror and suspended I/O support |
Table 11. External table
| Method |
External Table |
| Purpose |
- To efficiently move large quantities of data into newly created tables, or into tables that
already contain data.
- To efficiently unload data from a database to one of several external file formats.
|
| Cross platform compatible |
Yes |
| Best practice usage |
- External Tables for ingestion is best suited to situations where performance and online access
is your primary concern. In particular for columnar tables.
- External tables can be used as an alternative to the import utility. It is faster then the
import utility because it is bulk insert through INSERT FROM SELECT. External tables can fully
exploit resources, such as CPUs and memory on SMP and MPP environments.
- Similar to the import utility, External tables are suitable if the target tables are updatable
views, range-clustered tables, or nicknames; however, External Tables has superior performance
- External Tables as an unload mechanism are 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 or
database
|
| References |
External
Table |