Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Procedure to collect number of rows from all tables.

    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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You also have a problem with delimited table names. I would use:
    Code:
    'SELECT Count(*) FROM "' || '"' || SCHEMA || '"."' || vTableName || '"'
    Of course, this makes schema and table names case-sensitive as well.
    Reply With Quote
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2014
    Posts
    294
    I need the same scenario, this is the code,but this procedure is not working. please let me know what's wrong with this:

    Create Procedure T_COUNT ()
    Dynamic Result Sets 0
    Modifies SQL Data
    Language SQL
    BEGIN
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE exitcode 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='VENKATASD';

    DECLARE C2 CURSOR WITH RETURN FOR S2;

    DECLARE CONTINUE HANDLER FOR not_found
    SET exitcode = 1;

    SET stmt = '';

    -- No Commitment Control

    Delete from M101;

    OPEN c1;

    getRows:
    LOOP
    FETCH c1 INTO vTableName;
    IF exitcode = 1 THEN
    LEAVE getRows;
    END IF;

    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 M101(TABLE_NAME, TABLE_COUNT)
    VALUES (vTableName, vTableCount);
    CLOSE C2;
    ELSE
    LEAVE getRows;
    END IF;
    END LOOP getRows;

    CLOSE c1;
    END

    Thanks in advance

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    but this procedure is not working. please let me know what's wrong with this:.....
    when does the problem occurs ? error message ? any symptom ? any special condition ?
    have you tried debug option ?
    to use your syntax : please let us know what is wrong ....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply . There are no special conditions if record found, lets get the count of rows & Insert them into table. procedure is running but unable to insert values into the external table. I tried with debug after FETCH program is terminating.

Posting Permissions

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