Hello

I'm trying to run simple stored procedure written in COBOL on DB2. However I have no idea how to force it to work...

Procedure code is as follows (file HELLO.cob):

IDENTIFICATION DIVISION.
PROGRAM-ID. HELLO.
ENVIRONMENT DIVISION.
DATA DIVISION.
PROCEDURE DIVISION.
DISPLAY "Hello, World!".
STOP RUN.

File has been compiled to executable, static library and shared library with TinyCOBOL compiler -> results are accordingly HELLO, HELLO.a, HELLO.so.
All of these files have been put to directory /home/db2inst1/sqllib/function and their privileges have been changed to 777.

I tried to create stored procedure using these files in several ways like this:

CREATE PROCEDURE PROCEDURE ()

EXTERNAL NAME 'HELLO'
EXTERNAL NAME 'HELLO!HELLO'
EXTERNAL NAME '/home/db2inst/sqllib/function/HELLO.so'
EXTERNAL NAME '/home/db2inst/sqllib/function/HELLO.so!HELLO'

LANGUAGE COBOL
PARAMETER STYLE SQL

But when I try to call procedure from DB2 I always get something like this:

Routine "PROCEDURE" (specific name "SQL090630090438500") is implemented with code in library or path ".../sqllib/function/HELLO", function ".../sqllib/function/HELLO" which cannot be accessed. Reason code: "4".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.7.89
Routine "PROCEDURE" (specific name "SQL090630091203800") is implemented with code in library or path ".../sqllib/function/HELLO", function "HELLO" which cannot be accessed. Reason code: "4".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.7.89
Routine "PROCEDURE" (specific name "SQL090630091347500") is implemented with code in library or path ".../sqllib/function/HELLO.so", function ".../sqllib/function/HELLO.so" which cannot be accessed. Reason code: "4".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.7.89
Routine "PROCEDURE" (specific name "SQL090630091711000") is implemented with code in library or path ".../sqllib/function/HELLO.so", function "HELLO" which cannot be accessed. Reason code: "4".. SQLCODE=-444, SQLSTATE=42724, DRIVER=4.7.89

Where is the problem ? What should I pass as EXTERNAL NAME? Or what should I put extra to directory /home/db2inst1/sqllib/function ?

Any help will be appreciated...

Best regards
Melmack

BTW: Stored procedures written in Java work fine in this configuration.


Below I attach environment info:

$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL09070"
with level identifier "08010107".
Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXIA3297", and Fix Pack
"0".
Product is installed at "/opt/ibm/db2/V9.7".

$ db2licm -l
Product name: "DB2 Express-C"
License type: "Unwarranted"
Expiry date: "Permanent"
Product identifier: "db2expc"
Version information: "9.7"
Max number of CPUs: "2"
Max amount of memory (GB): "2"


$cat /proc/version
Linux version 2.6.26.8-57.fc8 (mockbuild@x86-3.fedora.phx.redhat.com) (gcc version 4.1.2 20070925 (Red Hat 4.1.2-33)) #1 SMP Thu Dec 18 19:19:45 EST 2008

Cobol stored procedures on DB2/linux are not supported as far as I know. Micro Focus has a solution. Personally I'm experimenting with OpenCobol in combination with the db2-cobol-pre-processor. This works, but I have to use C as intermediate language. You, working with Tiny will have to use assembly I suppose. So see if you can use assembler code as stored procedure (!google!) and then take you cobol source and ask Tiny to generage assembler-code for you. That should be the approach.
Please keep me informed, I thought I was the only one trying to combine linux/db2 with the open source cobol compilers

Thanks for answer

Finally I managed to by-pass the problem.
I simply wrote stored procedure in Java (this works fine as I stated above).
Then I compiled program in COBOL to dynamic library (*.so) and added in Java code call to function from this library (through JNI).
Of course this is not an ideal solution (Java as superfluous layer) but at least one that seems to be working

Best regards
Melmack

Nice! But are you planning to access DB2 from the COBOL-SP?

How exactly do you do that ?

db2 prep generates a .cbl file, but i have plenty of cobol warnings (use of undeclared host variable), and no .bnd file is generated.

After that i'm asking to cobc to generate a C file, but what's next ? How should i install the procedure in DB2 ?

already described here: Fujitsu Cobol & DB2 personal edition

Hi,

Already saw this post.
This describes how to call SQL or stored proc from an external cobol program, am i right ?

That's not what i'm looking for...

I want to migrate a stored procedure written in COBOL on a mainframe to a DB2LUW running on linux with opencobol (or an opensource COBOL).

Wrong. It describes how you can code a cobol program with inline SQL (like a mainframe source) and execute that on a linux machine without having to purchase Micro Focus cobol.

Building a cobol-stored-procedure is 1 step further and I have not figured that out yet. Have been thinking and experimenting but no success so far. I do not spend too much time because for me it is only a hobby (let's see if I can do that) and I do not have too much time to spare.

I will share my thoughts with you:
fact#1 Open Cobol + db2prep + compile/link works for "normal" programs
fact#2 DB2 LUW can handle stored procedures written in C with inline SQL
fact#3 An Open Cobol source can be translated into valid C code

The problem is the sequence of steps and the link/edit phase:
Normal situation:
- code C with inline SQL
- db2 prep the above C source to obtain a new C soure and a db2 plan
- compile & link the new C source with the shell script provided by IBM

Desired situation:
- code COBOL with inline SQL
- db2 prep the above COBOL code to obtain a new COBOL source and a db2plan
- use cobc to translate the new COBOL source into C
- compile and link the new C source with the shell script provide by IBM

Why does it not work (yet)/what has to be figured out?
the C code coming from cobc contains a lot of calls to the open-cobol modules/libraries. I am not familiar enough with the link-edit properties to achieve that. You will have to:

A. force the script to do static linking of all the open cobol modules/libraries
B. tell the O.S. or DB2 to include the open cobol libraries in their search path.

This area is too blurry for me. I do not know if A is better than B or vice versa. I have not figured that out yet.

If you can take it from there...