Re: Record format ID not the same for INDEX with ADD column-name specified

Chuck,

Sorry for the delay getting back to this...I wanted to wait till I had some
time to do some testing.

I don't believe that RCDFMT alone achieves independence for the index as
judging by the docs and the SQL source generated, RCDFMT by itself is the
same as
RCDFMT <name> ADD ALL COLUMNS ;

Which means that if the table is changed at a later date and at some point
the index is recreated, then the index will suddenly get the new column and
any RPG programs will get a level check.

Thus, for an SQL index to maintain its independence, you
must explicitly add the columns just like with a DDS LF. However, when
you explicitly add columns to an SQL index, keys always show up first
followed by the rest of the named columns...even if you have the the keys
columns someplace else in the list of columns..

This is fine if the index is brand new. But if you are trying to replace a
DDS logical, it is a problem.

Example:
A* ---- Physical: MYPF
A UNIQUE
A R MYPFFMT
A PRIKEY 10A
A FLD2 10A
A FLD3 10A
A ALTKEY 10A
A FLD5 10A
A K PRIKEY

A* ---- Logical: MYLF
A UNIQUE
A R MYLFFMT PFILE(MYPF)
A PRIKEY R
A FLD2 R
A FLD3 R
A ALTKEY R
A K ALTKEY

Now you can easily replace the PF with a SQL DDL defined table...

But you can not replace the LF with an index...
This
create index mylf
on mypf (ALTKEY)
rcdfmt MYLFFMT;

Gives you a *LF that includes FLD5.

And this
create index mylf
on mypf (ALTKEY)
rcdfmt MYLFFMT
add PRIKEY
, FLD2
, FLD3
, ALTKEY;

Gives you a LF with ALTKEY as the first column...not the last.

Charles


On Tue, Nov 27, 2012 at 2:24 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 27 Nov 2012 12:14, Charles Wilt wrote:

No takers on this one?

On Sun, Nov 25, 2012 at 9:37 PM, Charles Wilt wrote:

<<SNIP>>
However, I know that I'll be making changes to the new table and I
wanted to go ahead and make the record format of the index/logical
static by using the ADD column-name clause:
<<SNIP>>

I guess for "heritage" DDS files, I'll just need to stick to DDS
if I want a static format unless I'm willing to recompile existing
programs one time.

Unless somebody knows of something I'm missing?

I was not sure I understood the entirety of the message, so the reply
I originally started composing was filed. I think I would have better
understood if a v5r4 scripted SQL setup example had been included, to
show what was the basis for what needed to be replaced by the v7r1
scripted SQL.

If the aim is to achieve logical independence [¿a /static/ format?]
from the physical layout using SQL similar to using a DDS LF [effected
by naming the columns], then any program currently compiled against the
PF should be recompiled to reference such an LF instead; just as when
DDS is being used instead. And AFaIK any implication that using the ADD
COLUMN clause is required to achieve independence is not valid; i.e. I
believe the RCDFMT clause alone will achieve that independence for the
INDEX.

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