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
I had always wondered if this could be done. I played with it until I
figured it out. It was just a matter of looking at it from a group by point
of view. The only problem is it does not fit across the screen.
Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180
-----Original Message-----
From: Gill, Geoffrey L. [mailto:GEOFFREY.L.GILL AT SAIC DOT COM]
Sent: Wednesday, December 11, 2002 10:56 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Select Statement
There are so many replies to this that look like contradictions to each
other that I'd like to look at this further on my system. I am not a SQL
guru by any means, I can't even seem to combine a couple of statements to
get what I need. Hopefully someone can help me(us) out here. I do an audit
license daily so this should come back with good, or in this case, bad
results.
When I try to combine these to get an output I always get a statement error.
I want to find out what levels are having the problem. I think it's
ridiculous that this does not work for "ALL" client levels. It always did in
the past and I can't understand why I have to now upgrade possibly 80 some
nodes to fix it. Why doesn't IBM fix their server code instead?
Could someone please combine these to get the necessary output? Client
level, node name, amount backed up and I think group by client level might
work.
Select for client level:
select node_name,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 nodes order by
|| 2,1
Select for amount backed up:
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" 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 order by MBytes desc
Geoff Gill
TSM Administrator
NT Systems Support Engineer
SAIC
E-Mail: gillg AT saic DOT com
Phone: (858) 826-4062
Pager: (877) 905-7154
> -----Original Message-----
> From: Michel Engels [mailto:Michel.Engels AT DEVOTEAM DOT BE]
> Sent: Wednesday, December 11, 2002 6:22 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: Select Statement
>
>
> When I do remember well this problem appeared when I installed TSM
> Clients 5.1.0. There was a maintenance (I do not remember if it was
> 5.1.2 or 5.1.3) that solved the problem. Be sure you do an "audit
> license" regularly. I did not install a V5.1.5 yet, so I do not know
> if the problem reappears in that version.
>
> Hope this helps,
>
> Michel Engels
> Software Consultant
> Devoteam Belgium
>
>
>
>
>
>
> "Gill, Geoffrey L." <GEOFFREY.L.GILL AT SAIC DOT COM> on 12/10/2002 11:16:02
> PM
>
> Please respond to "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
>
> To: ADSM-L AT VM.MARIST DOT EDU
> cc: (bcc: Michel Engels/BE/Devoteam)
>
> Subject: Select Statement
>
>
>
> I got this somewhere and I know it used to work on my 4x server. Now
> that I'm on 5.1.5.2 I get a lot of nodes report back with 0 Megabytes
> but they obviously sent files.
>
> Anyone who can make it work since my SQL stills are pretty much non
> existant?
>
> /*--------------------------------------------*/
> /* Specify a date on the run line as follows */
> /* run q_backup 2001-09-30 */
> /*--------------------------------------------*/
> select entity as node_name, date(start_time) as date, - cast(activity
> as varchar(10)) as activity, time(start_time)as start, -
> time(end_time) as end, cast(bytes/1000000 as decimal(6,0))as
> megabytes, - cast(affected as decimal(7,0)) as files, successful from
> summary - where date(start_time)='$1' and activity='BACKUP' order by
> megabytes desc
>
> NODE_NAME: NODEA
> DATE: 2002-12-09
> ACTIVITY: BACKUP
> START: 20:00:51
> END: 20:48:07
> MEGABYTES: 0
> FILES: 100
> SUCCESSFUL: YES
>
>
>
> This one does the same thing with some nodes. It also only reports
> about half the nodes even though it looks like it's supposed to be
> going back a full day.
>
> NODEA NT_DOM WinNT
> 0.00
> 1
> 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 "CONECTIONS" 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 order by MBytes desc
>
> A better one would also work.
> 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
>
|
