MIDRANGE dot COM Mailing List Archive

Home » MIDRANGE-L » July 2008

Re: Modernization and multi-member files


Hello, Dave:

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

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 http://en.wikipedia.org/wiki/IBM_DB2 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 i5/OS.

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 object types.
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 "tricks."

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

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact