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.
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.
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.