4 ms·

AS/400 follows a similar idea if I recall correctly, on top of DB2

by wyan 4y ago

skissane 4y ago
Dubious. IBM's marketing wants to convince you it does, but as far as I can work out, the integration of DB2 into the system is nowhere near as deep as the marketing makes it sound.
- nrr 4y ago
  It isn't actually Db2. They call it "Db2 for i," but it's a completely different codebase from what you'll find on z/OS and elsewhere. That said, the object-relational database goes down pretty deep in the system. IBM i (as OS/400 is now called) relies on it for storing things like source code and bound programs in addition to, well, data. Aside from IFS, which seems seldom used except for Java, it's basically the way to store things. If you have a library (IBM i-ese for a directory, kinda, but it's actually more like a schema in something like PostgreSQL), say, called YCBR1 that contains a physical file named QRPGLESRC, you can run `SELECT srcseq, srcdta FROM ycbr1/qrpglesrc` against it even though the members in that physical file are RPG IV source code, and you'll get the usual kind of resultset that you'd expect from a relational database. Likewise, you'll get something similar for binaries.
  - skissane 4y ago
    > It isn't actually Db2. They call it "Db2 for i," but it's a completely different codebase from what you'll find on z/OS and elsewhere. From what I understand, the DB2/400 code base was forked from SQL/DS – so its closest relative is Db2 for VM/VSE – and Db2 for i is as much Db2 as Db2 for VM/VSE or Db2 for LUW is. (Db2 for z/OS is itself descended from SQL/DS – it started out as a port of SQL/DS from VM/CMS to MVS, although I believe most or all of the SQL/DS code was rewritten early in its development history and there may be little or none left by now.) > IBM i (as OS/400 is now called) relies on it for storing things like source code and bound programs in addition to, well, data Not really. You are talking about storing objects (including files) in the classic OS/400 filesystem – which hasn't fundamentally changed from that of S/38 – and S/38 didn't have SQL. During the development of OS/400, they took the higher layers of SQL/DS and ported them on top of the S/38 file system. But if we are talking about how RPG code is stored, that's all about those lower layers which predate SQL support > YCBR1 that contains a physical file named QRPGLESRC, you can run `SELECT srcseq, srcdta FROM ycbr1/qrpglesrc` against it even though the members in that physical file are RPG IV source code Nowadays, many other RDBMS systems support querying OS files as if they were DB tables. I won't deny that IBM i makes it somewhat more seamless than most other systems – but I think that's mainly because it is seen as somewhat of a niche requirement, other systems could easily make it more seamless if it was seen as a priority. No "deep" integration with the OS is necessary to implement it, either
    - nrr 4y ago
      Aha, that's just an interface for interrogating objects in the traditional QSYS.LIB filesystem. Thanks for that clarification. 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? Beyond CRTSRCPF/CRTPF vs STRSQL (or similar), what's the difference between using DDS and DDL for that? 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'm finding that the details are scant.
      - skissane 4y ago
        > 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/ssi/rep_ca/3/897/ENUS288-293/index.html 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.
        nrr 4y ago
        Hey, thanks again for following up. 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.

AS/400 follows a similar idea if I recall correctly, on top of DB2

by wyan 4y ago

AS/400 follows a similar idea if I recall correctly, on top of DB2

Dubious. IBM's marketing wants to convince you it does, but as far as I can work out, the integration of DB2 into the system is nowhere near as deep as the marketing makes it sound.

nrr 4y ago
It isn't actually Db2. They call it "Db2 for i," but it's a completely different codebase from what you'll find on z/OS and elsewhere. That said, the object-relational database goes down pretty deep in the system. IBM i (as OS/400 is now called) relies on it for storing things like source code and bound programs in addition to, well, data. Aside from IFS, which seems seldom used except for Java, it's basically the way to store things. If you have a library (IBM i-ese for a directory, kinda, but it's actually more like a schema in something like PostgreSQL), say, called YCBR1 that contains a physical file named QRPGLESRC, you can run `SELECT srcseq, srcdta FROM ycbr1/qrpglesrc` against it even though the members in that physical file are RPG IV source code, and you'll get the usual kind of resultset that you'd expect from a relational database. Likewise, you'll get something similar for binaries.
- skissane 4y ago
  > It isn't actually Db2. They call it "Db2 for i," but it's a completely different codebase from what you'll find on z/OS and elsewhere. From what I understand, the DB2/400 code base was forked from SQL/DS – so its closest relative is Db2 for VM/VSE – and Db2 for i is as much Db2 as Db2 for VM/VSE or Db2 for LUW is. (Db2 for z/OS is itself descended from SQL/DS – it started out as a port of SQL/DS from VM/CMS to MVS, although I believe most or all of the SQL/DS code was rewritten early in its development history and there may be little or none left by now.) > IBM i (as OS/400 is now called) relies on it for storing things like source code and bound programs in addition to, well, data Not really. You are talking about storing objects (including files) in the classic OS/400 filesystem – which hasn't fundamentally changed from that of S/38 – and S/38 didn't have SQL. During the development of OS/400, they took the higher layers of SQL/DS and ported them on top of the S/38 file system. But if we are talking about how RPG code is stored, that's all about those lower layers which predate SQL support > YCBR1 that contains a physical file named QRPGLESRC, you can run `SELECT srcseq, srcdta FROM ycbr1/qrpglesrc` against it even though the members in that physical file are RPG IV source code Nowadays, many other RDBMS systems support querying OS files as if they were DB tables. I won't deny that IBM i makes it somewhat more seamless than most other systems – but I think that's mainly because it is seen as somewhat of a niche requirement, other systems could easily make it more seamless if it was seen as a priority. No "deep" integration with the OS is necessary to implement it, either
  - nrr 4y ago
    Aha, that's just an interface for interrogating objects in the traditional QSYS.LIB filesystem. Thanks for that clarification. 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? Beyond CRTSRCPF/CRTPF vs STRSQL (or similar), what's the difference between using DDS and DDL for that? 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'm finding that the details are scant.
    - skissane 4y ago
      > 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/ssi/rep_ca/3/897/ENUS288-293/index.html 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.
      - nrr 4y ago
        Hey, thanks again for following up. 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.