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
