DSXchange

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

IBM will always recommend using IBM drivers. By all means try the Microsoft driver - it may perform better. Or it may not.

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) .