How good is the ADSM sql optimizer?  It should make no difference, but I'd try
removing the subselect e.g.

Select distinct a.Volume_name
from contents a, contents b
where a.file_name = b.file_name
  and b.volume_name='DJ0139'

If the optimiser is really stupid (and I have no information one way or the
other) it might be better to reverse the order of the where clause too.



Steve



Steve Harris

AIX/ADSM/Oracle/HACMP Guy

The Wesley Hospital, Brisbane Australia









Jonathan Lepage <Jonathan.Lepage AT FID.DESJARDINS DOT COM> on 24/12/99 05:27:19

Please respond to "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>








 To:      ADSM-L AT VM.MARIST DOT EDU

 cc:      (bcc: Steve Harris/Wesley)



 Subject: Copy pool Reclaim



Fax to:




Hi ADSMers and TSMers,

     IS there a way I could gnerate a report that would tell me what Tape to
mount for the reclaim process of my copy pool... Since I have only two 3590
drives (10 slot each) I cannot launch automatic reclamation.  I mounting tapes
at prompt can be a pain ( espacialy since we dont have an 24/7 operator).  I
tried to run the following SQL query but it takes lotta time to compute (after 3
hours, I had to end it because it impacted on production) :

Select distinct Volume_name
from contents a
where a.file_name in
     ( select file_name
        from contents b
        where volume_name='DJ0139')

I knew this would take a lot of time to compute... But more than 3 hours at 85%
of CPU on a AS/400 640 , I was surprised!


..





...