Re: [ADSM-L] TSM 6.2 SQL statement

Andy,

Try this:

select SUBSTR(CAST(VOLUME_NAME AS char(10)),1,10) as VOLUME_NAME,
SUBSTR(CAST(stgpool_name AS char(18)),1,18) as STG, PCT_UTILIZED,
EST_CAPACITY_MB,
TIMESTAMPDIFF(16,CHAR(current_timestamp-date(last_write_date))) as xdays,
SUBSTR(CAST(status AS char(10)),1,10) as status, SUBSTR(CAST(access AS
char(10)),1,10) as access, date(last_write_date) as "Last_Write"  from
volumes where status = 'FILLING' and access != 'UNAVAILABLE' and access =
'OFFSITE' and
TIMESTAMPDIFF(16,CHAR(current_timestamp-date(last_write_date))) \> 60 order
by last_write_date

regards,

Ankur Patel


> -----Original Message-----
> From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf 
> Of
> Huebner,Andy,FORT WORTH,IT
> Sent: Thursday, 8 March 2012 8:40 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: [ADSM-L] TSM 6.2 SQL statement
>
> We run this and other SQL statements that return dates in the form of
> days.  This SQL query works on 5.4, but does not work on 6.2.  I am hoping
> one of the more SQL savvy admins can quickly help me with this so I can fix
> the rest of the queries we use.
>
> The lines that don't work and I do not know how to translate are:
>
> (current_timestamp - last_write_date)days as "Days"
> and cast((current_timestamp - last_write_date)days as decimal(6,0)) \> 60
>  \
>
> select volume_name, \
>                   stgpool_name, PCT_UTILIZED, EST_CAPACITY_MB, \
>                   (current_timestamp - last_write_date)days as "Days", \
>                   status, access, last_write_date,  \
>                   cast ((last_write_date) as date) as "Last Write"  \
>                   from volumes \
>                   where status = 'FILLING' \
>                   and access != 'UNAVAILABLE' and access = 'OFFSITE' \
>                   and cast((current_timestamp - last_write_date)days as
> decimal(6,0)) \> 60  \
>                   order by last_write_date
>
>
> Thanks,
>
> Andy Huebner
>
> This e-mail (including any attachments) is confidential and may be legally
> privileged. If you are not an intended recipient or an authorized
> representative of an intended recipient, you are prohibited from using,
> copying or distributing the information in this e-mail or its attachments.
> If you have received this e-mail in error, please notify the sender
> immediately by return e-mail and delete all copies of this message and any
> attachments.
>
> Thank you.
>