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

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

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

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