Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    26

    Unanswered: Can somebody tell me why....?

    Hi all,

    can somebody tell me why the very last operation in a stored procedure (Z/0S, DB2 V7.1, Cobol LE370 stored procedure) has to be the DECLARE CURSOR?

    As a conscientious (sp?) programmer, I always check the SQL code after any SQL operation. However, if I do this AFTER the declare cursor with return, the result set is not available to the calling program. WHY? I once lost a couple of hours trying to figure out where my result set went. And I haven't found anything particularly enlightening on this subject anywhere.

    This is purely for my curiosity - if someone happens to know the why, I'd be interested.

    Thanx in advance

    L8r, eh?
    Ralph

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just to clarify, I think you mean that the OPEN cursor must be in the last SQL statement, not the DECLARE cursor. But I don't know the reason for it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2005
    Posts
    191
    (I too presume you mean OPEN) I've written SPs in this environment where I've checked the sqlcode after the open. And, since checking the sqlcode does not involve any interaction with DB2, I would be surprised if it did.

    The only thing that comes to mind is that you are somehow closing the cursor - either a commit/rollback without WITH HOLD or an explicit close.

    James Campbell

  4. #4
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    26
    Hi again,

    by 'last operation' I meant any operation, be it exec sql or otherwise. It seems there must be a goback immediately after the open cursor.

    yup, after the OPEN CURSOR I can't even check the sqlcode or my result set seems to disappear.

    In the calling program, after the exec sql call xxxx, I check the sqlcode to see if it is 466, when it is, then I do an associate locators followed by an allocate cursor using the locator.

    l8r, eh,
    Ralph

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •