AW: DB2 Stored Procedures

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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
>