Re: [EXTERNAL] Re: Expanding a field in a file or adding a new field to the file -- MIDRANGE-L

Rob's got a good example...

The only other detail I'd call out is that every existing logical needs to
be pointed to the new table and if they don't already have an explicit set
of columns, you'd add the original set of columns to them.

The end result is that every existing named objects, still exists and with
the same record format ID.

Thus no recompilation of programs needed.

The articles others have linked to provide a good step by step.

But while this technique is often used to move from DDS to DDL, and adding
for instance identity columns...

The idea of having a logical data layer between your programs and the data
has been around for a long time. As Rob also pointed out with respect to
Infor's design.

Charles

Charles

On Wed, Jul 31, 2019 at 2:09 PM Rob Berendt <rob@xxxxxxxxx> wrote:

No. All wrong.

Let's say your original table (MYTAB) looks like this
R MYTABR
COLA 2A
COLB 2A
COLC 2A
COLD 2A
...
So you copy this to NEWTABLE.
Change the record format to NEWTABLER
Add your new column
COL23 2A
Now you create a logical file called MYTAB over NEWTABLE. You use the
record format MYTABR and specify all the fields listed in NEWTABLE except
the new one. You give it the record format name of MYTABR.
This new logical file will have the same record format id that the old
table MYTAB did. No programs which aren’t using COL23 need to be
recompiled.
Instead of accessing NEWTABLE a better practice would have you create an
additional logical file, sample MYTAB01. This would contain the new
column, COL23. You would use this in the programs which need COL23.
A better practice is creating a logical file which only contains the
columns needed by the programs which need COL23.
A better practice is foregoing all these concerns about record format
level id's and trash using F specs in your programs and convert to SQL. If
you increase the size of a column then your only concern is to match work
fields in your programs to the new attributes. Well, that and output
columns.

Never use SELECT * in SQL. Always select the columns individually.

InforLX never uses the same record format in their logicals as their
physicals. Their logicals pick the columns individually. Their RPG never
accesses the physical tables directly. I have added columns to the base
physical and never had to recompile their programs, or their logicals.
Makes it real nice to add temporal support to their base physical files.
As much as I think they could improve their database with primary key
constraints, check constraints, referential constraints, etc this part
works out nice. I guess the only thing nice I can say about no key on the
physical is that it discourages people from coding to that directly.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Wednesday, July 31, 2019 3:49 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: [EXTERNAL] Re: Expanding a field in a file or adding a new
field to the file

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.

Thanks for the reply Mark
Now that this idea has sunk in a little more, here is where I am having a
problem Tell me where I am going wrong

I change the physical to add a new field (called - NEWTOKEN) Create new
logicals - defining all the fields in the physical (including NEWTOKEN) and
use these logicals where the new field NEWTOKEN is required

Here is the point I need to make
The logicals that are already built - but do NOT define ANY field - will
be recreated to include the NEWTOKEN field and will have an new level
identifier - correct?

That means the programs that use these logicals will still need to be
recompiled OR Change the level check on these logicals to *NO

Im not saying that this is a showstopper for this process - just trying to
cover all bases BEFORE work is started where I end up saying -
HMMMMMMMMMMMMMMMM

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
‘If you're going through hell, keep going.’
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Mark Waterbury
Sent: Wednesday, July 31, 2019 3:38 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: Expanding a field in a file or adding a new
field to the file

Alan, and Charles,

Do not force the issue of converting from DDS to SQL DDL ... at least, not
"right away" ...

In this case, for "legacy" applications, you can still use DDS to create a
Logical File that has the exact same record layout, record format level ID,
and it will have an index and so "native" Record I/O (e.g. RPG SETLL, READE
etc.) will all work just fine.

This technique has been available since the days of the System/38 and CPF,
and I am very surprised that more people are seemingly not aware of this
method to avoid having to recompile all of the programs that use the "base"
physical file, every time you want to add or change a single field or
column in a database physical file.

Hope that helps,

Mark S. Waterbury

On Wednesday, July 31, 2019, 3:07:22 PM EDT, Charles Wilt <
charles.wilt@xxxxxxxxx> wrote:

Sure it will...

Assume you have 100 programs that access the table now.
But only 10 actually do anything with the CC token column...

Instead of recompiling and testing 100 programs, you need only recompile
and test 10.

Only catch is RPG programs that use RPG record level I/O through an SQL
index (instead of a keyed logical file) Those would also need to be
recompiled and tested as explicitly including columns in an SQL index
changes the record layout...but that would only be needed this time. (Time
time you change the table, you're golden.) I thought I had an RFE to
change this...but guess not..

Charles

On Wed, Jul 31, 2019 at 12:19 PM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Thanks Sam
That article definitely helped
However, Im still not 100% sure this will help in what I need

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Sam_L
Sent: Wednesday, July 31, 2019 2:08 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: [EXTERNAL] Re: Expanding a field in a file or adding a
new field to the file

This article *might* help understand what Charles is suggesting:

https://www.mcpressonline.com/programming/rpg/use-a-logical-file-layer
-to-minimize-recompiles-from-field-additions

Sam

On 7/31/2019 2:00 PM, Alan Shore via MIDRANGE-L wrote:

Hi Charles
Im not too sure I have an understanding on this

I tried to create some type of diagram, following what you suggested
- but its NOT working out Any chance you could make it simpler for a

simpleton like me?

Alan Shore

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com