It is 2014. A year for me to start blogging.
Like I said, I feel comfortable in both worlds, mainframe & unix/linux. What makes me feel less comfortable is the fact that my
mainframe skills are hardly of any use in my current job. For reasons I never
understoot REXX never made it as a scripting language in the
unix/linux world (although language was available from the very 1st
start of linux).
COBOL
is a different story. Besides COBOL on the mainframe I've been lucky
to work with COBOL on Unix & windows as well using the Micro
Focus and Acu COBOL compilers. I have enjoyed myself a lot during
those assignments.
Sadly economic rules in the 21st
century have moved the art of COBOL coding from the Northern to the
Southern hemisphere and I happen to live on the Northern... so I had
to close that book professionally and I ended up being a DBA on Unix/Linux/windows a.k.a. 'dinky toys' :).
Computers
are both my profession and my hobby and in my spare time I found the
open-source COBOL compilers and I liked what I saw. Especially the
'open-cobol' project appealed to me.
The
area between open-source and closed-proprietary software can also
qualified as a 'twilight zone' and a challenge to explore.
I my case
this would be to access DB2 from open-cobol using exactly the same
syntax as I used to code back in my mainframe years. I remembered a
project in the 20th century where I did the same using
MicroFocus COBOL together with DB2/2 on OS/2. So I knew that 'my'
version of DB2 used to have a COBOL-precompiler as well.
That was not
strange in those days: sybase, adabas-d and oracle had one as well. I
know that adabas-d, in the transision to Max-DB, lost the
COBOL-precompiler and the rest, as far as I know, still supports
COBOL with inline SQL.
In
the mainframe DB2 world 'static SQL' is well known and often the only
acceptable way to access DB2 from application programs. In the LUW
world 'static SQL' is not something most DBA's are familiar with and
binding packages is something you must do once after installing or
updating DB2.
When
you start working with Stored Procedures it becomes more real and you
realize that 'binds' are also required after a new index has been
created and they become part of your housekeeping jobs to make
optimal use of your new runstats stastistics.
But
how does it work here? Stored Procedures do not produce any DBRM
objects. Well, I am relieved that I do not have to explain about DBRM
in this context because the DB2-LUW-COBOL-precompiles does not
produce them either. Instead only a bind-file is created.
That
is the mainframe situation and it works very similar on LUW.
I'll
try to make a drawing:
It
all starts with a COBOL/SQL source file (.sqb). All the SQL is
embedded in “exec sql” and “end-exec” block (bit like XML).
Example:
identification division.
program-id. prog001.
environment division.
configuration section.
special-names. decimal-point is comma.
***************************************
data division.
working-storage section.
77 tel pic s9(9) comp-5, value zero.
01 opmaak.
03 int-opmaak pic z.zzz.zz9- .
01 switses.
03 sw-curs pic 9(1).
88 sw-curs-open value 1.
88 sw-curs-fetch value 2.
88 sw-curs-close value 3.
exec sql
begin declare section
end-exec.
01 w-firstnme.
49 w-firstnme-len pic s9(4) comp-5.
49 w-firstnme-txt pic x(12).
01 w-lastname.
49 w-lastname-len pic s9(4) comp-5.
49 w-lastname-txt pic x(15).
01 w-midinit.
49 w-midinit-txt pic x(01).
49 w-midinit-nul-ind pic s9(4) comp-5.
88 w-midinit-null value -1.
exec sql
end declare section
end-exec.
exec sql
include sqlca
end-exec.
procedure division.
/
000-mainline section.
************************************
000-010.
perform 010-init-program.
perform 100-main-program.
perform 090-exit-program.
000-090.
stop run.
/
010-init-program section.
************************************
010-010.
perform 901-db2-connect.
set sw-curs-open to true.
perform 910-c-employee.
set sw-curs-fetch to true.
010-090.
exit.
/
100-main-program section.
************************************
100-010.
perform 910-c-employee
perform until sqlcode equal +100
display w-firstnme-txt
space w-lastname-txt
space w-midinit
add 1 to tel
perform 910-c-employee
end-perform.
100-090.
exit.
/
090-exit-program section.
************************************
090-010.
move tel to int-opmaak.
display int-opmaak.
set sw-curs-close to true.
perform 910-c-employee.
perform 909-db2-connect-reset.
090-090.
exit.
/
901-db2-connect section.
************************************
901-010.
exec sql
connect to sample
end-exec.
if sqlcode equal zero
then display "connect succeeded"
else display "connect failed"
stop run
end-if.
901-090.
exit.
/
910-c-employee section.
************************************
exec sql
declare c_employee cursor for
select firstnme
, lastname
, midinit
from db2inst1.employee
where sex = ucase ('f')
end-exec.
910-010.
evaluate true
when sw-curs-open
exec sql
open c_employee
end-exec
when sw-curs-close
exec sql
close c_employee
end-exec
when other
move space to w-firstnme
, w-lastname
, w-midinit
exec sql fetch c_employee into :w-firstnme
, :w-lastname
, :w-midinit-txt
:w-midinit-nul-ind
end-exec
if w-midinit-null
then
move '0' to w-midinit
end-if
end-evaluate.
*
evaluate sqlcode
when zero
when +100 continue
when other
display "cursor failed"
stop run
end-evaluate.
910-090.
exit.
/
909-db2-connect-reset section.
************************************
909-010.
exec sql
connect reset
end-exec.
909-090.
exit.
Yes,
COBOL is verbose. Nobody is going to deny that fact.
What do we see?
The programs builds a database connection to the sample database. We run this from the CLI as instance owner so user-id and password parameters are not required. The employee table is accessed for more that 1 row. This means that we must work with a cursor. When you have worked with java/jdbc or perl/DBI you'll recognize the structure. In this case 3 columns are selected from the table to show the concept of varchar and nullable columns. Because this is static SQL there is no need to 'prepare', but the cursor SQL has to be coded 'somewhere' in the source. You can do this either in the data-division or in the procedure division. The fact that it is specified in the same section where also the open/close/fetch is coded is my personal choice. The open/fetch/close logic remains the same as java or perl using dynamic SQL. All the selected colums are displayed. The NULL will be shown as “0”.
This source will compile & run on a mainframe as well (perhaps some very minor changes).
The
COBOL compiler does not know about this SQL blocks and a
clean-compile is out of the question with this source. The
db2-cobol-preprocessor finds & interprets these blocks and
degrades them into comment lines in the COBOL source. If the all the
SQL is error-free the COBOL source is enriched with additional COBOL
source-lines to ensure that the program will perform the right calls
to db2 on the right time on the right place. Another file is written
containing all the SQL. This file is called the bind-file.
The
1st output file (.cbl) contains pure COBOL now and can be
compiled & linked by the open-cobol compiler. When you examine
all the additional files created by the compiler you can see that it
boils down to a conversion to C code and let gcc have it's way.
The
2nd output file (.bnd) can be used to BIND and create a
db2 package.
In
a script the above looks like this:
#!/bin/sh
#
rm
./${1}.cbl
rm
./${1}
db2
connect to sample
db2
prep ${1}.sqb bindfile target ANSI_COBOL
cobc
${1}.cbl -static -t ${1}.lst -Wall -L/home/db2inst1/sqllib/lib64
-ldb2 -v -x -save-temps -O
db2
bind ${1}.bnd
db2
connect reset
The
link parameters did cost me some time to figure it all out, but these
settings work.
Kids:
if you want to try this at home you'll need:
- a computer with linux (I
used www.archlinux.org)
- db2
(I installed the express-C version)
- open-cobol
( apt-get install
open-cobol
is enough for any debian based system).
Have
fun and use old skills in a new environment.
