Working With Legacy IBM i Data

Draft. An outline of something I worked out in practice and intend to write up properly.

What this will cover

IBM i systems hold decades of business data and are entirely willing to give it to you, provided you accept the shapes it comes in. Nearly every problem is small, well understood by the people who have met it, and completely undocumented in any place you would think to look.

The intended shape of the article:

• CHAR padding. Fixed-width columns arrive padded to their declared width. Trim on read, or every comparison reports a change and every sync rewrites everything on every run.
• Packed and numeric dates. CYYMMDD-style columns are not dates and will not behave like them. Convert in the query, where the conversion is visible and testable, rather than in application code.
• Multiple libraries holding the same logical table, and what that means for uniqueness, scoping and anything that reconciles against a shared destination.
• ODBC realities. Driver bitness matching the application, and cast decisions belonging in SQL.
• The service-account trap. A user DSN lives in one person’s registry hive. A scheduled task running as a service account cannot see it, and fails with a data-source error that suggests the driver is missing. A DSN-less connection string removes the dependency.
• Filtering and casting at the source, since the alternative is holding a result set in memory to fix it afterwards

Why write it down

Every one of these costs an afternoon the first time and five minutes thereafter. The whole value of the article is turning the first case into the second for whoever reads it.