Improve loading into SQL server

[wahi80]

Hi,

I have a job which loads 4.5 mn records into an SQL server DB.
The jobs takes an hour to load the table.

Seq --> Transformer -->ODBC

The mode is Clear table and Insert records.

I'm looking at ways to reduce load time.
I have option of re-coding it to parallel, but not sure if it would help since it is SQL server.

[SURA]

1) You can try MS SQL Server stage in Server rather than ODBC and if i am right it is using the native driver.

2) I am not sure the need behind truncate then insert. Rather than you can use Truncate table then load (It will use BCP)

[wahi80]

I used ODBC connector and it saved some time around 5 minutes.
I'm looking for something in paralle which would save more time.
Is it possible?

[kwwilliams]

Have you tried enabling bulk load in the DSN?

[SURA]

I am not sure how much performance gain you can achive in PX in regards with SQL Server.

Why can't you try the Server job with the option as i mentioned in my comment.

Just give it a go for both my option as well as PX and tell us the difference.

[wahi80]

Hi,

There is no native SQL Server driver I can see in Datastage,hence I have been using ODBC.

Also in ODBC there is no option of Bulk lodaing only Clear tables and Insert rows.

[SURA]

1) You can try MS SQL Server stage in Server rather than ODBC and if i am right it is using the native driver.

Don't you have this?

[ray.wurlod]

Probably not on a UNIX-based server.

[SURA]

Thats new to me. Let me check.

[wahi80]

[Ray is right, I forgot to mention it is Unix machine, so no native drive for SQL Server

[priyadarshikunal]

You can use ODBC connector in PX with different number of nodes to get the best performance. truncate and load will not have much overhead however you might want to tweak transaction size/ commit interval.

Also, you should monitor the disk IO of both servers, network utilization and the setting on SQL Server database.

bottleneck can be anywhere in datastage, database, disk or network. Make sure you check all of them.

[PaulVL]

Give this a try:

If this is a pure incert, you could ensure that there are no duplicate entries, then convert the ODBC stage to parallel execution.

test in DEV, open the monitor in director and look to see if you did in fact open multiple connections, and what your rows per second are.

[kwwilliams]

Bulk load can be enabled in the DSN in all versions 8.1 or greater. Go to the .odbc.ini file and add this to the DSN

Code: Select all

EnableBulkLoad=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadOptions=0

Change the odbc driver from the classic driver:

Code: Select all

Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss24.so
Description=DataDirect SQL Server Wire Protocol driver

To the Native Driver:

Code: Select all

Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMsqls24.so
Description=DataDirect 6.0 SQL Server Native Wire Protocol

In version 8.7 the library would be VMmsss25.so and VMsqls25.so.

That will speed the load - however you will find that the server job itself is a bottleneck. If you need more speed, switch over to parallel

[spemma123]

I tried to do this in the DS V 8.5 on a Unix Platform but my job aborts. Does really work on UNIX. version of 8.5

[chulett]

Post your job design and the abort you are getting.