Hi,

is there a way to force db2 to invoke a user defined sql function in the select list of a select statement once per row returned by that select?

I have an udf like that:

create function nextid()
returns decimal(10)
language sql
not deterministic
external action
reads sql data
begin atomic
declare v_id decimal(10);
set v_id = next value for sys_objektid_seq;
return v_id;
end

(In reality that function contains some more lines to return a modified id, but the problem can be produced with this one).

The following statement

select nextid() from sysbed

where sysbed is a table with more than one row. The result of that select contains one row per row in the table, but the column value is the same in all lines. Executing the same statement again shows that the function has been invoked only once for the complete statement and not once per row (directly using next value for .... in the select list will return a new value per row).

I've allready tried modified function definitions (no external action and/or not deterministic) but that has not helped.

Any ideas to solve that without using a (java) external function (which is bad because in that case we had to deploy more than just sql scripts)? We are using DB2 8.2 Express Edition.

Holger Schlegel

Have you considered using
select rownumber() over () from sysbed

Sathyaram

I read that suggestion in another thread in this forum, but opened a new thread because that will not work for me:

1.) It generates the same ids each time the query is executed but I need new ids in each execution of the select.
2.) That select statement is just an example. In real, there are multiple "insert into select" statements targeting different tables. Each row inserted that way needs an id unique across all tables.
3.) As I wrote, the real function nextid() does more than simply returning the id generated from the sequence (the sequence value is multiplies by 10000 and a value read froma config table is added to it).

Holger Schlegel

Try adding a parameter to your function. You don't need to actually use the value for anything; I guess just referring to a table column in the function call could be enough.

I have tried that.

Passing in a column of the table, but not using the parameter inside the function results in the same effect, only one invocation of the function per query.

Adding a dummy variable to the function and assigning the parameter value to that variable (which is otherwise completly ignored) has the effect that the function gets invoked once per row, even if the passed in column contains duplicate values.
I also tried to pass in a literal value instead of a column but in that case, the function is again only invoked once per query. The same happens if I call the function using generate_unique() as argument (which should return a new, unique value each time it is called).

The select statement that contains the function invocation is generated dynamically by the client and executed via jdbc. The problem is that the code does not know anything except the name about the source table of the generated select, so it is not possible to automatically generate a table column as function argument.
If there any way to get the rownumber() of the query to be passed in as function argument? If yes, that might be a work around because it looks like a non-argument function in total.

Btw., even a non-argument function that internally uses the function RAND() is invoked only once per query...

Holger Schlegel

create function nextid(x int)
...
...
return v_id+x-x;
end

In the SQL ,

select nextid(int(case when 1=1 then char('0') else char(col1) end)) from tab1

where col1 is the name of a column in tab1 ...

I guess this should cause the function to be invoked once for each row ..

Try and let us know if this works ...

Cheers
Sathyaram

As I wrote in my previous post, the function gets invoked for each line if I pass in a column of the table directly and the passed in value does not matter. There is no need to use a "case ..." clause for the argument.
So "nextid(col)" works where col is any column of the source table.

The "remaining" main problem is, that I can not pass in a column of the selects source table because the code that generates and executes the statement does not know anything about the source table except its name.

Holger Schlegel