Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Unanswered: Creating/testing stored procedures and UDFs

    Hi, I have a tricky job to do and ideally need to do it in a way that's generic across databases (SQL Server, Oracle, Sybase & DB2). At the moment I'm struggling with creating and using stored procedures and UDFs in DB2, and I'd also like in general to know how one would approach this task in DB2.

    I'm new to DB2, with lots of experience woth SQL Server and some with MySql & Sybase.

    This is the problem: -
    I have to build up a report matrix consisting of several hundred cells (16 columns x 23 rows). I have to do this for each record in an Asset Class table, so ending up with a number of these matrices, plus a total matrix at the end. Obviously I'm going to put this information into a table for subsequent reporting. Each cell can be created by running a simple "SELECT SUM(value) WHERE condition 1 and condition 2" , and there are a limited number of SELECTs & WHERE conditions (basically a SELECT & a WHERE per column, and a WHERE per row). I could therefore construct the SELECT statement for each cell, rather than having to hand-code the whole lot - e.g. column 4 row 7 would be SELECT_Col4 + WHERE_Col4 + WHERE_Row7.

    My SQL Server solution would be: -
    1. Create a table with the SELECT & WHERE clauses in it as text columns.
    2. Create a UDF (User-Defined Function) which gets passed the column & row number, uses these to grab the statements parts, concatenates them into a string to form the whole select statement, runs it to get the resulting SUM(value) and Returns this value.
    3. Write a script that
    a) Loops through my Asset Class table (using either a cursor or a temporary table)
    b) Inserts an empty results record for each row in the matrix
    c) calls the UDF once for each cell and updates the appropriate value in my results table
    d) When the loop terminates, sum all the values for the final totals matrix

    My DB2 problems are: -
    I don't know how to do this in DB2, especially in a way that would run in both SQL Server & DB2. My main hang-up is that the DB2 CLP is so limited. In SQL Server etc. anything you can do in a stored procedure or udf you can do in the query window/CLP. It seems that in DB2 I can't even use a variable withoiut having to create a procedure - can this really be true or have I missed something obvious?

    I can't even seem to create a stored procedure in the CLP - even the simplest thing doesn't work for me. I've copied & pasted loads of example from the web, but they don't work.

    e.g. I copied this from a tutorial

    CREATE PROCEDURE conv_temp.f_to_c(IN temp_f REAL, OUT temp_c REAL)
    DYNAMIC RESULT SETS 0
    CONTAINS SQL
    DETERMINISTIC
    LANGUAGE SQL
    BEGIN
    DECLARE temp_value REAL;
    SET temp_value = (temp_f - 32);
    SET temp_c = (5 * temp_value) / 9;
    END

    but I just get 'An unexpected token "END-OF-STATEMENT" was found following "LARE
    temp_value REAL". Expected tokens may include: "<psm_semicolon>"'

    As the offending line ends with a sem-colon, I don't understand what the problem is. So basically - how do you create and test stored procedures? I would normally just develop it step by step as a script in the CLP, and when working stick "CREATE PROCEDURE" and a few parameter definitions on the front, and run this to create the stored procedure, and save the script so it can be run on other servers. Job done! But that doesn't seem to be an option in DB2 - what is the accepted method?

    I'm clearly going to struggle to get this set up in a generic way so it will run on any DBMS, but any pointers on areas which should be used/avoided would be welcome. e.g. I see that temporary tables are set up very differently between SQL Server & DB2, so am I better off with a cursor (normally I'd only ever use a cursor as a last resort) - and are temp tables & cursor also something that can't be in a script and have to be in a procedure?

    Sorry this is such a long and tedious question, but I wanted to present all the facts, and I'm at a loss as to how to make a start on this.

    Thanks
    Martin

    Product info -
    Microsoft Windows XP [Version 5.1.2600]

    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with level identifier "03050106".
    Informational tokens are "DB2 v8.1.11.973", "s060120", "WR21365", and FixPak "11".

    Product Name = "DB2 Personal Edition"
    Product Identifier = "DB2PE"
    Version Information = "8.2"
    Last edited by bventure; 02-18-09 at 12:49.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    First of all, DB2 8.2, which you are using, goes out of support after April, so you should consider migrating to a newer version, say, DB2 9.5.

    Secondly, try downloading IBM Data Studio, which provides facilities for SP debugging, among other things. IBM - Application Development with DB2 9

    Visit IBM DeveloperWorks - there are lots of tutorials and examples.

    Lastly, I think your approach to cross-platform development will fail. The only feasible way to build a database-agnostic solution would be to use one of the standard interfaces (ODBC, JDBC) and stick to pure SQL wherever possible (still does not guarantee portability though, because not all vendors implement standard SQL).
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    Hi Nick, thanks for your reply, and here are my comments.

    DB2 8.2 -
    was supplied by my employer as we have a client still using it. I will raise this as an issue.

    SP debugging -
    Debugging an SP is a strange concept in itself to me. I would normally just build a script step by step in CLP or equivalent and stick CREATE PROC on the front when it was working. I can't get my head around the concept that CLP, scripts & SPs have different capabilities! Why aren't they parsed & executed by the same engine? Very strange. In any case, I can't even consider debugging an SP until I can create one, which I've so far singularly failed to do. Can someone definitively tell me how to do it? Is there some reason I can't do it in CLP, and if so how is it done?

    ...cross-platform development will fail -
    I agree, it's a tall order, but it's part of the design brief so I have to give it a shot. I did a similar one OK a while back, but in this one I have to loop through a table creating a report matrix per record. This means as far as I can see I'm stuck with using a cursor (always my last resort) or a temp table. It seems in DB2 this means I can't do it in a script for some reason, but have to use a procedure. I hoped to use ANSI standard SQL throughout, but as you say this has been implemented differently by different vendors. I might have a chance with a cursor, but temp table implementation appears to differ too drastically.

    The general approach I outlined wasn't really meant to be cross-platform specifically, it was just how I thought it *should* be done. If this approach is not appropriate for DB2, I would be only too pleased to hear a more generic alternative.

    Regards
    Martin

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by bventure
    I can't even consider debugging an SP until I can create one, which I've so far singularly failed to do. Can someone definitively tell me how to do it? Is there some reason I can't do it in CLP, and if so how is it done?
    See if this makes sense: http://www.dbforums.com/db2/1634932-...-question.html

    PS. Just so there's no misunderstanding: that above was the subject of the original thread way back; it's not my judgment about your question
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2009
    Posts
    6
    Thanks again for your assistance Nick - must admit I feel like a bit of a bonehead trying to get to grips with DB2 - I shall consider it the learning process. Some of my difficulty with this is no doubt due to the fact that SQL Server doesn't require command terminators so delimiters have never really impinged on me - although GO can be used as a batch delimiter, fulfilling the same role as the @ in this case to indicate the end of the CREATE.

    Anyway, finally managed to create a SP with CLP after discovering I had to put -td@ in the command line too. Now, can I do the same in the Command Editor? I must admit I'm uncomfortable with command line processing, it's so unfriendly, I'd rather use the Command Editor if at all possible.

    Having got over the first hurdle I now need to try to create one that actually does something useful. I'll no doubt be back when I can't get my first cursor working...

    Edit >>>> have now tried to run the SP I created and it doesn't work.

    This was copied from a tutorial so I assumed it was OK, or is it something I have/haven't done?

    Here is the script I ran:
    --------------------------------------------------------------
    CONNECT TO SAB2@
    CREATE PROCEDURE f_to_c(IN temp_f REAL, OUT temp_c REAL)
    DYNAMIC RESULT SETS 0
    CONTAINS SQL
    DETERMINISTIC
    LANGUAGE SQL
    BEGIN
    DECLARE temp_value REAL;
    SET temp_value = (temp_f - 32);
    SET temp_c = (5 * temp_value) / 9;
    END@
    --------------------------------------------------------------

    Here is my call (in Command Editor)
    ---------------------------------
    CALL f_to_c(212, ?)
    ---------------------------------

    And here is the reply
    ------------------------------ Commands Entered ------------------------------
    CALL f_to_c(212, ?);
    ------------------------------------------------------------------------------
    CALL f_to_c(212, ?)
    SQL0579N Routine "" (specific name "") attempted to read data but was not
    defined as READS SQL DATA or MODIFIES SQL DATA. SQLSTATE=38004

    SQL0579N Routine "1" (specific name "1252") attempted to read data but was not defined as READS SQL

    DATA or MODIFIES SQL DATA.

    Explanation:

    The program used to implement the body of a routine is not
    allowed to read SQL data.

    User Response:

    Remove any SQL statements that read data then recompile the
    program. Investigate the level of SQL allowed as specified when
    defining the routine.

    sqlcode : -579

    sqlstate : 38004

    sqlstate : 42985


    I can't see where it reads any SQL data, so what the hell is it moaning about now?

    Thanks again
    Martin
    Last edited by bventure; 02-18-09 at 17:51.

Posting Permissions

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