Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

DB2 To PostgreSQL Migration

This set of Scripts converts as much as possible of a DB2 UDB database. It does not work with DB2 zOS.
1- Extract your database schema with db2look. This will generate a db2 SQL script
db2look -d my_database_name -e -l -xd -o my_db2_sql_script 
2- Launch db2topg.pl against the db2 SQL script. This will produce 3 Postgres SQL scripts:
./db2topg.pl -f my_db2_sql_script -o my_output_dir -d db2_dbname -u db2_user -p db2_pwd
  • before.sql must be run before importing data. It contains the creation statements of tables, sequences, tablespaces (if you asked for them, which you probably don't want), roles.
  • after.sql must be run after importing data. It contains the creation statements of indexes, and most constraints.
  • unsure.sql contains everything that may have to be converted: views, triggers, check constraints…
3- Run the before.sql script on PosgreSQL.
psql -e --set=ON_ERROR_STOP=1 --single-transaction -f before.sql my_ postgres_database
You can also use or not the ON_ERROR_STOP and --single-transaction parameters, depending on wether you want the script to stop on error, and cancel everything. This remark holds true for all following psql commands
4- Extract the data from db2 with something like /path/to/db2 -f export.db2
5- Load the data into PostgreSQL with  
deltocopy -d my_output_dir | psql -e --set=ON_ERROR_STOP=1 my_ postgres_database
deltocopy produces all the COPY statements to load data into PostgreSQL to its stdout. You may add a -e option to specify encoding.
To get the full list of supported encodings in Perl:
perl -e 'use Encode; print join("\n",Encode->encodings(":all"));'
If you are in a hurry, and your server can bear it, there is a parallel mode. I advise you to only use it when you have validated that everything is ok at least once:
time deltocopy.pl -d my_output_dir -j 12 -o 'psql mydb' 2>&1 | tee import_log (time is optional, it's just to measure how fast you go)

Adapt the -o option to your needs, it's the command that each parallel instance will use to output data to PostgreSQL. It must connect to the correct database, with no password.

6- Run the after.sql script on PosgreSQL
psql -e --set=ON_ERROR_STOP=1 --single-transaction -f after.sql my_ postgres_database
7- Run the unsure.sql script on PosgreSQL. Watch it fail and fix the statements that could not be translated automatically.
psql -e -f unsure.sql my_postgres_database
8- Launch a VACUUM ANALYZE and And clear the dead tubles(fragmentation)


Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

Script to kill ALL IDLE Connection In postgreSQL