Oracle to Informix Online Presentation


Demo presentation text:

Hello,

Let me show you a demo presentation of the migration tools provided by Ispirer Systems for the Oracle to Informix database migration.

First of all, I’d like to mention that Ispirer Systems provides tools and services for automating databases and applications migration processes for a variety of conversion directions. The list of tools supplied for such a kind of automation is as follows:

• Ispirer Migration and Modernization Engine – a command line tool that can be run in batch mode and can perform the automatic conversion of the database or the database and application code provided in scripts.

• SQLWays Wizard – a GUI tool that is designed to simplify the database conversion process tuning for Ispirer MnM Engine. Works with the source database using the ODBC connection.

• Ispirer Migration Studio – a GUI tool that is designed to simplify the database or application script conversion process tuning for Ispirer MnM Engine. Works with one script at a time.

• Ispirer Migration Commander - a GUI tool that is designed to simplify the database or application scripts conversion process tuning for Ispirer MnM Engine. Works with several scripts at a time.

In this demo, I will show you the Oracle to Informix migration using SQLWays Wizard, Ispirer Migration Studio and Ispirer Migration Commander Tools mentioned in the list provided.

For this demo, we have prepared an Oracle database. You can see its content in Oracle Management Studio, which is displaying on the screen. We have also prepared an empty database on Informix server, called oracle2informix. We are going to use it for the migration as well.

First let’s start with SQLWays Wizard – you can see it on your screen.

This tool connects to the source database – in our case is Oracle - using the ODBC connection only. For the target Informix database, it creates DB-ACCESS utility commands. After starting SQLWays Wizard, on the first screen we have to specify the license information for this product, and we can specify a project directory, where set of files required for the project is stored. These files contain such information as the data type mappings, identifier mappings, objects selected for the conversion, options that has been selected when tuning up the migration process.

So, in order to migrate from Oracle to Informix database, we need to provide the ODBC DSN for Oracle on a “Choose a Source Database” page. In addition, we have to enter a user name and a password. We would recommend specifying the user, which has the appropriate rights to work with the system tables in Oracle database, because a lot of additional information will be extracted from Oracle database for the business logic conversion from Oracle to Informix.

On a “Choose a Target Database” page, we have to select Informix as a target database from the list of the supported databases.

To run the import we need to specify:

The server name

The database name

The user name and the password, which we are going to use for loading the objects to the Informix database.

On the advanced page, we need to specify a bin directory on the current computer where DBACCESS tool resides in.

All the objects that SQLWays Wizard manages to extract from Oracle are depicted in a tree view on the “Specify Database Objects or Query” page. Here we can select all the objects required for the migration.

In the target pane, you can see the tree view of the selected objects.

On a “Specify DDL and data options” page there is a set of options that help us tune the conversion process up. One of the most commonly used options is to omit schema names in SQL scripts. This option helps us control the way the schema names are converted from Oracle to Informix database.

So we just specify “yes” if we prefer to remove all schema names from the conversion from Oracle to Informix database. There is also an option that controls the reserved words conversion.

By default, all the reserved words are enclosed in double quotes. We can change their behavior by adding some symbols to the reserved words template here.

In addition, I would like to mention such option like the data type mapping that can be global or local. Global data type mapping can be changed for the whole database conversion. Local data type mapping implies such situation when going to the specific table, we can change the data type mapping by tuning the data type for the specific column in that table. During the conversion, the local data type mapping has higher priority than the global one.

When talking about the database conversion using SQLWays Wizard, there is also an option that controls the way the DDL and the Data are converted: we can run their conversion as separate processes – for example, we have a lot of data in Oracle database, and we just want to check the definition conversion from Oracle to Informix database, but not to convert the data in the first run. In order to get that done, we have to go to the data options tab and uncheck export the data option - in this case, no data will be converted. When, instead, we want the data only to be converted, we should go to the DDL options tab, then select SQL scripts instead of the global data type mapping, and uncheck generate DDL.In this case only the data will be converted.

There are many different options, which help tune the migration project up. If you are interested in some specific options, please, contact us, and we will be glad to assist you in this.

