Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2009-12-28
Medium Priority
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
  • 5
LVL 21

Expert Comment

ID: 26132506
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

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

Expert Comment

ID: 26132545
However an indexed view would have increased performances so a view would be the way to go.
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 21

Expert Comment

ID: 26132550
LVL 21

Expert Comment

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

Accepted Solution

Gary Patterson earned 2000 total points
ID: 26133446
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:    http://www.experts-exchange.com/M_4382324.html

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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