Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unanswered: DB2 LUW Cost of Open/Close Cursor

    Hi all,

    We are currently developing DB2 stored procedure for AIX. The DB2 version is 8.1.2 FixPack 4.

    In the stored procedure, we need to declare 2 cursors and these cursors will be used in loop, i.e. one inner and one outer loop.

    The outer loop will insert data into a table based on the data provided from the inner loop. However, the number of records in inner loop cursor is expected to be small and it requires to start from beginning if it comes to the end of the cursor. As DB2 LUW does not support the SCROLL CURSOR option, the only option left will be to close the inner loop cursor and re-open the cursor if the cursor comes to the end. Will the open and close cursor has great impact to the performance of the stored procedure? Any reply is much appreciated.

    Regards
    WT

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure of what you are asking. How about posting the Stored Procedure code. It might clarify what you want to do.

    Andy

  3. #3
    Join Date
    Sep 2005
    Posts
    5
    Hi Andy

    I declared 2 cursors in stored procedure as below;

    DECLARE lsCur CURSOR WITH HOLD FOR
    SELECT a.primarykey, a.contactkey, l.primarykey, s.* FROM bcbleadstaging s, account a, lead l, campaign c
    WHERE s.PARTY_rk = a.BCBSASPARTYRK
    AND s.campaigncode = c.bcbSASCamCode
    AND s.commcode = c.id
    AND s.occurrenceid = l.BCBSASOCCURID
    AND a.primarykey = l.accountkey
    AND s.campaigncode = CAMPAIGN_CODE
    AND s.commcode = COMM_CODE
    AND s.occurrenceid = OCCUR_ID
    ORDER BY BRANCHCODE, CHANNELTEAM;

    DECLARE bcCtCur CURSOR WITH HOLD FOR
    SELECT s.primarykey, i.primaryKey FROM BCBLeadStaging ls, Info i, SalesChannel s
    WHERE substr(i.BCBBranch,4,4) = ls.BranchCode
    AND i.BCBPosition = (SELECT v.Value FROM Value v
    WHERE substr(v.IndexName,1,3) = '003'
    AND substr(ls.ChannelTeam,1,2) = substr(v.IndexName,4,2)) -- indicate this is channel team
    AND ls.CampaignCode = CAMPAIGN_CODE
    AND ls.CommCode = COMM_CODE
    AND ls.OccurrenceId = OCCUR_ID
    AND ls.BranchCode = currentBr
    AND ls.ChannelTeam = currentCt
    AND i.Status = 'ISActive'
    AND i.PrimaryKey = s.Responsible
    AND s.Type = 'ETypeInt'
    GROUP BY s.PrimaryKey, i.primaryKey;

    Basically I have to loop 2 cursors above. As the first lsCur has more records than the bcCtCur and it requires the data from bcCtCur to update a table, I need to Close bcCtCur and re-open the cursor again till the end of lsCur.
    The pseudo-code as below:

    OPEN lsCur;
    LOOP lsCur
    FETCH lsCur INTO....
    OPEN bcCtCur;
    FETCH bcCtCur INTO scKey, infoKey;
    Update table using scKey, infoKey;
    If bcCtCur reach end of cursor
    Close bcCtCur;
    Open bcCtCur;

    FETCH bcCtCur again
    END

    Question: Will the close cursor and open cursor within a loop has great performance impact? We expect the lsCur has roughly 50000 records and must complete everything within an hour time.

    Regards
    WT

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Without the entire SP code, it is still very hard to figure out what you are trying to do. That being said, you pseudo code shows only one loop on the IsCur cursor. I can only assume that the other cursor is only supposed to return one row, because you only are doing something based on that first row in your pseudo code. Why you are trying to detect if you are at the end of that cursor, and close then reopen it is beyond me.

    Andy

Posting Permissions

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