RE: AW: DB2 Stored Procedures

Calls to QCMDEXC do have additional considerations starting around V5R4.

The discussion of SQL constants found at
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzms
tch2cons.htm
might
help up some of the general confusion. The existence of a decimal point
is a
rather large switch between integer and packed decimal. And the
precision of
packed decimal values is determined by the number of leading (and
trailing)
zeroes in the provided constant.

Note that this reference is discussing default assumptions made by SQL.
Calls to QCMDEXC do have additional considerations starting around V5R4.
Bruce

On Sun, Nov 7, 2010 at 11:15 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx>
wrote:

Dennis

Good point - must have been asleep. 15,5, as others pointed out, is

the

default, so it just works.

So this got me thinking - because I've told this technique of filling
out all the numeric positions before - so I tried calling a program

with

a packed 9,0 - passed 000000001 - it failed, since what it actually
passed was x'000000000100000F' into the 9,0, which is only 5 bytes

long,

hence, data-decimal error. [call vern/testimg01
('AS400DOC-000000000002412', 1)]

This helps - I now can go back to what I always believed - about the
default length, and that we're not tricking it when using QCMDEXC.

Now I did call qcmdexc in SQL at v5r1 and got an error - here's the

CALL -

call qcmdexc ('wrksyssts', 9)

This worked, as Birgitta said -

call qcmdexc ('wrksyssts', 0000000009.00000)

So in SQL, there's been some kind of translation or conversion going

on,

it seems. Not CL, but SQL - interesting. How to try my test with the
packed 9,0 -

This failed with something about attributes of parameter 2 being

invalid -

call vern/testimg01 ('AS400DOC-000000000002412', 1)

So I tried this

call vern/testimg01 ('AS400DOC-000000000002412', 000000001)

and got the same error - hmm, it's 9,0 and I put 8 zeroes and the 1

in

there. So I tried adding a decimal point at the end, as here -

call vern/testimg01 ('AS400DOC-000000000002412', 000000001.)

And that worked!

I don't know how long SQL has done this, and apparently, according to
Birgitta, a more recent change has taken place.

Verrrrry interesting. Learn new things all the time! Love it!

Vern

On 11/7/2010 3:47 PM, Dennis Lovelady wrote:

Since CPF 1.0 (System/38), the default format of numeric values

passed

from

CL has been PKD(15,9). This is WHY QCMDEXC (and QCAEXEC before it)

expects

a parameter of those attributes - it?s not the other way around.

So

"quite

some time" in this case means 32 years or so. No V5R4 or i6.1

change

involved ... unless this is in reference to some SQL Stored

Procedure

change; I couldn't tell.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The radical of one century is the conservative of the next. The

radical

invents the views. When he has worn them out the conservative

adopts

them."

-- Mark Twain

I keep forgetting the method of using all digits - that's been

part of

CL for quite some time, I believe. I just tried it at v5r1 - so

it's a

CL thing, not SQL.

The memory leaks often for me!

Vern

On 11/7/2010 2:20 AM, Birgitta Hauser wrote:

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.

--
Regards,
Bruce
www.brucevining.com
www.powercl.com
--
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.