Declare Cursor in AS/400 COBOL

15 pts.
Tags:
AS/400
COBOL
SQL
I have and SQLCBL program that declares a cursor, opens the cursor, and then does a fetch to process the data in the cursor. The fetch is not always retrieving the first record in the file. It's my understanding that the open positions the pointer right before the first record and that the Fetch with no direction is a fetch next. Does anyone know of any possible causes or solutions to this issue. Thanks!


Software/Hardware used:
OS400
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    Assuming that the program should FETCH the "first" row first and that it doesn't always do so, the most likely reason is that it is coded incorrectly. But since we don't know anything about your file and can't see any of your code, we don't have any good idea what's wrong. There is also a very slim possibility that either DB2 or COBOL PTFs need to be up to date.
    36,035 pointsBadges:
    report
  • bvining
    When you declare your cursor you can use ORDER BY to control the order of the rows fetched. No ORDER BY means you don't care and the rows will be returned in whatever order is "convenient".
    7,070 pointsBadges:
    report
  • rdgribben
    Wow, I am honored to get a reply from you, Bruce. About 5 years ago I purchased your book IBM System I API's at Work, and emailed you with questions related to some issues we were having at the client site. Your insight and expertise was a tremendous help to me! Now back to the current question. I did not have an ORDER BY on my declare. I thought I didn't need it since the cursor was declared for a logical file keyed in the order that I needed the data. Some of my co-workers had just suggested this morning that there could be a problem with my ORDER BY. I have changed the declare to include an ORDER BY and the FETCH seems to be working as I would expect. Another interesting point is when I run the same program without the ORDER BY against the same file on our development machine which is V6R1 I get the rows in the order expected. The machine with the issue is V5R4. Just wanted to see if you had any thoughts about the different OS.
    Also, thank you TheRealRaven for your input.
    15 pointsBadges:
    report
  • bvining
    I doubt the OS level is making a difference. If you don't have an ORDER BY then the optimizer is free to do what it thinks best (though it's always possible for optimizer changes across releases). For "some" reason it is choosing to use the index in one case, in another it isn't. It could based on factors such as what you have specified for the WHERE clause suggesting another index might be more suitable, the number of records in the base physical file, a lot of things.
    7,070 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: