troubleshooting Question

How to create and deploy DB2 stored procedure

Avatar of hepstein123
hepstein123 asked on
DB2
5 Comments1 Solution62729 ViewsLast Modified:
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.

ASKER CERTIFIED SOLUTION
ghp7000

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros