Fujitsu Cobol & DB2 personal edition

dave9rave · #1 (**permalink**) 06-24-10, 15:57

Hi - my 1st post. I have been using the old Fuji Cobol V3 for several years and find it quite adequate for my purposes, all 'batch' programs nothing too fancy. Now I would like to access a DB2 database on my PC, create a cursor and do some further analysis on the retrieved rows. So - has anyone done this particular combination (Fuji Cobol / DB2)? If so any examples would be a great help. Running Windows XP Prof, SP2 on the PC.
Thanks, Dave Plump

dr_te_z · #2 (**permalink**) 06-28-10, 05:04

Just code your inline SQL the same way as you would do on the mainframe. Do not edit an *.cbl or *.cob file, but a *.sqb file. That file can be used as input for the precompiler (db2prep). The output is a *.cbl and a *.bnd file which can be compiled by fuji and bound by db2. I've got working examples (linux/opencobol) is you're interested.
You'll be pleased to learn that you can even generate copybooks using the db2dclgen command.

dave9rave · #3 (**permalink**) 06-30-10, 16:29

Thank you dr_te_z for your insights into this situation which I am now exploring. Dave

Walterke · #4 (**permalink**) 12-12-11, 08:16

Hello dr te z,
you mentioned that you have some examples of opencobol programs that are used in combination with DB2. Since I just started using opencobol (I always have been programming on z/OS with Cobol and DB2) I'm very much interested to view some examples. I'm using opencobol 1.1 and DB2 express-C version 9.7
I'm also interested in the correct procedures to become an executable file.
I already found that I have to write an .SQB-file instead of .CBL. First I have to precompile in DB2 and after that I can generate in opencobol.
Could you please inform me where I can find the examples that you mentioned.
Kind regards
Walter

dr_te_z · #5 (**permalink**) 12-13-11, 16:53

Hello Walter,

I will try to help you. I'v got it working on linux.
This cobol source (in the hello world catagory)

Code:

       IDENTIFICATION DIVISION.
       PROGRAM-ID.     cblsql2.
       ENVIRONMENT    DIVISION.
       INPUT-OUTPUT    SECTION.
       FILE-CONTROL.
       DATA           DIVISION.
       FILE            SECTION.
      *
       WORKING-STORAGE SECTION.
       77   W-START-WS                 pic X(08) value 'Start WS'.
       77   w-sqlcode     pic z(8)9+.
       77   w-dis-count   pic z(8)9+.
       01   switsjes.
            03 sw-8000_curs pic 9.
            88 sw-8000_curs-open value 1.
            88 sw-8000_curs-fets value 2.
            88 sw-8000_curs-clos value 3.
 
      /     DB2 thingies  
       exec sql
            include sqlca
       end-exec.
       exec sql begin declare section        end-exec.
       01   w-userid    PIC X(08).
       01   w-password  PIC X(08). 
       01   w-count     PIC S9(4) comp-5 .
       01   w-empno     pic  X(06) value  space.
       01   w-birthdate pic  X(10) value  space.
       01   k-empno     pic  x(06) value '200220'.
       exec sql end    declare section        end-exec.
      /
       PROCEDURE DIVISION.
       0000-main  section.
       0000-010.
           perform 0020-init-connect.
           perform 0100-main-process.
           perform 0090-exit-reset.
	0000-090.
           stop run.
      /
       0020-init-connect section.
       0020-010.
           exec sql 
                CONNECT TO sample 
           end-exec.
           if SQLCODE not equal zero 
           then move SQLCODE                   to w-sqlcode
                display 'CONNECT failed with rc ' w-sqlcode
           else display 'CONNECT :)' 
           end-if.            
       0020-090.
           exit.
      * 
       0090-exit-reset section.
       0090-010.
           exec sql 
                CONNECT RESET 
           end-exec.
           if SQLCODE not equal zero 
           then move SQLCODE                         to w-sqlcode
                display 'reset CONNECT failed with rc ' w-sqlcode
           else display 'reset CONNECT :)' 
           end-if.            

       0090-090.
           exit.

       0100-main-process section.
       0100-010. 
           set  sw-8000_curs-open to true.
           perform 8000-emp-curs.

           set  sw-8000_curs-fets to true.
           perform 8000-emp-curs
           perform until SQLCODE not equal zero 
               display w-empno space w-birthdate 
               perform 8000-emp-curs
           end-perform.

           set sw-8000_curs-clos to true.
           perform 8000-emp-curs. 
       0100-090. 
           exit.

      /  CURSOR declaration & processing
       8000-emp-curs section.
       exec sql 
        declare c8000 cursor for 
                select    empno
                     ,    birthdate  
                  from    db2inst1.employee
       end-exec.
       8000-010.
           evaluate true 
              when sw-8000_curs-open
                   exec sql 
                       open c8000
                   end-exec
              when sw-8000_curs-fets 
                   exec sql 
                      fetch c8000
                       into  :w-empno
                           , :w-birthdate 
                   end-exec
              when other  
                   exec sql
                      close  c8000
                   end-exec                
           end-evaluate.
           if SQLCODE equal zero or +100
           then continue
           else move SQLCODE to w-sqlcode
                display 'sqlCode      : '  w-sqlcode 
                display ' sw-8000_curs: '  sw-8000_curs
                display 'sqlerrm      : '  sqlerrm
           end-if. 
       8000-090.
           exit.

pre-compile, compile & link with this:

Code:

#!/bin/sh -x
#
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/opt/ibm/db2/v9.7/lib64 -ldb2 -v -x -save-temps
db2 bind ${1}.bnd
db2 connect reset

and the result:

Code:

┌─[22:44][dick@arch64:~/oldWasMachine]
└─> ./cblsql2
CONNECT :)
000010 08/24/1963
000020 02/02/1978
000030 05/11/1971
000050 09/15/1955
000060 07/07/1975
000070 05/26/2003
000090 05/15/1971
000100 12/18/1980
000110 11/05/1959
000120 10/18/1972
000130 09/15/1955
000140 01/19/1976
000150 05/17/1977
000160 04/12/1980
000170 01/05/1981
000180 02/21/1979
000190 06/25/1982
000200 05/29/1971
000210 02/23/2003
000220 03/19/1978
000230 05/30/1980
000240 03/31/2002
000250 11/12/1969
000260 10/05/1976
000270 05/26/2003
000280 03/28/1976
000290 07/09/1985
000300 10/27/1976
000310 04/21/1961
000320 08/11/1962
000330 07/18/1971
000340 05/17/1956
200010 08/14/1973
200120 10/18/1972
200140 01/19/1976
200170 01/05/1981
200220 03/19/1978
200240 03/31/1984
200280 03/28/1966
200310 04/21/1961
200330 07/18/1971
200340 05/17/1956
reset CONNECT :)

┌─[22:44][dick@arch64:~/oldWasMachine]
└─>

I've posted this before and I saw that people have been struggling with this on ms-windows http://www.opencobol.org/archive/1135.txt

Walterke · #6 (**permalink**) 12-15-11, 04:17

Hello dr_te_z,

thank you for your reply.
I already found the link to the opencobol forum you mentioned a few days ago, but I still have the problem that the module 'sqlgstrt' can not be found. I can not contact the forum from opencobol because I lost my password and I can not register again because they don't accept registrations for the moment.

I copied your code and executed the steps you mentioned :
1. Precompile
db2 => connect to sample

Database Connection Information

Database server = DB2/NT 9.7.5
SQL authorization ID = VERBRAWP...
Local database alias = SAMPLE

db2 => prep G:\Eclipse\Workspace\Test\Omzet2\cblsql2.sqb bindfile
target ANSI_COBOL

LINE MESSAGES FOR cblsql2.sqb
--------------------------------------------------------------------
SQL0060W The "COBOL" precompiler is in progress.
SQL0091W Precompilation or binding was ended with "0"
errors and "0" warnings.

The result of this was a .cbl and a .bnd file

2. Compilation of the program
The command that you mentioned didn't result in an .exe file. Therefore
I used the following command :
cobc -x cblsql2.cbl -I F:\IBM\SQLLIB\INCLUDE\cobol_a -L F:\IBM
\SQLLIB\LIB -ldb

The result was a .exe file

