Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    28

    Unanswered: Another DB2 OS/390 Question...

    I'm using v7 on OS/390...
    I was wondering if this cursor would work..and if only the appropriate sql would be materialized during OPEN or all sqls would be materialized...

    Declare cursor x1
    case var
    when 1 then Select.....(sql1)
    when 2 then Select.....(sql2)
    when 3 then Select.....(sql3)
    end
    end-exec

    Thanks!!!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What is var?

    A host variable?

    Can't be a college course...they don't teach mainframe anymore

    And yes, everything would be evaluated for the first page of data (I Think..) unless there's a sort, the it would be the entire set (I Think...but only sometimes)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What is var?

    A host variable?

    Can't be a college course...they don't teach mainframe anymore

    And yes, everything would be evaluated for the first page of data (I Think..) unless there's a sort, the it would be the entire set (I Think...but only sometimes)

    Also I don't believe that construct would work...you would also need to add FROM sysibm.sysdummy1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Is this a cursor declared within a program? If so, then why not have a separate cursor for each SQL statement and then let the program open the specific cursor based upon a programmatic evaluation of the value of "var"?

  5. #5
    Join Date
    Mar 2004
    Posts
    28
    yes var will be a host variable.
    yes it will be within a program.
    The multiple cursors are possible but I would like to minimize the coding.
    The actual code would actually result to 17 cursors so it would also need 17 sets of open/fetch/close codes. =(

    will this work? note that there is only 1 cursor(x1) but with multiple possible declarations.
    exec sql
    Case :hostvar
    When 1 declare cursor x1
    sql1 (select ...from...condition1etc)
    When 2 declare cursor x1
    sql2 (select ...from...condition2etc)
    When 3 declare cursor x1
    sql3 (select ...from...condition3etc)
    end
    end exec

    open x1
    fetch x1
    close x1

    Thanks for the replies!!!

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    I doubt that it will work that way. The "exec sql" statements are commented out in the precompile when the DBRM is created. I don't think that DB2 would like the case statement in the context you have it. You may need to code a separate cursor for each of the 17 sql statements that you have. Are these sql statements all similar?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Minimize coding?

    OK, lose the cursors...

    Also, if you do try to do it that way, You still have to code the opens and the fetches...which would probably be specific to each cursor anyway...

    Can you show us a sample of what one cursor is and what it's to be used for?

    Are you reading QSAM Files?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Posts
    28
    The sql statements are similar in the sense that the selected columns and some of the critierias are the same.

    if different cursors...
    cursor1 -> valid only if hostvar=x
    select a,b,c
    from taba
    where d >= :varmin
    and d <= :varmax
    and ....(common conditions)


    cursor2 -> valid only if hostvar=y
    select a,b,c
    from taba
    where e >= :varmin
    and e <= :varmax
    and ....(common conditions)


    cursor3 -> valid only if hostvar=z
    select a,b,c
    from taba
    where f >= :varmin
    and f <= :varmax
    and ....(common conditions)


    Since the selected columns and program logic for openning and retrieving records are the same, I thought I could minimize coding by using only 1 set of open/fetch/close.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The "declare cursor" statement is not actually executable code. In fact, it should be placed in the Working Storage section of the program and not in the Procedure Division (although it will work in any part of the program so long as it physically resides before the open cursor statement). Therefore the declare cursor itself never does anything, and never retrieves any (or all) rows.

    The Open Cusor statement (which is really what invokes the declared cursor) can be in a conditional statement. Whether the cursor is materialized when the open is executed (or when the first fetch is executed) depends on several factors such as whether DB2 has to retrive all rows and sort them in order to return the first row to the program when a fetch occurs. In the mainframe, DB2 will usually not actually do anything until the first fetch is executed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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