TimeStampDiff is unknown...

ANR2944E The reference 'TIMESTAMPDIFF' is an unknown SQL scalar function name.

                                  |
         .........................V.....................................
         IZED , EST_CAPACITY_MB , TIMESTAMPDIFF ( 16 , CHAR ( current_ti

This is the sql statement I tried:
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

Thank you,
Andy Huebner

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of 
Ankur Patel
Sent: Wednesday, March 07, 2012 7:35 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: 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.
>

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.