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.

Procedure

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

  1. Start of change Check for and insert missing system pages into the table space on the source system by following the procedure in Checking for missing system pages.
    Important: This step makes the tables in the table space self-describing. When a table space is self-describing, Db2 does not need to search the catalog or directory for table format information, which lessens the possibility of errors after a table is copied to the target subsystem.
    End of change
  2. 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.
  3. 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.
  4. 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.
  5. Stop the table space on the source and target subsystems.
  6. 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.
  7. Start the table space on the source and target subsystems for read/write access.
  8. Run REPAIR CATALOG TEST on the table space on the target subsystem to ensure that the catalog information matches the page set information.

    Take one of the following actions:

    • 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 3 to begin the process of copying the data again.
    REPAIR CATALOG updates the following columns:
    • OLDEST_VERSION in SYSTABLEPART
    • VERSION in SYSTABLES
    • OLDEST_VERSION and CURRENT_VERSION in SYSTABLESPACE
  9. 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.
  10. Run REBUILD INDEX on all indexes that are defined on the table on the target subsystem.