Hi, Margaret, Geoff.

If you select the key sequence for the backups table, you can optimize
your select so it doesn't kill your TSM instance.

v5: select colname, index_keyseq from syscat.columns where tabname='BACKUPS'
v6: I think it's keyseq instead of index_keyseq

If you then add clauses to your "where" predicate, such that you keep
the key sequence intact as low as possible, you minimize the row
scanning you do.

For example, in the v5 tabschema (I don't have a v6 tabschema saved),
the index_keyseq order for the BACKUPS table is node_name,
filespace_name, filespace_id, state, type.  In this case below, two
selects will actually run faster than one select.

select * from backups where
     node_name='NODEA' and
     filespace_name='/mnt1' and
     filespace_id='10' and
     state='ACTIVE_VERSION' and
     type='FILE' and
     ll_name='myfile'
select * from backups where
     node_name='NODEA' and
     filespace_name='/mnt1' and
     filespace_id='10' and
     state='INACTIVE_VERSION' and
     type='FILE' and
     ll_name='myfile'

runs faster than:

select * from backups where
     node_name='NODEA' and
     filespace_name='/mnt1' and
     type='FILE' and
     ll_name='myfile'

Note the removal of the FILESPACE_ID and the STATE clauses from the
WHERE predicate.  This will cause more row scanning because it's not
indexing as deeply; it stops after FILESPACE_NAME.  Even though TYPE is
used as a clause, because of the break in the indexing, it's not used as
an index.  Or, at least, that used to be the case in v5.  I suppose it's
possible v6/db2 handles skips in the index more gracefully, but someone
else would have to chime in on that.  :-)

Pro-tip 1:  For select testing, do a "query occupancy" to find a
filespace with fairly few objects and test on that filespace.
Pro-tip 2:  For large selects that aren't friendly to indexing, build a
shell/perl/whatever script to parse the occupancy table and issue a
billion small indexed selects.  I find it runs orders of magnitude
faster than one large select that kills your instance. Again, Y(v6)MMV.

Alex


On 7/12/2012 10:41 AM, Clark, Margaret wrote:

Well, since you asked for "any suggestions"...

It seems to me you could get this report using a command like

select archive_date,filespace_name from archives a,file_name from contents c where 
archive_date >= current_timestamp - 1 days and  a.node_name = c.node_name and 
a.filespace_name = c.filespace_name

or

select backup_date,filespace_name from backups b,file_name from contents c where 
backup_date >= current_timestamp - 1 days and  b.node_name = c.node_name and 
b.filespace_name = c.filespace_name

However, I haven't tested these commands, and I'm not about to.
On our system, any such command would run forever and probably die before it 
completed.
I have long since given up trying to use select statements using either the 
contents or backups tables, they are way too big.
If this report is not produced regularly, maybe it's because your predecessor 
gave up too!

P.S.  HL_NAME and LL_NAME  are not what you need... those are the IP address 
and port number for a client.

- Margaret

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of 
Geoff Gill
Sent: Thursday, July 12, 2012 9:13 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: [ADSM-L] Select or Query help

Good day all,

Below is a section of a file I was sent. I'm told the person who did this is 
long gone and nobody knows what command he used to get it. I was hoping someone 
might recognize it and help recreate it since I'm not sure we'll even be able 
to contact that person for help.

There are no headers for the columns but they seem obvious. You can see from 
the output the date, file://server/drive, file\path and filename. The filename 
and data in the file seems to indicate that this command is run to output one 
specific dates worth of data of every file a specific node backed up.

If anyone has any siggestions I would be eternally grateful because I'm being 
hammered to get an answer so I can run it and get the data.

       2011-10-17         \\bpsnt91\c$     \DOCUMENTS AND         NTUSER.DAT
02:36:24.000000                          SETTINGS\AFSADEV\
       2011-10-17         \\bpsnt91\c$     \DOCUMENTS AND         NTUSER.DAT.LOG
02:36:24.000000                          SETTINGS\AFSADEV\

      2011-10-17         \\bpsnt91\c$     \DOCUMENTS AND         NTUSER.INI
02:36:24.000000                          SETTINGS\AFSADEV\ Thank You Geoff Gill