Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Unhappy Unanswered: Issue trying count all records in tables of an Schema

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Obviously, you need to qualify the table name by adding its schema name when you create your select statement.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2007
    Posts
    8
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by luca15mx
    schema name and table name are fetched in cursor C1
    This is not true.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I would agree with Nick on this one. Review your SQL that builds your C1 cursor and you will see what nick means.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jul 2008
    Posts
    94
    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

  7. #7
    Join Date
    Aug 2007
    Posts
    8

    Red face

    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    IF SQLCODE = 0 THEN
    SET stmt ='SELECT Count(*) FROM ' || vTableSchema || '.' || vTableName;
    IF SQLCODE <> 0 THEN <what did you do?>

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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).

  10. #10
    Join Date
    Aug 2007
    Posts
    8
    tokuma, i'll try you consideration.

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

    Thanks a lot tokuma and Stealth_DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •