DB2 UDB API Stage

opdas · Post by **opdas** » Thu Mar 30, 2006 2:24 am

Hi,
When using DB2 UDB API stage gives the following error and aborts :

"DB2_UDB_API_0,0: Fatal Error: Fatal: SQLFetch: Error retrieving results from server. "

Only succeeds when try several time

ray.wurlod · Post by **ray.wurlod** » Thu Mar 30, 2006 2:33 am

How long between the stage starting and the error message appearing? How busy is DB2 doing other things? How busy is the DataStage machine?

opdas · Post by **opdas** » Thu Mar 30, 2006 3:25 am

ray.wurlod wrote:How long between the stage starting and the error message appearing? How busy is DB2 doing other things? How busy is the DataStage machine? ...

Job started at: 3/30/2006 1:49:42 PM
Error Received at: 3/30/2006 1:50:45 PM

How busy is DB2 doing other things? Not much
How busy is the DataStage machine? Not much

kumar_s · Post by **kumar_s** » Thu Mar 30, 2006 3:51 am

Try running this job alone in the server.
What is the operation performed, it is select operation?
If so do the same from the command prompt, measure the time taken for that.
How complex is the query?
Where is the database situated?

opdas · Post by **opdas** » Thu Mar 30, 2006 4:12 am

Hi Kumar,
Its a simple select query and there is no problem when trying to run from command promt even doing a "view data " from the stage is also no problem.

kumar_s wrote:Try running this job alone in the server.
What is the operation performed, it is select operation?
If so do the same from the command prompt, measure the time taken for that.
How complex is the query?
Where is the database situated?

kumar_s · Post by **kumar_s** » Thu Mar 30, 2006 4:20 am

Is it a remote database?
Try to write into a Sequential file directly.
Was there any other user accessing the same table. There can be the possibility of lock.

opdas · Post by **opdas** » Thu Mar 30, 2006 4:28 am

Yes it is a remote database, and there is no chances that somebody else is using that.I would like to add that my fellow developers are also facing the same problem.

kumar_s wrote:Is it a remote database?
Try to write into a Sequential file directly.
Was there any other user accessing the same table. There can be the possibility of lock.

kumar_s · Post by **kumar_s** » Thu Mar 30, 2006 5:15 am

Let me reconfirm.
Is it view data succedes immediatly everytime, where as job gets aborted often. Am I right.

opdas · Post by **opdas** » Thu Mar 30, 2006 6:49 am

yes
You are right !

kumar_s wrote:Let me reconfirm.
Is it view data succedes immediatly everytime, where as job gets aborted often. Am I right.

kumar_s · Post by **kumar_s** » Thu Mar 30, 2006 7:23 am

So have you tried to loading the table directly into a sequential file?
What is the structure of the job?

opdas · Post by **opdas** » Thu Mar 30, 2006 7:29 am

Structure of the job is like:

DB2 UDB API---->Transformer-------->Dataset

But my colegues are using different structure and have DB2 UDB API in it and they are also facing the same problem

kumar_s wrote:So have you tried to loading the table directly into a sequential file?
What is the structure of the job?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 30, 2006 1:41 pm

Is there any additional error message (specifically a DB2 error code) in the job log? If you reset the aborted job, is there an event logged "from previous run..." containing diagnostic information?

opdas · Post by **opdas** » Thu Apr 06, 2006 2:23 am

Hi All,
Thought of posting the way the problem was solved.
Just cleared the &PH& file in the project and its working fine now.

Thanks all of you for your time.

Om

kumar_s · Post by **kumar_s** » Thu Apr 06, 2006 2:37 am

Interesting!!
I could only see some internal warning messages stored in that directory. I am not sure how that help to resolve.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 06, 2006 3:20 pm

A very full &PH& directory can extend the startup time to such an extent that some timeouts begin to come into the picture. The dreaded code -14 (failed to start within one minute) can also occur under this circumstance. Keep your &PH& directories cleaned of old entries.