IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

[Solved]Internal Autonomous Stored Procedure - full of errors.


IBM Mainframe Forums -> DB2
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
Kakis Ziliaskoudis

New User


Joined: 15 Jan 2021
Posts: 3
Location: Sweden

PostPosted: Wed Sep 29, 2021 3:12 pm
Reply with quote

Hi!
I'm trying to create an internal autonomous stored procedure for logging my application progress. ^_^

At my job we have always used External Cobol SP, but I want to try something new.

There are always different errors and I'm currently struggling with -628 "The clauses are mutually exclusive" - even when I comment out almost all code. Why?

I'm also having problems with VARCHAR as parameters. I read in the docs that varchar can only be used with LANGUAGE C, but I really want SQL and varchar, since its the target columns datatype. What is good/best practice to handle this?

Can you spot more errors?

I'm on a Db2 on z/os, Function Level V12R1M501
Translations:
Nyckelord - Keyword
Fritext - Free text
Longfritext - "Long as in many characters"-free text

Thanks in advance
// Kakis
PS. I have 25 yrs of MS SQL Server Development, and just started with Mainframe/Db2/Cobol. Maybe it shows in my code?


Code:
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
--                                                                      00010000
CREATE PROCEDURE SYSPROC.LOGGA                                          00020012
(                                                                       00030000
    IN NYCKELORD CHAR(10), -- VARCHAR(10),                              00040021
    IN FRITEXT CHAR(30), -- VARCHAR(30),                                00050021
    IN LONGFRITEXT CHAR(50) --VARCHAR(5000),                            00060022
)                                                                       00070000
LANGUAGE SQL                                                            00080000
AUTONOMOUS                                                              00090000
WLM ENVIRONMENT DSAACCEPT                                               00110017
BEGIN                                                                   00120000
    DECLARE LV_PROGLOGID INTEGER;                                       00130010
    WITH LV_PROGLOGID(ID) AS                                            00131018
    (                                                                   00132018
      SELECT PROGRESSLOGID AS ID                                        00140018
      FROM FINAL TABLE                                                  00160018
      (                                                                 00170018
        INSERT INTO B40.PROGRESSLOG (NYCKELORD, FRITEXT)                00180018
        VALUES (NYCKELORD, FRITEXT)                                     00190021
      )                                                                 00200018
    );                                                                  00201018
    IF LONGFRITEXT IS NOT NULL THEN                                     00220018
      INSERT INTO B40.EXTRADATA (PROGRESSLOGID, LONGFRITEXT)            00230018
      VALUES (:LV_PROGLOGID, LONGFRITEXT);                              00240018
    END IF                                                              00250018
END#                                                                    00260001
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -628, ERROR:  THE CLAUSES ARE MUTUALLY EXCLUSIVE             
DSNT418I SQLSTATE   = 42613 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHSMS1 SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 5 0  0  -1  433  2905 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'00000005'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'000001B1'  X'00000B59' SQL DIAGNOSTIC INFORMATION                   
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                       
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                       
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72                 
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1                               
DSNE621I NUMBER OF INPUT RECORDS READ IS 28                                     
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 44                                 
******************************** Bottom of Data ********************************
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1210

PostPosted: Wed Sep 29, 2021 6:31 pm
Reply with quote

Two things looks as extremely strange in this SQL part.

1. WITH cannot be used without corresponding main clause SELECT. For myself, I always consider any WITH as a sort of the "local VIEW definition", which is defined for the SELECT main clause only.

2. The SELECT clause (defined here as the part of WITH) cannot include its inner INSERT clause. Where did you find these syntax rules?

I recommend, before putting everything into the stored procedure, - just run multiple tests on your complex SQL code (even using simple SPUFI). After you find that your specific SQL inventions really work as expected, you can start incorporating it into your stored procedure.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2792
Location: NYC,USA

PostPosted: Fri Oct 01, 2021 12:16 am
Reply with quote

You are creating temp table as LV_PROGLOGID and ID being the col. You cannot have INSERT within WITH. If you want to have ID to be Inserted then that INSERT should be outside of WITH.
Back to top
View user's profile Send private message
Kakis Ziliaskoudis

New User


