![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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 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
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 nullattribute 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.
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.
I would like to thank to Dan A. Simchuk (simchuk@us.ibm.com) for his expertise and review comments.
Learn
- Be sure to read "Sybase
to DB2 migration, Part 1: Process and methodology."
- Learn how to use the IBM Data Movement Tool.
- Plan for a port and migration to DB2 UDB, which includes descriptions of tools and
documents.
- Read "DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows" to
learn how to run Oracle applications.
- See the IBM Redbooks® publication titled "Sybase to DB2
Conversion Guide" to understand DB2 conversion tools, techniques, and
process.
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
more.
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Twitter.
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
- Participate in the discussion forum.
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
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.