On the “Specify Export File Options” page, you need to provide the directory, which serves as an intermediate repository for the whole migration process and stores all the automatically generated files.

There is also the “run in trace mode” option, which allows us to see the commented source and its equivalent.

On a “specify import options page” there are two main options that actually control the import process. The first one is to import to the local or remote target database will be executed from the local system, which means that we have an Informix client installed on our current computer, and we can run the import process from this computer.

There is also an option that provides information about the remote system, where the import process will be carried out. So, after the export has been completed, we can move all the files generated by SQLWays Wizard to the remote computer - it can be either on Windows or on Unix system.

We will perform the export first, and then proceed to import into the Informix System from this current location.

The import process has already started, and we can see its progress on the migration status page.

For the Export process there is a sqlways.log file, which contains the information about: the objects read from the Oracle database, their number, the number of rows of data and the size of the data per each table extracted, the number of lines of code (LOC) per each business logic object, like views, synonyms, stored procedures, functions, triggers, packages and etc. At the end of the export log file there is the summary information per each object type concerning the number of objects, common size of data, number of rows of data or lines of code, depending on the object type, the export status, which shows whether there were any failures or no during the export process and the Total information about the whole database export.

The same information about the export process is provided in the SQLWays_report.html file.

There is a summary table with the complete export status and statistics per each object type, there are also tables with the list of objects, and the detailed statistics per each object extracted from the database.

This is the SQLWays report file.

If we are talking about the import process, there is a sqlways_imp.log file (import log file) that represents the import into the Informix system, and actually, this is the information which Informix server provides us with, so the tool does not control the import process. It just initiates the commands import, one by one, for DBACCESS into the Informix database.

As for the files generated for this process, for example, for tables there are generated SQL file, DDL file with a commented source in its equivalent in the same file.

Also there is a text file with the generated data, batch file with DBACCESS tool specification for maintaining the load of definitions and data into the target database.

As for the stored procedures and business logic objects, you can see the SQL file that contains the source and its equivalent in one file, and the bat file with the DBACCESS tool executing the SQL statement in the SQL file.

Now let’s check the results of the converted database. Let’s open the DBACCESS utility in Informix and check whether the objects were converted appropriately into the Informix database.

In order to check the validity of the converted Business Logic, we need to run one of the Stored Procedures on both Oracle and Informix servers and compare the results.

Let’s choose the stored procedure called Procedure2 for this purpose. As you can see this procedure contains the cursor statements, as well as loop statements. Let’s run them and check the result.

Now let’s run the same SP in the Target database.

As you can see, the results are identical in both databases. So the conversion may be considered successful.

Now let’s turn to Ispirer Migration Studio and Ispirer Migration Commander tools that are designed for the database scripts and application scripts conversion purposes from Oracle to Informix.

Migration Studio works with one script at a time. In order to perform the conversion, we have to place the source Oracle code to the left pane of the tool. Now, let’s make this code conversion from the left to the right pane. In order to do that, we have to specify the source and the target directions first, and then just click run. At the bottom of the tool is a log of the conversion process that shows the status of the code conversion from Oracle to Informix database.

After the conversion, we can compare both source and target scripts in one tool side-by-side.

Now let us proceed to Ispirer Migration Commander. The main principle of work of this tool is same as of the Migration Studio - it works with the scripts conversion as well. The difference is that Migration Commander allows converting multiple scripts at a time. Let’s take, for example, some scripts in our source Oracle database.

And let’s convert them all from Oracle to the target Informix database. We can specify a file, multiple files, or the whole directory for the conversion. Now let’s run the conversion process. In the same way as for the previous tools, the command line is used for maintaining the conversion from one database to another, and we can see now how the conversion is being performed. Each file, one by one, is being converted from Oracle to Informix database.

Now let’s review the results of the conversion. By double-clicking the SQL file, we can compare both the source and its equivalent in the target pane.

That’s all that I wanted to tell you in this demo about the database migration from Oracle to Informix using Ispirer MnMTK.

This is just a review of the tools and their supported features. In case if you need any additional information about the conversion performed by Ispirer MnMTK, just contact us, and get our assistance and the expertise support.

Thank you very much for your attention. Bye.