|
View previous topic :: View next topic
|
| Author |
Message |
murugan_mf
Active User
.jpg)
Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
murugan_mf
Active User
Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India
|
|
|
|
Enrico,
Is there a possibility of getting sqlcode -904 during cursor fetch. Please let me know. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Yes it is possible |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| 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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| 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 |
|
|
suresh1624
New User
Joined: 21 Nov 2007
Posts: 28
Location: chennai
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
