Re: SQL database makovers (was: Query optimizer tells me to build an... -- MIDRANGE-L

There was a discussion some time ago, to the effect that the database on the 400 (and the 38 before that) is not a true RDBMS. However, I seem to recall that it was always presented that way. If I understand the Date book a little, it seems that the underlying physical structure of a database does not define whether it is relational, rather a higher level abstraction does that. BTW, physical here is not used in the same way as it is in the term, "physical file".

AFAIK, physical files are not the same as flat files. They ARE the same as tables created through the SQL CREATE TABLE statement.

And keyed logical files and SQL indexes are essentially the same thing - B-trees.

SQL views are superficially called logical files but have no real access path - they are merely a container for an SQL SELECT statement that is executed to generate something like an ODP (open data path).

Physical files and indexes all use a "primitive" construct called a data space. You CAN trace your way through a file to its associated data space in SST - I forget the details right now, haven't done so for a while.

The object-based nature of the 38/400/iSeries allows each object to use the same base objects (such as data space) with different attributes for each subtype.

Notice that I did not include the 36 in my list. As I understand it, physical files were typically fixed-length but unstructured records, whose fields were defined in a HLL.

To some degree, I'm inclined to define an RDBMS by the methods of accessing it - SQL is a relational language (with some deviations from pure Codd-RIP, yes), so anything that has sufficient SQL capability would be relational. That's probably not what strict practitioners would say, so please enlighten me.

So I'm asking for clarification of the statement that DB2/400 is a flat file system and not an RDBMS. What do the terms mean? At what architectural level are you applying these terms? And how do these distinctions matter?

Thanks
Vern

At 10:45 AM 3/30/2004 -0700, you wrote:

Gang,

Was there any more discussion on this subject, especially by someone
that truly understands the differences of how the iSeries handles flat
files built using DDS vs. how the iSeries and its DB2/400 engine handles
the tables and indexes built using DDL(SQL)?   I'd like to see a
technical document from IBM Rochester and/or IBM Santa Teresa Lab (DB2
build site ??) and/or IBM Toranto (DB2 build site) that addresses all
the ins and outs and how the iSeries handles this subject.

I suspect if the DB2/400 engine handles things anything like the
mainframe (DB2/MVS, DB2/VM and DB2/VSE), you'll find the OS/400 and
DB2/400 handle access to data more efficiently than the traditional flat
file DDS data access via the old key construct as all this relates to
queries.

However, since the OS/400 is a different breed of cat relative to the
mainframe operating systems, I don't really know.   Which is why I'd
like IBM to speak about how things really work.

I can tell you one thing, the faster iSeries folks move to a true
relational data base design vs. the traditional flat file model, the
better off they will be especially as they get pushed more toward
decision support systems for management that usually includes data
marts, data warehouses, data mining, OLAP, and more.   I would also
suspect, as the iSeries and the pSeries come closer in their use of the
same processors and hardware architecture and as something like Linux
gets more popular because of economic issues, you'll see most
application products built to a RDBMS of some sort, probably DB2 and
Oracle.  Therefore, the quicker shops move their application development
toward the true relational model, the better.

FWIW,

Dave Odom
Arizona

date: Mon, 29 Mar 2004 15:25:17 -0500
from: rick.baird@xxxxxxxxxxxxxxx
subject: SQL database makovers (was: Query optimizer tells me to build
        an....)

This talk of the difference between DDS and SQL created access paths
has
caused me to question long and deeply held beliefs when it came to
database
design on the iseries.

When designing databases, i'd always tried to follow these few simple
guidelines (others may disagree with them, but let's not quibble over
that
part - it's always worked pretty well for me).

1.  most physical files have a key, a unique one if appropriate.
2.  logical files were built for any access paths needed for inquiries,
or
batch pgms that would be used daily or more (generally).
3.  everything else would be handled via opnqry.
3.  opnqryf would be used for record selection rather than creating a
bunch
of single purpose logicals.
4.  if performance problems are a concern or are encountered, logicals
are
created as indicated to help out.

How does the apparent superiority of SQL generated access paths change
these rules?

Would you make a point of replacing existing logicals with SQL indices
where possible?

Would you build SQL indices and views next to any logicals (or
physical
keys) that can't be directly translated to SQL?

If not (or not always), why not and/or when?

and one more question...  Can you specify on an SQL view or index the
Access path maintenance  (MAINT keyword) or Access path recovery
(RECOVER
keyword)?  If so, how?

Thanks,

Rick

--------original message---------
midrange-l-request@xxxxxxxxxxxx wrote:

>   1. Re: Query optimizer tells me to build an access path that
>      already   exists (Clare Holtham)
>
>That's one of the indexes you ALWAYS want to build.

This subject does show a glimpse of the differences between PFs/LFs
and
tables/views/indexes. I've seen that if an LF is created over a PF and
both
share the same key, then the LF file description shows the PF as owning
the
access path. But a SQL CREATE INDEX using the same field doesn't appear
to
share the existing PF access path; it seems that a brand new one is
created. (There might be ways of getting LFs and SQL indexes to
coincide
better, but I haven't learned them.)

Two obvious considerations come to mind -- double access-path
maintenance
and double space for storing the access paths.

Tom Liotta

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