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)
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 . . . . . YES (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 . . . ORIGINAL (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 . . . . . . M (Mandatory, All relevant, None)
BP - Change batch job parameters
TU - Specify TEMPLATE usage
UO - Customize utility options
CO - Change options common to change functions 
The options on this panel are described in the following sections:
Worklist information:
- Worklist name
- Specify the name of the work statement list to use.
This name is also used as 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, the Worklist name value, and a name that indicates the purpose of the data set. For example, for the changes file, the complete name might be NBRON.PQ76055N.CHANGES.
The Worklist name value is also used as 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:
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. Specify Yes to prevent the compare process from
dropping any target objects.
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 of 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. If you specify
No, the drop behavior is determined by Suppress DROP
of objects field. The default is 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. Specify Yes to prevent the compare process from dropping any columns.
- Suppress adding columns
- Specify whether the compare process is to add columns to the target. Specify Yes to prevent columns in the source from being added to the target. This option is useful if you have extra columns on your source that you do not want added to your target.
- 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.
If a value is specified, this 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.
If you enter <NONE>, a SET CURRENT SQLID statement is not generated in the DDL.
If you leave this field 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.
- Object Grantor
- Specify an SQL ID to use in SET CURRENT SQLID statements that precede GRANT
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.
- Run Validate
- Specify whether to perform consistency checking. Consistency checking verifies that
for all of the primary objects in the source DDL, any dependent objects exist. These
dependent objects must exist in the source DDL or the target catalog.
When you specify V or 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 procedure run time (or during procedure call). - Allow implicit drop of excluded objects
- Specify whether excluded objects can be dropped implicitly.
If you specify Yes, excluded objects can be dropped if needed and are then recreated according to the target definition. If you specify No and an excluded object needs to be dropped, an error message is displayed and the compare fails. No is the default.
- Enable auth-switching
- Specify whether to generate DDL that is used by the authorization switching feature.
Authorization switching enables you to execute 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.
- 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. The default value is 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.
If you specify Yes, message ADB7353 is issued for this situation. This message is issued regardless of the value of the Suppress DROP of objects option.
Reporting options:
- Change reporting options
- Specify whether you want to change the options for reports.
If you specify Yes, the following panel is displayed after you press Enter. You can change the reporting options on this panel:
Figure 2. Specify Compare Reporting Options (GOC5RO) panel GOC5RO --------- Specify Compare Reporting Options ---------- 12:20 Report options for Compare: Only changed objects . . . YES (Yes/No) Ignore fields: User specified . . . . . YES (Yes/No) System generated . . . . YES (Yes/No) Object specific . . . . . YES (Yes/No) Translation masks . . . . YES (Yes/No) Summary report . . . . . . YES (Yes/No) Object count report . . . . YES (Yes/No) Conversion report . . . . . YES (Yes/No)- Only changed objects
- Specify whether the detailed report is to include only those objects that have changed.
- Ignore fields:
-
- User specified
- Specify whether the report is to include the names of user-specified ignore fields.
- System generated
- Specify whether the report is to include the names of system ignore fields.
- Object Specific
- Specify whether the report is to include the names of fields that are ignored for specific objects.
Related information: - Translation masks
- Specify whether the report is to include the translation masks that are used by
the compare job. Related information:
- Summary report
- Specify whether the report is to include a summary, which consists of one line per object.
- Object count report
- Specify whether the report is to include statistics of compared and changed objects.
- Conversion report
- Specify whether to report expected conversion problems for tables when a change is run.
The following example shows the corresponding strings for the parameters that are passed to step T03COMP PGM=GOC2CMP if you specify YES for the fields on panel GOC5RO. Specifying Yes for both User specified and System generated results in REPIGALL being used as the parameter.
Figure 3. Example of the Specify Compare Reporting Options (GOC5RO) panel with REPIGALL used as the PARM options. Compare --------------------- Generate Compare Jobs --------------------------- Option ===> Specify the following for DB2 Object Comparison Tool: Worklist information: Worklist name . . . . . . : AAAAAAAA (also used as middle qualifier in DSNs) Co.-------------------------------------------------------------------------. | Compare --------- Specify Compare Reporting Options ---------- 12:18 | | | | Report options for Compare: | | Only changed objects . . : REPCHG | Ch| Ignore fields: | | User specified . . . . : REPIGUSR | | Object Specific . . . . : Yes (Yes/No) | Da| System generated . . . : REPIGSYS | | Translation masks . . . : REPMASK | | Summary report . . . . . : REPSUM | Op| Object count report . . . : REPCOUNT | | Conversion report . . . . : REPCONV | | | | | | | '-------------------------------------------------------------------------' - 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
If you specify YES, the following panel prompts you to specify a name for the saved result:
Figure 4. 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.
If you specify 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.
If you specify No, a batch job is generated. You can submit this batch job later to perform the compare process in the background. The default value is No.
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.
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 executed in one job.
Use separate jobs to run the source extraction on a system other than the target system.
If you specify 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
If you specify Yes, you must specify a member name in the Member name field.
- Member name
- If you requested a single job, specify the name of the member where the compare job is to be generated. The default is COMPARE.
- Allow deferred restart
- Specify whether the generated compare job is to support deferred step restart. You can
specify one of the following values:
- YES
- Generated compare JCL will not include backward references to previous steps so that deferred step restart is possible. This option is ignored if Generate online is YES or Single compare job is NO.
- NO
- Generated compare JCL will include backward references when allocating temporary data sets. Therefore, deferred restart is not possible without changing these backward references. NO is the default.
-
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 or register a change in the Change Management database on the target subsystem. You can then use the apply jobs, work statement list, or change to apply changes to the target object. This process is shown in Figure 1.
The Generate apply jobs function uses the following input:
- 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. - The shared variables file, which contains the variables that were specified in the panels.
You can specify one of the following values:
- Yes
- Generate apply jobs.
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:
Figure 5. 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
- Do not generate jobs to apply the changes.
- 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 6. DB2 Object Compare Warning (GOCGCMPW) panel Compare -------------------- Generate Compare Jobs --------------------------- Option ===> Specify the following for DB2 Object Comparison Tool: More: + Worklist information: Worklist name . . . . . . : PQ76055N (also used as middle qualifier in DSNs) .----------------------------------------------------------------------------. | ----------------------- 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 | '----------------------------------------------------------------------------' .----------------------------. YSDA | Please read this carefully | YSDA Serial (tape) device : No (Yes/No) '----------------------------'- Generate one job
- Specify whether to generate a single apply job.Restriction: The Generate one job option does not apply when As work statement list is set to Yes.
You can specify one of the following 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. Specifically, Object Comparison Tool produces the following
apply jobs:
- T10Unnnn
- UNLOAD jobs, which contain the following steps:
- Step 1 issues a Db2 command to place the table space in read-only status.
- Step 2 deletes the SYSREC and SYSPUNCH data sets, if they already exist.
- Step 3 unloads the given table.
- Additional steps create Db2 LOAD utility control statements for the unloaded data.
Note: Version files that are created outside the scope of the Db2 catalog, such as those created from DDL, do not represent the same information that is found in the Db2 catalog. - T20DROP
- DROP job. This job includes statements that need to be executed before objects are created. It can include DROP, RENAME, and ALTER statements. This job contains DD statements that reference all unload data sets to ensure that all UNLOAD jobs have run before the objects are dropped.
- T30CREAT
- CREATE job. This job
creates the objects and their authorizations. It can also contain
ALTER SQL statements.If an
inline utility needs to be run between DDL statements, the T30CREAT
job is split into multiple jobs, named
T30Cnnnn.
- T40STOP
- STOP job. This job stops page sets. It can contain other SQL and utility statements.
- T50ALTER
- ALTER job. This job can
contain CREATE and ALTER SQL statements. If an
inline utility needs to be run between DDL statements, the T50ALTER
job is split into multiple jobs, named
T50Annnn.
- T60START/T61START
- START jobs. These jobs start page sets. The T61START job is for clone objects.
- T70Rnnnn
- RELOAD jobs.
- T71Rnnnn
- REORG jobs. These REORG jobs remove REORG-pending conditions. All REORG statements are combined into a single job if SHRLEVEL CHANGE is specified and the mapping table name is provided.
- T72REBLD
- REBUILD jobs.
- T8ROnnnn
- REORG jobs. These REORG jobs fully implement the effects of the changes (for example, space parameter changes). All REORG statements are combined into a single job if SHRLEVEL CHANGE is specified and the mapping table name is provided.
- T81REBLD
- REBUILD job. This job rebuilds indexes.
- T85REFR
- REFRESH job. This job refreshes tables.
- T89POSTI
- Jobs to grant authorizations on tables and to reload accelerators.
- T90RB
- Rebind jobs.
- Utilities
- After the LOAD jobs have run, optional jobs are created to run CHECK (T71CHECK), COPY (T73IMC), and RUNSTATS (T87RUNST). You can run these jobs in parallel.
- (Per) Process
- One job is created per process. For example, all UNLOAD jobs are merged
into one job for each process. However, if the number of steps in an UNLOAD,
reload, or REORG job (T10U0001, T70R0001, or T80RO001) exceed a maximum of
255, a second job corresponding to each process (T10U0002, T70R0002, or
T80R0002) is generated accordingly.
The following jobs are created:
- T10U0001
- UNLOAD job.
- T20DROP
- DROP job.
- T30CREAT
- CREATE job.
- T40STOP
- STOP job.
- T50ALTER
- Alter job.
- T60START
- START job.
- T70R0001
- Reload job.
- T89POSTI
- Table GRANTs and reload accelerator job.
- T71R0001
- REORG job.
- T72REBLD
- REBUILD job.
- T73CHECK
- CHECK job.
- T74IMC
- IMAGECOPY job.
- T80R0001
- REORG job.
- T81B0001
- REBUILD job.
- T87RUNST
- RUNSTATS job.
- Member prefix
- Specify a prefix to use for the member name or names for the apply job.
The default 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. You can either append to
or replace the work statement list.
You can specify one of the following values for this option:
- Yes
- If the work statement list (provided in the Worklist
name option) 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 work statement list and whether you want to build a batch job
to run the work statement list.
Figure 7. 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, work statement lists are automatically appended. To replace work statement lists for more than one dialog, you must run the comparisons individually.If you are appending to an existing work statement list, the Specify Work Statement List (ADB27WLD) panel prompts you to specify a different middle qualifier to avoid reusing data sets:
Figure 8. 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 work statement list (provided in the Worklist name option) does not exist, the Specify Work Statement List Data Set (GOC5WL) panel is displayed:
Figure 9. 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 10. 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) - No
- The apply jobs are generated in a separate data set. The Specify Data Set Name for Apply Jobs
(GOC5AJ) panel prompts you for that data
set name. 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 '-------------------------------------------------------------------------'
- 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. If you specify
Yes, utility jobs and work statement lists are generated
based on the utility definitions that you specified. Otherwise, the default
utility options are used. Related information:
- Content of apply job(s)
- Specify whether to generate only changes to database objects.
You can specify one of the following values for this option:
- All
- Generate all jobs and processes to reload the data.
- DDL
- Generate only DDL. 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.
- Unload method
- Specify the method that you want to use to unload data.
You can specify one of the following values for this option:
- Unload
- Use the Db2 UNLOAD utility.
- 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 Unload instead.
If the operation is eligible for a parallel unload, a template is used to allocate the unload data sets.
- HPU
- Use Db2 High Performance Unload for z/OS® to unload the data. Db2 High Performance Unload for z/OS must be available.
If Parallel unload and HPU are not valid options for the current unload, Object Comparison Tool automatically uses the UNLOAD utility.
- Generate templates
- Specify whether you want the compare process to generate templates for data
sets.
If you specify Yes, templates are generated for non-utility data sets with the definitions that you specified in Db2 Administration Tool.
If you specify No, the values for the Prefix for data sets and Worklist name options are used.
If the Take an image copy or Run REORG options are set to Yes, the utility templates are used.
- Stop on conversion error
- Specify whether you want the compare process to stop if a conversion error
occurs.
If you specify Yes and 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
- Use DEFER YES
- Specify whether to use DEFER YES clauses on any eligible CREATE INDEX
statements.
If you specify 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 will be generated. The DDL will still
contain the DEFER YES clause, but that clause will be ignored and the indexes
will be rebuilt.
- The changes file from the compare operation, which contains the following information:
- Allow rotate parts
- Specify whether to generate the ROTATE PARTITION statement or the ALTER PARTITION
statement when the condition for a rotation is met.
- 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.
If your table is not partitioned, specify Yes.
- Retain GENERATED ALWAYS:
-
- For ROWID
- Specify whether to retain the GENERATED ALWAYS attribute for ROWID columns. Specify Yes or No.
- For ROW CHANGE TIMESTAMP
- Specify whether to retain the GENERATED ALWAYS attribute for ROW CHANGE TIMESTAMP columns. Specify Yes or No.
- Retain START and RESTART values:
-
- For sequence object:
- Specify whether to retain START and RESTART values for the sequences. Specify
Yes or No.
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.
- IDENTITY START value
- Specify the value to use for the identity column when the table is re-created.
You can specify one of the following values for this option:
- 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 ultimately a new RESTART value might need to be provided based upon the underlying data and the application needs.
- Mask ignored fields
- Specify whether to apply masked values to ignored fields for newly added objects if
the field has been masked and ignored.
If you specify No, the original values from the source are applied. No is the default.
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 Db2
Object Comparison Tool to reload the data. Recommendation: Specify Yes if LOAD uses ENFORCE NO.
- Take an image copy
- Specify whether to generate COPY utility jobs.
You can specify one of the following values for this option:
- Reload
- Generate a COPY utility job for all tables that are affected by the generated LOAD utility jobs to reload the data.
- Alter
- Generate a COPY utility job for all table spaces, tables, and indexes that are altered with generated ALTER statements.
- Both
- Generate a COPY utility job for all tables that are affected by the LOAD utility job and all altered table spaces, tables, and index objects.
- None
- Do not generate any COPY utility jobs.
- 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.
You can specify one of the following values for this option:
- Mandatory
- Generate all REORG and REBUILD jobs that are needed to remove any REORG-pending and REBUILD-pending states and make the data available.
- 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.
- None
- Do not generate any REORG and REBUILD jobs. This option is not valid if you specified No for Allow rotate parts.
- Run RUNSTATS
- Specify whether to generate RUNSTATS utility jobs.
You can specify one of the following values for this option:
- Reload
- Generate a RUNSTATS utility job for all tables that are affected by the generated LOAD utility jobs to reload the data.
- Alter
- Generate a RUNSTATS utility job for all table spaces, tables, and indexes that are altered with generated ALTER statements.
- Both
- Generates a RUNSTATS utility job for all tables that are affected by the LOAD utility job and all altered table spaces, tables, and index objects.
- Minimum
- Generate RUNSTATS utility 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 has 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.
- None
- Do not generate any RUNSTATS utility jobs.
- Run REBIND
- Specify whether to generate a job to rebind the plans and packages that are affected
by the changes from an object comparison.
- Mandatory
- Generate a REBIND job for only those plans and packages that were invalidated by the changes.
- All relevant
- Generate a REBIND job for all plans and packages that were affected by the changes, including those plans and packages that were invalidated.
- None
- Do not generate a REBIND job.
Commands
You can enter the following 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 opens:
DB2 Admin -------------- DD1A Batch Job Utility Parameters -------------- 11:02 Command ===> Generate Job Card . ===> (Yes/No) DB2 System: DD1A Job cards: DB2 SQL ID: ADM001 ===> //JD4678SD JOB ,'DB2 UTILITY', ===> // REGION=8M,NOTIFY=USER1, ===> // MSGCLASS=X, ===> ===> Generate Job CLASS ===> YES (Yes/No) JOB CLASS . . . . . ===> JOBPARM: ===> ===> ===> ===> CM Batch EXEC statement parameters: Add SSID parameter . . YES (Yes/No) Add PLAN parameter . . YES (Yes/No) Additional parameters to add to CM Batch JCL EXEC statement: ===> ===> ===> ADBTEP2: Restart . . . . . . . (Yes/No) Maxerrors . . . . . . 88 (-1 to 99) BindError . . . . . . IGNORE (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) LOB/XML IC Unload . . U (Error, Use base data) Missing IC Unload . . U (Error, Use base data) Spanned . . . . . . . (Yes/No) DB2 Pending Changes options: Check at DROP . . . NO (Yes/No) Space parameters: Unit name ===> SYSDA Space unit . . . ===> TRK (BLK, TRK, CYL or 4096-32760) Max Primary . . . ===> 65535 (In above units or 99999999 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) 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 (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 opens:
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 1. 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 opens where you can select the Db2 utility for which you want to change the options:
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 ===>When you press Enter, the Specify Utility Options panel for the utility opens 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 opens:
ADB2PCO n Options for Change Functions 19:13 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 lock mode . . (Default is TABLESET) 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 PBR2 to PBR changes . . . . . . NO (Yes/No. Default is No) DB2 release number . . . . . . . . . . ____ (Use VVRM format) DB2 function level . . . . . . . . . . 502 (E.g. 100, 500, 501, 5nn) Table GRANT processing order . . . . . C (C - CREATE prefix for GRANT P - POSTUTIL prefix for GRANT Default is C )