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.
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.
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.
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
Last Comment
harsha_james
SOLUTION
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.
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.
--------------------------
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
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.
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 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
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