DB2 – Ryslander.com

Moving from DB2 to MSSQL

Moving data from DB2 to Microsoft SQL-server (MSSQL) can involve some difficulties, because there has to be a transformation of datatypes from one RDBMS to other.

DB2 and MSSQL doesn’t contain the exact same data types and therefore a translation/conversion between the two RDBMS has to occur.

This article from Microsoft explain how the feature DB2ToSQL handles that conversion. And can also be used for inspiration for your own handling of transferring data between the to RDBMS’s.

The article can be found here: Project Settings (Type Mapping) (DB2ToSQL)

Installing and configuring an DB2 ODBC CLI-driver on Windows

An DB2 CLI-driver is a self-sufficient driver that supports a subset of the functions that are provided by the DB2 ODBC-driver.

First download the DB2 ODBC CLI-driver from IBM’s homepage. As such you don’t need to install the driver. You just need to copy it to the location where you want it to reside.
In the below example the directory for the driver is: C:\Software\DB2_ODBC\
The file downloaded from the IBM-homepage is then unpacked into the directory v10.5fp11_nt32_odbc_cli

When the driver is unpacked go to the directory bin. In this directory you need to execute the command db2cli install -setup. This installs/makes the driver visible inside ODBC Data Sources in Windows.

Now you need to create or copy an existing db2cli.ini file to the directory (in this example): C:\Software\DB2_ODBC\v10.5fp11_nt32_odbc_cli\clidriver

Below is an example of what the contents of an db2cli.ini file could be. The db2cli.ini file is “just” a file containing meta-information about the alias that you want to use for you driver inside ODBC Data Sources in Windows.

Now you need to create or copy an existing file db2dsdriver.cfg. This file is to be placed in the directory (in this example): C:\Software\DB2_ODBC\v10.5fp11_nt32_odbc_cli\clidriver\cfg

Below is an example of what the contents of an db2dsdriver.cfg file could be. The db2dsdriver.cfg file contains the more technical information about the alias that you created in the db2cli.ini file. Therefore, the alias in this file must correspond to an alias in the db2cli.ini file. Pointing the alias in the db2cli.ini file to a server and database.

NB! The port number (port=”50000”) in the example below doesn’t have to be the port number used on your system. This is just the default port number for an DB2-instance.

Saving temporary tables in DB2

In DB2 it’s possible to save temporary tables in the schema SESSION.

SSIS and IBM DB2 – Don’t do it!

SSIS is a product that should be indifferent to whatever RDBMS you use. That’s also true. It will work with every RDBMS through drivers ODBC, OLE DB, ADO.NET etc. BUT it works best with Microsofts SQL-server.

This blogpost will take a look at using IBM DB2 with SSIS. Below is a test using ODBC-driver for IBM DB2 from IBM, OLE DB for IBM DB2 from Microsoft and an ADO.NET-driver.

Transfer rate of data

Transferring 100.000 rows from one IBM DB2-server to another IBM DB2-server.

OLE DB-driver for IBM DB2 from Microsoft	6 minuts
ADO.NET-driver	30 seconds
ODBC-driver for IBM DB2 from IBM	5 seconds

As seen above the ODBC-driver for IBM DB2 from IBM is the best solution when it comes to transfer rate. The OLE DB-driver for IBM DB2 from Microsoft is a really poor choice. But using the ODBC-driver for DB2 from IBM is not the solution to everything!

Lookup-task in SSIS

As seen in the picture below it’s only possible to use an OLE DB-connection in the Lookup-task. The Lookup-task is really slow and it might be better doing it in SQL. Regarding a lot of the tasks in SSIS, they are really slow (see links below).

OLE DB Command

SSIS also contains a ‘OLE DB Command’-task and no ‘ODBC Command’-task.

Fast load/Bulk load

OLE DB
It’s NOT possible to use the fast load option with the OLE DB-driver when it comes to IBM DB2. It’s possible to choose the fastload option, but you will get the error below.

