Hi, 

we don't have a change management systems and a strong versioning of our source program.

Now we have the need of add two fields on the table most used on my server. 

There are a lot of raw access to this table (with chain, read, write, update), about 1000 of programs use it, in write only a few programs, the major of this are in input. 

What do you suggest? Because we have fear of make a chgpf we are thinking of an extension of this file with the new fields. 

What are the risk if I add two fields in my table using lvlchk(*no)?

Because sure we can't recompile all the programs without a change management system,

------------------------------
Paolo Salvatore
------------------------------

Dear Paolo

>>>> What are the risk if I add two fields in my table using lvlchk(*no)? <<<<

I do not see anyone can give you a conclusive answer without knowing specific information about you application environment.  My suggestion is for you to take this opportunity to implement AD tool as it is one sensible solution while using LVLCHK(*NO) as a stopgap solution, but you may or may not encounter issue with some programs in the mean time.

To prevent this issue in the future, a general suggestion is to create logical files over the physical files and let all programs use the LFs. Then whatever future changes you may add to the PFs would not affect or minimally affect the programs using level check.  But this suggestion is easily done from the beginning of the AD cycle.  Or better yet, always use embedded SQL and you will have no need to worry about level check any more.

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
------------------------------

If you are "only" adding columns and all your access is with native I/O, IMHO the best solution is to do a CHGPF and recompile after all your programs.

I'd not suggest LVLCHK(*NO) even if it may work if you add the new columns at the end of the column list.But it is always a risk.

A lot of companies create additional tables/physical files for those enhancements that are joined with the unique key with the "original" table ... but finally they end up with tens (or even more) of additional tables/physical files. Which then have to accessed in the programs (if you need the additional fields). So you may end up with hundreds of additional code lines for accessing all the additional tables when you need one of the additional fields. (Which makes the programs not easy to maintain!)

If you decide adding a new logical file which should be accessed instead of the physical file. Keep in mind that all other logical files point to the original physical file ... so, if you access any of the logical files (with native I/O), the additional columns are included! (Unless you list in all of your logical files all columns/fields).

For the future I'd suggest for file access to move to embedded SQL. Instead for accessing the physical file/table directly you should add an additional view, which represents at first the table with all columns. All other views should be then build on the top of this base view. Instead of accessing the table/physical file itself you should access views. And even more you should not use SELECT * but explicitly list the columns you need in your programs. 

So when extending the underlying physical file/table, you only have to change the programs where you need the additional columns. All other programs do not even have to be recompiled.

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Hi Birgitta, Hi Satiq,

many ant thanks for your answer, 

we have to study the best solutions to avoid critical business impact.

thank you at all 

------------------------------
Paolo Salvatore
------------------------------

Original Message:
Sent: Thu October 12, 2023 06:48 AM
From: Birgitta Hauser
Subject: Change PF/Alter table strategy

If you are "only" adding columns and all your access is with native I/O, IMHO the best solution is to do a CHGPF and recompile after all your programs.

I'd not suggest LVLCHK(*NO) even if it may work if you add the new columns at the end of the column list.But it is always a risk.

A lot of companies create additional tables/physical files for those enhancements that are joined with the unique key with the "original" table ... but finally they end up with tens (or even more) of additional tables/physical files. Which then have to accessed in the programs (if you need the additional fields). So you may end up with hundreds of additional code lines for accessing all the additional tables when you need one of the additional fields. (Which makes the programs not easy to maintain!)

If you decide adding a new logical file which should be accessed instead of the physical file. Keep in mind that all other logical files point to the original physical file ... so, if you access any of the logical files (with native I/O), the additional columns are included! (Unless you list in all of your logical files all columns/fields).

For the future I'd suggest for file access to move to embedded SQL. Instead for accessing the physical file/table directly you should add an additional view, which represents at first the table with all columns. All other views should be then build on the top of this base view. Instead of accessing the table/physical file itself you should access views. And even more you should not use SELECT * but explicitly list the columns you need in your programs. 

So when extending the underlying physical file/table, you only have to change the programs where you need the additional columns. All other programs do not even have to be recompiled.

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
------------------------------

Hi Paolo,

I agree with Birgitta and would avoid LVLCHK(*NO); that's just asking for trouble in the future.

