helpt to fix the stored proc to count rows of tables in db

[niceboykunal123]

CREATE PROCEDURE tableCount()LANGUAGE SQLBEGIN 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'; DECLARE C2 DYNAMIC SCROLL CURSOR WITH RETURN FOR S2; DECLARE CONTINUE HANDLER FOR not_found SET stmt = ''; -- No Commitment Control Set Transaction Isolation Level NC; 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

 

[MarcLodge]

Hi,
When asking for help on here, it's always best to describe the problem, what you have done so far, and any error messages that you are getting rather than just posting a chunk of code.

Are you trying to write a stored procedure that counts the number of rows in each table in the catalog?

If so, what error is being returned?

Marc

 

[niceboykunal123]

Hi,

Yes I am trying to write stored proc, which counts the no. of rows present in the table and have the code as below:-

CREATE PROCEDURE test_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';
DECLARE CONTINUE HANDLER FOR not_found
SET stmt = '';


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

========================================================================
I am getting ERROR as
13:29:58.015 DBMS SAEQODS_STAGE_US_Standalone -- Error: [IBM][CLI Driver][DB2/SUN64] SQL0504N The cursor "C2" is not defined. LINE NUMBER=24. SQLSTATE=34000
============================================================================

Help appreciated...!

Cheers
Kunal

 

[MarcLodge]

Hi Kunal,
In your code, you don't have a definition for the C2 cursor.You'll need to define that properly before your query will work. You also have a PREPARE statement for S2, for which I can see no other previous definition.

Hope this helps.

Marc

 

[niceboykunal123]

Could you please help me to FIX the same, tried ifs & buts but No Luck....!

 

[MarcLodge]

Hi Kunal,
I haven't been able to test this as I'm not near a DB2 box, but try adding a line in the declare section that says:

DECLARE C2 CURSOR WITH RETURN FOR S2;

Let me know if this helps.
Marc