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 

Stored Procedure Built in Data Studio

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


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 30, 2015 10:46 am    Post subject: Stored Procedure Built in Data Studio Reply with quote

I built a SQL Native procedure in Data Studio and it's returning an extra variable I didn't create. I've searched the internet and IBM documentation and I can't find anything about it. Question

My Create
Code:

CREATE PROCEDURE FDBHRA.UPDATE_HRA_FOR_REISSUE
 (               IN  V_FAMILY_ID    INTEGER,
                 IN  V_CHECK_NBR    INTEGER,
                 IN  V_TRANS_TYPE    INTEGER,                 
                 OUT V_RESULT       VARCHAR(158)
                   )
 
   LANGUAGE SQL
    WITH EXPLAIN
     VALIDATE BIND -- expects all references to be valid
    DEFER PREPARE --    
     ALLOW DEBUG MODE
   WLM ENVIRONMENT FOR DEBUG MODE WLMDENV4
    RELEASE AT DEALLOCATE
     ISOLATION LEVEL UR



The output from the call 'RETURN_PARM' is the extra variable
Code:

Name         Type   Data type Value  Value (OUT)
 ------------ ------ --------- ------ ----------------------------------------------------------------------
 RETURN_PARM  OUTPUT INTEGER   0     
 V_FAMILY_ID  INPUT  INTEGER       123456
 V_CHECK_NBR  INPUT  INTEGER     300961
 V_TRANS_TYPE INPUT  INTEGER     9     
 V_RESULT     OUTPUT VARCHAR          SQL ERROR, SQLCODE= 100 SQLSTATE= 02000 UPDATE FDBHRA.HRA_TRANSACTIONS

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Sep 30, 2015 11:17 am    Post subject: Reply with quote

NASCAR9,

Just a minor difference in the table names.

CREATE PROCEDURE FDBHRA.UPDATE_HRA_FOR_REISSUE

vs

SQL ERROR, SQLCODE= 100 SQLSTATE= 02000 UPDATE FDBHRA.HRA_TRANSACTIONS

was that deliberate?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 30, 2015 12:08 pm    Post subject: Reply with quote

Kolusu,

The procedure updates two tables, I'm setting a variable in the program where the procedure encounters an error (Table1 or Table2). I'm creating an error on purpose testing the Out variable V_RESULT. It only gets populated when the procedure encounters an SQL error.
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Sep 30, 2015 1:44 pm    Post subject: Reply with quote

NASCAR9,

I am guessing that the default OUT variable is Return_Parm. So can you create the procedure as follows? I simply replaced the V_Result variable as Return_parm

Code:

CREATE PROCEDURE FDBHRA.UPDATE_HRA_FOR_REISSUE
 ( IN  V_FAMILY_ID    INTEGER,
   IN  V_CHECK_NBR    INTEGER,
   IN  V_TRANS_TYPE    INTEGER,                 
  OUT  RETURN_PARM VARCHAR(158))

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 30, 2015 2:25 pm    Post subject: Reply with quote

I tried. Now it comes out twice.
Code:

Name         Type   Data type Value  Value (OUT)
 ------------ ------ --------- ------ ----------------------------------------------------------------------
 RETURN_PARM  OUTPUT INTEGER   0     
 V_FAMILY_ID  INPUT  INTEGER   123456
 V_CHECK_NBR  INPUT  INTEGER   300961
 V_TRANS_TYPE INPUT  INTEGER   9     
 RETURN_PARM  OUTPUT VARCHAR          SQL ERROR, SQLCODE= 100 SQLSTATE= 02000 UPDATE FDBHRA.HRA_TRANSACTIONS


The complete procedure:
Code:

CREATE PROCEDURE FDBHRA.UPDATE_HRA_FOR_REISSUE
 (               IN  V_FAMILY_ID    INTEGER,
                 IN  V_CHECK_NBR    INTEGER,
                 IN  V_TRANS_TYPE    INTEGER,                 
                 OUT RETURN_PARM    VARCHAR(158)
                   )
 
   LANGUAGE SQL
    WITH EXPLAIN
     VALIDATE BIND -- expects all references to be valid
    DEFER PREPARE --    
     ALLOW DEBUG MODE
   WLM ENVIRONMENT FOR DEBUG MODE WLMDENV4
   STAY RESIDENT YES
    RELEASE AT DEALLOCATE
     ISOLATION LEVEL UR

   
