RE: estimating RGZPFM runtime -- MIDRANGE-L

Sorry, the chart didn't come thru as desired.

Here's a link.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/dbp/rbaforeorgtypes.htm

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Steinmetz, Paul
Sent: Tuesday, November 19, 2013 1:00 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: estimating RGZPFM runtime

Below chart is good summary of all the options.

We use *SHRUPD so not to cause an issue with any apps.

RRN was not an issue with an of our apps.

REUSEDLT records however is an issue with about 30 PF. (3rd party app)

RGZPFM FILE(&MBLIB/&MBFILE) MBR(&MBNAME) +

RBDACCPTH(*NO) ALWCANCEL(*YES) +

LOCK(*SHRUPD) /* rgzpfm while active */ ________________________________ Reorganization options You can specify whether the reorganize operation on a physical file member can or cannot be canceled. Consider the features of each option when deciding which option to use.

* ALWCANCEL(*NO): This is the traditional type of reorganize operation. A full copy of the data might be made, so you need up to two times the amount of space. This option cannot be canceled (suspended) and cannot fully run in parallel. It requires exclusive use of the physical file member.

* ALWCANCEL(*YES): The data rows are moved within the physical file member so that a full copy of the data is not required. The physical file must be journaled, however, so storage is necessary for the journal entries. You can use the journal receiver threshold to minimize the amount of storage used in a specific journal receiver.

This option can be canceled (suspended) and restarted. It can run in parallel if the DB2(r) Symmetric Multiprocessing option is installed. To control the amount of resources used by the reorganize operation, you might want to change the query attributes using the Change Query Attributes (CHGQRYA) CL command or using Change Query Attributes from iSeries(tm) Navigator.

This option requires exclusive use for only a few seconds after the reorganize operation is complete to return storage to the system. If the exclusive lock cannot be acquired, a warning message is sent to the job log indicating that space cannot be recovered. To recover the space, you can start the reorganization again when no concurrent users are accessing the file member. The reorganize operation then immediately attempts to recover the space before starting the reorganization. If concurrent data changes have occurred since the initial reorganize operation, only a portion of the space might be recovered.

If LOCK(*EXCLRD) or LOCK(*SHRUPD) is specified, the result of the reorganize operation is not guaranteed to be exact, because concurrent users might be locking rows or changing rows in the file member.

[cid:image001.gif@01CEE51E.893CDD10]The type of reorganize operation that you decide to use depends on several factors. For example, is your goal to recover space, or is the sequence of the rows important? Is it important that the reorganize operation can be canceled (suspended)? Is it important to allow concurrent access to the physical file member? Use the following table to determine which option is most appropriate based on these factors. The shaded entries (which are also identified by an asterisk) are the characteristics of a key file option that make its choice particularly desirable.[cid:image002.gif@01CEE51E.893CDD10]

ALWCANCEL(*NO)

ALWCANCEL(*YES)

KEYFILE(*NONE)

KEYFILE(*FILE or keyfile)

KEYFILE (*RPLDLTRCD)

KEYFILE(*NONE)

KEYFILE(*FILE or keyfile)

Cancel and restart

No

No

Yes*

Yes*

Yes*

Concurrent access

No

No

Yes*

Yes*

Yes*

Parallel processing

Only index rebuilds

Only index rebuilds

Data movement and index rebuilds*

Data movement and index rebuilds*

Data movement and index rebuilds*

Non-parallel performance

Very fast

Fast

Very fast*

Slower*

Slowest*

Temporary storage

Double data storage

Double data storage

Journal receiver storage*

Journal receiver storage*

Journal receiver storage*

LIFO KEYFILE index processing

N/A

Duplicates reversed

N/A*

N/A*

Duplicate ordering preserved*

Index processing (non-KEYFILE)

Synchronous or asynchronous rebuilds*

Synchronous or asynchronous rebuilds*

Maintain indexes or synchronous or asynchronous rebuilds*

