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.

Recommendations:
  • 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.
Related information:

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.

Table 1. The Db2 catalog table ignore fields
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
Start of changeSYSCONTROLSEnd of change
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
Notes:
  1. 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.
  2. 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.
  3. 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.
  4. 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.
Important: Some values are stored in the Db2 catalog in both internal and external formats. Internal format is only understood by Db2 (not documented); external format is suitable for input and output. Object Comparison Tool always ignores the internal format. To ignore the value, there must be an ignore specification for the field that contains the external format of the value. For example, SYSINDEXPART.LIMITKEY stores the high value of the limit key of the partition in internal format. SYSTABLEPART.LIMITKEY stores the high value of the partition in external format. Therefore, if you need to ignore SYSINDEXPART.LIMITKEY, specify SYSTABLEPART.LIMITKEY.

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
  • Start of changeBUSINESS_TIMEEnd of change
  • Start of changeCOLUMN_MASKSEnd of change
  • Start of changeHASH_ORGANIZATIONEnd of change
  • Start of changeINCLUDE_COLUMNS End of change
  • Start of changeKEYTARGETSEnd of change
  • PARTITIONING
  • Start of changePBG_NUMPARTSEnd of change
  • Start of changeROW_PERMISSIONSEnd of change
  • Start of changeSOURCE_PENDING_CHANGESEnd of change
  • SPACE
  • STORAGE
  • Start of change SYSTEM_TIME End of change
  • Start of changeXMLMODIFIEREnd of change
For those generic ignore specifications that directly correspond to catalog table columns, the following table shows which catalog fields are ignored when the generic ignore is specified.
Table 2. Generic ignore specifications
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
For those generic ignores that do not correspond to catalog columns, the following table describes the information that is ignored:
Table 3. Generic ignore descriptions for ignores that do not correspond to catalog tables
Generic ignore Information that is ignored
COLUMN_MASKS Columns masks

See Column mask (Db2 12 for z/OS).
ROW_PERMISSIONS Row permissions

See Row permission (Db2 12 for z/OS).
INCLUDE_COLUMNS Columns that are specified in the INCLUDE clause of the CREATE INDEX statement for unique indexes

See CREATE INDEX (Db2 12 for z/OS).
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.

Figure 1. Manage Ignores (ADB2C3) panel
ADB2C3 in ------------------- CM - Manage Ignores ----------------------- 20:10 
Option ===>                                                                     
                                                                                
                                                                                
   1 - Display ignores                                   DB2 System: DD1A       
   2 - Create an ignore                                  DB2 SQL ID: ADM001      
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
Enter display selection criteria (Using a LIKE operator, criteria not saved):   
Name . . . . . . .                    >  Created by . .          >              
Owner  . . . . . .          >            Altered by . .          >              
Created within . .                       Ignore ID  . .                         
Altered within . .