By: Parvathy Natraj | Updated: 2018-03-08 | Comments (1) | Related: > Other Database Platforms
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.
- Lines starting with "--" are comments and we don’t need those lines
- Lines starting with the word "DATA CAPTURE changes" indicate the implementation of CDC like features in DB2 and we don’t need these lines
- Lines starting with "IN" and "Organize” are also not required to migrate the tables to SQL Server
- 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.
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.
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.
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.
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.
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.
After successful execution, a SQL script has been created. The same SQL script has been validated successfully in SQL Server Management Studio.
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.
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.
The variables sourceFileFolder and the SourecFilePattern will be passed to the script task for further processing.
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".
I created a connection manager to connect to the SQL database. Also, I created a file connection manager to deploy the SQL file.
An Execute SQL Task has been configured to execute the SQL script against the target SQL server.
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.
The below image confirms that the DB2 file has been migrated to a SQL Server script.
The below image confirms that the DB2 tables have been successfully created in SQL Server.
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.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-03-08
