Hello:

Recently, my DB Crashed, loosing some data in some tables. Searching on DBForums for a procedure to count all rows in all tables, in all schemas of the crashed database, i found this:

DECLARE c1 CURSOR FOR
SELECT tabname from syscat.tables where tabschema in ('SY811', 'PD811', 'OL811', 'DD811', 'PS811', 'SVM811') AND tabname not LIKE ('%PSFT.F00165');

DECLARE C2 CURSOR FOR S2;

DECLARE CONTINUE HANDLER FOR not_found

SET stmt = '';

-- No Commitment Control
--Set Transaction Isolation Level NC;

Delete from sy811.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 sy811.COUNTERS (tableName, tableCount)
VALUES (vTableName, vTableCount);
CLOSE C2;
ELSE
LEAVE getRows;
END IF;
END LOOP getRows;

CLOSE c1;
END


Then, in Development Center, is ok to build and deploy; But when it runs show this error:

Data returned in result sets is limited to the first 100 rows.
Data returned in result set columns is limited to the first 20 bytes or characters.
SY811.TABLECOUNT - Exception occurred while running:
Se ha producido un error de gestor de bases de datos.[IBM][CLI Driver][DB2/NT] SQL0204N "PSFT.F00165" is an undefined name. SQLSTATE=42704
SY811.TABLECOUNT - Roll back completed successfully.
SY811.TABLECOUNT - Run failed.

The F00165 table is in OL811 and SY811 Schemas, but in PSFT.

PSFT is the power user i used to have access to database.

Any idea about to solve this issue?

Thanks a lot.

Obviously, you need to qualify the table name by adding its schema name when you create your select statement.

Thanks Nick.

But i think it's a dynamic contructed statement, schema name and table name are fetched in cursor C1, with where clausule schemas.

I pretend to have an automatic way to have this task.

This is not true.

I would agree with Nick on this one. Review your SQL that builds your C1 cursor and you will see what nick means.

Declare a variable for the schema to give as input parameter.


derive a statement

eg.

stmt='select count(1) from '||schema1||'.'||tabname

and then prepare the statement and then execute it

Thanks Lankar.


n_i you'r right, i have a big error in the script, now this is the corrected version... Builds ok, but when i run it, have the followin error.

[IBM][CLI Driver][DB2/NT] SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE statement is blank or empty. SQLSTATE=42617

When debug it, stmt variable it's empty, before to PREPARE S2 FROM stmt;

Any idea?

Thanks in advance.

CREATE PROCEDURE PSFT.Contar ( )
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5);
DECLARE vTableName VARCHAR(25);
DECLARE vTableSchema VARCHAR(10);
DECLARE vTableCount INTEGER;
DECLARE stmt varchar(2000);

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR FOR
SELECT tabname, tabschema from syscat.tables where tabschema in ('SY811', 'PD811', 'OL811', 'DD811', 'PS811', 'SVM811');

DECLARE C2 CURSOR FOR S2;

DECLARE CONTINUE HANDLER FOR not_found

SET stmt = '';

-- No Commitment Control
--Set Transaction Isolation Level NC;

Delete from PSFT.COUNTERS;

OPEN c1;

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

CLOSE c1;
END

IF SQLCODE <> 0 THEN <what did you do?>

luca15mx, Not sure if this is the problem or not but you have defined the variable to hold the table name as VARCHAR(25) and the table schema as VARCHAR(10). However both columns in SYSCAT.TABLES are VARCHAR(128).

Try either defining your variables as VARCHAR(128).

tokuma, i'll try you consideration.

Stealth_DBA you are right, i'll try what you say.

Thanks a lot tokuma and Stealth_DBA