Replace physical file with SQL view

[Cybso001]

Hello,

I'm an RPG newbie and seek some help :-)

We have a large physical file with over 200 columns and want to split that in multiple smaller files. To keep migration steps small I had the idea to move the data into the new files and replace the old physical file with an updateable view (via trigger).

While this works for programs that access the data via SQL it fails for RPG programs with error RNF7081 because of "missing common key fields".

I assume this is because a logical view defines key fields while an SQL view does not (and CREATE INDEX is not valid on views).

Is there any workaround for this problem or a "best practice" for migration a large table into multiple smaller ones in RPG context?

Best regards,

Roland

[JonBoy]

Could you clarify WHY you feel the need to do this? I have clients with multiple millions of rows and it doesn't cause any issues.

[Cybso001]

Not rows... columns :-)

For example, our article data file has around 250 columns, some of the kinde "XY1, XY2, XY3". These should be splitted into smaller files to reduce the amount of work when columns need to be changed/added and to transpose those "horizontal" values into vertical ones (multiple rows of XY instead of columns XY1, XY2, ...).

Additionally, the old table had a set of three columns as primary key, that we replace with a single ID that identifies each record.

[TedHolt]

When you remove the repeating group from a table and store each “bucket” as a row in a new second table, you’re normalizing to first normal form (1NF). The second table must have the same key fields as the first one, plus another key field to distinguish the rows from one another. That other key field is typically a sequence number, but it doesn’t have to be.

For example, assume a file of patient info:

Patient ID, patient name, phone number, diagnosis 1, diagnosis 2, diagnosis 3, . . . diagnosis n

The patient ID is the key and the diagnoses are the repeating group.

Put the diagnoses in their own table, one row per diagnosis, and key it by the patient ID and a sequence number.

Patient ID, sequence, diagnosis

Or you may not even need the sequence if the diagnosis code can distinguish the rows for a patient one from another.

Patient ID, diagnosis

The “common key field” that your error message says is missing would be the patient ID in this example.

[Cybso001]

@TedHold I wish I already came that far, but I'm still at the proof-of-concept phase.

Today I failed to reproduce the message I got yesterday, so I've started over with a VERY simplified example.

The table/pf is:

create table foo (
    key1 numeric(2),
    key2 char(6),
    key3 numeric(3),
    someAttribute char(10),
    otherAttribute char(10),
    primary key (key1, key2, key3)
);

The view is extremely simple (the real view will contain many joins and an UPDATE-Trigger):

create view bar as select * from foo;

The RPG is:

     FFOO       IF   E           K DISK    rename(FOO:FOOR)
     D wait            S              1A
     C     KFOO          KLIST
     C                   KFLD                    key1              2 0
     C                   KFLD                    key2              6
     C                   KFLD                    key3              3 0
     C     KFOO          CHAIN     FOOR                               80
     C     'success'     DSPLY     ' '           wait
     C                   SETON                                        LR

(At this stage of the PoC it doesn't matter that the file is empty and if the chain was successfully or not.)

This program works and prints 'success' on the 5250.

However, if I replace 'FOO' with 'BAR' (which is what I intend to allow small migration steps instead of re-writing literally hundreds of legacy RPGs at once), I cannot compile the program:

     FBAR       IF   E           K DISK    rename(BAR:FOOR)
     D wait            S              1A
     C     KFOO          KLIST
     C                   KFLD                    key1              2 0
     C                   KFLD                    key2              6
     C                   KFLD                    key3              3 0
     C     KFOO          CHAIN     FOOR                               80
     C     'success'     DSPLY     ' '           wait
     C                   SETON                                        LR

The message for line 7 (CHAIN) is:

RNF7075: Eine Operation mit Schlüssel wurde für eine Datei ohne Schlüssel angegeben.

I'm not sure what I did yesterday to get the RNF7081 message, but this one is more clear to me: the view has (in contrast to logical files) no key fields.

Of course, I could replace the view with a logical file for this simplified example, but not for the real table, where not only the normalization has to be "reverted" for the legacy programs but also some columns that originally referred to the triple-keys need to be translated from the new numeric ID fields.

So, is there any workaround to replace a physical file with an (updatable) SQL view in legacy RPG programs? Or won't this work and we really need to rewrite all programs at once to normalize our data tables?

[TedHolt]

I don’t see any way to avoid rewriting your programs to use the view, Cybs. Here’s one method I’ve used that may ease the pain.

• Write a callable routine (program or a subprocedure in a service program) to handle the I/O. I much prefer the service program. Let’s call it BAZ.
• Write two versions of the BAZ routine – one that accesses table FOO and one that accesses view BAR. Both have the same parameter list.
• Test both and make sure that they return the same data.
• Put the BAZ routine that accesses FOO into production.
• Change the RPG programs to call BAZ instead of chaining to FOO. Do them one at a time and put each one back into production when it passes testing. Do NOT wait and install all the changes at once. If something goes wrong, you’ll have a disaster.
• When all programs have been changed to call BAZ, install the version of BAZ that accesses the BAR view.

[Birgitta Hauser]

Writing procedures which perform the insert and update would be the best idea.
But I'd pass a data structure with the same fields of your view/former PF. Populate this data structure in your programs.
Then in your procedure you can currently do the insert/update for your current file ... and then in future you just can change your Insert/Update procedure so instead of a single file multiple files are updated.

Birgitta

[Birgitta Hauser]

If you do not want to split your big table into smaller ones and do not want to externalize the data access in hundreds of RPG programs, you may intent to create a good old JOIN LF (with the same fields as the original physical file). Since a JOIN File can have a key (contrary to a view), you can use it in RPG.
... then you have time to rework step by step your RPG programs, so you can finally remove the JOIN LF again.

Birgitta

[Cybso001]

Thank you for your feedback. I think we will try to take a middle course: Programs that access the file in write mode will be switch to the new files, as well as programs that access columns that do not have a 1:1 mapping. For the rest we will build a new logical file, which contains only the 1:1 mappable fields.