Re: SSD/HDD Performance & Balancing -- MIDRANGE-L

Adam,

Rob's original code was 1 for Physical Files, and 1 for Logical Files.
I've included 2 others that will span the entire Partition, not just a
single Library. Thank you Rob. These are verified on V7R1, TR9. I do not
recall which Release/TR level introduced these "system" catalogs.

To locate "HOT" physicals files in single production library (ADAM) 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
FROM QSYS2.SYSPARTITIONSTAT
WHERE TABLE_SCHEMA = 'ADAM' AND +
PHYSICAL_READS > 0 AND TABLE_SCHEMA NOT LIKE 'Q%'
ORDER BY PHYSICAL_READS DESC

To locate "HOT" physicals files in a single partition 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
FROM QSYS2.SYSPARTITIONSTAT
WHERE +
PHYSICAL_READS > 0 AND TABLE_SCHEMA NOT LIKE 'Q%'
ORDER BY PHYSICAL_READS DESC

To locate "HOT" logical files in single production library (ADAM) 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
FROM QSYS2.SYSPARTITIONINDEXSTAT
WHERE TABLE_SCHEMA='ADAM'
ORDER BY LOGICAL_READS DESC

To locate "HOT" logical files in a single partition 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
FROM QSYS2.SYSPARTITIONINDEXSTAT
ORDER BY LOGICAL_READS DESC

On Wed, Feb 25, 2015 at 12:20 PM, Adam Driver <AdamD@xxxxxxxxxxx> wrote:

Hi,

Rob, is there any chance you could repost the SQL statement referred to
below?

From Joe's mail:

Better solution 2: Using Sue Baker's prior dialogue, "most customers
place hot data" on SSD's and using Rob D's SQL statement, Identify our top

20/50/100/500 PF's and LF's, and SET their preferred unit with a focus on
getting the SSD's to nearly 90% full, using STRASPBAL TYPE(*MP) .

Thanks!

Adam Driver

Chicago, IL

T: 847-916-9499

M: 224-250-9049

www.linkedin.com/adamdriver<http://www.linkedin.com/adamdriver>

[Description:
http://www.quinnox.com/images/email-sig/quinnox-branding-logo.png]
[Description: http://www.quinnox.com/images/email-sig/facebook_icon.png]<
https://www.facebook.com/QuinnoxInc>

[Description: http://www.quinnox.com/images/email-sig/Twitter_icon.png]<
https://twitter.com/QuinnoxInc>

[Description: http://www.quinnox.com/images/email-sig/linkedin_icon.png]<
http://www.linkedin.com/company/quinnox>

www.quinnox.com<http://www.quinnox.com/>

________________________________

The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s) and
may contain proprietary, confidential or privileged information. If you are
not the intended recipient, you should not disseminate, distribute, print
or copy this e-mail. Please notify the sender immediately and destroy all
copies of this message and any attachments. Any views or opinions presented
in this email are solely those of the author and do not necessarily
represent the views of the company. Although the company has taken
reasonable precautions to ensure no viruses are present in this email, the
company cannot accept responsibility for any loss or damage arising from
the use of this email or attachments.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.