How to search contents table?

[ldmwndletsm]

[ Question ]
Why is it that I can search the backups table so quickly, e.g.

select OBJECT_ID from backups where NODE_NAME='node1' and FILESPACE_NAME=' /filesystem1' and HL_NAME=' /dir1/' and STATE='ACTIVE_VERSION'"

This returns the desired column value super fast, every time, no doubt about it. BUT then I try this to get the volume(s) thus:

select object_id, volume_name from contents where node_name='node1' and filespace_name='/filesystem1' and object_id='value_returned_from_above'"

and it hangs interminably. How on earth is this table indexed? Is it actually crawling through the entire contents table brute force and NOT narrowing it down via indexing? What am I missing here? What more do I need to do to expedite the search?

[ Question ]
Does the backups table not have as many entries as contents? How do these tables really differ, anyway?

I mean, I can check syscat.columns and all that, but I really don't understand the difference between these tables in terms of their sizes. Thus far, every search I do on the backups table is really fast, even if it's "select *", as long as the "where" specifies enough columns, e.g. node_name and filespace_name minimally, but even just those two columns alone (never mind HL_NAME) seems more than sufficient to pull information out of the backups table with minimal pain (no latency), and cancelling seems atomic as well. Not so with contents.

[ What I'm trying to accomplish ]
I want to know what copy pool volumes are required to restore a directory. This is for periodic data integrity validation checks wherein the data is first restored from the primary pool tape(s) and then compared (including cryptographic checksums) against the same data restored from the copy tape(s). The purpose here is NOT to restore some damaged primary pool tape. This is to ensure no bit rot is occurring on off-site tapes. The activity log is NOT going to tell you the required copy pool volumes UNLESS only those are loaded (they are not) and any loaded primary tapes are set to 'unavailable'. So it will default to the primary pool.

Yes, I can play games with `show bfobject object_id` , superbit file, all that, to track down the primary and copy pool volumes, or I can run `query backup blah-blah-blah -querydetail` on the client to retrieve the volume ids (hex) reported in the Volumes Summary Statistics, convert to decimal and then look those up in the appropriate DB2 table (ss_volume_ids) to decipher what the actual volume names are, but that's circuitous and only works for the volumes that are available, so if it's only the primary, then that's all that 'querydetail' will report. If one or more of the required copy pool volumes was in the library, and the counterpart primary volume(s) was set to 'unavailable' then, yes, it would report the copy pool volume ids in hex. It adjusts the report of the required volume ids accordingly, based on the volumes that are nearline and not 'unavailable'. I've tested that. Could also just use 'detail', grep for the volume id line and pipe that to a unique sort. That avoids having to convert those values to decimal, but you still have to search the DB2 table to get the volume names. Otherwise, I could run `restore volume volume_name preview=yes` for each of the required primary volumes in order to determine which copy pool volumes would be needed. Don't know how long that would take, but it's not like this has to be completed in 10 minutes. I would have at least a day or so since it's somewhat contrived.

I want to avoid bringing back more copy pool volumes from off site than necessary in order to minimize risk. Clearly, `query nodedata node_name` is not going to narrow down the list appropriately.

 

[ldmwndletsm]

Well, I suppose I could also carry out a brute force search method wherein I simply generate a list of all the copy pool volumes containing any data for the node (`q nodedata`) and then dump the contents (`q content`) for each volume, parsing for the directory name or some such thing for each, and then report the affected volumes. That could be a superset of the required volumes, though, depending on how often the directory's constituent files had been modified (active/inactive ... all that). If there had been few changes then we'd expect the list to be tiny in comparison to the total number of volumes containing that node. Otherwise, it might not be significantly fewer. Regardless, that could all be scripted (e.g. Perl). Seems ludicrous, though, and would only take all night to run.

 

[ldmwndletsm]

I worked with IBM on this. Apparently, the CONTENTS table is a view of multiple tables. I did find this:

Select from Contents performance

Why does a select from Contents take a long time?

www.ibm.com

My experience thus far is that it's completely pointless to attempt any search on this table, and the "view" appears to be the culprit. It's probably trying to search too much data.

However, the specific tables to obtain this necessary information do exist in DB2 and can, therefore, be searched. In my example, let's say the directory that I'm looking for is /data1/2020/january (file space = /data1) on node alpha, and I want to know the copy pool volumes. After working with IBM, a quadruple nested select statement (with the 'in' operator) seems to do the trick, once the necessary tables can be determined as:

db2 "select tabname, colname from syscat.columns where tabschema='TSMDB1'"

There's a lot of tables and sundry columns, and there seems to be overlap with a number of them, but it looks like the BF_AGGREGATED_BITFILES table has the equivalent of what the 'show bfobject' command returns, so it matches the superbit values to the objects ids.

The key here was to first determine the OBJECT_ID, for each constituent file, from the BACKUPS table (select 1 of 4), then get the superbit id (superbfid) from the BF_AGGREGATED_BITFILES table using the OBJECT_ID (select 2 of 4) then use the poolid (show sspool) and the BFID (SUPERBFID returned from select 2 of 4) to get the VOLID from the AF_SEGMENTS table (select 3 of 4) and finally to use the VOLID to return the volume name (VOLNAME) from the SS_VOLUME_IDS table (select 4 of 4).

Example:
[ Determine the poolid ]
show sspool

Note: Copypools appear to use negative numbers, primary positive

In this case, let's say the value is -4

[ As the DB2 user, once connected ]
db2 "select volname from ss_volume_ids where volid in (select volid from af_segments where poolid=-4 and bfid in (select distinct superbfid from bf_aggregated_bitfiles where bfid in (select object_id from backups where node_name='alpha' and filespace_name='/data1' and hl_name='/2020/january/' and state='ACTIVE_VERSION' and type='FILE')))"

This ran very fast and sure beats having to horse around with the silly CONTENTS table. When I have some time, I will have to see how fast `restore volume volume_name preview=yes` takes to run for each required primary pool volume (easy to determine as I posted earlier), but that could be a superset of the "actual" required volumes. If the number seems unreasonable then maybe something like the above approach *could* be used to zero in on the necessary volumes, but this might not be reliable due to subdirectories and/or inactive versions.

That said, all of this could probably be done outside of DB2, maybe in a script, by using 'show bfobject' instead and parsing the output, but the DB2 approach with the select statements might be cleaner? Not sure. Maybe it doesn't matter.

Anyway, in this DB2 example, I'm only interested in active versions, so I'm filtering only on top level files (type=FILE). Not sure how to handle sub directories, but that might involve a recursive solution and could get rather tricky. But otherwise, without the subdirectories this probably would be incomplete and not reliable. Also, what about inactive versions? Not sure how to handle that, so again, this approach might prove very limited. Moreover, in my example, there were only a half a dozen files or so, so I have no idea how quick this would be on a directory with a lot of files or inodes. This might get bogged down. Maybe I'll have to test it at some point on a directory with a much higher number of files, e.g. 1000.

Anyway, in the test, the information returned from the BF_AGGREGATED_BITFILES table agreed with that returned from the 'show bfobject object_id' command, so that was reassuring.

 

[marclant]

This returns the desired column value super fast, every time, no doubt about it. BUT then I try this to get the volume(s) thus:

select object_id, volume_name from contents where node_name='node1' and filespace_name='/filesystem1' and object_id='value_returned_from_above'"

and it hangs interminably. How on earth is this table indexed? Is it actually crawling through the entire contents table brute force and NOT narrowing it down via indexing? What am I missing here? What more do I need to do to expedite the search?

I don't know how it is indexed, but the object_id is a unique value, so you don't need the node_name and the filesyste_name in your where clause, the more items you search on, the more work the query needs to do. The object_ID alone is sufficient to get what you need as it's unique.

[ Question ]
Does the backups table not have as many entries as contents? How do these tables really differ, anyway?

Contents has more objects because some files span across volumes and if you have a copy storage pool, each file has at least 2 entries in the table.