DSXchange

All,
We have a sequence running 6 set of jobs, its a daily run , when the sequence is triggered first 4 jobs runs without any problem and the last two jobs having issue in connecting to the database getting below error.

scRetrieveAuditFields.ETL_JOBID_LIST: The connector could not establish a connection to data source IFP1 using user ETLJOB.

All the Jobs are connecting to the same Oracle database and only the last two jobs having this issue. Resubmitting the job for multiples times goes through.

Another fatal error is as

scRetrieveAuditFields.ETL_JOBID_LIST: ODBC function "SQLConnect" reported: SQLSTATE = HYT00: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver]Timeout expired.
ODBC function "SQLConnect" reported: SQLSTATE = 08001: Native Error Code = -6: Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver][Oracle]Network Operation Timed Out. (CC_OdbcConnection::connect, file CC_OdbcConnection.cpp, line 578)

Sufficient sessions available to connect the database as per the DBAs.

All jobs are using the same code

Please help

Seems you are trying to connect oracle database using ODBC, any reasons for not using oracle enterprise or connector?

For your error, As the description suggests, Its timing out. Did you try to get the round trip time and route from datastage box to database?

Issue resolved after using the Oracle Driver in ODBC entry. Looks strange and wierd but in the end workaround helped