Saturday, January 4, 2014

Linux/DB2 running COBOL with inline SQL just like a mainframe

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.

Hold on, 1 step back please: what the <beep> is a precompiler? 

Please read this and ignore the babling about DBRM: http://vensind.blogspot.nl/2008/04/db2-precompile-bind-process.html
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: 

  1. a computer with linux (I used www.archlinux.org)
  2. db2 (I installed the express-C version) 
  3. open-cobol apt-get install open-cobol is enough for any debian based system)


Have fun and use old skills in a new environment.
















3 comments:

  1. Now, I wonder if your code can run under control of a transaction monitor:

    https://www.jboss.org/blacktie.html
    is an open source tm.

    ReplyDelete
  2. I did not find "-static -t " in the cobc option list

    ReplyDelete
  3. Do you have sample to use Open COBOL for DB2 stored procedure?
    Really appreciated.

    ReplyDelete