A couple of questions:
1. Is it advisable to use a stored Procedure for an Online application?
2. How to write a Stored Procedure for Mainframe DB2?
3. Is there any way you can deploy your SQL Stored Proc written Stored Procedure Builder of UDB DB2 in Mainframe?
I personally don't think it is usually advisable to use stored procedures for on-line (CICS, etc) mainframe applications. There is virtually no performance advantage and development will be more complex and time consuming in most cases.
Stored procedures for distributed applications (GUI client with mainframe or server database) are definitely advisable since they cut down network communication time and often eliminate data integrity problems associated with remote clients accessing remote databases.
But if one develops an application completely on the mainframe, and then want to move it later to a GUI front end on a remote client, then that would justify stored procedures.
Thanks for the prompt reply!
Here is my situation:
My back-end is Mainframe DB2. we are developing web application to access this Database(Jsps, Servlets etc.,). Though SQL queries can be embedded in jsps, I am not in favour of this! This is why we are thinking of Stored procedures to fetch the resulsts and provide to jsp.
I think one can make a stronger case to use stored procedures when data is being updated on the remote database (because the stored procedure can do multiple validations and updates against other tables at the same time). If you only doing selects, it is sometimes less important, but it depends on how many communication calls to the server can be eliminated by using the stored procedures.
But I don't think there is any one right or wrong answer here. Part of the decision should probably be based on how much web activity there will be, response time requirements, complexity of the SQL's, and how much effort can be expended during development to squeeze the last bit of performance from the application.
I think, here is my second Question gains importance!!
I heard that you require COBOl and a JCL (or whatever!) to write and execute a Stord PProc in Mainframe. If this is the case, it is a painful job to maintain the COBOl programs. But if an SQL Stored Procedure can be written in Stored Procedure builder and if there is a way to deploy this in Mainframe, then maintaing the Procs will be easier.
I don't have any experience in Mainframe DB2. So if you could help me out in this, it will be great!!
Thanks in advance..