Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    10

    Unanswered: How to delete large amount of records in DB2?

    Hello,
    Would anyone know how to run a large delete with a commit every n
    rows?
    I want to delete a large amount of records whose timestamps are
    during a specific time duration.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to delete large amount of records in DB2?

    import from /dev/null of del replace into <table-name>

    HTH

    Cheers

    Sathyaram

    Originally posted by dnlchou
    Hello,
    Would anyone know how to run a large delete with a commit every n
    rows?
    I want to delete a large amount of records whose timestamps are
    during a specific time duration.

    Thanks in advance.

  3. #3
    Join Date
    Jul 2002
    Posts
    25
    You can also use the following SQL query to remove all the rows in a table

    ALTER TABLE DummyTab activate not logged initially WITH EMPTY TABLE

    However the condition here is that DummyTab has to be created with the NOT
    LOGGED INITIALLY clause.
    But note that, the primary key in DummyTab cannot be referenced as a foreign
    key if DummyTab is created
    with this clause.

  4. #4
    Join Date
    Mar 2002
    Posts
    34

    large delete - commit every n records

    Please see code below...Hope this helps

    //you want to have a counter then commit using the following
    //
    // IF MOD(v_rowCount,1000) = 0 THEN
    // COMMIT;
    // END IF;


    CREATE PROCEDURE SIEBEL.DELETE_EIM_PROD_SHIP(IN BATCH INT)
    LANGUAGE SQL

    BEGIN
    DECLARE v_rowId VARCHAR(50);
    DECLARE v_rowCount INT DEFAULT 1;
    DECLARE at_end INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE del_eim CURSOR WITH HOLD FOR
    SELECT
    ROW_ID
    FROM
    SIEBEL.EIM_PROD_SHIP
    WHERE IF_ROW_BATCH_NUM < BATCH
    FOR UPDATE;

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    OPEN del_eim;

    ins_loop:
    LOOP

    FETCH del_eim INTO
    v_rowId;

    set v_rowCount = v_rowCount + 1;

    IF at_end = 1 THEN
    LEAVE ins_loop;
    ELSEIF at_end <> 1 THEN

    DELETE FROM SIEBEL.EIM_PROD_SHIP
    WHERE CURRENT OF del_eim;


    IF MOD(v_rowCount,1000) = 0 THEN
    COMMIT;
    END IF;

    SET at_end = 0;
    ITERATE ins_loop;
    END IF;/* FOR AT_END */
    END LOOP;
    CLOSE del_eim WITH RELEASE;
    END

Posting Permissions

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