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

[SURA]

Hello All

I was struggling for fix an issue in SQL SERVER. My environment is Datastage 8.5 with SQL SERVER 2008.

Issue: Some of the job(s) keep on hanging / aborts with the error i mentioned in the subject. I was not able to find the reason for this and i raised a PMR and it is in progress. Meanwhile i found a workaround and i wish to share this! It may help others who is facing this issue.

If hangs: No useful information in the log

If fails:
Message Id: IIS-DSEE-MSSS-00001
Message: LND_MON_SITE_LANE_DATA,3: 01000 : [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
08S01 : [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

Workaround:

1. Made the SQL SERVER EE stage to run in Sequential. - This will run, but very poor performance.

2. Create a cluster index in that table - Running fine with good performance

I am working with IBM to find the solution. Until i get the solution / until i update this thread, if any one face the same issue, please try this option.

If it worked, good luck

Otherwise don't screw me

[SURA]

An update:
The pirticular table was having cluster index. I dropped the index and ran the job. It starts to working fine. Then i recreated the same index again, but no issue, job is working fine. So i am not sure why the job was keep on failing for more than two weeks!

Anyhow i already provided the log details to IBM. I will update once i get any answer.

[egsalayon]

Hi Sura,

I also encountered the "hanging" of job with sql server enterprise stage as a target.

What's your IS version? Mine is IS 8.5 fix pack 1..

[SURA]

Mine is same.

[SURA]

Please enable

1. Trace log in ODBC
2. Tack the log details in SQL Server
3. SELECT * FROM sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_CONNECTIVITY'

Some time this may help you.

[egsalayon]

Hi Sura,

Would you mind to discuss a bit more detail on these items? I only know how to execute the 3rd item...

Regards,
Eric

[SURA]

Export the data into a text file. Data will be in xml format, still you can open it in text file. Then search for

DisconnectDueToReadError OR NetworkErrorFoundInInputStream and if it have 1 that means you have problem in these areas.

To be honnest it is a DBA task and i am not DBA. But beased on the google search i found this and noticed.

[egsalayon]

Hi Sura,

Mine is now running ok!
But my scenario only hangs and and doesnt have any error, unlike yours which has the error "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite"

What driver did you use in the creation of your DSN? The resolution I found out was to use the driver provided by Microsoft, "SQL Server".



Previously, I use IBM provided driver for SQL Server.
I also tried using the driver "SQL Server Native Client" but also encountered error.

HTH,
Eric

[SURA]

We are using SQL Server 6.01.7600.16385 / SQ:SRVR32.DLL

We already tried native / IBM wire etc and none is working. I am not sure why IBM is not providing the native connectivity for SQL Server. In one of my email IBM replied, it is a limitation as of now.

If you use native, that will work with ODBC and not with SQL Server EE. Unfortunately ODBC doesn't have bulk load (Correct me if i am wrong).
ODBC is really it is making very slow.

For your question, yes we do have those hanging problem and it will happen in random and no permanent fix for that. I do raised one more question in this regards in the forum. I wish you should not face, but you will.

[ray.wurlod]

I am not sure why IBM is not providing the native connectivity for SQL Server.

They can't provide it on all platforms.

[SURA]

Yes Ray you are right. SERVER have OLEDB whereas PX dosen't and i can't understand why? is the reason i said.

[chulett]

Why not ask IBM? Not sure how anyone here would know...

[qt_ky]

DataDirect 6.1 ODBC drivers do offer native / bulk load for SQL Server. It appears that DataDirect 7.0 and 7.1 versions are also available. It looks like you're on Windows platform. I think if the Windows ODBC drivers say IBM, they're still made by DataDirect. Someone can correct me if wrong. Mabye you could check into using those.

Here is a template for the DSN from the .odbc.ini file. Bear in mind, this is from Unix; not Windows. Hopefully Windows has an equivalent.

Code: Select all

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

[SURA]

Why not ask IBM?

I already asked IBM and i posted here; Just i wish to share my thoughts.

[SURA]

Hi Eric

Thanks for sharing this info. Let me check it and post the details.