DB2 for System i (iSeries, AS/400)

Posted by Frank Fillmore on June 9, 2011 under DB2 Connect, DB2 for i. Tags: , , , .

Jim Tonchick had this recent post on DB2-L in response to a query from Dan Luksetich regarding DB2 for iSeries:

>> Yep, we’re out here.  But this forum is mostly for z/OS and LUW.  There are some iSeries forums, but DB2 is not the main focus. 

>> DB2 for iSeries is IBM’s best kept secret.  Every iSeries lpar has DB2 by default.  It comes with the operating system as the default file manager.  Even though it is on every instance of the i5 operating system, it is not covered by IBM at the OnDemand Conference or at IDUG’s conferences.  Even the IBM’s DB2 Family web site does not mention it (http://www-01.ibm.com/software/data/db2/).  You have to do to the IBM System i software path to find it (http://www-03.ibm.com/systems/i/software/db2/).  That’s a shame because DB2 for iSeries fits into the the DB2 family very well and can be a part of a distributed DRDA based application.

>> I attended IDUG NA 2011 in May and asked around why DB2 for iSeries was not even mentioned let alone represented.  The answers I received ranged from “not our target market” to “DB2 runs on the iSeries?”.  I’m glad to see someone in the industry finally take notice.

I agree that this member of the DB2 “family” is neglected.  For those of you who have been attending the DB2 Connect Virtual Users Group, DB2 Connect enables Distributed Relational Database Architecture (DRDA) between System i and DB2 for Linux, Unix, Windows because System i is EBCDIC-based (as is z/OS).

There’s a great manual for explaining the duality (split personality?) of DB2 for System i: “DB2 Universal Database for iSeries Database Performance and Query Optimization”.  It discusses the legacy Classic Query Engine (CQE) as well as the more-DB2-like SQL Query Engine (SQE).

There are also Redbooks

  • “OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4” (SG24-7326)
  • “SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries” (SG24-6654)
  • “Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS” (SG24-6598)

Finally there’s “SQL Reference for Cross-Platform Development” which takes the place of FREDS (Formal Register of Existing Differences of SQL – believe it or not!).  This manual compares the SQL dialects for

  1. Version 9 of DB2 for z/OS
  2. Version 6.1 of DB2 for IBM i
  3. Version 9.5 of DB2 for the Linux, UNIX and Windows Platforms