Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1278
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: 1278
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 Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm
No new posts TXT2PDF problem - printing @ in PDF vipul65 All Other Mainframe Topics 9 Thu Aug 25, 2016 12:48 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us