RE: DB2 Stored Procedures

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

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de

Birgitta Hauser

Envoyé : dimanche 7 novembre 2010 09:21 À : 'Midrange Systems
Technical Discussion'
Objet : AW: DB2 Stored Procedures

Hi Vern,

In fact - and I just verified this - you can call any existing
program,

even without using the CREATE PROCEDURE statement to register it.
That's why I wrote "must or at least should be registered".
Because of overloading it is more secure to register existing
programs.

Concerning QCMDEXC there was an enhancement within release
V5R4 or 6.1 so you can call it as follows.

Call QCMDEXC('WRKSYSSTS', 9);

... and even before this enhancement it also could be

called without

hex notation, but the numeric value must have all 15 digits

including

the decimal point.

Call QCMDEXC('WRKSYSTST', 0000000009.00000);

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 Vern Hamberg
Gesendet: Saturday, 06. November 2010 14:31
An: Midrange Systems Technical Discussion
Betreff: Re: AW: DB2 Stored Procedures

Hi Birgitta

I'm glad you generalized the topic - I was not sure whether the
original post referred only to SQL stored procedures, or the entire
range of the kinds of SPs.

To respond to the OP, stored procedures are nothing like

SQL scripts,

which I take as the source members used by RUNSQLSTM. At least not
functionally. If the stored procedure is an SQL SP, then,

yes, there

will be many SQL statements.
But there are also external SPs - those created using HLL,

as Birgitta

stated.

So you can have a program you've already written that

doesn't return

any values, and you can use it as a stored procedure.
In fact - and I just verified this - you can call any existing
program, even without using the CREATE PROCEDURE statement

to register

it. I have a program, TEST in library VERN - in STRSQL I simply
entered CALL VERN/TEST and, ba-da-bing, it ran and got an error
because it couldn't find a command that is in the program.

I have actually called QCMDEXC in an SQL session - you have

to specify

the length using hex notation, but this works -

call qcmdexc ('wrksyssts',x'000000000900000F')

There's probably no good reason to do this, other than

curiosity. It

merely demonstrates possibilities - and why you should use packed
fields!!! ;-)

BTW, QCMDEXC is not listed in SYSPROCS.

Vern

On 11/6/2010 4:11 AM, Birgitta Hauser wrote:

Stored Procedures are nothing else than programs written in

either an

HLL (such as RPG, COBOL or even CL) or with pure SQL, that can be
called from any interface that supports (embedded) SQL.

For to use

programs (or Procedures without return value) written in

an HLL as

stored procedures, those programs must or at least should be
registered with the SQL Command CREATE PROCEDURE.

In this way RPG programs can be easily called from

languages such as

JAVA

or

PHP.

Stored Procedures written with pure SQL will be converted into
C-Programs with embedded SQL. (iSeries Navigator debugger

allows you

to either debug the SQL or C code).
SQL supports everything you can use with embedded SQL

plus several

control statements, such as WHILE, LOOP or REPEAT to loop

through the

data or IF

and

CASE for conditions etc.)

Using stored procedures within client server application

will reduce

the traffic between client and server, because in an stored

procedure

several actions/steps are bundled.
That means a single call versus a several actions.

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 Dan Rasch
Gesendet: Saturday, 06. November 2010 03:53
An: midrange-l@xxxxxxxxxxxx
Betreff: DB2 Stored Procedures



I am researching the used of DB2 Stored Procedures, and

from what I

have seen, the look very similar to SQL scripts.

What are the advantages / trade-offs?

How do they compare to internal SQL, or even API's for
retrieving/modifying records?

Thanks,
- Dan

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,

please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,

please take

a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.