Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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: 6968
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: 6968
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: 6968
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Delete IMS remote transaction when MO... Sam Singh IMS DB/DC 0 Wed Aug 23, 2017 9:12 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us