Select a count of all rows from all tables

[markgargan]

I originally thought this would be an easy select statement to perform.

After a few minutes I realised I could only do it with a stored procedure. I recently switched jobs and hence dbs from Oracle to DB2 so if my questions seem basic please grant me the benefit of the doubt.

I thought I had it nailed. I'd all the tablenames in my schema coming back via a fetch into a variable 'vTableName'.

Then I tried to prepare a select statement within the for loop (I've posted the code at the bottom) but I couldn't prepare a statement with a SELECT? Maybe someone could explain this.

nor could I just call the select statement with the table as the variable name.

If anyone has any idea how to do this I'd really appreciate the help. I've managed to retrieve the results I needed from a java programme but I'd love to know how to do it with a stored procedure.

The error I get when I try to build the procedure is as follows. I understand the error, I just dunno how to get it to insert the variable name as the table name

DB2ADMIN.tableCount: 40: [IBM][CLI Driver][DB2/NT] SQL0204N "DB2ADMIN.VTABLENAME" is an undefined name. LINE NUMBER=40. SQLSTATE=42704


Thanks,
MArk.

Here's the code from my stored procedure.

------------------------------------------
CREATE PROCEDURE tableCount()
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE vTableName VARCHAR(20);
DECLARE vTableCount INTEGER;
DECLARE stmt varchar(2000);
DECLARE at_end INT DEFAULT 0;



DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR FOR
SELECT tabname from syscat.tables where tabschema='DB2ADMIN';

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;

-- continue if sqlstate 42704 ('undefined object name')
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
SET stmt = '';

Delete from COUNTERS;

OPEN c1;
getRows:
LOOP
FETCH c1 INTO vTableName;

IF at_end = 1 THEN
LEAVE getRows;
END IF;

SELECT count(*) into vTableCount from vTableName;

INSERT INTO COUNTERS (tableName, tableCount)
VALUES (vTableName, vTableCount);

END LOOP;
CLOSE c1;
END

---------------------------------------------------

 

[Mercury2]

Try this, that should work.

CREATE PROCEDURE tableCount()
LANGUAGE SQL
BEGIN
   DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE SQLSTATE CHAR(5);
   DECLARE vTableName VARCHAR(20);
   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='DB2ADMIN';

   [COLOR=red]DECLARE C2 DYNAMIC SCROLL CURSOR WITH RETURN FOR S2;[/color]

   DECLARE CONTINUE HANDLER FOR not_found

   SET stmt = '';

   [COLOR=red]-- No Commitment Control
   Set Transaction Isolation Level NC;[/color]

   Delete from COUNTERS;

   OPEN c1;

   getRows:
   LOOP
     FETCH c1 INTO vTableName;
     [COLOR=red]IF SQLCODE = 0 THEN
       [b]SET stmt ='SELECT Count(*) FROM ' ||  vTableName;[/b]
       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;[/color]

   CLOSE c1;
END

Actually -- I don't know why -- it seems that you can't use a SELECT INTO Variable to count the rows since I had to prepare the SQL stm and use a cursor followed by a FETCH to make it successfully.
I seem that maybe Java and some other HLL support a SELECT INTO clause...

 

[markgargan]

Thanks a million Tall Turkey.

I threw it in but got a couple of errors when building..

firstly I got


DB2ADMIN.tableCount: 22: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "Transaction" was found following " Set". Expected tokens may include: "<space>".

So I took out hte line

Set Transaction Isolation Level NC;

and then I got

DB2ADMIN.tableCount: 15: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<space>" was found following "C2". Expected tokens may include: "DYNAMIC". LINE NUMBER=15. SQLSTATE=42601

I'm trying to build the SP from the Development centre on v8.1

Any ideas? Thanks again in advance.
Mark.

 

[Mercury2]

Yes, take "Set transaction ..." line out and try
DECLARE C2 CURSOR FOR S2; instead
It's at least worth giving a try.

I can test the SP on OS/400 V5R2 platform only.

 

[markgargan]

Hey Tall turkey that worked!!! I tried it with a cursor for
the select before but it didn't work??
I dunno ye must have the touch.

Thanks a million for your help,
Mark.

 

[Mercury2]

happy to oblige, gorgon

Talkturkey is off, long live to Mercury2