Richard,
I just spotted a "typo" in that rather long reply ...
The "base" table or PF gets the renamed to "EMPLOYEE0" and then the new LF or View gets the name of the original table or PF name.
Sorry for any confusion caused,
Mark
On Wednesday, November 24, 2021, 11:32:27 AM EST, Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx> wrote:
Richard,
Rather than incurring the risk of missing some objects (during recompiles) etc., it is far better to use a methodology that dates back to S/38 CPF:
#1. rename the Physical File (you were going to do that anyway)
#2. create a new Logical File with the same name as the original physical file. Be sure to add all the fields in the existing PF to the LF definition DDS, as well as replicating any keys, if the PF is keyed.
#3. now ensure that the new LF has the same "Record Format Level ID" as the original PF ...
Depending on your naming conventions, say the PF was named EMPLOYEE with some LFs over it, e.g. EMPLOYEE1, EMPLOYEE2, etc. You could create the new LF with a name such as "EMPLOYEE0" ... you get the idea?
Next, do some local testing to ensure everything still works as expected.
Now, deploy all of that ... it could be as simple as deploying a "script" or small CL *PGM and then running it one time on each target system to automate the above steps. Obviously, this needs to run at night or over a week-end etc. when no users are in the application.
And then, on to "PHASE TWO" ...
Now, you are in a position to make changes to the newly renamed "base" Table or PF ...
If it is DDS-based, you can change the DDS source and use CHGPF to apply the changes, without having to copy all the data, etc. If SQL DDL based, it is an "ALTER TABLE ..." (also, be aware that CHGPF may require someone reply to an Inquiry message, to warn of "possible data loss" depending on what was changed.)
Be sure to test this locally on your "test" or Q/A environment before attempting it "live"...
You will need to ensure that any LFs (for DDS-based LFs) specify all the fields in the original table, before you do the CHGPF ... to prevent "level checks" etc. See above.
Now, when you have tested everything and are ready to deploy changes, you can just send over a "script" or CL *PGM to run one time to issue the "ALTER TABLE" (for DDL based tables) or "CHGPF" (for DDS based PFs).
Then, you need only to make changes to any programs that need to access any of the new or changed fields. You may want to create some new LFs for that access to the new fields, so no programs ever depend directly on the "base" table or PF.
One of the benefits of this approach is, you have to recompile and re-deploy far fewer objects in total, to make incremental changes to the database design.
This was one of the main reasons for logical files and SQL views in the first place, to provide a layer of independence from the underlying physical file / table implementation for the using applications and the users.
You may also need to investigate if any queries (esp. Query/400) are using the PF or table.
Please review the above, and let me know if you have any questions, etc.
All the best,
Mark S. Waterbury
On Wednesday, November 24, 2021, 10:56:30 AM EST, Richard Schoen <richard@xxxxxxxxxxxxxxxxx> wrote:
Right. Thus the complexity.
In my scenario you can assume V7R3 and higher.
There could be: DDL, DDS, add new fields, remove fields, resize fiels, add indexes, add logical files, the list is endless.......
And any one of these things can then cause level checks for RPG, COBOL or CL programs.
I knew this wouldn't be a simple question, but I'm curious where to start.
And in the end the answer may be: manually rename/move old table, manually stage the new table and copy data back, then recompile all the associated objects to avoid level checks.
Which also begs the question as to whether DSPPGMREF has any alternatives to id potentially affected changed programs.
I'm working with a IBMi dev team who is going from single box development to have a dev/test machine and production machine and trying to make their deployment options easier.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
midrange.com
