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.

Wednesday, 20 June 2012

IBM i hints and tips #5 to #12

Don't want to lose these in the depths of Twitter.

Assorted  hints and tips #5 : how to improve usability of CL commands very easily - great tool at 

Assorted  hints and tips #6 : RTVQMQRY ALWQRYDFN(*YES) allows retrieval of the equivalent SQL from a Query definition (*QRYDFN object)

Assorted  hints and tips #7: IBM i Access value add: socket level ping: cwbping any-hostname-or-ip-address /port:any-port-you-like

Assorted  hints and tips #8 : Limiting the size of an SQL result set : FETCH FIRST n ROWS ONLY

Assorted  hints and tips #9 : Want to know the total size of a library? DSPLIB to a spool file shows it at the bottom

Assorted  hints and tips #10: Hints for creating a usable AFP overlay from a letterhead stored in Microsoft Word: 

Assorted  hints and tips #11: To get a list of IFS items: RTVDIRINF. 2 linked outfiles:…D hierarchy,…O list of stream files/directories

 hints & tips #12: Spool files for jobs that change user belong to the current user, not to the job. OVRPRTF SPLFOWN(*JOB) changes this

Ways of handling XML and web services calls from RPG #ibmi

Someone asked me an IBM i/RPG/XML question yesterday and I couldn't find a simple summary of the options available, so I put one together - if I missed a suitable link, or if there are errors/omissions in this, please comment.

This list excludes the various third party options available.

Low-level reading and writing of XML direct from RPG code:

  • Parse: XML-INTO and XML-SAX RPG opcodes (V5R4 onwards)
  • Generate: Roll your own (not difficult, but not standards-based and not very maintainable)
  • Parse and Generate: XML Toolkit, 5733-XT1 (V5R4), 5733-XT2 (V6R1 onwards): usable from any ILE language, but complex to use direct; a ‘wrapper’ called XML4PR is provided to simplify usage from RPG
  • Best way of all, potentially: Open Access handlers, once these become available (apparently under development by IBM STG Lab Services) – have the potential to hide all XML parse and generate complexities from the programmer – I imagine they would be chargeable though (V7R1 onwards)

Consuming web services direct from RPG code:

  • Web Services Client for ILE: provides a way (WSDL2WS tool) of generating a ‘proxy’ C or C++ service program, although this is complex to invoke from RPG – recently simplified by incorporation of WSDL2RPG tool, which generates an RPG ‘proxy’ instead


  • Use your chosen open source Java XML and web services client functionality, invoking your Java classes from RPG
    • Pro:
      • This may be the simplest/most maintainable way to do it, especially if you make use of the Java Toolbox record access classes to handle external description
    • Con:
      • Configuration management of the necessary open source jar files may be an issue (you are also likely to duplicate functionality that is now built in to IBM i, e.g. Apache Axis open source web services layer)
      • Getting the performance right may take considerable effort the first time
      • Change management/source code control may be an issue unless you have other non-J2EE Java code in production

XML-based data storage and retrieval (XML SQL datatype) from RPG code: