Hi David,
You are right : this should be the correct syntax, however TSM 6.2 does not
seem to like it :
select start_time, end_time from summary where entity like upper(%'$1'%)
ANR0162W Supplemental database diagnostic information: -1:42601:-104
([IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "(" was found
following "re entity like upper". Expected tokens may include: "ESCAPE
<escape_char>". SQLSTATE=42601
).
ANR0516E SQL processing for statement select start_time , end_time from summary
where entity like upper ( % '$1' % ) failed.
ANS8001I Return code 3.
As you can see, something is wrong here ...
I also tried all the possible variants with single and double quotes, placed
before the "$1" and after, with no chance :-(
TSM 6.2 is slowly getting on my nerves : interface is miserable, installation
is a nightmare and most of my monitoring queries will have to be reviewed, to
generate more complicated code.
What I'm trying to achieve is to get the equivalent of that one, which
basically provide throughput statistics for migrations :
select start_time, end_time,start_time-end_time as "Elapsed
Time",entity,processes, cast(bytes/1024/1024/1024 as decimal(12,4)) as "GB
copied",((cast(bytes as decimal(18,0)) / cast((end_time -start_time)seconds as
decimal(18,0))))/1048576 "MB/second" from summary where activity='MIGRATION'
and cast((current_timestamp-end_time)minutes as decimal) >0 and cast((end_time
-start_time)seconds as decimal) >0 and entity like upper('%$1%')
So far, my "translation" looks like this :
select start_time, end_time, hour(end_time-start_time)||':'||
minute(end_time-start_time)||':'|| second(end_time-start_time)as "Time
elapased",entity,processes,bytes,cast(bytes/1024/1024/1024 as decimal(12,4)) as
"GB copied",(cast(bytes as decimal(18,0))/timestampdiff(2,cast((end_time
-start_time)as decimal(18,0)))/1048576) as "MB/second" from summary where
activity='MIGRATION' and cast((current_timestamp-end_time) minutes as decimal)
>0 and timestampdiff(2,(end_time-start_time)) >0 and entity like upper('%$1%')
But it's far to be perfect : no double digits for "time elapsed", and no way to
get $1 in the middle of an expression ...
Pretty unsatisfying ...
Cheers.
Arnaud
******************************************************************************
Corporate IT Systems & Datacenter Responsible
Panalpina Management Ltd., Basle, Switzerland,
CIT Department Viadukstrasse 42, P.O. Box 4002 Basel/CH
Phone: +41 (61) 226 11 11, FAX: +41 (61) 226 17 01
Direct: +41 (61) 226 19 78
e-mail: arnaud.brion AT panalpina DOT com
******************************************************************************
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
David Bronder
Sent: jeudi 1 juillet 2010 00:32
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Syntax for SQL "upper" in TSM 6.2
PAC Brion Arnaud wrote:
>
> Like many others here, I'm fighting against the real DB2 world SQL
> syntax, and am totally unable to get a working equivalent of that query
> (simplified for demo purposes) :
>
> select * from summary where entity like upper(%'$1'%)
>
> Did anyone succeed in having such one working in 6.2 ?
I haven't moved to TSM 6.2 yet, but in 5.5 it needs to be upper('%$1%')
(the "%"s need to be inside the quotes)...
--
Hello World. David Bronder - Systems Admin
Segmentation Fault ITS-SPA, Univ. of Iowa
Core dumped, disk trashed, quota filled, soda warm. david-bronder AT uiowa
DOT edu
|