Creating a new source table might
be necessary when the characteristics of the target table do not sufficiently
match the characteristics of the source when issuing the ALTER TABLE
statement with the ATTACH PARTITION clause.
Before creating a new
source table, you can attempt to correct the mismatch between the
existing source table and the target table.
Before you begin
To create a table, the privileges held by the authorization
ID of the statement must include at least one of the following authorities
and privileges:
- CREATETAB authority on the database and USE privilege on the table
space, as well as one of:
- 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 refers to
an existing schema
- DBADM authority
About this task
If attempts to correct the mismatch fail, error SQL20408N
or SQL20307N is returned.
Procedure
To create a new source table:
- Use the db2look command to produce the
CREATE TABLE statement to create a table identical to the target table:
db2look -d source_database_name -t source_table_name -e
- Remove the partitioning clause from the db2look output
and change the name of the table created to a new name (for example,
sourceC
).
- Next, load all of the data from the original source table
to the newly created source table, sourceC using a LOAD FROM
CURSOR command:
DECLARE mycurs CURSOR FOR SELECT * FROM source
LOAD FROM mycurs OF CURSOR REPLACE INTO sourceC
If this command fails because the original data is incompatible with
the definition of table sourceC, you must transform the data in the
original table as it is being transferred to sourceC.
- After the data is successfully copied to sourceC, submit
the ALTER TABLE target ...ATTACH sourceC statement.