How to create and deploy DB2 stored procedure

Hi,

Thanks for the suggestions.  However, I followed your instructions and the stored procedure compilation still fails with the following errors:

DB2ADMIN.AUDIT_EVENT_REMOVE_SP - Create stored procedure returns -7032.

DB2ADMIN.AUDIT_EVENT_REMOVE_SP: *** BIND

D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp\P3304730.sqc ***

LINE    MESSAGES FOR P3304730.sqc
------  --------------------------------------------------------------------        
          SQL0060W  The "C" precompiler is in progress.        
          SQL0091W  Precompilation or binding was ended with "0" errors and "0" warnings.

*** COMPILE D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp\P3304730.c

***DB2_SQLROUTINE_COMPILER_PATH=E:\SQLLIB\function\routine\sr_cpath.bat

@echo onset SQLROUTINE_FILENAME=P3304730
set db2path=D:\SQLLIBnmake /f D:\SQLLIB\function\routine\sqlproc.mak
*** D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp\P3304730.def
***LIBRARY P3304730EXPORTS _pgsjmp@12pgsjmp=_pgsjmp@12D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp>set SQLROUTINE_FILENAME=P3304730 D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp>set db2path=D:\SQLLIB D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp>nmake /f D:\SQLLIB\function\routine\sqlproc.mak

'nmake' is not recognized as an internal or external command,operable program or batch file.
----------------------------------------------------------------------------
SQL7032N  SQL procedure "" not created.  Diagnostic file is "".  SQLSTATE=00000

DB2ADMIN.AUDIT_EVENT_REMOVE_SP - Changes rolled back.

DB2ADMIN.AUDIT_EVENT_REMOVE_SP - Build failed.

********************************************************************
Note: I had modified the sr_cpath.bat by removing all REM commands for the MS C++ compiler (see below).  I then added this file to the DB2_SQLROUTINE_COMPILER_PATH property (although I know it defaults to this file).  Should I have left these as comments?
********************************************************************
@echo off
set VCV6_DRIVE=C:\Program Files\Microsoft Visual Studio
set include=%include%;%VCV6_DRIVE%\VC98\atl\include;%VCV6_DRIVE%\VC98\mfc\include;%VCV6_DRIVE%\VC98\include
set lib=%lib%;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib
set path=%path%;%VCV6_DRIVE%\Common\Tools\WinNT;%VCV6_DRIVE%\Common\MSDev98\Bin;%VCV6_DRIVE%\Common\Tools;%VCV6_DRIVE%\VC98\bin;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib

@echo off
REM set VCV5_DRIVE=c:\Program Files\devstudio
REM set include=%include%;%VCV5_DRIVE%\vc\include;%VCV5_DRIVE%\vc\atl\include;%VCV5_DRIVE%\vc\mfc\include
REM set lib=%lib%;%VCV5_DRIVE%\vc\lib;%VCV5_DRIVE%\vc\mfc\lib
REM set path=%path%;%VCV5_DRIVE%\sharedide\bin\ide;%VCV5_DRIVE%\sharedide\bin;%VCV5_DRIVE%\vc\bin

********************************************************************
My sqlproc.mak file
********************************************************************

# Note: The default compiler options are set for the Microsoft Visual C++
#       compiler. If you use the IBM VisualAge C++ compiler, remove the
#       "COMPILER=MS" below and uncomment "COMPILER=IBM" that follows.
COMPILER=MS
#COMPILER=IBM

# Note: The default is to build with no DEBUG options. If you want to
#       build with DEBUG options, remove "DEBUG=NO" below and uncomment
#       "DEBUG=YES" that follows.
DEBUG=NO
#DEBUG=YES

CFLAGSDEB=
LINKFLAGSDEB=

!IF "$(COMPILER)" == "MS"
# Microsoft Visual C++ compiler
CC=cl
!IF "$(DEBUG)" == "YES"
CFLAGSDEB=-Z7
LINKFLAGSDEB=-debug:full -pdb:none -debugtype:cv
!ENDIF
CFLAGS=-Ox -W2 -TC -D_X86_=1 -DPSMDBG $(CFLAGSDEB) -I"$(DB2PATH)\include"
LINKFLAGS=/link -dll $(LINKFLAGSDEB) /out:$(SQLROUTINE_FILENAME).dll
!ENDIF

!IF "$(COMPILER)" == "IBM"
# IBM VisualAge C++ compiler
CC=icc
!IF "$(DEBUG)" == "YES"
CFLAGSDEB=-Ti
LINKFLAGSDEB=/DEBUG
!ENDIF
CFLAGS=-Ge- -Gm+ -W0 -DPSMDBG $(CFLAGSDEB) -I"$(DB2PATH)\include"
LINKFLAGS=/B"/ST:96000 /PM:VIO /DLL $(LINKFLAGSDEB)"
!ENDIF

LIBS="$(DB2PATH)\lib\db2api.lib" "$(DB2PATH)\lib\db2psmd.lib"

{$(SQLROUTINE_FILENAME)}.dll:
!IF "$(COMPILER)" == "MS"
      $(CC) $(CFLAGS) $(SQLROUTINE_FILENAME).c $(LINKFLAGS) -def:$(SQLROUTINE_FILENAME).def $(LIBS)
!ELSE
      ilib /GI $(SQLROUTINE_FILENAME).def
      $(CC) $(CFLAGS) $(SQLROUTINE_FILENAME).c $(LINKFLAGS) $(SQLROUTINE_FILENAME).exp $(LIBS)
!ENDIF

********************************************************************
The stored procedure that I'm trying to build/compile using DB2 Stored Procedure Builder
********************************************************************

CREATE PROCEDURE audit_event_remove_sp(IN v_cutofftime TIMESTAMP)
 RESULT SET 0
 SPECIFIC AUEVREM
 LANGUAGE SQL
--*****************************************************************************
--   Inserted
--   May 2003
--   remove - called in EventDB
--*****************************************************************************
L_audit_event_re:
BEGIN NOT ATOMIC
   DELETE FROM EVENT_AUDIT_TBL
       WHERE CREATIONTIME < v_cutofftime;
   --
END L_audit_event_re

*************************************************************
ANY suggestions would be greatly appreciated!  THANKS!
*************************************************************

To solve your compilation troubles, I suggest that you just:
-Modify your system environment variable PATH adding every directory with binaries (.exe) from your compiler
-Modify your system environment variable INCLUDE adding every directory with include files (.h) from your compiler
-Modify your system environment variable LIB adding every directory with libraries (.lib) from your compiler
-Comments again (REM) the MS variables in your sr_cpath.bat if you are not using MS-Visual C++
==> If this still do not solve your compilation problem, please provide us with the exact name and release of your compiler and your db2 soft.

You can automate the delivery of your procedures using one of the 2 above solutions:
-Compile your procedures on the target db (but it requires another server installation of your compiler)
-Use the SAR functions to export ("GET ROUTINE INTO audit_event_remove_sp.sar FROM PROCEDURE audit_event_remove_sp" locally on the source db) and import ("PUT ROUTINE FROM audit_event_remove_sp.sar" locally on the target db) your procedures (The target db needs to be initiated to SAR functions using "db2updv7 -d YourTargetDBAlias" as SAR functions was first seen on DB2v7-Fixpack2 or 3)