Hi fellows,

can someone tell me the diference between the CHGPF and the CRTPF, yes I know the data will stay in the file, the LF's wil stay there, the security is the same, the triggers are still there.

But what is happening during the CHGPF???

Thanks a lot
LVL 17
MurpheyApplication ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I've never looked at the details, but I wrote my own CHGPF way back in OS/400 V2R2, so I might guess.

Start with CRTDUPOBJ into QTEMP or perhaps QRPLOBJ for the PF with DATA(*YES). Then CRTDUPOBJ the various LFs into the same library. You now have a basic copy the file, the data and the logical views.

Now CRTPF into the original library replacing the original physical file. Then do a CPYF temp back to original with FMTOPT(*MAP *DROP). This reloads the data into the new file defintion. Then CRTDUPOBJ the LFs from temp back to the original library. Finally, delete all the temporary stuff.

There are a bunch of problems that must be overcome to make this work with all authorities and triggers and constraints and SQL views, etc.; but that kind of stuff is easy for IBM. With various APIs to retrieve all the info into user spaces or memory beforehand, everything can be recreated.

This isn't intended to say that IBM's command does it this way. It only gives a conceptual view of the kinds of things that can be done. I have a RGZDBRPFM command on my old web site ( ) that has a few samples of what can be done to speed the process of rebuilding access paths if the file is large with many LFs that don't share access paths. My command uses Data Base Relations to cut the the re-org time on very large files. I suppose I ought to get back out there and modernize it, but it still works for most cases, so...

MurpheyApplication ConsultantAuthor Commented:
Yeah Yeah, I know,

I already have my own Zupdpd, and that is copying, creating, rebuilding, retrigerring and granting, but that was not the question,
I discovered the CHGPF just a couple of weeks ago, and I was wondering if IBM was as smart as we are :), or that they have an other way to add a column to the file.

SO ????

Good question... there are indeed a couple apparent oddities with CHGPF. Since IBM has direct access to any system state/system domain function, they can pretty much do whatever they choose regardless of what they let us do (without breaking security).

But, it's the same question that can be asked about all kinds of things -- e.g., IBM can mess around with program-associated spaces for ILE programs while we can only do so with them for OPM programs that have been compiled through the trusted translator (i.e., RPG or COBOL but not CL).

If IBM can directly manipulate object descriptions -- which they obviously can because they create them -- then there's nothing to stop them from rewriting the file/field descriptions directly and then simply moving bytes between data spaces adjusting for fields as needed and associating the new space with the updated object description.

Without major disassembly of CHGPF, I don't see how we can know. We could make some educated guesses based on what we see. I've seen one or two comments here about it.


Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

MurpheyApplication ConsultantAuthor Commented:
Hi Tom,

Yes, that's what I was thinking, If I look to the speed of a CHGPF and to the speed of my own program, I have the feeling that CHGPF is faster (maybe just becaus it is using C instead of CL....)

Thanks for your reaction, I will leave the question open for a while, I'm almost sure that Dave will add some comment to this.
A very interesting question that I have been looking into.
I have found some very dangerous consequences of using CHGPF and will post results in the next few days.

Hi chaps, I got it done a bit quicker that I thought

Like Tom has stated, unless we decompile the objects we can never know for sure.
What I can be sure of is that the CHGPF uses the SQL ALTER TABLE (AT) function to perform its database changes. It has some basic validation such as not allowing fields used as indexes to be removed – but this validation is very basic.

So I thought let’s see what we can do with both CHGPF and ALTER TABLE, to try and identify what IBM has done, I am running V5.1.

I did a basic test under debug to look what happened
Create a copy of a file – see how long it takes for timing purposes. Then create some indexes (14 seconds to create the file and 55 seconds to create the indexes)

Add and drop fields in the file, the copy part of the CHGPF takes 14 seconds.

Using CPYF to perform the same copy and it takes 2 minutes so IBM must be doing something different.

We can all see that the file that file Q_TA00001 is copied so I am guessing that this is the original file that has been renamed (it has to be a rename due to the speed).
The new file is created and the data pointers are mapped (not copied) back into it. Finally the Q_TA00001 file is deleted and the system re-associates the access paths etc to the new object. This can be achieved because we have an exclusive lock on the file.

