Special considerations for comparing Db2objects

You can perform most comparisons field by field, comparing the catalog records that represent the objects. However, special considerations are needed in some situations.

These situations are described in the following sections:

Constraint names

Constraint names are not compared (and differences not reported) because constraint names can be explicitly specified or, if they are not explicitly specified, be generated by Db2. If the constraint names are generated by Db2, the constraint names could be different between source and target, even if the DDL for the object might be the same for source and target.

Differences in constraint name are not compared, because this comparison would cause an unnecessary drop and re-create of constraints that are logically correct. Dropping and recreating constraints would put the table space in CHECK PENDING, that is, out of service. Because the objects have no real differences, only differences in the constraint names, putting the table space out of service might not be necessary.

DROP statements in the source DDL

All DROP statements in the source DDL are copied to the DDL that is produced during the compare process. The effect of the drop statements is the same as dropping the objects on the target before running the compare job. Data from the dropped tables is saved by generated unload utilities making it possible for you to recover data from the dropped tables manually. In addition, the corresponding RUNSTATS, IMAGECOPY, and CHECK DATA utilities are not generated even if they are requested on the Generate Compare Jobs (GOC5) panel.

All implicitly dropped objects are found when the target catalog is available. However, if the target catalog is specified in the DDL, the DROP impact might be incorrectly reported. Data in the dropped objects that is missing from the DROP impact report is not saved by generated unload utilities. It is important that you save the dropped objects if at least one DROP statement is in the source DDL and the target catalog is unavailable. DROP statements in the target DDL are ignored. The statement sequence CREATE/DROP for the same object is invalid, the result is unpredictable.

Functions

Functions are compared based on the function signature, meaning that the function-specific name is treated as an attribute of the function, and a comparison is performed. If specific names are different, the target function definition is upgraded with the source-specific name. If you do not want the function definition upgraded, SYSROUTINES.SPECIFICNAME should be ignored.

If SQL PL functions, including non-inline SQL scalar function and SQL table function, are included in the compared objects, use the compare option Bypass SQL PL functions to control how Db2 Object Comparison Tool should process the objects. When the Bypass SQL PL functions option is specified as NO and when the non-inline SQL scalar functions or the SQL table functions are detected, Db2 Object Comparison Tool terminates processing. Otherwise, Db2 Object Comparison Tool skips the non-inline SQL scalar functions and the SQL table functions. Db2 Object Comparison Tool then continues processing the other objects and generates the APPLY job or work statement list.

Note: Examine the APPLY job or work statement list to verify that the content is complete.

Implicit and explicit objects

Db2 Object Comparison Tool compares implicit objects from the source with implicit objects from the target and explicit objects from source with explicit objects from target. All objects from DDL source are explicit objects. If no explicit counterparts are found on the target, those objects are processed as new explicit objects which need to be added to the target. If no explicit counterparts for explicit target objects are found on the source, those objects are dropped from target.

Materialized query tables

Comparisons involving materialized query tables (MQTs) do not compare columns. Instead, only the table type is compared.

For example:
  1. If the target is defined as:
    CREATE TABLE <schema>.<mqt_name> AS (              
 SELECT * FROM SYSIBM.SYSDUMMY1 )                             
DATA INITIALLY DEFERRED REFRESH DEFERRED IN <dbname>.<ts_name>;
  2. And the source is a same-named, different columned table (it does not matter if the source had 20 more columns):
    CREATE TABLE <schema>.<table_name> (AAAAAAD CHAR(2))    
IN <dbname>.<ts_name>;
  3. The compare output shows: 
     SDSF OUTPUT DISPLAY XXXXXXX Jnnnnnnn  DSID   110 LINE 49      COLUMNS 02- 81   
 COMMAND INPUT ===>                                            SCROLL ===> CSR  
