|
View previous topic :: View next topic
|
| Author |
Message |
tsjpraveen_mainframe
New User
Joined: 31 Oct 2008
Posts: 2
Location: Chennai
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
tsjpraveen_mainframe
New User
Joined: 31 Oct 2008
Posts: 2
Location: Chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
*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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
