ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using CHGPF for adding/removing fields to a file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using CHGPF for adding/removing fields to a file

    I have a Physical file whose size is about 600GB (consists of over 300 million records). It has four logical files with 3 different keyed access paths.
    Each of the logical files has a unique record format and doesn't share the same record format as the physical file.
    Off-late maintenance on these files has become a nightmare.

    Today when we add a new field to any physical file these are the steps - we follow - (in a jist )
    - Drop the exisiting logical files
    - Rename the existing physical file
    - Create a new PF object with the changes
    - Copy the data from the backed up file
    - Drop the backup
    - Re-create all the logical files, with this new field added to its record format.

    Although a single command executes the CL that does all the above mentioned steps, it takes almost a day or two for this entire process to complete.(since the file size = 600GB)

    I'm exploring some options that may reduce the downtime when we have to make changes to this file. (For eg - Vertically splitting this file into 3 with a surrogate key to join)

    Another option I was exploring is to use 'CHGPF' and get the file changes done on the physical file.
    Since today, the logical files don't share the record format with the physical file. Using the above command leaves the logical files with a different format level identifier and the new field unusable via the logical files.

    What happens if the logical files share the same record format with the physical file? Are there any disadvantages/challenges I may face after implementing this?
    This would help me avoid recreating logical files every time I have a new field to be added. This would help me save a lot of downtime.

    Please share your thoughts/inputs on this.
    Also, any inputs/ideas on maintenance of large physical files will also be greatly appreciated

    Looking forward to the responses.

  • #2
    First, it should be very rare that columns/fields are added to (or removed from) tables/PFs. It's a "relational" database, so an early thought ought to be whether or not any additions might be created as part of a "related" table/PF. An added field isn't referenced anywhere in code that currently exists because the field itself doesn't currently exist. By relation, it may be brought into new code by way of a VIEW that presents the JOINed fields only where actually needed. A new LF could share an existing access path if it matches an existing one, so there might be no significant build nor maintenance time. The original physical record format wouldn't change for the life of the app (or probably of the current version of the app). Practically all access could be via VIEWs (or LFs); the tables/PFs would only be directly referenced in a very few (a single?) maintenance module. This does rely on an app design that accommodates it.

    Beyond that, assuming they are native LFs, I'd possibly just use RMVM to remove their members before making a PF change and ADDLFM to add them back after the change if I wanted the indexes out of the way. And I'd submit the ADDLFMs to a multi-threaded *JOBQ so they could run concurrently. Each job usually takes longer to run, but start to finish time for the overall process should be less than if done one after the other. When possible, I prefer removing members to fully recreating files.
    Tom

    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

    Comment


    • #3
      Thank you for your response.

      The table we are talking about is a snapshot table. It maintains Snapshots of how the "related" table looked in a period of last 5 years via weekly/daily/monthly snapshots.
      It is critical for the business to have this information. Hence any new field brought into "related" table will have to be brought in here as well and it causes an issue.

      However, your suggestion is to recreate the Logical Files eventually? (when it is possible)

      My thinking was on the lines, if I let the Logical file share the same record format as the Physical file, I wouldn't have to recreate them at all? Unless, I change the access path on of these logical files.( Which will almost never happen)

      Is it not advisable to do that or Any shortcomings in doing that?

      Comment


      • #4
        "It maintains Snapshots of how the "related" table looked in a period of last 5 years via weekly/daily/monthly snapshots."

        You may want to look into temporal tables.

        Cheers,

        Emmanuel

        Comment


        • #5
          Thank you for the suggestion.

          But what I'm really after lies in this part -
          "
          My thinking was on the lines, if I let the Logical file share the same record format as the Physical file, I wouldn't have to recreate them at all? Unless, I change the access path on of these logical files.( Which will almost never happen)

          Is it not advisable to do that or Any shortcomings in doing that?
          "

          Comment


          • #6
            My thinking was on the lines, if I let the Logical file share the same record format as the Physical file, I wouldn't have to recreate them at all? Unless, I change the access path on of these logical files.( Which will almost never happen)

            Is it not advisable to do that or Any shortcomings in doing that?

            Any inputs on the above question will be greatly appreciated.

            Comment


            • Scott M
              Scott M commented
              Editing a comment
              Do your logical fields explicitly list all the fields that they get from the physical? I am guessing that they don't because the format level identifier is changing. If you explicitly list all the fields then the CHGPF would work but you would have to recreate the logicals when you want to add the new field. You may find that some of the logicals don't need the new fields and could be left alone.

            • VigneshMohan
              VigneshMohan commented
              Editing a comment
              As of now, My LF explicitly lists all fields and have a different record format name.
              The change I'm planning to do is to make the LFs share the same record format as the physical file and get rid of the explicit field lists.
              I will let all the LFs have all the fields and hence bypass recreating logical files every time I add a new field.
              At the same time, I can use any of the LFs in the future with flexibility of having all the fields I'll probably need.

          • #7
            My thinking was on the lines, if I let the Logical file share the same record format as the Physical file, I wouldn't have to recreate them at all? Unless, I change the access path on of these logical files.( Which will almost never happen)
            You are correct. If the logical files share the same record format as the physical file, and you use the CHGPF FILE(filename) SRCFILE(sourcefilename), then the system will take care of the logicals automatically.

            One drawback that I can see is that when you change the record format of the logicals to be the same as the physical, you will have to recompile any programs that use any of the affected logicals any time you change the physical file. By isolating the logicals with their own record format, programs that reference the logicals won't be affected by adding a field to the physical file.

            To make sure it works the way you want, run a test with a small subset of records in a test environment.

            Comment


            • VigneshMohan
              VigneshMohan commented
              Editing a comment
              Great! Much appreciated!
          Working...
          X