Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Generating SQL Server Create Table Scripts from DB2 Create Table Scripts


By:   |   Read Comments   |   Related Tips: More > Other Database Platforms

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

We have database tables in DB2 and we want to migrate them to SQL Server. The DB2 DBA has generated create table scripts from the DB2 server. I would like to use these scripts to create the tables in SQL Server, but the syntax is not the same so how can I easily create these DB2 tables in SQL Server?

Solution

In this tip we will cover how this can be done using some C# code and SSIS.

Assumptions

In this tip, our aim is to read the DB2 script and create tables in SQL Server without any indexes or default values.

In this tip, I created two sample DB2 tables which comprise of multiple DB2 specific datatypes. This script has been generated in DB2. By closely observing the supplied script, we can make the below assumptions.

  1. Lines starting with "--" are comments and we don’t need those lines
  2. Lines starting with the word "DATA CAPTURE changes" indicate the implementation of CDC like features in DB2 and we don’t need these lines
  3. Lines starting with "IN" and "Organize” are also not required to migrate the tables to SQL Server
  4. Lines starting with the "Alter table" statement can be safely ignored

The below image represents the actual DB2 create table script for the two tables.

Sample DB2 Script - Description: Sample DB2 Script

In this tip, I will be creating a SSIS package to read the DB2 script and to generate a script for SQL Server. Later, the newly generated SQL script will be deployed against the target SQL Server to create the tables.

SSIS Package

At this stage, my aim is to build a script component in a step by step manner. Once the script component is fully developed, then I will showcase how to enhance the SSIS package further.

The below image represents the SSIS package with a simple script component.

SSIS Package Overview - Description: SSIS Package Overview

Script Iteration #1

Inside the script component, I have the below C# code to read the DB2 script and generate the SQL script.

The below code will read each line in the DB2 script until the end of the file. If a line contains the words "Create table", then it will set the "NewTable" flag to true.  In addition, if the line contains the word "DATA CAPTURE CHANGES" or "ORGANIZE BY ROW" or "IN" then the line will be ignored. By this way, only the selected content from the source DB2 script file will be migrated to the SQL script file.

 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Text;
 
public void Main()
{
// TODO: Add your code here
 
            string filename = "C:\\Analysis\\DB2_To_SQL\\DB_Script.ddl";
            string splitFilename = "C:\\Analysis\\DB2_To_SQL\\DB_Script_New.ddl";
            var tw = new StreamWriter(splitFilename, true);
            const Int32 BufferSize = 12;
 
            using (var fileStream = File.OpenRead(filename))
            using (var streamReader = new StreamReader(fileStream, Encoding.ASCII, true, BufferSize))
            {
                String line;
                bool newTable = false;
 
                while ((line = streamReader.ReadLine()) != null)
                {
                    if (line.Contains("CREATE TABLE"))
                    {
                        newTable = true;
 
                    }
                    else if (line.Contains("DATA CAPTURE CHANGES") || line.Contains("ORGANIZE BY ROW") || line.Contains("--") || line.Contains("IN "))
                    {
                        newTable = false;
                    }
                    if (newTable)
                    {
                        tw.WriteLine(line);
                    }
                }
                tw.Close();
            }
Dts.TaskResult = (int)ScriptResults.Success;
}
			

After successful execution of the package, a new script file has been created and the contents can be seen as per the image below.

Output Script - Description: Output Script

After analyzing the output, I have realized that the migrated script still refers to the DB2 datatypes and we must convert them to a suitable SQL Server datatype. This link has a detailed mapping of datatypes between DB2 and SQL Server. Based on these mappings, we need to update our script to SQL Server datatypes. I have identified that below DB2 datatypes that need to be migrated to SQL Server datatypes.

DB2 Datatype SQL Server Datatype
BLOB VARBINARY(MAX)
CLOB VARCHAR(MAX)
DECFLOAT FLOAT
TIME TIME(0)
TIMESTAMP DATETIME2(50)

Script Iteration #2

