Number of Rows

#11

On 2011-04-13 11:09, Joydeep Chakrabarty wrote:

Quote:

Lennart Jonsson submitted this idea :
On 2011-04-13 07:47, Joydeep Chakrabarty wrote:
[...]

I am trying this way, but it's not displaying any data.

create procedure return_count(in s_name varchar(128))
language sql
begin
declare stmt varchar(255);
declare tab1 varchar(250);
declare global temporary table t ( t_name varchar(250), cnt int
);

begin
FOR v_row AS SELECT tabname from syscat.tables where
tabschema = s_name
DO
set tab1 = v_row.tabname;
INSERT INTO session.t (t_name) VALUES (tab1);
set stmt = 'update session.t t1 set CNT = (select
count(1) from ' ||s_name||'.'||tab1||') where t1.T_NAME = ''' ||
tab1||'''';
execute immediate stmt;
END FOR;
end;
end @

In my example I opened a cursor that returns the data, in your code
nothing is exposed to the outside world.

Do you need exact numbers? If your stats are reasonable up to date and
you can live with an approximation, syscat.tables.card might be an
alternative:

select tabname, card from syscat.tables where tabschema = ?

/Lennart

That's new to me. I didn't know about "card". But I wanted to solve the
problem cursor way. I still wonder why it's not working.

Nothing is returned from your procedure. You'll need to do something
like (in the inner block):

begin
declare c1 cursor with return for select t_name, cnt from
session.t;
FOR v_row ...
...
END FOR;
open c1;
end

/Lennart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Number of Rows

comp.databases.ibm-db2 comp.databases.ibm-db2

Re: Number of Rows - 04-13-2011 , 09:11 AM