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:[email protected]] On Behalf Of Ankur
Patel
Sent: Wednesday, March 07, 2012 7:35 PM
To: [email protected]
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:[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.
>
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.
