Stored Procedures vs views for connecting to Iseries DB2 databse using .NET

Posted on 2009-12-28
Last Modified: 2012-05-08
I have a requirement to access the Iseries DB2 database using .NET.
I need to acce a number of AS 400 files on DB2.
For that we can think of the option of creating store procedures using Iseries ACcess, since stored procedures are mostly used for connecting to the datbases from .NET.
However we got a suggestion from a more experienced person on AS 400 that views will probably be a better solution.
We were told that it will be very easy for an AS 400 person to create one single views on all the tables that are required in a query and then oiut query will only access that view.
Can some one please suggest the pros and cons of  using views over Sored procedures or vice versa?
Question by:subhorachana
    LVL 21

    Expert Comment

    hmmm...both have cached exec plans, the only thing I can think that can be of benefit with the view is that security would be better since you can hide rows that you may not want exposed.
    LVL 21

    Expert Comment

    However and indexed view would have increased performances so a view would be the way to go.
    LVL 21

    Expert Comment

    However an indexed view would have increased performances so a view would be the way to go.
    LVL 21

    Expert Comment

    LVL 21

    Expert Comment

    i should warn you though...indexed views are more efficient for loading, but not if you're going to have many updates then the bad outweigh the benefits...
    LVL 34

    Accepted Solution

    There is somewhat of a "holy war" among developers about the "right" place for business (application) logic: in the DBMS, on the same platform with the DBMS, on an application server, or in the client application.  

    IMHO, the answer is "it depends".

    Creating views is a nice way to expose specific data to applications.  Views do have some limitations, but they are particularly nice for read-only applications that allow users to do column and row selection (report writers, EIS applications, data warehouses, etc.), and allows nice, granular control over the specific columns and rows presented to user applications, plus the ability to create joined views and sophisticated derived columns for users so that they don't have to figure out how to do that themselves.

    Performance can be an issue with extensive use of views (it can be an issue with stored procedures, too, but you have more control over performance impact with stored procedures).  In my real-world experience, applications that form queries and pass them back to the DBMS are more likely to have security holes than applications that exclusively make stored procedure calls, though this is more an application and security design issue than a "views" vs "stored procedures" issue.  Still, any time you allow applications do directly query the database, you create more data security exposure than when you have a more rigid, limited interface like through a SP.

    With views, the "application logic" is offloaded from the database server to an application server or a client application.  This can be a good thing or a bad thing, depending on where your greatest application development and processing resources lie.

    With stored procedures, some or all of the application logic is stored on the database server.  Stored procedures have benefits, including:
    • Higher levels of performance can sometimes be obtained with well-designed and well-written stored procedures.
    • Applications designed around stored procedures are often inherently more secure than applications that perform remote database queries.
    • On AS/400, stored procedures can be built in legacy languages (CL. RPG, COBOL), as well as AS/400 DB2 SQL PSM and Java, making it easy to reuse existing business logic.
    Stored procedures also have some drawbacks:

    • Application coding skills are required on the database server platform (AS/400, in this case), which may not be an ideal environment for .NET developers.
    • Database portability of applications is reduced somewhat, since stored procedures are generally not portable between different DBMS's.
    I've worked with numerous clients that have been faced with these questions in the AS/400 environment, and some of the factors that they have considered include:

    • Existing and future developer skill sets.
    • Need or desire to leverage legacy code.
    • Requirement (or lack thereof) for programming language, OS, and/or DBMS portability of applications.
    • Capacity and cost of various hardware and software resources.
    • Cost of various development resources (people cost)
    • Application and system availability requirements (you can't build an application that requires 99.99% availability on a middleware layer that is only 99% available, for example)
    • Increased troubleshooting complexity of cross-platform applications
    • Technical requirements of the applications being developed
    • Application functional requirements
    • Development schedules and deadlines
    • And more
    Hope this helps.

    - Gary Patterson

    Check out my EE profile:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Title # Comments Views Activity euclidean math 2 26
    Wordmerge 8 29
    Parse CSS value with RegEx 2 28 application + check if already running 5 27
    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now