Re: DB2 Stored Procedures

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?