[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite

[kwwilliams]

Bulk load to SQL Server through the native wire driver dates back to 8.1. In reality IBM is not setting the standard for load options to non-client based database connections (ODBC), it is the ODBC driver that makes this determination. DataDirect 6.0 is capable of bulk loading to sql server using either the ODBC EE or ODBC Connector. The change is made to the DSN.

[qt_ky]

Again, I'm coming from a Unix background here.

The SQL Server Native Wire Protocol driver is there in the DataDirect 6.1 file that's bundled with 8.7 whereas it does not exist in the DD 6.0 file bundled with 8.5 (not sure why). I checked the DD web site and it's like you said listed as a new feature in DD 6.0.

Related to SQL Server...

New in DataDirect 6.0:
35% greater throughput performance (over release 5.3)
75% greater CPU efficiency (over release 5.3)
Bulk Load

New in DataDirect 6.1:
New SQL Server Wire Protocol driver (available as of 6.0 SP2)

New in DataDirect 7.0 (this one's interesting...):
Support for connecting to a SQL Server database instance using NTLM authentication when connecting with the Connect for ODBC SQL Server Wire Protocol driver from a UNIX or Linux platform. The current SQL Server Wire Protocol driver provides NTLM authentication support from Windows platforms

[SURA]

I am using DS 8.5 i Windows 208 R2 / 64 BIT and herewith the details.

DataDirect i have is
File Name: D:\IBM\InformationServer\\ODBCDrivers\VMSQLS24.DLL
Version: 06.00.0043 (B0133, U0056)

But none of my job is using this and rather than it is connected with SQL Server 6.01.7600.16385

I am not sure why DataDirect is not used for connections.

Now i am considering the performance and trying to use the DataDirect. I too asked IBM to know which version should i use?

I will update once i find some solution!

[adityavinay]

I just switched one of my client's loads from the classic driver to native wire utilizing bulk load (DataStage 8.5.2, datadirect 6.0). Job went from 1 hour 15 minutes to 10 minutes. The target box is severely undersized, so bulk probably helps here more than it would in most cases

Hi Williams,
How did the performance increased, we are using DD6.0 and trying to do bulk loads, but we are not acheving it. Could you please give some idea on how you worked it out? here are the odbc configs:

Driver=/IBM/InformationServer/Server/branded_odbc/lib/VMsqls24.so
Description=DataDirect 6.0 SQL Server Native Wire Protocol
Database=databasename
PortNumber=portnumber
LogonID=<ID>
HostName=servername,portnumber
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

[kwwilliams]

Just happened to look out here and see this question this morning. You can have a DSN entry as simple as this work.

Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMsqls24.so
Description=DataDirect 6.0 SQL Server Native Wire Protocol
Database=
LogonID=
Password=
Address=ipaddr, portno
QuotedId=No
QEWSD=39338
AnsiNPW=No
EnableBulkLoad=1
BulkBinaryThreshold=32

I don't see anything in your DSN that looks off. Are you doing straight inserts?
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadOptions=0

[adityavinay]

Yes Keith, Its straight Insert. We activated Bulk mode in DSN, but is there some thing to do in ODBC Connector/DRS Connector(bulk INsert).

[kwwilliams]

no, nothing different from the client as long as they are straight inserts.

[adityavinay]

I changed array size and record count. It worked.