2.3 Porting

< Day Day Up >

The steps required to perform a database and application port are introduced in this section. The methods employed can vary, but at a minimum, the following steps are required. This chapter introduces:

Porting the database structure
Porting the database objects
Modifying the application
Modifying the database interface
Migrating the data

2.3.1 Porting the database structure

After you assess and plan the port, the first step to take is to either move or duplicate the structure of the source database onto a DB2 UDB system. Before this can happen, differences between the source and destination (DB2 UDB) structures must be addressed. These differences can result from different interpretation of SQL standards, or the addition or omission of particular functions. The differences can often be fixed syntactically, but in some cases, you must add functions or modify the application.

Metadata is the logical Entity-Relationship (E-R) model of the data, and describes the meaning of each entity, the relations that exist, and the attributes. From this model, the SQL data definition language (DDL) statements that can be used to create a database that can be captured. If the database structure is already in the form of metadata (that is, a modeling tool was used in the design of the system), it is often possible to have the modeling tool generate a new set of DDL that is specific to DB2 UDB. Otherwise, the DDL from the current system must be captured and then modified into a form that is compatible with DB2 UDB. After the DDL is modified, it can be loaded and executed to create a new database (tables, indexes, constraints, and so on.).

There are three approaches that can be used to move the structure of a DBMS:

Manual methods: Dump the structure, import it to DB2 UDB, and manually adjust for problems
Metadata transport: Extract the metadata (often called the "schema") and import it to DB2 UDB
Porting and migration tools: Use a tool to extract the structure, adjust it, and then implement it in DB2 UDB

Manual methods

Some DBMSs offer a utility that extract the database structure and deposit it into a text file. The structure is represented in DDL, and can be used to recreate the structure on another database server. However, before the DDL will properly execute in DB2 UDB, it is likely that changes are needed to bring the syntax from the source system into line with DB2 UDB. So, after you extract the DDL, and transport it to DB2 UDB, you will likely have to edit the statements.

Besides syntactic differences, there may also be changes needed in data type names and in the structure. It is often easiest to simply run a small portion of the source DDL through DB2 UDB, and examine the errors. From this, most of the corrections that will be needed will become evident. Chapter 5, "Conversion reference" on page 149 provides detailed discussion of the manual conversion process and examples. Please also see the appropriate DB2 UDB porting guide for more detail on the differences in syntax, names, and structure that you can expect at:

http://www-3.ibm.com/software/data/db2/migration/

Metadata transport

Many database structures are designed and put in place using modeling tools. These tools let the designer specify the database structure in the form of entities and relationships. The modeling tool then generates database definitions from the E-R description. If the system to be ported was designed (and maintained) using one of these tools, porting the database structure to DB2 UDB can be as simple as running the design program, and specifying an output of the form compatible with DB2 UDB.

Porting and migration tools

Probably the most popular means of porting a database structure (and other portions of a DBMS) today is the use of a porting and migration tool that cannot only connect to and take structural information from the source database, but can also modify and then deposit it in the destination database. As mentioned above, the IBM DB2 Migration Toolkit can be used to perform the migration using this method.

2.3.2 Porting the database objects

Database objects (stored procedures, triggers, and user-defined functions) are really part of the application logic that is contained within the database. Unfortunately, most of these objects are written in a language that is very specific to the source DBMS, or are written in a higher-level language that then must be compiled and somehow associated or bound to the target DBMS for use.

Capturing the database objects can often occur at the same time that the database structure is captured if the objects are written in an SQL-like procedural language and stored within the database (for this, you would use one of the porting and migration tools). For those objects written in higher-level languages (Java, C, PERL, etc.), capture and import means transferring the source files to the DB2 UDB system and finding a compatible compiler and binding mechanism.

Stored procedures and triggers will have to be converted manually unless the tool used to extract the objects understands the stored procedure languages of both the source DBMS and DB2 UDB. The IBM DB2 Migration Toolkit is an example of a tool that can aid in the conversions of stored procedures and triggers from various DBMSs to DB2 UDB. Expect many inconsistencies between the dialects of procedural languages, including how data is returned, how cursors are handled, and how looping logic is used (or not used).

Objects that are written in higher-level languages must usually be dealt with manually. If embedded SQL is included in the objects, it can be extracted and run through a tool that might be able to help convert the SQL code to be compatible with DB2 UDB. After that, each section can be replaced and then compiled with the modified higher-level code.

Note that conversion of objects will require testing of the resulting objects. This may mean that test data will be needed (and must be populated into the database structure) before testing can occur. This would require that you do at least some of the work in Porting step 7. Migrating the data before you complete this conversion phase.

