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
 

 

Stored Procedure V/S direct SQL codes

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

New User


Joined: 16 Dec 2005
Posts: 4
Location: Mumbai

PostPosted: Mon Dec 19, 2005 2:41 pm    Post subject: Stored Procedure V/S direct SQL codes
Reply with quote

hi friends,

can anyone specify that what all we have to consider while deciding on whether we should use a Stored Procedure or a direct SQL code to do a database operation
Back to top
View user's profile Send private message

iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Mon Dec 19, 2005 8:57 pm    Post subject: Re: Stored Procedure V/S direct SQL codes
Reply with quote

Hi There,

To your question one simple solution is that if your problem can be solved through SQL queries go ahead with it. You can make use of stored procedures or SQL queries, it all depends on the requirement of your project.

If you implement stored procedure concept in your code then you must be able to analyze the impact of the code how it is getting populated.


Hope I reached you.

Correct me if I am wrong.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Mon Dec 19, 2005 9:20 pm    Post subject: Re: Stored Procedure V/S direct SQL codes
Reply with quote

Good morning, Well at least on this side of the world,

There are many considerations using a stored procedure vs. direct SQL.

There are two types of stored procedures, SQL and external (i.e. COBOL Program).

In our shop we only create COCOL Stored Procedures, so I don?t have much experience with the SQL stored procedures.

Some of the benefits of using a COBOL stored procedure, or other language that supports DB2, is that the logic can be quite complex. It is, in fact, a subroutine that can contain all the functionality of any other subroutine you might write. I wrote one about a year ago that traverses 18 tables. In this case, as with all public subroutines, anyone that uses the subroutine receives the same answer because the logic is the same, nobody is trying to re-invent the wheel, and if the logic requires updating, everyone gets the update.

On the down side of DB2 Stored Procedures, they are very expensive on resources and should be called on a limited basis. The stored procedure I mentioned above was developed for an on-line application, where an additional .1 - .25 seconds for an on-line application is a small price. In a batch program that tried calling it about 8 million times as a mass update would have run for days. You do the math.. I am currently re-writing the stored procedure as a mass update program using several temporary tables and joining the 18 tables above in a different way that will take less than 15 min to run.

So, the answer is not easy. If the SQL is simple, and going to be called many times, I?d probably code it directly in the program. The more complex the logic, the Stored Procedure becomes a better choice, but called on a limited basis.
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
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
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am


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