Bharath's Tech Blog

Improvements to DB2 objects' structure comparison

In my previous blog I mentioned how and why I developed an alternate process to Object Capture using DB2 Admin tool. After some discussion and based on feedback from my team, I made some improvements to this process.

               1. Source and Target comparison

The old version of the process compared Source with Target, but not vice-versa. And, the job has to be submitted again with Source and Target names swapped.There can some objects present in Target but not in Source. So, these were getting missed. 

          

              Improvement: The new version of the process compares Source with Target and vice-versa.

               2. Tablespaces and Columns comparison

The old version of the process just tried to find whether the Tablespace and Columns existed in Target or not (same is done for Tables and Indexes). This may not be enough in our ERP environment as we can have versioned Tablespaces and Columns in different sequence (due to something like patches applied in DEV but not yet in QA and Prod).

            Improvement: The new version of the process compares the following for Tablespaces and Columns.

                               For Tablespaces:

                               a. Name

                               b. OLDEST_VERSION and CURRENT_VERSION

                               c. Type (UTS or non-UTS)

                               For Columns

                               a. Name

                               b. Sequence number

                               c. Data type

                               d. Length and Scale

                               e. Nulls

                3. Storing the structures comparison report for historical purpose

The old version of the process wrote the comparison report to a Sequential dataset. Referring the comparison report will be helpful (and needed) in our ERP world whenever a new upgrade or patch is applied. But, it is cumbersome to refer the datasets for historical purposes.

            Improvement: The new version of the process loads the comparison report into a DB2 table. It will be easy to query a DB2 Table to get the comparison details and is a better approach for storing the reports for historical purposes. The DB2 table has the following columns.

                                                     i.          Source DB

                                                     ii.         Target DB

                                                     iii.        Object type (TS/TB/IX/CL)

                                                     iv.        Object name

                                                     v.         Table name (only for columns)

                                                     vi.        Present in Source (Y/N) – for TS/TB/IX/CL

                                                     vii.       Present in Target (Y/N) – for TS/TB/IX/CL

                                                     viii.      Type mismatch (Y/N) – only for TS

                                                     ix.        Version mismatch (Y/N) – only for TS

                                                     x.         Sequence mismatch (Y/N) – only for cols

                                                     xi.        Data type mismatch (Y/N) – only for cols

                                                     xii.       Length and Scale mismatch (Y/N) – only for cols

                                                     xiii.      Nulls mismatch (Y/N) – only for cols

                                                     xiv.      Row entry time stamp