How to create and deploy DB2 stored procedure

Hi,

I'm new to DB2.  I'm trying to create the following stored procedure for my DB2 database:

CREATE PROCEDURE audit_event_remove_sp (IN v_cutofftime TIMESTAMP)
    RESULT SETS 1
    SPECIFIC AUEVREM
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
-- 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                      


I'm creating this from the IBM DB2 Stored Procedure Builder.  When I go to build this stored procedure (from the UI), the following exception is generated:


DB2ADMIN.AUDIT_EVENT_REMOVE_SP - Create stored procedure returns -7032.

DB2ADMIN.AUDIT_EVENT_REMOVE_SP:

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

LINE    MESSAGES FOR P0903590.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\P0903590.c ***

@echo off
REM set VCV6_DRIVE=c:\Microsoft Visual Studio
REM set include=%include%;%VCV6_DRIVE%\VC98\atl\include;%VCV6_DRIVE%\VC98\mfc\include;%VCV6_DRIVE%\VC98\include
REM set lib=%lib%;%VCV6_DRIVE%\VC98\mfc\lib;%VCV6_DRIVE%\VC98\lib
REM 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

@echo on
set SQLROUTINE_FILENAME=P0903590
set db2path=D:\SQLLIB

nmake /f D:\SQLLIB\function\routine\sqlproc.mak

*** D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp\P0903590.def ***
LIBRARY P0903590
EXPORTS _pgsjmp@12
pgsjmp=_pgsjmp@12

D:\SQLLIB\function\routine\sqlproc\HH223\DB2ADMIN\tmp>set SQLROUTINE_FILENAME=P0903590
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.

My questions regarding these issues:
1) How do I successfully build a stored procedure on DB2?
2) After building the stored procedure, can I use the "GET ROUTINE" command to extract the stored procedure into a *.sar file?  
3) What's the easiest way to create a *.sar file from a stored procedure?

I intend to use the "PUT ROUTINE" command to deploy the stored procedure to other systems.

Thanks much.

Start Free Trial