View previous topic :: View next topic |
Author |
Message |
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 30, 2015 10:46 am Post subject: Stored Procedure Built in Data Studio |
|
|
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.
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Wed Sep 30, 2015 11:17 am Post subject: |
|
|
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 |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 30, 2015 12:08 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Wed Sep 30, 2015 1:44 pm Post subject: |
|
|
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 |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 30, 2015 2:25 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Wed Sep 30, 2015 3:01 pm Post subject: |
|
|
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 |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 30, 2015 3:07 pm Post subject: |
|
|
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 |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 30, 2015 3:18 pm Post subject: |
|
|
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 |
|
|
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 30, 2015 3:55 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Wed Sep 30, 2015 6:17 pm Post subject: |
|
|
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 |
|
|
|
|