Hi
My requirement -

write a function to return count from a table that is given in the
argument.

reason :
use the function in sql like

select tabschema , tabname from syscat.tables where arun.func1
(tabschema,tabname)=0

want a list of all tables that have 0 count (requirement may vary,
like want list of all tables that have more than 10 rows of particular
predicate)....

My function :
create function arun.count_tab(v_tabschema varchar(32), v_tabname
varchar(32))
returns integer
begin atomic
declare v_count integer default 0;
declare stmt1 varchar(100) ;
--declare c1 cursor with return to client for stmt;

set stmt1=  'insert into arun.test1 select count(*)  from  ' ||
v_tabschema || '.' || v_tabname;

prepare stmt from stmt1  ;

execute  stmt into v_count  using v_tabschema, v_tabname;--using
v_tabschema, v_tabname into v_count ;

return v_count;
end;

[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "from"
was found following "name;   prepare stmt".  Expected tokens may
include:  "<join_type_without_spec> JOIN <join_operand>".  LINE
NUMBER=10.  SQLSTATE=42601

Statement processed with ERROR.

The same with SP works, but only when I use the cursor and pass the
count in resultset..

Please help, and let me know wny i cant use prepare and execute in
function.

That's the answer up to and including DB2 9.5.
In DB2 9.7 you can use the full power of SQL PL within a scalar function.
The telltale is omission of the "ATOMIC" keyword.
That is:
CREATE FUNCTION .... RETURN => INLINEd
CREATE FUNCTION .... BEGIN ATOMIC .... END => INLINEd
CREATE FUNCTION .... BEGIN ... END => COMPILEd

Disclaimer: No COMMIT and ROLLBACK in UDF (for obvious reasons)

Cheers
Serge

Yes!

On 26 Okt, 08:31, Arun Srinivasan <arunro... at (no spam) gmail.com> wrote:
Hi
My requirement -

write a function to return count from a table that is given in the
argument.

reason :
use the function in sql like

select tabschema , tabname from syscat.tables where arun.func1
(tabschema,tabname)=0

want a list of all tables that have 0 count (requirement may vary,
like want list of all tables that have more than 10 rows of particular
predicate)....

My function :
create function arun.count_tab(v_tabschema varchar(32), v_tabname
varchar(32))
returns integer
begin atomic
declare v_count integer default 0;
declare stmt1 varchar(100) ;
--declare c1 cursor with return to client for stmt;

set stmt1= 'insert into arun.test1 select count(*) from ' ||
v_tabschema || '.' || v_tabname;

prepare stmt from stmt1 ;

execute stmt into v_count using v_tabschema, v_tabname;--using
v_tabschema, v_tabname into v_count ;

return v_count;
end;

[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "from"
was found following "name; prepare stmt". Expected tokens may
include: "<join_type_without_spec> JOIN <join_operand>". LINE
NUMBER=10. SQLSTATE=42601

Statement processed with ERROR.

The same with SP works, but only when I use the cursor and pass the
count in resultset..

Please help, and let me know wny i cant use prepare and execute in
function.

Arun, I don't have an answer to your question but in general what you
can do in an sql function is more restricted than what you can do in a
sql stored procedure. But if you have a working SP, can't you call
that SP from the function and work around your problem that way?
That's the answer up to and including DB2 9.5.