Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DELETE Query Takes Too Much Time to execute from Batch..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ManojPrabakaran

New User


Joined: 21 Jan 2009
Posts: 6
Location: Bangaloe

PostPosted: Mon Feb 22, 2010 3:07 pm    Post subject: DELETE Query Takes Too Much Time to execute from Batch..
Reply with quote

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
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Feb 22, 2010 3:10 pm    Post subject:
Reply with quote

most likely a child table without a decent index.
Back to top
View user's profile Send private message
ManojPrabakaran

New User


Joined: 21 Jan 2009
Posts: 6
Location: Bangaloe

PostPosted: Mon Feb 22, 2010 3:34 pm    Post subject: Reply to: DELETE Query Takes Too Much Time to execute from B
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Feb 22, 2010 3:48 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10278
Location: italy

PostPosted: Mon Feb 22, 2010 3:55 pm    Post subject: Reply to: DELETE Query Takes Too Much Time to execute from B
Reply with quote

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
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Mon Feb 22, 2010 4:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Feb 22, 2010 4:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ManojPrabakaran

New User


Joined: 21 Jan 2009
Posts: 6
Location: Bangaloe

PostPosted: Mon Feb 22, 2010 4:37 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Feb 22, 2010 5:33 pm    Post subject:
Reply with quote

http://www.mainframesupport.dk/tips/tip0414.html
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Mon Feb 22, 2010 5:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Unable to copy data from spool in a d... srikant314 JCL & VSAM 2 Fri Jul 28, 2017 9:10 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts DSNACCOX (can it be run on 1 db/ts, t... SRICOBSAS DB2 5 Sat May 06, 2017 12:59 am
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us