After the conversion is completed, some adjustments will probably still be required. Issues such as identifier length may still need to be addressed. This can be done manually (looking at statistics, i.e. all database names over a certain length, and then doing a global search and replace on the names that appeared), or by using a tool (such as the IBM DB2 Migration Toolkit) that understands what to look for and how to fix it.

2.3.3 Modifying the application

While the porting of the database structure and objects can be automated to some extent using porting and migration tools, application code changes will mostly require manual conversion. If all database interaction is restricted to a database access layer, then the scope and complexity of necessary changes is well defined and manageable. However, when database access is not isolated to a database access layer (that is, it is distributed throughout application code files, contained in stored procedures and/or triggers, or used in batch programs that interact with the database), then the effort required to convert and test the application code depends on how distributed the database access is and on the number of statements in each application source file that require conversion.

When porting an application, it is important to first migrate the database structure (DDL) and database objects (stored procedures, triggers, user-defined functions, and so on). It is then useful to populate the database with a test set of data so that the application code can be ported and tested incrementally.

Few tools are available to port actual application code since much of the work is dependent upon vendor-specific issues. These issues include adjustments to logic to compensate for differing approaches to transaction handling, join syntax, use of special system tables, and use of internal registers and values. Manual effort is normally required to make and test these adjustments. Often, proprietary functions used in the source DBMS will have to be emulated under DB2 UDB, usually by creating a DB2 UDB user defined function and/or stored procedure with the same name as the proprietary one being ported. This way, any SQL statements in the application code that call the proprietary function in question will not need to be altered. Migration tools such as the IBM DB2 Migration Toolkit are equipped with some of the most commonly used vendor-specific functions and will automatically create a DB2 UDB-equivalent function (or stored procedure) during the migration process.

The vendor-specific DB2 UDB outline the necessary code changes that are needed to work successfully with DB2 UDB. Send an e-mail to <db2mig@us.ibm.com> to obtain the latest version of the porting guide. Contacts for EMEA and AP are <emeadbct@uk.ibm.com> and <APDB2@nz1.ibm.com> respectively. Applications written in a high level language with embedded SQL (DML) can be converted automatically by extracting the SQL portion from the source, adjusting it with porting and migration tools, and then reinserting it into the source.

Another issue when porting high-level language code (C, C++, Java, COBOL, etc.) involves compiler differences. Modifications to the application code may be required if a different compiler and/or object library are used in the DB2 UDB environment (which may be caused by the selection of a different hardware or OS platform). It is vital to fully debug and test such idiosyncrasies before moving a system into production.

For more information on various application development topics relating to DB2 UDB, and to view various code samples, visit the DB2 Universal Database™ v8 Developer Domain Web page on the IBM Web site:

http://www7b.software.ibm.com/dmdd/

2.3.4 Modifying the database interface

Applications that connect to the source database using a standardized interface driver, such as ODBC and JDBC, usually require few changes to work with DB2 UDB. In most cases, simply providing the DB2 UDB supported driver for these interfaces is enough for the application to be up and running with a DB2 UDB database.

There are certain circumstances where the DB2 UDB-supported driver for an interface does not implement or support one or more features specified in the interface standard. It is in these cases where you must take action to ensure that application functionality is preserved after the port. This usually involves changing application code to remove references to the unsupported functions and either replacing them with supported ones, or simulating them by other means.

