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.

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.

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

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

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.

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.

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