I was planning on writing this script in Java, as I have some practice writing in this language. I have written a program before that was able to access a MySQL database, but that required a JDBC driver that worked with MySQL....is there anything similar for TSM?
Mel Dennis -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Prather, Wanda Sent: Thursday, November 03, 2005 3:32 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] SQL query to find expiring archive files 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:[EMAIL PROTECTED] On Behalf Of Dennis Melburn W IT743 Sent: Thursday, October 27, 2005 2:35 PM To: ADSM-L@VM.MARIST.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
