This article illustrates the testing process and methodology to be followed after migrating from Sybase ASE 12.x/15.x to DB2® 9.7.x/DB2 10.1.x. We will describe the process that will enable you to perform successful testing through various verification and validation processes.
- Verify that all the source DB (Sybase) objects are migrated to the target DB (DB2).
- Validate all the migrated objects of the target database, ensuring that they are equivalent in structure and functionality to that of the source database.
- All the pre-migration checks and verifications will be performed by the migration team.
- Migration teams will sign off on the migration prior to hand-off to QA team for validation.
Objectives of testing
The objective of any database migration testing is to perform post-migration validation to ensure the database structure and data integrity. This includes consistent data formats between the source and the target databases, and continued interfacing capabilities of post-database migration.
- Database structure
- Have the source and the target instances been properly configured?
- Have the data files, transaction logs, table spaces, and other objects that comprise the database been created correctly?
- Database integrity
- How referential integrity of the source database objects is maintained on the target.
- Every piece of code that modifies the data is thoroughly tested.
- Tests which cause every trigger to be fired.
- Whether all the users of source database have been moved to the target database with similar privileges and permissions.
- Does each user of the application have a separate password to the database, or is there a shared database account for all users of the application?
- What is the permission-level of the account(s) used to access the database?
- Validate that the whole database is physically secure.
- Data formats
- All the target database data types and their formats are maintained in line with the source database data types and formats.
- When it is necessary to change the data type or the data format, ensure that there is no change to results from equivalent queries against the source and the target databases.
- All the objects of the target database are able to perform their intended functionality at similar or better levels compared to the corresponding source database objects.
Critical success factors
Successful database porting and data migration will be critical to the business, and each migration challenge is different. However, the following could be the critical success factors for any statement of work (SOW).
- Object count — All the source databases object are migrated to the target database.
- Data format — Database objects should be similar in structure between the source and the target database.
- Object functionality — Functional output of the migrated objects should match the source object functional output.
- Object integrity target — Database object referential integrity should be maintained with the source database.
- Data integrity — Data generated on the target database is the same as the source database-generated data.
- Security — Security aspects of the source database are maintained in the target database.
Figure 1 depicts the QA migration roadmap.
Figure 1. QA process flow
Figure 2 depicts the QA environment setup and post-migration verification and validation.
Figure 2. Post-migration verification and validation
Post-migration verification and validation
The following are the phases of validations to be carried out in achieving the overall database migration objectives:
- Verify the QA/shared QA environment has been provided with all required access rights for the QA team.
- Verify that all source objects are migrated to the target DB.
- Verify object count and object names.
- Verify customer inventory.
- Validate object parameters.
- Validate all object parameters (number of columns, data types, constraints etc.).
- Validate that all the migrated objects are integral in their database structure
compared with the source database.
- Validate the referential integrity of the target database with reference to the source database.
- Validate object functionality.
- Validate that the output of the target database object is the same as of the source database.
- Validate database security.
- Validate that all users are created on the target database.
- Validate that all groups are created.
- Validate all authorization and authentication of users and groups.
- Validate object performance.
- Tables, views and procedures.
Test data preparation
The following method(s) will be used to obtain data for the test environments:
- Copy an entire production database.
- Copy a subset of a production database.
- Create data using the Application Under Test (AUT).
- Create data using a utility.
- Combination (use some production data supplemented by fictitious data).
Suspension criteria and resumption requirements
- The testing will be suspended if the migrated DB provided by the development team is rejected in the pre-test verification as described below:
- Pre-test verification
- Verify Sybase and DB2 database located on the predetermined drop point.
- Verify the existence of all required documentation, including customer inventory.
- Verify that the database release notes are documented in the changes that are to be made, if any.
- Execution of smoke tests to determine the basic functionality of the DB2 database.
The QA team will distribute a database verification document to the project team upon completion of these steps. Regression of any resolved issues will follow the database verification and testing will continue. Upon completion of the pre-test verification, Regression testing will begin on the tests that originally failed.
- The testing will be resumed after receiving a fresh release of DB2 database from dev with fixes for all the blocking issues and the pre-test verification is passed.
Incident tracking processes
During testing, defects will be recorded using a defect management tool as identified during the initiation phase. The defects will be reviewed and prioritized and assigned for fix, if required. Defects, which are agreed as valid, will be categorized as follows:
Table 1. Defect categorization
|Fatal||Defects that would result in complete omission of a requirement from the scope of the document/code or applicable standards and guidelines.|
|Major||Defects with observable departures from the standard or guidelines resulting in incomplete and ambiguous documentation and program code.|
|Minor||Cosmetic problems like spelling, formatting, etc.|
|Cosmetic/Suggestion||These are primarily suggestions for improving/enhancing the functionality.|
Table 2. Defect status
|Open||Defects when reported and not reviewed by development team.|
|Fixed||The fixed status indicates that a change was made to the code and will be available in the next build.|
|Next phase||Defects that cannot be fixed in this release.|
|Need more information||Need more information or NMI indicates that the developer does not have enough information to duplicate or fix the defects.|
|Cannot be fixed||There may be extenuating circumstances where the defects will not be fixed because of technology, time constraints, and a risk of de stabilizing the code or other users. A better status to use is not to be fixed.|
|Closed||Only the defect reporter uses this status; developers or managers cannot use it. If a defects is not reproducible by the assigned developer or defects verifier, the defects reporter needs a chance to clarify or add to the defects.|
|Reviewed||This status is only given by the project managers. They give this once they review a defect and assign them to the developer for fixing it.|
Figure 3 depicts the incident-tracking process.
Figure 3. Incident-tracking process
Test deliverables are the artifacts given to the stakeholders of software projects during the software development lifecycle. There are different test deliverables at every phase. Some deliverables are provided before the testing phase and some after the testing cycles is over. The types of test deliverables:
- Test case documents
- Test plan
- Testing strategy
- Test scripts
- Test data
- Test trace-ability matrix
- Test results/reports
- Test summary report
- Install/config guides
- Defect report
- Release notes
System and hardware requirements
- Workstation — An individual standard configuration desktop to be provided for all QA resources, with access to dedicated QA environment, QA tools, and Sybase and DB2 clients installed.
- QA environment and access details — The QA environment is to be installed with the correct Sybase and DB2 versions with all required access rights, to be made available for the QA team.
- The Sybase and DB2 DDLs provided by the development team will be deployed by the QA team to create the source and the target database instances. However, the dev environment can also be dedicatedly made available for QA for the validation cycle.
- Acceptance criteria for the service provider.
- Requirement documents
- End-user acceptance condition (EUAC)
- Business-user acceptance condition/cases
- Development release document (DRD)
- Application/Database to test
- Acceptance criteria for end client
- Client acceptance criteria.
- All agreed-upon deliverables/artifacts must signed off.
- All agreed validation checks should be executed.
- All validation checks should have passed.
- End-user and business-user acceptance conditions, if any, have been completely satisfied.
- Signed-off mail from end client for the project.
- Client acceptance criteria.
This article describes the importance of various factors involved in testing: QA, post-migration verification, validation, defect and incident tracking, etc. Using these testing strategies will help with the successful completion of project deliverables.
I would like to thank to Dan A. Simchuk (firstname.lastname@example.org) for his expertise and review comments.
- Be sure to read "Sybase to DB2 migration, Part 1: Process and methodology" and Part 2.
- 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.
- 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.
- Participate in the discussion forum.
- Check out the developerWorks blogs and get involved in the developerWorks community.