Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: Can cursors be replaced by singleton selects which result in performance gain ?

    Hi all,

    I would like to retrieve all addresses related to an account.

    Currently my application uses a cursor to fetch all possible addresses, say something like below :

    Exec SQL
    Declare address-cursor Cursor for
    Select Address-line1,
    Addess-line2,
    City,
    State,
    Country
    From Address_table
    where Account_number = :ws-account-number
    End-Exec.

    There are a possible of say 4 addresses possible for an account - legal, mailing, business & seasonal.

    I am trying to replace the above cursor as a singleton select like

    Exec SQL
    Select Account_number,
    Max(Case Address_type
    When 'L'
    Then
    Address-line1
    ||Addess-line2
    ||City
    ||State
    ||Country
    Else
    NULL
    END
    ),
    Max(Case Address_type
    When 'M'
    Then
    Address-line1
    ||Addess-line2
    ||City
    ||State
    ||Country
    Else
    NULL
    END
    ),
    Max(Case Address_type
    When 'B'
    Then
    Address-line1
    ||Addess-line2
    ||City
    ||State
    ||Country
    Else
    NULL
    END
    ),
    Max(Case Address_type
    When 'S'
    Then
    Address-line1
    ||Addess-line2
    ||City
    ||State
    ||Country
    Else
    NULL
    END
    )
    INTO :Account-number,
    :Ws-Legal-Address-text:Ws-Legal-Address-len,
    :Ws-Mailing-Address:Ws-Mailing-Address-len
    :Ws-Business-Addess:Ws-Business-Address-len
    :Ws-Seasonal-Address:Ws-Seasonal-Address-len
    From Address_table
    Where Account_number = :Ws-Account-number
    Group by Account_number
    End-Exec

    The query fetches all addresses into the corresponding columns at a single select.

    But my question is will it actually result in performance gains - because we eliminate Cursor (OPEN,FETCH,CLOSE) ?
    How is the above singleton select processed internally by DB2 ? Is it processed similar to a cursor internally or like a singleton select ?
    Could the Max function here be replaced so that the column function doesnot cause any over load ?

    Your suggestions are much appreciated.

    Thanks.
    Siddharth

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DB2 processes all selects using a cursor. So whether you declare it and use it or DB2 does it internally, it is still the same.

    Andy

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation

    Yes you can !

    For this you have to prepare loop.

    For example you have table tbl1 with integer id column

    You perform LOOP until sqlcode <> +100
    Code:
    select id into :h-id from tbl1 where id >= :h-id
              order by id asc
              fetch first row only
    then
    Code:
    h-id = h-id + 1
    , or something like this.

    This method heavy used in CICS, when you perform XCTL and send last id to receiving program.

    Will work

    Lenny
    Last edited by Lenny77; 12-01-09 at 11:31.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A loop also uses a cursor under the covers.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by stolze View Post
    A loop also uses a cursor under the covers.
    But you can forget about...

    Lenny

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, you won't see it - as is the case for a SELECT INTO statement. But the cursor is still there, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    2nd part of question is: "...which result in performance gain" ?

    Be sure: Cursor is more efficient.

    Use cursors where it possible.

    Lenny

Posting Permissions

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