Sybase to DB2 migration, Part 2: Integrity check guidelines

Information assets are key to day-to-day business. They are also a huge source of institutional value and intellectual property that must be preserved, extended, and repurposed as the systems underpinning them change. Given the current business environment, migration has taken on strategic importance for a number of reasons. This article explains integrity check process for various database objects while migrating the databases to DB2®.

Amol D. Barsagade (amolbarsagade@in.ibm.com), Software Architect, IBM

Author Photo: Amol Barsagade Amol D. Barsagade is a software architect with IBM Enterprise Marketing Management, IBM India Software Lab, Pune. Amol provides database solutions for database architecture, performance tuning, capacity management, application development, and migrations to various partners and customers in Asia-Pacific. Amol has a bachelor's degree in computer science and several years of experience working with relational database systems, including Oracle and SQL server. He has made valuable contributions to IBM developer work by reviewing and publishing articles.


developerWorks Contributing author
        level

06 December 2012

Also available in Chinese

Introduction

This article provides a description of the steps one should follow to verify the integrity of the resulting databases that result when migrating from Sybase ASE 12.x/15.x to DB2 9.7.x/DB2 10.1.x. This article aims to describe the process that will enable you to perform successful migrations to DB2 for Linux®, UNIX®, and Windows® that include database integrity.


Overview

Integrity check is an important part of any migration process since it ensures that the source and the target databases objects are in sync. This process is not a separate or independent one, it should be done after the migration of each and every object, so that the migration process will be more secure and chances of the database inconsistency can be avoided.

This is a guideline for those who perform database migrations. Immediately after the migration of an object, you should make sure the integrity of the migrated object has been retained by following the steps mentioned.


Integrity check process

  • Integrity check for the database
    • Ensure that the database has been created with the same name by comparing the source and the target database names.
    • Ensure that the proper tablespaces and buffer pools have been created.

You can use IBM Data Studio to identify the tablespace and buffer pool. Figure 1 shows the tablespace and buffer pool details.

Figure 1. Tablespace details
Figure shows the tablespace details

Or:

  • db2 connect to sample
  • db2 list tables
  • db2 list tablespaces show detail
  • db2 select bpname, pagesize from syscat.bufferpools

