ODBC Stage Socket Error

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
neeraj
Participant
Posts: 107
Joined: Tue May 24, 2005 4:09 am

ODBC Stage Socket Error

Post by neeraj »

Hello,

I am using DataStage to read from Azure SQL Server and Writing into Azure Sql Server. During the process of reading and writing using ODBC connector Job is performing the CDC .

For the High Data volume jobs, if the job runs for more than 30 minutes, I get this error message

ODBC_Target,5: ODBC function "SQLEndTran(SQL_COMMIT)" reported: SQLSTATE = 08S01: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Wire Protocol driver]Socket closed.
ODBC function "SQLEndTran(SQL_COMMIT)" reported: SQLSTATE = 08S01: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Wire Protocol driver]SSL I/O Error.
ODBC function "SQLEndTran(SQL_COMMIT)" reported: SQLSTATE = HY000: Native Error Code = 5: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Wire Protocol driver]01006 (CC_OdbcConnection::commit, file CC_OdbcConnection.cpp, line 1,241)

I tried to work with IBM and Data Direct but could not find any solution.

Have anyone faced this issue and aware about the solution.

Regards
Neeraj
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

"Socket closed" suggests a problem with your network software. Involve your network administrator. Perhaps boost the "keep alive" setting.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Along those lines, it could be a keep alive or connection timeout setting in a firewall. We ran into that before with a different type of database connection.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Was thinking the same thing, especially if you meant "if the job runs for more than 30 minutes before it starts returning data". Now, if data is actively flowing through the job for those 30 minutes then something else is going on.
-craig

"You can never have too many knives" -- Logan Nine Fingers
neeraj
Participant
Posts: 107
Joined: Tue May 24, 2005 4:09 am

Post by neeraj »

Hello,

Thanks for the reply.

We have added keepAlive variable in odbc.ini file but still getting the problem and involved the network team, they are not saying any connection break during the process.

What change you performed to mitigate the issue?

Regards
Neeraj
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

In my situation I did not have to change any settings on Information Server. I had to request the firewall team to change the "extended session timeout" firewall settings. But like Craig said, that usually depends on idleness, so if your job was actively transferring data, then maybe a different setting is needed or it has a different cause. Any chance the database server has a hard limit on connection time?
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You still need to clarify exactly what's going on when it fails.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply