Link to home
Create AccountLog in
Avatar of harsha_james
harsha_james

asked on

For Loop or Cursor in SQL script

I require a script which gives me COUNT(*) output for all tables in my DB for Specified schema name.

I have written following sql script for the same,
CONNECT TO BTMNEW21@
SET CURRENT SCHEMA = 'SONEDBA'@
BEGIN ATOMIC
FOR ROW AS
select strip(name) as NAME from sysibm.systables where creator = 'SONEDBA'
DO
select count(*) from NAME;
END FOR;
END@
CONNECT RESET@
I am executing the script as db2 –td@ -f scriptname.sql>scriptname.out
In above script I expect output as all select count(*) statement followed by there result.
Ex:  select count(*) from tab1  
        200
       select count(*) from tab2  
        100
      select count(*) from tab3  
        2300

But I am getting output in out file as
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned: SQL0204N  "SONEDBA.NAME" is an undefined name.  LINE NUMBER=5.  SQLSTATE=42704

Please help me regarding above task,
Is my way of doing is right OR I need to use cursor for the same.
SOLUTION
Avatar of waynezhu
waynezhu

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
you have to use dynamic sql
you can't have the table name specified using a variable
check out the link that was posted above,
plus
when you run the CLP use the -v flag so it will echo each statement before its results
Avatar of harsha_james
harsha_james

ASKER

According to Link I have created following SP and it is working fine.
--------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE tableCount()
LANGUAGE SQL
BEGIN
   DECLARE SQLCODE INTEGER DEFAULT 0;
   DECLARE MYSQLCODE INTEGER;
   DECLARE SQLSTATE CHAR(5);
   DECLARE vTableName VARCHAR(100);
   DECLARE vTableCount INTEGER;
   DECLARE stmt varchar(2000);
   
   
   DECLARE c1 CURSOR FOR
   SELECT strip(name) from sysibm.systables where creator = 'SCBI201';

   DECLARE C2 CURSOR FOR S2;

   SET stmt = '';

   Delete from CountOutput;

   OPEN c1;
   SET MYSQLCODE=SQLCODE;

   getRows:
   LOOP
       FETCH c1 INTO vTableName;
       SET MYSQLCODE=SQLCODE;
     IF MYSQLCODE = 0 THEN
       SET stmt ='SELECT Count(*) FROM ' ||  vTableName;
       PREPARE S2 FROM stmt;
       OPEN C2;
       SET vTableCount = 0;
       FETCH C2 INTO vTableCount;
       SET MYSQLCODE=SQLCODE;
            IF MYSQLCODE = 0 THEN
                  INSERT INTO CountOutput (TableName, RecordCount)
                  VALUES (vTableName, vTableCount);
            ELSE
                  LEAVE getRows;
            END IF;
       CLOSE C2;
     ELSE
       LEAVE getRows;
     END IF;
   END LOOP getRows;

   CLOSE c1;
END@
--------------------------------------------------------------------------------------------------------
Please IF any one knows better Idea than creating SP please let us know.
This is bit complex as customer is showing resistance for SP.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi KDO,
I think this is the Best Simplest way one can do the Job,
I need to learn such type of thinking.
Thanks for Help.

Hi waynezhu,momi_sabag
Thanks for the Help and support