Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Stored Procedure Looping

    Hi,

    I am new to stored procedure. could you please help with a sample stored procedure for this requirement?

    Requirement:
    The source table A has 10000 records.
    Need to select first 100 records from the source table.store it an cursor and process the records(insert,update and delete).
    After processing the 100 records, need to delete the 100 records from the source table.
    In similiar manner i need to process the entire records in the table.

    Note: I need only 100 records stored in cursor for processing.after this records is processed i need to select next 100 records from the source table A.
    if any exception occurs during the process, i need only that particular batch to be rollback and the process has to continue.

    For e.g:
    1st 100 records - successfully processed
    2nd 100 records - successfully processed
    3rd 100 records - exception occured during 50 record, only this batch needs to rollback
    4 th 100 records - process has to continue

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Use a WITH HOLD cursor and commit between the batches.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Thanks stolze

Posting Permissions

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