Back on the reporting problem.

Happy New Year All!

With nothing better to do this morning than wonder about this amount backed
up reporting problem, I decided to poke around the scripts I have in
different places. The first script below is supposed to pull "ALL" node
data, yet it only reports some nodes and some of the ones that do report
report with 0.0 MB. I've got an open PMR on this, still waiting for a call
back.

select summary.entity as "NODE NAME", nodes.domain_name as "DOMAIN",
nodes.platform_name as "PLATFORM", cast((cast(sum(summary.bytes) as float) /
1024 / 1024) as decimal(10,2)) as MBYTES , count(*) as "CONNECTIONS", cast
(client_version as char(1)) || '.' || cast (client_release as char(1)) ||
'.' || cast (client_level as char(1)) || '.' || trim(cast(client_sublevel as
char(2))) as "Level" from summary ,nodes where
summary.entity=nodes.node_name and summary.activity='BACKUP' and
start_time>current_timestamp - 1 day group by entity, domain_name,
platform_name, client_version, client_release, client_level, client_sublevel
order by MBytes desc

Now here is one I found that only reports a single nodes data at a time, you
have to specify the nodename when run. I decided to run it on a node that is
reporting 0.0 MB with the first script and loandbehold it reported numbers.
It even reports numbers for nodes that are not even showing up with the
above script. I didn't run it on all the nodes I have but the ones I did all
worked.

The task now is for someone with much more SQL knowledge than me to maybe
figure out why. I'll also pass this on to TSM support once they call me back
on the PMR. I was wondering if it's possible to change the script that works
to report all nodes at once instead of having to plug in each seperately.

Here is the script as saved:

/************************************************/
/* Script that reports all the objects that are */
/* backed up for a node X                       */
/* The X parameter that has to be given is the  */
/* nodename                                     */
/************************************************/
set sqlmathmode round
set sqldatetimeformat usa
select substr(cast(avg(end_time-start_time) as varchar(17)),3,8) as elapsed,
-
    cast(avg(affected) as decimal(7,0)) as "Avg Obj", -
  case -
    when avg(bytes) > 1073741824 then -
         cast(cast(avg(bytes)/1073741824 as decimal(7,3)) as varchar(24))||'
Gb' -
    when avg(bytes) > 1048576 then -
         cast(cast(avg(bytes)/1048576 as decimal(7,3)) as varchar(24))||'
Mb' -
    when avg(bytes) > 1024 then -
         cast(cast(avg(bytes)/1024 as decimal(7,3)) as varchar(24))||' Kb' -
    else cast(cast(avg(bytes) as decimal(7,3)) as varchar(24)) -
  end as "Avg Bytes" -
  from summary -
  where activity='BACKUP' and -
     entity=upper('$1') and -
     successful='YES'

Geoff Gill
TSM Administrator
NT Systems Support Engineer
SAIC
E-Mail:    <mailto:gillg AT saic DOT com> gillg AT saic DOT com
Phone:  (858) 826-4062
Pager:   (877) 905-7154