Re: Attempting to automatically select a recommended sort sequence

I'm not sure about automatically, but in RSS go to schemas and find the file you want under tables. Right click and select work with indexes. Off to the right is query use count. The QUSRMBRD api also can retrieve those counts.

On Thu, 2020-02-13 at 17:50 +0000, Rob Berendt wrote:

Assuming that physical row order is not a concern...


I ran this statement:

select

system_table_schema, system_table_name, system_table_member,

avgrowsize, number_deleted_rows, avgrowsize * number_deleted_rows as deleted_space

from qsys2.syspartitionstat

order by 6 desc

;

I have 17 in the nine figures and one in the 10 figures of 'deleted_space'.

Instead of just issuing a blanket RGZPFM for each significant row returned I thought of trying to automatically determine a recommended value for KEYFILE on the RGZPFM command.

Is there a way to determine a "most used" index?

I realize that "most used" may be not be the way to go if you really want some particular program to be screamingly fast that requires a different order but I'm figuring that something is better than nothing.


Our current ERP package does not use primary keys on the tables so keeping it simple by stating KEYFILE(*FILE) often buys you nothing.


Rob Berendt

--

IBM Certified System Administrator - IBM i 6.1

Group Dekko

Dept 1600

Mail to: 2505 Dekko Drive

Garrett, IN 46738

Ship to: Dock 108

6928N 400E

Kendallville, IN 46755

<http://www.dekko.com>

http://www.dekko.com





[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com



CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.