Re: PF Compiled Files with Dictionaries vs. SQL-Created Tables

[Jason Abreu <jason.abreu@xxxxxxxxxxxxxxxxxxxx>]

Perhaps you are confusing referential integrity with SQL Views and DDS 
Logical Files?

Referential integrity is visible by the relationships between SQL Tables 
(or DDS physical files).  This is accomplished by a foreign key 
constraint on the file.


Jason Abreu
Abreu Innovations, Inc.
jason.abreu@xxxxxxxxxxxxxxxxxxxx
http://www.abreuinnovations.com/

On 2/2/2011 10:12 AM, Mike Cunningham wrote:
> So ADDPFCST actually creates a logical file? What do you use on the "F" spec in RPG in this example?
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Wednesday, February 02, 2011 9:51 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: PF Compiled Files with Dictionaries vs. SQL-Created Tables
>
> You're right, referential integrity is not supported by DDS.  You can add it after the fact with ADDPFCST.  But remaining with DDS and using ADDPFCST is dangerous because it implies some sort of MAKE tool and when you get that big pay raise and promotion with a move to another plant and that new hire with 20 times of 1 years experience of i comes in to replace you he will just see the DDS and try compiling that.
>
> ADDPFCST is cool for adding keys to output files.  DSPUSRPRF followed by ADDPFCST and boom, traditional RPG can do a CHAIN by user profile.  No need to create a DDS LF.  There are solutions to workaround format level checks after your os upgrade you need to be aware of.
>
>
> Rob Berendt
> --
> 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
>
>
>
>
>
> From:   Jason Abreu<jason.abreu@xxxxxxxxxxxxxxxxxxxx>
> To:     midrange-l@xxxxxxxxxxxx
> Date:   02/02/2011 09:05 AM
> Subject:        Re: PF Compiled Files with Dictionaries vs. SQL-Created
> Tables
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Isn't referential integrity one feature supported by DDL and not by DDS?
> I can't say I've ever seen a foreign key constraint in a DDS source file.
>
> Jason Abreu
> Abreu Innovations, Inc.
> jason.abreu@xxxxxxxxxxxxxxxxxxxx
> http://www.abreuinnovations.com/
>
> On 2/2/2011 8:20 AM, Vern Hamberg wrote:
> > I found that the website listed for NiceSQL seems not to come up today -
> > it's been years since I tried. The product is downloadable - haven't
> > tried to buy it lately - CNet has it for download - just google for
> > NiceSQL if interested.
> >
> > On 2/2/2011 6:53 AM, Vern Hamberg wrote:
> > > Chuck
> > >
> > > There are SQL formatting apps out there - I use NiceSQL - that take any
> > > statement and "pretty-print" it. Very nice for readability.
> > >
> > > NiceSQL is very nicely supported - several years ago the developer even
> > > added stuff for AS/400 - was it that long ago? Maybe it was iSeries.
> > >
> > > Anyhow, 20 bucks - free to try. It's been good for me, quite satisfied
> I
> > > am. Simple example -
> > >
> > > before:
> > > SELECT DISTINCT CR.COLOUR_ID, DBO.GETRECIPE(CR.COLOUR_ID,
> > > CR.RECIPE_ID, CR.RECIPE_VERSION) AS RECIPE FROM
> > > PRODUCT P, COLOUR_RECIPE CR WHERE (CR.COLOUR_ID = 11000001005 AND
> > > P.STATUS_ID = 'P')
> > >
> > > after:
> > > SELECT
> > > DISTINCT
> > > CR.COLOUR_ID,
> > > DBO.GETRECIPE(CR.COLOUR_ID, CR.RECIPE_ID, CR.RECIPE_VERSION) AS RECIPE
> > > FROM
> > > PRODUCT P,
> > > COLOUR_RECIPE CR
> > > WHERE
> > > (
> > > CR.COLOUR_ID = 11000001005 AND
> > > P.STATUS_ID = 'P'
> > > )
> > >
> > > Vern
> > >
> > > On 2/1/2011 7:05 PM, CRPence wrote:
> > > > On 2/1/11 1:13 PM, hockchai Lim wrote:
> > > > > I know I'll probably get blasted for saying this. But I must admit
> > > > > that I've never been a big fan of using SQL to create tables. I
> > > > > understand that using SQL will allow me to create table with feature
> > > > > that is not supported by DDS (such as Identity column). But that is
> > > > > IBM's fault not mine :). The main reason I didn't like SQL is because
> > > > > it strains my eye to read that SQL create table statement. DDS is
> > > > > just much neater and easier to read.
> > > > And DDS even supports some features which SQL does not. I find
> > > > little value to change from DDS to use SQL DDL for existing database
> > > > *FILE objects, without some obvious need to access some new feature
> > > > provided by the TABLE; e.g. either data types supported only in the
> SQL,
> > > > or better integrity for numeric data. Without any specific goal to be
> > > > achieved by changing, what point is there in change except change for
> > > > the sake of change? That is, change without justification is
> > > > intuitively, not justified. Best to understand what will be lost and
> > > > what will be gained by the change, to avoid remorse for having made
> the
> > > > jump, and even more regrets for having to move back in order to
> > > > "recover" from the first change. Changing "just because" to using SQL
> > > > DDL versus DDS is, for lack of a better word, daft.
> > > >
> > > > While many DDS PF source may be very clean and concise, I mostly
> > > > disagree that DDS specifications would be generally easier to read
> than
> > > > the SQL, except to those who have some experience coding to
> fixed-format
> > > > source specifications. I think difficulty in reading the SQL DDL would
> > > > probably be caused mostly by poor formatting of the statements,
> > > > exacerbated by [typically self imposed] limitations to line length.
> > > > FWiW the LABEL ON statement for column headings and text being moved
> > > > outside the functional field definitional attributes could be
> considered
> > > > by many to be much nicer than having them intermingled and even
> > > > line-wrapped from and back into the functions area of a DDS source
> > > > member.
> > > >
> > > > Regards, Chuck