MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

DB2 SQL Procedures on z/os

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA
PostPosted: Mon Mar 23, 2009 12:53 pm    Post subject: DB2 SQL Procedures on z/os Reply with quote
Hi Friends,
We are developing a web application which access data through Db2 on z/os. We need to create SQL procedures and when I create the following sql I got -628 sql code on it. Can we have SQL procedures created in DB2 catalogs and can these be called through by CALL statement in Java. We are in version 8 DB2. Please advise.
Code:

CREATE PROCEDURE UPDATE_DRUGS         
   (IN  DMUMNEM               CHAR(8)
   ,IN  DAYSNO                  INTEGER
   )                                 
   DYNAMIC RESULT SETS 0             
   LANGUAGE SQL                       
   PARAMETER STYLE GENERAL           
   MODIFIES SQL DATA                 
   WLM ENVIRONMENT TDB2SID1           
   STAY RESIDENT YES                 
   COMMIT ON RETURN YES               
   COLLID ICPC                       
   BEGIN UPDATE IDVPCS01.ITORDSET     
       SET DURATION = DURATION * DAYSNO
   WHERE MNEMONIC = DMUMNEM;           
END
Back to top
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
PostPosted: Mon Mar 23, 2009 1:21 pm    Post subject: Reply with quote
could check here:

DB2 for z/OS Stored Procedures: Through the CALL and Beyond, SG24-7083-00
Redbooks, published 29 March 2004, last updated 22 February 2006

http://www.redbooks.ibm.com/cgi-bin/searchsite.cgi?query=SQL+AND+PROCEDURES
_________________
Dick Brenholtz
American living in Varel, Germany
Back to top
View user's profile Send private message
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA
PostPosted: Tue Mar 24, 2009 8:08 am    Post subject: Reply with quote
Thank you for the link. I have coded my procedure as follows. Still I get an error during creation. Could some one help me please?
Code:

CREATE PROCEDURE DRUGLIST           
   ( IN  PHOSPNO       CHAR(4)       
    ,OUT PMNEMONIC     CHAR(8)       
    ,OUT PDRUGSTAT     CHAR(1)       
    ,OUT PDRUGNM       CHAR(76)     
    ,OUT PDURATION     CHAR(2)       
    ,OUT PFREQ         CHAR(10)     
    ,OUT PDOSE         DECIMAL(5, 2)
    ,OUT PSQLCODE      INTEGER       
    ,OUT PSQLSTATE     CHAR(5)       
    ,OUT PSQLERRMC     VARCHAR(250) 
   )                                 
   RESULT SETS 0                     
   MODIFIES SQL DATA                 
   NO DBINFO                         
   WLM ENVIRONMENT TDB2SID1             
   STAY RESIDENT NO                     
   COLLID ICVPC                         
   PROGRAM TYPE MAIN                   
   RUN OPTIONS 'TRAP(OFF),RPTOPTS(OFF)'
   COMMIT ON RETURN NO                 
   LANGUAGE SQL                         
   BEGIN                               
   DECLARE SQLCODE INTEGER;             
   DECLARE SQLSTATE CHAR(5);           
   SELECT                               
       MNEMONIC                         
      ,DRUGSTAT                         
      ,DRUGNM                           
      ,DURATION                         
      ,FREQ                             
       ,DOSE                 
    INTO  PMNEMONIC           
       ,PDRUGSTAT             
       ,PDRUGNM               
       ,PDURATION             
       ,PFREQ                 
       ,PDOSE                 
    FROM IDVPCS01.ITORDSET   
    WHERE HOSPNO = PHOSPNO   
;                             
    SET PSQLCODE   = SQLCODE ;
    SET PSQLSTATE  = SQLSTATE;
    SET PSQLERRMC  = 'ADIOS';
END

I got the following error. I tried to remove the semi colons after Declare. But it didn't work.[/code]
BEGIN
DECLARE SQLCODE INTEGER;
---------+---------+---------+---------+---------+---------+---------+---------+
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 1749 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'000006D5' X'00000000' SQL DIAGNOSTIC INFORMATION[/code]
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group