Ignores
An ignore provides the ability to specify that certain fields in the Db2 catalog records are to be ignored when objects are compared. Ignores help avoid meaningless comparisons and protect those fields, called ignore fields, from being changed.
For example, you might want to ignore fields that contain space information, because production tables and indexes are often larger than the corresponding test tables and indexes. You might also want to ignore fields that contain buffer pool names, because a broader set of buffer pools might be implemented in the production system.
You can specify ignore fields during comparisons. Comparisons occur in the following situations:
- When you use IBM® Db2 Object Comparison Tool for z/OS® to compare objects
- When you run a change in Db2 Admin Tool
The place where you define ignores depends on the comparison process for which you want to use them. Ignores that are specified when running a change must be defined in the Change Management (CM) database. Ignores that are specified when using Object Comparison Tool to compare objects can be defined in either the CM database or in a data set.
When you specify an ignore, the specified fields in the Db2 catalog records are not used for comparisons. If you must re-create an object for other changes, values for ignored fields are taken from the target version. All other field values are taken from the source version.
- Consider managing all your ignores through Change Management. When ignores are stored in the CM database, they are easy to track and recover because they are stored in Db2 tables.
- Use caution when specifying ignore fields. If possible, use the generic specifications, which account for some common fields that are often intentionally different on source and target systems. See Generic ignores.
- When specifying ignore fields, consider that many fields in theDb2catalog records are interdependent. Therefore, when one field is ignored, the value in another field might be invalid if that field is not also ignored. For example, consider the TYPE fields for tables and table spaces. If TYPE is ignored for table spaces, a table space could keep the LARGE attribute. If the compare source is a segmented table space, the resulting set of attributes is invalid if the SEGSIZE field is not also ignored. Another example of dependency is between the SQTY and SECQTYI fields in SYSTABLEPART and SYSINDEXPART. If the secondary quantity is to be ignored, specify both fields or use the generic SPACE specification.
System ignores
Some catalog fields are automatically ignored, such as statistics, dates, and internal identifiers, because these fields are generally meaningless for comparisons. These types of ignores are called system ignores. These ignores are included by default and do not need to be explicitly specified on the ISPF panels. You can list system ignores by setting the compare reporting option System generated to YES; See Batch compare report format (IBM Db2 Object Comparison Tool for z/OS 12.1.0).
If the compared objects originate from two different versions of Db2, they might be different, because more parameters, attributes, or clauses are supported by one of the versions (typically the newer version). In this case, Object Comparison Tool ignores such fields just like ignore fields that are manually entered. No differences of this kind are reported.
Ignore fields
Only certain fields in certain Db2 catalog tables can be ignored. The following table shows the Db2 catalog tables and the fields that you can specify for ignores.
Db2 catalog table | Ignore fields | ||
---|---|---|---|
SYSCHECKS |
CHECKCONDITION
CREATOR |
||
SYSCOLUMNS |
ALTEREDTS
COLTYPE1 DEFAULT1, 2 DEFAULTVALUE1 FLDPROC4 FOREIGNKEY3 |
KEYSEQ
LABEL LENGTH1 LENGTH2 NULLS1 PARTKEY_COLSEQ |
PARTKEY_ORDERING
REMARKS SCALE1 STATS_FORMAT TYPENAME TYPESCHEMA |
SYSCONTROLS |
OWNER
OWNER_TYPE ENABLED |
REMARKS
RULETEXT |
|
SYSDATABASE |
BPOOL
CREATOR DBCS_CCSID ENCODING_SCHEME |
GROUP_MEMBER
INDEXBP MIXED_CCSID ROSHARE |
SBCS_CCSID
STGROUP TYPE |
SYSDATATYPES |
ENCODING_SCHEME
LENGTH METATYPE |
OWNER
SCALE SOURCESCHEMA |
SOURCETYPE
SUBTYPEREMARKS |
SYSFIELDS |
EXITPARM
EXITPARML FLDPROC |
PARMLIST
WORKAREA |
|
SYSINDEXES |
AVGKEYLEN
BPOOL CLOSERULE CLUSTERING COPY CURRENT_VERSION |
DSETPASS
ERASERULE INDEXTYPE OLDEST_VERSION PADDED PGSIZE |
PIECESIZE
RELCREATED REMARKS SPACEF UNIQUERULE VERSION |
SYSINDEXPART |
DSNUM
FREEPAGE GBPCACHE INDEXTYPE LEAFFAR LEAFNEAR |
LIMITKEY
PARTITION PQTY PSEUDO_DEL_ENTRIES SECQTYI |
SPACEFEXTENTS
SQTY STORTYPE STORNAME VCATNAMEPCTFREE |
SYSSYSKEYS |
COLSEQ
ORDERING |
||
SYSPARMS |
CAST_FUNCTION
CCSID ENCODING_SCHEME LENGTH LOCATOR ORDINAL |
OWNER
PARMNAME ROWTYPE SCALE SPECIFICNAME |
SUBTYPE
TABLE TABLE_COLNO TYPENAME TYPESCHEMA |
SYSRELS |
CHECKEXISTINGDATA
DELETERULE |
ENFORCED
IXNAME |
IXOWNER
RELNAME |
SYSROUTINES |
ASUTIME
CAST_FUNCTION CLASS COMMIT_ON_RETURN DBINFO DEBUG_MODE DETERMINISTIC EXTERNAL_ACTION EXTERNAL_NAME EXTERNAL_SECURITY FENCED FINAL_CALL FUNCTION_TYPE INLINE JAR_ID JARSCHEMA |
JAVA_SIGNATURE
LANGUAGE LOBCOLUMNS MAX_FAILURE NULL_CALL NUM_DEP_MQTS OWNER OWNERTYPE PACKAGEPATH PARALLEL PARAMETER_CCSID PARAMETER_STYLE PARM_COUNT PARSETREE PROGRAM_TYPE REMARKS |
RESULT_COLS RESULT_SETS
RUNOPTS SCRATCHPAD SCRATCHPAD_LENGTH SECURE SOURCESCHEMA SOURCESPECIFIC SPECIAL_REGS SPECIFICNAME SQL_DATA_ACCESS STAYRESIDENT SYSTEM_DEFINED TEXT WLM_ENV_FOR_NESTED WLM_ENVIRONMENT |
SYSSEQUENCES |
CACHE
CYCLE INCREMENT MAXVALUE |
MINVALUE
ORDER OWNER PRECISION |
REMARKS
RESTARTWITH SEQTYPE START |
SYSTABLEPART |
COMPRESS
DSNUM EXTENTS FREEPAGE IXCREATOR IXNAME |
LIMITKEY
LOGICAL_PART PCTFREEGBPCACHE PQTY SECQTYI SPACEF |
SQTY
STORNAME STORTYPE TRACKMOD VCATNAME |
SYSTABLES |
AUDITING
CHECKS CLUSTERTYPE CREATEDBY DATACAPTURE DBNAME EDPROC |
ENCODING_SCHEME
KEYCOLUMNS LABEL LOCATION OWNER OWNERTYPE REMARKS |
STATUS
TBCREATOR TBNAME TSNAME TYPE VALPROC |
SYSTABLESPACES |
BPOOL
CLOSERULE CREATOR DBCS_CCSID DSETPASS ENCODING_SCHEME ERASERULE |
IMPLICIT
INSERTALG LOCKMAX LOCKRULE MAXROWS PARTITIONS |
PGSIZE
SBCS_CCSID MIXED_CCSID SEGSIZE STATUS TYPE |
SYSTRIGGERS |
GRANULARITY
OWNER |
REMARKS
TRIGEVENT |
TRIGTIME
TEXTTRIGNAME |
SYSVIEWS |
APP_ENCODING_CCSID
CHECKTEXT ENABLE ISOLATION |
MAINTENANCE
PATHSCHEMAS REFRESH REFRESH_TIME |
RELCREATED
SIGNATURE TYPE |
- The SYSCOLUMNS fields COLTYPE, LENGTH, SCALE, DEFAULT, and DEFAULTVALUE are all part of the column type definition. The NULLS field is also related, because in some cases, it is part of the default specification. If you choose to ignore some, but not all, of the fields that are part of a column definition, the result can be inconsistent attributes and, subsequently, invalid DDL.
- The DEFAULT field can have a relationship to a SYSSEQUENCES row. Ignoring the DEFAULT field can cause the SYSSEQUENCES row to be included or excluded, depending on the value of the DEFAULT field in the target SYSCOLUMNS row. To ignore fields in the SYSSEQUENCES row, you must explicitly select them.
- The FOREIGNKEY field specifies the subtype of a character type column. Ignoring the FOREIGNKEY field not only removes the check for SBCS and MIXED data, but also the FOR BIT DATA specification. As a result, CCSID conversions can occur, if applicable.
- The FLDPROC field can have a relationship to a SYSFIELDS catalog row. Ignoring the FLDPROC field can cause the SYSFIELDS row to be included or excluded, depending on the value of FLDPROC in the target SYSCOLUMNS row. To ignore fields in the SYSFIELDS row, you must explicitly select them.
Ignore syntax
The syntax for specifying an ignore is:
objecttype: field1, field2,...,fieldn
where:
- objecttype is the Db2 catalog table name
- fieldx is the Db2 catalog column to be ignored
For example, the following lines show ignore field specifications. The first specification is for a database. It shows that for SYSDATABASE, the field BPOOL is ignored when the comparison is performed.
SYSDATABASE: BPOOL
SYSDATABASE: INDEXBP,STGROUP
SYSTABLESPACE: BPOOL
SYSTABLEPART: PQTY,SQTY,STORNAME,VCATNAME
SYSINDEXES: INDEXSPACE
SYSINDEXPART: PQTY,SQTY,STORNAME,VCATNAME
Generic ignores
Generic ignores specify that you want to ignore all information of a certain type, such as all buffer pools, all allocated space information, and all information about how data is stored and partitioned. Specifying a generic ignore has the same effect as specifying ignore fields individually.
The generic ignore specifications are:
- BUFFERPOOL
- BUSINESS_TIME
- COLUMN_MASKS
- HASH_ORGANIZATION
- INCLUDE_COLUMNS
- KEYTARGETS
- PARTITIONING
- PBG_NUMPARTS
- ROW_PERMISSIONS
- SOURCE_PENDING_CHANGES
- SPACE
- STORAGE
- SYSTEM_TIME
- XMLMODIFIER
Generic ignore | Db2 catalog table | Ignore fields |
---|---|---|
BUFFERPOOL | SYSDATABASE | BPOOL, INDEXBP |
SYSINDEXES | BPOOL | |
SYSTABLESPACE | BPOOL | |
BUSINESS_TIME | SYSCOLUMNS | COLTYPE |
LENGTH | ||
SCALE | ||
NULLS | ||
DEFAULT | ||
HASH_ORGANIZATION | SYSTABLES | HASHKEYCOLUMNS |
SYSCOLUMNS | HASHKEY_COLSEQ | |
SYSTABLEPART | HASHSPACE | |
SYSTABLESPACE | HASHSPACE | |
SYSINDEXES | HASH | |
KEYTARGETS | SYSINDEXES | KEYTARGET_COUNT |
IX_EXTENSION_TYPE | ||
SYSKEYTARGETS | KEYSEQ | |
ORDERING | ||
TYPESCHEMA | ||
TYPENAME | ||
DATATYPEID | ||
SOURCETYPEID | ||
LENGTH | ||
SCALE | ||
NULLS | ||
CCSID | ||
SUBTYPE | ||
DERIVED_FROM | ||
PARTITIONING | SYSINDEXPART | PARTITION |
SYSTABLESPACE | PARTITIONS | |
SYSINDEXPART | LIMITKEY | |
SYSTABLEPART | LIMITKEY | |
LIMITKEY_INTERNAL | ||
LOGICAL_PART | ||
PARTITION | ||
SYSTABLES | PARTKEYCOLNUM | |
SYSCOLUMNS | PARTKEY_COLSEQ | |
PARTKEY_ORDERING | ||
SYSAUXRELS | PARTITION | |
PBG_NUMPARTS | SYSTABLESPACE | PARTITIONS |
SPACE | SYSINDEXPART | PQTY, SQTY, FREEPAGE, PCTFREE, SECQTYI |
SYSTABLEPART | PQTY, SQTY, FREEPAGE, PCTFREE, SECQTYI | |
SYSTABLESPACE | MAXROWS | |
STORAGE | SYSDATABASE | STGROUP |
SYSINDEXPART | STORTYPE, STORNAME, VCATNAME | |
SYSTABLEPART | STORTYPE, STORNAME, VCATNAME | |
SYSSTOGROUP | VCATNAME | |
SYSVOLUMES | VOLID | |
SYSTEM_TIME | SYSCOLUMNS | COLTYPE |
LENGTH | ||
SCALE | ||
NULLS | ||
DEFAULT | ||
XMLMODIFIER | XSROBJECTS | XSROBJECTSCHEMA |
XSROBJECTNAME | ||
TARGETNAMESPACE | ||
SCHEMALOCATION | ||
SYSXMLTYPMSCHEMA | ELEMENT_NAME |
Generic ignore | Information that is ignored |
---|---|
COLUMN_MASKS | Columns masks |
ROW_PERMISSIONS | Row permissions |
INCLUDE_COLUMNS | Columns that are specified in the INCLUDE clause of the CREATE INDEX statement for unique indexes |
SOURCE_PENDING_CHANGES | Pending changes on the source objects |
The Manage Ignores (ADB2C3) panel
The Manage Ignores (ADB2C3) panel is the main menu for managing ignores in the CM database. From this panel, you can view the existing ignores or create a new ignore.