Re: SQL vs V5R1 and service woes

-----Original Message-----
From: Joe Pluta <joepluta@PlutaBrothers.com>
To: midrange-l@midrange.com <midrange-l@midrange.com>
Date: Thursday, September 06, 2001 11:08 PM
Subject: RE: SQL vs V5R1 and service woes


>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.

This is not quite true. If you are using SELECT *, then adding fields to the
file means that the program must be able to deal with the new fields that
are added to the files, just like if you were using native I/O, the
structure of the target changes and the program must be recompiled. You
actually allude to that in your next statement.

> (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?

In a real environment? Sometimes more often than you would like to think. In
a leasing firm I once worked at, the lease master file grew from 30 or 40
fields to over 300 fields in the course of two years time. Business changes
and new offerings drove this. We were using native I/O, so recompiles over
the weekends were the standard fare for programmers. And, yes, I know that
some of those fields did not really belong in that file, but that required
more careful examination and analysis and design time than the users were
willing to put up with at that time. It was not a small company.

> 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!).

See my previous statement. Are you kidding? 81,000 objects on the system,
5,000+ using this one file. 8 Million lines of code on-line, 1 million lines
of code active. How hard was it to recompile when only three new programs
were going to use the field? (retorical)

Adoption of SQL by large mainframe and COBOL using organizations, is
partially predicated on the fact that these numbers are SMALL to them. And
SQL helps them isolate and manage those inevitable changes.

> 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.

Actually, the bad data does not happen in an SQL situation. In a situation
with level checks turned off, the bad data happens because of a write
without the field and no defaults enforced for the data in the new fields.

When you alter a table to add a field, it must be null capable initially.
That's because as the field is added, it is null. Then you can alter the
field to have a default value and/or not accept a null (if you populate the
field first).

If you are using SQL inserts, the data will not be corrupted in any field or
the write will not complete. Any missing fields will be either null or their
specified defaults.

>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.

Ah. But the trigger (non SQL triggers, program triggers) is the program that
DOES depend on the format of the record as a whole. The record buffers are
in there and dependant on the added fields.

But since there are a specific number, usually less than a few thousand
<vbg>, that are attached to the file, the location and recompilation of the
triggers in association with column changes and additions, is less fretful
than the previously described scenario.


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"America is the land that fought for freedom and then
  began passing laws to get rid of it."

     - Alfred E. Neuman