Applications that use specialized or native database interfaces (Oracle's OCI as an example) will require application code changes. Such applications can be ported using the DB2 UDB's native CLI interface, or by using a standardized interface such as ODBC, JDBC, etc. If porting to CLI, many native database-specific function calls will need to be changed to the CLI equivalents; this is not usually an issue as most database vendors implement a similar set of functions. DB2 UDB's CLI is part of the SQL standard and mappings of functions between other source DBMSs and DB2 UDB CLI can be found in the applicable DB2 UDB porting guide.

DB2 UDB also provides a library of administrative functions for applications to use. These functions are used to develop administrative applications that can administer DB2 UDB instances, backup and restore databases, import and export data, and perform operational and monitoring functions. These administrative functions can also be run from the DB2 UDB Command Line Processor (CLP), Control Center, and DB2 UDB scripts.

The following lists some of the common interfaces used with DB2 UDB. Most of these interfaces are described more fully in the IBM DB2 UDB Application Development Guide: Programming Client Applications V8, SC09-4826. Additional useful vendor-specific database information can be found in the applicable DB2 UDB porting guide.

JDBC and SQLj

DB2 UDB provides several JDBC drivers to write dynamic SQL programs in Java. DB2 UDB provides support for the Type 2, Type 3, and, new to Version 8, the Type 4 drivers.

SQLj offers developers a way to write static SQL programs using Java. SQLj programs generally outperform their JDBC counterparts because the query access plans of executable statements have been optimized before run-time.

More information about the Java supported interfaces can be found on

DB2 Java Application Development Web page at: thttp://www7b.software.ibm.com/dmdd/zones/java/.
The article DB2 and Java: The Big Picture posted on the DB2 Developer Domain also provides a good summary about the different Java options available for DB2 UDB version 8. The link to this article is: http://www7b.software.ibm.com/dmdd/zones/java/
Considering SQLj for Your DB2 V8 Java Applications is a white paper that addresses access to relational data from Java. This paper can be found at http://www7b.software.ibm.com/dmdd/library/techarticle/0302tsui/0302tsui.html
Optimal DB2 performance with SQLj and JDBC explores some of the performance issues related to using Java with DB2 UDB. This article can be accessed with a Developer Works logon ID through the link: https://www6.software.ibm.com/reg/devworks/dw-db2sqlj-i?S_TACT=102B7W81&S_CMP=DB2DD

Embedded SQL (static and dynamic)

DB2 UDB provides programmers the option of writing applications with SQL statements directly embedded within the host language. The SQL statements provide an interface to the database while the host language provides facilities to perform the application logic. DB2 UDB supports several host languages including C/C++, FORTRAN, COBOL, and Java (SQLj). Programmers have the option of using static or dynamic SQL, depending on the nature of the application.

ODBC

Microsoft's ODBC standard provides a set of APIs for accessing a vendor's database. Vendors must supply their own driver that implements a subset of the API for accessing the database. The DB2 UDB's CLI driver can be used on its own to access a DB2 UDB database or as an ODBC driver. DB2 UDB conforms to most of the Level 3 compliance level for ODBC.

ADO and OLE DB

Microsoft's ActiveX Data Objects provide a set of methods for accessing data from a wide variety of data sources including relational databases, HTML, video, text, and just about any other source of data. Access to the data is handled by ADO and is accessed through a service such as OLE DB or ODBC. In order to use OLE DB with DB2 UDB, you must first download the newest driver from the OLE DB Web page.

Microsoft .NET

.NET is Microsoft's new development platform that competes with the J2EE standard. The .NET framework programming model that enables developers to build Web-based applications, smart client applications, and XML Web services applications, which expose their functionality programmatically over a network using standard protocols such as SOAP and HTTP. Full support for the .NET standard for DB2 UDB is on the way. Currently, IBM is offering a .NET driver program for developers interested in writing applications using the .NET standard. More information about the .NET Framework program and DB2 UDB's support for .NET can be found on the DB2 UDB .NET Program Web page at:

http://www7b.software.ibm.com/dmdd/zones/vstudio/

Perl DBI

DBI is an open standard API that provides database access for client applications written in Perl. DBI defines a set of functions, variables, and conventions that provide a platform-independent database interface. The latest DB2 UDB Perl driver and information about Perl can be found at the DB2 UDB Perl DBI support Web page:

http://www-3.ibm.com/software/data/db2/perl/

DB2 UDB CLI

DB2 UDB's CLI Driver implements most of the function set defined in the ODBC standard as well as additional functionality specific to DB2 UDB. This interface offers more available functionality than do the other non-embedded and driver options. The latest information about developing applications with CLI can be found in the DB2 UDB reference manuals: Call Level Interface Guide and reference CLI Volume 1, SC09-4849, and Volume 2, SC09-4850.

Stored procedures

Another popular method of interfacing with the database is through stored procedures. Stored procedures can be written in DB2 UDB's SQL procedural language, or in an external programming language such as C/C++ or Java. Restricting database access through stored procedures offers numerous benefits such as a reduction in network traffic (all processing takes place on the server), and providing an additional layer of isolation between the application code and business logic. An excellent reference for building SQL stored procedures can be found in the book DB2 SQL Procedural Language for Linux, UNIX, and Windows, ISBN 0-13-100772-6. Additional material can also be found in Application Development Guide: Programming Server Applications, SG09-4827.

2.3.5 Migrating the data

You can move data (often called migration) from one DBMS to another by using numerous commercially available tools (including porting and migration tools such as the IBM DB2 Migration Toolkit, Ascential DataStage, and others).

In many cases, as the data is moved, it is also converted to a format that is compatible with the new DBMS (DATE/TIME data is a good example). This process can be quite lengthy when there is a large amount of data, which makes it quite important to have the conversions well defined and tested.

In some cases, it will still be necessary to do some customized conversions (specialized data, such as time series, geospatial, etc. may require extensive adjustments to work in the new DBMS). This is usually accomplished through the creation of a small program or script.

< Day Day Up >