INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

 

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

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

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

(OP)
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

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

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

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

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

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

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:

CODE

DECLARE C2 CURSOR WITH RETURN FOR S2;

Let me know if this helps.
Marc

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close