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
 

 

Best option/method for mass Delete from DB2 table

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

New User


Joined: 31 Oct 2008
Posts: 2
Location: Chennai

PostPosted: Tue Mar 24, 2009 6:59 pm    Post subject: Best option/method for mass Delete from DB2 table
Reply with quote

Hi,

I am currently using a DB2 program to delete a few lakh of records from a DB2 table using the below query

Code:
DELETE * FROM <TABLE NAME> WHERE <COL NAME> NOT IN ('<ABC>', '<XYZ>')


Is there is any other best/optimized way to do the same operation? like using a DB2 utility instead of program?

Also, my program is frequently abended due to -911 deadlock/timeout error as the same table is accessed by Online screens for updating.

Hence, what is the best way to handle above scenario?

Note: I have not used COMMIT in my program as it is a mass delete.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Mar 24, 2009 7:16 pm    Post subject:
Reply with quote

I have always been a believer in cursors.
especially when so many updates (a delete is an update) are involved.
Until a commit is performed (either explicitly or the rookie method of program end),
all those deleted rows are maintained by DB2 in the event of rollback.
that is quite a systems resource hog.

and why are you doing mass deletes during online processing??????
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Mar 24, 2009 7:19 pm    Post subject:
Reply with quote

one way to continue the 'during the day process':

1. open a read only cursor.
2. do singleton deletes
3. COMMIT frequently.
Back to top
View user's profile Send private message
tsjpraveen_mainframe

New User


Joined: 31 Oct 2008
Posts: 2
Location: Chennai

PostPosted: Tue Mar 24, 2009 7:25 pm    Post subject:
Reply with quote

Thanks dbz for quick reply..

We are in need to delete those bulk records frequently, hence whether online system working or not, gets lesser priority than deleting the records...

Cant we use any utility to make delete statement faster...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Mar 24, 2009 7:35 pm    Post subject:
Reply with quote

*sigh*

you have problems with contention and volume. these are db2 problems.
nothing is going to do this faster without removing the contention.
do you really want to impact the online?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Mar 24, 2009 7:40 pm    Post subject:
Reply with quote

you could make the delete process several jobs.
each spread over the primary key,
'split the table into sections' by range.

I also think that you will be surprised
at how fast a cursor with singleton deletes and appropriate commits will run.

Besides, FASTER is a relative term. I work for large banks and insurance companies. they can afford to have mainframes that will process much faster than small mainframes (or overloaded).
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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Mass comment and edit elixir1986 IBM Tools 3 Thu Mar 02, 2017 10:09 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


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