Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Exclamation Unanswered: Mainframe DB2 Stored Procedures

    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?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I will answer the first question only.

    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.

    This is just one person’s opinion.
    Last edited by Marcus_A; 09-25-03 at 17:18.

  3. #3
    Join Date
    Sep 2003
    Posts
    8

    Talking

    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.

    Am I moving in the right direction?
    Thanks,

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    8
    Thanks Again:-)
    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..

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Don't know if an SQL stored procedure developed on Stored Procedure Builder on DB2 for Linux/Unix/Windows can be moved to the mainframe.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you want to use SPB connecting directly to the mainframe DB2?

    Yes this is possible

    http://www-3.ibm.com/software/data/d...pb/client.html

    will give you details ...

    But, if you intend to develop a procedure on the Unix and deploy it on mainframe, mmm ... , not too sure ... You may have limitations ... Cross-Platform SQL Guide may give details

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Sep 2003
    Posts
    8
    Thanks for that!!!
    I got one more day to submit my proposal.. by this time if you have any further information, please let me know...
    Thanks in advance...!!!
    Last edited by ssreep; 09-25-03 at 23:29.

  9. #9
    Join Date
    Sep 2003
    Posts
    8
    What does DB2 Connector do? I got an information that you can qrite SQL procs and execute against Mainframe DB2 through DB2 Connenctor..! True??

    Thanks!!

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    DB2 Connect is a client to talk with DB2 on legacy systems(Mainframe, AS/400 etc) ..

    BTW, you will have to set up DDF on the mainframe side if you intend to use db2 connect ...

    Cheers
    Sathyaram

    Originally posted by ssreep
    What does DB2 Connector do? I got an information that you can qrite SQL procs and execute against Mainframe DB2 through DB2 Connenctor..! True??

    Thanks!!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Sep 2003
    Posts
    8
    Sorry... I couldn't get the second part ...DDF?
    Thanks !!

  12. #12
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is Distributed Data Facility on Mainframe DB2 .. DDF listens at port 446(not too sure) and passes any incoming requests to the DB2 Engine ...

    HTH

    Sathyaram


    Originally posted by ssreep
    Sorry... I couldn't get the second part ...DDF?
    Thanks !!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •