Scenario: Comparing DDL to a catalog

You can compare the DDL for a single object to the Db2® catalog to make changes on the system for only that object.

About this task

Suppose you want to change a table on your test system. For example, you might add a column in the middle or at the end of the table. So, you generate DDL that shows how the table will look after the change. The DDL will be your source for the comparison. It includes only a CREATE TABLE statement for the table. The DDL does not include any related indexes, foreign keys, or other related objects. Those objects will not be changed, because they are not included in the source DDL.

You then specify that the compare target be selected automatically from the Db2 catalog. In this case, Db2 Object Comparison Tool determines how to change the table. If the table does not currently exist in the target, Object Comparison Tool creates the table. If the table exists, Object Comparison Tool uses the version file instead of the catalog. Object Comparison Tool might alter the table or drop and re-create it, depending on the changes that need to be made. Db2 Object Comparison Tool restores objects and dependencies, such as indexes. If the table needs to be dropped and re-created, Db2 Object Comparison Tool also re-creates objects that have been dropped as a result of dropping the table. The table data is unloaded and, after the object definitions are applied, reloaded back into the table.

Procedure

To compare DDL to a catalog:
  1. Specify the compare source.
    In this scenario, the source is the table definition in the DDL.
    1. On the DB2 Object Comparison Tool Menu (GOCMENU) panel, select option 1 - Specify compare source (new), and press Enter.
    2. On the Specify Compare Source (GOC1) panel, select option 1 - Source is from a DDL file, and press Enter.
    3. On the Specify Source DDL File (GOC11) panel, specify the following information, and press Enter:
      • The name of the data set that contains the DDL.
      • The name of a data set to use for the source version file.

    On the DB2 Object Comparison Tool Menu (GOCMENU) panel, notice that the Specification Status: next to option 1 is now DDL file specified.

  2. Specify the compare target.
    In this scenario, you want Object Comparison Tool to determine the target objects from the Db2 catalog based on the source.
    1. On the DB2 Object Comparison Tool Menu (GOCMENU) panel, select option 2 - Specify compare target (old).
    2. On the Specify Compare Target (GOC1) panel, select option 4 - Target is from the DB2 catalog and objects are automatically selected.
    3. On the Specify Target DB2 Location (GOC14) panel, enter the following information, and press Enter:
      • The location of your subsystem.
      • The name of a data set to use for the target version file. If the data set does not already exist, it is created.

      On the DB2 Object Comparison Tool Menu (GOCMENU) panel, notice that the Specification Status: next to option 2 is now Automatic (DB2 catalog extract).

      For this scenario, do not specify any masks or ignore fields.

  3. Generate the compare job:
    1. On the DB2 Object Comparison Tool Menu (GOCMENU) panel, select option 5 - Generate compare job.
    2. On the Generate Compare Jobs (GOC5) panel, specify the following options, and press Enter:
      Worklist name
      TEST
      Suppress DROP of objects
      YES
      PDS for jobs
      TEST
      Prefix for data sets
      TEST
      Single compare job
      YES
      Member name
      COMPARE

      Set all the remaining options to NO, N, or blank.

  4. Edit the generated compare job as needed, and submit the job.
  5. Check the output to confirm that the job completed successfully.
  6. If needed, make any necessary corrections, generate the compare job again, and recheck the comparison report.
  7. Apply the changes to the target table:
    1. On the DB2 Object Comparison Tool Menu (GOCMENU) panel, select option 5 - Generate compare job.
    2. On the Generate Compare Jobs (GOC5) panel, specify the following additional options, and press Enter:
      Generate apply jobs
      YES
      Generate one job
      YES
      Member prefix
      APPLY
      Content of apply job(s)
      ALL
      Unload method
      U
      IDENTITY START value
      ORIGINAL
      Run REORG/REBUILD
      A
    3. If the Change Management Prompt (ADB2CMRO) panel opens, specify NO.
      (For this scenario, assume that you are not using Change Management.)
    4. On the Specify Data Set Name for Apply Jobs (GOC5AJ) panel, enter the name of the data set where you want the apply job generated.
    5. Edit the generated compare job as needed, and submit the job.
    6. Check the output to confirm that the job completed successfully.
    7. Run the generated apply job to change the target table.