Microsoft OLE-DB Provider for DB/2 ( for SQL Server v2014 )

Download

Microsoft bundles a database connectivity provider for DB/2.

It is bundled within the Feature Pack.

Depending on the version of SQL Server you have installed, you will be downloading a different package.

For us, we are on SQL Server 2014.  And, so will be downloading the Feature Pack for that version.

Download Link

Microsoft SQL Server Feature Pack is available here

Link

Download

Link

Image

Details

There are two files, ENU\DB2OLEDBV5_x64.msi and ENU\DB2OLEDBV5_x86.msi.

Depending on your OS bitness, SQL Server bitness, and use-cases Server ( Engine [ Linked Server], Development [Business Intelligence Development Studio [BIDS] ) you will likely need one or both files.

Installation

Install Microsoft OLE-DB Provider for DB/2

License Agreement

Image

licenseAgreement_20170922_0208PM

 

Registration Information

Image

RegistrationInformation_20170922_0208PM

 

Feature Selection

Outline

  1. Changed installation path from “C:\Program Files\Microsoft OLE DB Provider for DB2” to “E:\Program Files\Microsoft OLE DB Provider for DB2”
    • The basis for the change being we will like to reserve our system drive (C: ) for the OS

 

Image

featureSelection_20170922_0209PM

 

Revised

featureSelection_20170922_0210PM

 

Ready to install the Program

ReadyToInstallTheProgram_20170922_0210PM

 

Installing

 

Installing_20170922_0211PM

 

Validation

 

UDL File

Steps

  1. Launch Windows Explorer
  2. Create a new udl file
    • The file can have any name as long as it’s extension is udl
  3. Once the file is created, please right click on it, and choose the Open menu button
  4. Screens
    • Connection

 

Images

Connection

The first tab that comes to focus is the “Connection” tab.  We will be good if we are trying to a SQL Server DB, but as we will be connecting to DB/2, we will go back and choose the Provider tab.

Provider

Provider – Original

Provider – Revised

Provider – Explanation
  1. Changed provider from “Microsoft OLE DB Provider for SQL Server” to “Microsoft OLE DB Provider for DB2

 

Advanced

Advanced – Original

SQL Server

Linked Server

Add new Linked Server

Linked Server – General

Image

Textual
Item Meaning Value we used
Linked Server The name that the Linked Server will be referred to DB2
Server Type Other Data Sources Other Data Sources
Provider Provider name of the providers installed on the system Microsoft OLE DB Provider for DB2
Product Name Can be anything
Data Source Please leave blank
Provider String Please get Provider String from your DB/2 team Data Source=dbraq.labdomain,org;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=dbraq.labdomain.org;Network Port=3300
Location Disabled when provider is DB/2
Catalog  Initial default catalog MVSBQ

Linked Server – Server Options

Image

 

Validate Linked Server

sp_tables_ex

Query


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

 

TroubleShooting

Network Address – When “Fully Qualified Domain Name” not used
Script

USE [master]
GO

if exists
(
    select *
    from   sys.servers
    where  [name] = N'DB2RAQ'
)
begin

    sp_dropserver  @server = N'DB2RAQ', @droplogins='droplogins'

end
go

/****** Object:  LinkedServer [DB2RAQ]    Script Date: 9/23/2017 8:49:54 AM ******/
EXEC master.dbo.sp_addlinkedserver
      @server = N'DB2RAQ'
    , @srvproduct=N'Microsoft OLE DB Provider for DB2'
    , @provider=N'DB2OLEDB'
    , @provstr=N'Data Source=dbraq;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=raq;Network Port=33006;Package Collection=MSNC001;'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2RAQ',@useself=N'False',@locallogin=NULL,@rmtuser=N'acct',@rmtpassword='password'
GO


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

Textual

OLE DB provider "DB2OLEDB" for linked server "DB2RAQ" returned message "A TCPIP socket error has occured (10022): An invalid argument was supplied.".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 35]
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "DB2RAQ".

Incorrect user credentials
Script

USE [master]
GO

if exists
(
    select *
    from   sys.servers
    where  [name] = N'DB2RAQ'
)
begin

    exec sp_dropserver  @server = N'DB2RAQ', @droplogins='droplogins'

end
go


EXEC master.dbo.sp_addlinkedserver
      @server = N'DB2RAQ'
    , @srvproduct=N'Microsoft OLE DB Provider for DB2'
    , @provider=N'DB2OLEDB'
    , @provstr=N'Data Source=dbraq.labdomain.org;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=dbraq.labdomain.org;Network Port=33006;Package Collection=MSNC001;'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2RAQ',@useself=N'False',@locallogin=NULL,@rmtuser=N'acct',@rmtpassword='password'
GO


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

 

Textual

OLE DB provider "DB2OLEDB" for linked server "DB2RAQ" returned message "The user does not have the authority to access the host resource. Check your authentication credentials or contact your system administrator.".

Summary

Microsoft provides a very capable OLE-DB Provider for DB/2.

Please keep in mind it is not an ODBC nor JDBC connectivity library.

BIDS – Oracle to SQL Server – Fetch data into Variables

Background

Now that we have the foundation stuff out of the way, let us return to our original problem.

Tried to read data from an Oracle table into local variables defined in a BIDS package, but no go.

Lineage

Here are earlier posts.

  1. “Oracle Database 11g Express Edition” – Installation on Windows
    Link
  2. Oracle – SQLPlus – Create new User
    Link
  3. Oracle Database Client on Windows
    Link

Exercise

Package

Launched BIDS and created a new package.

 

DataSource

Connection Manager

Provider – Native OLE DB \ Microsoft OLE DB Provider for Oracle

Connection
Image

Detail
  1. Password
    • Please enter the password for the user you have been provisioned
  2. Persist Security Info
    • Yes
  3. User ID
    • The user provisioned for this activity
  4. Data Source
    • localhost
  5. Provider
    • MSDAORA.1
Test Connection
Image

Data Source Designer
Image

Control Flow

Execute SQL Task Editor

Execute SQL Task Editor – General

Image

 

Outline
  1. Options
    • Code page :- 1252
  2. Result Set
    • Result Set :- Single row
      • We are only expecting a single record from the database
  3. SQL Statement
    • Connection Type :- OLE DB
    • Connection :- localhost.msftbids
    • SQLSourceType :- Direct input
    • SQLStatement :- select USER as currentUser, SYSDATE as currentDate from DUAL
    • IsQueryStoredProcedure :- False
    • Bypassprepare :- False

 

Execute SQL Task Editor – General – Parse Query

Image

Outline

The query parsed correctly.

Execute SQL Task Editor – Parameter Parsing

Image

Outline

We do not have any parameters

 

Execute SQL Task Editor – Parameter Parsing

Image

Outline
Result Name Variable Name Variable Type
currentUser User::currentUser String
currentDate User::currentDate Datetime

 

Execute SQL Task Editor – Expressions

Image

Outline

— None defined —

 

Script Task Editor

Script Task Editor – General

Image

Outline

 

Script Task Editor – Script

Image

Script Variable Name Value
ScriptLanguage Script Language Microsoft Visual Basic .Net
PrecompileScriptIntoBinaryCode Indicate whether the script is pre-compiled into binary code True
OptimizeScriptExecution Indicate whether the script is pre-compiled into binary code True
EntryPoint Specifies the entry point ScriptMain
ReadOnlyVariables Comma separated list of variables to be made available for read access currentUser, currentDate
WriteOnlyVariables

 

Script Task Editor – Script

Outline
  1. To access the User Variables in “Script Task” script, we can choose a couple of methodologies
    • Dts.VariableDispenser
      • Dts.VariableDispenser.LockForRead(varName) // Dts.VariableDispenser.GetVariables
  2. We stole code from ZappySys
    • Title :- 3 Ways -SSIS Read Write Variables – Script Task C# / VB.net
    • Link
      Link
Code

 



' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms

