ADSM-L

Re: SQL query to find expiring archive files

2005-11-03 15:32:47
Subject: Re: SQL query to find expiring archive files
From: "Prather, Wanda" <Wanda.Prather AT JHUAPL DOT EDU>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Thu, 3 Nov 2005 15:32:29 -0500
I don't know if this will help you at all, but when I had to do
something similar using the backups table, I found that I could do much
better by driving the query from a higher-level script (perl, for
example). 

The archives (and backups) tables are the biggest and ugliest tables you
can go after.
They are indexed on NODE_NAME (and some other fields).  

In a host languae script you can first query to get all the node names,
then run the query to get your info repetively, one node_name at a time,
writing the output to a file. 

While it is more trouble to write, doing one node at a time may run a
lot faster overall than running the query against the entire archives
table without using an index.

I don't know in your case whether the impact of the join and the CASE
statement will be such that it negates the benefit of using the indexes.
That's just the only suggestion I have to offer.

Wanda Prather
"I/O, I/O, It's all about I/O"  -(me)


-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Dennis Melburn W IT743
Sent: Thursday, October 27, 2005 2:35 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL query to find expiring archive files


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

<Prev in Thread] Current Thread [Next in Thread>