On 22 Oct 2012 11:09, rob@xxxxxxxxx wrote:
I have a sandbox lpar. On it I have a few flavors of BPCS and some
other software. No one else is currently on it at this time.
I went into iNav's Run SQL Scripts and fired this one off:
select
NUMBER_DELETED_ROWS, SYSTEM_TABLE_SCHEMA
, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER
, ROUTINES.QCMDEXC('SBMJOB CMD(RGZPFM
FILE(' || TRIM(SYSTEM_TABLE_SCHEMA) || '/'
|| TRIM(SYSTEM_TABLE_NAME)
|| ') MBR(' || TRIM(SYSTEM_TABLE_MEMBER)
||')) JOB(' || TRIM(SYSTEM_TABLE_NAME) || ') JOBQ(QS36EVOKE)')
from qsys2.syspartitionstat
order by number_deleted_rows desc;
No WHERE clause. Which probably explains a few messages:
<<SNIP>>
Job 340048/ROB/QADBXREF ended abnormally.
...
But, in general, it ran pretty slick. I had some partitions
numbering close to a million deleted rows. <<SNIP>>
Ugh!... "Blind" reorgs. While that is a "slick" way to get some work
done, the specific work shown may not be the greatest example. Note
that in addition to some SNIPped caveats that were listed, reorganizing
might benefit from some ordering due to shared and keyed dependencies.
Sometimes reorganizing various application databases may best be
programmed by the [effective] DBA versus being attempted generically via
effective system administration.
IMO there is far too little logic in that SQL request. :-( That lack
of logic was a common problem seen with examples of the same effective
programming done using DSPFD *MBRLIST [or *MBR] output first; which in
the above, by comparison, is effectively just pushed into a UDTF
referenced in the VIEW. Minimally there should be a predicate which
precludes inclusion of any database members without any deleted records
and omitting any source physical files; i.e. no reason to submit a job
that will do effectively nothing, or worse, effect a great amount of
work for effectively no gains. If not, then possibly excluding all
PF-SRC which would typically never have a deleted row, but would best
avoid accidental date and\or sequence reset [if even parameter default
changes could effect that; I did not check]. An additional predicate
should omit any [non-quasi-user] system library [at least omitting QADB%
in QSYS, though probably any database file in QSYS and possibly also
libraries like QSPL and QRPLOBJ, and especially QRECOVERY]. I have
always recommended excluding all Q% libraries, and including explicitly
only the QUSRSYS, QGPL, and QSYS2... if those libraries were not to be
handled separately from such more-generic logic; e.g. the former two
being left to CLEANUP. Similarly other QUSRxxx libraries may have their
own effective CLEANUP-like features, for which user requests to perform
RGZPFM outside of that LPP's software may not be well supported or even
problematic; e.g. a product like BRMS could intend that only its
command(s) would be used to effect any reorganization and any other
member\data maintenance.?
IMO a WHERE clause really should at least have included not only some
minimal number of deleted rows, but also a minimal ratio of deleted to
active rows and even possibly a minimal potential amount of recovered
storage; i.e. consider effective record-length. Would anyone really
want to perform the Reorganized Physical File Member to recover the
storage for just one row... which of course often effects greater
storage requirements rather than reduced storage; though a second
consecutive RGZPFM would truncate any extra row-storage that had been
reserved as part of the prior reorganize request. And what about
recovering storage for 200 ten-byte rows meaning less than 2K? Even if
that was over 20% of the number of active records.? Also, if an
application gets great gains from concurrent insert, then there are
negative consequences for removing the deleted rows. Again, some
libraries may best be left to a[n effective] DBA to decide when and how
to process [and schedule] the reorganize activity.
midrange.com
