Re: Logical File or OPNQRYF or any other way ? - Legacy

So if I create a database:

CREATE TABLE order (
  record CHAR(1000) NOT NULL)

and populate it like this:

INSERT INTO order
  VALUES('00001001Item1002Item2003Item3004Item4')

or maybe
UPDATE order SET record =
    order# || line1 || item1 || line2 || item2 || line3 || item3)

or even more silly:
UPDATE order SET
  SUBST(record,1,5) = order#,
  SUBST(record,6,3) = line1,
  SUBST(record,9,5) = item1,
  SUBST(record,14,3) = line2,
  SUBST(record,17,5) = item2,
  SUBST(record,22,3) = line3,
  SUBST(record,25,5) = item3,
  SUBST(record,30,3) = line4,
  SUBST(record,33,5) = item4

where the first 5 positions is order number, 
      the next 3 are line number,
      the next 5 are item number,
      the next 3 are line number,
      the next 5 are item number,

 and so on, you will claim that it is normalized because it is an SQL database?

I don't think so.  It is just an example of how someone that doesn't know
database design will mess up even an SQL database.

DB2/400 is a valid relational database, IMO, because it allows for the DDL to
create the database.  Just because it has features to create the database with
non-DDL (i.e., DDS) doesn't make it any less a relational db.

I do group operations all the time with DB2/400 within RPG... it's called using
the SQLRPG compiler...  I create tables the same way on occasion.  You're
saying that because DB2/400 is a SUPERSET of SQL, it's really not relational. 
Hmmm...

I hate to burst your bubble, but under the covers, if you do a DELETE filename,
the SQL engine deletes each record one-at-a-time.  There's no magic going on...
only the high level simplification of the request.  Delete a million-record
file using SQL and see how long it takes.  You can even go green-screen and
watch the number of deleted records in the file (oops! er, TABLE) increase as
the little engine remoces the records.

If I make a change to the database, yes, I will have to modify (typically,
recompile) my RPG programs.  So what?  That's part of the process.  The fact
that the database is bound to the program gives RPG its well-deserved
reputation for random-access performance.  That is the design tradeoff for a
business machine.  If the data layouts are changing all the time, well, what
kind of model is the business operation on?

My point is that the tools are as good as the developer.  I've seen designs as
whacked out as the example I gave above by folks.  My rule is "learn the
concepts and don't argue about the implementation."  Clean design wins in my
book every time.

William


> 
> date: Tue, 8 Mar 2005 14:34:05 -0500
> from: "Paul Morgan" <pmorgan@xxxxxxxxxxxxxx>
> subject: Re: Logical File or OPNQRYF or any other way ? - Legacy
> 
> Joe,
> 
> IMHO DB2 is and isn't a relational database.
> 
> If you access the database using RPG you're using DB2 as a flat-file system.
> RPG access to the data violates many of Codd's rules (Five, Seven & Eight at
> least).
> 
> Five because you're using RPG instead of SQL to access the database.  You
> can't do everying with the database using RPG.  You can't create tables
> using RPG.  You can with SQL.
> 
> Seven because you can't do group operations against the database with RPG.
> I can't delete all records satisfying a condition as I can with SQL.  I can
> only delete one record at a time.
> 
> Eight because an underlying change to the database will require changes to
> RPG programs.  If the sequence of fields in a table are changed or length of
> fields changed  the RPG program must be modified to deal with the database
> change.  No so with SQL.  SQL doesn't care that that column is the first
> column in the table.
> 
> Having said all that, if you stick strictly to SQL for all your database
> access then the AS/400 database is a relational database.  Until DB2/400
> prevents access to the database outside of SQL it's not strictly relational.
> 
> Can you access Oracle, MS SQL Server, MySQL without using SQL?  No - even
> ODBC uses SQL for data access.  You can with DB2/400 - RPG, COBOL, C, CL
> commands - not good.
> 
> Nothing in Codd's rules say relations between tables must be defined in the
> database.  Codd's definition of relations is between the columns in a table.
> The values in a tuple/row are related - thus the term.  To relate tables you
> combine them to make other tables/sets.
> 
> Paul
> 
> -- 
> Paul Morgan
> Senior Programmer Analyst - Retail
> J. Jill Group
> 100 Birch Pond Drive, PO Box 2009
> Tilton, NH 03276-2009
> Phone: (603) 266-2117
> Fax:   (603) 266-2333
> 
> "Joe Pluta" wrote
> 
> > 1. Please define exactly what characteristic gives a file "depth", and
> > how that is not supported in DB2.
> >
> > 2. Name a database that is relational.  Identify which of the Codd rules
> > it follows that DB2/400 does not.
> >
> > 3. Where don't we compete?  As far as I know, DB2/400 is completely ANSI
> > compliant.  Please explain where it falls short.
> 
>