Dates/times for all backed up files on a volume?

[ldmwndletsm]

What horrible SQL concoction will tell me this?

 

[AngeloDeAngelis]

Let me see if there could be something in the library for this.

 

[ldmwndletsm]

Well, I suppose that I could run something exhausting like `query content volume`, grabbing the following three fields for every entry:

Node Name
Filespace Name
Client's Name for File

From this, I could then extract the path and filename from 'Client's Name for File' using something as simple as the Unix dirname and basename commands. Next, I could run something like this for each of these:

select * from backups where node_name='Node Name' and FILESPACE_NAME='Filespace Name' and HL_NAME='/path_from_dirname /' and LL_NAME='filename_from_basename'

and this would give me the OBJECT_ID and BACKUP_DATE for each entry on the tape.

I could script this using Unix shell or Perl, but seems ludicrous. There must be more efficient SQL witchcraft to accomplish this, but I'm not versed enough with SQL (complex joins and such) or all the sundry tables where things are stashed away to make this trivial or to do it in the most efficient manner. Then again, maybe it's easier than I suspect.

It's unfortunate that TSM doesn't just include the date/time field in the content information for the volume. Seems that would make more sense.

And suppose that I just want a listing of the file spaces, not the actual file names, on a volume. So, if I run:

query content volume | grep 'Filespace' | sort -u

then, yes, that would work, but it takes forever because TSM tracks everything down to the file level or individual object. It would be nice if it also included a list of just the file spaces in another table for the tape so you could quickly list them out.

 

[marclant]

This could work:

select contents.*,backups.backup_date from backups,contents where contents.object_id=backups.object_id

Could add other critieria to the where clause like and volume_name='VOLUME'and/or and contents.node_name='NODE'

I could not test on a large server, so I have no idea if this will run efficiently or not.

 

[AngeloDeAngelis]

Could start with this:

select contents.*,backups.backup_date from backups,contents where contents.object_id=backups.object_id and volume_name='/Spectrum/file/000003C0.BFS'