Contents

Read about the following new features of MTK V1.4:

• Migrating to IDS
• Migrating to DB2 UDB for z/OS
• Migrating from Sybase SQL Anywhere
• Oracle TIMESTAMP

Migrating Oracle/Sybase SQL Anywhere to Informix Dynamic Server

This section of the article describes the five-step process to be followed while migrating to Informix Dynamic Server databases.

Before performing the five-step migration process, create a new project with either Oracle or Sybase SQL Anywhere as the source and Informix Dynamic Server as the target.

Step 1: Specify source

MTK provides you the ability to either extract the source objects directly from the source database or import scripts containing SQL to be translated. For Informix Dynamic Server as the target, source extraction is supported only through a JDBC™ connection. Make sure that your system CLASSPATH contains the source JDBC driver before using MTK for extraction from a source database.

For the purpose of this article, you will extract a simple table from an Oracle database. If you have SQL already existing in a file, you can convert that by importing it.

If you are extracting objects directly from the source, provide the necessary information for connecting to the source after clicking the extract button. The panel that comes up shows the information from the Oracle source database that you are accessing. You can select the tables that you want to migrate and extract the DDL into a file. Currently MTK V1.4 does not support migration of Oracle Procedures/Functions, triggers, and packages to Informix. Support for migrating these to Informix is planned for future releases.

Step 2: Convert

After extracting the data into the file, click on the Convert tab. Here you can view the Oracle information extracted into the file. This script will be converted to a script usable by IDS by pressing the convert button. Before converting, you can change the default mappings of Oracle data types to any of their allowable IDS mapping by clicking the Global Type Mapping button. The Advanced Options can be used to change the format of the converted output. The Set Context button provides the user the ability to select a context file that has the information on which the file being converted depends.

After MTK has converted the Oracle file, an IDS (.ids) file and a report (.rpt) file are generated. The .ids file contains the IDS DDL statements that were translated from Oracle DDL statements during the conversion. The report file contains a list of errors that were identified during the conversion. The Refine page that opens after the conversion allows the user to view these files. This is discussed in the following section.

Step 3: Refine

The Refine panel allows the user to view the translated output, locate error messages, and make changes to the converted file. These changes can be made by clicking on either the source (Oracle) or target (IDS) tabs. If you do make any changes in this step, you will have to redo the Convert step for these changes to take effect in the translated file.

Step 4: Generate data transfer scripts

The Generate Data Transfer Scripts panel allows you to generate scripts for extracting data and lets you select the client with which you want to deploy the SQL scripts and the generated data to the target server. The scripts for the Informix target currently generated by MTK V1.4 can only be deployed using the MTK GUI. Manual deployment using the generated scripts is not supported. The available clients through which you can deploy to the Informix target client are JDBC and DBACCESS. The Informix JDBC driver is currently shipped with the MTK. If you plan to use DBACCESS, you need to run MTK on a machine that has the environment set up for running DBACCESS. For the purposes of this article, JDBC was used for DDL and data deployment to Informix Dynamic Server. Clicking the Next button lets you create files for deployment.

Step 5: Deploy to target

This is the final step in migration of a database from a source to the Informix target. In this step, the MTK provides you the ability to deploy the DDL, extract the data, and load the data into the target database.

For Informix as the target, you will have to provide the following information:

• Host Name: The machine on which you have Informix server deployed.
• Port: The port number for your Informix server.
• Server Name: The name of the Informix server in which your database will be deployed. This is the same as the value of the INFORMIXSERVER environment variable on the machine on which you have Informix server installed.
• Database Name: The name of the database to which you want to deploy the DDL and the data. Currently MTK supports deployment to Informix ANSI databases only.

The Advanced options button also lets you specify the DB_LOCALE and the CLIENT_LOCALE of your Informix database. If you want to either create a new database or recreate an exiting Infrmix database select the (Re)create option. You will then have to specify the credentials to be used on the Informix server to which you want to deploy your DDL and data. The MTK provides you the ability to deploy the DDL, extract data and load the data into the target database.

After filling in the required fields, click the Deploy button. This will deploy the data to your target server. A verification report will be generated which can be used to check for any problems with the deployment. This report indicates the number of source database objects deployed to Informix target database.

Tip: If you are using DBACCESS for deployment to Informix and you machine is not a trusted client, MTK will prompt you for a password during the deployment process. You will need to provide the same password as you did in the credentials on the Deploy panel.

Target database: DB2 UDB for z/OS

MTK V1.4 supports migrations to a second new target, DB2 UDB for z/OS. Migrating to DB2 UDB on this IBM mainframe platform previously required using a separate, beta product. With a successful beta period now complete, IBM can incorporate support for migrations from Oracle 8i, 9i, and 10g to DB2 UDB for z/OS Version 8 into MTK V1.4.

When migrating to DB2 UDB on z/OS opposed to non-mainframe systems, there are a number of unique setup requirements. DB2 must be configured to support Java™ user-defined functions and procedures, which requires special workload manager (WLM) support. Additionally, an FTP server must run on the mainframe. Since the z/OS platform uses the EBCDIC file format instead of ASCII, which is common for servers running the Oracle database, the source data format must be converted. During the data deployment step MTK uses the z/OS FTP server's built-in ASCII-EBCDIC conversion when required to transform data to the z/OS-native format. Further, resource recovery services (RRS) must be properly configured. Please reference DB2 UDB for z/OS documentation for details (see Resources). Finally, and just as in non-z/OS targets, the DB2 server must be cataloged on the machine from which MTK is run.

Migrating to DB2 UDB for z/OS occurs through the familiar five-step process, as referenced above: 1) Specify the Source, 2) Convert, 3) Refine, 4) Generate Data Transfer Scripts and 5) Deploy. MTK presents the same user interface for steps one through three, regardless of your chosen target database. However, there are user interface differences in the fourth and fifth steps. Let's take a look at what's changed when migrating to DB2 UDB for z/OS.

Generate data transfer scripts

Figure 6, below, is a screenshot of the tab for the Generate Data Transfer Scripts step. You are presented with three options for the location of Oracle data that will be extracted. Choose to store the data on the zSeries® system (option 1 or 3) when you're ready to migrate. The second option is only meant to provide a local copy of the table contents. Next, fill in the root DSN, which relates to where the data files will be stored on the z/OS machine before importing into DB2. Check with your the z/OS administrator for the proper value, if required. Then click Create Scripts.

In comparison to DB2 UDB for Linux®, UNIX® and Windows® migrations, there are fewer options on this panel. MTK always loads data to DB2 UDB for z/OS using the LOAD utility with the REPLACE option. An IMPORT utility is not available on z/OS. Also, since LOAD requires that data files be placed on the EBCDIC z/OS machine, the ASC or DEL file format choices presented in the Linux/UNIX/Windows context are irrelevant.

Deploy to target

The last step in the migration process brings you to the tab shown in Figure 7, below. This tab has a number of new fields due to the design of DB2 UDB for z/OS. Enter the name of the DB2 server as it appears in your local catalog and your credentials. Then specify up to two different workload managers responsible for user-defined functions and stored procedures, according to the z/OS configuration. Next enter the appropriate FTP information, especially the volume name. Clicking Advanced Allocations for Load allows you to specify non-default volumes for LOAD auxiliary files such as SYSUT1 and SYSERR. Finally, check any appropriate combination of the bottom three checkboxes and click the Deploy button.

Support for migration from Sybase SQL Anywhere databases

MTK V1.4 provides limited support for migrating Sybase SQL Anywhere version 9 (ASA) databases to Informix Dynamic Server. It also provides significant support for migrating Sybase SQL Anywhere version 9 databases to DB2 UDB for Linux, UNIX, and Windows databases. The steps to be followed for migrating from Sybase ASA to either Informix or DB2 UDB for Linux, UNIX, and Windows are same as noted above. For migrating to Informix, DDL and data extraction from the Sybase ASA database is only supported using JDBC. For DB2 UDB for Linux, UNIX, and Windows as the target, you can use either JDBC or ODBC. For extracting from Sybase ASA databases, JConnect 5_2 or later must be installed and accessible through the system $PATH and $CLASSPATH.

Oracle TIMESTAMP

An additional feature of MTK 1.4 is the support for the Oracle data type TIMESTAMP. Introduced in Oracle 9i, TIMESTAMP supports an optional precision ranging from 0 to 9. Further, TIMESTAMP can be used with either of two optional clauses: "WITH TIME ZONE" and "WITH LOCAL TIME ZONE".

MTK converts an Oracle TIMESTAMP of any precision to DB2 as a TIMESTAMP with precision of 6. When the target is IDS, TIMESTAMP is converted to DATETIME with precision of 5. Translation of the "WITH TIME ZONE" and "WITH LOCAL TIME ZONE" clauses is not supported to any target.

Resources

Learn

• DB2 UDB for z/OS documentation.
  
  
• DB2 UDB for z/OS LOAD utility.
  
  
• DB2 UDB for z/OS Basic Skills Information Center.
  
  
• Learn about the EBCDIC file format.
  
  
• Learn about migration issues in the developerWorks article Automate the Switch to DB2 Using the IBM DB2 UDB Migration Toolkit.
  
  

Get products and technologies

• Download IBM MTK
  
  

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Contents
• Migrating Oracle/Sybase SQL Anywhere to Informix Dynamic Server
• Target database: DB2 UDB for z/OS
• Support for migration from Sybase SQL Anywhere databases
• Oracle TIMESTAMP
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers