AS400 Physical Files and Changing Fields from NUMERIC to BIGINT type.

[Justin Fox]

We have some files at work that have fixed length physical file fields of type 'S' with length 5. Well the completely unexpected thing happened (sarcasm) where you eventually run out of numbers.
Is there a way to update the physical file fields' type without having to re-compile every RPG program that references that file? Another issue is the display files were set to mimic that field length as well.
Is there an 'easy' way to accomplish this? I mean, there are a TON of programs, CL programs, query procedures and etc. This is a system that has been developed and has had modules added to it since the early 80's.

The solution proposed is to simply add a separate key field that is used to further extend the use of those 99,999 unique id annually.

I kind of feel like this is just helping it limp along until the next time it 'breaks'.

Thanks for any help,

Justin

I'm a .NET developer and use SQL Server frequently.

[Rocky]

The proposed solution doesn't solve the issue of updating display files, print files and recompiling programs.

You could add a separate file that uses the same key, change the necessary programs to use that and the display/print files. This may be worth it if you have a LOT of programs that use the file, but not that field.

[Peder Udesen]

There are more aspects to take into consideration.
1) Are you using level check on the files? When changing the file it causes the level number to change !
2) The difficult thing is that it is a key field. Then you have to change/update all indexes.
3) You could change the old field from zoned (5,0) to packed (9,0). This would use the same amount of bytes but still causes change of level number.
4) As Rocky says you still have to change display files and printer files not to mention that all work fields internal in the programs used must be tested.
Are the size hard coded to (5,0) or are they defined using "like".
5) I recommend extending the field now -- making a quick fix causes you in the future to regret not doing it.


A solution could be to implement the modification in more phases.
If you don't use level check you could add a new field to the end of the record of the file and then add a trigger that copies the value in the old field into the new field.
I remember seeing some examples in the past how to do it with date field but don't remember where. Try google for it.
All programs updating the file should then be compiled ( and initialising the new field where the record is written to the file ).

Then you can start implementing it in all programs.

Regards
Peder

[Rocky]

If the field has to be changed to BIGINT I assume it's because the current field isn't big enough, if so the trigger won't work as hoped.

If you aren't using level check you could add a field to the end of the record with same name as BIGINT - rename the current field to something else and copy the data accordingly.

You will have to recompile every program that updates the file, or uses that field. If the program does not use that field it won't need to be recompiled.

How complicated do you want to go? You could create a separate file with a common key that has the new field and create a view joining the two together. Use the view for all programs that need to refer to that field. You can then create an instead of trigger that would update the value of that field for times when you want the program to update the value of the new field... any program that doesn't refer to the field will not need to be recompiled, any other program will need to specify the name of the view instead of the file and recompiled.