dynamic SQL in stored procedure. Need Help

[db2sysc]

I am getting SQL0519N. What am I doing wrong.
Creare procedure get_table_count
language sql
specific get_table_count
begin

declare v_table_count integer;
declare v_table_name varchar(30);
declare sqlcode integer;
declare v_stmt statement;
declare v_sql varchar(200);

declare c0 cursor for select tabname from syscat.tables where tabschema
= 'db2inst11';

declare c1 cursor for v_stmt;

open c0;
fetch c0 into v_table_name;
while sqlcode <> 100 do

set v_sql ='SELECT COUNT(*) FROM DB2INST11.'||v_table_name
prepare v_stmt from v_sql;
open c2;
fetch from c2 into v_table_count
insert into get_table_count(table_name,table_count) values
(v_table_name,v_table_count);
fetch c0 into v_table_name;
end while;
close c0;

END @

[Serge Rielau]

db*****@yahoo.com wrote:

I am getting SQL0519N. What am I doing wrong.
Creare procedure get_table_count
language sql
specific get_table_count
begin

declare v_table_count integer;
declare v_table_name varchar(30);
declare sqlcode integer;
declare v_stmt statement;
declare v_sql varchar(200);

declare c0 cursor for select tabname from syscat.tables where tabschema
= 'db2inst11';

declare c1 cursor for v_stmt;

open c0;
fetch c0 into v_table_name;
while sqlcode <> 100 do

set v_sql ='SELECT COUNT(*) FROM DB2INST11.'||v_table_name
prepare v_stmt from v_sql;
open c2;
fetch from c2 into v_table_count
insert into get_table_count(table_name,table_count) values
(v_table_name,v_table_count);
fetch c0 into v_table_name;
end while;
close c0;

END @

What about closing c2? ;-) The error message was dead on. It fails in
the second iteration.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jan M. Nelken]

db*****@yahoo.com wrote:

I am getting SQL0519N. What am I doing wrong.

Correcting several syntax errors renders stored procedure usable:

Create procedure get_table_count
language sql
specific get_table_count
begin

declare v_table_count integer;
declare v_table_name varchar(30);
declare sqlcode integer;
-- declare v_stmt statement;
declare v_sql varchar(200);

declare c0 cursor for select tabname from syscat.tables where tabschema
= 'DB2ADMIN';

declare c2 cursor for v_stmt;

open c0;
fetch c0 into v_table_name;
while sqlcode <> 100 do
set v_sql ='SELECT COUNT(*) FROM DB2ADMIN.' || v_table_name;
prepare v_stmt from v_sql;
open c2;
fetch from c2 into v_table_count;
close c2;
insert into get_table_count(table_name,table_count) values
(v_table_name,v_table_count);
fetch c0 into v_table_name;
end while;
close c0;

END @
Jan M. Nelken

[db2sysc]

Thanks a lot! that was a silly mistake

One more question, instead of opening and closing C2, can I simply use
EXECUTE after the prepared statement?

When I use it, it gives me SQL0519N

[Serge Rielau]

db*****@yahoo.com wrote:

Thanks a lot! that was a silly mistake

One more question, instead of opening and closing C2, can I simply use
EXECUTE after the prepared statement?

When I use it, it gives me SQL0519N

No, you need the cursor. In a future release of DB2 you may be able to
execute SELECT INTO dynamically, but not currently.

The only thing you could do to improve the procs readability is to use a
FOR loop instead of the outer cursor. A matter of taste.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab