Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2015
    Posts
    11

    Unanswered: Batch deletes with commit count is deleting records very slow

    Hi, My requirement is to delete data from 1 master table and 5 child tables. Master table has got time stamp column to get the set of records to delete. Master table also has got unique primary key which is foreign key in child tables based on which child tables data get deleted. I am using the below stored procedure to delete the data from all the tables but getting deleted very slow. Please let me know if any thing can be corrected.

    CREATE PROCEDURE MY_PROCEDURE(IN v_SCHEMA_NAME VARCHAR(24),IN v_DAYS INTEGER, IN v_COMMIT_COUNT INTEGER)
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN

    -- DECLARE Statements
    DECLARE SQLCODE INTEGER;

    DECLARE v_CHILD1_DELETE_QUERY VARCHAR(1024);
    DECLARE v_CHILD2_DELETE_QUERY VARCHAR(1024);
    DECLARE v_CHILD3_DELETE_QUERY VARCHAR(1024);
    DECLARE v_CHILD4_DELETE_QUERY VARCHAR(1024);
    DECLARE v_CHILD5_DELETE_QUERY VARCHAR(1024);
    DECLARE v_MASTER_DELETE_QUERY VARCHAR(1024);

    DECLARE v_CHILD1_DELETE_STATEMENT STATEMENT;
    DECLARE v_CHILD2_DELETE_STATEMENT STATEMENT;
    DECLARE v_CHILD3_DELETE_STATEMENT STATEMENT;
    DECLARE v_CHILD4_DELETE_STATEMENT STATEMENT;
    DECLARE v_CHILD5_DELETE_STATEMENT STATEMENT;
    DECLARE v_MASTER_DELETE_STATEMENT STATEMENT;

    SET v_CHILD1_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD1_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS))
    FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_CHILD1_TABLE';

    SET v_CHILD2_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD2_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS)) FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_CHILD2_TABLE';

    SET v_CHILD3_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD3_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS))
    FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_CHILD3_TABLE';

    SET v_CHILD4_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD4_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS))
    FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_CHILD4_TABLE';

    SET v_CHILD5_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD5_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS))
    FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY) AS DELETE_CHILD5_TABLE';

    SET v_MASTER_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (
    CURRENT TIMESTAMP(0) - ' || v_DAYS || ' DAYS) FETCH FIRST ' || v_COMMIT_COUNT || ' ROWS ONLY) AS DELETE_MASTER_TABLE';


    PREPARE v_CHILD1_DELETE_STATEMENT FROM v_CHILD1_DELETE_QUERY;
    PREPARE v_CHILD2_DELETE_STATEMENT FROM v_CHILD2_DELETE_QUERY;
    PREPARE v_CHILD3_DELETE_STATEMENT FROM v_CHILD3_DELETE_QUERY;
    PREPARE v_CHILD4_DELETE_STATEMENT FROM v_CHILD4_DELETE_QUERY;
    PREPARE v_CHILD5_DELETE_STATEMENT FROM v_CHILD5_DELETE_QUERY;
    PREPARE v_MASTER_DELETE_STATEMENT FROM v_MASTER_DELETE_QUERY;

    DELETE_LOOP:
    LOOP
    EXECUTE v_CRAD_DELETE_STATEMENT;
    EXECUTE v_SRT_DELETE_STATEMENT;
    EXECUTE v_DES_DELETE_STATEMENT;
    EXECUTE v_EXCLOG_DELETE_STATEMENT;
    EXECUTE v_PENDLOG_DELETE_STATEMENT;
    EXECUTE v_APPDATA_DELETE_STATEMENT;

    IF SQLCODE = 100 THEN
    LEAVE DELETE_LOOP;
    END IF;
    COMMIT;
    END LOOP;

    COMMIT;

    END
    Last edited by Anjidbforums; 08-27-15 at 06:04.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    time is relative
    very slow = can be xxx compared to ???
    quantity ? time ??? lobs ???
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2015
    Posts
    11
    Quote Originally Posted by przytula_guy View Post
    time is relative
    very slow = can be xxx compared to ???
    quantity ? time ??? lobs ???
    Very slow means its getting deleted 20k records in an hour but we have to delete 600k from live system.

    Also commit is not working for every commit count given in as input parameter i.e example 10k. As per my knowledge select query should fetch first 10k records and delete them and commit in the same flow but that is not done when we kill the scripts no even single record. Please help any error in my logic.

    Thanks in advance.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    definitions / hardware /mem / resources ??? all the same db cfg?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Do you have index on master table with timestamp_column as first key column? If not, create index with timestamp_column, unique_id_column on master table.

    Satya..

  6. #6
    Join Date
    Aug 2015
    Posts
    11
    Quote Originally Posted by stiruvee View Post
    Do you have index on master table with timestamp_column as first key column? If not, create index with timestamp_column, unique_id_column on master table.

    Satya..
    Thank you Satya. We do have indexes on primary key column and time stamp column.

  7. #7
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Try moving fetch clause to inner select for master table.

    SET v_CHILD1_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD1_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS)
    FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY)
    ) AS DELETE_CHILD1_TABLE';

  8. #8
    Join Date
    Aug 2015
    Posts
    11
    Quote Originally Posted by stiruvee View Post
    Try moving fetch clause to inner select for master table.

    SET v_CHILD1_DELETE_QUERY = 'DELETE FROM (SELECT 1 FROM ' || v_SCHEMA_NAME || '.CHILD1_TABLE WHERE UNIQUE_ID_COLUMN IN (
    SELECT UNIQUE_ID_COLUMN FROM ' || v_SCHEMA_NAME || '.MASTER_TABLE WHERE TIMESTAMP_COLUMN < (CURRENT TIMESTAMP(0) - '|| v_DAYS ||' DAYS)
    FETCH FIRST ' || RTRIM(CHAR(v_COMMIT_COUNT)) || ' ROWS ONLY)
    ) AS DELETE_CHILD1_TABLE';

    Thank you I can do this change as you suggested. Please let me know why batch commit not working as I said above I have commit count for every 5k chunk as per the logic after processing every 5k records it should commit the work but not committing.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Quote Originally Posted by Anjidbforums View Post
    Thank you Satya. We do have indexes on primary key column and time stamp column.
    Do you have the corresponding indexes on each of the child's tables foreign key? Also, mush safer and more practical to define the foreign key relationship as delete cascade, then just simply delete from the parent and let the database take care of the children. Also, just for the sake of another idea, have you considered load replace with the just the data you wish to keep? no logging and sometimes faster, but there is a small associated outage.
    Dave

  10. #10
    Join Date
    Aug 2015
    Posts
    11
    In the same procedure I am trying to print some messages but getting -204 error. Can some one explain how to enable logging framework in DB2 stored procedure?

  11. #11
    Join Date
    Aug 2015
    Posts
    11
    Hi Dave, In child tables foreign key is not a primary key to have the indexes and for foreign key relation on delete no action set so can not delete child tables on cascade if master table data deleted. No logging at the moment.

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you should still have the appropriate index to support the manner in which you are attempting to delete the data.
    Dave

Tags for this Thread

Posting Permissions

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