RGZPFM - KEYFILE(*NONE) or KEYFILE(*FILE)

[Former Member]

Curious if anyone has performed reorgs comparing the impact in performance when performed with KEYFILE(NONE) vs. KEYFILE(FILE)

To date, we've only used the default KEYFILE(NONE) and didn't know if KEYFILE(FILE) would buy us anything.

Thanks

Doreen

[Former Member · Accepted solution]

Hi,

Is anyone using online reorg's with large tables in production? We have some rather large tables (75-90 gig or so, 40% deleted rows due to archiving) that I am hesitant to try an online reorg with but we really can't aford to start a down time reorg and have it run long on Sunday since India and China are now live.

If anyone can share their experiences, I'd appreciate it. We are on V5R3 and pretty current with PTF's, running 4.7 x2.00 Unicode.

I believe the proper syntax for this type of reorg is:

RGZPFM FILE(R3PRDDATA/COSS) MBR(FIRST) KEYFILE(FILE) ALWCANCEL(YES) LOCK(SHRUPD) RBDACCPTH(*YES);

Thanks,

Craig

[Former Member · Accepted solution]

Hi Craig,

I would copy the table COSS and all LFs to a different library and would open this with UPDDTA, RPG or other tools for the complete time. Then I would issue the following command and see how it works.

Later I would perhaps repeat that with the offline approach in order to see differences ...

I'm pretty sure, all works fine )

Regards

Volker Gueldenpfennig, consolut.gmbh

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de

[Former Member · Accepted solution]

Two points to keep in mind when considering doing on-line table reoganization:

1) If your disk does not have much empty space letf (say, it is more than 80% used), there can be a good chance of highly fragmented "empty disk chunks" lying scattered around. To consolidate these "empty" chunks of disk space together to form a larger and more contiguous empty chunks for efficient allocation, you can run the command STRDSKRGZ ASP(your ASP number) TIMLMT(*NOMAX) (which can run on-line also) and let it run for 3-4 hours before starting RGZPFM.

You can monitor this DSKRGZ job by using command WRKSYSACT that comes with PT1 licensed program. The job name is SMDEFRAGTA.

2) Running RGZPFM on-line will increase disk IO access. Therefore, it is a good idea to run it during the period of lowest system workload to prevent slow response time due to disk being highly utilized.

Satid S.

IBM Thailand

[Former Member · Accepted solution]

It's been years since I have run reorgs on our SAP systems.

It used to be that SAP instance had to be down and you had to end journaling on the R3<sid>DATA library prior to any mass reorgs (we wrote our own in-house CL pgm) and then restart journaling when the reorg was complete. This applied even if you were only reorganizing a few files.

I understand from this thread and others like it that the SAP instance can be online if you are only reorganizing a few files.

What about the journaling?

I'm asking because our PRD AQPD table currently has 13,596,349,780 deleted bites (per DB02 Deleted Records analysis). That's a lot and I would want to bring the system to it's knees with a reorg on the file if it's going to journal anything.

Any information is appreciated.

Robert

[Former Member · Accepted solution]

Anyone care to comment on my question, please?

Thanks!

Robert

[volker_gldenpfennig · Accepted solution]

Hi Robert,

with PTFs later than about 1 year ago, that version is save. So, you can run it that way and it will work fine.

So, the syntax will be:

RGZPFM FILE(APQD) ALWCANCEL(YES) LOCK(SHRUPD)

Have fun

Regards

Volker Gueldenpfennig, consolut.gmbh

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de

[Former Member · Accepted solution]

Thanks, Volker

Did you mean to say, "...that version is..." saFe ?

Thanks,

Robert

P.S. Why do you only have 2 forum points? If that's a true value, then we haven't been treating you proprerly!

[Former Member · Accepted solution]

Hi Robert,

sorry, I always mix that up ;-(

yes, it is saFe - you can use it when you do have PTFs on, that are not far older than 1 year ...

Regards

Volker Gueldenpfennig, consolut.gmbh

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de

P.S.: I saw that the first time as and found out: My C-user has more and the S-User only 2 ... now it should have more points again )

[Former Member · Accepted solution]

I finally got a chance to reorganize some files. Everything went fine until I got to file BSIS. It took hours and hours and has increased DASD by over 89 gigs!!! We show an increase of nearly 15% DASD utilization. I have IPL'd but it doesn't help.

PLEASE! Does anyone have advice on how to correct this?

We are at V5R3M5 with all latest PTFs, cumes, etc.

Thanks for any assistance,

Robert

Edited by: Robert Clay on Mar 3, 2008 1:23 AM

[Former Member · Accepted solution]

Never mind. The DASD increase is due to over 537 journal receivers!!

As soon as I get them saved off and deleted, it should be okay.

It's 1:30 in the morning here and I guess I need to get some sleep.

Thanks anyway

Robert

[Former Member]

KEYFILE(*FILE) might also be useful if you upgrade your OS-release because there is a change in SQL-Definition in V5R2.

Before V5R2 a statement like 'select * from tablex' returned the records in 'order by primary key'

And since V5R2 the result is ordered by physical sequence in the file.

So if you use own ABAPs with SQL-statements where the 'order by'-clause is missing you might get different results. In this case RGZPFM with KEYFILE(*FILE) could be a workaround.

But the solution would be to always use a 'order-by' clause.

Regards

Guido

[Former Member]

Hi Guido,

I'm sorry, but this is now really a crazy "solution" for the problem ! It is just not reliable, because with further inserts, it changes again!

The ONLY solution here is to code "oder by primary key" as you intended to do!!!

Regards

Volker Gueldenpfennig, consolut.gmbh

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de

[Former Member]

The parameter KEYFILE(*FILE) should mean to use the table's primary key constraint as a reference in arranging the sequence of the records in that table.

KEYFILE parameter can be useful when U process the data in a table sequentially and frequently based on a particular order of an index. Reorganizing that table based on that particular index helps reduce ramdom IO disk access in such a case and thus provides better processing performance, especially in a case when the table contains a very large amount of records and U R processing the majority of (or all) the records in that table. If the table is not large enough, the reduced amount of ramdom IOs (after table reorg) may not be noticeable.

This should mean if U know of a particular query that processes a very large amount of records of a table based on an order of a particular index and have a compelling need to run it faster, U can try RGZPFM with KEYFILE(index file name) parameter specified in an off-hour period before U start that query.

Satid S.

IBM Thailand

[Former Member]

Hello Doreen,

We tried both methods long time ago on model 830 with V4R5 and V5R2. I can guess what you are expecting and in theorey I agree, but the fact is that we didn't notice much difference in performance... maybe just for our case.

Thank you,

Victor

[Former Member]

Hi Doreen,

I tried the same a few years ago and even created a special FastRgz Toolkit to do that in parallel - but there was no noticable difference ...

... even when it sounds pretty promissing ... I heard from other people, that tried this is the "first V5R2 time" that this helped a lot ...

So, in my eyes it cannot be bad, but do not expect real changes then ...

Regards

Volker Gueldenpfennig, consolut.gmbh

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de