Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

For Loop or Cursor in SQL script

Avatar of harsha_james
harsha_james asked on
DatabasesDB2
5 Comments1 Solution2541 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent OlsenFlag of United States of America imageData Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers