Problem in connecting with database

[Jayakannan]

Hi,

we are getting below error messages,

lkp_LastCntlNbr,0: SQLExecute reported: SQLSTATE = 40003: Native Error Code = -30081: Msg = [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.67.38.201". Communication function detecting the error: "recv". Protocol specific error code(s): "*", "*", "0". SQLSTATE=08001 (CC_DB2DBStatement::executeSelect, file CC_DB2DBStatement.cpp, line 1958)

lkp_LastCntlNbr,0: SQLEndTran(SQL_ROLLBACK) reported: SQLSTATE = 08003: Native Error Code = -99999: Msg = [IBM][CLI Driver] CLI0106E Connection is closed. SQLSTATE=08003 (CC_DB2Connection::rollback, file CC_DB2Connection.cpp, line 1545)

This Lookup stage (lkp_LastCntlNbr) performs Sparse Lookup with a DB2 table (abc). DB2 stages before this lookup stage are extracting data but after this lookup stage are producing the same kind of error messages.

Sparse lookup table query is as below,

select
cntl_num
from
abc
where
table_name='xyz'
UPDATE WITH RR

This table (abc) will be updated in the same job later.

The job is in Testing environment and it has been running successfully for a week before this issue occured.

Any suggestions?

[ray.wurlod]

it has been running successfully for a week before this issue occured

What has changed? ("Nothing" is not the correct answer.)

[Jayakannan]

Unfortunately Nothing is the answer because it is a Read-Only job in QA and no migrations happened during this one week for this job.

[ray.wurlod]

"Nothing" is not the right answer. The change might not be in DataStage.

[Jayakannan]

We modified the Sparse lookup to Normal lookup and the issue is resolved.

I got the below reason for failure,

When a job starts running, all the DB stages will try to estabilish connection to Database and if it doesn't happen within a specific time period (2 mins) the job aborts with the error message I posted earlier.

There is a DB2 extract before this Sparse Lookup and it was finishing within 2 mins during the initial succssful runs but the same extract was running for more than 2 mins later. Because of this Sparse lookup DB2 stage was not able to estabilish the connection on-time.

[ray.wurlod]

So the change was in the volume of data.