DS Job tuning for SQL server ODBC

scbruce · Post by **scbruce** » Mon May 02, 2005 10:45 pm

I'm quite new to DS and I'm running a DS job that uses SQL server ODBC stages for the source and target tables.

The job is running for a verrry long time for a relatively small number of updates to a target table.

The DBA monitored the job and identified certain queries that were potential problems but they are not meaningfully identified. eg

exec sp_execute 1, 709968

exec sp_execute 3, 321, 'Apr 18 2005 10:10:11:000PM', 709968

I don't know how to relate these to the actual ds queries.

Please help

Thanks
Scott

chulett · Post by **chulett** » Tue May 03, 2005 12:23 am

Those don't sound like 'ds queries', those wouldn't look any different from 'regular' queries run in the database. That looks more like someone is executing 'stored procedures' or whatever the equivalent term would be for SQL Server.

Check the actual ODBC stages, both source and target and look in the 'Before Sql' tab. Is anything being executed there?

palmeal · Post by **palmeal** » Tue May 03, 2005 2:12 am

I've just binned the idea of using the ODBC stage for MSSQL Server as it is painfully slow. I was getting an insert rate of 7 rows per second which for thousands of rows is not a non-starter. Unfortunately there is no bcp or import stage within DataStage for MSSQL Server and breaking out to unix is not an option as MSSQL's bcp won't run on UNIX. There are a couple of related posts regarding MSSQL and ODBC performance.

What I am doing to get around this is to use the FTP stage to fire files across to the MSSQL Server and have a process running there that will use bcp to fire them into a staging table and then a stored procedure to play the records into the database. Although this takes a bit of control away from DataStage it is the only way that I can make this tool work for MSSQL databases.

This product seriously needs a bcp stage for MSSQL server especially before Microsoft bring out their ETL tool with Yukon !!.

Not sure if this will help you in your situation but just sharing my experiences with DataStage and MSSQL Server.

marc_brown98 · Post by **marc_brown98** » Wed May 04, 2005 2:46 pm

You can use the bulk insert stage in DS at least 6.x. It is called sqlblk7_0. Works fine with SQL 2000. Also, that seems painfully slow to only get 7 rows per sec. on insert via ODBC, you might want to check a few things with the DBA. I usually average around 900-1200 and have spent very little time tuning.

shawn_ramsey · Post by **shawn_ramsey** » Wed May 04, 2005 2:57 pm

palmeal wrote: This product seriously needs a bcp stage for MSSQL server especially before Microsoft bring out their ETL tool with Yukon !!.

There is a BCP stage that is supposed to work with SQL Server, it is just hidden under the Sybase stage collection. I have never used this stage since Microsoft states that BCP is only for backward compatibility and that SQL Bulk should be used.

palmeal · Post by **palmeal** » Thu May 05, 2005 1:42 am

Thanks for the feedback guys but ODBC in my case is just too slow. The database set is optimised - my main skill is Performance & Tuning with Sybase/SQL Server so hopefully I know what I'm doing when these things get set up

.

The main problem in my case is that the DataStage Server is based in the States whilst all of my Sybase/SQL Servers are UK based so there is a lot of data going back and forward. Couple that with ODBC which isn't exactly the best software for transferring data - date fields have to go as strings so there's conversion involved at the source and target stages.

I don't think that I would trust the Sybase bulk copy for SQL Server 2000 as if you look at the command line versions then the commands are somewhat different. As SQL Server and Sybase continue to develop independently then I think it is unwise to use this. I think the remote call and the use of Bulk Insert is the best supported way of doing this.