Error with SQL STATE 40003

dxk9 · Post by **dxk9** » Fri Apr 27, 2012 12:33 am

Hi,
When I run the server job 1st time, I get the following error:

Communication link failure. SQLSTATE=40003
SQLSetStmtAttr: Failed to set row array size statement attribute for prefetch.
[IBM][CLI Driver] CLI0106E Connection is closed. SQLSTATE=08003
SQLAllocHandle: Failed to allocate environment handle 'STMT'.

But when I run it the second time, it runs successfully.

Job Structure:
From DB2 -> Transformer -> Output sequential file

Rejects from transformer are loaded to Reject sequential file

Thanks,
dxk9

qt_ky · Post by **qt_ky** » Fri Apr 27, 2012 8:19 am

CLI0106E link (FYI):

Application returning error CLI0106E

dxk9 · Post by **dxk9** » Thu May 03, 2012 5:00 am

But any clue of why the job is running successfully the second time ? And whats the actual cause of this error.

chulett · Post by **chulett** » Thu May 03, 2012 6:28 am

No, not really. Seems like something you should be taking to your official support provider.

qt_ky · Post by **qt_ky** » Thu May 03, 2012 5:15 pm

Make sure no DBA is killing sessions.

Does it abort with that error the first time, every time/every day?

How much delay is there between starting the job and the abort?

Share the above info with support. Share what you find out.

dxk9 · Post by **dxk9** » Fri May 04, 2012 6:00 am

No DBA is killing the session.

The job is aborting the 1st time due to the error & on manual start again, its running fine.

The time gap between the start & the abort is approx 30-35min. Sometimes its even an hour. Not sure what is happening in that time frame as we don't have access to monitor the DB.

Please help.

Thanks,
dxk9

chulett · Post by **chulett** » Fri May 04, 2012 6:23 am

Help yourself - involve support. People here aren't going to be able to dig into your problem and help figure it out like they would be able to do.

However, if this "first time" happens every day such that you need to re-run it every day and this is a scheduled run that happens at the exact same time every day, I would suggest an experiment. Change the start time of the job, delay it your 30 to 60 minutes. See if that changes the behaviour.

qt_ky · Post by **qt_ky** » Fri May 04, 2012 6:27 pm

According to the link I posted above, "the connection to the database from the application has been lost and new connection should be established." Whatever is causing that is local to your environment. You should have local support like a DBA who can monitor it while it runs and see what's happening. If you're saying nobody where you work can have access to monitor, well guess what... nobody on this forum does either. You could also check with DataStage support to see if it's a known issue fixed by a patch. In your case you would be lucky if it is.