Blog do projektu Open Source JavaHotel

poniedziałek, 29 grudnia 2014

MS SQL -> DB2 migration, foreign keys

Introduction
During migration from MS SQL Server to DB2  I was facing a problem to generate foreign keys (in DB2 supported format) from MS SQL generated object script without having an access to MSSQL database. It is not a problem to recreate them manually if you have several objects but it could be a problem if you are dealing with hundreds or thousands of them.
So I decided to spend some time on creating a simple program (in Python) to accomplish the task automatically.
The source code is available here (Eclipse PyDev project).
Packages
The solution comprises several packages.
readfiles : read lines from several files (list of files in constructor). It flattens several files to one single reader.
atomizer : transforms input into sequence of atoms. For instance:

CREATE TABLE [dbo].[departments](
 [dept_no] [char](4) NOT NULL,
 [dept_name] [varchar](40) NOT NULL,
PRIMARY KEY CLUSTERED 
Atomizer will output: CREATE TABLE [dbo.department] ( [dept_no ... etc. It simply breaks input  (list of text lines) into sequence of elements ignoring spaces, line breaks etc.
tokenizer : transforms list of atoms into the sequence of recognized keywords ignoring elements out of importance here.
For instance.
Assuming set of constants describing keywords important here:
ALTER=0
SEMICOLON=1
TABLE=2
ADD=3
CONSTRAINT=4
FOREIGN=5
KEY = 6
REFERENCES = 7
BEGCOMMENT=8
ENDCOMMENT=9
CREATE=10
VIEW=11
FUNCTION=12
PROCEDURE=13
GO=14
The output will be the sequence: CREATE (constant 10) TABLE [dbo.department] (as single tekst) ( [dept_no ... (etc). Tokenizer makes further analysis more easier.
foreign : selects foreign key definition and prepares data structure: base table, constraint name, list of columns, reference table name and reference column list. Example:
ALTER TABLE [dbo].[dept_emp]  WITH CHECK ADD FOREIGN KEY([dept_no])
REFERENCES [dbo].[departments] ([dept_no])
ON DELETE CASCADE
or
ALTER TABLE [dbo].[dept_emp]  WITH CHECK ADD CONSTRAINT DEPT_NO_DEPARTMENT_FK FOREIGN KEY([dept_no])
REFERENCES [dbo].[departments] ([dept_no])
ON DELETE CASCADE
publish : takes data structure (describing foreign key definition as describe above) and prepares DDL in DB2 format. For instance:

ALTER TABLE dbo.dept_emp ADD CONSTRAINT "FK_dept_emp_departments" FOREIGN KEY
   (dept_no)
   REFERENCES dbo.departments
   (dept_no)
@
The main program

def test4():    
    R = readfiles.ReadFiles(INF)
    A = atomizer.Atomizer(R)
    T = tokenizer.TOKENIZER(A)
    F = foreign.ForeignSearcher(T)
    a = F.nextForeign()
    f = open("output/foreign_keys.db2","w")
    while a != None :
        s =  foreignDB2.foreignDB2(a)
        print s
        print ""
        print ""
        f.write(s)
        f.write("\n")
        f.write("\n")

        a = F.nextForeign()
    f.close()
The first four statements (objects construction) can be fused into a single statement.
Example input and output.
Conclusion
I found this approach useful. I also reused it in resolving several other problems.

  • Migrate only a subset foreign keys definition. Read firstly list of tables (without foreign keys) already migrated and emits only foreign keys related to them.
  • Prepare list of all objects (tables, view, UDF and SP) in MS SQL object script. 

Brak komentarzy:

Prześlij komentarz