|
View previous topic :: View next topic
|
| Author |
Message |
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
Help me in finding the Efficient Solution to purge a DB2 Table.
- Millions of records
- Records inserted real time (can not lock that table)
- Maintain 2 Yrs of data
- put in place a scheduled process to purge data monthly.
- Not indexed on TimeStamp.
Planning :
Create a job which will delete the data inserted more than 2 Years.
Thanks in Advance. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How many rows will be deleted each month? |
|
| Back to top |
|
|
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
Hello
Thanks for replying...
There is no exact count, but that will be in millions.
Currently it has 5 Years of data.
Year Counts
2008 863404
2009 9812490
2010 31300237
2011 25441616
2012 28690033
2013 6939013 (Till April)
Regards,
GK |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you schedule a maintenance window some weekend, unload the entire table, delete the data that is no longer needed, and reload the data to be retained. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| dick scherrer wrote: |
| Suggest you schedule a maintenance window some weekend, unload the entire table, deleted the data that is no longer needed, and reload the data to be retained. |
| GauravKudesiya wrote: |
| Records inserted real time (can not lock that table) |
This is probably one of those cases where an app using this table absolutely, positively, has to be available 24x7x365 or it'll be TEOLAWKI 
If so, Gaurav-kun, you'll have to work with your DBAs and sysprogs to create a mirror table, apply the ETL to the mirror, and then swap the tables. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Howdy,
Yup, i saw that, but even some of those systems concede some time periodically. . .
We'll see.
d |
|
| Back to top |
|
|
GauravKudesiya
Warnings : 1
New User
Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL
|
|
|
|
I was thinking to fetch 1000 rows and delete and while declare the cursor i can use CS as UR.
will this approach impact anything.. i ma not sure.. 
and may be i can delete 6 month data in 1 run by passing the date from JCL and using that date in where condition.
Please advice
Regards,
GK |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| REORG TABLESPACE DISCARD FROM TABLE ... WHEN column < CURRENT DATE - 2 YEARS |
| Quote: |
| You can specify any SHRLEVEL option with DISCARD. However, if you specify SHRLEVEL CHANGE, modifications that are made during the reorganization to data rows that match the discard criteria are not permitted. In this case, REORG TABLESPACE terminates with an error. |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
Dhrubojoty mukherjee
New User
Joined: 23 Mar 2010
Posts: 14
Location: kolkata
|
|
|
|
This is really a problem. The reorg option is the best solution but before doing that it should be confirmed that whether there are no lock available to that table and then only the Reorg operation can be done.Apert from this, after this the statistics needed to be build and it should be started because with out this no one can access the data.
Besically for such a kind of data if the table space is partitioned it would be the best thing to access the data and as well as to maintain. You can define the part key as the months and delete the desired partition using any application program. And also the Reorg operation could be done on partition level of a table space. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
@Dhrubojoty mukherjee : OR I completely misunderstand your post OR you have no idea what you are talking about.
1)
Reorg can run in SHRLEVEL CHANGE !!
Reorg can gather statistics during reorg.
2)
Implementing a rotating partition system is a bit more complex than what the OP asked for. and pretty difficult when "Not indexed on TimeStamp" |
|
| Back to top |
|
|
Dhrubojoty mukherjee
New User
Joined: 23 Mar 2010
Posts: 14
Location: kolkata
|
|
|
|
| Thanks for ur clarification..I missed out the share level...but it is always better to use partitioned tablespace where the data is huge and also it is increasing... |
|
| Back to top |
|
|
ajayhegdemainframe
New User
Joined: 05 Jun 2013
Posts: 5
Location: Mumbai, India
|
|
|
|
Slight change in dick scherrer approach
1.Schedule a maintenance window some weekend
2.Unload the entire table
3.Apply a sort on unloaded file to get records those eligible to be deleted
4.Delete records using file created in step 3 using cobol-db2 program make sure to use table indexed key in the where clause
Hope it helps.
Thanks |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If the rows to be deleted number in the millions, i suspect (predict?) the individual DELETEs will not perform acceptably |
|
| Back to top |
|
|
ajayhegdemainframe
New User
Joined: 05 Jun 2013
Posts: 5
Location: Mumbai, India
|
|
|
|
Agreed, there could be performance issue from performance/cost wise best way as advised by you is to perform reload using a db2 utility
Here is recent execution stats that could see from JHS where similar purge process (cobol-db2 program) deleted aprox 4 million records TOTAL CPU TIME= 36.85 TOTAL ELAPSED TIME= 557.1 |
|
| Back to top |
|
|
|
|
