Real Systems Do Extracts, Part 6

We practice continuous improvement of our extract processes.  Too many payment systems teams get locked into the ‘coolness’ factor on the OLTP side and leave the entire extract side of the equation until very late in the project game.  That’s not the way our OLS.Switch clients see things.  The extract is of equal importance.  After all, it’s how they get paid.  You can do all the transactions you want in whiz-bang way, but if you can’t reliably produce and ship a related extract file one or more times a day, your sponsoring bank isn’t going to fork over a dime.  You’ll be hearing from your client’s accountant (hey, maybe even the CFO) very early in morning.  In fact, in this business, if you get a 3 AM phone call on the red phone like Hillary, I can guarantee you it’s not because your most excellent jPOS OLTP engine blew up.  It’s because your extract PoC flopped.  

At one client site, we just formulated and are prepared to rollout an improved process for troubleshooting exceptions in the OLS.Switch nightly extract.  The approach also aims to contain the operational fallout that occurs when an exception is encountered.  Our goal:  Provide our clients' on-call engineers with the tools required to attain self-sufficiency in troubleshooting and resolution.  While don’t mind the phone calls, the new approach greatly increases the odds that they can sort it out themselves.

Specifically, when the nightly extract encounters an exception condition, we face the following challenges:

1. The exception is buried in the q2.log.  It can be difficult to find.
2. The exception relates to one or more specific records on the tranlog, but the ID is not referenced.  It takes a fair degree of intuition and esoteric knowledge to figure out which row prompted the error.
3. The exception leaves the files in a “half-baked” state, but to the automated scripts that follow they appear complete.  As a result, [our client] sends invalid files (they don’t have trailer records) to processors.
4. The exception is especially dangerous with respect to [one specific] extract.  Because this file format does not contain a trailer record, the half-baked file is processed correctly by [that endpoint].  This outcome heightens the possibility of duplicate billing of [that provider’s] transactions when the extract is re-run.

To address these concerns, OLS has re-engineered the extract exception process as follows:

1. The exception text in the q2 log now references the ID of the offending row.
2. To prevent the VB script from executing at job’s end, when an exception is found all extract files are renamed with an “*.bad” suffix. 
3. For each extract and report file, the OLS.Switch extract process writes a complementary “*.log” file when an exception occurs.  The exception message will appear inside the log related to the specific file in which we encountered an error while trying to create it.