You create a stored procedure in the DB2 catalog with CREATE PROCEDURE.

To execute a stored procedure you use CALL statement, like db2 "call schema.procname(p1, p2)"

To grant the privilege to CREATE a PROCEDURE, give the person BINDADD and either (a) IMPLICIT_SCHEMA privilege on the database to create a new schema or (b) CREATEIN ON SCHEMA x for an existing schema. Anyone with DBADM or SYSADM authority can also CREATE a PROCEDURE.

To grant the ability to invoke (i.e. CALL) a procedure GRANT EXECUTE ON PROCEDURE x.y TO USER z where y can be a wildcard for all procedures in that schema and z can be PUBLIC.

Best, Hal Steiner


-----Original Message-----
From: db2udbdba-ezmlmshield-x14946654.[Email address protected]
To: [Email address protected]
Sent: Wed, 13 Dec 2006 8:04 PM
Subject: stored procedure



Hi all,
I got a request; developer has a stored procedure and saved as an
sp.sql file; how can I run this procedure in db2udb?
What dba need to do to make this procedure can execute on clinet
machine ?
I have do: db2 "grant bindadd on database to user unica"; unica
is the shcema owner and
also the user use "unica" to connect to DB.

Thanks for help!!

Sue


-- populate UA_time table

begin atomic
declare timeid_v int;
declare hour_v int;
declare minute_v int;
set timeid_v = 0;
set hour_v = 0;
while hour_v < 24 do
set minute_v = 0;
while minute_v < 60 do
set timeid_v = hour_v*10000+minute_v*100;
insert into UA_time(TimeID, Hour, Minute) values
(timeid_v,hour_v,minute_v);
set minute_v = minute_v + 1;
end while;
set hour_v = hour_v + 1;
end while;
end
@


**********************************************************************
This e-mail transmission and any attachments that accompany it may contain
information that is privileged, confidential or otherwise exempt from
disclosure under applicable law and is intended solely for the use of the
individual(s) to whom it was intended to be addressed. If you have received this
e-mail by mistake, or you are not the intended recipient, any disclosure,
dissemination, distribution, copying or other use or retention of this
communication or its substance is prohibited. If you have received this
communication in error, please immediately reply to the author via e-mail that
you received this message by mistake and also permanently delete the original
and all copies of this e-mail and any attachments from your computer. Thank you.

**********************************************************************


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
Check out the new AOL. Most comprehensive set of free safety and security tools, free access to millions of high-quality videos from across the web, free AOL Mail and more.

DB2 & UDB email list listserv db2-l LazyDBA home page