I think I have a problem in my select statement. The problem is not that it
produces to much output or does not work. The problem is that it takes to much
time to give a result. Up to 25 minutes to get the report wanted (see output).
Could anyone give me an indication on how I have to change my select statement
or my script to improve the response time. There is the possibility to work with
cursors but I did not find any documentation so I do not know what is possible.
The scripts is:
Name Line Command
Number
---------- ------
------------------------------------------------------------
Q_STGVOLS2 1 /* -------------------------------------------*/
Q_STGVOLS2 1 /* -------------------------------------------*/
5 /* Script Name: Q_STGVOLS2 */
10 /* Description: Display the number of volumes */
15 /* for each storage pool and the */
20 /* maxscratch value */
25 /* Parameter: none */
30 /* Example: run q_stgvols2 */
35 /* -------------------------------------------*/
40 select stgpool_name, -
45 count(*) as "Nbr Datasets", -
50 sum(est_capacity_mb) as "Capacity" -
55 from volumes -
60 where devclass_name = 'DISK' -
65 group by stgpool_name
70 select a.stgpool_name, -
75 count(*) as "Nbr Tapes", -
80 b.maxscratch, -
85 sum(a.est_capacity_mb) as "Capacity" -
90 from volumes a, stgpools b -
95 where a.devclass_name <> 'DISK' and -
100 a.stgpool_name=b.stgpool_name -
105 group by a.stgpool_name, b.maxscratch
The output is:
Report Start Time: 1:30
Tivoli Storage Manager
Command Line Administrative Interface - Version 4, Release 1, Level 0.0
(C) Copyright IBM Corporation, 1990, 1999, All Rights Reserved.
Session established with server TSMMVS01: MVS
Server Version 3, Release 7, Level 1.0
Server date/time: 03/28/2001 01:27:56 Last access: 03/28/2001 01:27:55
ANS8000I Server command: 'run q_stgvols2'
STGPOOL_NAME Nbr Datasets Capacity
------------------ ------------ ---------------------------------
ARCHIVEPOOL 2 1001.2
ARCHIVEPOOL 2 1001.2
ARCHTEST 1 100.5
BACKUPPOOL 31 12715.2
BCKPTEST 1 500.6
CC_DEV_BUPOOL 6 6003.0
CC_PROD_BUPOOL 8 2752.8
CC_PROJECT_STUFF_- 1 500.6
BUPOOL
IEBUPOOL 12 12006.0
IPPABACKUPPOOL 7 3253.8
PAPBUPOOL 3 1501.8
TESTPOOL 3 1501.8
STGPOOL_NAME Nbr Tapes MAXSCRATCH
Capacity
------------------ ----------- -----------
---------------------------------
ARCTAPPOOL 1 20
ARCTAPPOOL 1 20
9216.0
AXABCOPYPOOL 3 100
27615.7
BUCOPYPOOL 1 50
9216.0
BUMIGPOOL 624 800
525159.4
BUMIGPOOL2 31 150
347562.6
CC_DEV_BUMIGPOOL 33 100
27888.6
CC_PROD_BUMIGPOOL 19 100
16193.9
IEMIGBUPOOL 150 180
126243.1
IPPABUMIGPOOL 18 100
14996.7
PAPMIGPOOL 2 50
1563.8
SAP_BACKUPPOOL 2 150
1440.0
ANS8002I Highest return code was 0.
Report End Time: 1:55
Any help is appriciated. Thanks in advance,
Michel Engels
TSM Administrator
|