The reason why the OLE DB-driver is that slow is probably that it transfers one row at a time.

ODBC
It’s possible to use a batch/fast load load option in ODBC.

ADO.NET
It’s also possible to use a bulk/fast load option in ADO.NET.

Links

The links below is to different sources that can help you with SSIS.

SQL Server Integration Services Design Patterns
A book that describes different design pattern for SSIS.

Task Factory for SSIS from Pragmatic Works
A collection of optimized SSIS-tasks.

Cozyroc
A collection of optimized SSIS-tasks.

PragmaticWorks free online training videos
Free online training videos about SSIS (search for SSIS).

Pluralsight
Online training videos in SSIS (requires a paid subscription to Pluralsight).

Download Microsoft OLE DB v.5 for IBM DB2

Go to the page https://www.microsoft.com/en-us/download/details.aspx?id=42295

Select language and the download.

Choose the correct bit-version for you OS.

NB! It has not been possible for me to get this driver to work with SSIS.

How to install and configure DB2 ODBC driver

The following shows you how I got an ODBC connection to DB2 on a Windows Server 2008R2. After doing a search on the Internet I found this page that really helped me a lot. They do it a bit different.

NEW
If you also want to install the IBM Control Center you might want to look at this page. This also installs the ODBC out-of-the-box.

If your need a 32 or 64-bit version for Windows and DB2 version 9.7. You should look for something like this.

On this page you can find and download the ODBC-drivers that fits your system. I you don’t know what version of DB2 that you’re running, you can contact your DBA. The DB2-version can be found using the command DB2level.

Choose either ODBC 32-bit or 64-bit for your DB2-version and download it for Windows.

If your need a 64-bit version of the ODBC-driver for Windows and DB2 version 9.7. You should look for something like this.

NB! You need to create an IBM-account to download the file.

Copy the content of ZIP-file containing the ODBC-driver that you just downloaded to c:\program files\IBM. The path is optional, you can choose a different path if you want to or are using a 32-bit ODBC-driver. The files and folders in c:\program files\IBM, should look something like this.

Start a Windows command-prompt as administrator. If you don’t know how to do this, you can take a look at this page.

Go to the folder c:\program files\IBM\bin and type db2oreg1 –i (if you later want to uninstall it type db2org1 –u).

Go to the folder c:\program files\IBM\bin and type db2oreg1 –setup (Thanks to Noel for this comment).

This will register the DB2 ODBC-driver in Data Sources (ODBC) on Windows. You can take a look at the drivers tab.

NB! If you have installed af 32-bit version of the ODBC-driver on a 64-bit system, then you have to look in the 32-bit version of the ODBC Administrator to find the driver. The 32-bit version of the ODBC Administrator can be found here: C:\Windows\SysWOW64\odbcad32.exe (Thanks to Grae for this comment)

Now you have to setup a ODBC-driver for DB2. You can either make a System or User DSN.
In this example i’m creating a User DSN. Go to the User DSN-tab and press the Add-button.

Choose the DB2 ODBC-driver and press the Finish-button.

Give the DSN a name that you can use in your programs. Here I have just given it the name DB2DB.

Then press the Add-button and go to the Advanced Settings-tab.

Here you need to press the Add-button to add the settings below.
Database: Should point to the database that you want to connect to.
Hostname: Should point to the server where your DB2-server is installed.
Port: The portnumber where the DB2-server on the Hostname is responding.
Protocol: The protocol used to connect to the DB2-server. There’s different options as you can se in the picture below.

The final settings should be the ones listed below.

Now you press the OK-button and you have a new DB2 ODBC connection and your done. You can now use this DB2 ODBC connection in your different programs.

NOTE
If you get the error described below in SAS. Then you most likely didn’t install the correct version 32/64-bit for your operating system. Install the correct bit-version of the Control Center from the IBM DB2 Client Server pack (further information at the start of this post).

The SAS/ACCESS Interface to DB2 cannot be loaded. The SASDBU code appendage could not be loaded