Re: Create DDL from exiting PF&LFs

Hello, all:

I just want to add a cautionary note about this topic ...

You should be careful if you have many logical files that share access
paths, because the "rules" for how access paths may be shared are much
stricter for DDL, to conform to ANSI standard SQL. So, if you just "close
your eyes and step on the gas" and convert all of your PFs to SQL DDL and
then attempt to convert all of your LFs to a view and an index, the SQL
indexes are NOT normally shared.  This could mean that you will now incur
much more overhead in index (access path) maintenance.

Worst case analysis: say you have a PF with 15 LFs over it, and the 15 LFs
were carefully constructed or created with the longest key sequence first,
so that, in theory, all 15 LFs could share just one access path.  The
problem is, with SQL, you will have 15 separate indexes that are not shared,
and thus, every time you update a record, the system must update 15 indexes,
versus just a single update of one shared access path with the DDS-created
LFs.
At the last few COMMON conferences, I had a chance to discuss this very
topic with some IBM developers who work on SQL and DB2 on the iSeries, and
they confirmed this.

So, you might want to do some analysis on your PFs and LFs first, to
determine how much access path sharing you really have, before you "rush in"
and convert everything to SQL.

Regards,

Mark S. Waterbury

----- Original Message -----
> From: "Jon Paris" <Jon.Paris@xxxxxxxxxxxxxx>
> To: <midrange-l@xxxxxxxxxxxx>
> Sent: Sunday, July 03, 2005 12:15 PM
> Subject: RE: Create DDL from exiting PF&LFs
>

> Thanks for the reference Joe.
>
> It is an interesting article and answers a number of questions relating to
> some of the rumors floating around that I hadn't had time to check out.
>
>   >> The old-style DDS files do the checking on the read.
>
> This is the only problem I have with the piece.  As far as I know the DB
> does not do any checking.  The application itself simply "blows up" when
> attempting to use the bad data.  That's why COBOL apps don't suffer as
badly
> as RPG from DD errors - if they don't touch the field they don't blow up.
> RPG on the other hand moves every field from the buffer to its storage
> location.  It is that move that causes the explosion on the read.
>
> I do agree though that having it done on the write is great - wonder what
> error RPG signals if the data is bad?  I'll write a test case sometime
when
> I have 5.
>
>
> Jon Paris
> Partner400
>
> www.Partner400.com
> www.RPGWorld.com
>
>
> --
> 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.
>