The script task can be extended further to replace the above datatypes. The C# code will be extended further as shown below. The C# code will look for the DB2 data type, if the data type is found then the replacement datatype will be replaced on the line content and that line will be written into the SQL script.

After the successful execution of the SSIS package, the SQL script has been created. It is confirmed that the SQL Server data types have been successfully replaced in the script.

 
public void Main()
{
// TODO: Add your code here
 
            string filename = "C:\\Analysis\\DB2_To_SQL\\DB_Script.ddl";
            string splitFilename = "C:\\Analysis\\DB2_To_SQL\\DB_Script_New.ddl";
            var tw = new StreamWriter(splitFilename, true);
            const Int32 BufferSize = 12;
 
            using (var fileStream = File.OpenRead(filename))
            using (var streamReader = new StreamReader(fileStream, Encoding.ASCII, true, BufferSize))
            {
                String line;
                bool newTable = false;
 
                while ((line = streamReader.ReadLine()) != null)
                {
                    if (line.Contains("CREATE TABLE"))
                    {
                        newTable = true;
 
                    }
                    else if (line.Contains("DATA CAPTURE CHANGES") || line.Contains("ORGANIZE BY ROW") || line.Contains("--") || line.Contains("IN "))
                    {
                        newTable = false;
                    }
                    if (newTable)
                    {
                        if (line.Contains("BLOB"))
                        {
                            int i = line.IndexOf("BLOB");
                            line = line.Substring(0, i) + "VARBINARY(MAX),";
                        }
                        else if (line.Contains("CLOB"))
                        {
                            int i = line.IndexOf("CLOB");
                            line = line.Substring(0, i) + "VARCHAR(MAX),";
                        }
                        else if (line.Contains("DECFLOAT"))
                        {
                            int i = line.IndexOf("DECFLOAT");
                            line = line.Substring(0, i) + "FLOAT,";
                        }
                        else if (line.Contains("TIME"))
                        {
                            int i = line.IndexOf("TIME");
                            line = line.Substring(0, i) + "TIME(0),";
                        }
                        else if (line.Contains("TIMESTAMP"))
                        {
                            int i = line.IndexOf("TIMESTAMP");
                            line = line.Substring(0, i) + "DATETIME2(50),";
                        }
                        tw.WriteLine(line);
                    }
                }
                tw.Close();
            }
Dts.TaskResult = (int)ScriptResults.Success;
}
			

After successful execution of the package, a new script file has been created and the contents can be seen as per the image below.

Output script with SQL datatypes - Description: Output script with SQL datatypes

I noticed that we need a "Go" statement at the end of the create table statement. Hence, I extended the script to add "Go" after every create table statement.

Script Iteration #3

The below script will help us migrate the DB2 script to a SQL Server script by adding the GO command.

 
public void Main()
{
// TODO: Add your code here
 
            string filename = "C:\\Analysis\\DB2_To_SQL\\DB_Script.ddl";
            string splitFilename = "C:\\Analysis\\DB2_To_SQL\\DB_Script_New.ddl";
            var tw = new StreamWriter(splitFilename, true);
            const Int32 BufferSize = 12;
 
            using (var fileStream = File.OpenRead(filename))
            using (var streamReader = new StreamReader(fileStream, Encoding.ASCII, true, BufferSize))
            {
                String line;
                bool newTable = false;
                int RowCount = 1;
 
                while ((line = streamReader.ReadLine()) != null)
                {
                    if (line.Contains("CREATE TABLE"))
                    {
                        if (RowCount!=1)
                        {
                            line = "Go" + Environment.NewLine + line;
                        }
                        newTable = true;
 
                    }
                    else if (line.Contains("DATA CAPTURE CHANGES") || line.Contains("ORGANIZE BY ROW") || line.Contains("--") || line.Contains("IN "))
                    {
                        newTable = false;
                    }
                    if (newTable)
                    {
                        if (line.Contains("BLOB"))
                        {
                            int i = line.IndexOf("BLOB");
                            line = line.Substring(0, i) + "VARBINARY(MAX),";
                        }
                        else if (line.Contains("CLOB"))
                        {
                            int i = line.IndexOf("CLOB");
                            line = line.Substring(0, i) + "VARCHAR(MAX),";
                        }
                        else if (line.Contains("DECFLOAT"))
                        {
                            int i = line.IndexOf("DECFLOAT");
                            line = line.Substring(0, i) + "FLOAT,";
                        }
                        else if (line.Contains("TIME"))
                        {
                            int i = line.IndexOf("TIME");
                            line = line.Substring(0, i) + "TIME(0),";
                        }
                        else if (line.Contains("TIMESTAMP"))
                        {
                            int i = line.IndexOf("TIMESTAMP");
                            line = line.Substring(0, i) + "DATETIME2(50),";
                        }
                        line = line.Replace("\"", "");
                        if (line.Contains("CLS     ."))
                        {
                            line = line.Replace("CLS     .", "CLS.");
                        }
 
                        tw.WriteLine(line);
                    }
                    RowCount = RowCount + 1;
                }
                tw.Close();
            }
Dts.TaskResult = (int)ScriptResults.Success;
}
			

