August 29, 2023
Tutorial: Accessing COBOL data in real-time using SQL
This demo will show how you can access your COBOL data in real time using SQL. We’ll use FairCom’s RTG COBOL Edition. COBOL faces multiple challenges providing access to its data. The problem we’ll examine today is one most businesses face: The COBOL data is inaccessible from other environments, such as SQL and ODBC, so it’s difficult to use reporting tools and business intelligence applications.
You’re about to see how RTG solves these problems without rewriting your COBOL application. RTG COBOL edition is a complete server. It’s a full SQL server, so it’s also a relational database. You can drop it into a COBOL environment with no changes in your source code, which is why we say it’s ready to go. Just by replacing the file system, you get a much more robust environment than the file system provided by the COBOL runtime.
It’s designed for a high level of concurrency, so it handles thousands of simultaneous connections without experiencing corrupted files. That, by itself, is already a major breakthrough for your COBOL environment. In addition, it provides protection, monitoring, and encryption. That’s all part of the RTG package. Today we’ll examine something even more remarkable. You’ll see how RTG allows you to use SQL Interfaces to access the data sitting in those COBOL files.
This is a commercially available ERP application, which we’re using to demonstrate the power of RTG. It could be any type of application. We support multiple flavors of COBOL. We can browse the vendors and see our sample data. You can see the application’s data files in this folder. This particular COBOL file has already been moved to RTG, so I can show you how to access it with SQL. The SQL-ized file is transparent to COBOL. That means your COBOL program and your SQL interfaces can access the same data at the same time.
Switching over to our SQL Explorer tool, you can see I’m connected to the SQL Server. Our SQL server supports stored procedures, user-defined functions, indexes, views, triggers… It’s an ANTSI compliance SQL server. You have a full SQL server managing your data on one side. On the other side, your COBOL application is accessing the same set of data through a robust file system.
This is a file we saw earlier in the application, but now we see it from SQL. If I click table records, you’ll see the same records you saw in COBOL. I can run SQL queries here, for example. And there you go. We’ve just run a SQL query on top of our COBOL data. In this example, we’re running the query from our SQL Explorer tool. This is a graphical tool that comes with RTG. For database management, you have direct, interactive sequel to run the query, or you can use one of our multiple APIs such as ado.net, ODBC, JDBC, and many others. This is live data. We’re not duplicating data, so you’re accessing the same data as your COBOL program.
Real-time access takes the hassle out of using batch jobs to extract data, and it makes the data available immediately. When I edit the record, changing these letters to capitals, I’m actually changing the COBOL file. To see what we’ve changed, we switch back to the COBOL application. If we go back to the vendor’s table and browse it again, you’ll see that the record has changed. These letters are now capitals. Both the COBOL application and the SQL interface are accessing the same set of data. Our SQL server is managing everything in the background. This opens the door for you to integrate your COBOL application with any other SQL-oriented environment.
For example, a spreadsheet can access the same set of data files using an ODBC connection. We go to the Data Connection Wizard and choose an ODBC data set. When I select our driver, you see a list of tables. We hit finish to see the data. You can see the same data, Ampersand UNO, just like we saw in the COBOL application and exactly like we saw in the SQL Explorer when we were browsing the records, which is where we changed the data.
Now you have full SQL access to your COBOL data while your COBOL environment accesses the same data simultaneously, which opens up multiple possibilities. You can integrate your COBOL application with other solutions like business intelligence, data warehousing, and data mining, and even included in your big data project. For example, I built a view to use for a report. It’s a join of two SQL tables. Switching to my report tool, I can run a report using that view. It’s a Java environment, so we are connecting with JDBC. Now we hit preview and using our JDBC connector, we can build beautiful reports on top of your COBOL data files, and you have real-time access to your data. No more waiting for batch reports.
Your entire COBOL environment is now open for reports and other business intelligence solutions, and that’s just how easy it is. Drop us a line if you’d like to learn more or try downloading a working version of RTG to try it yourself.