archive expire dates

nbritton2015

Newcomer
Joined
Oct 31, 2015
Messages
2
Reaction score
0
Points
0
PREDATAR Control23

I need to report out the expiration date of files archived as part of a project to move some data from tsm to a customer.

They want the data in folders by expiration date.

This is how i thought the query should work, but i am having trouble with the dateadd part. Can someone help me out please?

SELECT a.node_name, a.class_name, a.filespace_name, a.hl_name, a.ll_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(ao.bfsize) as number_of_objects, ac.RETVER, DATEADD(dd,ac.RETVER,DATE(a.archive_date)) AS Expire

FROM archives a, archive_objects ao, ar_copygroups ac, nodes n

WHERE a.object_id=ao.objid and a.node_name='GPETL001' and a.class_name=ac.class_name and ac.domain_name = n.domain_name

GROUP BY a.node_name, a.class_name, DATE(a.archive_date), a.filespace_name, a.hl_name, a.ll_name, ac.RETVER
 
PREDATAR Control23

Sorry, it's over my SQL skills. I just want to add that you better be prepared for this query to run for several hours (if not days).

How many different management classes do you have for archive? If you only have a handful, it may be easier to do something like:

Let's assume MC1 has a retention of 90 days:
select node_name,class_name,filespace_name,hl_name,ll_name, date(archive_date), date(archive_date+90) as expire from archives where class_name='MC1'

Repeat for your other management classes. You'd have to factor in the domain too.
 
Top