[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
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
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
Choose a job you love, and you will never have to work a day in your life. - Confucius
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!
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!
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
-
- Participant
- Posts: 32
- Joined: Tue Dec 13, 2011 7:27 am
Hi Williams,kwwilliams wrote: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
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
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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
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
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 32
- Joined: Tue Dec 13, 2011 7:27 am
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
no, nothing different from the client as long as they are straight inserts.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 32
- Joined: Tue Dec 13, 2011 7:27 am