Fetch problem in DB2

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

I am facing one issue with SQL Fetch. It is a online screen. Based on certain criteria entered in the CICS screen rows will be fetched from the DB2. But it is getting hanged. I did a Trace and i could see that the cursor is getting declared successfully and it is getting opened. But while fetching it is not coming out of the DB2 call. It just hangs.

In spufi, i tried the same query and it works fine.

Totally confused with what is happening. It will be great if any one could help me out whether it could be a issue with table side or in program.

GuyC · Posted: Fri Jan 07, 2011 4:41 pm Post subject:

Probably a performance/accesspath issue.
There is no way that you have tried "the same query" in spufi. Spufi doesn't allow host-variables.

Replacing host-variables with literals does have an impact on the accesspath chosen. So comparing a host-variable infested sql-statement with a sql statement with literals in spufi is useless when it comes to performance.

Kjeld · Posted: Fri Jan 07, 2011 4:41 pm Post subject:

You cannot test HOST SQL cursors in SPUFI. I assume you have tested the query SQL successfully, but how does your declare statement look like? Have you supplied host variables in the correct formats (PIC)?

Could you have a resource deadlock when you execute the CICS transaction. Check a DB2 monitor tool or the master log for possible messages.

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

The same program was working fine in test environment. But only in QA it is getting hanged.

Kjeld · Posted: Fri Jan 07, 2011 5:28 pm Post subject:

Check if your table resources used in the query are available in the environment.

Have you checked the DB2 master log or a DB2 monitoring tool?

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

I tried replacing host variables with literals in SPUFI. I have to check the DB2 Master log.

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

But i don't know how to check the DB2 Master log

could you pls let me know@kjeld

GuyC · Posted: Fri Jan 07, 2011 5:49 pm Post subject:

I would compare :
- accesspaths(EXPLAIN) in test and QA.
- statistis of tables/indexes involved .

Kjeld · Posted: Fri Jan 07, 2011 6:02 pm Post subject:

sijayapal · New User Joined: 23 Dec 2008 Posts: 69 Location: India

I GAVE FETCH ONLY IN THE CURSOR AND IT WORKED

Kjeld · Posted: Fri Jan 07, 2011 8:03 pm Post subject:

You mean you declared your cursor FOR READ ONLY?

Then it must have been a locking issue.