Compiling and linking stored procedures under DB2 LUW

Compiling and linking a PL/I stored procedure using DB2 LUW is a bit different than compiling and linking most PL/I applications. Use this procedure to compile and link DB2 stored procedures under DB2 LUW.

Note: The DB2 LUW preprocessor defaults to option COMMIT=2, where COMMIT statements are implicitly generated on STOP RUN statements and at the end of the program. When PL/I stored procedures are called from within a global transaction (for instance, from within CICS/IMS online transactions or IMS BMPs/DSNRLI programs), set COMMIT=1 to avoid generating the COMMIT call and its associated SQLCODE -30090 error message. See the COMMIT reference topic for more information.

Use mfplx to compile the program you want to use as a stored procedure with the DB2 SQL Option just like any DB2 LUW program. In this case though, you just want to create an OBJ file. To do this, specify the -c compiler option with mfplx.
If your program contains stored procedure CALL statements, you must specify the CALL_RESOLUTION DB2 SQL option to prevent SQL error SQL0204 from occurring. For example:
```
mfplx -sql db2 -optsql DB=SAMPLE getprml.pli -nolaxdcl –ppsql -c
```
Note: Compile stored procedures using the -noaxdcl compiler option to prevent undeclared variables. Undeclared variables can cause a range of problems with stored procedures.
Windows:

To link the program as a Dynamic Link Library (.dll), you use CBLLINK rather than the normal PL/I link command.

To link a PL/I DB2 stored procedure, you must link extra modules so that DB2 LUW can load the PL/I run time and load the PL/I routine properly.

If the stored procedure is not defined with an OPTION of MAIN, add the systemsql.obj and setupdeffiles.obj object modules, as well as the mfplimd.lib and the 32-bit db2api.lib library files to the link command. For example:
```
cbllink /d /V /K GETPRML.obj db2api.lib mfplimd.lib systemsql.obj setupdeffiles.obj
```
If the stored procedure is not defined with an OPTION of MAIN, link these additional modules in the link command:
- lpimainstub.obj
- lpimain.obj
- mfexttbl.lib
For example:
```
cbllink /d /V /K GETPRML.obj db2api.lib mfplimd.lib mfexttbl.lib systemsql.obj setupdeffiles.obj lpimainstub.obj lpimain.obj
```
UNIX:

To link the stored procedure, you must build the module using cob, ensuring that the resultant module has no file extension.

On AIX platforms, you should use the following command to build the stored procedure as an executable :
```
cob -x GETPRML.o -e GETPRML -L$COBDIR/lib -lmfpliz -lplidump
```
On other UNIX platforms, you should use the following command format to build the stored procedure as a self-contained Callable Shared Object :
```
cob -y GETPRML.o -o GETPRML -e GETPRML $COBDIR/lib/32/dllmain_cob.o -L$COBDIR/lib -lmfpliz -lplidump
```
Copy the stored procedure object that is produced to the function directory under your DB2 instance.
Note: By default, after calling a stored procedure, DB2 LUW does not unload it called until DB2 LUW is stopped. To avoid issues copying the .dll, you can do one of the following if you are not in a production environment.
- Execute db2stop, copy the DLL, then execute db2start from a DB2 administrative command prompt.
- Execute the following command from a DB2 command prompt:
```
db2 update dbm cfg using KEEPFENCED NO
```
Executing this command in a production environment has a significant performance impact, so it should not be used. Refer to your IBM documentation for more information on this command.