Created On:  17 November 2010

Problem:

How can I build and run DB2 Stored Procedures on UNIX?
When call a COBOL DB2 Stored Procedure on UNIX, I get a SQL0444N error.
When call a COBOL DB2 Stored Procedure on UNIX, I get a SQL1131N error.
How can I use the DB2 ECM for creating DB2 Stored Procedures on UNIX ?

Resolution:

In order to build DB2 LUW applications, you must have installed the DB2 Application Development Client, and preferably also the Application Development Client Samples.

DB2 stored procedures execute as a setuid application on UNIX, which means that -- for OS security reasons -- do not inherit the environment when executed. In order to set up your environment, you need to do the following :

1. Prior to starting your DB2 instance, set up your COBOL environment, typically COBDIR, PATH, and LD_LIBRARY_PATH/LIBPATH/SHLIB_PATH, as appropriate for your platform.

See KB article #32552 for details on how to set up the DB2 environment correctly.

2. In order for DB2 to inherit the setting of COBDIR, you must use the db2set command as follows :

   db2set DB2ENVLIST="COBDIR"

You can invoke

   db2set

to confirm that the setting has been made. You can then restart your DB2 instance. Please refer to IBM DB2 documentation for further details on the db2set command.

3. Due to LD_LIBRARY_PATH (etc.) not being inherited by setuid applications, any dependant shared objects need to be available from within a trusted directory. The easiest way to achieve this is to set the DB2LIBPATH variable to include $COBDIR/lib , for example:

db2set DB2LIBPATH=$COBDIR/lib

4. The build scripts shipped by IBM with the Application Development Client, under samples/cobol_mf, contain a build script, bldrtn, which be used for building your Stored Procedure.

**NOTE 1**  If running Server Express 4.0 or later, you may need to amend the bldrtn script, to uncomment the setting of an environment variable :

#USE_SX40=1

**NOTE 2** If working in a 32-bit environment, to ensure that the client-side application and stored procedures get built as 32-bit applications, you will need to update the bldapp and bldrtn scripts, per the comment within those scripts :

# Uncomment the next line to force a 32-bit application compile/link
#BITWIDTH=32

**NOTE 3** There is a known issue with the IBM supplied build scripts being the cause of the 1131N error.  Up until FixPak 4 IBM DB2 V8 contained incorrect build instructions for creating COBOL stored procedures (bldrtn), specifically on AIX platforms.

If, having worked through the other steps listed within this article and you are still getting errors, we strongly suggest that you upgrade to the latest FixPak level, where a corrected bldrtn script is provided.

5. Using the DB2 ECM (DB2 compiler directive) to build the stored procedure.

These scripts use IBM's command-line precompiler, 'db2 prep' for building the client and server pieces. It is also possible to utilise the DB2 ECM for building the client and server modules. You will need to reference the embprep, bldapp and bldrtn scripts to determine the exact command line for your platform. For example, for x86 running Linux, you can use the following command-lines for building the outcli/outsrv samples provided by IBM :

Stored Procedure:
cob -yo outsrv -k outsrv.sqb checkerr.o -C "MFSYNC db2(db==sample)" -d cobdb2

Client modfule:
cob -xk outcli.sqb checkerr.o -C "db2(db==sample call_resolution==deferred)"  -L $DB2DIR/lib -ldb2 -ldb2gmf -d cobdb2

6. Debugging/Run-time Configuration

If you wish to utilise COBOL run-time tuneables via cobconfig, or enable the Consolidated Trace Facility for the stored procedure, you will need to set additional environment variables prior to starting the DB2 instance:

For run-time tuneables, ensure that the COBCONFIG environment variable is set to your configuration file. If you wish to generate a core dump on encountering an error for debugging purposes, then your COBCONFIG file should contain :

set core_on_error=3
set core_filename="explicit path to writeable core file"

for example

set core_filename="/tmp/mycore"

For the Consolidated Trace Facility, ensure that the MFTRACE_CONFIG environment variable is set to your configuration file.

Then, use the DB2ENVLIST mechanism for ensuring that DB2 inherits these environment settings, for example :

db2set DB2ENVLIST="COBDIR LD_LIBRARY_PATH PATH COBCONFIG MFTRACE_CONFIG"