Sybase to DB2 migration, Part 1: Process and methodology

This article illustrates the process and methodology for migrating from Sybase Adaptive Server Enterprise (ASE) 12.x/15.x to DB2® 9.7.x/DB2 10.1.x. The purpose is to enable you to perform successful migration to DB2 for Linux®, UNIX®, and Windows® by following this processes and methodology. For the validation test outlined here, it is assumed that the migration was from Sybase 15.x to DB2 9.7.x/DB2 10.1.x. Later, you can apply the same strategy to other migrations from relational database management systems like Oracle and SQL server, as well as for DB2 version upgrades.

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

29 November 2012

Also available in Chinese

Introduction

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 re-purposed as the systems underpinning them change. Given the current business environment, migration has taken on strategic importance for a number of reasons. The benefits of rationalizing processes and systems hinges on outcomes where services and functionality are as good or better than before, while free of costly redundancies or unnecessarily diverse technologies. Poorly executed migrations diminish these returns and fail to meet the "as good as or better than before" criteria.

Here we illustrate the process and methodology to be followed for migrating from Sybase ASE 12.x/15.x to DB2 9.7.x/DB2 10.1.x. This article will enable you to perform successful migration to DB2 for LUW by following this processes and methodology. For the validation test, as outlined here, it is assumed that the migration was from Sybase 15.x to DB2 9.7.x./DB2 10.1.x. Then apply the same strategy to other migrations from RDBMSes and use it for DB2 version upgrades.


Migration strategy

Migration includes various components, such as DB2 servers, DB2 clients, database applications, scripts, migration tools, etc., and each should be examined in detail before actual migration starts.

Figure 1 depicts the recommended migration roadmap for the components in your environment.

Figure 1. Migration roadmap
Image shows migration roadmap

The following is an example of a migration strategy in which you test your database applications and routines and determine that they run successfully in DB2:

  • Set up a DB2 test server and create source-equivalent test databases.
  • Test your database applications and routines on the DB2 test database to determine whether they run successfully.
  • Determine what the migration issues are and how to resolve them. Use this information to adjust your migration plan.
  • Migrate your DB2 server into your production environment. Ensure that it operates as expected (You will probably want to run the production migration in parallel with the current production system for a time to ensure that both are generating equivalent results).
  • Test your database applications and routines on the DB2 migrated environment to determine whether they run as expected.
  • Make your migrated environment available to users.
  • Modify your database applications and routines as planned. Ensure that they run successfully in DB2 9.7.x/DB2 10.1.x.

Migration prerequisite

To proceed, it is important to first collect all the information about the source environment. You can use a porting assessment questionnaire, which helps in getting the relevant information for planning the migration work. Send the porting assessment questionnaire to the customer interested in migrating to DB2 for LUW. Ask them to fill in all the details and return it with sample code.


Migration process flow

  • Data and SQL migration
  • Application migration
  • Verification and validation of migration
  • Performance optimization and tuning

Pre-migration checklist

Identify the software and hardware, define the size of the target database, plan the network, and identify software for all target environments. The checklists provided below should be customized and can be used to understand the source system environment:

  • System
    • Java™ 1.6.x
    • Migration tool (IBM data movement tool (IDMT)/IBM migration toolkit (MTK))
  • Databases
    • Source database and version (e.g., Sybase ASE 12.x/15.x)
    • Target database release DB2 9.7.x/DB2 10.1.x )
    • Trimmed data (fewer than 5,000 rows/table)
    • Database partitioning requirements
    • Database security requirements
    • Database storage (table space allocation) requirements
    • Database administration requirements
    • Database driver's requirements
    • Database and memory tuning parameters
    • Sufficient disk space for the expected size of the database
    • Sufficient size of the temporary database for handling temporary working storage, such as sorts
    • Sufficient size of transaction log of the user database created
  • Users and privileges
    • Required users and logins
    • Required roles and groups
    • Required authentication, authorities, and privileges

Application checklist using a porting assessment questionnaire

You should prepare your own porting assessment questionnaire (PAQ) to get the system, application and the database details from your customer. That will help you complete the technical assessment and estimation of the migration tasks. Along with the porting assessment questionnaire, get sample code from the application. This will help in estimating additional efforts:

  • A PAQ should contain:
    • Customer details
    • Supported platforms (software/hardware)
    • Application and database overview
    • Additional components and tools
    • Workload (OLTP/OLAP/BI/mixed)
    • Programming languages (C, C++, Java, Pro*C, etc.) with numbers of modules, files, and lines of code
    • Database access interface (JDBC, ODBC, Perl, Ado.net, SQL Scripting, etc.) with numbers of modules, files, and lines of code
    • Database characteristics (approximate size, data types, information about tables, LOBs, CLOBs, sequences, identity values, referential integrity details, etc.)
    • Database objects (views, procedures, UDF, triggers, indices, etc.) with lines of codes and relevant information about dynamic SQL statements, SQL exceptions, array declaration, user-defined data types, temporary table, nested stored procedures, etc.)

Using the information completed by the customer will help in the effort estimation, which is based on the individual object complexity and count. Thus, after completion of this step with several reviews, you will get an idea about how much time this migration task will take.


Migration tasks

  • Creation of equivalent DB2 database structure and design (table spaces with equivalent containers, spaces and capacity management, for example).
  • Generate DDL scripts from the source database(s) for database objects, including tables, indices, constraints, sequences, views, procedures, UDFs, triggers, etc.
  • IDMT
    • This tool can be used to move data from various RDBMes, such as Sybase, Oracle, SQL server, and Teradata to DB2.
    • Learn how to use the IBM Data Movement Tool.
  • MTK
    • The IBM migration toolkit supports migrating to DB2 from various source databases like Oracle, Sybase ASE, Sybase SQL Anywhere (ASA), and Microsoft® SQL server. It supports migrations to target databases: DB2 for LUW, DB2 for i5®, DB2 for z/OS®, and Informix®.
    • Get information about the IBM Migration Toolkit .

To migrate the database objects, extract the DDL scripts for these objects:

  • Tables and indices
  • Temporary tables and global temporary tables
  • Views
  • Sequences, aliases
  • Users, roles, and groups
  • Stored procedures
  • Triggers
  • User-defined functions

Post-migration tasks

First, validate the migration with thorough testing. The testing should include validation of:

  • UI transactions
  • Batch processing
  • Administration procedures
  • Application performance

Perform a sanity check on the migrated data with the following tasks:

  • Count the number of objects in the source and target databases.
  • Count the number of rows for each table in both the source and target databases.

If a set of integration, system, and user acceptance tests exist, those test can be used for the validation of the migration. If such tests are not available, an alternative testing process must be performed to ensure that the migration completed successfully:

  • Test individual stored procedures
    • Generate stored procedure EXEC calls for test.
    • Using Perl or another script language, generate an EXEC call for each stored procedure. Find out what the stored procedures are doing, and generate relevant and valid input parameters based on that.
    • Executes the calls on source Sybase and target DB2 databases.
    • Compare the results that the source and target database are producing.
    • Identify the issues and fix them.

The differences in the output of the EXEC calls on the source and target database can indicate the issues with the migration. Analyze these differences/issues and make the necessary changes to the migration scripts.

  • Re-execute the procedures
    • As the issues get resolved, re-execute the procedures, and check the results.
  • Start a Sybase application and test it for correct results
    • Repair and map any stored procedure, trigger, or constraint that does not produce the correct result.
  • Verify the data transfer
    • Count the number of rows for all the tables whose data has been transferred from Sybase to DB2 server.
    • For any mismatch in the row count, the error log should be checked for the reason of failure.
  • Validate the data integrity
    • Database integrity is automatically checked when creating or enabling constraints after data transfer.
    • Lack of primary key and foreign key constraints in the database require through testing of application to verify the data integrity.

