Compare job options
When you generate a compare batch job, you can specify a number of options to control the behavior of the comparison operation and job. These options are listed on the Generate Compare Jobs (GOC5) panel.
GOC5 ---------------- Generate Compare Jobs ----------------------------------
Command ===>
Specify the following for DB2 Object Comparison Tool:
Worklist information:
Worklist name . . . . . . . PQ76055N (also used as middle qualifier in DSNs)
Compare options:
Suppress DROP of objects . NO (Yes/No)
Drop FKs not in source . . NO (Yes/No)
Suppress DROP of columns . NO (Yes/No)
Suppress adding columns . . NO (Yes/No)
Run SQLID . . . . . . . . . (Blank, an SQLID, or <NONE>)
Object Grantor . . . . . . (Blank or an SQLID)
Run Validate. . . . . . . . V (Validate, None)
Allow implicit drop of
excluded objects . . . . NO (Yes/No)
Enable auth-switching . . . YES (Yes/No)
Disable REORG optimization YES (Yes/No)
Scope Warning Messages . . YES (Yes/No)
Change reporting options . . YES (Yes/No)
Save compare results . . . . YES (Yes/No)
Data set information:
PDS for batch jobs . . . . CMP.PQ76055N
Prefix for data sets . . . NBRON
Changes file data set name.
Member name . . . . . . . (if Changes file is an existing PDS)
Options:
Generate online . . . . . . YES (Yes/No)
Single compare job . . . . NO (Yes/No)
Member name . . . . . . . COMPARE (default COMPARE)
Allow deferred restart . . NO (Yes/No)
Generate apply jobs . . . . YES (Yes, No, or (Delta) Change)
Generate one job. . . . . YES (Yes, No, or (Per) Process)
Member prefix . . . . . APPLY (default APPLY)
As work statement list . YES (Yes/No to append to work stmt list)
Embed IFF into WSL . . NO (Yes/No)
Use customized util opts. YES (Yes/No)
Content of apply job(s) . ALL (All, DDL)
Unload method . . . . . : P (Unload, Parallel unload, HPU)
Generate templates. . . . NO (Yes/No)
Stop on conversion error. NO (Yes/No)
Use DEFER YES . . . . . YE (Yes/No)
Allow rotate parts . . . . YES (Yes/No)
Retain GENERATED ALWAYS:
For ROWID . . . . . . . . YES (Yes/No)
For ROW CHANGE TIMESTAMP. YES (Yes/No)
Retain START and RESTART values:
For sequence object . . . (Yes/No)
IDENTITY START value . . . ORIGINA (Original, Computed)
Mask ignored fields . . . . NO (Yes/No)
Optional jobs after Reload or Alter:
Run CHECK DATA . . . . YES (Yes/No)
Take an image copy . . R (after: Reload/Alter/Both/None)
Run REORG/REBUILD . . . M (Mandatory, All relevant, None)
Run RUNSTATS . . . . . R (after: Reload/Alter/Both/Min/None)
Run REBIND . . . . . . N (M, MP, A, R, RP, N)
REBIND options . . . YES (Yes/No)
BP - Change batch job parameters
TU - Specify TEMPLATE usage
UO - Customize utility options
CO - Change options common to change functions
ADV - Advanced options
Simple and advanced display modes
The Generate Compare Jobs (GOC5) panel can be displayed in the following modes:
- Simple mode
- This display mode shows only those options that you must specify to run a basic comparison. You can switch to simple mode by using the SIM command.
- Advanced mode
- This display mode shows all compare job options. You can switch to advanced mode by using the ADV command.
You can also change the display mode on the Panel Display Options (ADBPPDO) panel. For instructions, see Setting panel display options (IBM Db2 Administration Tool for z/OS 13.1.0).
By default, the display mode is initially simple for new users when Db2® Admin Tool is first installed and advanced for existing users when Db2 Admin Tool is upgraded.
When simple display mode is used, Object Comparison Tool uses the default values for those options that are displayed only in advanced mode. The exception is if those advanced options were explicitly changed. If an advanced option was changed to a value that does not conflict with a simple option, that value is used. For example, if you toggle to advanced mode and specify a value for one or more options, those values are saved and used. Similarly, if those advanced options were changed in a previous session and saved, those saved values are used. In the case where a specified value for an advanced options conflicts with any simple options, the default value is used for that advanced option.
The following options are available in simple display mode:
- Worklist name
- Run SQLID
- Change reporting options
- PDS for jobs
- Prefix for data sets
- Changes file data set name and Member name
- Compare job member name
- Generate apply jobs
- Generate one job
- Member prefix
- Use customized util opts
- Generate templates
The following table shows those options that are available only in advanced display mode and the default value for each option. For a detailed description of the option, click the link.
| Option | Default |
|---|---|
| Suppress DROP of objects | YES |
| Drop FKs not in source | NO |
| Suppress DROP of columns | NO |
| Suppress adding columns | NO |
| Object Grantor | blank (an empty string) |
| Run Validate | N |
| Allow implicit drop of excluded objects | NO |
| Enable auth-switching | NO |
| Disable REORG optimization | NO |
| Scope Warning Messages | NO |
| Save compare results | NO |
| Generate online | NO |
| Single compare job | YES |
| Allow deferred restart | NO |
| As work statement list | YES |
| Embed IFF into WSL | NO |
| Content of apply job(s) | ALL |
| Unload method | U (Unload) |
| Stop on conversion error | NO |
| Use DEFER YES | NO |
| Allow rotate parts | YES |
| Retain GENERATED ALWAYS: For ROWID | NO |
| Retain GENERATED ALWAYS: For ROW CHANGE TIMESTAMP | NO |
| Retain START and RESTART values: For sequence object: | NO |
| IDENTITY START value | ORIGINAL |
| Mask ignored fields | NO |
| Run CHECK DATA | YES |
| Take an image copy | B |
| Run REORG/REBUILD | A |
| Run RUNSTATS | B |
| Run REBIND | A |
| REBIND options | NO |
Option descriptions
The options on this panel are described in the following sections:
Worklist information:
- Worklist name
- Specify the name of the work statement list (WSL) to use.
The specified name is also used for the following items:
- The middle qualifier in the names of the work data sets that are created for the job
The prefix for these work data sets is the value in the Prefix for data sets field in the Data set information section of this panel. The complete data set name is the Prefix for data sets value, followed by the Worklist name value, and then a name that indicates the purpose of the data set. For example, for the changes file, the complete name might be NBRON.PQ76055N.CHANGES.
- A name for the Db2 Administration Tool SQL or DDL executor, which has a checkpoint facility.
The Worklist name value is used as a key to the checkpoint table. Use a unique name for each comparison that you run.
Related information: - The middle qualifier in the names of the work data sets that are created for the job
Compare options:
- Suppress DROP of objects
- Specify whether the compare process is to drop objects that are in the target but not in the source.
- Values:
-
- YES
- Objects that are in the target but not in the source are retained. Specify YES to prevent the compare process from dropping any target objects.
- NO
- Objects that are in the target but not in the source are dropped.
- Default value:
- YES
Regardless of the value that you set for this option, Object Comparison Tool replaces all relationships between a parent and a child if a foreign key is specified in the source. To delete a foreign key, both the parent and the child must be present in the source (without a foreign key).
Also, if DROP statements are part of the source DDL, objects are dropped regardless of the value specified for this option.
Object Comparison Tool drops all explicit LOB objects from the target if they are not specified on the source. However, if the base table associated with the LOB objects is kept because Suppress DROP of objects is set to YES, all the LOB objects are kept.
- Drop FKs not in source
- Specify whether the compare process is to drop from the target table any foreign keys that are not specified in the corresponding source table.
- Values:
-
- YES
- Any foreign keys that are not specified in the corresponding source table are dropped.
- NO
- Drop behavior is determined by the Suppress DROP of objects field.
- Default:
- NO
- Suppress DROP of columns
- Specify whether the compare process is to drop columns that are in the target tables but not in the source table.
- Values:
-
- YES
- Any target table columns that are not in corresponding source table are retained. Specify YES to prevent the compare process from dropping any columns.
- NO
- Any target table columns that are not in corresponding source table are dropped.
- Default value:
- NO
- Suppress adding columns
- Specify whether the compare process is to add source columns to the target. This option is useful if you have extra columns on your source that you do not want added to your target.
- Values:
-
- YES
- Columns that exist in the source only are not added to the target. Specify YES to prevent columns in the source from being added to the target.
- NO
- Columns that exist in the source only are added to the target.
- Default value:
- NO
- Run SQLID
- Specify a valid SQL ID to use when creating, dropping, or altering objects. This ID is typically an administrative SQL ID whose only privileges are to create objects.
- Values:
-
- blank
- A SET CURRENT SQLID statement is generated in the DDL before each object that is created. Where possible, the SQL ID that was originally used to create the object is used in the SET statement.
- SQLID
- The specified SQL ID becomes the owner of the databases and table spaces. If the specified SQL ID is different from the current owner, the databases and table spaces (and all dependent objects) are dropped and recreated to change the owner.
- <NONE>
- A SET CURRENT SQLID statement is not generated in the DDL.
- Object Grantor
- Specify an SQL ID to use in SET CURRENT SQLID statements that precede GRANT statements.
- Values:
-
- blank
- No SET CURRENT SQLID statements are generated.
- SQLID
- The specified SQL ID is used in the SET CURRENT SQLID statements.
If an SQL ID is specified for this option, but the Run SQLID option is set to <NONE>, no SET CURRENT SQLID statements are generated to precede GRANT statements.
- Default value:
- blank (an empty string)
- Run Validate
- Specify whether to perform consistency checking. Consistency checking verifies that for all the primary objects in the source DDL, any dependent objects exist. These dependent objects must exist in the source DDL or the target catalog.
- Values:
-
- V (Validate)
-
The following checking is performed:
- The table space in a CREATE TABLE statement exists.
- The table in a CREATE INDEX statement exists.
- The child and parent tables in referential constraints exist.
- If the index in a CREATE INDEX statement is a clustering index, a clustering index does not already exist.
- For primary index and unique index changes, matching keys for primary keys and unique keys exist.
- For primary key and unique key changes, matching indexes for primary keys and unique keys exist.
- The number of index partitions matches the number of table space partitions.
Object Comparison Tool also checks that the dependent objects exist if the following statements are generated:- CREATE TRIGGER
- CREATE VIEW
- CREATE MQT
- CREATE INDEX
- ADD FOREIGN KEY
When you request consistency checking, a consistency checks report (ADB2WVL) is generated. If a check fails, a message is written to the report with a return code of 8.
Note: For native stored procedures, even if validation is successful, the existence of the object in the native stored procedure body cannot be known at the procedure run time (or during the procedure call). - N (NONE)
- Consistency checking is not done.
- Default value:
- N
- Allow implicit drop of excluded objects
- Specify whether excluded objects can be dropped implicitly.
- Values:
-
- YES
- Excluded objects can be dropped if needed and are then recreated according to the target definition.
- NO
- If an excluded object needs to be dropped, an error message is displayed and the compare fails.
- Default:
- NO
- Enable auth-switching
- Specify whether to generate DDL that is used by the authorization switching feature. Authorization switching enables you to run DDL and DCL under the authority of another user.
This field is visible only if the authorization switching facility is enabled for the subsystem during the customization process.
- Values:
-
- YES
- DDL can be used by authorization switching.
- NO
- DDL cannot be used by authorization switching.
- Default value:
- NO
- Disable REORG optimization
- Specify whether you want to disable REORG optimization. REORG optimization reduces the number of REORG utility statements that are issued, and thus the number of times that your system halts.
- Values:
-
- YES
- REORG optimization is disabled.
- NO
- REORG optimization is used.
- Default:
- NO
- Scope Warning Messages
- Specify whether to issue a warning message in the case where the target of an object comparison operation is automatically selected, and the source is not a table space. This message warns that objects that exist only in the target might be dropped.
- Values:
-
- YES
- Message ADB7353 is issued for this situation. This message is issued regardless of the value of the Suppress DROP of objects option.
- NO
- No message is issued for this situation.
- Default:
- NO
Reporting options:
- Change reporting options
- Specify whether you want to change the options for reports.
- Values:
-
- YES
- You are prompted to specify new reporting options on the Specify Compare Reporting Options (GOC5RO) panel after you press Enter. For more information about those options, see Compare reporting options.
- NO
- No options are changed.
- Save compare results
- Specify whether compare results are to be saved.
Restriction: Compare results are saved for only the following objects:
- tables
- indexes
- global variables
- distinct data types
- Values:
-
- YES
- Compare results are saved.
- NO
- Compare results are not saved.
- Default value:
- NO
If you specify YES, the following panel prompts you to specify a name for the saved result after you press Enter:
Figure 2. Save Compare Results (ADB2C22) panel ADB2C22 n ----------------Save Compare Results -------------------------- 08:22 Command ===> Owner . . . OWN1 > (Optional, default is VNDRG, ? to lookup) Name . . . NEW1 > (Required, ? to lookup) Comment . . MY FIRST COMPARE RESULT > Eligible for auto-delete . . 30 (number of days, blank for no auto-delete)
Data set information:
- PDS for jobs
- Specify the name of the partitioned data set (PDS) where the compare jobs are to be generated.
- Prefix for data sets
- Specify the prefix to be used for data sets that are allocated by the batch job. For example, UNLOAD, DDL, and LOAD data sets can be allocated.
- Changes file data set name
- Specify the name of the data set to store the changes that are output by the compare job. This data set is used primarily for changes that are to be imported to Change Management. To generate these changes for Change Management, set Generate apply jobs to Change.
If this data set is partitioned, it must be preallocated, and you must specify a member name in the Member name field.
If you preallocate this data set, the data set must meet the following requirements:- For delta changes, this data set must be either fixed length with an LRECL of 80 or variable length with an LRECL of 16384.
- For changes, this data set must be variable length with an LRECL of 16384.
- Member name
- If the CHANGES data set is partitioned, specify a member name.
Options:
- Generate online
- Specify whether the compare process is to be run online.
- Values:
-
- YES
- The compare process runs immediately when you press the Enter key after specifying the compare job options. If Generate apply jobs and As work statement list are also set to YES, the work statement list is created online but is not run. You can run the work statement list later.
- NO
- A batch job is generated. You can submit this batch job later to perform the compare process in the background.
When the compare process is run in batch, messages are placed in the SYSPRINT data set. You can override this output data set by using the TU command to define the ADBWORK template. If you do not specify YES for the Generate templates option and define ADBWORK, the default data set name, prefix.wsl.SYSPRINT, is used.
- Default:
- NO
Restriction: This Generate online function is not available when comparing multiple sources and targets.Related information: - Single compare job
- Specify whether all job steps are to be run in one job. Use separate jobs to run the source extraction on a system other than the target system.
- Values:
-
- YES
- A single job is generated. Specify a member name for the job in the Member name field.
- NO
- Up to four jobs are generated for the following actions:
- Extract the source if the source is DDL or the Db2 catalog
- Extract the target if the target is DDL or the Db2 catalog
- Compare the source and target
- If Generate apply jobs is set to YES, generate apply jobs or register job when Change Management is enabled
- Default value:
- YES
- Compare job member name
- Member name
-
In simple mode, this field is called Compare job member name. In advanced mode, this field is called member name.
If you requested a single job, specify the name of the member where the compare job is to be generated. The default value is COMPARE.
- Allow deferred restart
- Specify whether the generated compare job is to support deferred step restart.
- Values:
-
- YES
- The generated compare JCL is not to include backward references to previous steps, so that deferred step restart is possible. This option is ignored if Generate online is set to YES or Single compare job is set to NO.
- NO
- Generated compare JCL is to include backward references when allocating temporary data sets. Therefore, deferred restart is not possible without changing these backward references.
- Default:
- NO
Related information: - Generate apply jobs
- Specify whether to generate jobs to apply the changes that were found during the comparison to the target objects.
Instead of generating apply jobs, Object Comparison Tool can also generate a work statement list (WSL) or register a change in the Change Management database on the target subsystem. You can then use any of these sources (the apply jobs, work statement list, or change) to apply changes to the target object.
The Generate apply jobs function uses the following input:
- The shared variables file, which contains the variables that were specified in the panels.
- The changes file from the compare operation, which contains the following information:
- The DROP, CREATE, and ALTER statements
- The UNLOAD requests
- Table space information records, which allow Object Comparison Tool to determine the size of the UNLOAD requests
Restriction: Do not attempt to import a changes file that is generated by a normal comparison job into Change Management as a change. Doing so can lead to loss of data when the change is run.
- Values:
-
- YES
- Jobs are generated to apply the changes.
If you request the generation of apply jobs, run the Object Comparison Tool dialog connected to the target Db2 subsystem to pick up the correct libraries for use in the apply jobs. Alternatively, if the Object Comparison Tool dialog is run on a different Db2 system, you must manually update the apply jobs to use the correct Db2 libraries.
When you specify YES, you also have the option to create a work statement list, use the utility options, select the unload method, and generate templates.
If you specify YES and As work statement list = NO, the following panel prompts you for a data set name for the apply jobs after you press Enter:
Figure 3. Specify Data Set Name for Apply Jobs (GOC5AJ) panel GOC5AJ --------- Specify Data Set Name for Apply Jobs ---------- 12:22 Enter/verify the following: Data Set Name ===> ADM001.COMPARE.D97220 - NO
- No apply jobs are generated.
- CHANGE
- Generate a delta change in the CHANGES file that can later be imported to Change Management. (The CMDELTA parameter for GOC2CMP). No apply jobs or work statement list is generated.
If you specify Change, the compare process is said to run in CMDELTA mode.
The Generate apply jobs function runs as an EXEC (ADBGAJOB) in a TSO/ISPF batch job and uses ISPF skeletons to generate the apply jobs or work statement list.
If you request an apply job where the source or target are from DDL input, they must include all dependent objects. Otherwise, the loss of objects or authorizations can result. In this case, Object Comparison Tool issues the following warning message, which gives you the option to continue or to end the operation:
Figure 4. DB2 Object Compare Warning (GOCGCMPW) panel .----------------------------------------------------------------------------. | ----------------------- DB2 Object Compare Warning ----------------------- | | | | You have asked to generate apply jobs, but the source and / or target | | objects are not being extracted from the DB2 catalog. If your extracts | | do not include all dependent objects and authorizations, this may | | lead to the loss of these objects and/or authorizations. | | | | Press ENTER to continue or END to stop this operation. | | F1=HELP F2=SPLIT F3=END F4=RETURN F5=RFIND F6=RCHANGE | | F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT | '----------------------------------------------------------------------------' .----------------------------. | Please read this carefully | '----------------------------'
- Generate one job
- Specify whether to generate a single apply job.
Restriction: The Generate one job option does not apply when the As work statement list option is set to YES.
- Values:
-
- YES
- A single apply job is generated. If the number of steps exceeds a maximum limit of 255, more than one job is generated.
If you specify Yes, specify a value in the Member prefix field.
- NO
- Multiple jobs are generated. For a list of those jobs by name, see Apply jobs.
- PROCESS
- One job is created per process. For example, all UNLOAD jobs are merged into one job for each process. For a list of the generated, see Apply jobs.
- Member prefix
- Specify a prefix to use for the member name or names for the apply job. The default value is APPLY.
If the number of steps for the apply job exceed the limit of 255, more than one job is generated. For example, if Member prefix is APPLY, the member names are APPLY001, APPLY002 and so on.
Member prefix does not apply if you specify NO or PROCESS for Generate one job.
- As work statement list
- Specify whether to put the apply changes (those changes that are generated when you set Generate apply jobs to Yes) in a work statement list (WSL).
- Values:
-
- YES
- The apply changes are generated in a WSL.
- NO
- The apply jobs are generated in a separate data set.
- Default value:
- YES
If you specify YES, you are prompted for additional information after you press Enter, depending on whether the WSL (provided in the Worklist name option) already exists:
- If the WSL already exists:
- The Specify Work Statement List Data Set (ADB2WLDA) panel prompts you for the data set name. On this panel, you can also indicate whether you want to append to or replace the WSL and whether you want to build a batch job to run the WSL.
Figure 5. Specify Work Statement List Data Set (ADB2WLDA) panel Compare --------------------- Generate Compare Jobs --------------------------- Option ===> Specify the following for DB2 Object Comparison Tool: Worklist information: Worklist name . . . . . . : PQ76055N (also used as middle qualifier in DSNs) Co.-------------------------------------------------------------------------. | DB2 Admin-------- Specify Work Statement List Data Set ---------- 12:18 | | Existing Worklist | | | | Work stmt list dsn . . . WLIST.WSL | Ch| Work stmt list name . . . WLIST | | | Da| Existing name action . . . (Append or Replace) | | Build JCL to run work stmt list . . . (Yes/No) | | | Op'---------------–------------------------------------------------------—-—'Restriction: The replace capability is not supported if you are using the MultiCompare function to compare more than one saved dialog. If you are using MultiCompare, WSLs are automatically appended. To replace WSLs for more than one dialog, you must run the comparisons individually.If you are appending to an existing WSL, the Specify Work Statement List (ADB27WLD) panel prompts you to specify a different middle qualifier to avoid reusing data sets:
Figure 6. Specify Work Statement List (ADB27WLD) panel Compare --------------------- Generate Compare Jobs --------------------------- Option ===> Specify the following for DB2 Object Comparison Tool: Worklist information: Worklist name . . . . . . : PQ76055N (also used as middle qualifier in DSNs) Co.-------------------------------------------------------------------------. | DB2 Admin -------------- Specify Work Statement List -------------------| | Existing Worklist | | | | Appending to an existing WSL may generate duplicate dataset names. | Ch| To avoid this, please specify a new middle qualifier. | | | Da| Middle Qualifier . . . . D5787 | | | '---------------–------------------------------------------------------—-—' - If the WSL does not exist:
- The Specify Work Statement List Data Set (GOC5WL) panel is displayed:
Figure 7. Specify Work Statement List Data Set (GOC5WL) panel Compare --------------------- Generate Compare Jobs --------------------------- Option ===> Specify the following for DB2 Object Comparison Tool: Worklist information: Worklist name . . . . . . : PQ76055N (also used as middle qualifier in DSNs) Co.-------------------------------------------------------------------------. | Compare --------- Specify Work Statement List Data Set ---------- 13:48 | | | | Enter/verify the following: | | Work stmt list dsn . . | Ch| | | Build JCL to run work stmt list (Yes/No) | Da'-------------------------------------------------------------------------'If you specify YES for Build JCL to run work stmt list, the Specify Job Parameters (ADB2W1R) panel prompts you to specify the job library partitioned data set (PDS) and member prefix:
Figure 8. Specify Job Parameters (ADB2W1R) panel ADB2W1R ---------------- Specify Job Parameters --------------- 09:08 Enter/verify the following: Generate one job ===> NO (Yes,No or Per Process) Job library PDS ===> Member prefix ===> RLS1 (Prefix, max 6 chars) Jobname = member? ===> (Yes/No)
If you specify NO, The Specify Data Set Name for Apply Jobs (GOC5AJ) panel prompts you for that data set name after you press Enter. If the data set does not exist, it is created.
Compare --------------------- Generate Compare Jobs --------------------------- Option ===> Specify the following for DB2 Object Comparison Tool: Worklist information: Worklist name . . . . . . : PQ76055N (also used as middle qualifier in DSNs) .-------------------------------------------------------------------------. Co | Compare --------- Specify Data Set Name for Apply Jobs ---------- 12:18 | | | | Enter/verify the following: | | Data Set Name ===> APPLY.DEF1045 | | | Ch | | | | Da '-------------------------------------------------------------------------'- Embed IFF into WSL
- Specify whether the WSL is to include the contents of the IFF file in an encoded format. An IFF file is produced if the change requires an unload operation. The benefit of embedding the IFF contents in the WSL is that you can transport the WSL to another system without having to separately transport the IFF file.
- Values:
-
- YES
- The IFF file is embedded in the WSL.
- NO
- The WSL does not include the IFF file.
- Default:
- NO
- Use customized util opts
- Specify whether you want to use the options that you set for the COPY, CHECK DATA, MODIFY, REBUILD, REORG, RUNSTATS, UNLOAD, and LOAD utilities on the Db2 Administration Tool panels.
- Values:
-
- YES
- Utility jobs and WSLs are generated based on the utility options that you specified.
- NO
- The default utility options are used.
Related information: - Content of apply job(s)
- Specify whether to generate only changes to database objects.
- Values:
-
- ALL
- All jobs and processes to reload the data are generated.
- DDL
- Only the DDL is generated. Object Comparison Tool does not generate UNLOAD statements, LOAD statements, or other utilities except for rebind and REORG operations that are needed to apply the pending definition changes and remove any restrictive states. These operations are necessary to allow the subsequent statements to be successful.
When DDL is specified, any data conversion errors are ignored and no conversion report is generated.
- Default value:
- ALL
- Unload method
- Specify the method that you want to use to unload data.
- Values:
-
- U (Unload)
- Use the Db2 UNLOAD utility.
- P (Parallel unload)
- Use the Db2 UNLOAD utility with parallel processing.
Parallel unload cannot be used in the following situations:
- A limit key change
- A change in number of partitions
- The use of an identity column in the partitioning key
If Db2 Object Comparison Tool determines that the operation is not eligible for a parallel unload, it uses U (Unload) instead.
If the operation is eligible for a parallel unload, a template is used to allocate the unload data sets.
- H (HPU)
- Use Db2 High Performance Unload for z/OS®(HPU) to unload the data. HPU must be available.
- Default value:
- U
If P (Parallel Unload) and H (HPU) are not valid options for the current unload, Object Comparison Tool automatically uses U (Unload).
- Generate templates
- Specify whether you want the compare process to generate templates for data sets.
- Values:
-
- YES
- Templates are generated for non-utility data sets with the definitions that you specified in Db2 Administration Tool.
- NO
- The values for the Prefix for data sets and Worklist name options are used for the data set names.
If the Take an image copy or Run REORG options are set to be run, the utility templates are used.
- Stop on conversion error
- Specify whether you want the compare process to stop if a conversion error occurs.
- Values:
-
- YES
- If a conversion errors occurs, the APPLY job is not generated, and an error message similar to the following message is displayed:
Compare table source(aaaaaa.bbbbbb) and target(aaaaaa.bbbbbb) Column COLNAME Conversion not supported for Col COLNAME (TIME to INTEGER) (D)Type changed from TIME to INTEGER (E)This type change is not supported Tables have identical column lists Table aaaaaa.bbbbbb will be dropped Table will be recreated Table data conversion jobstep will not be generated Conversion will fail because of datatype mismatch Run stopped because conversion(s) not supported
- NO
- The compare process does not stop if a conversion error occurs.
- Default value:
- NO
- Use DEFER YES
- Specify whether to use DEFER YES clauses on any eligible CREATE INDEX statements.
- Values:
-
- YES
- DEFER YES is used for eligible indexes. However, any user-defined masks for the DEFER attribute take precedence over the value of this option.
If you specify DEFER YES = YES and Run REORG/REBUILD = A, REBUILD INDEX jobs are generated. The DDL still contains the DEFER YES clause, but that clause is ignored and the indexes are rebuilt.
- NO
- DEFER YES is not used.
- Default value:
- NO
- Allow rotate parts
- Specify whether to generate the ROTATE PARTITION statement or the ALTER PARTITION statement when the condition for a rotation is met.
- Values:
-
- YES
- Generate the ROTATE PARTITION statement. Data from the rotating partitions is unloaded before the rotation. You can discard this data or to load it back into the new partitions.
- NO
- Generate the ALTER PARTITION statement and a REORG statement for the affected partitions. For the Run REORG/REBUILD option, specify either MANDATORY or ALL RELEVANT to generate the REORG statements. Data from the rotating partitions is loaded back into the table so that you do not have to manually perform the reload. Logical and physical partitions are preserved.
- Default value:
- YES
If your table is not partitioned, specify YES.
- Retain GENERATED ALWAYS:
-
- For ROWID
- Specify whether to retain the GENERATED ALWAYS attribute for ROWID columns.
- Values:
-
- YES
- Retain the GENERATED ALWAYS attribute.
- NO
- Do not retain the GENERATED ALWAYS attribute.
- Default value:
- NO
- For ROW CHANGE TIMESTAMP
- Specify whether to retain the GENERATED ALWAYS attribute for ROW CHANGE TIMESTAMP columns.
- Values:
-
- YES
- Retain the GENERATED ALWAYS attribute.
- NO
- Do not retain the GENERATED ALWAYS attribute.
- Default value:
- NO
- Retain START and RESTART values:
-
- For sequence object:
- Specify whether to retain START and RESTART values for the sequences.
- Values:
-
- YES
- Retain START and RESTART values.
- NO
- Do not retain START and RESTART values.
If you specify NO and ignores are specified for the START or RESTART fields, the ignored fields are not changed. If you specify NO and ignores are not specified for the START and RESTART fields, the values on the target are changed according to the source.
- Default value:
- NO
- IDENTITY START value
- Specify the value to use for the identity column when the table is re-created.
- Values:
-
- ORIGINAL
- Use the START value for the identity column from the Db2 catalog.
- COMPUTED
- Compute the START value based upon the identity attributes of the column.
The computed value is based on the existing identity column attributes, such as MAXASSIGNED and the current cache size, at the time that the DDL is produced. Any changes made after the creation of the DDL, to either the identity values or to the data, are not reflected in the DDL and make the DDL obsolete. Object Comparison Tool does not locate an unassigned value from the existing data, and a new RESTART value might need to be provided based upon the underlying data and the application needs.
- Default value:
- ORIGINAL
- Mask ignored fields
- Specify whether to apply masked values to ignored fields for newly added objects if the field has been masked and ignored.
- Values:
-
- YES
- Apply the masked values.
- NO
- The original values from the source are applied.
- Default:
- NO
This option is not applicable to ignore files that are provided in the CM Register Options (ADB2CRO) panel.
Optional jobs after Reload or Alter:
- Run CHECK DATA
- Specify whether to generate a CHECK DATA utility job for all table spaces that are affected by the LOAD utility jobs that are generated by Object Comparison Tool to reload the data.
- Values:
-
- YES
- Generate a CHECK DATA job.
- NO
- Do not generate a CHECK DATA job.
- Default value:
- YES
Recommendation: Specify YES if LOAD uses ENFORCE NO. - Take an image copy
- Specify whether to generate COPY utility jobs.
- Values:
-
- R (Reload)
- Generate a COPY job for all tables that are affected by the generated LOAD jobs to reload the data.
- A (Alter)
- Generate a COPY job for all table spaces, tables, and indexes that are altered with generated ALTER statements.
- B (Both)
- Generate a COPY job for all tables that are affected by the LOAD job and all altered table spaces, tables, and index objects.
- N (None)
- Do not generate any COPY jobs.
- Default value:
- B
- Run REORG/REBUILD
- Specify whether to generate REORG TABLESPACE utility jobs and REBUILD INDEX utility jobs, if needed. These jobs are run after applying the changes from an object comparison to make the target system operational.
- Values:
-
- M (Mandatory)
- Generate all REORG and REBUILD jobs that are needed to remove any REORG-pending and REBUILD-pending states and make the data available.
- A (All relevant)
- Generate all REORG and REBUILD jobs that are needed to fully implement the changes. For example, changing PRIQTY is registered when the table space is altered, but the new value is not used until the table space is reorganized.
- N (None)
- Do not generate any REORG and REBUILD jobs. NONE is not valid if you specified NO for Allow rotate parts.
- Default value:
- A
- Run RUNSTATS
- Specify whether to generate RUNSTATS utility jobs.
- Values:
-
- R (Reload)
- Generate a RUNSTATS job for all tables that are affected by the generated LOAD jobs to reload the data.
- A (Alter)
- Generate a RUNSTATS job for all table spaces, tables, and indexes that are altered with generated ALTER statements.
- B (Both)
- Generate a RUNSTATS job for all tables that are affected by the LOAD job and all altered table spaces, tables, and index objects.
- M (Min)
- Generate RUNSTATS jobs for the following conditions:
- If a table space is dropped and recreated, generate RUNSTATS statements for the tables and indexes.
- If a table is dropped and recreated, generate RUNSTATS statements for only the indexes and not the table.
- If an index is created, recreated, or had columns added, generate RUNSTATS statements for the index.
- If the index is created with DEFER YES and REBUILD is generated, the RUNSTATS operation is performed after the REBUILD operation.
- N (None)
- Do not generate any RUNSTATS jobs.
- Default value:
- B
- Run REBIND
- Specify whether to generate a job to rebind the packages that are affected by the changes from the comparison.
- Values:
-
- M (Mandatory)
- Generate a REBIND job for only those packages that were invalidated by the changes.
- MP (Mandatory package level)
- Generate a REBIND job for only those packages that were invalidated by the changes except for packages with statement-level dependencies.
- A (All relevant)
- Generate a REBIND job for all packages that were affected by the changes, including those packages that were invalidated.
- R (Recommended)
- Generate a REBIND job for the following packages:
- packages invalidated by the changes
- packages that require a rebind to implement the changes from the ALTER statement (VALID='A' in SYSPACKAGE)
- packages that reference a table on which a new index was added. A rebind refreshes the access path.
- RP (Recommended package level)
- Generate a REBIND job for the following packages:
- packages invalidated by the changes except for packages with statement-level dependencies
- packages that require a rebind to implement the changes from the ALTER statement (VALID='A' in SYSPACKAGE)
- packages that reference a table on which a new index was added. A rebind refreshes the access path.
- N (None)
- Do not generate a REBIND job.
- Default value:
- A
- REBIND options
- Indicate whether you want to specify your own BIND options for any packages that are rebound. To use this field, you must specify M (Mandatory) or A (All relevant) in the Run REBIND field.
- Values:
-
- YES
- Allows you to specify BIND options on the subsequent REBIND options (ADBPREBO) panel.
- NO
- Any affected packages are rebound with their existing BIND options (the options that were used during the previous bind or rebind operation).
- Default value:
- NO
If you specify YES, the REBIND options (ADBPREBO) panel is displayed after you press Enter:
Figure 9. REBIND options (ADBPREBO) panel ADBPREBO ------------------------ REBIND options ------------------------ Command ===> Specify additional REBIND parameters to generate rebinds for dependent packages. APREUSE . . . . (None, Warn, Error) EXPLAIN . . . . (Yes, No, Only) OWNER . . . . . > (Owner of package) OWNERTYPE . . (Role, User) Additional options: _________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________ _____________________________________________________________________________When this panel is displayed, specify any BIND options for dependent packages, and press Enter:
Note: Any BIND options that you specify in the Additional options field are added to the REBIND statement as is; they are not validated.
Commands
You can use the commands listed at the bottom of this panel to specify additional job options:
BP - Change batch job parameters TU - Specify TEMPLATE usage UO - Customize utility options CO - Change options common to change functions ADV - Advanced options SIM - Simplified options
You can enter any of these commands on the command line:
- BP - Change batch job parameters
- Allows you to change the parameters for batch utility jobs, such as the job card and space parameters.
When you specify the BP command and press Enter, the Batch Job Utility Parameters (ADB2UPA) panel is displayed:
ADB2UPA n -------------- DD1A Batch Job Utility Parameters -------------- 11:02 Command ===> More: + Generate Job Card . . . YES (Yes/No) DB2 System: DD1A Job cards: DB2 SQL ID: TS6462 ===> //TS6462D JOB 'CMPTEST',MSGLEVEL=(1,1),CLASS=A, Y', ===> // USER=TS6462,REGION=0M ===> ===> ===> Generate Job CLASS . . NO (Yes/No) JOB CLASS . . . . . . . B JOBPARM: ===> ===> ===> ===> CM Batch EXEC statement parameters: Add SSID parameter . . (Yes/No) Add PLAN parameter . . (Yes/No) Additional parameters to add to the EXEC statement: ===> ===> ===> ADBTEP2: Restart . . . . . . . YES (Yes/No/Force) Maxerrors . . . . . . 99 (-1 to 99) BindError . . . . . . MAXE (MAXE, Save or Ignore) Log DIAG . . . . . . . YES (Yes/No) AutoCheck . . . . . . YES (Yes/No) LOAD Summary Report YES (Yes/No) Auto Rebuild . . . . . YES (Yes/No) Auto Reorg . . . . . . YES (Yes/No) Advisory Auto Rebuild YES (Yes/No) Advisory Auto Reorg YES (Yes/No) Auto Reorg/Rebuild after STOGROUP change. YES (Yes/No) Auto Rebind . . . . . NO (Yes/No/All/M/YP/AP/MP) APREUSE . . . . . . (None, Warn, Error) EXPLAIN . . . . . . (Yes/No/Only) OWNER . . . . . . . > OWNERTYPE . . . . . (Role, User) Additional options . > Preserve tablespace access state . . . . . YES (Yes/No) LOB/XML IC Unload . . E (Error, Use base data) Missing IC Unload . . E (Error, Use base data) Spanned . . . . . . . YES (Yes/No) DB2 Pending Changes options: Check at DROP . . . NO (Yes/No) Use DSNTPSMP . . . . . YES (Yes/No) Timeout Retries . . . 0 (0 to 99 times) Timeout Wait Time . . 120 (1 to 3600 secs) Retry Deprecated Obj . YES (Yes/No) Use ADBSYSPT DD . . . NO (Yes/No) Space parameters: Unit name . . . . . . SYSDA Space unit . . . . . . TRK (BLK, TRK, CYL or 4096-32760) Max Primary . . . . . 65535 (In above units, 999999999 or blank) In KB: 3145680 Max DASD . . . . . . . 65535 (In above units. Allocations beyond this are sent to tape) In KB: 3145680 Tape Unit . . . . . . TAPE (Unit for tape if size is greater than Max DASD) Additional tape parameters . . . . Calc space . . . . . . YES (Yes/No) Default space allocation if unable to calculate: Primary alloc . . . . 30 (In above units) Secondary alloc . . . 30 (In above units) Function-specific parameters: Unload pct . . . . . . (0-99 - % increase for converted data set)Related information: - TU - Specify TEMPLATE usage
- Allows you to modify templates for the data sets that are allocated and used by Db2 Object Comparison Tool.
When you specify the TU command and press Enter, the Specify UTILITY TEMPLATE Usage (ADB25TU3) panel is displayed:
ADB25TU3 DD1A Specify UTILITY TEMPLATE Usage 11:58 Command ===> Line commands: T - Toggle Use On/Off C - Clear data ? - Choose Template for the Keyword E - Edit Template Template type ===> OC (UTIL, ALT, MIG, RES, OC) Generate template statements ===> NO (Yes/No) Sel Keyword Use Template Comment --- ------------ --- -------- ------------------------------------------------- More: + GOCALTR GOCCREA GOCDROP GOCRBND GOCIFFN GOCSHVR GOCCHNGFrom this panel, you can modify the templates for data sets. The default work data sets and descriptions are shown in the following table:
You can specify the following variables in templates:Table 2. Work data set descriptions Template keyword Default data set Description GOCALTR prefix.worklist.DDL.ALTER Primarily ALTER statements GOCCREA prefix.worklist.DDL.CREATE Primarily CREATE statements GOCDROP prefix.worklist.DDL.DROP Primarily DROP statements GOCRBND prefix.worklist.CMD.REBIND REBIND control statements GOCIFFN prefix.worklist.IFF Internal version file GOCSHVR prefix.worklist.SHRVARS ISPF variables GOCCHNG prefix.worklist.CHANGES Changes from compare - The following functional variables:
- &GOCPRE
- The prefix for data sets, which you specify on the Generate Compare Jobs (GOC5) panel
- &GOCWLN
- The statement work list name, which you specify on the Generate Compare Jobs (GOC5) panel
- The date and time variables that are supported for the Db2 TEMPLATE utility.
- &USERID
- The following functional variables:
- UO - Change utility options
- Allows you to specify options for Db2 utilities.
When you specify the UO command and press Enter, the Change Utilities Options (ADB2UOPS) panel is displayed:
ADB2UOPS ---------------- DD1A Change Utilities Options ---------------- 11:15 Select one of the following, then press Enter. C - Image copy KD - Check data M - Modify O - Reorg tablespace OI - Reorg index RB - Rebuild index R - Runstats tablespace U - Unload L - Load Option ===>On this panel, you can select the Db2 utility for which you want to change the options. When you press Enter, the Specify Utility Options panel for the selected utility is displayed, and you can enter the options that you want. Press Enter to save your selections.
Restriction: Some utility options are not available for utility jobs that are built by Db2 Object Comparison Tool.For Db2 Object Comparison Tool to generate utilities with the options that you specified, on the Generate Compare Jobs (GOC5) panel, you must set the Use utility options to YES. The options that you select are retained and used for any subsequent jobs where Use utility options is set to YES.
- CO - Change options common to change functions
- Allows you to review and change options that are common to change functions in Db2 Administration Tool and Db2 Object Comparison Tool.
When you specify the CO command and press Enter, the Options for Change Functions (ADB2PCO) panel is displayed:
ADB2PCO n Options for Change Functions 14:19 Command ===> DB2 System: DD1A Recreate accelerated tables . . . . . YES (Yes/No. Default is Yes) Restore replication of tables . . . . YES (Yes/No. Default is Yes) Reload accelerated tables . . . . . . YES (Yes/No. Default is Yes) Restore acceleration of tables . . . . YES (Yes/No. Default is Yes) Remove deleted accelerated tables . . YES (Yes/No. Default is Yes) Load accelerated tables LOCKMODE . . . NONE (Default is TABLESET) Load accelerated tables DETECTCHANGES DATA (Default is DATA) Unload altered tables . . . . . . . . NO (Yes/No/Des. Default is YES) Preserve all data . . . . . . . . . . YES (Yes/No. Default is YES) Enable WSL authorization switching . . NO (Yes/No. Default is No) Object processing order . . . . . . . H (T - Object type, H - DB hierarchy. Default is H) Statement validation exit name . . . . (Name of EXEC used to validate statements in WSL Validate) Allow PBR RPN to PBR changes . . . . . NO (Yes/No. Default is No) DB2 release number . . . . . . . . . . 1215 (Use VVRM format) DB2 function level . . . . . . . . . . 504 (E.g. 100, 500, 501, 5nn) GRANT processing order . . . . . . . . C (C - CREATE prefix for GRANT P - POSTUTIL prefix for GRANT Default is C ) Auto view regenerate . . . . . . . . . NO (Yes/No. Default is No) Ignore source column order . . . . . . NO (Yes/No. Default is No) Generate hidden ROWID columns . . . . NO (Always, Only, No. Default is No) - ADV - Advanced options
- Toggles the display mode to show all compare job options.
- SIM - Simplified options
- Toggles the display mode to show only the basic compare job options.