DB2 AS/400 (iSeries) to PostgreSQL Online Presentation

Demo presentation text:


Let me show you a demo presentation of the migration tools provided by Ispirer Systems for the DB2 iSeries to PostgreSQL database migration.

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:

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

• SQLWays Studio – a GUI tool that is designed to simplify the database or application script conversion process tuning for the SQLWays command-line tool. Works with one script at a time.

• SQLWays Commander - a GUI tool that is designed to simplify the database or application scripts conversion process tuning for the SQLWays command-line tool. Works with several scripts at a time.

• Sqlways.exe – a command line tool that can be run in batch mode and can perform the automatic conversion of the database using the ODBC connection or the database and application code provided in scripts.

During this demo I will show you the DB2 iSeries to PostgreSQL migration using SQLWays Wizard, SQLWays Studio and SQLWays Commander Tools mentioned in the list provided.

First let’s start with SQLWays Wizard.

This is a tool that can connect to DB2 iSeries database using an ODBC connection only. So, to migrate from DB2 iSeries to PostgreSQL database, you need to provide the ODBC DSN for DB2 iSeries on the “Choose a Source Database” page. Also you need to specify a user and a password with the appropriate rights for making selection from system tables from DB2 iSeries.

On a “Choose a Target Database” page you need to choose a target database from the list of the supported databases. As you can see a PostgreSQL database is specified here. You also need to provide other credentials for the PostgreSQL database for support of definitions and data load using native utilities of this database.

All the objects that SQLWays manages to extract from the DB2 iSeries it depicts in a tree view on the “Specify Database Objects or Query” page. Here you can select all the objects required for the migration.

After that you can specify different options for automatic DB2 iSeries to PostgreSQL conversion, like option that controls the schema name conversion, reserved words conversion, data types conversion, identifier names conversion and etc. Also there are options that can help you to run the definitions and data migration as separate processes.

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, like SQL files with the converted definitions of the objects, TEXT files with the data prepared to be loaded into the target database, BAT or SH files with the commands for native utilities to load both definitions and data on Windows or on Unix like OS.

All the DB2 iSeries to PostgreSQL migration process is performed in two stages. It’s an export stage during which the information is extracted from a source database and transformed and import stage, during which loading of the prepared information to a target database is performed. Each of them has separate logging information.

For the Export process there are sqlways.log files provided that contain the information about: the objects read from the DB2 iSeries 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 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 concerning whether there were any failures or no during the export process and the Total information about the whole database export.

Similar information but in more readable format is provided in the sqlways_report.html file. There is a summary table with the complete export status and statistics per each object type and there are also tables with the list of objects and more detailed statistics for each object extracted from DB2 iSeries database.

As for the import process, SQLWays generates a sqlways_imp.log file for the import into the PostgreSQL database that contains information about what object is being loaded and whether there are any issues during the import process or no.

As you can see the data from the source database is transformed into an IFX file for subsequent loading into Oracle.

Let’s check the results of the migration by comparing a procedure call from the source database with a call of the same procedure from the target database. As you can see, the results are identical.

Now let’s turn to the SQLWays Studio and SQLWays Commander tools that are designed for database scripts and application scripts conversion purposes.

In SQLWays Studio to convert from DB2 iSeries to PostgreSQL all you need to do is to provide the conversion direction using the “Source” and “Target” options, place the source DB2 iSeries code to the right pane of the tool and Run the conversion. At the bottom of the tool you can see the log of the conversion process that shows whether there are any errors during the script conversion from DB2 iSeries to PostgreSQL database. After the conversion you can compare both source and target scripts in one tool side-by-side for your convenience.

In SQLWays Commander there are also two panes which give you the opportunity to specify a file or files or the whole directory for the conversion. So, to convert from DB2 iSeries to PostgreSQL you should specify the source and target from the list of supported databases, choose the files for conversion and click Run. When you specify the directory for the conversion you also need to provide the template for the files that you would like to convert from DB2 iSeries to PostgreSQL database and provide the target directory. At the bottom there is a generated conversion log with the information about conversion issues if any. After the conversion by double-clicking the file in the source and in the target pane you can review the conversion results side-by-side like in SQLWays Studio.

That’s all that I wanted to tell you in this demo. The information about the peculiarities of DB2 iSeries to PostgreSQL migration and other tools that Ispirer Systems provides for database migration support will be described in our next demos or can be found on our website.

I hope this demo was useful. Thank you!

Do you want to move from DB2 AS/400 to PostgreSQL?

Let's do it together!

Learn more