Hmm, Mark
Sounds like a bug.
I have not run this recently but Larry is at the same TSM as me and  his ran ok
My sql when I did run it under 3.1 was
/* NUMBER OF TAPES USED BY NODE */
/* NB. THIS IS LONG RUNNING, TAKES 40 MINS. HIGH CPU COMMAND */
SELECT NODE_NAME,COUNT(DISTINCT VOLUME_NAME) AS "# OF TAPES" -
  FROM VOLUMEUSAGE -
  GROUP BY NODE_NAME

So you see I took roughly the same time as you under 3.1
However I would have thought yours should have been slightly quicker as you are
specifying
a tape storage pool.
Maybe you are cpu constrained.
Check your RMF stats covering the period.
John




Mark Brown <mark.brown AT MCGILL DOT CA> on 11/02/2000 04:31:34 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: John Naylor/HAV/SSE)
Subject:  Re: Really bad performance for a sql query after upgrading to v 3
      .7



To answer your questions, adsm database is as follows:

Available Assigned   Maximum   Maximum    Page     Total      Used
Pct  Max.
    Space Capacity Extension Reduction    Size    Usable     Pages
Util   Pct
     (MB)     (MB)      (MB)      (MB) (bytes)
Pages                  Util
--------- -------- --------- --------- ------- --------- --------- -----
-----
-----
   11,620   11,620         0     2,648   4,096 2,974,720 2,296,359
   11,620   11,620         0     2,648   4,096 2,974,720 2,296,359
77.2  77.2


I also run the process as a batch job.

Mark

John Naylor wrote:
>
> Mark,
> Couple of suggestions.
> 1) How full is your database, sql queries use free space in the database as a
> work area.
> 2) Run as a batch job, then you can just cancel the session if it is using too
> much resource, and or taking too long.
>
> Mark Brown <mark.brown AT MCGILL DOT CA> on 11/02/2000 02:45:05 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: John Naylor/HAV/SSE)
> Subject:  Really bad performance for a sql query after upgrading to v 3.7
>
> Hello,
>
> We are currently running TSM Version 3, Release 7, Level 3.6 on OS/390.
> We upgraded from version 3.1. On the 3.1 version I used to run a select
> command to show me how many tapes each node was using. Here is the
> select
> command:
>
> select node_name, count (distinct volume_name) as Numvols from
> volumeusage -
> where stgpool_name='TAPEMCO' group by node_name order by Numvols desc >
> tapem.txt
>
> This select used to take about 45 minutes to run on the 3.1 version but
> now this
> select command simply grinds the server to a halt. I eventually have to
> cancel adsm
> to recover the server. Is there something about the 3.7 version or my
> select command
> that would cause this kind of problem. If the command takes 2 hours to
> run then that
> is fine but I can't have the server slowed down so much that I cannot do
> other things.
>
> --
> Mark Brown
> Operations Supervisor
> =====================
>
> Tel      ---> 514-398-2321
> Fax      ---> 514-398-6876
> E-mail   ---> Mark.Brown AT mcgill DOT ca
>
> **********************************************************************
> The information in this E-Mail is confidential and may be legally
> privileged. It may not represent the views of Scottish and Southern
> Energy plc.
> It is intended solely for the addressees. Access to this E-Mail by
> anyone else is unauthorised. If you are not the intended recipient,
> any disclosure, copying, distribution or any action taken or omitted
> to be taken in reliance on it, is prohibited and may be unlawful.
> Any unauthorised recipient should advise the sender immediately of
> the error in transmission.
>
> Scottish Hydro-Electric and Southern Electric are trading names of
> Scottish and Southern Energy Group.
> **********************************************************************

--
Mark Brown
Mark Brown
Operations Supervisor
=====================

Tel      ---> 514-398-2321
Fax      ---> 514-398-6876
E-mail   ---> Mark.Brown AT mcgill DOT ca






**********************************************************************
The information in this E-Mail is confidential and may be legally
privileged. It may not represent the views of Scottish and Southern
Energy plc.
It is intended solely for the addressees. Access to this E-Mail by
anyone else is unauthorised. If you are not the intended recipient,
any disclosure, copying, distribution or any action taken or omitted
to be taken in reliance on it, is prohibited and may be unlawful.
Any unauthorised recipient should advise the sender immediately of
the error in transmission.

Scottish Hydro-Electric and Southern Electric are trading names of
Scottish and Southern Energy Group.
**********************************************************************