Re: DB2 Stored Procedures

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

David

I hope I've not release a monster by mentioning the ability to call HLL 
programs without registering (using CREATE PROCEDURE) them. That is a 
peculiarity, a happy accident, perhaps, of SQL on the i - no other RDBMS 
does that, so far as I know. It does make it possible to bring current 
code into an SQL environment more easily. No other RDBMS has "external" 
SPs, only those completely written in SQL, as Birgitta mentioned early on.

But you are better off using CREATE PROCEDURE with its various 
parameters. There are return codes, there are other matters that pertain 
to SQL that we never dealt with in our programs before.

Best look up stored procedures - there's a Redbook on them, plus 
user-defined functions.

Registering just means using CREATE PROCEDURE to get the procedure 
(external or SQL) listed or "registered" in the system database tables, 
like SYSPROCS. There is no object created by CREATE PROCEDURE - only a 
row in a view.

HTH
Vern

On 11/8/2010 3:24 AM, David FOXWELL wrote:
> Hi Birgitta,
>
> What I still don't get is, if the HLL used by the stored procedure is RPG, and the caller can simply do CALL program inside the embedded sql, why do I need to do CREATE PROCEDURE?
>
> > -----Message d'origine-----
> > De : midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Birgitta Hauser
> > Envoyé : lundi 8 novembre 2010 09:50
> > À : 'Midrange Systems Technical Discussion'
> > Objet : AW: DB2 Stored Procedures
> >
> > Stored Procedures cannot handle optional parameters (in the
> > same way as RPG does).
> > But Stored Procedures can be overloaded, that means within
> > the same library/schema several stored procedures with the
> > same name, but a different number of parameters can coexist.
> > When calling a stored procedure the number or parameters will
> > be checked and the procedure with the appropriate number of
> > parameters will be called.
> >
> > Mit freundlichen Grüßen / Best regards
> >
> > Birgitta Hauser
> >
> > "Shoot for the moon, even if you miss, you'll land among the
> > stars." (Les
> > Brown)
> > "If you think education is expensive, try ignorance." (Derek
> > Bok) "What is worse than training your staff and losing them?
> > Not training them and keeping them!"
> >
> > -----Ursprüngliche Nachricht-----
> > Von: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von David FOXWELL
> > Gesendet: Monday, 08. November 2010 09:30
> > An: Midrange Systems Technical Discussion
> > Betreff: RE: DB2 Stored Procedures
> >
> > Birgitta, Vern,
> >
> > That was interesting, but I don't get the registration bit.
> > What do you mean by overloading and more secure?
> > And back to the OP, so there is no trade off?