MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

Massive Reorg



fixed

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:
Job 339804/ROB/QASPLINFO ended abnormally.
Job 339896/ROB/QASNADSR ended abnormally.
Job 339933/ROB/QASNADSQ ended abnormally.
Job 339964/ROB/QAOKP04A ended abnormally.
Job 339965/ROB/QAOKP01A ended abnormally.
Job 339970/ROB/QAOKP08A ended abnormally.
Job 339971/ROB/QAOKP09A ended abnormally.
Job 340048/ROB/QADBXREF ended abnormally.
...

But, in general, it ran pretty slick. I had some partitions numbering
close to a million deleted rows.

Even though I used QS36EVOKE, the number of jobs allowed to run at once in
QBATCH on this system is ten. Starting to wind down now...
Job User Type -----Status----- Function
GLH ROB BATCH ACTIVE CMD-RGZPFM
LCH ROB BATCH ACTIVE IDX-LCHL06
LLC ROB BATCH ACTIVE CMD-RGZPFM

Now, for the usual disclaimer:
If you are using 'direct' file access (where you access your file by
relative record number) instead of sequentially or by key this will cause
you issues. Most of us haven't seen this technique used since the S/34
except only temporarily on the S/36 using ADDROUT sort files.
If you rely strictly on arrival sequence of your data and have no other
way (like a timestamp or identity column) you may lose this sequencing of
data.
If you don't understand either of those two descriptions then you
shouldn't do this. Ask a DBA.
If you'd rather reorg by a key file, for example
RGZPFM FILE(MYLIB/MYFILE) MBR(MYMEMBER) KEYFILE(*FILE)
or
RGZPFM FILE(MYLIB/MYFILE) MBR(MYMEMBER) KEYFILE(MYLOGICAL)
Then you may want to eschew this technique, or reorganize them again when
done.
If these files are in use by your users you will get many more 'ended
abnormally' messages AND jobs will blow because they will try to access a
file that's locked by a long running reorg process.

Rob Berendt





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