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:[EMAIL PROTECTED] On Behalf Of Richard Sims Sent: Friday, January 06, 2006 7:56 AM To: ADSM-L@VM.MARIST.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