About the author
Article from 2013: Mark Whitfield is an IT Project Manager for the RTLX payments software at Insider Technologies (UK) and has spent over 22 years working with HP NonStop (formerly Tandem) systems at ascending points in the software development lifecycle.
After graduating, Mark’s first 5 years were spent in NonStop electronic banking at what became FIS (formerly Deluxe Data / eFUNDS, UK) working on sp/ARCHITECT which eventually evolved into CONNEX Advantage.
In 1995, Mark moved to Insider Technologies to focus more on HP NonStop monitoring, diagnostic and payments software which included software design, team leading and eventually product management for Enterprise Manager, BASE24, EPS, XPNET and payment engine integrations.
Episode IV – A New Hope
(…for both Archived Legacy and Real-Time Transactions and NonStop EMS Alerts)
Mark Whitfield
IT Project Manager, Insider Technologies
A long time ago (well, 2010) at an EBUG conference far, far away (I guess it depends on where you’re coming from but anyway, Madrid)… Cue anthemic signature music and then, as the strings slowly fade away, pan left to a NonStop hand held aloft on the back row of a technical track presentation and an Insider speaker raising his eyebrows to acknowledge… ‘Yes, at the back’… a qualified voice is heard and a question is raised… ‘Can I store and query on 10 to 15 years’ worth of legacy, tape archived BASE24 TLF and PTLF transaction log data?’… the qualified voice continues… ‘I would also like to query on related EMS messages, POS and ATM administration data’…slight pause… ‘Oh yes, and display appropriate standard and custom tokens for EMS alerts, TLF and PTLF transaction data’… and one last volley of questioning tone ‘with consideration towards on-site directives for PCI DSS compliance?’…
The Insider speaker is not fazed by the informed, acronym ridden questions but rather smiles knowing that his subsequent PowerPoint bullet points have all been pre-empted (phew). The usual adrenaline and nerves subside and the Insider speaker relaxes into a recent case study where all these questions have been answered. For a moment (or three), he adopts the character of Morpheus talking to Neo (in ‘The Matrix’ training program… good code that) and presents his beautifully animated slides (probably over animated to be honest) as if to say ‘Welcome to the World of the Real!’… In ‘Reality’ (ironically), in answer to the delegate’s question, I probably just said… ‘Yes, all of that can be achieved and more, much more’ but as a movie buff, I like to remember my past podium performances with a little more romance and poetry injected. That aside, the science fiction from this point on, now becomes science fact.
Fact: A Microsoft SQL Server database can contain up to 999 partitions.
Fact: Hard drive sizes these days are measured in terabytes (I have just measured one on my desk as I write this so I know I’m right).
Fact: 10 – 15 years of transaction data partitioned by month (including header, authorisation, tokens, administration, settlement and summary data) equates to 50+ terabytes.
Fact: (I mean factual conclusion)… Storing and accessing this amount of transaction data is very, very doable (with an Insider’s approach of course) with today’s database technology (with room to spare)… read on.
The theme of this issue of ‘The Connection’ journal is Application Modernization. Sometimes modernization can be as much about dove-tailing with and extending a reliable, business critical, legacy application (and trusted platform like HP NonStop) into more widespread technologies (alas, known more intuitively by those pesky (but likeable) graduates).
In this context of BASE24 transactions (and other competitor payment engine solutions) and NonStop EMS message alerts (generated by XPNET nodes, stations lines, links, processes, device handlers, WebSphere MQ, Open System Services (OSS), Pathway and so on), this means taking fully optimised data feeds from the payment application, across TCP/IP (v4 and v6), onto server platforms like Windows running Microsoft SQL Server. Whilst doing this, making sure to uphold those golden NonStop commandments of check pointing and fail over for restart situations (or glitches in The Matrix… that’s right, I am beginning to believe) and ensuring that transaction and EMS data is appropriately normalised (masked and/or encrypted if required) and stored to enable ultra-rapid query response times.
This following case study highlights how one of Insider’s many clients have successfully enhanced their BASE24 ATM and POS transaction querying capabilities, away from restrictive, legacy green-screens to intuitive, browser based, wizard-driven visuals. Third-party solutions are available for taking transaction data off the NonStop platform (and other systems) for monitoring purposes, escalation, producing real-time charts and management information reports. This case study acts as a ‘real world’ (versus Matrix dream world) summary for what works when taking sensitive payment data sideways and of course, for building your own product check list if currently looking for solutions for extensive payment querying (multiple years), monitoring and problem escalation in real-time.
Summary + Client Profile
One of the largest banks in the Middle East (ME) was experiencing a growing amount of pressure from the central regulator to provide fast turnaround of customer card queries (across multiple bank departments with non-technical, back office staff) for transactions that dated back from more than a few months, to sometimes over 8 years. Failure to comply with this Service Level Agreement (SLA) would result in financial penalties for the bank and given the extremely large numbers of transactions involved, this presented a significant challenge.
The bank has a significant network in the ME area with over 500 branches, over 100 dedicated branches for women, more than 3,100 ATMs, 25,000 POS terminals; installed with merchants within the network of SPAN, GCC Net, MASTERCARD and VISA; and the largest customer base of any bank in the Kingdom, in addition to 130 remittance centres.
The bank uses the latest version of ACI’s BASE24 Classic product on HP NonStop to acquire, authenticate, route, switch, and authorize financial transactions and these are held online (on the NonStop) for a fixed period.
For POS and ATM transactions older than 6 months, the transaction log files (and their corresponding alternate key/index files) are typically archived to magnetic tape for mass storage by the bank. This is because the data footprint of transaction log files (both TLF and PTLF) requires a sizeable number of gigabytes for each bank processing day.
If a customer query concerns one or more transactions that are not currently online, the bank needs to locate the transaction log file on a given tape, restore the tape archived file to an accessible NonStop disk (with the correct file security settings) and locate the appropriate customer card transactions using the payment application green screens. This process can take a considerable amount of time which is in conflict with the Central Bank’s SLA directives.
In order to meet these imposed SLAs, the bank needed to find a solution that enabled the turnaround of card queries in minutes and hours rather than days or longer!, using modern, optimized database access techniques, encryption and masking (where appropriate) and partitioning. This would then provide secure querying of terabytes of transaction data which could then also be archived using the latest technology.
Problem – Context
The main business drivers and requirements behind this card transaction querying project where:
- Increasing pressure from the central regulator and in-house audit and compliance directives to provide quick turnaround of card transaction queries in line with newly imposed SLAs
- Provide secure, user-friendly, fast querying of terabytes of ATM and POS card transaction data across multiple bank departments for non-technical staff to respond to enquiries both internal and external
- Provide access to card transaction administration data, e.g. POS settlement records
- Enable the parsing of BASE24 standard token fields (BASE24, ATM and POS – S6, 24, B8, B9, 03, B4, AG, B7, B0, B1, 08, B2, B3 etc. etc. etc.) as well as bank customized tokens both of which are located at the trailing end of each card transaction record (in the (P)TLF logs) in a non-specified sequence
- To provide a card transaction query solution within budget and to timescale to avoid financial penalties imposed by the central regulator for broken SLAs
- To comply with bank PCI DSS directives for encryption and masking of Primary Account Numbers (PANs) and other sensitive card transaction fields
- To enable the reading, relay, parsing and storing of multiple years of BASE24 version 5.0+ POS and ATM transaction data within a reasonable timeframe
Solution – Selection Process
The first phase of this card querying project involved a Proof-of-Concept (PoC) exercise requiring each third-party involved to prove the transaction capabilities and efficiency of their solution.
The product was installed on a 32-bit Windows Server (on-site). For the PoC it was decided that a single Virtual Machine (VM) would be used. This could then be reused by the bank for subsequent PoC installs.
Transaction extraction agent objects were deployed from the product’s Process Console view on the Windows Server to the live HP NonStop ServerNet system (later NonStop Integrity Blade system) running BASE24 Classic version 6.10 (NOTE: a log replicated DR / standby NonStop node is also a valid option for extraction agent deployment. Look for this flexibility and also compatibility with Active-Active payment engine configurations).
The extraction agents read and relay (across TCP/IP) all transaction data records from TLF (ATM) and PTLF (POS) entry-sequenced Enscribe files using advised and proven approaches for minimal CPU cycles (with log file position check pointing and recovery as standard out-of-the-box). This data relay is optimized and card transaction data then populates a Microsoft SQL (normalized) database on the Windows Server. The database is populated using efficient, multithreaded processes with best-approach disk I/O methods.
A secure browser (of your choice) can then query ATM, ATM administration and POS records along with displaying an agreed subset of related standard ACI tokens.
The PoC was a success and was subsequently presented to a number of departments within the bank (on a Sunday morning in the Middle East) and they were then quick to move to the implementation stage of the card transaction querying project. The bank also requested further product features including:
- Extraction and parsing of a further 38 transaction tokens, some bank customized, stored into XML row meta tags
- Extraction and parsing of the POS settlement records
- Deployment of multiple extraction agents to enable the fast download of many years’ worth of ATM and POS transaction log files in any date order (and on any NonStop disk / subvolume)
- Further enhanced EMS message output for NonStop technical staff to check the status and position of the batch file extraction of (P)TLF log files
- Strong AES_256 encryption (and field masking) of PAN data fields in the transaction records to comply with on-site PCI DSS directives
Solution – Implementation
After integration testing, the software was then installed on two 64-bit, Quad Core Windows Servers (one for the transaction database and one for the main application). This approach further optimized performance of the solution by separating the fast parsing of transaction records and token data fields from the database read/write processes. Look for this flexibility in your chosen solution.
Multiple ATM and POS log extraction agents were then deployed to the bank’s new HP NonStop Quad Core NB54004 Integrity Blade system.
The Microsoft SQL database was partitioned by month (fact reprise: up to 999 partitions allowed in MS SQL Server) and records inserted in line with the entry log timestamp of the original transaction. A number of single terabyte hard drives were made available by the bank and connected to the Windows Server to receive the transaction data to the appropriate MS SQL file group partition (each monthly partition can grow at a configurable rate in terms of space allocation, e.g. 500MB).
Splitting out card data into monthly partitions enables MS SQL Server to provide fast look-up querying of transaction rows by pre-checking the partition schema for data row location based on the transaction timestamp. This enables ultra-rapid query response times for transaction data by locating disk and partition before primary key and index.
The bank had already restored from tape and readied all of the TLF (ATM) and PTLF (POS) log files from the previous processing year (2011).
Your chosen querying solution should not require the use of the NonStop alternate key index files for the batch download of transaction data. This saves on NonStop disk space (and administration time) when restoring (P)TLF logs and allows more files to be restored from tape for each batch processing run. This change was requested by the bank in the original PoC.
Batch download speeds for the ATM and POS transaction logs now enable the bank to parse and replay weeks of card data in hours for subsequent, concurrent querying by the various bank departments.
Results and Benefits
A summary of the business results and benefits a solution like this can provide:
- SLAs for customer and departmental queries on ATM and POS transactions reduced to eliminate bank penalties for slow customer response times (less reliance on green screens and ENFORMs)
- Bank technical staff time and resources are no longer required for locating and restoring appropriate ATM and POS transaction log files from tapes
- Concurrently accessible and secure browser query views for card transaction data (and related EMS) enable the various banking departments not to be totally reliant on HP NonStop BASE24 technical staff for ATM and POS queries
- By default, the solution provides another backup archive (beyond older tape media) of ATM and POS transaction data which can then be transferred to more modern, non-magnetic storage devices if required
- The ATM and POS area of the bank can now respond more quickly to requests by their own departments and external bodies for management information relating to card transactions. This can now be achieved using an inbuilt CSV export function and tools like Microsoft Reporting Services, Excel and Seagate’s Crystal Reports
- Terabytes of card data can now be securely queried (and queries saved by user groups) based on any transaction field or set of fields (with both character and string wildcarding) in the ATM or POS record, a feature and capability not offered by BASE24 Classic. Something to look for in your chosen solution.
- The bank do not need to keep as much ATM (TLF) and POS (PTLF) data online for green screen access and so can free up HP NonStop disk space for other uses since the transaction data is held in a partitioned, secured MS SQL database
A New Hope – Reprise
To summarise then, terabytes of payment and transaction data (50+ terabytes in this particular case study) can be hosted securely (with appropriate data encryption and masking) and queried with ease using mid-to-high end Server platforms. This of course coupled with advised approaches to database encryption, partitioning, indexing and normalisation for your chosen querying and monitoring solution.
Modern competing solutions for real-time monitoring and querying (and reporting) of payment data should provide a bank with:
- A tailorable, colour coded, graphical monitoring view(s) for monitoring EMS alerts, system and ATM/POS transaction level issues, e.g. possible fraud, transaction throughput down 10% or interchange/switch down or quiet with optional issue escalation
- A set of tailorable, self-refreshing transaction performance charts (line, pie and bar charts, gauges and tables) for monitoring ATM/POS performance and usage, issuers, acquirers, card types, transaction types and of course, EMS alert breakdowns and totals
- A comprehensive, field-level transaction querying view for all transactions (and settlements) processed by your payment application as well as querying for NonStop EMS message alerts with CSV file export. No programmatic knowledge required. All transaction / EMS queries can be saved by user group to be re-executed as required
- Integrated ATM and POS management information reports generated both automatically and / or manually as required by the bank in various formats, e.g. PDF, Word, CSV, HTML
Look out for our next article, ‘Episode V – The Insider Strikes Back (from a remote system)’ in a future edition of ‘The Connection’ journal.
About Insider Technologies
Founded in 1989, Insider Technologies Limited (Insider) is a UK-based software house and services company, which has grown to achieve an enviable track record and market position providing Business Application Monitoring (BAM) of high volume transaction processing systems.
In addition to a strong pedigree in financial processing systems (ATM, point of sale, fraud detection, card processing systems, money transfer SWIFT and SEPA), Insider also supplies international stock exchanges, defence, retail, telecommunications, utilities and livestock management applications and systems.
An HP partner, Insider Technologies provides both the Reflex and MultiBatch software solutions for business-critical 24×7 systems that evolved into what is known as HP NonStop computing technology and HP Integrity (formerly Tandem Computers).
A Microsoft Gold Certified Partner and Quality certificated to ISO 9001:2008 and TickITplus, Insider Technologies are also the team behind the Sentra and RTLX Reactor payment, transaction and message monitoring and tracking products for the Windows, HP NonStop, Linux and Unix platforms.
PDF Connection Magazine Article – page 12
Previous Contact Information:
Insider Technologies Limited (ITL)
Spinnaker Court
Chandlers Point
37 Broadway
Salford Quays
Manchester
United Kingdom.
M50 2YR
Mark Whitfield 