db_ddl_diff script

The db_ddl_diff script reports any DDL differences between two schemas within the database.

The db_ddl_diff command is part of the Db2 support tools, and it exports DDL differences for sequence, function, table, synonym, view and procedure, between the source and target schema that you specify. To run it, a database name is required following -d | -db, a source schema name is required following -sschema, and a target schema name following -tschema. By default, the report shows objects existing in both source and target schema. You can also use -only_in_source, -only_in_target, or -only_if_different to narrow down the output. You can also use arguments -verbose or -brief to show or hide the details of DDL differences.

Syntax

db_ddl_diff  -d|-db <database_name> -sschema <schema_name>  -tschema <schema_name>
                      [<optional_arguments>]

Required parameters

-db <database_name>: Specifies database name.
-sschema <schema_name>: Specifies source schema name.
-tschema <schema_name>: Specifies target schema name.

Optional parameters

By default, all object types will be processed. To restrict the comparison to specific object types, use any combination of these switches.

-only_in_source|-only_in_target|-only_if_different

Displays objects that meet one or more of the following criteria, depending on the combination of parameters that you use:

The object exists only in the source database.
The object exists only in the target database.
The objects exist in both databases but are different.

By default, the report shows objects that meet any of those criteria.

-verbose

When two objects differ, the default report will only list the name of the object. To see a list of the actual differences, include this switch.

The -verbose and -brief parameter are mutually exclusive.

-brief

The default report includes various header information for readability. If you want the report to list only the object names, for example, to make it easier to process by a subsequent script, include this switch.

The -verbose and -brief parameters are mutually exclusive.

-ignore

Causes the comparison to ignore (not flag) the following differences:

Differences due to the use of uppercase or lowercase
The use or absence of double quotation marks anywhere in the DDL statements
Table constraints (UNIQUE, PRIMARY, or FOREIGN keys)

For example, if you specify the -ignore parameter, the following two table definitions match:


CREATE TABLE "Example1" 
(          "Customer_ID" bigint not null  
          ,UNIQUE ("Customer_ID") 
          ,PRIMARY KEY ("Customer_ID")  
) 
DISTRIBUTE ON ("Customer_ID");  

CREATE TABLE EXAMPLE1  
(    CUSTOMER_ID bigint not null  
) 
DISTRIBUTE ON (CUSTOMER_ID);

Outputs

A sample command and output follow:


 $ db_ddl_diff -db BLUDB -table  -sschema production  -tschema development

     Object Type: TABLE

     Only in source     schema: PRODUCTION
     ==============
     COPY_OF_PAYROLL
     COPY_OF_STATES_DIMENSION

     Only in target     schema: DEVELOPMENT
     ==============
     TEST1
     TEST2
     TEST3

     Differences
     ===========
     CUSTOMERS