Copying tables - IBM DB2 9.7 for Linux, UNIX, and Windows

A basic COPY performs a simple copy of one table to another. This action is a one-time copy only. A new table is defined based on the definition of the selected table, and the contents are copied to the new table.

Before you begin

To copy a table, you need both:

One of the following authorities on the source table:
- SELECT privilege
- DATAACCESS authority
One of the following authorities on the target database:
- CREATETAB and CONNECT privileges, and either:
  - IMPLICIT_SCHEMA authority on the database if the implicit or explicit schema name of the table does not exist
  - CREATEIN privilege on the schema if the schema name of the table exists
- DBADM authority

About this task

Options that are not copied include:

Check constraints
Column default values
Column comments
Foreign keys
Logged and compact option on BLOB columns
Distinct types

A new table is defined based on the definition of the selected table, and the contents are copied to the new table. You can copy a table into the same database or a different database.

Procedure

To copy a table using the Control Center:
1. Open the Copy Table window: From the Control Center, expand the object tree until you find the Tables folder. Click on the Tables folder. Any existing tables are displayed in the pane on the right side of the window. Right-click the table you want to copy and select Copy from the pop-up menu. The Copy Table window opens.
2. Specify the name of an existing host or server for the target table and the instance that contains the database that will contain the target table.
3. Specify the database that will contain the target table, the schema for the target table, and a unique name for the target table. If a table with the same name already exists in the schema, the copy will fail.
4. Optional: Select a table space for the target table. Select a REGULAR DMS table space other than the default table space if you want to specify an index table space or long data table space.
5. Optional: Select a table space in which to create any indexes on the target table.
6. Optional: Select a table space in which to store the values of any long columns in the target table.
To copy a table using the command line, use the EXPORT and IMPORT commands.