TruusVlugindewind
Porting Cobol/indexed file to SQL without redesign, can be and has been done. But it is not a project for the faint-of-heart. Let me tell you a couple of stores.
Once upon a time, long ago (7-8 years) and far away (Ohio), I worked for an ERP software company that decided to move to SQL server data base. What we did was externalize all the file handing routines. The system was designed so that all Cobol programs called a file system DLL. This C++ application read a series of files the type and location of a file/table. This information would allow us to define at a file/table level whether the data was stored as an indexed file, a Betrieve table, PSQL table or a SQL Server table. Oracle was also in the original plan. The file system DLL would access the data and build a Cobol data record in memory and pass a memory pointer back to the Cobol program.
This design allow us to run the same program (same object code) in Betrieve and a few records in a data file and run the same program again in SQL Server.
The performance hit for the process was huge. I think that a 30X slower would not be out of line. We did finally get to a 5-8X slower with a lot of work.
So why do it. While it is true that a straight up conversion is slower once you have converted to SQL you can limit the records that are processed. If I have an invoice file with 20,000 records and you are writing a report listing all orders over $10,000. A normal Cobol program will read all 20,000 records. With a SQL data base you can limit the records returned with a simple where statement.
Select * from invoice_header where inv_total > 10000
If there are 100 invoices over $10,000 only 100 records will be processed. Reading 100 records in SQL is faster than reading 20,000 records using indexed files.
During the time that the SQL file system was being developed the Cobol developers continued enhance and develop new applications. From their point of view the back end data base did not matter. We added SQL specific enhancements later.
This may seem like a lot of effort to separate these processes. But we had 10 million lines of Cobol code, 18-24 modules depending on who was counting, 1800 files/tables, 38,000 sites, and 175,000 users in 17 countries. We could not afford to disrupt the code or the product.
Story 2. … then we sold the company.
I took a year off and then I wanted to work on some Cobol code from projects of mine before the ERP company. What I didn’t have was a SQL server file system. But I thought “I know how this should work, HOW HARD CAN IT BE?”. True to the old saying anyone that uses the phrase “How hard can it be?” does not understand the issue.
So I developed some code using the EXEC … END-EXEC. It worked but I did not like the way the code looked. So I started learning VB.NET and moved to Fujitsu’s Cobol.Net product. My third VB.Net application was a file handler. I liked the way that the sqldatareader worked so that is the product that I used. I wrote the code, converted 850 programs and 300 tables. The batch programs worked fine I will be converting the screen over the next 6 months to finish the process.
Things you should know.
1. Read Next logic for a multiple part key is the most difficult.
2. Key fields should be defined as NOT NULL. There is a serious performance hit if you do not do this.
3. Normalize all tables. The implicit redefines that occurs when you use multiple 01 levels in and FD can cause data corruption and other issues. Flatten these files.
4. Fix nulls in VB, don’t send them back to Cobol.
5. Forget multiple data types for moving data back and forth between Cobol and VB. Use COMP-1, COMP-2, and strings. I transferred numbers as COMP-2 then in Cobol moved the data to the actual Cobol data type.
Good luck
Tom Leaders
The Cobol code.
SET-A-UPCXX-FILE.
SET A_UPCXX_FILE_STATUS OF A_UPCXX_FILEOBJ
TO A-UPCXX-FILE-INV-READ.
SET A_UPCXX_FILE_GET_WHERE OF A_UPCXX_FILEOBJ
TO A-UPCXX-FILE-WHERE.
MOVE AA-UPCXX-FILE-KEY
TO PDA_AA_UPCXX_FILE_KEY.
SET AA_UPCXX_FILE_KEY
OF A_UPCXX_FILEOBJ TO PDA_AA_UPCXX_FILE_KEY.
MOVE AA-UPCXX-CATALOG
TO PDA_AA_UPCXX_CATALOG.
SET AA_UPCXX_CATALOG
OF A_UPCXX_FILEOBJ TO PDA_AA_UPCXX_CATALOG.
GET-A-UPCXX-FILE.
SET A-UPCXX-FILE-INV-READ
TO A_UPCXX_FILE_STATUS OF A_UPCXX_FILEOBJ.
SET PDA_AA_UPCXX_FILE_KEY
TO AA_UPCXX_FILE_KEY OF A_UPCXX_FILEOBJ.
MOVE PDA_AA_UPCXX_FILE_KEY
TO AA-UPCXX-FILE-KEY.
SET PDA_AA_UPCXX_CATALOG
TO AA_UPCXX_CATALOG OF A_UPCXX_FILEOBJ.
MOVE PDA_AA_UPCXX_CATALOG
TO AA-UPCXX-CATALOG.
ZA-UPCXX-FILE-READ.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ "A_UPCXX_FILE_READ".
PERFORM GET-A-UPCXX-FILE.
ZA-UPCXX-FILE-S-EQUAL.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ "A_UPCXX_FILE_START_EQUAL".
PERFORM GET-A-UPCXX-FILE.
ZA-UPCXX-FILE-S-LESS.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ "A_UPCXX_FILE_START_LESS".
PERFORM GET-A-UPCXX-FILE.
ZA-UPCXX-FILE-S-NOT-LESS.
PERFORM SET-A-UPCXX-FILE.
INVOKE A_UPCXX_FILEOBJ
"A_UPCXX_FILE_START_NOT_LESS".
PERFORM GET-A-UPCXX-FILE.
Some of the VB.NET code for a direct read. I’m not going to post the READ_NEXT code because while it works I am not happy with the way is works. I hope to see other concepts so I don’t want to prejudice anyone
Public Function A_UPCXX_FILE_READ()
A_UPCXX_FILE_INV_READ = 1
sSQL = "SELECT TOP 1 " & _
"AA_UPCXX_CATALOG, " & _
"AA_UPCXX_FILE_KEY, " & _
"IDI " & _
"FROM A_UPCXX_FILE " & _
" WHERE AA_UPCXX_CATALOG = '" & SQL_AA_UPCXX_CATALOG & "'" & _
" AND AA_UPCXX_FILE_KEY = '" & SQL_AA_UPCXX_FILE_KEY & "'" & _
" "
Dim myData As SqlClient.SqlDataReader
myData = GetDataReader(sSQL)
If myData.HasRows Then
myData.Read()
If myData.IsDBNull(000) Then SQL_AA_UPCXX_CATALOG = " " Else SQL_AA_UPCXX_CATALOG = myData.GetString(000)
If myData.IsDBNull(001) Then SQL_AA_UPCXX_FILE_KEY = " " Else SQL_AA_UPCXX_FILE_KEY = myData.GetString(001)
A_UPCXX_FILE_INV_READ = 0
End If
myData.Close()
End Function
