Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Stored procedure - problem handling multiple results

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
geeta.mishra

New User


Joined: 27 Dec 2005
Posts: 21
Location: Bangalore

PostPosted: Thu Jun 05, 2008 8:34 pm    Post subject: Stored procedure - problem handling multiple results
Reply with quote

Hi,

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.

Thanks,
Geeta
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Jun 05, 2008 9:49 pm    Post subject:
Reply with quote

Can you provide some more information about the program?
ex. Cursor declaration, how you are getting input etc.
Back to top
View user's profile Send private message
geeta.mishra

New User


Joined: 27 Dec 2005
Posts: 21
Location: Bangalore

PostPosted: Thu Jun 05, 2008 10:11 pm    Post subject:
Reply with quote

My SPDEF declaration is as follows:

------------------------------------------------------------------------------------
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'))

AND ( PAYI.PI_ID = :WS-PI-ID )

ORDER BY PAYI.PI_ID

WITH UR

---------------------------------------------------------------------------

Host variable input:
:WS-PI-ID = 0
:WS-ACCT-LVALUES = LOW-VALUES
:WS-ACCT-HVALUES = HIGH-VALUES
:WS-CURR-INPUT-DATE = '2008-06-05'
:IN-CHANNEL-CODE = 1
:IN-CHAN-CUST-ID = 7602
:IN-BANK-ID = 'RBOS'

-----------------------------------------------------------------------------

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.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Thu Jun 05, 2008 11:21 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Sat Jun 07, 2008 2:21 pm    Post subject:
Reply with quote

Hi,

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.

thanks,
Jaspal
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Jun 10, 2008 7:24 pm    Post subject:
Reply with quote

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

Hope this would help.
Back to top
View user's profile Send private message
jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Tue Jun 10, 2008 7:56 pm    Post subject:
Reply with quote

Hi,

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.

Regards,
Jaspal
+919833814470
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Jun 10, 2008 9:00 pm    Post subject:
Reply with quote

Thanks. The Document is really nice.

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.
Back to top
View user's profile Send private message
anv2005

New User


Joined: 14 Jul 2005
Posts: 44
Location: US

PostPosted: Tue Jun 10, 2008 10:49 pm    Post subject:
Reply with quote

There a tool that allows you to execute your stored procedures without writing any extra front-end programs. Take a look at http://forum.relarc.com/phpbb3/viewtopic.php?f=6&t=5
Back to top
View user's profile Send private message
jaspal

New User


Joined: 22 May 2007
Posts: 68
Location: mumbai

PostPosted: Tue Jun 10, 2008 10:58 pm    Post subject:
Reply with quote

Hi anv,

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.

regards,
Jaspal singh
+919833814470
Back to top
View user's profile Send private message
anv2005

New User


Joined: 14 Jul 2005
Posts: 44
Location: US

PostPosted: Tue Jun 10, 2008 11:17 pm    Post subject:
Reply with quote

The following thread demonstrate a sample invocation of the IBM's stored procedure SYSPROC.DSNACCOR right from within this tool:

http://forum.relarc.com/phpbb3/viewtopic.php?f=6&t=6

The tool allows you to select (among many other things) a stored procedure or UDF, specify the input parameters and then execute SP or UDF.
Back to top
View user's profile Send private message
manikumar_711
Currently Banned

New User


Joined: 11 Sep 2007
Posts: 7
Location: Chennai

PostPosted: Sun Jun 29, 2008 2:29 pm    Post subject: Anybody have material for Stored procedures
Reply with quote

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.


Thanks ,
Manikumar
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Jun 29, 2008 2:38 pm    Post subject:
Reply with quote

Hello,

What are you asking/providing?

Does your reply relate to this topic?

Please clarify your reply and we can start a new topic if your reply is not concerning the question here.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 am
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us