Compare table source(<schema>.<mqt_name>) and target(<schema>.<tb_name>         
  (A)Table type changed from Materialized Query Table to normal table           
  Table will be altered                                                         
                                                                                
GOC2CMP - Ended normally
  4. And the result is: 
    --#ADMIN PROCESS CREATE                        
  ALTER TABLE <schema>.<mqt_name>    
    DROP MATERIALIZED QUERY ;                  
  COMMIT ;

Native SQL procedures

Db2 Object Comparison Tool compares active and inactive versions of a native SQL procedure by comparing the options and the native SQL procedure bodies. The native SQL procedure bodies are compared the same way as the trigger bodies are compared.

Object authorizations

Db2 Object Comparison Tool handles object authorizations differently, depending on the object location:
  • For objects that exist on both the source and the target, Db2 Object Comparison Tool compares and reports the authorization differences, but does not propagate the differences from the source to the target. Db2 Object Comparison Tool does not propagate the differences to avoid corrupting the target authorizations. During the apply job, the GRANT statements from the source are ignored and the GRANT statements from the target are read.
  • For objects that exist only on the source and that are added to the target during the apply job, the source authorization is applied to the target objects.

Online schema evolution

The following DB2® 8 online schema evolution functionality is not exploited by Db2 Object Comparison Tool. This means that none of the related ALTER statements are generated when applying these types of changes:
  • Alter of Identity column attributes.
  • Add partitioning key. This function is intended for adding partitioning information for a table in a partitioned table space if the definition of the table is incomplete.

Partitioned tables

Tables in partitioned table spaces can be dropped only by dropping the table space. If a table in a partitioned table space has changes that require the table to be dropped and re-created, the partitioned table space is dropped and re-created as well, even if the table space comparison shows no differences.

Db2 Object Comparison Tool can accept differences in the number of partitions by ignoring the field SYSTABLESPACE.PARTITIONS. In this case, no comparisons are performed at a partition level, and all partition characteristics are taken from the target.

If the table space is not part of the comparison (that is, the comparison is performed at the table level), the following conditions apply when a partitioned table needs to be dropped and re-created:
  • If the target is a DDL file, the table space cannot be dropped and re-created because the table space definition is not available.
  • If the target contains tables from the Db2 catalog, the table space definition from the catalog is stored in the version file. Unless otherwise indicated, the fact that a table is partitioned is derived from the stored table space definition. In any other case, the table space definition is used only for the purpose of re-creating the table space.

Pending Changes

Pending changes are included in version files created from catalog records. No pending changes can be included in version files from DDL. Pending changes are merged into the changed objects before the objects are compared. If pending changes are ignored, the source and target pending changes are not merged into the changed objects. If the source DDL contains an ALTER statement with DROP PENDING CHANGES, the ALTER statement is passed to the target, and pending changes in the target version file are ignored.

Renamed objects

When comparing objects, Db2 Object Comparison Tool considers whether a database, table space, table, index, or column was renamed in the source system. You can tell Db2 Object Comparison Tool when a rename has occurred in the source system by using rename specifications. Enter rename specifications the same way that you enter compare masks. For more information about entering rename specifications, see 3. Specifying compare masks. When you specify that an object or column was renamed in the source, Db2 Object Comparison Tool compares the existing object in the target with the renamed object in the source. When the target object is updated, the data in the target system is preserved. For example, you have the following source and target objects: 
Source = CREATE TABLE USERA.T2 (COLA, COLB, COLY, COLZ)
Target = CREATE TABLE USERA.T2 (COLA, COLB, COLY, COLZ)
If you rename the source table T2 to T1 and COLY to COLX, the source and target objects are now different. 
Source = CREATE TABLE USERA.T1 (COLA, COLB, COLX, COLZ)
Target = CREATE TABLE USERA.T2 (COLA, COLB, COLY, COLZ)
Using the following RENAME specifications (see 3. Specifying compare masks for syntax examples and supported object types), the table is renamed during the compare process to T1 and COLY is renamed to COLX: 
RENAMETB:USERA.T2,USERA.T1
RENAMECOL:USERA.T1.COLY,COLX
Note: The new table name (T1) is referenced in the RENAMECOL statement because the RENAMETB statement occurs before the RENAMECOL statement. If the RENAMECOL statement was issued first, you would reference the original table name in the RENAMECOL statement.
The following steps are generated on the target system:
  • Unload the table T2 data
  • Drop table T2 and create table T1
  • Load the COLY data from table T2 data into COLX in table T1
Restrictions:
  • It is not always possible for Db2 Object Comparison Tool to uniquely relate a column to a specific table because there is no connection to Db2 at the time the compare process is run (the object definitions also might originate from DDL). This situation occurs when a view references two tables and has an unqualified reference to a column. Db2 Object Comparison Tool checks if a rename might be the reason for the difference and indicates this reason in the report. If differences exist, the final outcome is not affected and the view is changed accordingly.
  • Renaming an implicit index is not supported.
  • Renaming an auxiliary table is not supported.

Table columns

Table columns are matched based on column name. If column positions are different, the table is dropped and re-created to reflect the source sequence of columns.

Column names that are not found in the source file are considered dropped and are removed from the target table unless suppress drop of columns is specified in the Generate Compare Jobs (GOC5) panel.

Column names that are not found in the target file are considered new and are added to the target table. If the source and target tables are identical except for one or more appended columns, the target table is altered to add the new columns if the column attributes are acceptable. Otherwise, the table is dropped and re-created.

Table drop/re-create without data conversion

Under certain conditions Db2 Object Comparison Tool can determine that the step that occurs between the unload and load steps to convert the data is not necessary. Performance can improve when the conversion step is omitted from the batch job.

In general, Db2 Object Comparison Tool does not generate a conversion step when the following table modifications are made:
  • The table is renamed.
  • Columns in the table are:
    • Moved
    • Renamed
    • Deleted
    • Inserted with an attribute of WITH DEFAULT or NULLS
  • Only the attributes of the column are changed.
The data types and lengths are changed according to the matrix in the following table:
Table 1. Matrix for data type and length changes that do not require data conversion. The following table describes the matrix used by Db2 Object Comparison Tool to determine whether the data conversion step between unload and load can be skipped.
From data type To data type
SMALL
INT
 INT DEC FLOAT CHAR
VAR
CHAR
LVAR
CHAR
 DATE TIME
TIME
STAMP
 RID
BIG
INT
DEC
FLOAT
(16)
DEC
FLOAT
(34)
BIN
ARY
VAR
BIN
ARY
SMALLINT Y Y A Y − − − − − − − Y Y Y − −
INT − Y A Y − − − − − − − Y Y Y − −
DEC A A A Y − − − − − − − Y Y Y − −
FLOAT(1−21) − − − Y − − − − − − − Y Y Y − −
FLOAT(22−53) − − − Y − − − − − − − Y Y Y − −
CHAR A A A − Y Y Y − − − − − − − Y −
VARCHAR A A A − Y Y Y − − − − − − − − Y
LVARCHAR − − − − Y Y Y − − − − − − − − −
DATE − − − − − − − Y − − − − − − − −
TIME − − − − − − − − Y − − − − − − −
TIMESTAMP − − − − − − − Y Y Y − − − − − −
RID − − − − − − − − − − Y − − − − −
BIGINT Y Y Y Y − − − − − − − Y − − − −
DECFLOAT(16) Y Y Y Y − − − − − − − − Y Y − −
DECFLOAT(34) Y Y Y Y − − − − − − − − Y Y − −
BINARY − − − − Y − − − − − − − − − Y −
VARBINARY − − − − − Y − − − − − − − − − Y
Notes:
  • Y = YES, data conversion is always skipped.
  • A = ACTION, data conversion is normally performed, with truncation, if necessary. If the new column can accommodate the data, data conversion is skipped for the following conversion types:
    • smallint to decimal
    • integer to decimal
    • decimal to smallint
    • decimal to integer
    • decimal to decimal
    However, if the scale of the decimal type is changed, the data conversion is performed.

When the data conversion step is skipped, a converted unload data set is not created.

Triggers

Triggers are represented as character strings that contain CREATE TRIGGER statements. To apply masks to the trigger definition, triggers are parsed and the language elements are identified. Masks are applied to the source trigger elements where masks are applicable, and the triggers are compared element by element.

The only exception to this process is that to successfully compare an unqualified name to a qualified name, the compare program attempts to determine implicit qualifiers for unqualified names. If the trigger has changed, the change is reported.

The sequence in which triggers are created is important because they are run in the same sequence by Db2. To maintain the correct sequence, all triggers for a table are processed at the same time.

The manner in which the batch compare program processes triggers depends on the value that you entered in the Suppress DROP of target field in the Generate Compare Jobs (GOC5) panel. The following table provides more information.

Table 2. Trigger comparison process. The following table describes how different types of triggers are handled when the "Suppress DROP of target" field is set to No or Yes.
Suppress DROP of target objects No Suppress DROP of target objects Yes
Source file sequence and contents are used. Source file sequence and contents are used for all triggers in the source file.
Triggers are compared, one by one, based on the trigger name. Triggers are compared, one by one, based on the trigger name.
If a trigger is not in the target file or if the compare finds a difference, the trigger is added or dropped and re-created. All subsequent triggers are dropped and, if applicable, re-created to maintain the correct sequence. If a trigger is not in the target file or if the compare finds a difference, the trigger is added or dropped and re-created. All subsequent triggers are dropped and, if applicable, re-created to maintain the correct sequence.
Only triggers found in the target file are dropped. To avoid violating the sequence of triggers in the source file, only triggers that are found in the target file are included in the first possible position. This approach maintains the original position of these triggers in the target file.

Views

Views are represented as character strings that contain CREATE VIEW statements. To apply masks to the view definition, views are parsed and the language elements are identified. Masks are applied to the source view elements where masks are applicable, and the views are compared element by element.

The only exception to this process is that to successfully compare an unqualified name to a qualified name, the compare program attempts to determine implicit qualifiers for unqualified names. If the view changes, the change is reported and the view definition, changed or not, is stored.

When the batch compare program has processed all views, it analyzes two types of dependencies:
View dropped
A view is dropped if one of the base tables or views that is referred to was dropped. If a view is dropped, it is re-created regardless of whether it was changed.
View dependent on another view
The sequence in which views are created is important because a view can refer to another view. The stored view definitions are sequenced to take this into account.

This behavior means that CREATE VIEW statements are not necessarily listed in the sequence in which they were processed.