Public Class ScriptMain

    'http://zappysys.com/forums/topic/ssis-read-write-variables-in-script-task-csharp-or-vb-net/
    '//Example:  Dim myVarValue As Object = ReadVariable("User::MyVar")
    Private Function ReadVariable(ByVal varName As String) As Object
        Dim result As Object
        Try
            Dim vars As Variables = Nothing
            Dts.VariableDispenser.LockForRead(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex '//Handle things your way or throw back
        End Try
        Return result
    End Function

	Public Sub Main()

        Dim strMessage As String
        Dim currentUserLocalVar As Object
        Dim currentDBDateLocalVar As Object


        'Read Variables into local Variable
        currentUserLocalVar = ReadVariable("User::currentUser")
        currentDBDateLocalVar = ReadVariable("User::currentDate")

        'Display Data using msgbox
        MsgBox(currentUserLocalVar.ToString(), MsgBoxStyle.Information, "currentUser")
        MsgBox(currentDBDateLocalVar.ToString(), MsgBoxStyle.Information, "currentDBDate")


        Dts.TaskResult = Dts.Results.Success

	End Sub

End Class

Execution

Source Code Control

GitHub

Link

 

Dedicated

Dedicate to ZappySys.

The code shared here was a lifesaver for me.

 

 

Oracle Database Client on Windows

Background

In the last couple of posts we touched on installing Oracle XE to avail a small footprint Oracle DB Server.

Later we used SQLPlus to validate connectivity and add a new ‘regular‘ user.

 

Lineage

  1. “Oracle Database 11g Express Edition” – Installation on Windows
    Link
  2. Oracle – SQLPlus – Create new User
    Link

Download Area

The current version of the Oracle Database Engine Client is Oracle Database 11g Release 2 (11.2.0.1.0).

It is available for download here.

Which App

We will be using the Oracle Client DB Connectivity layer on MS Windows x64 bit.

Unfortunately, the client tool is Microsoft’s Business Intelligence Development and that client is 32 bit, we will have to take the Oracle 32-bit Client.

Here it is….

Size wise It is 700 MB.

Install

Select Installation Type

Image

Details

  1. InstantClient ( 174 MB )

 

Specify Installation Location ( Step 2 )

Image

Before

After

Details

  1. Changed from user specific folder to generic folder

Perform Prerequisite Checks ( Step 3 )

Image

Before

After

Details

  1. Path
    • Path’s Length
      • Value
        • Expected Value :- 1023
        • Actual Value :- 1239
      • Oracle will like for the path length to be less than 1024
      • Chose to Ignore

 

Summary ( Step 4 )

Image

Details

  1. Global Settings
    • Disk Space :-  174 MB
    • Install Type :- Instant Client
    • Oracle Home Location :- D:\app\oracle\product\11.2.0\client_1
  2. Inventory Information
    • Inventory Location :-  C:\Program Files (x86)\Oracle\Inventory

 

Install Product ( Step 5 )

Image

Validation

tnsping

Let us tnsping to see if we can connect to our locally installed Oracle XE ( Oracle Express Engine ).

Command


tnsping XE

Output

tnsnames.ora

Please review and modify tnsnames.ora to add new & modify existing alias.

Commendation

Please review Tyler Chessman very generous write-up for a good, solid, write-up on connectivity installation & configuration between SQL Server and Oracle.

 

References

  1. Tyler Chessman
    • Connecting to an Oracle Database from SQL Server and Microsoft BI Tools
      Avoid common pitfalls
      Published On :- 2014-May-14th
      Link

Oracle – SQLPlus – Create new User

 

Background

Now that we have Oracle DB Express installed, let us create a new normal user.

The reason being that there are some hoops you have to jump over if you try to use the sys or sysdba system accounts.

 

SQLPLus

There is a minimalist Client bundled with Oracle XE, it is called SQLPlus.

Launch

To launch on Windows access menu and underneath “Oracle Database 11g Express Edition” \ “Run SQL Command Line“.

 

Use

Connect as SYS

SQL


SQL> connect SYS as SYSDBA

Output

Output – Connecting …

Output – Connected

 

Create User

SQL


create user msftbids identified by mylittpwd;
grant connect to msftbids;

Connect User

Please launch another SQLPlus session and attempt to connect as the user you just created.

SQL


connect msftbids

Output

“Oracle Database 11g Express Edition” – Installation on Windows

Background

A week ago today, DH called me to speak about an interoperability problem he was facing accessing data on an Oracle DB from SQL Server.

The tool he was using is Microsoft’s Business Intelligence Development ( BIDs)

We tried to debug the problem over phone and email.

Did not get far and so I ended up connecting remotely to his machine and found a workaround using Microsoft Linked Server.

The problem was vexing enough and I wanted to reproduce locally in our Lab environment.

And, so here we go downloading and installing a Light version of the Oracle DB Engine, Oracle Express.

Download

Overview

To download please visit Oracle’s Express Edition Overview page here.

The current version is Express Edition 11g Release 2.

Download

Image

Textual

  1. Oracle Database Express Edition 11g Release 2 for Windows x64
  2. Oracle Database Express Edition 11g Release 2 for Windows x32
  3. Oracle Database Express Edition 11g Release 2 for Linux x64

 

Installation

Image

Welcome

License Agreement

Choose Destination Location

Original

Revised

Explanation
  1. Changed destination folder from C:\Oraclexe to D:\Oraclexe
  2. Space Required to 631124 K ( 630 MB)

 

Specify Database Passwords

Original

Revised

Explanation
  1. Enter password for the System Account
    • Btw, the system accounts are
      • SYS
      • SYSTEM

Summary

Image

Explanation
  1. Oracle Database Listener :- 1521
  2. Oracle Services for Microsoft Transaction Server :- 2030
  3. Oracle HTTP Listener :- 8080

Complete

Image

Sql Server – DataTypes – datetime2 & Legacy Apps

Background

Customer raised a P-1.

Ticket Contents

VB retirement calc tool application is experiencing very weird behavior.
This SQL query “SELECT abflag_updatedate FROM abtbl WHERE tablename = ‘plan’ returns a datetime string and will be formatted by this VB code :
getUpdateDate = Format(rstBase.Fields.Item(“abflag_updatedate”).Value, C_DateFormat1) //C_DateFormat1=”mm-dd-yyyy”

Starting this week errors were reported by tester. And by tracing into the code, I noticed that the Format() method call does not do the formatting as expected. The string value getting from the query of a datetime is like “2017-09-20 22:44:26.890” which is ignored by the Format function.

Production application is working, we verified that.

I suspect that the SQL query in VB code / Recordset was getting different format of string in the past and in production at of now so the Format function was working. Please help investigate this issue, to see if there was SQL server configuration change that made the impact.

I am concerned that this issue will impact a lot of places in the VB applications as I see this type of code everywhere. And we should be aware of the root cause so it will NOT apply to Prod.

TroubleShooting

Did not have Visual Basic, but developer gave enough details for a clean room implementation.

Snippet


Dim C_DateFormat1 as string

C_DateFormat1="mm-dd-yyyy"

Do While NOT Recordset.Eof   

 dtABFlag = Format(rstBase.Fields.Item("abflag_updatedate").Value, C_DateFormat1)  
	
 Recordset.MoveNext     

Loop

DB Changes

Made a slight structural change to db, created a new column, abflag_updatedateAsDatetime.

Notice original column’s datatype is datetime2(3).

New column’s datatype is datetime.

If it were MTV’s Real World, would have gone with a computed column.

 

Clean Room Implementation


DB_CONNECTION_STRING = "Provider=sqloledb;Data Source=DBSERVER;Initial Catalog=rbp;Integrated Security=SSPI;"

'declare the SQL statement that will query the database

rem SQL = "SELECT abflag_updatedate FROM dbo.abtbl WHERE tablename = 'plan'"
SQL = "SELECT abflag_updatedate, abflag_updatedateConverted = convert(datetime,  abflag_updatedate ) FROM dbo.abtbl WHERE tablename = 'plan' "

 

'create an instance of the ADO connection and recordset objects

' 

Set Connection = CreateObject("ADODB.Connection")

Set Recordset = CreateObject("ADODB.Recordset")

 

'open the connection to the database

wscript.echo DB_CONNECTION_STRING

Connection.Open DB_CONNECTION_STRING 

 

'Open the recordset object executing the SQL statement and return records 

Recordset.Open SQL,Connection

 

'first of all determine whether there are any records 

If Recordset.EOF Then 

	wscript.echo "There are no records to retrieve; Check that you have the correct job number."

Else 

'if there are records then loop through the fields 

Do While NOT Recordset.Eof   

 
  field = Recordset("abflag_updatedate")

  fieldFormatted = FormatDatetime(Recordset.Fields.Item("abflag_updatedate").Value, vbShortDate )

	
  if field <> "" then

    wscript.echo "field " & field
    wscript.echo "fieldFormatted " & fieldFormatted

 end if
	
 if (Err.Number <> 0) Then 	

   Wscript.echo String(CHAR_LENGTH, CHAR_SEP)
		
   wscript.echo "Err.Number :- " & Err.Number
   wscript.echo "Err.Description :- " & Err.Description
		
   Wscript.echo String(CHAR_LENGTH, CHAR_SEP)		
		
   Err.Clear
	
 end if	

   fieldConverted = Recordset("abflag_updatedate")

   rem fieldFormatted = Format(Recordset.Fields.Item("abflag_updatedate").Value, C_DateFormat1)

   fieldConvertedFormatted = FormatDatetime(Recordset.Fields.Item("abflag_updatedateConverted").Value, vbShortDate )

	
  if (Err.Number <> 0) Then
	
	Wscript.echo String(CHAR_LENGTH, CHAR_SEP)	
	wscript.echo "Err.Number :- " & Err.Number
	wscript.echo "Err.Description :- " & Err.Description
		
	Wscript.echo String(CHAR_LENGTH, CHAR_SEP)				


  end if

  if fieldConverted <> "" then

    wscript.echo "fieldConverted " & fieldConverted
    wscript.echo "fieldConvertedFormatted " & fieldConvertedFormatted

  end if
	
  Recordset.MoveNext     

Loop

End If

Findings

  1. Format Versus FormatDatetime
    • VBScript does not support the Format Function, and so please use FormatDateTime
  2. Function – FormatDatetime
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails with error
          • Error Number ( Err.Number ) :- 13
          • Error Number ( Err.Description ) :- Type mismatch
  3. Function – Format
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails silently
        • Returns passed in datetime without formatting as requested
  4. Function – CDate
    • Database Data Type
      • datetime
        • Works
      • datetime2
        • Fails

Source Code Control

GitHub

DanielAdeniji/SQLServerDatatypeDatetime2LegacyApp

Link

 

Dedicated

There is always a first time.

First post under the Visual Basic Category.

 

Moral of the Story

Do not use new data types and if you have to, use views, stored procedures, tracking ( computed )  columns to shield from frontend.

Bishop Dale C. Bronner

Introduction

Bishop Dale Bronner pastors the Word of Faith Family Worship Cathedral in Austell, GA.

Mission

“Reaching the Lost and Teaching the Found.” Our prayer is that God will meet you at your point of need and that “You won’t leave here like you came in Jesus Name.”

 

Videos

    1. Dangers of Distractions
      • Bishop Dale Bronner | The Dangers of Distractions (Relationships)
        • FaceBook
          • Bishop Dale Bronner | The Dangers of Distractions (Relationships)
            Link
        • YouTube
          • The Danger of Distractions
            • The Danger of Distractions
              Sunday, February 16, 2014 – 11am
              Channel :- woffamily
              Link
    2. Transitions of Life
      • YouTube
        • The Transitions Of Life – March 23, 2014
          Channel :- BartPiercelive
          Location :- Rock City Church
          Date :- March 23rd, 2014
          Link
    3. Heart Revolution Conference 2015
      • Cornerstone Church of San Diego
        • YouTube
          • Heart Revolution Conference 2015
            Published On :- 2015-July-30th
            Link