Maintain indexes or synchronous or asynchronous rebuilds*

Maintain indexes or synchronous or asynchronous rebuilds*

Final row position exact

Yes*

Yes*

Only if LOCK(*EXCL) and not restarted

Only if LOCK(*EXCL) and not restarted

Only if LOCK(*EXCL) and not restarted

Amount of CPU and I/O used

Smallest*

Next smallest*

Smallest

More

Most

Variable lengths segment reorganize

Good*

Good*

Worse

Worse

Worse

Allows referential integrity parents and FILE LINK CONTROL DataLinks

Yes*

Yes*

No

No

No

Allows QTEMP and database cross-reference files

Yes*

Yes*

No

No

No

Replication cost

Minimal (one journal entry)*

Minimal (one journal entry)*

More (journal entries for all rows moved)

Most (journal entries for all rows moved)

Most (journal entries for all rows moved)

-----Original Message-----

From: midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel

Sent: Tuesday, November 19, 2013 11:49 AM

To: Midrange Systems Technical Discussion

Subject: RE: estimating RGZPFM runtime

It doesn't really matter to me if they are in sequence.

I collapse common RRN's into the OLDEST and the NEWEST for the time period of interest (sorted by RRN & timestamp). This makes the journal reporting job finish in a few minutes and simplifies the logic.

So if a record gets updated 100 times today, our journal processing pgm only sees the first "before" image and the last "after" image.

-----Original Message-----

From: midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx%3cmailto:midrange-l-bounces@xxxxxxxxxxxx>> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt

Sent: Tuesday, November 19, 2013 10:33 AM

To: Midrange Systems Technical Discussion

Subject: Re: estimating RGZPFM runtime

Rob,

I'm pretty sure they are always in sequence...but hopefully one of the journal gurus will jump in and confirm or deny. :)

Charles

On Tue, Nov 19, 2013 at 11:19 AM, <rob@xxxxxxxxx<mailto:rob@xxxxxxxxx<mailto:rob@xxxxxxxxx%3cmailto:rob@xxxxxxxxx>>> wrote:

I would think that a before/after image would be in very close proximity.

Granted there's no guarantee that they will be consecutive in a high

volume environment. Right? However, I would think they would be

close enough together that you would not be able to fit a reorg between them.

Active or dedicated.

Sequence Code Type Object Library Job Time

24706634 R UB ITEMMSTR ROB ROBS1 11:12:05

24706635 R UP ITEMMSTR ROB ROBS1 11:12:05

IDK how you activate these fields

Logical unit of work :

Transaction ID . . . :

or if they would help your tying it all together.

Sorry about the farm the journals. I thought you were dumping them

another file for perceived easier processing. Must have been another

poster...

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

From: "Stone, Joel" <Joel.Stone@xxxxxxxxxx<mailto:Joel.Stone@xxxxxxxxxx<mailto:Joel.Stone@xxxxxxxxxx%3cmailto:Joel.Stone@xxxxxxxxxx>>>

To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx%3cmailto:midrange-l@xxxxxxxxxxxx>>>

Date: 11/19/2013 11:04 AM

Subject: RE: estimating RGZPFM runtime

Sent by: midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx%3cmailto:midrange-l-bounces@xxxxxxxxxxxx>>

You lost me at your first statement.

I am thinking that you HAVE to rely on RRN when processing the journals.

How else can you compare the BEFORE & AFTER images and be sure that

you are comparing the same entity?

When I say "farm the journals" I am referring to the same thing you

are doing; ie "read the journals".

-----Original Message-----

From:

midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx%3cmailto:midrange-l-bounces@xxxxxxxxxxxx>

