Many of the sites that I manage have asked to recieve a quarterly report
showing a list of archive files that are going to expire within the next
year. I was able to design an SQL query that does this, but my problem
is that it takes a really long time. Is there some other way to go
about doing this that I am not aware of? Here is the sql query:
select -
archives.node_name as "NODE_NAME",-
archives.filespace_name as "FILESPACE_NAME",-
archives.type as "TYPE",-
archives.hl_name as "HL_NAME",-
archives.ll_name as "LL_NAME",-
substr(char(archives.archive_date), 1, 10) as "ARCHIVE_DATE",-
case -
when ar_copygroups.retver = 'NOLIMIT' then
substr(char(archives.archive_date + 100 years), 1, 10) -
else substr(char(archives.archive_date + ar_copygroups.retver days),
1, 10) -
end as "EXPIRATION_DATE" -
from archives, nodes, domains, ar_copygroups -
where archives.node_name = nodes.node_name -
and nodes.domain_name = domains.domain_name -
and domains.defmgmtclass = ar_copygroups.class_name -
and ar_copygroups.set_name = 'ACTIVE' -
and days(date(case -
when ar_copygroups.retver = 'NOLIMIT' then
substr(char(archives.archive_date + 100 years), 1, 10) -
else substr(char(archives.archive_date + ar_copygroups.retver
days), 1, 10) -
end)) - days(date(archives.archive_date)) <= 365
BTW, I ran this query against one of my smaller sites that only has
about 12 GB of data archived, and it took about 22 minutes. At my main
site, I have multiple TSM backup servers that have well into the 3-4 TB
range. As you can see, this would take about 5 days to finish at the
same rate!
Mel
