Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007

    Unanswered: delete is taking 15 hours for 15 lacs records


    I'm using db2v8.1 on AIX.
    I'm deleting 15 lacs records from a table at our testing server and it's taking 10 minutes to delete the records.

    The same query is taking 16-17 hours to delete the records from other server database (this server is an enterprise server and used for developement purpose).

    I compared the tablespace , bufferpool space, transaction log parameters. The values are same in both the databases.

    I applied reorg, runstats before applying the delete query on both databases.
    but still the development database is taking 15 hours to delete the records.

    It look strange to me and I'm not finding any difference in both databases.

    Can anyone help me to find the issue as why deletes is taking such a long time?

  2. #2
    Join Date
    May 2006
    hi .. i m a oracle newbie.... not a db2 person.
    wat abt constraints on ur other server where it is taking more time. there is something called DISABLE NOVALIDATE in Oracle. i wonder you have something similar in DB2

    can u try disabling the constraints on ur table and then chk how long it takes

  3. #3
    Join Date
    Nov 2007
    same constraints are there in both the tables.

    Both the databases tables are same when compared for the data, table design etc. Not sure with the file system and other DB2 internal settings.

  4. #4
    Join Date
    May 2006
    wat abt Indexes? are indexes enabled in tables on both DBs?
    are both databases in archive log mode?
    on ur second server are there users accessing/modifying data in the table when you are deleting the records??
    either of which will increase the time required to delete!!
    is the second server under heavy load with users accessing it?

  5. #5
    Join Date
    Nov 2007
    why delete first ...
    if on unix you can create truncate stored procedure here is the body of the stored procedure
    here is the procedure
    -- 'TRUNCATE' stored procedure removes all rows from the table without using
    -- DELETE operation thereby eliminating extensive logging and resulting in
    -- better performance.
    -- Create a stored Procedure 'truncate' to implement the truncate table
    -- functionality. Use IMPORT with REPLACE INTO clause to implement truncate.
    -- IMPORT is done using ADMIN_CMD in SQL. Use '/dev/null'(for UNIX) &
    -- 'NUL'(for windows) for IMPORT as this file always exists & does not
    -- contain anything.

    -- Create the stored procedure 'truncate'

    CREATE PROCEDURE truncate(IN sch_name VARCHAR(130),IN tab_name VARCHAR(130))
    -- SQL Stored Procedure truncate

    DECLARE stmt VARCHAR(1000);
    DECLARE param VARCHAR(1000);
    DECLARE full_name VARCHAR(1000);

    IF sch_name IS NULL
    SET full_name = tab_name;

    -- Check whether the table exists or not
    SELECT tabname INTO a
    WHERE tabname = UCASE(tab_name);

    SET full_name = sch_name||'.'||tab_name;

    -- Check whether the table exists or not
    SELECT tabname INTO a
    WHERE tabname = UCASE(tab_name) AND tabschema = UCASE(sch_name);

    END IF;

    IF UCASE(a) = UCASE(tab_name)
    -- Uncomment one of the following statements depending on the
    -- platform on which the sample is run.
    SET param = 'IMPORT FROM /dev/null OF DEL REPLACE INTO '||full_name;
    -- SET param = 'IMPORT FROM NUL OF DEL REPLACE INTO '||full_name;

    SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';

    PREPARE s1 FROM stmt;
    EXECUTE s1 USING param;


    -- Table does not exists.

    END IF;

    END @

  6. #6
    Join Date
    Jan 2003
    Provided Answers: 5
    Truncating a table is only valid if you want to delete all of the rows. I am assuming that this is not the case for the OP.

    You need to ask yourself what is different between the two systems (you will have to look at everything from hard disks, to cpus, to memory, bufferpools, indexes, foreign keys, etc). Wherever there is a difference is a place to focus your attention.


  7. #7
    Join Date
    Sep 2004
    Maybe there are differences in the foreign keys of derived tables?
    DELETE will have to go through all "child" tables (i.e., tables with a FOREIGN KEY REFERENCES your_table), and even do DELETEs there in case the FK has the CASCADE option set (or do updates there when it is set to SET NULL).
    Those cascaded deletes could have their own child tables being updated, etc.

    So search the catalog:
    SELECT tabschema||'.'||tabname AS child_table, deleterule 
    WHERE   reftabschema = 'SCHEMA_OF_YOUR_TABLE'
      AND   reftabname = 'YOUR_TABLE_NAME'
    and look for differences returned by this query on the two systems.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

Posting Permissions

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