Hi,

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?

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

hi,
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.

hmmm...
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?

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))
LANGUAGE SQL
---------------------------------
-- SQL Stored Procedure truncate
---------------------------------
BEGIN

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

IF sch_name IS NULL
THEN
SET full_name = tab_name;

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

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

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

END IF;

IF UCASE(a) = UCASE(tab_name)
THEN
-- 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;

ELSE

-- Table does not exists.

END IF;

END @

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.

Andy

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:
and look for differences returned by this query on the two systems.