How to perform bulk load(insert) on seq server database

prasson_ibm · Post by **prasson_ibm** » Wed Sep 11, 2013 7:27 am

Hi,

I want to perform bulk load on sql server databse but i am using ODBC connection to sql server.
I tried to do bulk insert using DRS connector stage but it seems that it is inserting throgh normal load.I got below entry in log.

Code: Select all

Generated INSERT statement: INSERT INTO AIT_Staging.dbo.SRE_CORRECTED_SALES(column list) 
VALUES (ORCHESTRATE column list)

I read the document and it says:-

The DRS Connector stage does not support bulk load operation to Microsoft SQL Server databases. Note however that the Microsoft SQL Server ODBC wire driver included with InfoSphere Information Server supports bulk load mode for writing data to the database. Therefore DRS Connector can be used to perform bulk load to Microsoft SQL Server by following these steps:

1.Configure DRS Connector stage to use ODBC database type.
2.Define ODBC data source name (DSN) based on the Microsoft SQL Server wire driver.
3.Customize the DSN definition to enable the bulk load mode of operation.

My question is what parameters reqired to perform bulk load in ODBC DSN.

prasson_ibm · Post by **prasson_ibm** » Wed Sep 11, 2013 7:30 am

My ODBC connection details are below:-

Code: Select all

[MSSQL_SRE]
QEWSD=2456224
Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMsqls25.so
Description=DataDirect SQL Server Native Wire Protocol
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationName=Caissa
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadOptions=2
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=AIT_Staging
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EncryptionMethod=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=C02SQLQA04
HostNameInCertificate=
InitializationString=
Language=English
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
PacketSize=-1
Password=
Pooling=0
PortNumber=1565
QueryTimeout=0
ReportCodePageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
WorkStationID=
XML Describe Type=-10

chulett · Post by **chulett** » Wed Sep 11, 2013 7:41 am

As a crazy guess, any that mention "Bulk"... especially this one:

EnableBulkLoad=0

Set it to 1. The rest should be fine for the initial attempts.

prasson_ibm · Post by **prasson_ibm** » Wed Sep 11, 2013 8:15 am

Hi,
I made changes in DSN

EnableBulkLoad=1

Rest are having default value:-

BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadOptions=2

I reran the job but still i cant see any change

.Again in log i can check is job is generating insert statement.

chulett · Post by **chulett** » Wed Sep 11, 2013 8:21 am

That's all I got. Don't do SQL Server.

I was wondering what the various options controlled and found this if you're curious. Looks like it may still generate inserts but do bulk loads 'under the covers'. In your shoes I'd enlist the aid of your DBA or official support provider to confirm.

prasson_ibm · Post by **prasson_ibm** » Wed Sep 11, 2013 8:57 am

Hi Chulett,

This is interesting.I am going to talk to DBA fist to check if it is using bulk load.

Thanks a lot for this information.

ArndW · Post by **ArndW** » Wed Sep 11, 2013 10:58 am

While our server is on Windows, we use the IBM wire driver and the bulk load option setting in that, and it does indeed perform a very fast SQL Server bulk load when doing inserts.

SURA · Post by **SURA** » Wed Sep 11, 2013 5:02 pm

Mid of last year (2012) we did the test about how Datastage is pushing the data into SQL Server and what type of method is it using, how SQL Server is handling it?

Our SQL Server DBA said, for some reason Datastage is not using the latest load method (Sorry i am trying to remember what was the word he used) and using the very old method of loading. He too added, now a days SQL Server made lot of changes in their load approach as Datastage is not calling that method to load!

what i can remember is, when the load it triggered it is creating the SP and loading the data.

We tested with

Datastage 8.5 using SQL Server EE stage and the load method was Append
OS Windows 2008 R2
DB SQL Server 2008

In relates with Driver

We test with Data Direct Driver , IBM SQL Server Wire Protocol driver , SQL Server native and SQL Server ODBC Driver 06.01.7600

It is worth to sit with DBA, trigger the job and trace is in the SQL Server. You can know how the data is being handled by SQL Server.

adityavinay · Post by **adityavinay** » Mon Sep 16, 2013 2:34 pm

Hi,
Here are few steps i followed to use bulk load in SQL server. Results looked awesome.

Datastage:
Use DRS connector stage and Write Mode = "insert rows".
Array size 50000
Record Count - 200000

Odbc Drivers Definition: Enable Bulk load

Driver=/...../IBM/InformationServer/Server/branded_odbc/lib/VMsqls24.so
Description=DataDirect 6.0 SQL Server Native Wire Protocol
Database=
PortNumber=1433
LogonID=<ID>
HostName=
WorkStationID=
Language=
ApplicationName=
PacketSize=0
XMLDescribeType=-10
LoginTimeout=15
QueryTimeout=0
AnsiNPW=1
ApplicationUsingThreads=1
EnableQuotedIdentifiers=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
InitializationString=
AuthenticationMethod=1
GSSClient=native
EncryptionMethod=0
TrustStore=
TrustStorePassword=
HostNameInCertificate=
ValidateServerCertificate=1
SnapshotSerializable=0
ReportCodepageConversionErrors=0
LoadBalancing=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
AlternateServers=
AlwaysReportTriggerResults=0
FailoverMode=0
FailoverGranularity=0
FailoverPreconnect=0
Pooling=0
ConnectionReset=0
MaxPoolSize=100
MinPoolSize=0
LoadBalanceTimeout=0
EnableBulkLoad=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadOptions=0

Thanks,
Aditya.

prasson_ibm · Post by **prasson_ibm** » Mon Sep 16, 2013 4:54 pm

Hi,
How can you make sure that it is using bulk load method? Which stage are you using to perform bulk insert?

adityavinay · Post by **adityavinay** » Mon Sep 16, 2013 5:09 pm

You are enabling bulk mode in odbc driver definition

EnableBulkLoad=1

This will enable bulk load utilities....
While using DRS Connector make sure you use ODBC drivers .

Basically it will enable bulk load but based on the record count and array size it will push blocks of data to database.

I tried it with write mode as bulk response is not as expected. But when I changed it to insert rows it worked . Inserted 100 million records in 40 mins into transactional system.

Thanks,
Aditya.

prasson_ibm · Post by **prasson_ibm** » Tue Sep 17, 2013 3:41 am

Hi,

I am working with DBA on this and will update you the result.