> Aha, that's just an interface for interrogating objects in the traditional QSYS.LIB filesystem. Thanks for that clarification.
From what I understand, to create SQL/400, they used the SQL parser and query engine out of SQL/DS, but not the lower level storage code; instead, they used the existing QSYS.LIB database file code as a storage engine.
It is worth pointing out that OS/400 V1R1 did not include SQL, SQL/400 was a separately licensed add-on product; I'm not sure at which point SQL/400 got integrated into OS/400 – but this very fact shows that it is not quite as integrated as some of the hype suggests – https://www.ibm.com/common/ssi/ShowDoc.wss?docURL=/common/ss...
> Though, that does seem to imply that running a CREATE TABLE in STRSQL actually creates a physical file in a library just the same, doesn't it?
Yes, "CREATE TABLE" and "CRTPF" both create a physical file. So in principle they are equivalent.
> Beyond CRTSRCPF/CRTPF vs STRSQL (or similar), what's the difference between using DDS and DDL for that?
That said, in practice they aren't 100% equivalent. They set different defaults. I also believe there are certain features exposed through CREATE TABLE that are not exposed via DDS. I believe they ultimately reach the same lower-level internal code, but they get there via different code paths.
> I'm going to have to poke around and see if I can glean more insight into the SQL/DS lineage. Do you perhaps remember where you learned about its relation to Db2 for i?
I read it somewhere, unfortunately I can't remember where any more.
I have the feeling that the hype about the integrated database functionality might have more to do with the fact that the storage engine goes pretty deep than the fact that there's a frontend for it that speaks SQL.
That was kind of my point in originally noting that it isn't _really_ Db2: It's the object-relational database management system built into QSYS.LIB with an alternative frontend built from the bits that look like Db2. The rest of it just feels like conflation with perhaps what was a bit of initial upselling before SQL/400 became taken for granted enough such that IBM stopped making it a chargeable feature.
As a consequence, it all smells (at least to me) wildly different from the Db2 on z that uses VSAM under the hood. That last part is notable: VSAM is just a dataset access method, not something like i's ORDBMS that allows for, e.g., more reasonable ad hoc reporting. Doing that with ordinary VSAM datasets is kind of a pain, particularly if you need to do a lot of joins to get what you're after.
Additionally, where you have to migrate data out of ordinary VSAM datasets into Db2 on z, the data is already there in Db2 for i since it's all the same object-relational storage engine underneath. The other details seem to hinge on DDS-defined PFs allowing invalid data on write where DDL-defined tables validate data as it's written, differences in how keys are handled, differences in allowed field lengths, and so on. The two are, nevertheless, allowed to coexist much more easily.
> That was kind of my point in originally noting that it isn't _really_ Db2
Well, "Db2" isn't really a thing. There is the original DB2 for MVS (now Db2 for z/OS). And then there are three other codebases which have (at least partially) independent origins and were later rebranded as "DB2"/"Db2" – SQL/DS (now Db2 for VM and VSE – which actually came out before DB2 for MVS did), OS/2 Extended Edition Database Manager (now Db2 for Linux/Unix/Windows aka LUW), and SQL/400 (later DB2/400 and then Db2 for IBM i). The IBM i "edition" is just as much DB2 as the VM/VSE and Linux/Unix/Windows editions are, and if there is such a thing as true/real Db2, it is only Db2 for z/OS.
> It's the object-relational database management system built into QSYS.LIB
QSYS.LIB is not an "object-relational database". IBM makes a lot out of its "object-oriented" nature, but it really isn't "OO" in the sense that an OORDBMS is:
* OS/400 never allowed anybody other than IBM to define object types. We are talking about a form of "OO" in which only the OS vendor can create classes.
* OS/400 has never had inheritance, in any sort of generic way. There are certain ad hoc notions of inheritance in the system – for example, *FILE objects have a type field (the object attribute aka OBJATR) which distinguishes various types (physical files, logical files, device files, ICF files, DDM files, etc). Similarly, file members are classified into different types (such as source vs data) by their FILEATR/FILETYPE/SRCTYPE attibutes. But this is a long way off the more general idea of inheritance in a true OORDBMS – and as in my first point, only IBM can define new "subclasses", customer and ISV applications can only use the ones defined by IBM
(Historically, there have been a few ISV-related object types – for example, the Novell Netware object types found in older OS/400 releases – but they were added by IBM engineering due to some special partnership between IBM and that ISV, IBM has never offered ISVs the ability to create object types themselves. There is also an immense profusion of object types for IBM's own add-on products, both current and defunct – once an object type is added to the system, its definition can never be removed, although all the code which actually uses it can be excised.)
> As a consequence, it all smells (at least to me) wildly different from the Db2 on z that uses VSAM under the hood.
No, it is actually quite similar to VSAM. For storing customer data, only a small set of object types are actually relevant – *FILE, *USRIDX, etc – most of which have pretty direct equivalents in VSAM.
The biggest difference with VSAM, is that VSAM doesn't have any standardised way to specify the structure of a record (how to break it down into fields). COBOL copybooks are commonly used, but you also see people doing it using assembler macros, PL/I includes (inside IBM, PL/X too), and more recently C headers. There are tools available to convert between these different formats, but they cost extra $$$. By contrast, with DDS, you get a standard way to specify record structures which works cross-language, and all the compilers support importing DDS definitions. That is a real advantage over MVS – but actually has nothing to do with object-orientation at all – none of these record definitions are actually object-oriented.
> Additionally, where you have to migrate data out of ordinary VSAM datasets into Db2 on z, the data is already there in Db2 for i since it's all the same object-relational storage engine underneath
Db2 on z/OS's native storage mechanism is VSAM LDS. So it does actually use VSAM, but you are right the way it uses it is incompatible with legacy COBOL/CICS/etc applications which use VSAM directly (which would be KSDS, ESDS or RRDS not LDS). However, there are add-ons for Db2 for z/OS which enable it to access those KSDS/ESDS/RRDS datasets, for example IBM QMF – https://www.ibm.com/docs/en/qmf/12.1.0?topic=data-creating-v...
This has nothing to do with how "deeply integrated" Db2 for z/OS (or other z/OS RDBMS such as Oracle) is with the OS compared to Db2 for IBM i. IBM could have made DB2 for z/OS use KSDS/ESDS/RRDS instead of LDS, which would have produced roughly the same situation as you get on IBM i. And it has nothing to do with anything being "object-relational", given IBM i's "object-orientation" is more superficial than real, and this design decision has nothing really to do with object-orientation anyway. I think the real difference is, Db2 for z/OS prioritised performance over backward compatibility (VSAM LDS is faster, and by keeping the details of the storage architecture undocumented, IBM could change it in backward-incompatible ways across DB2 releases) – Db2 for IBM i made the opposite choice. Both could well be legitimate choices in their respective business contexts, but put that way, IBM i's choice doesn't appear more "advanced" than z/OS's.
From what I understand, to create SQL/400, they used the SQL parser and query engine out of SQL/DS, but not the lower level storage code; instead, they used the existing QSYS.LIB database file code as a storage engine.
It is worth pointing out that OS/400 V1R1 did not include SQL, SQL/400 was a separately licensed add-on product; I'm not sure at which point SQL/400 got integrated into OS/400 – but this very fact shows that it is not quite as integrated as some of the hype suggests – https://www.ibm.com/common/ssi/ShowDoc.wss?docURL=/common/ss...
> Though, that does seem to imply that running a CREATE TABLE in STRSQL actually creates a physical file in a library just the same, doesn't it?
Yes, "CREATE TABLE" and "CRTPF" both create a physical file. So in principle they are equivalent.
> Beyond CRTSRCPF/CRTPF vs STRSQL (or similar), what's the difference between using DDS and DDL for that?
That said, in practice they aren't 100% equivalent. They set different defaults. I also believe there are certain features exposed through CREATE TABLE that are not exposed via DDS. I believe they ultimately reach the same lower-level internal code, but they get there via different code paths.
> I'm going to have to poke around and see if I can glean more insight into the SQL/DS lineage. Do you perhaps remember where you learned about its relation to Db2 for i?
I read it somewhere, unfortunately I can't remember where any more.