Hi
I want to get a count from some tables whose names are passed using a
cursor against syscat.tables view.
I want to achieve the following.

SET TXT1 = 'insert into db2admin.size_store(count) SELECT
count(*) FROM ?' || '.' || '? where avg>10';
PREPARE ST1 FROM TXT1 ;
EXECUTE ST1 using v_tabschema,v_tabname ;

It throws an error 42601 - saying 'An unexpected token SELECT count(*)
FROM ?.?' was found following 'tore(count)'. Expected tokens may
include "<from clause>" SQLCODE : SQL0104N

I've tried substituting the parameter markers on other parts of the
query, works perfectly fine. If this is disallowed in db2, what could
I use to achieve the above?

On 2010-06-23 19:52, Arun Srini wrote:
You can't use parameter markers that way (AFAIK). I'll suggest you have
a look at the execute immediate stmt. Example:


db2 "create table size_store (cnt int not null)"

db2 "create procedure test
(tabschema varchar(128), tabname varchar(128)
) begin
declare stmt varchar(300);
set stmt = 'insert into size_store(cnt) select count(1) from ' ||
tabschema || '.' || tabname;
execute immediate stmt;
end"

db2 "select count(1) from size_store"

1
-----------
0

1 record(s) selected.

db2 "call test('SYSIBM','SYSDUMMY1')"

Return Status = 0

db2 "select count(1) from size_store"

1
-----------
1

1 record(s) selected.


/Lennart

On Jun 23, 11:21*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thanks.. this solved my problem.