All I got in reply was a CEO level generic response of "I want to improve disk access times, especially for long running batch jobs.".
There are specific jobs I'm concerned with, but listing their names would mean nothing to the readers of these e-mails.
The increase in "throughput" after we added 8 SSD's was significant. I want to tune the SSD resource though since I don't have enough space available on them to place all the "HOT" production files there.
What I was trying to understand, as stated in my original post, was if there was any advantage to moving files to SSD based on file type (LF or PF).
I believe I got the answer to that question too. It doesn't matter what type of files are placed on SSD. The more important criteria is file usage. I should analyze this usage and then place the most used files on SSD without concern for file type. .
I have devised ways to figure this out. One of the easiest is to query the system catalog statistics files for usage information.
To locate "HOT" physicals files in my production library (P1FILES) I run a query like this:
SELECT TABLE_SCHEMA, TABLE_NAME, MEDIA_PREFERENCE, LOGICAL_READS, PHYSICAL_READS, INSERT_OPERATIONS, UPDATE_OPERATIONS, DELETE_OPERATIONS, DATA_SIZE
WHERE TABLE_SCHEMA = 'P1FILES' AND PHYSICAL_READS > 0 AND TABLE_SCHEMA NOT LIKE 'Q%'
ORDER BY PHYSICAL_READS DESC
To locate "HOT" logical files in my production library (P1FILES) I run a query like this:
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MEDIA_PREFERENCE, LOGICAL_READS, INDEX_SIZE, INDEX_TYPE, LAST_QUERY_USE, QUERY_USE_COUNT, QUERY_STATISTICS_COUNT, LAST_STATISTICS_USE
ORDER BY LOGICAL_READS DESC
Once "HOT" data is identified, I change the UNIT parameter of the file from *ANY too *SSD. I then occasionally run the STRASPBAL *MP option to insure UNIT(*SSD) files are moved to SSD and UNIT(*ANY) files are moved off of SSD.
I choose to use this method over STRASPBAL *USAGE because I want to focus on our production schema, not all the data in the iASP.
Thanx for your reply to my post Rob. If anyone else has any suggestions on how I might do things differently I'd appreciate your comments.
Reply or Forwarded mail from: Kenneth E Graap