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