RE: SQL vs V5R1 and service woes

> -----Original Message-----
> From: thomas@inorbit.com
>
> > If your argument for SQL is that it makes it easier to change
> your database
> > layout, it only holds for "SELECT *".
>
> Can you elaborate on why you say this? Is this only because of
> the specific syntax that was originally discussed?

The original statement was that it in the example it would be easier to add
a field to both files due to the SQL statement.  This in fact is a common
misconception: if you use SQL, you can change your database more easily.

"Easier" in this case meant that you could add a field to both files without
changing the SQL statement.  This is only the case when using "SELECT *",
and my belief is that "SELECT *" really isn't really a common use, except in
a subselect.  (The other use is when you subsequently FETCH into a data
structure, but then you have the same issues as RPG or COBOL.)

The other way SQL is easier is that "you don't have to recompile".  This is
true if you're explicitly naming fields in your SELECT statements, but then
my comment on that is "Uh, so what?".  How often do you change your
database?  How hard is it to then recompile the programs that use the
changed file?  To determine which programs use a given file, you can do a
DSPPGMREF to an outfile and use SQL to determine the programs to recompile
(this, by the way, is a really GOOD use of SQL, in my book!).  Personally, I
prefer the security of format level checking - with SQL, if I forget to
change one of my SQLRPG programs to update the new fields, I don't even get
a warning.  I just find bad data somewhere down the road, and I'm not even
sure why.  It isn't terribly difficult to recompile programs, so SQL isn't
really that much easier, and it's potentially pretty dangerous.

You can avoid (some) of these dangers with database triggers.  While not
capable of handling every business rule, triggers can handle most.  And so a
combination of SQL and trigger programs can induce a level of database
integrity and ease of use that has a certain appeal.  In some ways, they can
even separate the business logic from the physical database by allowing the
triggers to do "invisible" file updates in response to application actions.

However, once you've gone to that much trouble, my personal choice for
separating the business logic from the database is to use message-based data
servers.  This way, you can change the database however you want, and the
programs that perform the actual business logic don't change at all.  With
SQL, if I change the names of my tables or columns, or split files apart or
combine them, or move them to different machines, my SQL statements change.
If what I'm after is the ability to be able to change my database without
having to recompile my application programs, a message-based server is by
far the best way to go.  Using servers, I can have older applications
co-existing with newer applications that take advantage of new features of
the database.  So if you use SQL to "not have to recompile", and triggers to
ensure integrity when your database changes, you might want to go the next
step to an architecture that's completely independent of the physical
location of data.

Joe