MVSFORUMS.com

shekar123

Hi all,

I am new to SQL procedures creation and i am trying create a SQL stored procedure in SPUFI but i i get SQL code -104.My code:
Can anyone tell where i am wrong ?

Code:

DB2 -TD@ -VF CREATESQLPROC.DB2
CREATE PROCEDURE SALES_STATUS
(
IN QUOTA INTEGER,OUT SQL_STATE CHAR(5)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE RS CURSOR WITH RETURN FOR
SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
FROM SALES
GROUP BY SALES_PERSON
HAVING SUM(SALES) > QUOTA;
OPEN RS;
SET SQL_STATTE = SQLSTATE;
END @

MESSAGE

Code:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "-". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR

_________________
Shekar
Grow Technically

kolusu · Posted: Fri Nov 11, 2005 8:10 am Post subject:

shekar123,

A sql code of -104 is usually associated with syntax error. Make sure that you dont' have any garbage values after col 72 in your query. Also hypen(-) is not valid in DB2 , you need to use underscore(_).

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

shekar123

Kolusu,

I checked for any garbage values after column 72 but none were found.So i tried using underscore(_) in the syntax:

Code:

DB2 _TD@ _VF CREATESQLPROC.DB2
CREATE PROCEDURE SALES_STATUS
(
IN QUOTA INTEGER,OUT SQL_STATE CHAR(5)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE RS CURSOR WITH RETURN FOR
SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
FROM SALES
GROUP BY SALES_PERSON
HAVING SUM(SALES) > QUOTA;
OPEN RS;
SET SQL_STATTE = SQLSTATE;
END @

I still get the same error -104(syntax error) and how do i solve this problem ?

Code:

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "_". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR

_________________
Shekar
Grow Technically

kolusu · Posted: Fri Nov 11, 2005 2:42 pm Post subject:

shekar123,

what are you trying to do? It does not look like you are defining a stored procedure ! what does the first line do?

And you have a typo for SQL_STATTE . you have an additional "T".

Look at the syntax for create procedure in "sql reference" manual . You can access it via quick manuals link !

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

shekar123 · Posted: Fri Nov 11, 2005 3:58 pm Post subject:

Kolusu,

Thanks for your instructions.I am creating sql stored procedure using this syntax below but i am getting an SQL of -20071 .

Code:

CREATE PROCEDURE SALESSTATUS
(
QUOTA INTEGER
)
LANGUAGE SQL
SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
FROM SALES
GROUP BY SALES_PERSON
HAVING SUM(SALES) > QUOTA;

OUTPUT

Code:

DSNT408I SQLCODE = -20071, ERROR: WLM ENVIRONMENT NAME MUST BE SPECIFIED
SKILL.SALESSTATUS
DSNT418I SQLSTATE = 53099 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIVRO SQL PROCEDURE DETECTING ERROR

I have the information that SQL error code -20071 means that

Code:

-20071 WLM ENVIRONMENT NAME MUST BE SPECIFIED function-name
Explanation: The WLM ENVIRONMENT option was
not specified on CREATE FUNCTION, and there is no
default WLM environment for the installation.
System Action: The statement could not be
processed.
Programmer Response: Select a WLM
ENVIRONMENT name and include it in the CREATE
FUNCTION statement. Contact your system
administrator to find out the names of the WLM
environments that have been defined for your
installation.
SQLSTATE: 53099

How do i know the value of WLM environment defined / defaults in my shop ? If i am not able to get the info ,is there any way to proceed ahead ?
_________________
Shekar
Grow Technically

shekar123

Sorry i missed out the option IN (QUOTA INTEGER ) in the syntax in my previous post but still i get the same SQL code of -20071 .

Code:

CREATE PROCEDURE SALESSTATUS
(
IN QUOTA INTEGER
)
LANGUAGE SQL
SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
FROM SALES
GROUP BY SALES_PERSON
HAVING SUM(SALES) > QUOTA;

_________________
Shekar
Grow Technically

kolusu · Posted: Fri Nov 11, 2005 5:26 pm Post subject:

Manas Biswal · Posted: Mon Nov 14, 2005 10:18 am Post subject:

shekar123,

In a create procedure statement, if you do not specify the WLM ENVIRONMENT parm, DB2 assumes by default that you are going to use a WLM environment and searches for the default WLM defined to your installation - a DSNZPARM parameter. It seems, in your DB2 installation, there is no default WLM environment defined in zparm. Hence, you have got two options -

1. Ask your DBA about the WLM environment that you have authorization to use and can use for your purpose.

2. Use a DB2 SPAS (Stored procedure address space) - an address space managed by DB2 for running your stored procedure. For that, use the "NO WLM ENVIRONMENT" clause explicitly in your create procedure command.
Pls note that there are various restrictions on the create procedure (on language, security etc.) if you use the "NO WLM ENVIRONMENT" clause.

HTH....Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

shekar123

Thanks Manas,

The information provided by was really helpful for me.I have tried using 'NO WLM ENVIRONMENT' clause but still i encounter SQL code -104 syntax error.Can u please help me out where i am going wrong.

Code:

CREATE PROCEDURE SALESSTAT
( IN QUOTA INTEGER , OUT RETCODE CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NO WLM ENVIRONMENT
BEGIN
DECLARE RETCODE CHAR(5);
DECLARE SALES_RESULTS CURSOR WITH RETURN FOR
SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
FROM SALES GROUP BY SALES_PERSON
HAVING SUM(SALES) > QUOTA;
OPEN SALES_RESULTS;
SET RETCODE = SQLSTATE;
END

OUTPUT

Code:

CREATE PROCEDURE SALESSTAT
( IN QUOTA INTEGER , OUT RETCODE CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NO WLM ENVIRONMENT
BEGIN
DECLARE RETCODE CHAR(5);
---------+---------+---------+---------+---------+---------+---------+---------+
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

_________________
Shekar
Grow Technically

Ravi · Beginner Joined: 27 Jun 2005 Posts: 88 Topics: 2

I never knew that we can create stored procedures(SP) thru SPUFI. If yes then why is COBOL390 required to create a SP. Whynot some COBOL VS II could be used to do the create an SP? IBM Has something to setup to create a SP thru the Hold Language.

Definetly COBOL390 gives a lot of additional features to manipulate and add regular logic to give the desired result.

Even if we can create a SP thru SPUFI what would be the purpose acheived? You can do the same with a VIEW itself.

Help me if am missing something...

I cannot test to Crying or Very sad

create an SP thru SPUFI. My shop no-longer has DB2

kolusu · Posted: Mon Nov 14, 2005 2:05 pm Post subject:

Manas Biswal · Posted: Mon Nov 14, 2005 3:44 pm Post subject:

shekar123,

The default SQL terminator used in SPUFI is ";". And you are using ";" as the terminator inside your CREATE PROCEDURE statement to terminate individual SQLs. This is creating a conflict. If you intend to use the ";" terminator inside your CREATE PROCEDURE statement, then use a different SQL terminator for your SPUFI session.
When you enter SPUFI, on the 2ND screen (CURRENT SPUFI DEFAULTS), there is a option where you can specify the SQL terminator for your session. Change the terminator to something else other than ";". "#" for example. And then code your CREATE PROCEDURE something like the following -

shekar123 · Posted: Tue Nov 15, 2005 1:34 pm Post subject: unable to create stored procedure

Thanks Manas,

Your information was really useful and i had to change the SPUFI defaults to '#" to execute the stored procedure code in SPUFI and it got created succesfully.But i when i try to execute the stored procedure by using CALL statement i get SQL code -084.Can we not invoke a stored procedure in SPUFI ,please guide me further ?

Code:

-- CREATE PROCEDURE SALESSTAT
-- ( IN QUOTA INTEGER , OUT RETCODE CHAR(5))
-- DYNAMIC RESULT SETS 1
-- LANGUAGE SQL
-- NO WLM ENVIRONMENT
-- BEGIN
-- DECLARE RETCODE CHAR(5);
-- DECLARE SALES_RESULTS CURSOR WITH RETURN FOR
-- SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
-- FROM SALES GROUP BY SALES_PERSON
-- HAVING SUM(SALES) > QUOTA;
-- OPEN SALES_RESULTS;
-- SET RETCODE = SQLSTATE;
-- END #
-- COMMIT #
CALL SALESSTAT(3,?)#

Tried using spaces in CALL statement too.

Code:

-- CREATE PROCEDURE SALESSTAT
-- ( IN QUOTA INTEGER , OUT RETCODE CHAR(5))
-- DYNAMIC RESULT SETS 1
-- LANGUAGE SQL
-- NO WLM ENVIRONMENT
-- BEGIN
-- DECLARE RETCODE CHAR(5);
-- DECLARE SALES_RESULTS CURSOR WITH RETURN FOR
-- SELECT SALES_PERSON,SUM(SALES) AS TOTAL_SALES
-- FROM SALES GROUP BY SALES_PERSON
-- HAVING SUM(SALES) > QUOTA;
-- OPEN SALES_RESULTS;
-- SET RETCODE = SQLSTATE;
-- END #
-- COMMIT #
CALL SALESSTAT (3,?) #

MESSAGE

Code:

CALL SALESSTAT(3,?)#
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -084, ERROR: UNACCEPTABLE SQL STATEMENT
DSNT418I SQLSTATE = 42612 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHAPLY SQL PROCEDURE DETECTING ERROR

Code:

CALL SALESSTAT (3,?) #
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -084, ERROR: UNACCEPTABLE SQL STATEMENT
DSNT418I SQLSTATE = 42612 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHAPLY SQL PROCEDURE DETECTING ERROR

_________________
Shekar
Grow Technically

Cogito-Ergo-Sum · Posted: Wed Nov 16, 2005 3:27 am Post subject:

Flip the default delimiter back to ; ?
_________________
ALL opinions are welcome.

Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.

Manas Biswal · Posted: Wed Nov 16, 2005 9:57 am Post subject:

shekar123,