After the successful execution of the package, we can see a "Go" statement after every create table statement. We can also observe that the last column of the create table statement contains a comma.  As this is not valid syntax, it is mandatory to remove the comma. In addition, we need to include a closing bracket after the comma.

Script with Go statement - Description: Script with Go statement

Looking at the script it is still not right.  The last line has a comma and no closing parenthesis.

Script Iteration #4

I have extended the C# code to remove the comma and added a code snippet to add a closing parenthesis. Here is a screen shot and the complete code that can be found further down in the tip.

Script to remove comma in the output - Description: Script to remove comma in the output

After successful execution, a SQL script has been created. The same SQL script has been validated successfully in SQL Server Management Studio.

Output SQL script - Description: Output SQL script

Enhancing the SSIS Package

As a next step, we need to develop a scalable solution to dynamically migrate the DB2 script to SQL Server. In addition, the solution should be able to deploy the new SQL script to the target SQL Server.

The below image gives an overview of the scalable solution.

Scalable SSIS solution overview - Description: Scalable SSIS solution overview

The script task will loop through all available DB2 files and migrate them to SQL Server script files. Then the For Each Loop container will pick up each SQL script file and deploy it to the target SQL Server.

I have added three variables "SourceFilePattern", "SourceFilesFolder" and "TargetFilePath".

  • SourceFilePattern - will be used to store the pattern of the DB2 files (like *.ddl).
  • SourceFilesFolder - will be used to store the folder path for the DB2 files
  • TargetFilePath - will be used by the For Each File Loop container to lookup each SQL Server script file for deployment purposes.
Variable definition - Description: Variable definition

The variables sourceFileFolder and the SourecFilePattern will be passed to the script task for further processing.

Script task configuration - Description: Script task configuration

