Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    11

    Question Unanswered: Large deletes...

    Hello all,

    would anyone know how to run a large delete with a commit every n rows?

    How would I determine how much rollback/undo info is recorded in a transaction?


    Thanks in advance,
    Noel.

  2. #2
    Join Date
    Feb 2002
    Posts
    11
    OK, I've managed to work out how much redo I'm generating.

    I used snapshot monitoring to check 'UOW log used'

    Anyone any ideas on how to do a controlled delete?

    Thanks,
    Noel.

  3. #3
    Join Date
    Mar 2002
    Posts
    34
    The only way I know to do a control delete is using a stored proc with a commit count every x records. on the other hand if you want to truncate a table basically, I have posted a db2 cheat sheet that explains how to basically truncate in db2.

    Also, note if you are doing large deletes you wil need to reorg the table often.

    Hope this helps,

    BK

  4. #4
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Hi nekelly,

    Export all the rows from your table, other than the ones you want deleted and then load replace them back into the table.

    export to myexport.ixf of ixf select * from table where id <> value

    load from myexport.ixf of ixf replace into table





    Regards
    Sumeet

  5. #5
    Join Date
    Feb 2002
    Posts
    11
    Hello Sumeet, thanks for the advice. I need to do this delete on a production table that should have been truncated nightly in a batch job but the job was never implemented. I ended up doing the job in chunks manually.

    briankalberer, thank you for your reply also. Would you happen to know if DB2 offers a built-in scripting language similar to Oracle's PL/SQL instead of having to resort to Java/C?

    Thanks,
    Noel.

  6. #6
    Join Date
    Mar 2002
    Posts
    34

    DB2 PL SQL

    Db2 has there version of PL SQL. When you use the stored proc builder you can use sither java or sqlproc, for coding languages. If you need some code examples I have replied to a few other db forums post its with attachments of code.

  7. #7
    Join Date
    Mar 2002
    Posts
    34
    here's sample delete code

    CREATE PROCEDURE SIEBEL.DELETE_SHIP_STG()
    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_ship_stg CURSOR WITH HOLD FOR
    SELECT
    ROW_ID
    FROM
    SIEBEL.CX_SHIP_STG
    WHERE
    PERIOD_NAME LIKE '%2002 Q3' AND
    DATE(CREATED) = '2002-07-15'
    FOR UPDATE;

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    OPEN del_ship_stg;

    ins_loop:
    LOOP

    FETCH del_ship_stg 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.CX_SHIP_STG
    WHERE CURRENT OF del_ship_stg;


    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_ship_stg 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
  •