Re: Articles or links to build a case on why to improve your rpg-db2 database.

The following may be dated but I just noticed I didn't send it...




We've done those related files also. Picture IIM2, add more columns make
IIM3. You would think that on a custom file like IIM2 we could control
which programs use that and recompile all those when we wanted to add more
columns to that instead of adding IIM3. IDK why that wasn't done but it's
been a long time since I've been that deep into their stuff. More of a
systems guy now.

Which, when you consider that IIM is an Infor Item master file and they
never access the table using RLA and only access it via the indexes (LF's)
when using RLA it's not really necessary.
They don't even have a key on that table. All keys are in the indexes and
they never specify unique. And, yes, if you add rows outside of their
programs (like when doing a conversion, or combining databases) it's quite
easy to have duplicate keys.

We've actually tested adding new columns to the RCO (for use with temporal
tables). We added the new columns using ALTER TABLE RCO ADD COLUMN... We
did not recompile the indexes. We did not recompile any of their
programs. None of their programs blew up.

We spend a lot of money and time on a new package and training to query
their data on a periodic basis. The current roll out is Cognos. I often
think that if we just created some decent views it would really help. For
example, the users REALLY don't want to have to take IOPB+IADJ+IRCT-IISS
just to get on hand. A decent view would get:
- Reasonable column names
- Meaningful table (really view) names
- Calculate the virtual columns, like on hand.
- Join the files together, like: ECH - Order header, ECL - Order line, IIM
- Item master, RCM - customer master, and so on...

They also have no referential integrity. Want to create a line with no
order number? UPDDTA can easily do that. This you might have to be
careful on. If they don't delete the relations in the right order then it
can cause program issues. I believe that a good tool, if the database is
defined with good referential integrity, can visualize the joins itself.



Rob Berendt