Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to Purge a DB2 Table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Wed Nov 06, 2013 11:45 pm    Post subject: Need to Purge a DB2 Table
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 07, 2013 12:22 am    Post subject:
Reply with quote

Hello,

How many rows will be deleted each month?
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 07, 2013 12:29 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 07, 2013 1:41 am    Post subject:
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Nov 07, 2013 2:16 am    Post subject:
Reply with quote

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 icon_razz.gif

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 07, 2013 2:26 am    Post subject:
Reply with quote

Howdy,

Yup, i saw that, but even some of those systems concede some time periodically. . .

We'll see.

d
Back to top
View user's profile Send private message
GauravKudesiya
Warnings : 1

New User


Joined: 11 Oct 2008
Posts: 74
Location: Chicago, IL

PostPosted: Thu Nov 07, 2013 3:17 am    Post subject:
Reply with quote

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.. icon_sad.gif

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 07, 2013 12:26 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 07, 2013 12:40 pm    Post subject:
Reply with quote

<=== Criteria after WHEN should be enclosed in ( )

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.ugref/src/tpc/db2z_reorgtablespacesyntax.htm
Back to top
View user's profile Send private message
Dhrubojoty mukherjee

New User


Joined: 23 Mar 2010
Posts: 14
Location: kolkata

PostPosted: Thu Nov 14, 2013 9:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Nov 14, 2013 9:54 pm    Post subject:
Reply with quote

@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
View user's profile Send private message
Dhrubojoty mukherjee

New User


Joined: 23 Mar 2010
Posts: 14
Location: kolkata

PostPosted: Fri Nov 15, 2013 12:25 am    Post subject:
Reply with quote

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
View user's profile Send private message
ajayhegdemainframe

New User


Joined: 05 Jun 2013
Posts: 5
Location: Mumbai, India

PostPosted: Fri Nov 15, 2013 4:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Nov 15, 2013 7:35 pm    Post subject:
Reply with quote

Hello,

If the rows to be deleted number in the millions, i suspect (predict?) the individual DELETEs will not perform acceptably
Back to top
View user's profile Send private message
ajayhegdemainframe

New User


Joined: 05 Jun 2013
Posts: 5
Location: Mumbai, India

PostPosted: Fri Nov 15, 2013 9:07 pm    Post subject:
Reply with quote

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
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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us