Sunday 24 June 2012

An attempt at explaining DB2 on #ibmi to the non-i developer


DB2 for i as a relational database:

  • The functionality is right up there with the competition.
  • DB2 for i looks very similar to other DB2’s from the coder’s perspective, but do not be fooled.
  • Management and maintenance are specific to IBM i, and are far simpler than on other platforms, because the database takes full advantage of the IBM i architecture.
The fundamental differences from other database platforms are cultural and historical:
  • IBM i shops have traditionally (and probably, with hindsight, foolishly) had no DBA role, and thus the structure, security, performance, and integrity of the database are not owned as they would be on other platforms.
  • SQL is extensively used in application coding (whether RPG, Cobol, or Java), but some sites still use native externally described file functionality from RPG or Cobol. Even where coding uses SQL, the database itself is frequently still created and maintained using the alternative Data Description Specifications (DDS) approach.
  • Data types used may well be limited to those that were supported by RPG on the System/38 (e.g. no DATEs).
  • Referential integrity tends to be built into the application.
  • Primary keys are not seen (or, really, implemented) as conceptually different from other unique keys.
  • Many applications do not use transaction boundaries (commitment control).
  • Database files (tables/views/indexes) are scattered across the system, frequently sharing libraries (schemas) with other objects.
  • Management and maintenance is typically all done from a green screen.

DB2 within the IBM i environment:

  • The database is built in to the operating system (and therefore, effectively, free of charge).
  • Database components (physical and logical files, i.e. tables, views and indexes) can exist in any library (schema); they can be managed and accessed via SQL or via traditional IBM i commands and programming techniques, interchangeably.
  • Columns are traditionally referred to as fields, and rows as records.
  • Table and column names are frequently short (for use in RPG) and/or meaningless (because automatically generated), and the provided ability to alias them to longer, meaningful values is very often ignored. Metadata support similar to that of DB2 on other platforms is present, but is not widely used. However, where DDS is the chosen database maintenance mechanism, there is usually extensive IBM i native metadata present (50-character descriptive text, up to 3 column headings); the system-wide catalog in library QSYS2 (SYSTABLES, SYSCOLUMNS, etc.) exposes this native metadata together with whatever non-IBM i-specific metadata may be present.
  • A stored procedure, function, or trigger handler can be written  in SQL or in any programming language supported on IBM i, including Java.
  • There is a very powerful SQL optimizer supported by strong performance analysis tools (part of the free-of-charge IBM i Navigator).
Some additional functionality:
  • MySQL can be used as a front end to DB2 on i.
  • Omnifind provides a system-wide database search facility.
  • The database has XML input/output functionality (see recent relevant blog post).
  • Encoded Vector Indexes (EVIs) are supported for tables with low cardinality.
Distributed database processing:
  • The native connectivity of the database, as with DB2 on any platform, is DRDA (Distributed Relational Database Architecture, aka DB2 Connect).
  • However, DB2 on i's standard ODBC/JDBC support uses the IBM i-specific database host server and has nothing at all to do with DRDA.
  • Except between IBM i systems, DB2 Connect connectivity to DB2 on i is optional (i.e. costs money), rarely used, and poorly understood. Other DB2’s and their DBAs use DB2 Connect as standard, so this is often a barrier.
  • DRDA between IBM i systems is easy to implement, free of charge, and supports two-phase commit (Distributed Unit of Work). The corresponding non-SQL  functionality, Distributed Data Management (DDM), is also highly functional and widely used.
  • The listeners for both DRDA and DDM are started using STRTCPSVR *DDM.
  • DRDA is configured using WRKRDBDIRE.

No comments:

Post a Comment