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.
DB2Databases

Avatar of undefined
Last Comment
harsha_james

8/22/2022 - Mon
SOLUTION
waynezhu

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
momi_sabag

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
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
Kent Olsen

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
harsha_james

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck