Deleting large numbers of rows quickly

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
tennis
Member
Posts: 1
Joined: Tue Nov 14, 2006 6:54 pm

Deleting large numbers of rows quickly

Post by tennis » Tue Nov 14, 2006 7:21 pm

We do some fairly heavy transaction processing. We have some important tables we desire to keep as small as possible by deleting out transactions that are successfully processed. We have a batch process that runs against the table every day and marks transactions as processed by changing a status. We then can delete these rows as time permits. It can take a long time.

Are 'table-controlled partitions' using a status column (perhaps 10 possible values in the domain) appropriate? I see a fair amount on-line of how to create these partitions, but not a lot on why or when to use them. They seem to be just the thing - partition based upon the status column, and every so often drop the partition having the 'processed' status and create a new one.

We're using UDB on an AIX box.

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

Post by Kalicharan » Thu Nov 16, 2006 12:00 pm

Hi,

Partitioning strategy is good for that case.

In DB2, we can truncate partition alone. No need to drop the partition and add it again.
alter table table_name truncate partition partition_name;

Look whether this can be done in UDB as well.

There is no disadvantages because of partitioning apart of a little bit overhead on updates
which is negligible (as per my knowledge).
In this particular case, I find no disadvantages to go with partitioning approach.
But all the indexes on that table should be global indexes only.
If you create local indexes on those tables, there can be performance degradation.

My views are based on DB2. But as per my knowledge implementation should be same in UDB as well.

Thanks
Kalicharan

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others