Page 1 of 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
Posted: Tue Apr 17, 2012 12:41 am
by 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
Re: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionW
Posted: Tue Apr 17, 2012 10:45 pm
by 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.
Posted: Thu Apr 19, 2012 6:58 pm
by 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..
Posted: Thu Apr 19, 2012 7:00 pm
by SURA
Mine is same.
Posted: Thu Apr 19, 2012 7:09 pm
by 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.
Posted: Thu Apr 19, 2012 7:41 pm
by 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
Posted: Thu Apr 19, 2012 8:06 pm
by 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.
Posted: Sat Apr 21, 2012 12:56 am
by 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
Posted: Mon Apr 23, 2012 5:13 pm
by 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.
Posted: Mon Apr 23, 2012 11:17 pm
by ray.wurlod
SURA wrote:I am not sure why IBM is not providing the native connectivity for SQL Server.
They can't provide it on all platforms.
Posted: Tue Apr 24, 2012 12:57 am
by SURA
Yes Ray you are right. SERVER have OLEDB whereas PX dosen't and i can't understand why? is the reason i said.
Posted: Tue Apr 24, 2012 6:00 am
by chulett
Why not ask IBM? Not sure how anyone here would know...
Posted: Tue Apr 24, 2012 5:32 pm
by 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
Posted: Tue Apr 24, 2012 10:55 pm
by SURA
chulett wrote:Why not ask IBM?
I already asked IBM and i posted here; Just i wish to share my thoughts.
Posted: Tue Apr 24, 2012 10:59 pm
by SURA
Hi Eric
Thanks for sharing this info. Let me check it and post the details.