Copying tables from one subsystem to another

You can copy tables from one subsystem to another by using the DSN1COPY utility. When you copy these tables, ensure that the object metadata on the target subsystem matches the object metadata on the source subsystem. Object metadata includes items such as the number of columns, column type, table space type, and version information.

About this task

Recommendation: Do not use DSN1COPY to copy XML table spaces from one subsystem to another. Documents in XML table spaces have dependencies on DB2® catalog tables and on tables in the XML schema repository database (DSNXSR). In particular, XML documents in XML table spaces have unique strings IDs that must match values in catalog table SYSIBM.SYSXMLSTRINGS. Documents might also have XSR object IDs that must match values in XML schema repository table SYSIBM.XSROBJECTS. If you copy XML table spaces to from one subsystem to another, the string IDs and XSR object IDs in the XML documents will not match the values in SYSIBM.SYSXMLSTRINGS or SYSIBM.XSROBJECTS on the target subsystem.

To copy a table space and its tables from one subsystem to another:

1. If a table space or table does not exist on the target subsystem, create it.

   If a table has an identity column, specify that column as follows:

   1. Issue a SELECT statement on the source subsystem to query the SYSIBM.SYSSEQUENCES entry that corresponds to the identity column for the table on the source subsystem.
   2. Add the INCREMENT value to the MAXASSIGNEDVAL value to determine the next value (nv) for the identity column.
   3. For the table on the target subsystem, specify nv for the START WITH value.
   4. Make all of the attributes for the identity column on the target table the same as the identity column attributes for the source table.
2. If the table space or table exists on the target subsystem, examine the table space, table, and column definitions in the catalog to ensure that the definitions are the same on the source and target subsystems.
3. If the table has been altered since its creation and has never been reorganized, take one of the following actions:
   • Run the REORG utility on the table space.

     You can determine if an object has been altered but not reorganized by checking the values of the OLDEST_VERSION and CURRENT_VERSION columns in SYSIBM.SYSTABLESPACE. If OLDEST_VERSION is 0 and CURRENT_VERSION is greater than 0, run REORG.

   • Do an INSERT or UPDATE after the last ALTER, to force the creation of a system page.
4. Ensure that enough version numbers are available. The combined active number of versions for the object on both the source and target subsystems must be less than 255. Use the following guidelines to calculate the active number of versions for the object on both the source and target subsystems:
   • If the value in the CURRENT_VERSION column is less than the value in the OLDEST_VERSION column, add the maximum number of versions to the value in the CURRENT_VERSION column.
   • Use the following formula to calculate the number of active versions:

     number of active_versions =
     MAX(target.CURRENT_VERSION,source.CURRENT_VERSION)
     - MIN(target.OLDEST_VERSION,source.OLDEST_VERSION) + 1

   If the number of active versions is too high, you must reduce the number of active versions by running REORG on both the source and target objects. Then, use the COPY utility to take a copy, and invoke the MODIFY RECOVERY utility to recycle the version numbers.

5. Query the DBID, PSID, and OBID of the object in the target subsystem. If the values are not the same as the source object, specify the DBID, PSID, and OBID as part of the OBIDXLAT data set for DSN1COPY.
6. Stop the table space on the source and target subsystems.
7. Run the DSN1COPY utility with the OBIDXLAT and RESET options. In the SYSXLAT data set, specify the proper mapping of table database object identifiers (OBIDs) for the table space from the source to the target subsystem.
8. Start the table space on the source and target subsystems for read/write access.
9. Run REPAIR VERSIONS on the table space on the target subsystem to attempt to fix the catalog information.
   • If REPAIR CATALOG TEST ends with return code 0, continue to the next step.
   • If REPAIR CATALOG TEST ends with return code 4, run REPAIR CATALOG to fix any mismatches.
   • If REPAIR CATALOG TEST ends with return code 8, go to step 2 to begin the process of copying the data again.

   If REPAIR VERSIONS ends with return code 8, some mismatches cannot be fixed. Go to step 2 to begin the process of copying the data again.

   REPAIR VERSIONS updates the following columns:

   • OLDEST_VERSION in SYSTABLEPART
   • VERSION in SYSTABLES
   • OLDEST_VERSION and CURRENT_VERSION in SYSTABLESPACE
10. If there are any indexes that are defined on the table on the source subsystem, but are not defined on the table on the target subsystem, create those indexes.
11. Run REBUILD INDEX on all indexes that are defined on the table on the target subsystem.