Start of change

Accessing historical data from moved tables by using image copies

Before materializing any pending changes to move tables from a table space, you can create image copies of the table space so that historical data from the tables can be accessed after the tables are moved.

About this task

FL 508

You can use the ALTER TABLESPACE MOVE TABLE statement to move a table from a source table space to a target table space. If the data sets of the source table space are already created, the change is a pending definition change that must be materialized.

After tables are moved, historical data from the tables can be accessed from image copies of the source table space that were created before materialization of the MOVE TABLE operations.

Data from tables that were not moved during MOVE TABLE operations can also be accessed from the image copies by running the UNLOAD utility on the source table space. However, certain operations on unmoved tables or the source table space, such as dropping a column or changing the page size, prohibits the use of the UNLOAD utility on image copies. If any of these operations occur after the image copies are created, running the UNLOAD utility on the source table space returns an error message and fails.

Attention: If a moved table contains one or more XML columns, using this procedure for any purpose other than accessing historical data from the table might result in an error. In this case, issuing SQL INSERT or UPDATE statements or running the LOAD utility might generate DOCID values that conflict with the restored data.
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 the SYSIBM.SYSXMLSTRINGS catalog table. Documents might also have XSR object IDs that must match values in the SYSIBM.XSROBJECTS XML schema repository table. If you copy XML table spaces 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

The following procedure first describes how to create image copies of the source table space and then describes how to access historical data from the moved tables from those image copies.

  • To create image copies of the source table space:
    1. Check for and insert missing system pages into the table space.
      1. Run REPAIR CATALOG TEST to check for missing system pages for tables. If the 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 in version 0 format, run REPAIR INSERTVERSIONPAGES SETCURRENTVERSION to insert system pages into the table space. Tables that are in version 0 format have had no version-changing alter operations.
    2. Collect the following information:
      • The DDL for the source base table space, tables, and indexes
      • The DBID, PSID, and OBID values for the source base table space, which can be queried from the SYSIBM.SYSTABLESPACE catalog table
      • The OBID value of each table, which can be queried from the SYSIBM.SYSTABLES catalog table
      • The INCREMENT and MAXASSIGNEDVAL column values for each table in the source base table space that has an identity column, which can be queried from the SYSIBM.SYSSEQUENCES catalog table. Add the INCREMENT value to the MAXASSIGNEDVAL value to determine the next value (nv) for the identity column.
      If any related auxiliary LOB or XML table spaces exist, collect the following additional information:
      • The DDL for the auxiliary table spaces
      • The DBID, PSID, and OBID values for the auxiliary table spaces, which can be queried from the SYSIBM.SYSTABLESPACE catalog table
    3. Using the collected information, make full image copies or incremental copies of the source base table space and any related auxiliary LOB and XML table spaces.
      • For consistent image copies, run the COPY utility with the SHRLEVEL REFERENCE or FLASHCOPY CONSISTENT SHRLEVEL CHANGE option on the list of table spaces.
      • For fuzzy image copies, run the COPY utility with the SHRLEVEL CHANGE option on the list of table spaces.
        Attention: Using fuzzy image copies for this procedure might result in fuzzy data, such as duplicate rows, missing rows, or uncommitted data.
    After the image copies are created, you can materialize the MOVE TABLE operations by running the REORG utility.
  • To access historical data from the moved tables from the image copies:
    1. Using application compatibility level V12R1M503 or lower and the information that was collected in step 2 from the first part of this procedure, create the following objects. If incremental copies were made, use the information that was collected from the most recent incremental copy.
      • Create a new base table space with the same attributes as the source base table space at the time the image copy was created.
        Note: If the source base table space of the MOVE TABLE operation is a simple table space, you cannot create a new simple table space. If simple table spaces that have the same attributes as the source base table space already exist for testing purposes, use one of those table spaces instead of creating a new table space.
      • If image copies of related auxiliary table spaces were created, create new auxiliary table spaces with the same attributes as the source auxiliary table spaces at the time the image copies were created.
      • Create new tables in the new base table space with the same attributes as the source tables at the time the image copy was created. For tables with identity columns, specify nv for the START WITH value in the CREATE TABLE statement.
        Note: For tables with XML columns, you cannot set or alter the starting value for the DOCID columns. Do not issue SQL INSERT or UPDATE statements or run the LOAD utility after this procedure because duplicate DOCID column values might be generated.
      • Create indexes that are needed for the new tables.
    2. Collect the following information:
      • The DBID, PSID, and OBID values for the new base table space, which can be queried from the SYSIBM.SYSTABLESPACE catalog table
      • If new auxiliary table spaces were created, the DBID, PSID, and OBID values for the new auxiliary table spaces, which can be queried from the SYSIBM.SYSTABLESPACE catalog table
      • The OBID value of each new table, which can be queried from the SYSIBM.SYSTABLES catalog table
    3. For each new table space, including any new auxiliary table spaces, complete the following steps:
      1. Issue the STOP DATABASE command.
      2. Run the DSN1COPY stand-alone utility with the following specifications to restore the full image copy to the new table space:
        • Specify the full image copy as SYSUT1, the input data set.
        • Specify the table space linear data set (LDS) as SYSUT2, the output data set.
        • Specify the RESET option so that the log RBA or LRSN values in the pages are set to zero.
        • Specify the OBIDXLAT option. In the SYSXLAT data set, specify the proper mapping of DBIDs, PSIDs, and table OBIDs from the production to the new table space.
        • For sequential full image copies, specify the FULLCOPY option unless the new table space is a segmented table space. For a segmented table space, specify the SEGMENT option instead.
        Note: If incremental copies were created, first run the DSN1COPY utility to restore the full image copy, and then restore each incremental copy with the INCRCOPY option.
      3. Issue the START DATABASE command.
    4. Run the REPAIR CATALOG utility on the new base table space to check and correct data version information in both the table space and the catalog.
    5. If indexes are needed, run the REBUILD INDEX utility to rebuild the new indexes.
    6. If consistent data is needed, run health checks such as the CHECK INDEX, CHECK DATA, and CHECK LOB utilities.
      If fuzzy copies were used as input, inconsistencies might be detected in the data. To correct data inconsistencies, use the REPAIR utility, the SQL DELETE statement, or the SQL UPDATE statement to delete or update the data.
    7. To access data from the tables in the new base table space, take one of the following actions:
      • Run the UNLOAD utility to extract data from the tables. You can load the extracted data by subsequently running the LOAD utility on other tables.
      • Issue the SQL SELECT statement to retrieve data from the tables.
      • Issue the SQL INSERT statement with a fullselect to extract data from the tables and insert it into other tables.
End of change