A suggestion:

I always forget (or mistype) that stgpool_name has to be upper case.
You can make the script bulletproof by using the UPPER function in the WHERE
clause:

... from volumes where stgpool_name=upper('$1') and...

That works whether you type in upper or lower...

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Mark D. Rodriguez
Sent: Tuesday, August 31, 2004 9:05 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Select for access date


Geoffrey,

You asked for access time which really isn't tracked but both read and
write times are so I used them.

Ok this is sort of a long one.  It is all one line so if you put it into
a Admin Script keep that in mind.  Also, I am giving it to you as if it
where going to be a script where you pass it parms, i.e. $1 and $2.
Here it is:

select volume_name, cast(last_write_date as date) as "Last Write Date",
cast(last_read_date as date) as "Last Read Date" from volumes where
stgpool_name='$1' and cast(last_write_date as date)<(current_date-180
days) and cast(last_read_date as date)<(current_date-180 days) order by $2

$1 will be the storage pool name, remember all caps
$2 will be the column you want to sort by

Good luck I hope this works for you.

Gill, Geoffrey L. wrote:

>I'm wondering if anyone has a select or query that can be tailored for a
>specific tape pool that will show me last access date of the tape. I have
>quite a few archive tapes I want to start getting out of the library, to
>make room for scratch, and would like to start by removing tapes from that
>pool that have not been accessed for at least 6 months. Or is that even
>available?
>
>
>
>Thanks,
>
>
>
>Geoff Gill
>TSM Administrator
>NT Systems Support Engineer
>SAIC
>E-Mail:   gillg AT saic DOT com
>Phone:  (858) 826-4062
>Pager:   (877) 854-0975
>
>
>

--
Regards,
Mark D. Rodriguez
President MDR Consulting, Inc.

============================================================================
===
MDR Consulting
The very best in Technical Training and Consulting.
IBM Advanced Business Partner
SAIR Linux and GNU Authorized Center for Education
IBM Certified Advanced Technical Expert, CATE
AIX Support and Performance Tuning, RS6000 SP, TSM/ADSM and Linux
Red Hat Certified Engineer, RHCE
============================================================================
===