OK so what would happen if I tried to remove a field that was used as a key?
This is not possible with CHGPF but it is with AT
When using the AT command to remove a filed that is used as a key you get the warning that data will be lost – the indexes are then deleted – a bit dangerous we still have the original data so not too bad. Again the copy takes about 14 seconds so I guess that is via the data pointers.

How can I prove my data pointers theory?
With a keyed physical, the deleted records are removed using the CPYF with the COMPRESS(*NO) option , with a non-keyed physical they remain. So I created a keyed physical deleted some records and changed it – result the deleted records where still there. This looks like it backs up my data pointer theory.

This got my mind in a bit of a state; it is simply pointer re-allocation with out any additional checks what is to stop us messing up some indexes.

So I created a file

F01      Char 10
F02      Char 10
F03      Char 10

Create a unique index on F02 and added records
for all three fields
then altered the attributes on F02 to Char 9 using the CHGF command.
It sailed through with only a might result in data loss warning. I now have a unique logical with duplicate keys – not too bad because the original data is still available.

OK so if I can do it on a logical I should be able to do it on a physical.
So I recreated the file as a keyed physical and performed the same test – with the same result i.e. duplicate keys in a unique file

This to me is proof that only the data pointers are re-mapped and not the actual data. The down side is that you can corrupt your data

I did a simple UPDDTA on the file that generated the following error, you can not access the file for update (I got member full error)

I wanted to perform more tests on the data but I am unsure of the consequences of say RGZPGM and do not want to put the production machine in danger (I have change jobs and do not have my own test system any more)

If anyone has a test box that can be put in danger you could try some other stuff on the corrupt indexes and post.

Some other tests
Changing keys.
The data is again mapped quickly 14 seconds and a QDBSVR job takes on the roll of re-building the access path

So my conclusion is the CHGPF renames the original database, and then it uses the data pointers to populate the data pointes of the new file with-out performing any consistency checks any associations i.e. access paths, triggers are transferred to the new object so alleviating the need for them to be re-created.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MurpheyApplication ConsultantAuthor Commented:
Hi Dave,

I already thought That this was a typical problem/question for you... :)

You mentioned ".....not too bad because the original data is still available."
do you mean when you change the field back from 9 to 10 pos, the last position must be available again????


You mentioned ".....not too bad because the original data is still available."
Hi that is right. but all of the other data in the file is available via the physical (except for the last byte of the altered field) and the logical file can be deleted.

But for a keyed physical I am not sure what will happen - and did not dare to experiment too much.

The command is designed to be used by programmers and I guess that you need to be carful.

MurpheyApplication ConsultantAuthor Commented:
Wow this is more then I expected, but when I look to your total story, the conclution is:

As long as-
- we don't make fields shorter,
- we don't mess around with indexes
it's quite save to use it.

most of the time I will just use it to add fields to a file. So no problem ?!?!?!?!

MurpheyApplication ConsultantAuthor Commented:
The more answers of this level I read, the more I be aware that I don't know very much about the AS/400 that's a bad score after 18 years AS/400 experience  ;-)

it is one of those things you take it on trust that it works, and for 99.999% of the time it will.
Now that we are aware of some limitiations we can make sure our system remain safe - and that has to be good news.

Again indexes are fine it is only unique indexs that are bad news.

Hi again
>>>The more answers of this level I read, the more I be aware that I don't know very much about the AS/400 that's a bad score after 18 years AS/400 experience  <<

It is not that we don't know much - it's just that the iSeries/AS400 is so vast and so poweful that our accumulated knowledge is probable about 20% of what the beast is capable of.

If IBM made the details more available I am sure that no other computer on the planet would come close. Come on Big Blue you have the best system out there - let the world know and let us know the full potential so we can develop world beating applications.

With ILE we now have the capacity to develop flexible, robust, highly scalable applications at great speed - but no one is doing it.

A slightly bemused Dave
it kept me out of the lounge for a few days **grin**
MurpheyApplication ConsultantAuthor Commented:

The machine is good (the best) the DB fast, the posibilities are endles...... but my boss perfers to sell websphere, and don't put any power in AS/400 customers, market or developement.... only networking en e-Junk.

So I'm afraid that the 400 had his best (longest) time......
I have just found out some more info.
The accesspath is re-built asynchronously instead of synchronously which is why it is quick and can not check for duplicates.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.