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"

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).

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

See if this makes sense: bonehead question

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

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