Enter the command "db2 -[Email Address Removed] -vf your-sql-file" to execute the file(to
create proc) where '@' = delimeter...:)

Suersh G.




"hsteiner" <db2udbdba-ezmlmshield-x75874125.[Email address protected]
12/14/2006 09:31 AM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Re: stored procedure






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.


---------------------------------------------------------------------
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



CONFIDENTIALITY NOTICE:
This is a transmission from Kohl's Department Stores, Inc.
and may contain information which is confidential and proprietary.
If you are not the addressee, any disclosure, copying or distribution or use of the contents of this message is expressly prohibited.
If you have received this transmission in error, please destroy it and notify us immediately at 262-703-7000.

CAUTION:
Internet and e-mail communications are Kohl's property and Kohl's reserves the right to retrieve and read any message created, sent and received. Kohl's reserves the right to monitor messages to or from authorized Kohl's Associates at any time
without any further consent.


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