[

mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of

rob@xxxxxxxxx<mailto:rob@xxxxxxxxx<mailto:rob@xxxxxxxxx%3cmailto:rob@xxxxxxxxx>>

Sent: Tuesday, November 19, 2013 8:36 AM

To: Midrange Systems Technical Discussion

Subject: Re: estimating RGZPFM runtime

1 - We journal like crazy and use it to audit transactions. We do not

rely upon RRN. Then again, we read the journals - not some journal

farm created by processing the journals.

2 - Reorg while active, or reorging while not active, will have the

same effect. RRN's are going to change. You may want to consider the

one poster's idea about disabling the journal during the reorg.

Modify his cl

by adding your journal farm request right before the reorg, if you

still want to stick with your journal farm.

3 - If you can adapt your train of thought to accept the RRN change

associated with reorganization you might want to consider reuse

deleted records. The only difference being is you can snapshot your

journal farm around the reorgs, not around the reuse.

4 - You may want to consider the KEYFILE parameter of RGZPFM. This

can help your performance on reading. Use the DB tools to see the

most popular index or suggested index.

5 - If you do start using reuse deleted records, and the number of

deleted

records doesn't get terribly skewed, then you should never have to do

a dedicated RGZPFM. For example, if you're going to have roughly the

same number of deleted records every night due to some process you may

as well not try reorging to get the disk space back since it will need

it again the next night. However, if you recently discovered that

you're dropping from retaining 15 years of history to just 2, then by

all means, reorg to get the space back. Or one oopsie runaway job

really stuffed it full of garbage you cleaned out, then reorg to get that space back.

Trying to duplicate the reorg on a different lpar, test library, etc

can be a terrible waste of time. Unless you're very certain all

things are

equal: memory, disk, controllers, current load on system, etc. Well,

normally your second setup is less powered than your primary so it may

show you 'worst case'. Keep in mind that some indexes may be

configured for delayed rebuild and be doing that in the background so

you'll have to check out some system tasks for that to see when it's

'really' done. But if you do the duplication by some process that

inherently does a reorg, like certain options of CPYF or CRTDUPOBJ,

then your results will again be

quite skewed.

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

From: "Stone, Joel" <Joel.Stone@xxxxxxxxxx<mailto:Joel.Stone@xxxxxxxxxx<mailto:Joel.Stone@xxxxxxxxxx%3cmailto:Joel.Stone@xxxxxxxxxx>>>

To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx%3cmailto:midrange-l@xxxxxxxxxxxx>>>

Date: 11/18/2013 04:24 PM

Subject: estimating RGZPFM runtime

Sent by: midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxx%3cmailto:midrange-l-bounces@xxxxxxxxxxxx>>

How can I estimate down-time required to RGZPFM a file? Is there a

utility or rule-of-thumb on these?

Can it be killed if not completed OR will the file then be compromised?

Thanks!

______________________________________________________________________

This outbound email has been scanned for all viruses by the

MessageLabs Skyscan service.

For more information please visit http://www.symanteccloud.com

______________________________________________________________________

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:

MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxx>

Before posting, please take

a moment to review the archives at

http://archive.midrange.com/midrange-l.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:

MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxx>

Before posting, please take

a moment to review the archives at

http://archive.midrange.com/midrange-l.

______________________________________________________________________

__ This inbound email has been scanned for all viruses by the

MessageLabs SkyScan service.

______________________________________________________________________

__

______________________________________________________________________

This outbound email has been scanned for all viruses by the

MessageLabs Skyscan service.

For more information please visit http://www.symanteccloud.com

______________________________________________________________________

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:

MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxx>

Before posting, please take

a moment to review the archives at

http://archive.midrange.com/midrange-l.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:

MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxx>

Before posting, please take

a moment to review the archives at

http://archive.midrange.com/midrange-l.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxx>> Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

________________________________________________________________________

This inbound email has been scanned for all viruses by the MessageLabs SkyScan

service.

________________________________________________________________________

______________________________________________________________________

This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.

For more information please visit http://www.symanteccloud.com

______________________________________________________________________

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L-request@xxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxx>>

Before posting, please take a moment to review the archives

at http://archive.midrange.com/midrange-l.