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_REMOV
E_SP - Create stored procedure returns -7032.
DB2ADMIN.AUDIT_EVENT_REMOV
E_SP:
*** BIND D:\SQLLIB\function\routine
\sqlproc\H
H223\DB2AD
MIN\tmp\P0
903590.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\H
H223\DB2AD
MIN\tmp\P0
903590.c ***
@echo off
REM set VCV6_DRIVE=c:\Microsoft Visual Studio
REM set include=%include%;%VCV6_DR
IVE%\VC98\
atl\includ
e;%VCV6_DR
IVE%\VC98\
mfc\includ
e;%VCV6_DR
IVE%\VC98\
include
REM set lib=%lib%;%VCV6_DRIVE%\VC9
8\mfc\lib;
%VCV6_DRIV
E%\VC98\li
b
REM set path=%path%;%VCV6_DRIVE%\C
ommon\Tool
s\WinNT;%V
CV6_DRIVE%
\Common\MS
Dev98\Bin;
%VCV6_DRIV
E%\Common\
Tools;%VCV
6_DRIVE%\V
C98\bin;%V
CV6_DRIVE%
\VC98\mfc\
lib;%VCV6_
DRIVE%\VC9
8\lib
@echo off
REM set VCV5_DRIVE=c:\Program Files\devstudio
REM set include=%include%;%VCV5_DR
IVE%\vc\in
clude;%VCV
5_DRIVE%\v
c\atl\incl
ude;%VCV5_
DRIVE%\vc\
mfc\includ
e
REM set lib=%lib%;%VCV5_DRIVE%\vc\
lib;%VCV5_
DRIVE%\vc\
mfc\lib
REM set path=%path%;%VCV5_DRIVE%\s
haredide\b
in\ide;%VC
V5_DRIVE%\
sharedide\
bin;%VCV5_
DRIVE%\vc\
bin
@echo on
set SQLROUTINE_FILENAME=P09035
90
set db2path=D:\SQLLIB
nmake /f D:\SQLLIB\function\routine
\sqlproc.m
ak
*** D:\SQLLIB\function\routine
\sqlproc\H
H223\DB2AD
MIN\tmp\P0
903590.def
***
LIBRARY P0903590
EXPORTS _pgsjmp@12
pgsjmp=_pgsjmp@12
D:\SQLLIB\function\routine
\sqlproc\H
H223\DB2AD
MIN\tmp>se
t SQLROUTINE_FILENAME=P09035
90
D:\SQLLIB\function\routine
\sqlproc\H
H223\DB2AD
MIN\tmp>se
t db2path=D:\SQLLIB
D:\SQLLIB\function\routine
\sqlproc\H
H223\DB2AD
MIN\tmp>nm
ake /f D:\SQLLIB\function\routine
\sqlproc.m
ak
'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_REMOV
E_SP - Changes rolled back.
DB2ADMIN.AUDIT_EVENT_REMOV
E_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.