Create a new table and copy existing table data to the new table using RC/Extract and RC/Migrator
Article ID: 52841
Updated On:
Products
Issue/Introduction
Sometimes it may be necessary to copy the contents of a table to a new location. This means creating the new table and associated objects and
copying the data over to the new table either with SQL or a utility. It could involve creation of RI also if a group of tables are copied with their RI.
Resolution
RC/Extract for Db2 for z/OS (RCX) can do both the creation of a new table and its associated objects and RI but also the copying of the existing data.
This can also be done using RC/Migrator for Db2 for z/OS (RCM) although only a utility can be used with RC/Migrator to do the load. RC/Extract can use SQL or a Utility for the load.
Firstly ensure that the database and tablespace that the source table is defined on also exists in the target location because RC/Extract does not create these.
Create an RC/Extract Source Definition and select the target table or tables in the normal way. If the data to be extracted will be a selection of the original data then use the WHERE
clause or the KEY file to narrow down the records. This record selection is also something that an RC/Migrator migration can't do. Save the source definition.
So far it's the same as a normal extract.
Perform the extract of the data with the "Extract DDL" option set to "Y". This will obtain the table and associated definitions from the catalog as well as the table
data during the extract and store this in the extract object.
Create the target definition and select the correct target table and index name(if any). Use the DEPSW command to show indexes. Change the names so that
the target name is correct and also the SSID if different than the source. Use the SAVE command to save the changes.
Then using the target definition initiate a load step with the "I" line command on the target definition. Here we make sure that the "DDL Options:" are set to "Y". This will
cause RC/Extract to generate the DDL for the target objects before the load step and create the target objects that the Target Definition said were "NEW".
This would mean the table, index, view and RI of the source table or tables.
The load step either with SQL or a Load utility (Fast Load or IBM Load) will create the target table and its associated objects first then it will load the data
to the new table or tables that have just been created.
That existing extract object could be used more than once to create and load other copies of this table without the need to unload the table data again.
All you would need to do is to create additional target definitions on the same extract object. The Extract object could in fact be kept as a sort of backup of both the DDL and the data.
Additional Information
Feedback
