Although some DB2 "purists" do not like to admit it, the IBM System/38
relational database that was built into CPF was shipped at least two
years before DB2 became available for mainframes (in June of 1983).
(IBM's SQL/DS was announced in 1981 and was not delivered until 1982.)
The IBM System/38 is widely accepted as the world's first commercially
available relational database product. (The IBM System/38 was announced
in 1978 and delivered in 1980.) These facts are well documented in the
Support for both triggers and stored procedures were added to DB2/400 in
V3R1 and V3R6, circa 1994.
DB2/400 was one of the first of IBM's DB2 implementations to support
triggers and referential integrity, starting with OS/400 V3R1 in 1994
and V3R6 in 1995. This was two years before stored procedures first
appeared in "DB2 4" in 1996 and five years before trigger support was
added to "DB2 6" on the IBM mainframes in 1999.
And DB2/400 UDB has probably the best implementation of stored
procedures, because any program on OS/400 or i5/OS can be used as a
stored procedure. You can write your stored procedures in COBOL, RPG, C,
or the SQL Stored Procedure Language (SPL) --take your pick.
Yet you hold up "DB2 on the mainframe" as if it was the "definitive"
version DB2 -- yet on the mainframe, you must code JCL to create a
"stored procedure" and when the stored procedure gets invoked, it
submits a batch job -- how quaint is that? See
for more information that shows how
DB2 on the mainframe lagged behind almost all the other DB2s on all
other platforms for many years.
DB2 on the mainframe is written in PL/S, IBM's internal system
programming language. The System/38 database was written in a
combination of PL/MI for the part that runs "above the MI" and PL/MP for
the parts that are "below the MI" layer -- the PL/MP parts were
rewritten in C++ for the SLIC kernel for the PowerPC RISC AS/400 that
first appeared as V3R6. (I do not know what languages other components
of DB2/400 UDB might be written in nowadays, but probably some dialect
of C or C++.)
All of the other DB2s you mentioned (for AIX, for OS/2, for Windows,
etc.) are all based on a common code base that is written in C, where
the DB2 database exists or resides within one or more "table spaces"
which are carved out of (guess what?) FLAT FILES! (That's the part that
is the security concern -- if you could directly access those FLAT
FILES, you would be bypassing all of the built-in security and integrity
etc. of the database. But that's NOTHING LIKE how DB2/400 works -- at all.)
You mention "single level storage" in some of your posts. All DB2/400
tables and views (physical and logical files) are implemented as various
MI objects (data spaces, data space indexes, cursors, etc.) that reside
in the single level storage -- so the pages of these objects can reside
anywhere on any DASD volume within the containing ASP. There is no need
to worry about or allocate 'table space" or worry about what DASD
volumes the table space must reside on -- OS/400 or i5/OS takes care of
all of that automatically, as part of single-level storage. (NOTE:
This is a FAR CRY from a database built on top of "flat files.)
System/38 CPF was the first system to implement the idea that all
"files" are really database tables (and views), and those are just
another object type that resides in the single level storage. This is
the essence of the MI architecture and what we know as CPF, OS/400, and
The folks in Rochester realized when they created CPF and its built-in
database, that most of their existing customers were coming from a
System/3, System/32 or System/34 background, and they were used to "flat
files" and using languages like COBOL or RPG. So, they did something
very clever -- they provided a way to access the "database" via "native"
I/O statements (READ, SETLL, READE, etc.) which made the transition much
easier. This in no way undermines the integrity of the database,
because, at the MI level, ALL access, whether through SQL, or through
"native" I/O statements, must go through the exact same database access
routines in the operating system, and those are using the built-in MI
objects (data spaces, data space indexes, journals, journal receivers,
and cursors, etc.) with special MI instructions that work with those
Some vendors and customers created database tables (files) in CPF or
OS/400 that are not truly "normalized" -- but anyone can create some
un-normalized tables in DB2 on the mainframe, or in DB2 on any other
platform, or in MS SQL Server, or Sybase, or Oracle, or any database
system you care to name. It's your choice..
I also know of many vendor applications packages on other platforms that
go to great lengths to "simulate" ISAM access by using stored procedures
and triggers, etc. -- if they had the kind of direct native I/O access
that we have on the System i, they would not need to resort to those
It is simply not the case that we are all just a bunch of OS/400 or
i5/OS "bigots" -- our version of DB2 is far more advanced than any other.
That's my opinion.
Mark S. Waterbury
> Dave Odom wrote:
Gee, in this case, I'm not arguing IBM vs. the rest or the RDBMS nor am I arguing IBM is not the most ANSI compatible. I'm speaking mainly of the difference in two things that make DB2/400 very different than the rest of its "brothers" in IBM. One, and we've been down this road before, Rochester's implementation of its DB2 vs the rest of IBM. Rochester found a way to make a DB2 on the i5 that would have somewhat of a look and feel of a DB2, so they could claim they are in the "family" but it is not, nor does it operate the same; and I'm not talking the single level store stuff nor the ANSI SQL capability.
Second, the WAY the implementation is used by a lot of the i world; not anything like the rest of IBM's DB2 shops. That's why the rest of the IBM DB2 community tilts their head like the RCA dog when i5 folks talk of their OS having a DB2. I know because I've brought it up at DB2 User Group meetings, banging the IBM i drum. While they are polite, they consider all the rest of the DB2s, VM, VSE, MVS, AIX and Windoz, even old DB2/2, as being a DB2 but not the i5. My "bullet points" above are some reasons why.
I'm sure many here are saying... OK, so what. The answers are contained in some of my previous missives on this subject. But, the most compelling is... to make the i5 able to compete in the market place after the current batch of old architecture folks are gone. But, perhaps I'd better stop tilting at this windmill as it's clear I"m goring someone's ox, committing blasphemy and folks don't want to hear non-dogma views of their system's future.