Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Fetch problem in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Fri Jan 07, 2011 2:18 pm    Post subject: Fetch problem in DB2
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 07, 2011 4:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Jan 07, 2011 4:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Fri Jan 07, 2011 5:16 pm    Post subject:
Reply with quote

The same program was working fine in test environment. But only in QA it is getting hanged.
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Jan 07, 2011 5:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Fri Jan 07, 2011 5:37 pm    Post subject:
Reply with quote

I tried replacing host variables with literals in SPUFI. I have to check the DB2 Master log.
Back to top
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Fri Jan 07, 2011 5:44 pm    Post subject:
Reply with quote

But i don't know how to check the DB2 Master log icon_redface.gif
could you pls let me know@kjeld
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 07, 2011 5:49 pm    Post subject:
Reply with quote

I would compare :
- accesspaths(EXPLAIN) in test and QA.
- statistis of tables/indexes involved .
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Jan 07, 2011 6:02 pm    Post subject:
Reply with quote

sijayapal wrote:
But i don't know how to check the DB2 Master log icon_redface.gif
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
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Fri Jan 07, 2011 7:39 pm    Post subject:
Reply with quote

I GAVE FETCH ONLY IN THE CURSOR AND IT WORKED icon_smile.gif
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Jan 07, 2011 8:03 pm    Post subject:
Reply with quote

You mean you declared your cursor FOR READ ONLY?

Then it must have been a locking issue.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Problem commiting to DB2 from IMS MPP Ole Soerensen IMS DB/DC 2 Wed Jul 26, 2017 5:19 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us