How do I count number of rows in tables in a schema? I am using DB2 on
ZOS.

Thanks,
Joydeep

Joydeep Chakrabarty submitted this idea :
I wrote this function :

CREATE FUNCTION getRowCount(t_name VARCHAR(10))
RETURNS INT
LANGUAGE SQL
RETURN
SELECT count(*) FROM t_name;

But it's giving me error "SQL0204N "SCHEMA.T_NAME" is an undefined
name."

Please help

Thanks,
Joydeep

On 2011-04-06 06:12, Joydeep Chakrabarty wrote:
[...]
This might not apply to z/os, but in general you can't use a variable as
the tablename. I don't think you can use execute immediate in a
function, it would work in a stored procedure though, so one option is
to stuff that into a procedure and call that from the function. If I
wanted to encapsulate such functionality I would probably do it in the
application layer.


/Lennart

on 4/6/2011, Lennart Jonsson supposed :
I wrote this procedure.

CREATE PROCEDURE return_count (IN schema_name VARCHAR(20))
LANGUAGE SQL
SPECIFIC return_count
tr: BEGIN
DECLARE t_name VARCHAR(50);
DECLARE sql_str VARCHAR(200);
DECLARE cnt_rows INT;
DECLARE r_cur CURSOR FOR
SELECT tabname
FROM syscat.tables
WHERE TABSCHEMA = schema_name
AND TYPE = 'T';
OPEN r_cur;
FETCH FROM r_cur INTO t_name;
WHILE (SQLSTATE = '00000') DO
SET sql_str = "SELECT count(*) FROM " || tname;
EXECUTE IMMEDIATE sql_str INTO cnt_rows;
FETCH FROM r_cur INTO t_name;
END WHILE;
CLOSE r_cur;
END tr

I am still getting lot of errors.
Please help.

Thanks,
Joydeep

On 2011-04-06 09:49, Joydeep Chakrabarty wrote:
[...]
Apperantly you can't use execute immediate for select stmts, sorry if I
mislead you there.


/Lennart

[...]

on 4/6/2011, Lennart Jonsson supposed :
But I am getting "SQL0104N An unexpected token "into" was found
following "TE IMMEDIATE sql_str". Expected tokens may include:
"CONCAT". LINE NUMBER=18. SQLSTATE=42601"

Thanks,
Joydeep

On 04/06/2011 12:45 PM, Joydeep Chakrabarty wrote:
[...]
Not sure this is such a great idea ..., but here it goes:

create procedure return_count(in tabname varchar(128))
language sql
begin
declare stmt varchar(255);
declare global temporary table t ( cnt int not null );

begin
declare c1 cursor with return for select cnt from
session.t;

set stmt = 'insert into session.t (cnt)
select count(1)
from syscat.' || tabname;

execute immediate stmt;
open c1;
end;
end @

db2 "call return_count('tables')"


Result set 1
--------------

CNT
-----------
396

1 record(s) selected.


You'll need a user temporary tablespace for the temporary table, and you
will also have to add handling of SQLSTATE 42710 in case session.t
already exists during declaration

After serious thinking Lennart Jonsson 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 @

--
Thanks and regards,
Joydeep

On 2011-04-13 07:47, Joydeep Chakrabarty wrote:
[...]
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

Lennart Jonsson submitted this idea :
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.

--
Thanks and regards,
Joydeep