Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    11

    Question Unanswered: General Stored Proc info

    Hi

    I've spent the last 5 years working with SQL Server as a developer and now my new job uses DB2. I'm looking at some stored procedures and from what I've read and seen, things are a little different. I'm trying to figure out what's the best way/guidelines for writing stored procedures.

    It looks like in DB2, you return data to the calling object with the use of a cursor. So I would write my query defined as a cursor, open the cursor and end the stored procedure.

    I'm looking at some stored procedures that have aleady been written. These are basically 200-300 line queries defined as a cursor with joins to numerous tables and subqueries all over the place. This doesn't seem efficient to me, but being new to DB2, I can't be sure. If I were to write this in a SQL Server stored procedure, I would create some #temp tables, do some processing (maybe in a loop or two) to insert data in the temp tables and at the end just do a select on the final #temp table.

    So my questions are:
    1) Would writing a similar stored proc in DB2 (as I would have in SQL Server) work?
    2) What would be the best way to do this (I know "best" can be subjective)?

    TIA

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) Yes
    2) It depends on too many variables to give you an answer.

    You best bet is to try it and see the access plans generated to see if they are optimal.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hookemhorb View Post
    If I were to write this in a SQL Server stored procedure, I would create some #temp tables, do some processing (maybe in a loop or two) to insert data in the temp tables and at the end just do a select on the final #temp table.
    Generally speaking, if something can be done by a single query, it should be done by a single query instead of "maybe a loop or two". This applies to DB2 as well as any other SQL database, because such databases are optimized for query processing but not necessarily for functional programming.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2012
    Posts
    11
    Quote Originally Posted by n_i View Post
    Generally speaking, if something can be done by a single query, it should be done by a single query instead of "maybe a loop or two". This applies to DB2 as well as any other SQL database, because such databases are optimized for query processing but not necessarily for functional programming.
    I guess this is a big difference between SQL Server and DB2 in that cursors are pretty much outlawed in SQL Server. They are used to hold a result set for processing in a loop, not to return data. So, when I hear the term "cursor", I automatically flinch. It's going to take an adjustment period and learning new terminology.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hookemhorb View Post
    cursors are pretty much outlawed in SQL Server
    I learned something new today. Is this an international law, or specific to a particular country?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Oct 2012
    Posts
    11
    Cursors are a different animal in SQL Server. You'll find varying opinions on them. A good developer questions everything and never takes the opinion of just one person (unless they are the ones signing your paycheck). Here's a blog found on microsoft's site: Increase your SQL Server performance by replacing cursors with set operations - SQL Programmability & API Development Team Blog - Site Home - MSDN Blogs.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hookemhorb View Post
    Increase your SQL Server performance by replacing cursors with set operations
    Isn't this just another aspect of if something can be done by a single query, it should be done by a single query instead of "maybe a loop or two"? Only in this case it's a matter of a single UPDATE statement vs. multiple UPDATE statements in a loop?

    You seem to be using the term "cursor" to describe a procedural loop over a cursor.

    Let's look at your examples again:

    So I would write my query defined as a cursor, open the cursor and end the stored procedure [returning one cursor].
    If I were to write this in a SQL Server stored procedure, I would create some #temp tables, do some processing (maybe in a loop or two [over a cursor or two]) to insert data in the temp tables and at the end just do a select on the final #temp table [, returning the third cursor].
    And how is that more efficient?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Several of the places i've worked are totally against cursors for their Win-based and *nix based applications. Most of these people began on sql databases and gui presentation. They knew little or nothing about procedural code.

    The people who have worked with db2 have primarily used procedural languages and the cursor was the way to handle multiple returned rows for a query. The processing of a scrolled screen took multiple "trips" to the db. To help performance, db2 now has multi-row fetch.

  9. #9
    Join Date
    Oct 2012
    Posts
    11
    Quote Originally Posted by n_i View Post
    Isn't this just another aspect of if something can be done by a single query, it should be done by a single query instead of "maybe a loop or two"? Only in this case it's a matter of a single UPDATE statement vs. multiple UPDATE statements in a loop?
    I understand that in most cases if it can be done in a single query, it probably is more efficient, but I've run across a some exceptions in SQL Server mainly dealing with tables with millions of records in them. When you start joining these tables and using subqueries to compare against the last record for a customer and such, it may be better to use temp tables which would hold much smaller subsets of the huge tables. At least this is the case in SQL Server.

    Then being new to DB2, I'm trying to figure out how to write stored procedures that would do something like the following:
    Read through a transaction table for the transactions that were added that day;
    if the transactions were in response to something we sent, then find the corresponding record in our table and update it;
    if it is a new transaction from Vendor A then insert a new record in another table;
    if it is a corresponding record and it was a successful transaction and the customer is from Company A or B, insert a record into a table for Vendor B;
    if the customer is from Company C or D, insert a record into a table for Vendor C and so on;
    Finally return just the corresponding records that were in Error.

    I know how to do something like this in T-Sql, but not in DB2 PL. The only part I can do right now set up the cursor to return the records it is supposed to. It's the processing of the records that I'm having problems with. I'm basically taking T-sql code and translating it. I'm just not sure if this is the best way of doing it which resulted in a question for guidelines.

  10. #10
    Join Date
    Oct 2012
    Posts
    11
    Quote Originally Posted by papadi View Post
    ...To help performance, db2 now has multi-row fetch.
    I'm not familiar with "multi-row fetch". Is this some sort of caching?

    I'll see if I can find any documentation on it.

Posting Permissions

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