Oracle -> DB2 migration, useful awk scripts

Introduction
Database migration is a painstaking process but by means of Oracle Compatible Feature migration from Oracle to DB2 can be done very easy. There is an excellent redbook explaining the process in more details. Although the complexity is reduced I created some useful awk scripts to support it.
While working on migration of SQL code it is very convenient to split all objects into separate files and in the case of any trouble to focus on a single file containing the object under scrutiny.
So I created a set of useful and simple awk scripts which make it easy. The source code is available here. It can be modified and extended according to needs.
Splitting Oracle source file
The following objects can be extracted and split into separate files: tables including indexes and constraints, foreign keys, packages headers, packages bodies, triggers, views, stored procedures outside packages, UDF, sequences and types.
The main script is obj.awk.
The launching sequence:

awk -v extracttype={value} -v objtype={value} -f obj.awk {input file(s)}

Two parameters are recognized: extracttype and objtype
objtype can have the following values

• TABLE
• VIEW
• TRIGGER
• PROCEDURE
• FUNCTION
• FOREIGN
• TYPES
• PACKAGE
• PACKAGEBODY
• SEQUENCE

extracttype can have the following values

• TABLES
• ALLOTHERS
• LIST

 The following actions are triggered:

• extracttype=TABLES, no objtype, all tables object are extracted
• extracttype=ALLOTHERS, no objtype, all objects but tables are extracted
• extracttype=LIST, objtype can have value or be ignored, only object names are listed
• no extracttype, objtype=value, only objects pointed by objtype are extracted

Example

  awk -v extractype=TABLES -f obj.awk {input file}

All tables are extracted.

awk -v extracttype=LIST objtype=SEQUENCE -f obj.awk {input file}

All sequence names are printed.

awk -f objtype=PACKAGE -f obj.awk {input file}

All package headers are extracted.

Extracted objects are saved in the following directories

• TABLE, tables
• VIEW, views
• TRIGGER, triggers
• PROCEDURE, procedures
• FUNCTION, functions
• FOREIGN, foreign
• TYPES, types
• PACKAGE, packages
• PACKAGEBODY, packagesbody
• SEQUENCE, sequences

Extracted code modification

Although very little is supposed to be done to have Oracle SQL code running on DB2, sometimes small adjustments can make life easier.

The following awk scripts are executed on the fly against every extracted object.

• TABLE, modiftable.awk
• VIEW, modifview.awk
• TRIGGER, modiftrigger
• PROCEDURE, (none)
• FUNCTION, (none)
• FOREIGN, (none)
• TYPES, modiftypes.awk
• PACKAGE, modifpackage.awk
• PACKAGEBODY, (none)
• SEQUENCE, modifsequence.awk

An example of modification script for the sequence.

@include "fun.awk"

{
  line = gensub(/CREATE SEQUENCE/,"CREATE OR REPLACE SEQUENCE","g",$0)   
  line = gensub(/MAXVALUE\ +[0-9]+\ /,"NO MAXVALUE ","g",line)
  printline(line)
}

The modification scripts can be extended or modified as needed.