sobota, 26 września 2015

MSSQL -> DB2 migration, useful awk scripts

Introduction
Database migration is a painstaking task, mostly manual, but with a little help of simple awk scripts it can be alleviated. As an example, I'm using Adventure Works Databases 2012 sample data and by means of "Generate scripts" tool I'm creating text scripts containing all SQL objects.First task
Output scripts are encoded as UTF-16, therefore one can spend a lot of time running grep command without any result. Thus, the first thing to do is to transform scripts to UTF-8 encoding and change CRLF line break to LF.

iconv -f UTF-16 $1 | dos2unix >`basename $1`

List of objects
Before starting any activity, it is a good idea listing all objects to migrate and prepare a working spreadsheet to track migration progress (listobjects.awk)

awk -v objtype=ALL -f listobjects.awk iscript.sql >list.csv

Tables
For tables migration very useful is free tool DCW (Data Conversion Workbench). This tool extracts table schema directly from MSSQL and transforms them to DB2 format. Important: the tool does not handle foreigns key correctly, but it can be improved by a Python tool.Split objects into files
Although MSSQL "Generate scripts" tool allows exporting objects into separate files, very often we have the objects in one big text file. Nevertheless, during migration it is very convenient to have a single object in a single file and migrate them one after one. obj.awk script executes this task.

awk -v extracttype=ALLOTHERS -f obj.awk iscript.sql

As a result, all objects except tables, are put into directories: views, triggers, types, procedures and function.
Example: view directory

Although MSSQL->DB2 migration is still manual and mundane process by applying very simple automation we can speed it up and make it more organized. Very interesting object splitting combine with transformation. This transformation (take a look into the body of obj.awk script) can be enhanced regarding the needs.