Joined: 15 Jan 2021
Posts: 3
Location: Sweden

PostPosted: Fri Oct 01, 2021 2:29 pm
Reply with quote

I agree on the strangeness. I have now stripped my code down to

Code:
CREATE PROCEDURE LOGGA                       
(                                           
    IN NYCKELORD CHAR(10), -- VARCHAR(10),   
    IN FRITEXT CHAR(30), -- VARCHAR(30),     
    IN LONGFRITEXT CHAR(50) --VARCHAR(5000),
)                                         
WLM ENVIRONMENT DSAACCEPT
BEGIN
END
#


But I still get
Code:
SQLCODE = -628, ERROR:  THE CLAUSES ARE MUTUALLY EXCLUSIVE
SQLSTATE   = 42613 SQLSTATE RETURN CODE                   
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2792
Location: NYC,USA

PostPosted: Fri Oct 01, 2021 5:01 pm
Reply with quote

If you have existing one just refer else start from here pls.
www.ibm.com/docs/en/db2-for-zos/12?topic=statements-create-procedure-sql-native
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1210

PostPosted: Fri Oct 01, 2021 8:01 pm
Reply with quote

Kakis Ziliaskoudis wrote:
I agree on the strangeness. I have now stripped my code down to

Code:
CREATE PROCEDURE LOGGA                       
(                                           
    IN NYCKELORD CHAR(10), -- VARCHAR(10),   
    IN FRITEXT CHAR(30), -- VARCHAR(30),     
    IN LONGFRITEXT CHAR(50) --VARCHAR(5000),
)                                         
WLM ENVIRONMENT DSAACCEPT
BEGIN
END
#


But I still get
Code:
SQLCODE = -628, ERROR:  THE CLAUSES ARE MUTUALLY EXCLUSIVE
SQLSTATE   = 42613 SQLSTATE RETURN CODE                   

Your problem is not in the stored procedure, but in misunderstanding of SQL concepts.

I repeat again:
Run the SQL code itself out of the stored procedure (simplified to run autonomously), and using any simple tool like SPUFI.
Only when your SQL is running successfully by itself, you can start incorporating it into your stored procedure, or whatever else.
Back to top
View user's profile Send private message
Kakis Ziliaskoudis

New User


Joined: 15 Jan 2021
Posts: 3
Location: Sweden

PostPosted: Thu Oct 14, 2021 2:42 pm
Reply with quote

First I want to thank you for your replies. They have been helpful.

Problem one: Between the declaration of SP and the body I had to specify
Code:
LANGUAGE SQL --- IMPORTANT                                       
DETERMINISTIC -- -- NEED TO BE SPECIFIED 
AUTONOMOUS -- IMPORTANT                           
WLM ENVIRONMENT FOR DEBUG MODE DSAACCEPT -- NEED TO BE SPECIFIED 


You asked about where the "WITH" came from. Some references:
stackoverflow.com/questions/25221966/db2-storing-results-from-final-table-clause
stackoverflow.com/questions/51619437/what-is-the-final-table-equivalent-for-merge
They have now been removed.

This is what I have in my body so far:
Code:
DECLARE LV_PROGLOGID INTEGER;                     

SELECT MAX(ID) INTO LV_PROGLOGID               
FROM FINAL TABLE                                   
(                                                 
  INSERT INTO B40.PROGRESSLOG (NYCKELORD, FRITEXT)
  VALUES (NYCKELORD, FRITEXT)                     
);                                               
IF LONGFRITEXT IS NOT NULL THEN                             
  INSERT INTO B40.EXTRADATA (PROGRESSLOGID, LONGFRITEXT)     
  VALUES (LV_PROGLOGID, LONGFRITEXT);                       
END IF;                                                     

Now I'm dealing with PRIVELIGE problems. But thats something for my DBA.

Again! Thank you all for your help!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Getting error S000 U0016 (Cn Internal... JCL & VSAM 4
No new posts "Output" internal table (OD... COBOL Programming 0
No new posts Stored procedure cpu utilization DB2 1
No new posts In JCL Procedure how we can execute R... CLIST & REXX 7
No new posts Trying to submit COBOL PGM - JCL ERRO... JCL & VSAM 5
Search our Forums:

Back to Top