New to Stored Procedures

Robert Glover

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

Philip Sevetson

Re: New to Stored Procedures
(in response to Robert Glover)
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

Sudhir Raj

Re: New to Stored Procedures
(in response to Philip Sevetson)
Change the default SQL TERMINATOR (on the DEFAULTS panel for SPUFI) to a character other than semicolon (like # )
Sudhir Muthuraj
RealTime-DBA
www.realtimedba.com




________________________________
From: Robert Glover <[login to unmask email]>
To: [login to unmask email]
Sent: Mon, January 25, 2010 1:52:55 PM
Subject: 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 = DSNHPARSSQL 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

Jeffrey Schade

Re: New to Stored Procedures
(in response to Sudhir Raj)
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

Philip Sevetson

Re: New to Stored Procedures
(in response to Jeffrey Schade)
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