When you do the SELECT that Richard suggests on SYSCAT.COLUMNS, you will
see that some columns have the field INDEX-KEYSEQ.
This field indicates how the ARCHIVES table is indexed.  

Like most data bases, if you use the index, the query on the TSM data
base runs much faster.
There are 4 indices on the ARCHIVES table:

The first index is on NODE_NAME, then FILESPACE_NAME, then FILESPACE_ID,
then TYPE.

So the first thing I would suggest is to try

SELECT DESCRIPTION FROM ARCHIVES WHERE NODE_NAME='PICKANODE'

If your query works fine that way and does not time out, pursue queries
using the NODE_NAME index.

To see how many different node_names have archives:

select distinct node_name from archives

If you don't have many different nodes with archives, you can just take
the resulting list of node_names, and run your SELECT for description
one node at a time, save the results in a file.

If there are too many, what I have done in the past is write a perl
script that selects the list of node names, then processes through the
list doing the selects one node_name at a time.  Because including the
WHERE on node_name uses the index, you still get done much faster than
if you do just 1 select without the index.

Wanda Prather
"I/O, I/O, It's all about I/O"  -(me)
  



-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Richard Sims
Sent: Friday, January 06, 2006 7:56 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: select statement for Archives


On Jan 6, 2006, at 3:10 AM, De Keyser Bart wrote:

> ...
> Within the retrieve module of the Backup-Archive Gui, you can view a
> list of all available archives.. so it should be possible to generate
> this list with a select, no?? ...

Yes, but fishing expeditions are very expensive in the TSM database,
no matter how they are conducted. You need to restrict your search to
limited areas so as to go after a reasonable number of items in each
request.

You can pursue the Select per the TSM doc, the TSM 5.1 Technical
Guide redbook appendix on SQL, and the available fields reported via
'SELECT * FROM SYSCAT.COLUMNS'. In your case, do: SELECT * FROM
SYSCAT.COLUMNS WHERE TABNAME='ARCHIVES' to see the column names you
can operate on.

   Richard Sims