Use DRS stage to do bulk insert to MSSQL

[aluthra48]

I have a job that inserts rows into a table in a sql server database. I set up a DRS stage with "Truncate table then insert rows" for the "update action". I set the array size to 15000 and the transaction size to 50000.
I found out from the sql server DBA that it was inserting one row at a time. It took 55 minutes to load 7 million rows. The suggestion was to do a bulk insert to speed up the load.
When I selected "Bulk insert" from the drop down for "update action", I got the following fatal error:

Stage supports bulk load to sql server only on windows

Does this mean that a patch is required, or there is something wrong with my settings?

We or on a solaris platofrm.

Thanks

[chulett]

No, just like it says it means you can only use that option from DataStage installed on a Windows server, not a UNIX one... it being a Microsoft product and all.

Your only other option is to ftp the file to the database server and then remote execute a scripted bulk load on that server. From the command line.

[aluthra48]

Thank you.

[Ultramundane]

This will soon be generally available. IBM let me test the new DD6R2 ODBC drivers and a patched ODBC connector. Let's just saying. Pretty amazing results. A load that was running at 4000 rows/second and using 100% cpu time on one our DS server CPUs has gone to 17000 rows/second and only using 50% cpu time on the DS server CPU.

Because of the reduced CPU, we added a new column "tinyint" column to the SQL server table and we partitioned on 4 values. 0, 1, 2, 3, 4.

We redesigned the job to use a transformer to send 0 to Connector, 1 to a Connector. You get the idea.

75,000 rows/second.

Good stuff. IBM is going to release this shortly and provide a technote on the specifics.

Thanks,
Ryan