|
View previous topic :: View next topic
|
| Author |
Message |
ManojPrabakaran
New User
Joined: 21 Jan 2009
Posts: 6
Location: Bangaloe
|
|
|
|
Hi
Submitting the Delete Query From the Batch Takes too much time to execute
DELETE FROM TABLECREATOR.TABLENAME
WHERE CREATE_TIMESTAMP < CURRENT TIMESTAMP - 400 DAYS;
COMMIT;
What May be the reason Behind this...?
Thanks ~ Manoj |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| most likely a child table without a decent index. |
|
| Back to top |
|
|
ManojPrabakaran
New User
Joined: 21 Jan 2009
Posts: 6
Location: Bangaloe
|
|
|
|
But The table name has an Index created and I can see that in SYSIBM.SYSINDEXES table with all the Values specified.
Also An Entry In the SYSIBM.SYSINDEXPART |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
definition of child table : Another table with has a referential integrity defined to this table.
| Code: |
| select * from sysibm.sysrels where reftbcreator = 'TABLECREATOR' and reftbname = 'TABLENAME' |
on that table an index should exists on the columns named in sysforeignkeys |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| DELETE Query Takes Too Much Time to execute from Batch.. |
how many rows are You deleting??
processing a large number of updates is really a resource hog! from any point of view,
You will have the logs, live and archive full of deleted rows!
and incur the usual processing issues for recoverable resources...
and strange enough, I remember reading somewhere that for deletes the <time> is not really linear ( at least for large numbers of deletes)
there are some things to be aware anyway,
for mass updates You need to evaluate the <convenience> point
i.e what is the limit for doing the updates <online> with dbms calls
or doing them <offline> with unload, cleanup, reload
I will not suggest for Your environment the percentage of hit counts where You should change approach,
only that you must be aware of the issue
From my experience for more than 30% of hits the <offline> approach is a must!
sometimes even less is advisable |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
The DBAs at my installation recommends these bulk deletes, like the one you supposedly are trying to execute, done as unloading the table with a subsequent load of the rows that you want to keep.
In this case only, rows with CREATE_TIMESTAMP >= CURRENT TIMESTAMP - 400 DAYS should be loaded into the table again.
The point in doing it like this is the you probably will have to reorganise the tablespace, and update stats when you delete a large portion of the table. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| As this is not a clear matching DELETE, it is bound to take time. You can use REORG with DISCARD option (where you specify the condition) . This is much faster than normal deletes and would be a 2 in 1 deal (DELETE and REORG). |
|
| Back to top |
|
|
ManojPrabakaran
New User
Joined: 21 Jan 2009
Posts: 6
Location: Bangaloe
|
|
|
|
Hi All
Thanks All For the Reply..
My intention is to reduce the time and this query is executed through BATCH and the Job will look Like as follows.
System - DB2P and Subsystem -- DDB2P.
//DELREP EXEC DSNTIAD0,SYSTEM=DB2P
DELETE FROM DDB2P.TXXXREPO
WHERE CREATE_TIMESTAMP < CURRENT TIMESTAMP - 400 DAYS;
COMMIT;
//* ------------------------------------------------------
//*
//DELCNT EXEC DSNTIAD0,SYSTEM=DB2P
DELETE FROM DDB2P.TXXXCNTL
WHERE CREATE_TIMESTAMP < CURRENT TIMESTAMP - 60 DAYS;
COMMIT;
//* ------------------------------------------------------
//DELDAT EXEC DSNTIAD0,SYSTEM=DB2P
DELETE FROM DDB2P.TXXXDATA
WHERE CREATE_TIMESTAMP < CURRENT TIMESTAMP - 60 DAYS;
COMMIT;
GuyC
I Executed the Query that you suggested and there is no entry for this tables in the SYSRELS table.
Enrico
The rows are Very Limited. The count in thousands only. Mostly Less than 5000.
KJeld
Every day the REORG AND STATS Job is running for this Table.
Ashimer
Can U Explain little elaborate
Let me know if you have any concerns.
Thanks ~ manoj |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| Can U Explain little elaborate |
This is an additional option in REORG where in the REORG card you can mention the selection criteria for the rows which are to be qualified for deletion. Which utility are you using for REORG? |
|
| Back to top |
|
|
|
|
