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 an Oracle ODBC-driver

Go to https://www.microsoft.com/en-us/download/details.aspx?id=104113 and download the Microsoft Connector for Oracle V1.2 that fits your OS. Here we’ll be using MicrosoftSSISOracleConnector-15.0.2000.215_x86.msi.

MicrosoftSSISOracleConnector-15.0.2000.215_x86.msi is the 32-bit version of the ODBC-driver.
MicrosoftSSISOracleConnector-15.0.2000.215_amd64.msi is the 64-bit version of the same ODBC-driver.

Install MicrosoftSSISOracleConnector-15.0.2000.215_x86.msi on the machine that needs to connect to Oracle.

When the installation is done you should be able to go to create an ODBC Data Sources with the below new Oracle ODBC driver.

Now you can enter the information needed to get access to Oracle-server through ODBC.

If you don’t have or can’t get access through a TNSNames Connection. Then you can go to another location/server/PC and you TNSPing to get the information needed to use Standard Connection.

NB! It is possible to set e.g. the Initialization String in the Advanced-tab.

Get information about users in AD-group

The below batch-code (.bat) will ask you for an AD-group. It will then run the command net group for the entered AD-group. Paste the result to a temporary text file and open that text file in Notepad for you to view and search through.

@echo off
set /p gname="Enter groupname: "

net group /domain %gname% > C:\temp\groupoutput.txt
notepad C:\temp\groupoutput.txt

Get information about users AD-groups

The below batch-code (.bat) will ask you for an AD-user name. It will then run the command net user for the entered user. Paste the result to a temporary text file and open that text file in Notepad for you to view and search through.

NB! It is also possible to use the command gpresult, if the command net user doesn’t work for you.

@echo off
set /p uname="Enter username: "

net user /domain %uname% > C:\temp\useroutput.txt
REM gpresult /user %uname% /R > C:\temp\useroutput.txt
notepad C:\temp\useroutput.txt

 

Quick Guide to installing SAS ODBC-driver for SAS SPD-server

Below is a step-by-step guide to install and configure an ODBC-driver for SAS SPD-server. Be aware, that this guide installs the 64-bit version of the ODBC-driver.

  1. Download SAS ODBC-drivers from the SAS-homepage. Do a search on the web to find the page.
  2. Unpack and install odbcdrvrweb__94180__wx6__xx__web__1.zip
    This should be done as administrator.
  3. Unpack and install spdsclibsweb__99150__wx6__xx__web__1.zip
    This should be done as administrator.
  4. It is the best option just to install these drivers as suggested by default during the installation process. If you deviate from this – you can face issues when setting up the connections in ODBC.
  5. Extract the file clientlibs_spds50_Windows_x86_64.zip in the directory C:\Program Files\SASHome\SASScalablePerformanceDataServer\5.4\lib into the same directory C:\Program Files\SASHome\SASScalablePerformanceDataServer\5.4\lib.
  6. Copy all the files from C:\Program Files\SASHome\SASScalablePerformanceDataServer\5.4\lib (excluding the clientlibs_spds50_Windows_x86_64.zip) into the directory C:\Program Files\SASHome\SASDriversforODBC\9.46

If this is to be used by SSIS, then you will need to create these as 32-bit ODBC-connections. SSIS it not able (as of November 2021) to easily handle 64-bit ODBC-connections.

Below example is for the SPDPROD SPD-server library on the SPDSERVER01 server

On the Server-tab

Name <SERVER NAME>.<PORT> e.g. SPDSERVER01.5180

Press the Configure-button (in the dialog above)

Server Address <SERVER NAME> e.g. SPDSERVER01
User Name <I THINK THIS MUST BE THE USERNAME FOR USED FOR YOUR DBQ>
User Password < THE USER PASWWORD FOR USER USED ABOVE>
Connection Options DBQ='<FOUND IN YOUR SPD-SERVER CONFIGURATION>’ HOST='<SERVER>’ SERV='<PORT>’
E.g.
DBQ='<FOUND IN YOUR SPD-SERVER CONFIGURATION>’ HOST=’SPDSERVER01′ SERV=’5180′

On the Libraries-tab

Name <NAME OF THE LIBNAME ON THE SPD-SERVER TO BE ACCESSED>
Hostfile <NAME OF THE LIBNAME ON THE SPD-SERVER TO BE ACCESSED>
Description <DESCRIPTION>
Engine SPDSENG
Options DBQ='<FOUND IN YOUR SPD-SERVER CONFIGURATION>’

