Moving data that you are merging

In the context of Db2 data sharing, a target refers to the Db2 subsystem to which you are moving data (the data sharing group). A source refers to the Db2 subsystem from which you are moving data.

Procedure

To move data that you are merging:

  1. Decide the method that you will use to move data.
    Three options are outlined below; you can use a combination of the specified methods. Each of the methods assumes that the objects that are to be created in the target system are created with the exact DDL that is used in the source system. Descriptions of methods 1, 2, and 3 are referred to throughout the procedure, and are described in detail below.
    • Method 1: Unload and Reload: The data that is to be moved is unloaded from the source Db2 subsystem and loaded into the target data sharing group by using the Db2 online utilities, UNLOAD and LOAD. This approach is the most simple because tables are created without concern for object IDs, and data is unloaded or reloaded using conventional techniques. The major disadvantage of this method is that it is the slowest of the three methods, in terms of performance. The unload and reload methods operate against rows of a table, whereas the other techniques operate against pages or data sets as their level of granularity. Additionally, this method requires two to three times the disk space that the other methods require, depending on how you unload or reload (source, target and intermediate unload).
    • Method 2: DSN1COPY with OBID translation: The data that is to be moved is copied from the source system to the target system using the Db2 offline utility DSN1COPY. DSN1COPY is usually faster than unload and reload because it moves pages of data, rather than rows. This method is more complicated because the object IDs in the source data will need to be translated.
    • Method 3: Use Db2 VSAM data sets from the source system in the target system: The VSAM data sets that are used to hold the table space and index space data from the source system are used in the target system, without copying any data. Because this method does not require copying the data, it is the fastest of the three methods, in terms of performance. It is, however, the most complicated. Another advantage of this method is that it requires less disk space than the other methods; this method is generally used for very large table or index spaces. Method 3 assumes that all of the disk volumes in the source system used by objects being moved by this method are shared and accessible with the target system.
  2. Choose a catalog that belongs to one of the Db2 subsystems to be the original catalog for the data sharing group.
    This Db2 subsystem is considered the originating member of the group.

    You should consider many different factors when choosing which Db2 subsystem to be the originating member of the group. For example, it makes sense to choose the member with the most database objects as the originating member in order to minimize the number of objects that you move.

    However, if all Db2 subsystems are mostly equivalent, and you are not planning to use DSN1COPY, consider the log RBA values of the existing subsystems. Compare the end-of-log RBAs with the high-order 10 bytes (for 10-byte RBAs) or 6 bytes (for 6- byte RBAs) of the time-of-day clock timestamp on each of their systems. This value is called the truncated timestamp. The usual case is that the RBA is less than the truncated timestamp. In this case, any Db2 subsystem is chosen as the originating member.

    In the event that the current end-of-log RBA in any of the existing Db2 subsystems is higher than the truncated timestamp value at the time you are ready to enable sharing, you have the following choices:

    • Choose the Db2 subsystem that has the highest RBA as the originating member.
    • Use DSN1COPY with the RESET option to reset the log RBAs in each data and index page to 0 when you move databases from other Db2 subsystems to the data sharing group.
  3. Resolve name conflicts among the objects and authorization IDs in the data sharing group.
  4. Create the objects on the target subsystem.

    When creating objects while you are using methods 1 or 2 to move the data, the CREATE statements can be entered in any order. Db2 assigns new OBIDs for these objects.

    If you are using method 3, you must perform the following steps:
    1. Consider creating the data sets with the minimum size settings to save space. The actual target system VSAM data sets created by the DDL execution are not used because the VSAM data sets that are from the source system are used instead. By choosing to create the objects with minimum sizes, you eventually need to issue ALTER TABLESPACE and ALTER INDEX statements to change the PRIQTY and SECQTY after the CREATE. If, however, you are using the same high-level qualifier for the source and target systems (which is not recommended), you must create the objects with DEFINE NO, and take additional steps later. DEFINE NO is needed when keeping the same high-level qualifier, because the VSAM data set name would be identical in the source and target systems. In this case, Db2 would not be able to define the data set on the target system during the create processing because the data set already exists.
    2. Query the SYSIBM.SYSTABLES table in the Db2 catalog on the source subsystem to get the table OBID for tables that are within the databases that are being moved. Additionally for both the source and target systems, the DBID and PSID are needed for table spaces (from SYSIBM.SYSTABLESPACE) and the DBID and ISOBID are needed for indexes (from SYSIBM.SYSINDEXES). These will be used to build REPAIR jobs to modify these IDs in the header page, and potentially the first space map page in a later step.
    3. Use the OBID clause on the CREATE TABLE statement on the target subsystem to specify an OBID that is the same as the table OBID on the source subsystem. Verify that the OBID that you specify is available. If an OBID is being used for another object within the same database, such as an index or referential constraint, Db2 does not allow you to create the table with the specified OBID.
    Recommendation: To help guarantee the availability of OBIDs for all tables within a database, defer the creation of all indexes and referential constraints until all tables are created. All CREATE TABLE statements must have the OBID clause to guarantee that they are assigned the correct OBID. If an explicit table space name is specified, then the CREATE TABLESPACE statement must come immediately before the first CREATE TABLE statement for that table space. This helps prevent a group of CREATE TABLESPACE statements from using up OBIDs that are needed for the tables.
    For methods two or three, the objects in the target system will be created with several assumptions that might require the additional steps that are outlined below:
    • The VSAM data sets for each new object are created using the ‘I' prefix for each of the data sets. It is possible that on the source system online REORG has executed, causing the data sets to have a ‘J' prefix. If this is the case, you will need to code the DSN1COPY for method two, to have the correct ‘J' data set designation or rename the data set back to the ‘I' prefix for method three in a later step. You should query the source catalog for column IPREFIX in SYSIBM.SYSTABLEPART for table spaces, and SYSIBM.SYSINDEXPART for indexes, to find any ‘J' prefix objects.
    • If any of the index names involved in the move are greater than eight characters in length, Db2 generates a unique INDEXSPACE name to be used in naming the VSAM data set. The INDEXSPACE name generated will most likely be different in the source and target systems. You should query the source and target catalogs to get a list of the INDEXSPACE names for the indexes that have a name greater than eight characters (found in SYSIBM.SYSINDEXES). These will be used in a later step.
  5. On the source Db2 subsystem, run the REORG utility on any table spaces for which a table alteration has occurred with no subsequent REORG.

    If you are unsure if a table meets this criteria, query SYSIBM.SYSTABLES for those tables in which CREATEDTS does not equal ALTEREDTS. However, there is no way to tell from the Db2 catalog what kind of ALTER was performed and whether it would have affected this process. By comparing the CREATEDTS and ALTEREDTS, you will at least gather a list of candidates. If all rows have CREATEDTS equal to ALTEREDTS, running the REORG utility is unnecessary.

  6. Move the data choosing one of the three methods that match the ones listed above:
    • Method 1: Unload / Reload:
      1. Start the objects on the source system in RO mode.
      2. Execute the UNLOAD utility on the source system to unload the data to a sequential file.
      3. Execute the LOAD utility on the target system to load the data that was unloaded when performing the step listed above.
    • Method 2: DSN1COPY with OBID translation :
      1. Stop the object, for both table space and index, on the source and target systems.
      2. Execute DSN1COPY with OBID translation and the RESET option to copy the data from the source system to the target system. If the online REORG utility has been run, be sure to code the proper I/J data set names in the DSN1COPY job. Also, if any indexes were created with names greater than eight characters long you will need to incorporate the INDEXSPACE name differences retrieved in an earlier step.
      3. Start the objects on the target systems R/W.
    • Method 3: Use Db2 VSAM data sets from source system in the target system:
      1. Stop the table spaces and index spaces in the target system.
      2. If the high level qualifier (HLQ) is not changing between the source and target systems, which is not recommended, then perform the following steps on the target system. Note that this process assumes that the objects were created in the target system with DEFINE NO utility.
        1. Change SPRMCTU in the DSN6SPRM macro to one. This enables the catalog to be updated. Also, change DLDFREQ to zero, which disables the down level detection. You must note the original value, because you will change DLDFREQ back to the original value in a later step. Assemble the changed zparm and restart the target system to pick up the change.
        2. Using SQL, update the SPACE column from SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART from -1 to 0 for the table and index spaces that you are moving using method three.
        3. Run the REPAIR DBD REBUILD utility for the databases created earlier. This step is needed because the above step changed the SPACE value in the Db2 catalog from -1 to 0, but the value is also in the DBD. This means that when the REPAIR DBD REBUILD utility is run, it will take the information from the catalog and rebuild the DBD in the directory, making Db2 think that the data set has already been defined.
          Note: Do not try to access the tables at this point. Both systems will think that the data set is theirs, but only one system will actually be able to access the data set. The REPAIR DBD REBUILD utility only should be executed after all table spaces and index spaces that are being moved by method three for a particular database, have their catalogs updated via SQL.
        4. Change SPRMCTU back to zero, and then DLDFREQ to the previous value it was assigned. Next, reassemble the zparm and recycle the target system so it recognizes the change.
      3. Delete the target system VSAM data sets for the table spaces and indexes, unless they were created with DEFINE NO, because the HLQ did not change.
      4. Use the REPAIR utility on the source system to change the identifiers in the page set header page, or header pages, if you have a partitioned page set, to match the new identifiers on the target subsystem.
        The identifiers consist of two, 2-byte fields, HPGDBID and HPGPSID. You must locate and replace these identifiers with the new DBID and PSID of the target system as follows:
        • For non-partitioned page sets, locate and replace the 4-byte fields starting at X'0C', which begins on page zero.
        • For partitioned page sets, locate and replace the 4-byte fields starting at X'0C' on page zero for each partition.
      5. If the table space that you are working with is segmented and compressed, and a dictionary exists, you must also run the REPAIR utility on the source system to change the OBID in the first space map page.
      6. Stop the object, for table space and index, on the source system.
      7. If the HLQs of the source and target systems are different, you must run the IDCAMS ALTER statements. Running the IDCAMS ALTER statements changes the HLQ of the VSAM data sets from the source system to be the new HLQ of the target system. If the HLQ is changing, then the VSAM data sets of the target system must be first deleted before running IDCAMS ALTER. Additionally, if you know from completing one of the previous steps that the online REORG utility has been executed on the source system, make sure that you change any ‘J' data sets to match the target system. The target system should have all ‘I' data sets, as they were newly created. Also, if any indexes were created with names greater than eight characters long, you will need to incorporate the INDEXSPACE name differences retrieved in an earlier step.
      8. Start the table spaces and index spaces in the target system.
      9. Use the REPAIR utility with the LEVELID option to reset the level indicator of the page sets to a neutral value on the target system.
      10. Stop the object, for table space and index, on the source system.
  7. You now have the option to drop the database objects on the source subsystem. If you do not choose to drop the objects at this point, and used method three to move data while keeping the same HLQ for the data sets, then you should consider altering the VCAT name in the source system to a different invalid value. You should alter the VCAT name so that the source Db2 does not try to open the data set that is now in use by the target system.
  8. Take full image copies of all data. This is the earliest time at which data recovery can occur after the merge.
  9. Run the RUNSTATS utility on the target system.
  10. Bind all plans and packages on the target subsystem that were bound on the source subsystem. Bind any plans and packages on the target subsystem that have changed because of name conflict resolution. Grant the appropriate authorizations to all plans and packages.