Re: Different Physical File Attributes. -- MIDRANGE-L

Rob, thanks that is very helpful.  I still have one additional question.

The following is true for all JDE tables on our AS400:
JDE OneWorld does not use table constraints.  If I view table properties in
iSeries Navigator, there are NO contraints listed under the constraints tab.
All uniqueness is handled by unique indexes (one or more) defined for the
table.  Again, this is true for ALL tables whether they be arrival sequence
or keyed sequence.

As an aside, I believe JDE does this for one or both of the following
reasons:
1) OneWorld attempts to be RDBMS agnostic (SQL Server, UDB DB2, DB2/400, and
Oracle all supported).  A unique index is a unique index.  Maybe constraint
languages/rules (eg.  DDS on DB2/400 and DRI on MS SQL) vary too much.
2)  I'm not sure about Oracle, but both DB2 and MS SQL Server enforce unique
constraints via unique indexes anyway...so why not just specify all indexes
(unique or not) in the same place instead of offiscating the method of
actual enforcement.

Now, based on what you stated on keyed sequence, if the UNIQUE keyword had
been specified during the creation of the physical file, I get the
impression I would see a table constraint.  Since, I don't see a table
constraint, I'm assuming the keyed access path serves some other purpose (as
you mentioned).  Is it that the keyed sequence (minus the UNIQUE keword) is
dictating what order the table is physically stored on DASD?

You see, I'm trying to figure out why JDE used both a non-unique keyed
access path AND unique indexes with the same physical file.  In the SQL
Server world, I would say it was because queries were expected to return
ranges (of the keyed access path) and the intention was to ellicit a sorted
index scan with a cooresponding somewhat sequential I/O and/or parallel
prefetch of data against the table.

Is this probably the idea? Uunderstand that I am not asking you to read the
mind of JDE...just to comment on the design practice as it pertains to
DB2/400.

Thanks.

Now, based on what you stated regarding the keyed sequence

<rob@xxxxxxxxx> wrote in message
news:OF44935F2E.5D73B324-ON8525714D.00709CB2-8525714D.0071CA12@xxxxxxxxxxxx
> I really don't know your background so it's hard for me to talk in
> language you'd understand.
>
> If you are familiar with SQL then a table created with a primary key
> constraint will have a "key".  For example
> CREATE TABLE RYAN
> (MYKEY CHAR ( 5) NOT NULL WITH DEFAULT,
>  MYDATA CHAR ( 15) NOT NULL WITH DEFAULT,
> PRIMARY KEY (MYKEY))
>
> With a table set up this way you cannot have two rows in the table with
> the same value for MYKEY.  Also, if you use a HLL language like cobol or
> rpg you could retrieve records by that key using direct language
> attributes.
>
> If you are familiar with DDS it would be more like
>                                             UNIQUE
>                 R RYANR
>                   MYKEY          5A
>                   MYDATA        15A
>                 K MYKEY
> The file level keyword UNIQUE says there can only be one row where the
> fields declared keys by the K (as seen in front of MYKEY) are alike.
>
> Now, you could have K fields without UNIQUE.  And instead of being like a
> primary key constraint, it's more like combining a table with an index.
> Understand?
>
> Now, if you do not have any keys, or primary key constraints, then when
> you retrieve rows from the table they are normally retrieved in the order
> they were deposited in the file, or, "arrival sequence".
>
> Rob Berendt
> -- 
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 04/11/2006 04:10 PM
> Please respond to
> Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
>
>
> To
> midrange-l@xxxxxxxxxxxx
> cc
>
> Subject
> Re: Different Physical File Attributes.
>
>
>
>
>
>
> I think you are on to something.  F0907 with the Access Path tab has DSPFD
> Access Path = "Keyed".  F0911 with DSPFD Access Path = "Arrival" does not
> have the tab.
>
> What is this "Keyed" and "Arrival" access path attribute?
> Thanks.
> RH
> <vhamberg@xxxxxxxxxxx> wrote in message
>
news:041120061850.17483.443BFA66000BC3620000444B2206999735099D0A0D030E0890@xxxxxxxxxxxxx
> .
> > I'll guess that the second is in arrival sequence. DSPFD lists that as
> its
> access path, although one could say arrival sequence is not an access
> path.
> >
> > The other is about fixing the access path when it gets damaged somehow,
> IIRC.
> >
> > There is a question mark in the title bar - click it and then click on
> whatever you want to know about - a tool tip window appears.
> >
> > HTH
> > Vern
> >
> > -------------- Original message -------------- 
> > From: "Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>
> >
> > > We're a JDE OneWorld customer so my question pertains to tables that
> we
> part
> > > of an install I know nothing about. I have two different tables that
> have
> > > different attributes when viewed in iSeries Navigator.
> > >
> > > The first file/table (F0907) has an additional tab when I view the
> > > description of the object. The tab is called "Access Path". The
> > > maintenance is set to "Do Not Wait" - which I assume means to maintain
> > > indexes as the physical file is changed. There is also a "Recovery"
> > > attribute with a value of "During IPL" - I'm not sure what this means.
> > >
> > > The second file/table is F0911 and it does not have the "Access Path"
> tab.
> > >
> > > Does anyone know what causes the difference?
> > >
> > >
> > >
> > > -- 
> > > 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.
> > >
> > -- 
> > 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.
> >
> >
>
>
>
> -- 
> 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.
>
>
> -- 
> 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.
>
>