This is the complete C# code that has been extended further to make use of the new variables.

 
public void Main()
{
// TODO: Add your code here
            String strSourceFilesFolder, strSourceFilePattern;
            strSourceFilesFolder = Dts.Variables["User::SourceFilesFolder"].Value.ToString();
            strSourceFilePattern = Dts.Variables["User::SourceFilePattern"].Value.ToString();
            string[] fileList;
 
            DirectoryInfo directoryInfo = new DirectoryInfo(strSourceFilesFolder);
            string OutputFile;
            if (directoryInfo.Exists)
            {
                fileList = Directory.GetFiles(strSourceFilesFolder, strSourceFilePattern);
                foreach (string sourceFile in fileList)
                {
                    FileInfo sourceFileInfo = new FileInfo(sourceFile);
                    OutputFile = strSourceFilesFolder +"\\" + Path.GetFileNameWithoutExtension(sourceFile) + ".sql";
                    ConvertDB2ToSQLScript(sourceFile, OutputFile);
                }
            }
 
Dts.TaskResult = (int)ScriptResults.Success;
}
 
        public void ConvertDB2ToSQLScript(string Sourcefilename,string Outputfilename)
        {
            var tw = new StreamWriter(Outputfilename, true);
            const Int32 BufferSize = 12;
 
            using (var fileStream = File.OpenRead(Sourcefilename))
            using (var streamReader = new StreamReader(fileStream, Encoding.ASCII, true, BufferSize))
            {
                String line;
                bool newTable = false;
                int TableCount = 1;
 
                while ((line = streamReader.ReadLine()) != null)
                {
                    if (line.Contains("CREATE TABLE"))
                    {
                        if (TableCount != 1)
                        {
                            line = ")" + Environment.NewLine + "Go" + Environment.NewLine + line;
                        }
                        TableCount = TableCount + 1;
                        newTable = true;
 
                    }
                    else if (line.Contains("DATA CAPTURE CHANGES") || line.Contains("ORGANIZE BY ROW") || line.Contains("--") || line.Contains("IN "))
                    {
                        newTable = false;
                    }
                    if (newTable)
                    {
                        if (line.Contains("WITH DEFAULT"))
                        {
                            int i = line.IndexOf("WITH DEFAULT");
                            line = line.Substring(0, i) + (line.Contains(",") ? "," : ""); 
                        }
                        if (line.Contains("BLOB"))
                        {
                            int i = line.IndexOf("BLOB");
                            line = line.Substring(0, i) + "VARBINARY(MAX)" + (line.Contains(",") ? "," : "");
                        }
                        else if (line.Contains("CLOB"))
                        {
                            int i = line.IndexOf("CLOB");
                            line = line.Substring(0, i) + "VARCHAR(MAX)" + (line.Contains(",") ? "," : ""); 
                        }
                        else if (line.Contains("DECFLOAT"))
                        {
                            int i = line.IndexOf("DECFLOAT");
                            line = line.Substring(0, i) + "FLOAT" + (line.Contains(",") ? "," : "");
                        }
                        else if (line.Contains("TIMESTAMP"))
                        {
                            int i = line.IndexOf("TIMESTAMP");
                            line = line.Substring(0, i) + "DATETIME2" + (line.Contains(",") ? "," : "");
                        }
                        else if (line.Contains("TIME"))
                        {
                            int i = line.IndexOf("TIME");
                            line = line.Substring(0, i) + "TIME(0)" + (line.Contains(",") ?",":"");
                        }
 
 
                        line = line.Replace("\"", "");
                        if (line.Contains("Sales     ."))
                        {
                            line = line.Replace("Sales     .", "Sales.");
                        }
 
                        tw.WriteLine(line);
                    }
                }
                line = ")" + Environment.NewLine + "Go" + Environment.NewLine + line;
                tw.WriteLine(line);
                tw.Close();
            }
        }
			

The For Each Loop has been configured to access the folder location to lookup .sql files as shown in the picture below. The identified file name will be stored in the variable "TargetFilePath".

For each file loop configuration - Description: For each file loop configuration
For each file loop configuration - Variable - Description: For each file loop configuration - Variable

I created a connection manager to connect to the SQL database. Also, I created a file connection manager to deploy the SQL file.

connection managers

An Execute SQL Task has been configured to execute the SQL script against the target SQL server.

Execute SQL Task - Description: Execute SQL Task

The below image highlights that the SSIS package has successfully migrated the DB2 script to a SQL Server script. Also, the converted SQL script has been deployed to the target server.

SSIS Execution - Description: SSIS Execution

The below image confirms that the DB2 file has been migrated to a SQL Server script.

SQL Script Generation - Description: SQL Script Generation

The below image confirms that the DB2 tables have been successfully created in SQL Server.

Tables have been deployed - Description: Tables have been deployed

Summary

In this tip, we learned to develop a simple SSIS package to migrate DB2 create table scripts to SQL Server create table scripts. In addition, we also deployed the tables to the target SQL Servers.

Next Steps
  • Learn about alternative approaches to migration here.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Parvathy Natraj Parvathy Natraj is an independent Agile Data Analyst in the UK with Master Degree in Business Administration. She is an ISEB Certified tester and Microsoft Certified System Administrator.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools