Create Procedure TABLECOUNT ()
Dynamic Result Sets 0
Modifies SQL Data
Language SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5);
DECLARE vTableName VARCHAR(50);
DECLARE vTableCount INTEGER;
DECLARE stmt varchar(2000);

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR FOR
SELECT tabname from syscat.tables where tabschema='DB2ADMIN1';
DECLARE C2 CURSOR FOR S2;
DECLARE CONTINUE HANDLER FOR not_found

SET stmt = '';

-- No Commitment Control


Delete from COUNTERS;

OPEN c1;

getRows:
LOOP
FETCH c1 INTO vTableName;
IF SQLCODE = 0 THEN
SET stmt ='SELECT Count(*) FROM ' || vTableName;
PREPARE S2 FROM stmt;
OPEN C2;
SET vTableCount = 0;
FETCH C2 INTO vTableCount;
INSERT INTO COUNTERS (tableName, tableCount)
VALUES (vTableName, vTableCount);
CLOSE C2;
ELSE
LEAVE getRows;
END IF;
END LOOP getRows;

CLOSE c1;
END

Im trying to execute the above procedure to get number of rows for all tables lying under a schema.

When i executing this in the last object it is executing recursively(loop is not ending and im im having more entries for the last object.

One more thing is i want to give schema as IN Parameter.
to give the value during runtime for the below objects.


COUNTERS
'SELECT Count(*) FROM ' || SCHEMA.vTableName

you did not declared any condition to be executed when not_found..

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET ab_end = 1 ;
....
FETCH c1 INTO
v_name, v_creator, v_sinterval,v_rinterval ;
If at_end = 1 then
LEAVE fetch_loop ;


CREATE PROCEDURE DB2MONITOR.BPINS (
IN OPT VARCHAR(100),
..

hope this will help

You also have a problem with delimited table names. I would use:
Of course, this makes schema and table names case-sensitive as well.
Reply With Quote