I assume this change doesn't have to go live so soon so I'd suggest two methods of attack.  

Assuming the source code for all your production programs are in limited locations, you should use the PDM offering and search ALL of the source members for the name of the physical file and search for the name of each of the logical files over the physical file. Otherwise, you can list ALL of the source files in your system and search all of their members for the physical file and the logical files. 

Do you have your files journaled? If not, you could just journal this one file and, as you are interested in knowing what reads the file as well as what updates, you could just journal for Open and Closes in the journal (in normal situation, I'd recommend omitting those entries and just journal updates - mind you, should you consider what updates the file and make appropriate changes for the new fields). What you should then do is look to create a new receiver each morning to give a clean cut; following that, display the journal output from yesterdays receivers (the system may have auto generated new receivers) so that you can query the output to display a list of objects referencing the physical file - over time you will build up a list of objects. The risk with this option is that you will miss the jobs that run periodically, like end of month or less often but it's still better than nothing.

If you're interested in more details on how to perform either of these steps, let me know. 

You mention that you don't have any change management system. To deploy this change which consists of many programs, I would suggest you create a new (temporary) library, create a copy of the new version of the file (it does not need any data yet) - take care creating the logical files (make sure they are are pointing at the new file). 

Set your library list to be a copy of production and put this new library at the top (so it is ahead of any production libraries and the new version of the file is referenced); compile the programs that reference this changed file into this temporary library.   

When you are ready to deploy, you can copy the original file to the new file using the format options (*MAP *DROP). 

Check the number of records match. If they do, move the original file to another (temporary) library - back this up afterwards and, if you have enough disk space, keep this online for a month or two just in case of any issues and you need to analyse the original data. You should also move all the original programs to this temporary library. 

NOTE: From WRKOBJPDM, use the user options in PDM; set up one user option command to move the original objects from the production library to the 2nd temporary library and set up another user option command to move the new object from the 2st temporary library into the production library. You can then do WRKOBJPDM on the library holding the new objects, execute the command to move the original objects (enter the code against the first object and use F13 to repeat). Deal with any issues. Once everything moved, execute the command to move the new objects. You should end up with the first library being empty.

------------------------------
Tony Davis
------------------------------

We are using an ERP package that has been around for multiple decades.  Sure, it's been through a few evolutions.  Basically it uses DDS for most of it's physical and logical files.

The physical files have no keys.  All access is done via logical files.  No logical file does a 'select all'.  Instead they list each field.  The nice thing about this is that you can add a column to the physical file and neither the logical file nor any programs using the logical file has to change or be recompiled.  I went hog wild and converted one of their simple base tables to a temporal table and added multiple columns to facilitate this.  Stuff like timestamps, etc.  I didn't change any of the existing logical files and none of their programs skipped a beat.  I think I have all this in a Power Point from when I used to do some speaking.

You can hose this up if you're one of those who do a SELECT * FROM MYTABLE INTO :myextDS and the external DS is based off of the PF as you will now have to recompile since the format of the DS will have changed.  IBM flip flops from release to release as to whether this very situation is a fatal error or simply an informational one.

------------------------------
Robert Berendt IBMChampion
------------------------------

It depends of course by many local details and implementation, but I tend to consider lvlchk *no as a temporary measure while one fix programs in order to introduce gradually some changes in a running system in a well planned manner.

Regarding the "additional table" solution, yes, it is a solution seen many times to avoid risks (but you introduce overhead in the future and risks anyway).

It works. But if the added fields are "semantically" related to the existing ones (es. production data) is it worth IMHO to take time to extend the original table.

Also, and many times this is not given enough thought, a secondary table it introduces different lock semantics. One can lock the primary (because uses only such fields) but not the second, but the information can be related and so one can expect reasonably to be protected by the same concurrency level.

IMHO a secondary table is worth only if the data is clearly a different set of infos (say purchasing data vs logistics data) worthy of introducing two record, pieces of information with separated lock and concurrency infos.

If one have sources and know, adding a field, with the right planning, is not so bad. Take time and the customer should understand this to reduce risks.

Search sources...

Use DSPPGMREF...

Query SYSPROGRAMSTMTSTAT to maybe spot a nasty embedded SQL that uses * or a reference to a DS based on the file subjected to change etc...

my 2c

------------------------------
--ft
------------------------------