Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Cannot run COBOL stored procedure on DB2 - SQL error 444

    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

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    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

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Nice! But are you planning to access DB2 from the COBOL-SP?

  5. #5
    Join Date
    Feb 2012
    Posts
    2

    Question Could you be more explicit

    Quote Originally Posted by dr_te_z View Post
    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.
    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 ?

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

  7. #7
    Join Date
    Feb 2012
    Posts
    2

    Exclamation out of scope ...

    Quote Originally Posted by dr_te_z View Post
    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).

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by nka View Post
    This describes how to call SQL or stored proc from an external cobol program, am i right ?
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •