Multiple record output file with variable field emulation from COBOL

Hey all of you programmers (not developers :P) in the COBOL world.

I love COBOL, I’ve actually sold my fist COBOL application in 1987, a video club.  Well I guess I am ancient, but I saw things work at 8088 atx pc’s with 2 floppy disk, one for the OS and the COBOL runtime and one for the data.

I’ve worked COBOL mostly on UNIX servers with index files, and yes I’ve worked .NET VB C# and SQL SERVER too, but as life goes to circles I’m again at iseries COBOL environment with emended DB2 SQL

Oh, all you lucky ones for this blessing. Unlimeted power if you combine the tools, not only combine, overlap them to the far edges. Because I saw that COBOL programmers don’t want to be involved much with SQL and vice versa.

Well, personally I’ve worked both environments, loved both techniques and when I found them together I wanted to bring them as close they could get so I present their child.

Multiple record output file with variable field emulation from COBOL

Ok, I know, I did not invented the wheel, but I know back in the 90’s how costly was in time and effort to do all the work. Design the Cobol output records, make the Z masks, keep in track, print with semicolon, and have a csv to work in XL. OK. No variable fields though eh?

Time to go beyond. Time for the code which also started differently because of the urgent of the project.

Well I knew that the requirements of the output was a csv of multiple records and variable length fields(put that aside).

But it was damn urgent and it had its difficulties as you had to open cursor over cursor over cursor.

Get sums by customer, product month.

Calculate fields, interests, percentages,

Sup up sub totals again

Sum up totals by product month

Trail to output 8 different kind of records of other data.

All of us coding on COBOL we know, 3.000 lines of coding lol

So I decided to go by just SQL and see what happens…

1 cursor with doing_it_all_sql statement RAPID development in 2 days I’ve done the tests

That was pretty a 3 level SQL statement that turn to my cursor (not so simple as there were joins cases, complex where and so on).

  

An Inner pool fetched groups of totals per customer, product month

A middle pool took the fields and made all calculation action

And Finally an outer Join that made the total sum ups group by product month.

TOTAL_POOL:

(OUTER POOL:

(SELECT SUM(MP.FIELD1, AVG(MP.FIELD2), AND SO ON

MIDDLE POOL:

(SELECT IP.FIELD1  * MJ1.FIELD1 /MJ2.PERCENTAGE (AND SO ON)

FROM(

INNER POOL :   SELECT I_FIELD_1, I_FIELD2…. SUM(I_FIELDn

                            FROM

                            INNER JOINS

                            WHERE

                            GROUP BY I_FIELD_1, I_FIELD2…. SUM(I_FIELDn) IP

INNER JOIN MJ1 ON

INNER JOIN MJ2 ON

WHERE) AS MP

WHERE

GROUP BY SUM(MP.FIELD1, AVG(MP.FIELD2), AND SO ON) AS OUTER POOL) as TP

Standard SQL, standard procedure for SQL developers, but not for COBOL ones. It should because when you start thinking bind with the tool you are limited to the tool. When you think the solution and not the tools you choose the tool.

I had already decided to have just my output record and no other COBOL command than

WRITE OUTPUT-REC.

I  already fetched my fields  so either I have it on WORKING storage (through FETCH INTO or even better I nest an another select level above OUTER POOL that goes like this. I would recommend a different step for purposes of code control but it could be a final nest of a FROM SYSIBM.SYSDUMMY  for performance purposes.

SELECT A.TRIM([FORMAT_VARCHAR]A.FIELD) || ‘;’ || TRIM(TP.FIELD1) ||’;’||’This is a vertical label:’ ||’;’||FORMAT_VARCHAR(B.DATEFIELD, YYYYMM) and so on and so forth

FROM TP or SYSIBM.SYSDUMMY1

INTO :OUTPUT-REC

INNER JOIN A ON

INNER JOIN B ON

WHERE

ORDER BY

END-EXEC

WRITE OUTPUT-REC (the maximum length required)

And the code went on to gather the other kind of records, concat fields, format them as needed and output them, rapidly.

Hope this would be useful  to some of you 

Polykarpos Parioritsas

sr. IT Analyst