P1: BEGIN
   -- ####################################################################
   -- #  RESET HRA PAYMENTS To REPROCESS
   -- ####################################################################
   
   DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
   DECLARE SQLCODE INTEGER DEFAULT  0;
   DECLARE WHERE_I_AM  VARCHAR (30);           
      
   
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND
      SET RETURN_PARM = 'SQL ERROR, SQLCODE= ' ||
                     TRIM(CHAR(SQLCODE))   ||
                     ' SQLSTATE= '         ||
                      SQLSTATE           ||
                          ' '                 ||
                      WHERE_I_AM   ;   
 
         
            SET WHERE_I_AM = 'UPDATE FDBHRA.HRA_TRANSACTIONS';
           
          UPDATE FDBHRA.HRA_TRANSACTIONS                     
           SET   COMMENTS  =                             
                  'Check Number ' || V_CHECK_NBR   ||                 
                  ' was Reissued Voided or Stale Dated New Check Date ' ||
                    CHAR(CURRENT DATE +                                   
                         CASE DAYOFWEEK(CURRENT DATE)                     
                           WHEN 1 THEN  3                               
                           WHEN 2 THEN  2                               
                           WHEN 3 THEN  1                               
                           WHEN 4 THEN  0                               
                           WHEN 5 THEN  6                               
                           WHEN 6 THEN  5                               
                           WHEN 7 THEN  4                               
                          END DAYS                                     
                         ,USA)                                             
                ,CHECK_NBR  = NULL                       
                ,USERID     = 'UPDATE_HRA_FOR_REISSUE'       
                ,LASTUPDT   = CURRENT TIMESTAMP         
            WHERE FAMILY_ID        = V_FAMILY_ID       
             AND TRANSACTION_TYPE = V_TRANS_TYPE                       
              AND CHECK_NBR        = V_CHECK_NBR    ;     
             
           SET WHERE_I_AM = 'UPDATE FDBHRA.PRESCRIPTION_WAIT ';
             
              UPDATE FDBHRA.PRESCRIPTION_WAIT             
                  SET TRANS_STATUS    = 200                   
                     ,CHECK_NBR       = 0                     
                     ,USERID          = 'UPDATE_PRES_WAIT_FOR_REISSUE'   
                     ,LASTUPDT        = CURRENT TIMESTAMP     
           WHERE FAMILY_ID        = V_FAMILY_ID     
              AND CHECK_NBR        = V_CHECK_NBR    ;          
   
END P1

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Sep 30, 2015 3:01 pm    Post subject: Reply with quote

NASCAR9,

One more quick test, Can you change the return_parm definition to CHAR(158) instead of varchar and check if you still got the additional column.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 30, 2015 3:07 pm    Post subject: Reply with quote

Kolusu,

I made the change, result was the same. I'm at a loss. I was even thinking maybe it's the way Data Studio builds the procedure. But it would seem to me IBM would document the extra variable.
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 30, 2015 3:18 pm    Post subject: Reply with quote

Also tried making it an INTEGER.
Code:

Name         Type   Data type Value  Value (OUT)
 ------------ ------ --------- ------ -----------
 RETURN_PARM  OUTPUT INTEGER   0     
 V_FAMILY_ID  INPUT  INTEGER   991469
 V_CHECK_NBR  INPUT  INTEGER   123456
 V_TRANS_TYPE INPUT  INTEGER   9     
 RETURN_PARM  OUTPUT INTEGER          99

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 30, 2015 3:55 pm    Post subject: Reply with quote

I wrote a quick COBOL program to call the procedure. I didn't define the extra variable and the call worked correctly. I'm thinking the RETURN_PARM OUTPUT INTEGER is a by product of Data Studio. I wish I could prove it.
Kolusu, Do you have any IBM Data Studio experts you could get an answer from?
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Sep 30, 2015 6:17 pm    Post subject: Reply with quote

NASCAR9 wrote:
Kolusu, Do you have any IBM Data Studio experts you could get an answer from?


Unfortunately NO. sorry. It is really a herculean task to get hold of the right person in the big blue land.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
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