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
 
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: 6968
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Calling CLIST through job and REXX Sahasra K CLIST & REXX 19 Tue Aug 01, 2017 10:25 am
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

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