Ending Enscribe SQL Envy

As powerful as NonStop SQL is, its predecessor – Enscribe – casts a long shadow. And for good reason. It was the first database available for NonStop developers, and it delivered all the essential attributes for OLTP applications: high performance; relative and key based indexing; data partitioning; scalability.

If NonStop SQL had been around from the start, none of that would matter – all of our applications would be built around the SQL engine, and we wouldn’t have any Enscribe applications left to manage. But it wasn’t, so we do. And the awkward truth is with limited cost benefits in migrating those applications (even if it were possible, which often it isn’t), that management task isn’t going away any time soon. But what if we could leave the applications and Enscribe doing what they do well, and just add SQL based query support on top? Wouldn’t that be a great way to silence the “SQL Envy” of our data maintenance and mining colleagues wanting convenient access to our Enscribe data? These were exactly the questions posed to Ascert by Bankart, Slovenia’s leading payments processor and long time NonStop user, that led to the development journey described in this article.

Full disclosure – Ascert were not the first NonStop product vendor to develop a solution in this space. In fact it was the decision by the new owners of Bankart’s incumbent solution (Attunity Connect) to cease support for the NonStop product which had created the difficult situation of having no supported tool for an essential part of their production system. Borrowing the words of Matej Sovdat, who presented Bankart’s implementation at Ascert’s annual User Group, London 2022:

“Every wish from our business – data change, query etc – led to great efforts to write custom programs. And a Windows back office that already supported web services meant duplicating data from our payments application. Via Atttunity we had modernized this approach to avoid both of these problems by providing direct SQL access to our Enscribe application files. But everything has an end, and when Qlik announced the sunset of Attunity Connect on NonStop we were left searching for a vendor to help us. At first it seemed nobody was brave enough to take up the challenge. And then we came to ask Ascert. They were a vendor we already used for VersaTest and Relate, and it was a blessing from heaven when they said ‘yes’ – they could create a full replacement for us.”

Matej’s use of the words “brave” and “challenge” are on point – “can you build something to give me SQL access to flat files” is the sort of question that a non-technical project manager asks without realising the full depth of skills and experience that actually doing this entails. So, since it’s also a neat way of summarizing the product requirements, let’s list those now:

•  In-depth understanding of NonStop Data Dictionary Language (DDL) and COBOL copybooks. Where SQL has a schema for tables, Enscribe has DDL. Except DDL has a rich and wholly incompatible feature set for overlaying and redefining different formats for ‘records’ (the Enscribe equivalent of SQL rows) within a single data file. SQL access to Enscribe data means needing to dynamically access and map this DDL into a simpler model of tables, rows and columns that can be used in SQL queries.
•  Knowledge of the Java Database Connectivity (JDBC) standard, and the ability to create drivers to access Enscribe data both from remote Java client’s over TCP/IP, or local Java programs running under NonStop OSS.
•  And last but definitely not least, a comprehensive understanding of the NonStop personalities for creation of a command line interface (CLI) to allow direct SQL queries from interactive TACL or OSS command shells.
  

By virtue of their long history with the NonStop platform, what seemed daunting to some of the vendors contacted by Bankart felt more like an everyday project to Ascert’s team. Being Java based, the JDBC skills were present in the VersaTest product team already. And the Relate product team had the DDL, Enscribe and TACL command shell experience. All that was needed was to meld the skills from these teams to develop the new product solution. In fact, the task was welcomed by the developers as a refreshing, new and interesting piece of work. In the words of Andrew Mould, Product Manager for Relate:

“For some years we’ve described Relate’s command-line interface as ‘like SQL but without the full power’. It was great to have the chance to banish that caveat and bring all of SQL’s flexibility to Enscribe users”.

So what does the finished solution look like? Again, borrowing further from Matej’s presentation we can see the essential components of the Relate EnSQL product itself:

•  EnSQL/CI supports interactive and batch SQL queries from a TACL or OSS shell command line into the underlying Enscribe files.
• The EnSQL JDBC Server provides similar SQL access but to remote Java JDBC clients over TCP/IP connections.
• And finally, the EnSQL JDBC Lib provides direct access from local Java programs running on the NonStop under OSS.

Regardless of which of these three data access methods are used, the same essential features apply:

•  Comprehensive and standards compliant SQL syntax set, with extensions to handle the subtle complexities of the NonStop platform and those tricky Enscribe DDL data formats we mentioned earlier such as record redefinitions and field overlays.
•  No modification to the underlying Enscribe data is required – the files remain exactly as used by the existing application.
•  No “intercept libraries” are required. For Bankart this was an essential feature, to ensure that they could continue to use their existing intercept libraries for data security, tokenization and replication.
  

Of course, the proof is always in the pudding, which in this case comes in the form of a diagram showing Bankart’s (now complete) migration plan from Attunity Connect to Relate EnSQL:

By now you may be wondering what one of these SQL queries looks like, so for completeness we’ll include one of the worked examples using Ascert’s standard Enscribe “training” DDL:

At which point, readers experienced with databases may be wondering “What’s the big deal, this is just normal SQL?”. And that is exactly the big deal – this is just normal SQL, but for Enscribe data. No more “SQL Envy” required.