If you want to install the 32-bit version of the ODBC-driver after the installation of the 64-bit version of the ODBC-driver.
Then be aware, that the 32-bit version of the ODBC-driver is installed in this folder, if the default installation path is chosen for the 64-bit version of the ODBC-driver: C:\Program Files\SASHome\x86\SASScalablePerformanceDataServer\5.4\lib

If you do not have a tool to test a ODBC-connection available on the machine for the installation. It is possible to test these through PowerShell.

This is possible using the PowerShell code below.

$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "DSN=<NAME OF YOUR ODBC-CONNECTION>; UID=<USERID>; PWD=<USER PASSWORD>"
$conn.open()
$sql = "SELECT * FROM <TABLE IN LIBNAME ON SPD-SERVER>";
$cmd = New-Object System.Data.Odbc.OdbcCommand($sql, $conn);
$rdr = $cmd.ExecuteReader()
$rdr.read()
$rdr.GetValue(0)
$conn.Close();

Port scanning in PowerShell

The below script in PowerShell can scan a given range of ports for a specific server and will – if a port on the server is open – return the text: TCP port <PORT NUMBER) is open!

foreach ($port in <START PORT>..<END PORT>) {If (($a=Test-NetConnection <SERVER> -Port $port -WarningAction SilentlyContinue).tcpTestSucceeded -eq $true){ "TCP port $port is open!"}}

Example

foreach ($port in 3388..3390) {If (($a=Test-NetConnection MYSERVER -Port $port -WarningAction SilentlyContinue).tcpTestSucceeded -eq $true){ "TCP port $port is open!"}}

TCP port 3389 is open!

NB! I looks like there should be “something” answering in the other end of the port before you will receive a ‘TCP port XXXX is open!

Installing and configuring an SAS OLEDB-driver for MSSQL

To get the correct SAS OLEDB driver or newest SAS OLEDB driver. It is best to do a search e.g. on Google.
NB! You need an account at SAS to be able to download.

And even thou this guide for Installing and configuring an SAS OLEDB-driver for MSSQL is old – it is still very useful.

Be aware, that it is possible to code a program in e.g. .NET that reads a SAS-dataset. It can be done with the SasReader (currently in version 1.0.6).

Below code in C# reads a SAS-dataset and outputs it into a .CSV-file.
Credits to my colleague that figured this out.

using System;
using System.IO;
using SasReader;
using System.Text;

namespace SasToCsvConverter
{
    class Program
    {
        static void Main(string[] args)
        {
           // Define paths
           // string sasFilePath = @"C:\temp\<YOUR SAS-DATASET>.sas7bdat";
           // string csvFilePath = @"C:\temp\output.csv";
            try
            {
                // Initialize SAS file reader
                using (FileStream sasToParseFileInputStream = File.OpenRead(sasFilePath))
                {
                    SasFileReader sasFileReader = new SasFileReaderImpl(sasToParseFileInputStream);

                    // Open the CSV file for writing
                    using (var writer = new StreamWriter(csvFilePath, false, Encoding.UTF8))
                    {
                        // Read and write META DATA
                        var sasMetaColumns = sasFileReader.getColumns();

                        // Write header
                        var headerNames = new StringBuilder();
                        foreach (var column in sasMetaColumns)
                        {
                            headerNames.Append(column.getName()).Append(",");
                        }

                        // Remove the trailing comma
                        writer.WriteLine(headerNames.ToString().TrimEnd(','));

                        // Write DATA
                        long rowCount = sasFileReader.getSasFileProperties().getRowCount();
                        for (int i = 0; i < rowCount; i++)
                        {
                            var row = sasFileReader.readNext(); // object[]
                            var rowValues = new StringBuilder();
                            foreach (var value in row)
                            {
                                var stringValue = value?.ToString() ?? string.Empty;
                                rowValues.Append(EscapeCsvValue(stringValue)).Append(",");
                            }

                            // Remove the trailing comma
                            writer.WriteLine(rowValues.ToString().TrimEnd(','));

                            // Optional: Log progress
                            Console.WriteLine($"Processed row {i + 1}/{rowCount}");
                        }
                    }
                }

                Console.WriteLine("Conversion to CSV completed successfully.");
            }

            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }

        private static string EscapeCsvValue(string value)
        {
            if (value.Contains(",") || value.Contains("\"") || value.Contains("\n"))
            {
                return $"\"{value.Replace("\"", "\"\"")}\"";
            }
            return value;
        }
    }
}

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.