Pretty neat.
+1
Paul
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, October 22, 2012 11:10 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Massive Reorg
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
--
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
--
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.
midrange.com
