Results 1 to 8 of 8

Thread: Commitcount

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Commitcount

    Quick question:

    Is COMMITCOUNT a valid option within a stored procedure? I have a procedure that will be updating hundreds of thousands of rows and want to commit every 1000 rows.

    What would that syntax look like?

    Thanks!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, it is not a valid option is a stored procedure. The only way I can think to do what you want is to keep a counter and when it reaches you limit (1000) then commit.

    DECLARE counter integer;
    ...

    SET COUNTER = 0;
    WHILE ()
    INSERT ROW
    SET COUNTER = COUNTER + 1;
    IF COUNTER = 1000
    THEN
    COMMIT WORK;
    SET COUNTER = 0;
    END IF;
    END WHILE;
    ...

    HTH

    Andy

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Here's what I came up with. One question: does it need to be COMMIT WORK or will just COMMIT suffice?

    DECLARE RECORDCOUNT INTEGER;
    DECLARE decArchiveRetrievalID DECIMAL (13,0);
    DECLARE AT_END int DEFAULT 0;
    DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
    DECLARE UPDATE_CURSOR CURSOR WITH HOLD FOR
    SELECT ARCHIVERETRIEVALID FROM AIM.AIMRETRIEVEDITEM
    WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID IN (SELECT
    AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    FROM
    AIM.ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVEDITEM
    WHERE
    AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
    AND AIM.AIMRETRIEVEDITEM.IMAGEFRONT IS NOT NULL
    AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));

    DECLARE CONTINUE HANDLER FOR NOT_FOUND
    SET AT_END = 1;

    OPEN UPDATE_CURSOR;
    FETCH_LOOP:
    LOOP
    FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
    IF AT_END = 1 THEN
    LEAVE FETCH_LOOP;
    END IF;
    UPDATE AIM.AIMRetrievedItem
    SET ImageFront=NULL,
    ImageFrontSize=NULL,
    ImageFrontType=NULL,
    ImageBack=NULL,
    ImageBackSize=NULL,
    ImageBackType=NULL
    WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = decArchiveRetrievalID;
    SET RECORDCOUNT = RECORDCOUNT+1;
    IF RECORDCOUNT=1000 THEN
    COMMIT;
    END IF;
    END LOOP FETCH_LOOP;
    CLOSE UPDATE_CURSOR;

    Thanks for the input....
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    COMMIT and COMMIT WORK are the same. The keywork is implied if it is omitted.

    Andy

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Thanks!

    Last question:

    The other dba here keeps talking about having to commit outside the procedure then returning to the caller?!? He said something about not being able to do an explicit commit inside of a stored procedure...

    As far as I know, what I came up with should work just fine - nothing ele should be needed, right?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    We have several SPs that have the COMMIT statement to commit the work they do. We are running 8.1.2 on Linux. the other OSs may be different, I would not know.

    If it is not allowed then the create procedure will fail.

    Andy

  7. #7
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I'm trying to prove that the commits are working, so I put this logic in there:

    SET intLOOPS = 0;

    OPEN UPDATE_CURSOR;
    FETCH_LOOP:
    LOOP
    FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
    IF AT_END = 1 THEN
    LEAVE FETCH_LOOP;
    END IF;
    UPDATE AIM.AIMRetrievedItem
    SET ImageFront=NULL,
    ImageFrontSize=NULL,
    ImageFrontType=NULL,
    ImageBack=NULL,
    ImageBackSize=NULL,
    ImageBackType=NULL
    WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = decArchiveRetrievalID;
    SET RECORDCOUNT = RECORDCOUNT+1;
    IF RECORDCOUNT=10 THEN
    COMMIT;
    SET intLOOPS = intLOOPS+1;
    SET RECORDCOUNT=0;
    END IF;
    END LOOP FETCH_LOOP;
    CLOSE UPDATE_CURSOR;

    intLOOPS is defined as an output parameter. When I run the procedure, the rows do get updated, but intLOOPS is always zero. Is this somehow jumping out of the loop and picking up the original value of intLOOPS?
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try it this way:

    DECLARE intLOOPStmp integer;

    SET intLOOPStmp = 0;

    OPEN UPDATE_CURSOR;
    FETCH_LOOP:
    LOOP
    FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
    IF AT_END = 1 THEN
    LEAVE FETCH_LOOP;
    END IF;
    UPDATE AIM.AIMRetrievedItem
    SET ImageFront=NULL,
    ImageFrontSize=NULL,
    ImageFrontType=NULL,
    ImageBack=NULL,
    ImageBackSize=NULL,
    ImageBackType=NULL
    WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = decArchiveRetrievalID;
    SET RECORDCOUNT = RECORDCOUNT+1;
    IF RECORDCOUNT=10 THEN
    COMMIT;
    SET intLOOPStmp = intLOOPStmp+1;
    SET RECORDCOUNT=0;
    END IF;
    END LOOP FETCH_LOOP;
    CLOSE UPDATE_CURSOR;

    SET intLOOPS = intLOOPStmp;;

    HTH

    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
  •