This works on 18.104.22.168:
cast(day(current_timestamp-last_write_date) as decimal) as "Days",
status,access,last_write_date,substr(char(last_write_date),1,19) as "Last
Write" from volumes
where status='FILLING' and access!='UNAVAILABLE' and access='OFFSITE'
and 60 < cast(day(current_timestamp-last_write_date) as decimal)
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Sent: Wednesday, March 07, 2012 1:40 PM
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
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.