Hi, what's being reorged...all files in a library?
-----Original Message-----
From: Paul Therrien <ptherrien@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Mon, Oct 22, 2012 1:12 pm
Subject: RE: Massive Reorg
Pretty neat.
1
Paul
-----Original Message-----
rom: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx]
n Behalf Of rob@xxxxxxxxx
ent: Monday, October 22, 2012 11:10 AM
o: midrange-l@xxxxxxxxxxxx
ubject: Massive Reorg
I have a sandbox lpar. On it I have a few flavors of BPCS and some other
oftware. No one else is currently on it at this time.
I went into iNav's Run SQL Scripts and fired this one off:
elect NUMBER_DELETED_ROWS, SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
YSTEM_TABLE_MEMBER, ROUTINES.QCMDEXC('SBMJOB CMD(RGZPFM FILE(' ||
RIM(SYSTEM_TABLE_SCHEMA) || '/' || TRIM(SYSTEM_TABLE_NAME) || ') MBR(' ||
RIM(SYSTEM_TABLE_MEMBER) ||
)) JOB(' || TRIM(SYSTEM_TABLE_NAME) || ' ) JOBQ(QS36EVOKE)') from
sys2.syspartitionstat order by number_deleted_rows desc;
No where clause. Which probably explains a few messages:
ob 339804/ROB/QASPLINFO ended abnormally.
ob 339896/ROB/QASNADSR ended abnormally.
ob 339933/ROB/QASNADSQ ended abnormally.
ob 339964/ROB/QAOKP04A ended abnormally.
ob 339965/ROB/QAOKP01A ended abnormally.
ob 339970/ROB/QAOKP08A ended abnormally.
ob 339971/ROB/QAOKP09A ended abnormally.
ob 340048/ROB/QADBXREF ended abnormally.
..
But, in general, it ran pretty slick. I had some partitions numbering close to
million deleted rows.
Even though I used QS36EVOKE, the number of jobs allowed to run at once in
BATCH on this system is ten. Starting to wind down now...
ob User Type -----Status----- Function
LH ROB BATCH ACTIVE CMD-RGZPFM
CH ROB BATCH ACTIVE IDX-LCHL06
LC ROB BATCH ACTIVE CMD-RGZPFM
Now, for the usual disclaimer:
f you are using 'direct' file access (where you access your file by relative
ecord number) instead of sequentially or by key this will cause you issues.
ost of us haven't seen this technique used since the S/34 except only
emporarily on the S/36 using ADDROUT sort files.
f 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.
f you don't understand either of those two descriptions then you shouldn't do
his. Ask a DBA.
f 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
o eschew this technique, or reorganize them again when done.
f these files are in use by your users you will get many more 'ended
bnormally' messages AND jobs will blow because they will try to access a file
hat's locked by a long running reorg process.
Rob Berendt
-
BM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to:
505 Dekko Drive
Garrett, IN 46738
hip to: Dock 108
6928N 400E
Kendallville, IN 46755
ttp://www.dekko.com
--
his is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To
ost a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
hange list options,
isit:
http://lists.midrange.com/mailman/listinfo/midrange-l
r email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment
o review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