3. Binding of the program
db2 => bind G:\Eclipse\workspace\test\omzet2\cblsql2.bnd

LINE MESSAGES FOR cblsql2.bnd
------ ---------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.

db2 => connect reset

4. Execution of the program
G:\Eclipse\Workspace\Test\OMZET2>cblsql2
libcob: Cannot find module 'sqlgstrt'

These are the environment variables defined :
COB_CONFIG_DIR=F:\Programming\OpenCobol\config\
COB_COPY_DIR=F:\Programming\OpenCobol\copy;F:\IBM\ SQLLIB\include
\cobol_a\
COB_LIBRARY_PATH=F:\Programming\OpenCobol\bin\;F:\ IBM\SQLLIB
\INCLUDE\cobol_a\;F:\IBM\SQLLIB\LIB\;F:\IBM\SQLLIB \BIN\
COB_PRE_LOAD=db2agapi;db2api
COB_SCREEN_ESC=Y
COB_SCREEN_EXCEPTIONS=Y

I think I have to be patient untill I can register again for the opencobol
forum and ask them for a solution.

Kind regards
Walter Verbraeken

Walterke · #7 (**permalink**) 12-15-11, 05:44

Hello dr_te_z,

I just checked which DLL's are loaded when I execute the compiled program
and I noticed that the DLL's mentioned in the environment variable COB_PRE_LOAD are not loaded. I copied the DLL to the directory where my
executable file is, and even then the DLL is not loaded.

I investigated db2agapi.dll with 'Dependency Walker' and found that the module 'sqlgstrt' is included in this dll. So I believe that my problem will be solved when I get this dll loaded at the moment of execution of my program.

Kind regards
Walter Verbraeken

dr_te_z · #8 (**permalink**) 12-16-11, 03:17

Hello Walter, Your name sounds like belgium, am I right?

On linux I also bounced my head against the 'sqlgstrt' module, but the keyword 'static' in the cobc command string saved my day. Maybe the excutable is not optimal in a linux-world, but it works.
I am glad other people worked out the windows counterpart because this was started by me as a 'hobby project' and windows in not my hobby

The next phase will be to try to code and execute an open-COBOL-stored-procedure, like I did in the past on the mainframe.... must find the time...

Walterke · #9 (**permalink**) 12-16-11, 04:05

Hello dr_te_z,

I'm indeed living in Belgium (Turnhout).
I succeeded yesterday in starting the execution of the program, but now it
crashes everytime after a few seconds. I have to find out how I can debug it so that I can find out what te problem is. I'm afraid this will take me a lot of time and I don't know if my boss will give me this time.
Anyway, thanks for helping me so far.

Kind regards
Walter

dr_te_z · #10 (**permalink**) 12-16-11, 04:42

Turnhout! That is very close to the civilised world

Sorry, could not resist.

Walter, on windows it could be more easy to try to use the ODBC connection. It is more "common" and you will use dynamic instead of static SQL on DB2. Only mainframe DBA's become crumpy because of that.

Let me guess: ever since micro focus bought acucobol they got the monopoly on the non-mainframe-cobol market and the current licence fees drive you to open source solutions. Am I close?

Walterke · #11 (**permalink**) 12-19-11, 03:10

Hi dr_te_z,

I'm working as a consultant for 18 years (always mainframe) but for the moment the company I work for is looking for another client. Meanwhile I'm sitting at the office (studying) and the person responsible for education asked me to teach Cobol to one of my colleagues. Since we don't have a connection with a mainframe I have to teach him in a PC environment. Therefore I was trying to find a solution with OpenCobol, in combination with Eclipse and the
Cobos-project.
I'll see what I can find about using ODBC with OpenCobol. Thanks for the information so far.

Kind regards
Walter

dr_te_z · #12 (**permalink**) 12-19-11, 06:03

Jobs fled from Belguim to India eh?
Open cobol is very well suited for that purpose and I guess that the classic way of working (pre-compile/compile/link) is the best in this case. You could install the db2/opencobol on linux

. Then you will get the chance to learn unix/linux yourself. That will never hurt because mainframe clients will become more and more rare.
That's what I did. I am a former mainframer too

Regards,
Dick

Internet Services

Web Development

Digital Marketing

Consumer Tech