MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Re: Massive Reorg



fixed

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.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact