Support Article
Issues in Mapping DB2 Out Parameter of type integer
SA-2558
Summary
We are calling a stored procedure from PRPC and mapping all the out parameters in the clipboard. One of the parameter defined as Integer is not getting mapped in the clipboard.
The stored procedure is hosted DB2 mainframe db.
DB Details:
1. DB2 - Version 10
2. Driver Installed in App Server:
db2jcc_license_cisuz-3.64.106.jar
db2jcc-3.64.106.jar
Sample call made from PRPC.
call SYSPROC.CIP_CIPSPCIA ('F','0','FDC1307230000500',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','S',{.QRYERRO out},{.QRYRET out},0);
Pl let me know if you need additional info.
================================================
Procedure Name:
SYSPROC.CIP_CIPSPCIA
Parameter Description:
Parm Char
Seq Parameter name type Data type Length Encode Scale Subtype
---------------------------------------------------------------------------
10 QRYARA In CHAR 1 EBCDIC 0 SBCS AREA
20 QRYDB In CHAR 1 EBCDIC 0 SBCS Which Database
30 QRYTRN In CHAR 16 EBCDIC 0 SBCS TRN
40 QRYMLT In CHAR 13 EBCDIC 0 SBCS **This is a “Montran Literal” Would need Dave’s input for more info**
50 QRYMNI In CHAR 26 EBCDIC 0 SBCS Montran Number
60 QRYIVN In CHAR 16 EBCDIC 0 SBCS Voucher Number
70 QRYROL In CHAR 2 EBCDIC 0 SBCS Party Roll 1
80 QRYACC In CHAR 15 EBCDIC 0 SBCS Party Account 1
90 QRYROL2 In CHAR 2 EBCDIC 0 SBCS Party Roll 2
100 QRYACC2 In CHAR 15 EBCDIC 0 SBCS Party Account 2
110 QRYCRDB In CHAR 1 EBCDIC 0 SBCS TRN “D” or “C”
120 QRYAMF In CHAR 16 EBCDIC 0 SBCS Amount From (front loaded with zeros)
130 QRYAMT In CHAR 16 EBCDIC 0 SBCS Amount To (front loaded with zeros)
140 QRYVDF In CHAR 6 EBCDIC 0 SBCS Date From (YYMMDD)
150 QRYVDT In CHAR 6 EBCDIC 0 SBCS Date To (YYMMDD)
160 QRYPRD In CHAR 3 EBCDIC 0 SBCS Product Type
170 QRYCRN In CHAR 16 EBCDIC 0 SBCS CRN
180 QRYCRDB2 In CHAR 1 EBCDIC 0 SBCS TRN 2 “D” or “C”
190 QRYIMD In CHAR 6 EBCDIC 0 SBCS FED IMAD
200 QRYSSN In CHAR 6 EBCDIC 0 SBCS CHIPS SSN
210 QRYEAMF In CHAR 16 EBCDIC 0 SBCS ** “Editable” “Instruction” Would need Dave’s input for more info**
220 QRYEAMT In CHAR 16 EBCDIC 0 SBCS ** “Editable” “Instruction” Would need Dave’s input for more info**
230 QRYIDF In CHAR 6 EBCDIC 0 SBCS ** “Editable” “Instruction” Would need Dave’s input for more info**
240 QRYIDT In CHAR 6 EBCDIC 0 SBCS ** “Editable” “Instruction” Would need Dave’s input for more info**
250 QRYIMADDT In CHAR 8 EBCDIC 0 SBCS IMAD Date (YYYYMMDD)
260 QRYIMADBK In CHAR 8 EBCDIC 0 SBCS IMAD Destination ID
270 QRYIMADSQ In CHAR 6 EBCDIC 0 SBCS IMAD last six Sequence
280 QRYCALLTYP In CHAR 1 EBCDIC 0 SBCS CIA “P” or SI “S”
290 QRYERR Out INTEGER 4 0
300 QRYSQL Out CHAR 4 EBCDIC 0 SBCS
310 QRYRET Out INTEGER 4 0
Sample
call SYSPROC.CIP_CIPSPCIA("F", "0", " ", " ", " ", " ", "DB", "CAS/8900709030 ", " ", " ", "D", "0000000024612.30", "0000000024612.30", "131025", "131025", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", " ", QRYERRO output, QRYSQLO output, QRYRETO output)
PRPC Connect Rule
call SYSPROC.CIP_CIPSPCIA ({SearchCIP.SearchArea},{SearchCIP.SearchDatabase},{SearchCIP.TRN},' ',' ',' ',{SearchCIP.PartyRole},{SearchCIP.PartyAccountNumber},{SearchCIP.PartyRole2},{SearchCIP.PartyAccountNumber2},{SearchCIP.CreditDebit},{SearchCIP.AmountFrom},{SearchCIP.AmountTo},{SearchCIP.ValueDateFrom},{SearchCIP.ValueDateTo},{SearchCIP.ProductType},{SearchCIP.CRN},{SearchCIP.Amount},{SearchCIP.IMAD},{SearchCIP.SSN},' ',' ',' ',' ',{SearchCIP.IMADDate},{SearchCIP.DestinationID},{SearchCIP.FEDSequence},'S',0,' ',0);
Error Messages
Error from the driver/database for this store procedure invocation from Pega:
There was a problem performing a database query: There was a problem getting a list: code: -301 SQLState:
42895 Message: DB2 SQL Error: SQLCODE=-301, SQLSTATE=42895, SQLERRMC=29, DRIVER=3.64.106
Steps to Reproduce
Execute the Activity which calls the connect sql rule.
Root Cause
Below syntax resolved the problem. datatype 'Integer' is explicitly specificed in the store procedure call.
{call SYSPROC.CIP_CIPSPCIA ('F','0','FDC1406050000400',' ',' ',' ',' ',' ',' ' ,' ' ,' ' ,' ' ,' ' , ' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ',' ',' ',' ',' ' ,' ' ,' ','S',{SearchCIP.SPReturnValue out Integer},{SearchCIP.QRYRET out},{SearchCIP.QRYERRO out Integer})}
Resolution
Below syntax resolved the problem. datatype 'Integer' is explicitly specificed in the store procedure call.
{call SYSPROC.CIP_CIPSPCIA ('F','0','FDC1406050000400',' ',' ',' ',' ',' ',' ' ,' ' ,' ' ,' ' ,' ' , ' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,' ',' ',' ',' ',' ' ,' ' ,' ','S',{SearchCIP.SPReturnValue out Integer},{SearchCIP.QRYRET out},{SearchCIP.QRYERRO out Integer})}
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.