*****SPAM***** Re: [DB2-L] New to Stored Procedures

James Campbell

*****SPAM***** Re: [DB2-L] New to Stored Procedures
One place is the Appendix in Application Programming and SQL Guide titled "Running
DSNTEP2 and DSNTEP4"

James Campbell

On 25 Jan 2010 at 15:23, Sevetson, Phil wrote:

> Be careful, all. I used Jeff's code but put a space between the two dashes and the hash ("-- #") -- almost decided it didn't work, but "--#" does the trick.
>
> Thanks for the tip, Jeff! Where'd you find it?
>
> --Phil
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Schade, Jeffrey
> Sent: Monday, January 25, 2010 3:07 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] New to Stored Procedures
>
> The commands to reset the end-of statement are:
>
>
> --#SET TERMINATOR #
> SP code goes here
> #
> --#SET TERMINATOR ;
>
> Jeff
< rest snipped>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Jeffrey Schade

Re: New to Stored Procedures
(in response to James Campbell)
We use it at my shop for trigger code and it has been awhile since I checked for it in the manuals but I found it documented in the Application Programming & SQL Guide.

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738
  FAX: (201) 748-1500  
[login to unmask email]


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, January 25, 2010 3:23 PM
To: [login to unmask email]
Subject: Re: [DB2-L] New to Stored Procedures

Be careful, all. I used Jeff's code but put a space between the two dashes and the hash ("-- #") -- almost decided it didn't work, but "--#" does the trick.

Thanks for the tip, Jeff! Where'd you find it?

--Phil

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Schade, Jeffrey
Sent: Monday, January 25, 2010 3:07 PM
To: [login to unmask email]
Subject: Re: [DB2-L] New to Stored Procedures

The commands to reset the end-of statement are:


--#SET TERMINATOR #
SP code goes here
#
--#SET TERMINATOR ;

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738
  FAX: (201) 748-1500  
[login to unmask email]

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Monday, January 25, 2010 2:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] New to Stored Procedures

Robert,

You need to change your SPUFI options/settings so that SPUFI doesn't recognize the semicolon as an end-of-statement, then put your new end of statement marker after the "END MAIN" expression.

Contact me offline if you need more detail and I'll send screen captures in .DOC format.

--Phil S.

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Robert Glover
Sent: Monday, January 25, 2010 1:53 PM
To: [login to unmask email]
Subject: [DB2-L] New to Stored Procedures

Hello,
I am writing my very 1st SP and need a little direction. I call the SP and
present it w/ (2) input fields and expect (1) output value returned. No Selects
are to be done. To make it really simple I coded one single IF. It follows:

CREATE PROCEDURE BGPROC
(IN PARAMETER_NAME CHARACTER(24)
,IN SETTING CHARACTER(39)
,OUT NEW_SETTING CHARACTER(39)
)
RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
EXTERNAL NAME 'BGPROC'
WLM ENVIRONMENT DPG1WLM1
ASUTIME NO LIMIT
------------------------------------------
MAIN: BEGIN
------------------------------------------
IF (SUBSTR(PARAMETER_NAME,1,7) = 'SMFACCT') THEN
SET NEW_SETTING = 'NO';
END IF;
END MAIN

What I get is the following:
---------+---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-
STATEMENT>". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: ;
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN
CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING
ERROR
DSNT416I SQLERRD = 502 0 0 -1 967 0 SQL DIAGNOSTIC
INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000'
X'FFFFFFFF'
X'000003C7' X'00000000' SQL DIAGNOSTIC INFORMATION

Thanks, in advance for the help.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L