Different Drivers for SQL Sever

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
nvalia
Premium Member
Premium Member
Posts: 180
Joined: Thu May 26, 2005 6:44 am

Different Drivers for SQL Sever

Post by nvalia »

Hi,

We are on Information Server 9.1 on Windows, using Server Jobs
and the DB is on SQL Server 2012 server

I see different drivers to connect to SQL Server like

IBM Native SQL Server Wire Protocol 7.10.03.128
IBM SQL Server Wire Protocol 7.10.02.19
SQL Sever 6.01.7601.17514

When we migrated from V7.5 to V9.1, we are seeing a 50-60% performance degradation for exactly the same jobs. All compiled successfully and are running fine without any failures but a lot slower

I understand we should expect ~ 10% slow down due to the way Authentication works in 9.1 v 7.5

Our testing indicates the SQL read/write is where the major performance loss is and on contacting IBM they recommended we use ODBC Connector instead of the ODBC Stage. This change did not help much

We have been using the SQL Server Native driver, is using the IBM provided drivers recommended? We will try that as well, but if any one has already used these or any other direction/pointers will be helpful

Thanks,
NV
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

IBM will always recommend using IBM drivers. By all means try the Microsoft driver - it may perform better. Or it may not.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

I have seen this performance degrade when my client moved from 7.51A ( Solaris) to 8.7.01 on AIX
It also created a bunch of metadata issues ( Importing table defs etc )
We switched the ODBC SQL Server driver library to this one

[NGDCAGLAPassport]
Driver=/datastage/IBM/InformationServer/Server/branded_odbc/lib/VMsqls25.so
Description=DataDirect SQL Server Native Wire Protocol

Since you are 9.1 , you probably don't seem to even have the Datadirect drivers , so you may have to go to this driver
IBM Native SQL Server Wire Protocol 7.10.03.128

I also see you are on Windows ,and doing Server jobs, Can you try using the MS-OLEDB Stages also ? I have used them earlier and they worked well for SQL Server. My 9.12 environment is on AIX , so the OLEDB Stages are not provided for Unix environments ( They would be useless) .
Post Reply