Migrating Database Stored Procedures--A Case Study

Technologies changing with each passing day and global business trends cause tremendous existing system fail to catch up with latest business requirement changes. System migration and update become the buzzword in IT fields recently. One of the key topics is backend database migration, specially stored procedure migration. Because of the apparent incompatibility between different DBMS system that vendors provides, number of the issues are still on the fly in despite of so many migration tools available in the market. This paper will open an research to address the issues during stored procedures migration from SQL Server 2000 to DB2 UDB 8 based on a case study within SSZUTC, and try to illustrate the solutions from three perspectives. From the point of view of source code conversion, we focus on the problems that caused by difference between specifications (e.g. syntax and XML generation), OS supporting (e.g. SQL Server supports COM technology), DBMS (e.g. system tables), and build-in functions. We also propose solutions to some platform dependent problems by simulating these functionalities via Java stored procedures and taking advantage of compile and JDBC technology to solve the XML generation problem. Source code level engineering makes those proposal more flexible and inerrable. As to the post code migration stage, testing is the hot spot that is addressed in this paper. One of the most time-consuming tasks at the testing stage of database-based application is to generate test case for stored procedure. It is crucial to the efficiency of testing, thus greatly affecting the life cycle of software development. Although various techniques have been proposed to automatically generate test case for general application, few, if any, of them are intended for stored procedure. To fill the gap, an automatic test case generation method for stored procedure is proposed in this paper. It's also a source code level and path-oriented method, which employs SQL query to combine together stored procedure and data in database to generate test case. They are especially useful in legacy system porting project as demonstrated in the case study. Furthermore, a sample test case generator for stored procedure is provided. Finally, from the experimental project perspective, there are some lessons learned based on project plan, resource allocation and migration strategies. Project related data are also provided for future reference. Though this paper deals with the migration issues that come out in migrating from MS SQL Server 2000 to IBM DB2, our approaches have a lot in common techniques which can be leveraged in dealing with other database products' migration...