Verification and validation checklist

  • Database structure
    • Identifying all available objects in the source/target database.
    • Verify and validate all the objects in the target database against the source database.
    • Verify and validate that the normalization process is followed.
    • Verify and validate the database design.
    • Verify and validate that adequate capacity planning is followed.
    • Verify and validate a data redundancy check.
    • Verify and validate the system and its environment.
    • Verify and validate database growth.
    • Verify and validate the security design.
    • Verify and validate the structure of the system log.
    • Verify and validate the structure of the transaction log.
    • Verify and validate concurrency control.
  • Tables
    • Verify and validate the total number of tables to source database tables.
    • Verify table names.
    • Verify and validate the number of column in each table against source database table.
    • Verify and validate each data type mapping and size.
    • Verify and validate user-defined data types, if any.
    • Verify and validate default values.
    • Verify and validate null values.
    • Verify and validate identity columns.
    • Verify and validate primary and foreign key constraints.
    • Verify and validate dependency of objects like FK, triggers, and SP.
    • Verify and validate target indices.
  • Stored procedure
    • Verify and validate the total number of SPs against source database SPs.
    • Verify and validate the procedure name.
    • Verify and validate whether any user-defined data types are there.
    • Verify and validate input/output parameters and data type.
    • Verify and validate return values and return data type.
    • Verify and validate transaction mode (chained/unchained- @@tranchained).
    • Verify and validate for transaction- begin/end/save/commit/rollback.
    • Verify and validate isolation level 0/1/2/3.
    • Verify and validate @@transtate/@@trancount/@@isolation.
    • Verify and validate locking mode share/exclusive.
    • Verify and validate server/owner naming convention.
    • Verify and validate recompile option.
    • Verify and validate business logic.
    • Verify and validate dependency of objects.
    • Verify and validate SET NOCOUNT on/off, QUOTED IDENTIFIER on/off, and others.
    • Verify and validate for correct implementation of open query/ linked server, if any.
    • Verify and validate dynamic query statements.
    • Verify and validate DCL statements, GRANT permission, etc., if any.
    • Verify and validate for correct output behavior for respective inputs in terms of joins and DML statements.
  • User-defined functions
    • Verify and validate the total number of UDFs against source database UDFs.
    • Verify and validate UDF names.
    • Verify and validate I/O parameter passing.
    • Verify and validate the order of input parameter passing.
    • Verify and validate return data type.
    • Verify and validate user-defined data types.
    • Verify and validate dependency of objects.
    • Verify and validate business logic.
  • Views
    • Verify and validate the total number of views against source database views.
    • Verify and validate view name.
    • Verify and validate for correct view definition in terms of objects and source database.
  • Index
    • Verify and validate total against source database.
    • Verify and validate index name.
    • Verify for clustered (PK)/non-clustered index for target database.
  • Trigger
    • Verify and validate total number of triggers.
    • Verify and validate name of triggers.
    • Verify trigger dependency.
    • Verify and validate business logic/triggering event.
    • Verify and validate insert/update/delete trigger.
    • Verify standard format/best practices.
  • Constraints
    • Verify and validate the total number of constraints.
    • Verify and validate the name of constraints.
    • Validate FK, PK, check, null, and default constraints.
  • Rules
    • Verify and validate the total number of rules.
    • Verify and validate the name of rules.
    • Verify and validate rule condition and logic in target database.
  • Defaults
    • Verify and validate defaults.
  • Cursor
    • Verify and validate the total number of cursors.
    • Verify and validate cursor name.
    • Verify and validate holdlock/noholdlock/shared.
    • Verify and validate read only/for update cursor.
    • Verify and validate allocate and deallocate cursor.
    • Verify and validate @@sqlstatus/@@rowcount.
    • Verify and validate where current of clause.
  • Temporary table
    • Verify and validate global/temporary table.
  • Built-in function
    • Verify and validate built-in function.
  • System variable
    • Verify and validate the system-defined/global variable.
  • Others
    • Verify and validate USE keyword for Sybase.
    • Verify and validate GO keyword.
    • Verify and validate break/continue in while loop.
    • Verify and validate raiserror for exception handling/ @@error.
    • Verify and validate convert function.
    • Verify and validate @@error/@@transtate/@@sqlstatus/@@tranchained, @@textsize/@@rowcount.
  • Data testing
    • Verify and validate total number of records in source and target database.
    • Verify and validate loaded records are same as source records.
    • Verify and validate for null values and junk values.
    • Verify and validate data.
  • Database performance testing
    • After the database and the application are migrated, check the overall performance of the application. Using available DB2 utilities, derive performance of the application that is equivalent to or better against the target DB2 server.

Conclusion

The article describes the importance of various migration process and methodologies. You can leverage the various pre-migration, post-migration verification and validation checklists mentioned above for successfully migrating to DB2.

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=847257
ArticleTitle=Sybase to DB2 migration, Part 1: Process and methodology
publish-date=11292012