I am trying to compare the row count of table in two different databases but the tables are similar with same DDL. I need to call table name in cursor and database names are parameters of the procedure.
Replace procedure testProc(a varchar(30),b varchar(30))
-- Cursor is having the names of table in order
OPEN MY_CURSOR ;
WHILE (SQLCODE = 0) DO
FETCH MY_CURSOR INTO T_NAME;
Now I have to comapre the row count of a.T_name and b.T_name. If they are equal continue and if they are not equal register an error and continue.
Can you please help me in this. Any help would be appreciated.
You need to use "dynamic SQL that returns a result set", something like this:
DECLARE SqlStr VARCHAR(1000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT COUNT(*) FROM '||a||'.'||T_name;
PREPARE S1 FROM SqlStr;
FETCH C1 INTO DB_A_CT;
But i was thinking to create a volatile table and insert two values of count of two tables and compare. If duplicate values register error. And at end of loop it will delete the values. It will do the same for all T_NAME. Is this a good idea.
SET V_T_Name = 'INSERT INTO volatiletable SELECT COUNT(*) FROM ' || A || '.' || T_NAME || ';' ;
SET V_T_Name = 'INSERT INTO volatiletable SELECT COUNT(*) FROM ' || B|| '.' || T_NAME || ';' ;
SEL COUNT(*) INTO k FROM volatiletable
GROUP BY rowcount
HAVING ( COUNT(rowcount) > 1 );
DEL FROM Volatiletable ALL;