pass an recordset to a procedure

[tomtailor]

Hello!

I am migrating from PostgreSQL to DB2 and now I've got a problem with a
Stored Procedure. In PG it is possible to pass SQL Results to a
Procedure but how to do this in DB2? The online Doku only explains the
"normal" Datatypes such as int, real, ...

The procedure call should look like:
call myprocedure((select nr from street where ...));

Can Anyone hlep??

Thanks
Christian Meier

[nsd]

Hi,
The following code may help u..

db2 describe table t_street
--------------------------------------------------------------------------------
Column Type Type
name schema name Length
Scale Nulls

------------------------------ --------- ------------------ --------
----- -----
-
SNO SYSIBM INTEGER 4
0 Yes

ADD1 SYSIBM VARCHAR 20
0 Yes
2 record(s) selected.

---------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
CALLER
---------------------------------------------------------------------------------------------------
CREATE PROCEDURE proc_main()
LANGUAGE SQL
SPECIFIC mainprocedure
LOOP:
BEGIN
DECLARE v_sno integer default 1;
DECLARE v_stmt VARCHAR(100);
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_sql STATEMENT;
DECLARE cur_slctfrmstrt CURSOR FOR SELECT sno FROM t_street;
OPEN cur_slctfrmstrt;
WHILE(SQLSTATE = '000000') DO
FETCH cur_slctfrmstrt INTO v_sno;
SET v_stmt = 'CALL myprocedure(?)';
PREPARE v_sql from v_stmt;
EXECUTE v_sql USING v_sno;
CLOSE cur_slctfrmstrt;
END WHILE;
END LOOP

DB20000I The SQL command completed successfully.
------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
CALLED
--------------------------------------------------------------------------------------------
CREATE PROCEDURE myprocedure(IN a_sno INTEGER)
LANGUAGE SQL
SPECIFIC myprocedure
BEGIN
DECLARE v_sno integer default 0;
SET v_sno = a_sno;
end

DB20000I The SQL command completed successfully.
-------------------------------------------------------------------------------------------------

db2 call proc_main()
Return Status = 0

Regards,
nsd

[tomtailor]

OK, I see ...

i have to correct my positing -> it's a function not a procedure (its
diffrent in DB2) - my mistake! sorry!

Ok, problem is, that the function returns simple out of passed arrays.
such as a weighted arverage. i pass an array to the function an get a
weighted average of the arrays data. i have a lot of those functions
and a lot of uses (thats why i change to db2). if your way is the only
way, i have to develop n caller-functions and it's unpossible to pass
dynamic data, i had to create a caller first for each query.

is there any other/easier way? i tried user difined data types - but
the compiler sais that he don't want to operate with user defined types
like mine (not distinct).

[Knut Stolze]

to*******@freesurf.fr wrote:

OK, I see ...

i have to correct my positing -> it's a function not a procedure (its
diffrent in DB2) - my mistake! sorry!

Ok, problem is, that the function returns simple out of passed arrays.
such as a weighted arverage. i pass an array to the function an get a
weighted average of the arrays data. i have a lot of those functions
and a lot of uses (thats why i change to db2). if your way is the only
way, i have to develop n caller-functions and it's unpossible to pass
dynamic data, i had to create a caller first for each query.

is there any other/easier way? i tried user difined data types - but
the compiler sais that he don't want to operate with user defined types
like mine (not distinct).

If I got this right, you want to implement your own aggregate function (aka
column function). Have a look here for some ideas:
http://www.ibm.com/developerworks/db...309stolze.html

http://www-106.ibm.com/developerwork...lze/index.html

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[tomtailor]

yes but rolling aggregate functions instead of sum over the whole
column i need functions with sum of the last tree - for example.
To do this I pass subqueries with a fetch first n rows only statement.

ok read your links and as far as good, now one last question.

i have
create type mine as (i double) with funktion access mdode db2sql;
--> query OK

then i have
create testfunc(test mine) returns double
language sql
begin atomic
return 1.3; -- just to do something
end
--> Query OK

then i have
select testfunc(mine()..((select nr from street))) from street;

-> Query OK, But the result ist 1.3 as defined(!)

Question now is, how to access values of test with datatype mine() ??
I tried some queries kinds like select i from test()

thank you!!

Christian

[Knut Stolze]

to*******@freesurf.fr wrote:

yes but rolling aggregate functions instead of sum over the whole
column i need functions with sum of the last tree - for example.
That raises immediately the question "what are the last three"? After all,
SQL is set-oriented and there is no order within a set. So there are no
"last three" rows. (Granted, you could use an ORDER BY somewhere to
enforce a certain order.)
ok read your links and as far as good, now one last question.

i have
create type mine as (i double) with funktion access mdode db2sql;
--> query OK
Which query?
then i have
create testfunc(test mine) returns double
language sql
begin atomic
return 1.3; -- just to do something
end
--> Query OK
testfunc() gets a single value of type "mine" as input parameter, processes
it and returns a single value of type "double" as output parameter.
then i have
select testfunc(mine()..((select nr from street))) from street;

-> Query OK, But the result ist 1.3 as defined(!)
I doubt that the query runs ok because it isn't even syntactically correct.

What did you actually expect to happen?

Also, the query did only work because you have at most 1 row in your table
"street", correct? Otherwise you run into the following case:

A value of type "mine" is constructed and its attribute "i" (a scalar value)
is modified/mutated. So whatever you want to set "i" to must be a scalar
value, which implies that your subselect must be a scalar subselect (only
one row, one column --> one value).

Btw, you could probably simplify the above query to:

SELECT testfunc(mine()..i(nr)) FROM street
Question now is, how to access values of test with datatype mine() ??
I tried some queries kinds like select i from test()

What should that do? "testfunc()" (I assume that's the same as "test()" is
a function that returns a scalar value - not a table - of type "double".
You should have a look at table functions if your function is to return
something more complex.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[tomtailor]

yes the thing is, that the input variable contains more than one row,
but returns a single value. table functions are retunring tables thats
not that what i need. I need somithing like sum, avg, min,max, ...

how can i do this?

THX!
Christian

[Knut Stolze]

to*******@freesurf.fr wrote:

yes the thing is, that the input variable contains more than one row,
but returns a single value. table functions are retunring tables thats
not that what i need. I need somithing like sum, avg, min,max, ...

how can i do this?

As I said before, you want to have user-defined aggregates (multiple
rows/values in, single row/value out). There is no native support in DB2
for that kind of feature yet. But the links I provided in the other post
show you a way to achieve what you want.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[tomtailor]

I see ...
So I do have some Java Code ..

Thanks!!!

Christian