Data movement options

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
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.
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