I am working on a proj where the front end is handled by Java and the Back end has Stored procedures.
I am running the Stored proc through DB2 connect . This Stored proc has total 6 results sets declared in the SPDEF. All the 6 cursors except the 4th cursor are returning the required results to the front end.
The problem here is when I am running the Stored proc through DB2 connect the there are no rows returned by the 4th cursor. Whereas If I run the same (4th query) using SPUFI I am able to get the required rows.
Can anyone please let me know what can the problem here?
All the queries have the same filtering criteria in the WHERE clause.
------------------------------------------------------------------------------------
IN BANK_ID CHARACTER(04) FOR SBCS DATA CCSID EBCDIC,
IN CHANNEL_CODE SMALLINT,
IN CHAN_CUST_ID DECIMAL(11,0),
IN PAY_SUM_TYPE CHARACTER(04),
IN CURR_INPUT_DATE CHARACTER(10),
IN CURRENT_DATE_MIN30 CHARACTER(10),
IN ACCOUNT_NUMBER VARCHAR(34),
IN CUST_STATUS_CODE CHARACTER(05),
IN PAY_TYPE_CODE SMALLINT,
IN PI_ID DECIMAL(11,0),
IN BENEFICIARY_ID DECIMAL(11,0),
OUT RESPONSE_AREA CHARACTER(214) FOR SBCS DATA CCSID EBCDIC)
DYNAMIC RESULT SETS 6
EXTERNAL NAME 'VFSY41'
LANGUAGE COBOL
PARAMETER STYLE GENERAL
FENCED
NO DBINFO
COLLID #COLLID
WLM ENVIRONMENT #DB2SUAE09
ASUTIME LIMIT 50000
STAY RESIDENT YES
PROGRAM TYPE SUB
SECURITY DB2
RUN OPTIONS 'H(,,ANY),STAC(,,ANY,),STO(,,,20K),BE(4K,,),LIBS(4K,
) ,RPTOPTS(OFF),ALL31(ON)'
COMMIT ON RETURN NO
INHERIT SPECIAL REGISTERS;
----------------------------------------------------------------------------------
The input passed to the program is :
call RAVFS3.VFSY41_EXPTPMTREC('RBOS','1','7602','TSUM','2008-06-05','2008-05-06',' ',' ','0','0','0',?)
And the 4th query running using the baove input is:
SELECT PAYI.PI_ID
,BYO_NARP.NARR_LINE1 AS BY_ORDER_ACC_NUM
,BYO_NARP.NARR_LINE2 AS BY_ORDER_OF_LINE1
,BYO_NARP.NARR_LINE3 AS BY_ORDER_OF_LINE2
,BYO_NARP.NARR_LINE4 AS BY_ORDER_OF_LINE3
,BYO_NARP.NARR_LINE5 AS BY_ORDER_OF_LINE4
,PIPY_ORD.PI_PARTY_BIC AS ORD_BIC
,PIPY_ORD.PI_PARTY_NCC_PRFX AS ORD_NCC_PREFIX
,PIPY_ORD.PI_PARTY_NCC AS ORD_NCC
,PIPY_ORD.PI_PARTY_NAME AS ORD_INST_NAME
,ORG_ADDR.ADDRESS_LINE_1 AS ORG_ADDR_LINE_1
,ORG_ADDR.ADDRESS_LINE_2 AS ORG_ADDR_LINE_2
,ORG_ADDR.ADDRESS_LINE_3 AS ORG_ADDR_LINE_3
,ORG_ADDR.ADDRESS_LINE_4 AS ORG_ADDR_LINE_4
,PIPY_INT.PI_PARTY_BIC AS INT_BIC
,PIPY_INT.PI_PARTY_NCC_PRFX AS INT_NCC_PREFIX
,PIPY_INT.PI_PARTY_NCC AS INT_NCC
,PIPY_INT.PI_PARTY_NAME AS INT_INST_NAME
,INT_ADDR.ADDRESS_LINE_1 AS INT_ADDR_LINE_1
,INT_ADDR.ADDRESS_LINE_2 AS INT_ADDR_LINE_2
,INT_ADDR.ADDRESS_LINE_3 AS INT_ADDR_LINE_3
,INT_ADDR.ADDRESS_LINE_4 AS INT_ADDR_LINE_4
FROM TVFPAYI_PI PAYI
LEFT OUTER JOIN TVFPIBS_PI_STATUS PIBS
ON PAYI.PI_ID = PIBS.PI_ID
AND PAYI.CHANNEL_CODE = PIBS.CHANNEL_CODE
AND PAYI.CHAN_CUST_ID = PIBS.CHAN_CUST_ID
AND PAYI.PARTITION_NO = PIBS.PARTITION_NO
LEFT OUTER JOIN TVFIFBS_IF_STATUS IFBS
ON PAYI.IMPORT_FILE_ID = IFBS.IMPORT_FILE_ID
AND PAYI.CHANNEL_CODE = IFBS.CHANNEL_CODE
AND PAYI.CHAN_CUST_ID = IFBS.CHAN_CUST_ID
AND PAYI.PARTITION_NO = IFBS.PARTITION_NO
INNER JOIN TVFDACC_DEBIT_ACC DACC
ON PAYI.PI_ID = DACC.PI_ID
AND PAYI.PARTITION_NO = DACC.PARTITION_NO
INNER JOIN TVFACCT_ACC_STUB ACCT
ON DACC.ACCOUNT_ID = ACCT.ACCOUNT_ID
AND PAYI.BANK_ID = ACCT.BANK_ID
LEFT OUTER JOIN TVFNARP_PI_NARR BYO_NARP
ON PAYI.PI_ID = BYO_NARP.PI_ID
AND PAYI.PARTITION_NO = BYO_NARP.PARTITION_NO
AND BYO_NARP.NARRATIVE_TYPE = 'BYORDEROF'
LEFT OUTER JOIN TVFPIPY_PI_PARTY PIPY_ORD
ON PAYI.PI_ID = PIPY_ORD.PI_ID
AND PAYI.PARTITION_NO = PIPY_ORD.PARTITION_NO
AND PIPY_ORD.PI_PARTY_ROLE_TYPE = 'ORDERINGINST'
LEFT OUTER JOIN TVFADDR_ADDRESS ORG_ADDR
ON PIPY_ORD.PI_PARTY_ADDR_ID = ORG_ADDR.ADDRESS_ID
LEFT OUTER JOIN TVFPIPY_PI_PARTY PIPY_INT
ON PAYI.PI_ID = PIPY_INT.PI_ID
AND PAYI.PARTITION_NO = PIPY_INT.PARTITION_NO
AND PIPY_INT.PI_PARTY_ROLE_TYPE = 'INTERMEDINST'
LEFT OUTER JOIN TVFADDR_ADDRESS INT_ADDR
ON PIPY_INT.PI_PARTY_ADDR_ID = INT_ADDR.ADDRESS_ID
WHERE PAYI.CHANNEL_CODE = :IN-CHANNEL-CODE
AND PAYI.CHAN_CUST_ID = :IN-CHAN-CUST-ID
AND PAYI.BANK_ID = :IN-BANK-ID
AND PAYI.EXECUTION_DATE = :WS-CURR-INPUT-DATE
AND PAYI.PI_TYPE IN ('PAY','BLP')
AND COALESCE(PIBS.BUS_STATUS_CODE,' ') NOT IN
('EXPRD','DELET')
AND (ACCT.ACCOUNT_NUMBER BETWEEN
:WS-ACCT-LVALUES AND :WS-ACCT-HVALUES)
AND (IFBS.IMPORT_STATUS IS NULL
OR IFBS.IMPORT_STATUS IN ('IMPSU','IMPER','CANCD'))
Please note that the WHERE clause which I am using in the above query is exactly same in rest 5 queries as well and they are returning results when tested thru DB2 connect while the above query does not.
If I run the above query in SPUFI I am getting the proper results.
Please check your cursor declaration whether you are using "WITH HOLD WITH RETURN".
If you have not used the above in the declare cursor, then when the transaction is completed with the SQLCODE >= 0, SP will be committed, which result in Closing the cursor.
If you have used the above, then put some display statement before opening Cursor and check all the input passed properly to the program.
write a program which would call this procedure : call this from that program and return values from after each query to calling program and use display in that calling program.. u cant or should not use display in store proceudre as it resides in DB2 subsystem and handle all the SQLCODE's other then 0
i think u should use GTT table for store values inside procedure.
In our project, we do have an utility to display the results. That utility will get all the display messages and display it in Spool.
If you have any kind of utility, you can display the results after fetching cursor.
Else as suggested by Jaspal write a COBOL program to call this SP and Display it in the Main program.
If you do not have the previlage to create GTT, then for each Fetch control has to return back to the COBOL program to fetch the results. This leads to have some modification on the original SP.
If you do not want to modify the original SP, then write a new SP to call the Main SP and the fetch should happened in the calling SP and this calling SP should be called from a COBOL program
As per my understanding we can't give displays in store procedure .......audit of package fails where that SP lies.
however, if u want to test it in your test region u can give displays in SP and find result .....and whenu will try to move it in your development region you need to audit the package.
1). SP can we used ib Batch programs.
2). SP can be used for online programs.
i do have document on SP which would make you clear..why we use it what are the possibilty ..and incase you need any code for calling program that calls SP and how it retreives values...schema etc i can help to out.
But Audit job for the components are specific for a Particular project or we can say for an Organization. It is just a check in the Audit job.
But we have Display statements in the Production component also. But it will be controlled by a Flag which will be table driven. If you want to check the problem in production, set the flag = 'Y' in the table and in the next run it will display all the values given in the Display statement.
Whether using or not using Display statements in a SP is based on the Project/Organizational standards.
i dont know what u want to say...we were talking to call
SP through a calling program...however in tha link sent by you say it is called by rexx program.
pls. do clarify wat you want ti give or show in the forum.
just seeing and pasting the web link doesn't solve the purpose.
Hi,
I got offer from different organisation, but they are looking for candidates known as DB2 Stored Procedures. if anyone having pls upload here. its usefull for others also.
