Informix to PostgreSQL Online Presentation

Demo presentation text:


Welcome to this demonstration of the migration tools provided by Ispirer Systems for the Informix to PostgreSQL database migration.

In this video, I’d like to show you how easy the migration from Informix to PostgreSQL can be performed by Ispirer tools.

Before proceeding directly to the demo, I would like to say a few words about the Ispirer toolkit provided for automatic migration and modernization. There are three graphical tools based on a command line tool called

• Ispirer Migration and Modernization Engine – it can be run in batch mode, and performs the automatic conversion of the database or the database and application code contained in scripts.

• Ispirer SQLWays Wizard is a GUI tool designed particularly for the database migration. It works with the source database using the ODBC connection.

• Ispirer Migration Studio is a GUI tool oriented specifically to the conversion of a separate application or database script. Works with one script at the same time.

• Ispirer Migration Commander is a GUI tool designed for the conversion of multiple files containing application or database code. The difference is that it can work with several scripts simultaneously.

Let’s now begin the demonstration. For this demo, we have prepared an Informix database. You can see its content in the Informix DBACCESS Tool, which is displaying on the screen. We have also prepared an empty database on PostgreSQL server called ifmx2pg. 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 works with the source database through the ODBC connection. For the target PostgreSQL database it creates the native tool 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 have been selected for the conversion.

I’ve selected only one option here, and we can go next.

So, in order to migrate from Informix to PostgreSQL, we need to supply a user or system DSN name for the ODBC connection and specify it on a choose a source database page. Also we need to specify the user name and password credentials. We’d recommend specifying the user, which has the appropriate rights to work with the system tables in Informix database, because a lot of additional information will be extracted from Informix database for the conversion.

On a “Choose a Target Database” page, we now need to identify the target database from the list of the supported databases.

Also we need to provide other credentials for this target database, including server name, database name, user name and password that we are going to use for loading the objects to the PostgreSQL database.

All of the objects that SQLWays Wizard manages to extract from the source database are depicted in a tree view on the “Specify Database Objects or Query” page. Here we’re able to select all of the objects required for the migration from Informix to PostgreSQL.

In the target pane, you can see the tree view of the selected objects. As I’m using the project directory, I’ve selected all of the objects here. And we can go next.

On a “Specify DDL and data options” page there is a set of options that help us tune up the conversion process.

One of the most commonly used options is to omit schema names in SQL scripts. This option helps control the way the schema names are converted from Informix to PostgreSQL database.

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

I’d also like to turn your attention to such option as 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 a specific table, we can change the data type mapping by tuning the data type for a 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 Informix database, and we just want to check the definition conversion from Informix to PostgreSQL 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 simply switch off the export 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 Generation Options 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 up the migration project. 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, we identify the directory, which will serve as an intermediate repository for the entire migration process storing all of 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 the “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 of the files generated by SQLWays Wizard to the remote computer - it can be either on Windows or on Unix system.

I use this computer locally, so I’ll make 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.

Both the export and import processes has separate comprehensive logging information. For the export process SQLWays provides the SQLWays.log file which contains information about the objects read from the source Informix database, their number, the number of rows of data and the size of the database. At the end of the export log file there is a summary of information for each object type followed by totals for the entire database export.

Similar information but in a more structured and readable format is provided in a SQLWays_report.html file.

This includes a summary table with the complete export status and statistics for each object type along with tables containing the list of objects, and further detailed statistics for each object extracted from our source database.

This is the SQLWays report file.

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

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

Also there is a text file with the generated data,

batch file with the 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 tool executing the SQL statement in the SQL file.

Now let’s check the results of the converted database. Let’s open the pgAdmin utility in PostgreSQL and check whether the objects were converted appropriately to the PostgreSQL database.

In order to check the validity of the converted Business Logic, we need to run one of the Stored Procedures on both Informix and PostgreSQL servers and compare the results. Let’s choose a stored procedure for this purpose.

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

As you can see, the procedure contains the cursor statements and loop statements.

Now let’s turn to Ispirer Migration Studio and Ispirer Migration Commander tools that are designed to perform the database and application scripts conversion.

Migration Studio works with one script at the same time. To accomplish the conversion, let’s insert a fragment of the SQL code into the left pane - just like in the example provided.

Now, let’s make this code conversion from the left to the right pane.

At the bottom of the tool is a log of the conversion process that shows the status of the code conversion.

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

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

And let’s convert them all from the source to the target database. We can specify a file, multiple files, or the whole directory for the conversion.

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 Informix to PostgreSQL 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 Informix to PostgreSQL database 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 expertise support.

Thanks for listening to this demo.

Do you want to move from Informix to PostgreSQL?

Let's do it together!

Learn more