[DB2] Stored Procedure ช่วยลบข้อมูลเยอะๆ

ในงานของเรา อาจจะมีการลบข้อมูลเยอะๆ ไม่ว่าใน DBMS ตัวไหนก็ตามครับ ถ้าเป็น DB2 ถ้าเราลบข้อมูลเยอะๆ อาจจะเจอปัญหา Transaction Log Full ได้ครับ ครั้นจะปิด Log แล้วลบทิ้ง ก็อาจจะมีปัญหาครับ เช่น ตัว HADR ถ้าปิด Log นี้ ไม่ Sync กันนะครับ ผมเลยมี Stored Procedure ที่ช่วยในการลบข้อมูล ดังนี้ครับ

SET CURRENT SCHEMA = INVEST;

SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,INVEST;

CREATE OR REPLACE PROCEDURE INVEST.DELETE_MANY_ROWS (
    IN TABSCHEMA	VARCHAR(128),
    IN TABNAME	VARCHAR(128),
    IN PREDICATE	VARCHAR(1000),
    IN COMMITCOUNT	INTEGER )
  SPECIFIC SQL160427171738433
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
P1: BEGIN
	DECLARE SQLCODE INTEGER;
	DECLARE txt VARCHAR(10000);
	DECLARE stmt STATEMENT;
	SET txt = 'DELETE FROM (SELECT 1 FROM "'
	|| tabschema || '"."' || tabname || '" WHERE '
	|| predicate || ' FETCH FIRST ' ||
	RTRIM(CHAR(commitcount)) || ' ROWS ONLY) AS D';
	PREPARE stmt FROM txt;
	l: LOOP
	EXECUTE stmt;
	IF SQLCODE = 100 THEN LEAVE l; END IF;
	COMMIT;
	END LOOP;
END P1;

เห็น Code และมาดูก่อนว่า มันต้องใส่ Parameter อะไรบ้าง

• TABSCHEMA : ระบุชื่อ Schema ของ Table ที่ต้องการจะลบข้อมูล
• TABNAME : ระบุชื่อ Table ที่ต้องการจะลบข้อมูล
• PREDICATE : SQL เงื่อนไจในการลบข้อมูล
• COMMITCOUNT : บอกให้ Stored Procedure รู้ว่าลบไปกี่ Row แล้วถึงจะ Commit ได้ครับ อันนี้ต้องจูนให้เหมาะสมกับ ขนาดของ Transaction Log ครับ

การทำงาน วน Loop DELETE ข้อมูลจนกว่า Query จะมี SQLCODE = 100 (No Data) ซึ่งหมายถึง ลบจนกว่าคำสั่ง DELETE จะไม่มีข้อมูลให้ลบครับผม

2019 Update: เพิ่มตัวอย่างการเรียกใช้งาน [DB2] ตัวอย่างการใช้งาน Stored Procedure DELETE_MANY_ROWS