Ensure that the diaglevel and log paths are in place. Also ensure that the database in circular logging mode or archival mode.

  • Integrity check for tables
    • Perform integrity check for source table, use Sybase Central.
    • Perform integrity check for the target table using IBM Data Studio.
    • Ensure that the table name is same in both source and the target.
    • Ensure that the tables have the same number of columns.
    • Ensure that each column's data types are the same as in source or compatible with the source data type.
    • For the data types like char and varchar, ensure that size is correct.
    • For the data types like decimal and numeric ensure that the scales are correct.
    • Ensure that columns null/not null/default values are in place.
    • Ensure that check constraint are in place, if it exists.
    • Ensure that the tables primary constraint is in place, if it exists.
    • Ensure that the table's foreign key constraint is in place, if it exists.
    • Ensure that all the indices have been created in the correct order.
    • Ensure that the table and its indices have been created in the respective table spaces.
    • Ensure that the table has the correct access rights for the respective users.
    • Ensure that the identity column has been replicated properly in the target database.
    • Ensure that its name is the same.
    • Ensure that it has the same or compatible data types.
    • Ensure that it started with the correct initial value.
    • Ensure that it has the same increment value.
    • Ensure that its next value is the same as expected.
  • Integrity check for sequences:
    • Ensure that the sequence has been created with the same name.
    • Ensure that the sequence has the same or compatible data type.
    • Ensure that the start value and end value are properly mentioned, if it exists.
    • Ensure that the increment value is correct.
    • Ensure that the trigger associated with the sequence have been created properly, if it exists.
  • Integrity check for views:
    • Ensure that the view name is correct.
    • Ensure that the view has the same number of columns.
    • Ensure that the view columns data types are same or compatible.
    • Ensure that the view column names are same.
    • Ensure that the view definition is correct.
    • Ensure that the view-dependent tables exist.
    • Ensure that the view has the same access rights for the respective users.
  • Integrity check for primary key:
    • Ensure that table and the corresponding fields exist in the database.
    • Ensure that the field has created with not null attribute in the target database.
    • Ensure that the primary keys name is correct.
  • Integrity check for foreign key:
    • Ensure that the underlying table and the corresponding data types are present in the target database.
    • Ensure that the referenced table exists in the target database.
    • Ensure that the foreign keys name is correct.
  • Integrity check for indices:
    • Ensure that the table and the corresponding fields are present in the target database.
    • Ensure that the field list and their order is the same.
    • Ensure that the index has been created in correct order (ascending/descending or with allow-reverse scan).
  • Integrity check for aliases:
    • Ensure that the alias name is correct.
    • Ensure that the alias has been created on the correct object.
    • Ensure that the alias has all the corresponding access rights.
  • Integrity check for user-defined data types:
    • Ensure that the user-defined data types have been created properly in the target database.
    • Ensure that they have the same data types or compatible data types.
  • Integrity check for groups:
    • Ensure that all groups from source database have been created in the target database with the same name.
    • Ensure that the access rights for each group have been replicated properly in the target database as in source database.
    • Ensure that users attached to each group are same in both the source and the target database.
  • Integrity check for user:
    • Ensure that Sybase users have migrated to DB2 properly with the same name.
    • Ensure that the user belongs to the same groups as in source.
    • Ensure that they have the same access rights for the database objects in destination database as in source database.
  • Integrity check for triggers:
    • Ensure that the trigger has been attached to the corresponding tables only.
    • Ensure that the triggers type is the same for before trigger/after trigger and for insert trigger/delete trigger/update trigger.
    • Ensure that the trigger condition is correct.
    • Ensure that the trigger body is correct.
    • Ensure that the trigger has been created as before trigger if the trigger body has frequent number of rollbacks.
  • Integrity check for stored procedures:
    • Ensure that the stored procedure name is correct.
    • Ensure that the stored procedure has the same number of input, output and in out parameters.
    • Ensure that stored procedure returns the same number of result sets.
    • Ensure that the stored procedure returns the result sets to client or to caller as expected.
    • Ensure that the stored procedures dependent tables/views exist in the destination database.
  • Integrity check for user-defined functions-scalar
    • Make sure that the user-defined function has been created with the same name.
    • Make sure that the input parameters are the same in number.
    • Make sure that the input parameter data types are the same or compatible.
    • Make sure that the return type is the same or compatible.
    • Make sure the objects referred from the UDFs are defined in the destination database.
    • Make sure that the UDF returns the result as expected.
    • Make sure that the body of the user-defined function is the same or contains the same logic.
  • Integrity check for user-defined functions table
    • Make sure that the user-defined function has been created with the same name.
    • Make sure that the input parameters are the same in number.
    • Make sure that the input parameters data types are the same or compatible.
    • Make sure that the return type is table: return table has the same number of columns and has the same or compatible data type.
    • Make sure the objects referred from the UDFs are defined in the destination database.
    • Make sure that the UDF returns the result as expected.
    • Make sure that the body of the user-defined function is same or contains the same logic.

Conclusion

This article describes the importance of integrity checks for most database objects. After successful completion of the entire integrity check process, the target database is ready to use and can be considered as a deliverable to the customer.


Acknowledgement

I would like to thank to Dan A. Simchuk (simchuk@us.ibm.com) for his expertise and review comments.

Resources

Learn

Get products and technologies

  • Download the IBM DB2 10.1 for Linux, UNIX, and Windows evaluation version.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=848418
ArticleTitle=Sybase to DB2 migration, Part 2: Integrity check guidelines
publish-date=12062012