|
View previous topic :: View next topic
|
| Author |
Message |
sijayapal
New User
.jpg)
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. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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. |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
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? |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I would compare :
- accesspaths(EXPLAIN) in test and QA.
- statistis of tables/indexes involved . |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
| sijayapal wrote: |
But i don't know how to check the DB2 Master log
could you pls let me know@kjeld |
You probably have to ask a DBA at your installation what the name of the DB2 subsystem task is, and then look that name up in your JES operations tool.
| Quote: |
| I tried replacing host variables with literals in SPUFI |
I was thinking more on the host variables your cursor is fetched into. |
|
| Back to top |
|
|
sijayapal
New User
Joined: 23 Dec 2008
Posts: 69
Location: India
|
|
|
|
I GAVE FETCH ONLY IN THE CURSOR AND IT WORKED  |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
You mean you declared your cursor FOR READ ONLY?
Then it must have been a locking issue. |
|
| Back to top |
|
|
|
|
