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:[email protected]] On Behalf Of > Huebner,Andy,FORT WORTH,IT > Sent: Thursday, 8 March 2012 8:40 AM > To: [email protected] > 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. >
