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.
Deleting large numbers of rows quickly
Moderators: Kalicharan, Moderator Group
- Kalicharan
- Moderator
- Posts: 31
- Joined: Wed Feb 08, 2006 1:51 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
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
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page