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.
    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.
    1. Run REPAIR CATALOG TEST to check for missing system pages for tables.

      If a table space has any missing system pages, message DSNU667I is issued, with this additional information: MISSING SYSTEM PAGE IN THE PAGE SET.

    2. If the table space has any missing system pages for tables that are not in version 0 format, and no SQL operations or utilities have been run that store the current table version in the table space, skip to step 2.
    3. If the table space has any missing system pages for tables in the table space that are in version 0 format, run REPAIR INSERTVERSIONPAGES SETCURRENTVERSION to insert system pages into the table space that describe those tables. Tables that are in version 0 format have had no version-changing alter operations.

      REPAIR INSERTVERSIONPAGES SETCURRENTVERSION performs the following actions:

      • Inserts missing system pages for tables that are in version 0 format into the table space.
      • Updates the version numbers in the VERSION column of the SYSIBM.SYSTABLES catalog table for all tables in the table space that are at version 0 to the same version number as in the CURRENT_VERSION column in SYSIBM.SYSTABLESPACE.
    4. Run REORG TABLESPACE on the table space on the source system to materialize all data rows to the current table space version, and to roll up all table space versions into the current table space version.
    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. Start of change 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
    End of change
  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.