Thursday, April 17, 2014

Another look at the DB2 client

Triggered by this
I have been playing/learning about the new way of configuring db2 clients.

It seems that the “db2 catalog tcpip node” syntax is soo 2013 and db2cli is not only an .ini file anymore, but also a real db2 command. This combined with the noble art of selection the right version justifies the time spent on this subject.

When you look at the size of some versions (ODBC/CLI only 33 MB) you cannot help thinking that the 'old way'  is not going to work because the db2cmd shell on windows will not be there anymore.
So no more db2 commands. Scary thought.

I will not go into detail because the links mentioned earlier covers it just fine and I've got nothing to add. The only thing I want to mention is that an external database configured the new way:
  • not found in “db2 list db directory"
  • present in the db2dsdriver.cfg file
can be used by clpplus & odbc but a simple “db2 connect to” will not work. The good old  db2cmd box does not seem to use the new file. The situation described by Ian in his blog could not be re-produced by me.

Very nice all but:

  • the db2cli.ini file is still there and is being used
  • the db2dsdriver.cfg file seems to have a lot of overlap in functionality with db2cli.ini, so what would be the best approach? Do we still need the db2cli.ini or is the configuration ideally 100% described in the db2dsdriver.cfg?
  • The db2cli.ini file must be maintained using the “db2 update cli cfg”  command, so the db2cmd box is still required (yes you can edit by hand, but a worthy DBA does not do that kind of things, right?)
So, I do not know about you, but it is not yet clear to me. Hope IBM clarifies things soon.

Next I was involved with a project in which a windows 2008 server needed an ODBC connection  to the db2-mainframe without using the db2 connect gateway like we always do. 

IBM sales told us that this solution is becoming obsolete and nowadays the CLI clients can connect directly to the mainframe, just like their JDBC type 4 cousins. Just add the licence file.

Aha! Now I can use my fresh knowledge about the db2cli command creating the db2dsdriver.cfg file! 

I know the new way of:
・    catalog tcpip node
・    catalog database
・    catalog system odbc

and that is enough to configure a perfect ODBC connection to a LUW database. 

For the mainframe I need 1 additional line and I only have to ask Google the new syntax to “catalog dcs db” and it will work.....



<knock>  <knock>

I asked you a question.....


Silence.... No hits... No go..

Bottom line: 
  • Yes I managed to import our connect licence file in the client
  • Yes I managed to make a connection from CLI/ODBC to the mainframe bypassing the connect gateway
  • No I could not configure the connection using the new db2dsdriver.cfg file for the mainframe. 
  • The catalog-knowledge inside my head is still valuable :)

IBM: please correct me if I am wrong.

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:
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).

       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
       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

           exec sql
              include sqlca
       procedure division.
       000-mainline               section.
           perform 010-init-program.
           perform 100-main-program.
           perform 090-exit-program.
           stop run.

       010-init-program           section.
           perform 901-db2-connect.
           set sw-curs-open  to true.
           perform 910-c-employee.
           set sw-curs-fetch to true.

       100-main-program           section.
           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
       090-exit-program           section.
           move tel to int-opmaak.
           display     int-opmaak.
           set sw-curs-close  to true.
           perform 910-c-employee.
           perform 909-db2-connect-reset.
       901-db2-connect            section.
           exec sql
              connect to sample
           if sqlcode equal zero
           then display "connect succeeded"
           else display "connect failed"
                stop run 
       910-c-employee             section.
           exec sql
                declare c_employee cursor  for
                select firstnme
                  ,    lastname
                  ,    midinit
                  from db2inst1.employee
                 where sex = ucase ('f')
           evaluate true
               when sw-curs-open
                    exec sql
                         open c_employee

               when sw-curs-close
                    exec sql
                         close c_employee

               when other
                 move space to                   w-firstnme   
                                              ,  w-lastname
                                              ,  w-midinit
                 exec sql fetch c_employee into :w-firstnme 
                                              , :w-lastname
                                              , :w-midinit-txt
                 if w-midinit-null
                    move '0' to w-midinit
           evaluate sqlcode
               when zero
               when +100  continue
               when other
                    display "cursor failed"
                    stop run 

       909-db2-connect-reset      section.
           exec sql
              connect reset

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:

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
  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.