ODBC Timeout When SP runs more than 30 minutes

oacvb · Post by **oacvb** » Thu Mar 08, 2007 7:25 pm

Hi,

I am calling an Oracle Stored Procedure from ODBC stage, i am getting the following error if SP runs more than 30 minutes.

ExtractPSFTServiceSeq..Transformer_Input_T1: ORA-03113: end-of-file on communication channel

Attempting to Cleanup after ABORT raised in stage ExtractPSFTServiceSeq..Transformer_Input_T1

ExtractPSFTServiceSeq..Transformer_Input_T1: ORA-03114: not connected to ORACLE

I called the same store procedure through unix script and working fine if SP runs more than 30 minutes. As well i called the Script through a sequencer and it works fine.

I checked the ODBC Config & ini files and didn't find anything wrong. The property LockTimeOut is set to -1 in the ini file. Please find the config entry for same from ini file.

QEWSD=37957
Driver=/datastage/Ascential/DataStage/branded_odbc/lib/VMora19.sl
Description=DataDirect Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=oracledb
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=scott
Password=tiger
PortNumber=2521
ProcedureRetResults=0
SID=oracledbsid
UseCurrentSchema=1

Please help me in resolving the issue

oacvb · Post by **oacvb** » Fri Mar 09, 2007 12:18 am

Please let me know the solution if someone knows.

chulett · Post by **chulett** » Fri Mar 09, 2007 12:52 am

They will. Me, I don't use ODBC so can't really help.

Or give Support a call and open a case.

kumar_s · Post by **kumar_s** » Fri Mar 09, 2007 4:30 am

Even in one of the other project in my site reporting the same issue. Need to check out the reason.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 09, 2007 7:50 am

An exact match Search on -03113 got 13 hits. Perhaps one of these will help?

kumar_s · Post by **kumar_s** » Fri Mar 09, 2007 7:54 am

Few were towards some setting in database end, and few were towards network traffic. They would have probably tried those option discussed here. As mentioned, if the stored procedure is called via shell, it can running for a long hrs, and not via ODBC stage.
Kinda weired.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 09, 2007 7:59 am

No evidence that they'd searched at all, so I had to point it out.

kumar_s · Post by **kumar_s** » Fri Mar 09, 2007 8:02 am

Its nothing wrong in searching again though.

oacvb · Post by **oacvb** » Sun Mar 11, 2007 5:20 pm

I searched for the key but i didn't get any result.

oacvb · Post by **oacvb** » Sun Mar 11, 2007 5:21 pm

I did search on -03113 , result was my message nothing else.

chulett · Post by **chulett** » Sun Mar 11, 2007 6:17 pm

As noted, do an Exact Match search in All forums - you'll get almost a dozen other threads that mention your error.

kumar_s · Post by **kumar_s** » Sun Mar 11, 2007 6:40 pm

Specifying '-' before search key word will function unexpectedly. As per Goolge standards, if you mention something preceeded by '-', it will produce the search result apart from the given key word.

oacvb · Post by **oacvb** » Sun Mar 11, 2007 9:24 pm

I searched it but couldn't find what is required. Anyways thanks for your responses.

ashik_punar · Post by **ashik_punar** » Sun Mar 11, 2007 9:36 pm

Hi All,

In my view this problem is occuring because the connection you are having with Oracle is getting timed out due inactivity. So, in order to get rid of this problem you have to get in touch with your DBA and increase the time-out time for your user name.

I was facing a problem like this with Sybase DB and i was using ODBC stage for accessing the DB.The above mentioned solution helped me in solving the issue.I hope it will be helpful to you also.

Correct me if i am wrong some where.

Thanks & Regards,
Punar Deep Singh

oacvb · Post by **oacvb** » Sun Mar 11, 2007 9:44 pm

Thanks Punardeep, but here issue is different. I am able to execute same SP which runs for more than 30 minutes through Shell Script or from a Job Sequnce by Calling the scripts which calls SP but not directly from ODBC.