Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Calling Stored Procedure from within another Stored Procdure

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

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Thu Mar 15, 2012 3:52 pm    Post subject: Calling Stored Procedure from within another Stored Procdure
Reply with quote

Hello,

I am looking for clarity on a particular scenario regarding nesting of stored procedures. Suppose I have made 2 SQL stored procedures (say SP1 and SP2) for DB2 on Z/OS for inserting data in 2 different tables with 2 columns each (say Col1 and Col2 for Table1 and Col3 and Col4 for Table2). Now, from my java code, instead of calling the stored procedure twice, I want to pass data for all the four columns using a sigle API call (via another stored procedure, say SP3) in order to reduce the IO by having a single API call.

The skeleton of SP1 and SP2 is something like

CREATE SP1
(Col1 CHAR(4), Col2 CHAR(4))
LANGUAGE SQL
MODIFIES SQL DATA
INSERT INTO Table1
(Col1, Col2)
VALUES
(Col1, Col2);

CREATE SP2
(Col3 CHAR(4), Col4 CHAR(4))
LANGUAGE SQL
MODIFIES SQL DATA
INSERT INTO Table1
(Col3, Col4)
VALUES
(Col3, Col4);

So what should be the structure of SP3 that I need to create? Is it something like:

CREATE SP3
(
Col1 CHAR(4),
Col2 CHAR(4),
Col3 CHAR(4),
Col4 CHAR(4)
)

Call SP1(Col1, Col2)
Call SP1(Col3, Col4)

Could some one please validate if Iam proceeding in the right direction?

Thanks in advance!
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Mar 15, 2012 4:22 pm    Post subject:
Reply with quote

as it seems your knowledge is rudimentary,
here are several RED Books, which will increase your skills with SPs.

Stored Procedures, Triggers and UDFs

DB2 for z/OS SPs: Through the CALL and Beyond

DB2 9 for z/OS SPs: Through the CALL and Beyond

and if you would rather watch videos than read:

Data Web Services on DB2 for z/OS, Part 1: Unlock business functions using DB2 for z/OS stored procedures and Data Web Services
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 Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
This topic is locked: you cannot edit posts or make replies. Calling REXX code from ISPF panel sanchita dey TSO/ISPF 4 Mon Jan 02, 2017 5:13 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us