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
 
Handling sqlcode -904 during cursor fetch

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

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Thu Apr 16, 2009 2:26 pm    Post subject: Handling sqlcode -904 during cursor fetch
Reply with quote

Hi,

While opening a cursor, the query for the cursor gets executed and we will obtain the result set. My question is will the result set be buffered and from the buffer each row will be fetched.

Is it ok to handle the resource unavailable error(sqlcode -904) only during open cursor, because the query gets executed while opening the cursor or do i need to handle sqlcode -904 during cursor fetch also. Please let me know your thoughts.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10373
Location: italy

PostPosted: Thu Apr 16, 2009 2:33 pm    Post subject: Reply to: Handling sqlcode -904 during cursor fetch
Reply with quote

the error handling should be defined by Your organization standards

it is a good practice anyway, to handle all the error situations
wherever they might happen...

could be
specific handling with error recovery
generic handling with just a message

but as I said
choose one according to Your organization standards
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Thu Apr 16, 2009 2:48 pm    Post subject:
Reply with quote

Enrico,

Is there a possibility of getting sqlcode -904 during cursor fetch. Please let me know.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 16, 2009 3:07 pm    Post subject:
Reply with quote

Yes it is possible
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Thu Apr 16, 2009 3:12 pm    Post subject:
Reply with quote

Quote:
Is there a possibility of getting sqlcode -904 during cursor fetch
Yes...

If you got -904 and the SQL statement being executed was a cursor FETCH, DB2 closes the cursor.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Thu Apr 16, 2009 11:34 pm    Post subject: Reply to: Handling sqlcode -904 during cursor fetch
Reply with quote

Hi Murugan

I don't find any chance of getting SQLCODE -904 while using FETCH. Yes, there is this chance.
Assume the OPEN cursor reads many rows from the base table and if there is not enough buffer space is available in the system, the OPEN will fail.
Since the OPEN cursor is done successfully, your desired rows are available only in the buffer as "result table" or "result set". That "result set" is accessible only to the application program which OPENs the cursor. No other program can access the buffer.
FETCH read the rows from the buffer only, not from the base table.
So properly and logically coded program will never get SQLCODE -904 while using FETCH even though your cursor is declared with "FOR UPDATE OF". Yes, the -904 can occur.

Hope this will clear your doubt.

Regards
Raghu
Back to top
View user's profile Send private message
MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Fri Apr 17, 2009 12:03 am    Post subject:
Reply with quote

Yes- Possible.

Code:

-904   UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON           
       reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME             
       resource-name                                                             
                                                                                 
Explanation:  The SQL statement could not be executed because resource           
resource-name of type resource-type was not available at the time for the         
reason indicated by reason-code. Refer to Table 3 in Appendix B, "Problem         
determination" in item -PROBLEM_DETERMI for an explanation of resource type       
codes. Refer to "DB2 codes" in item -DB2_CODES for an explanation of the given   
reason code.                                                                     
                                                                                 
If this is issued in connection with Multilevel Security authorization           
(resource-type 402), then the Security Server return and reason codes for         
the unavailable resource appear as the resource-name. In this case, refer         
to the z/OS Security Server RACROUTE Macro Reference.                             
                                                                                 
System Action:  The SQL statement cannot be executed. If the SQL statement       
being executed was a cursor FETCH, DB2 closes the cursor. Subsequent             
attempts to use that cursor without first doing an OPEN for it receive an         
SQLCODE -501.                                                                     
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Sat Apr 18, 2009 1:57 am    Post subject: Reply to: Handling sqlcode -904 during cursor fetch
Reply with quote

Hi MFRASHEED

Thanks for the information from the IBM manual.
Can you say in which DB2 version's manual it is available.

Regards
Raghu
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sat Apr 18, 2009 2:04 am    Post subject:
Reply with quote

Quote:
Can you say in which DB2 version's manual it is available.
Go to the top of the page and click on IBM Manuals.
Scroll down to find a section titled "Manuals: Messages & Codes".
Pick out whatever DB2 Messages & Codes you want.
Back to top
View user's profile Send private message
suresh1624

New User


Joined: 21 Nov 2007
Posts: 28
Location: chennai

PostPosted: Fri Apr 24, 2009 11:32 pm    Post subject:
Reply with quote

If you a get a -904 cursor check the following
a) status of all the tablespaces of the tables used in cursor.they should be in RW
b) Some application is accessing the page for which ur cursor need it for update of the row
c) The cursor has fetched a huge number of rows for which the buffer pool is not sufficient to hold the records

Most of the times C) scenario is true and the cursor's where clause needs to be examined/changed
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Sat Apr 25, 2009 12:00 am    Post subject: Reply to: Handling sqlcode -904 during cursor fetch
Reply with quote

Hi suresh1624

Quote:
The cursor has fetched a huge number of rows for which the buffer pool is not sufficient to hold the records


If the bufferpool is not sufficient, the OPEN cursor will fail. Then how can you FETCH. Is your program is not checking the sqlcode of any SQL statement?


Regards
Raghu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Sat Apr 25, 2009 12:04 am    Post subject: Reply to: Handling sqlcode -904 during cursor fetch
Reply with quote

If you get a -904,
expand the SQLCA with DSNTIAR,
which will tell you EXACTLY what the resource name is
and a REASON CODE which if you look up in the documentation
will tell you why.

that way you don't have to go thru a check list of possible problems
that someone gave you, which may or may not apply to your system.
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 query to fetch record which has only ... maxsubrat DB2 3 Mon Dec 11, 2017 5:03 pm
This topic is locked: you cannot edit posts or make replies. Cobol to fetch the content by using s... arunc55 COBOL Programming 4 Fri Nov 17, 2017 1:25 pm
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm

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