Unanswered: DB2 Dynamic query execution which returns row count
Here i am trying to run a dynamic query in DB2 which returns row count in a variable.
Could anyone help me out how to store the result of a dynamic query into a variable so that I could use it further? Below are code snippet:--
CREATE PROCEDURE SP_REC_COUNT ( )
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
DECLARE SQL_STR VARCHAR(5000);
DECLARE V_TabSchema VARCHAR(50);
DECLARE V_TabName VARCHAR(100);
DECLARE CNT INTEGER DEFAULT 0;
DECLARE CountCur CURSOR FOR select TabSchema,TabName from SysCat.Tables where TabSchema='NGCORE' or TabSchema='NGCOREAU';
FETCH CountCur INTO V_TabSchema,V_TabName;
IF SQLCODE = 0
SET SQL_STR ='select count(*) into CNT from '|| V_TabSchema||'.'||V_TabName;
PREPARE SQ FROM SQL_STR;
SET SQL_STR ='INSERT INTO NGCORE_TMP.PHYISICAL_BACKOUT_REC_CNT(SCHEMA_NM,TAB LE_NM,BEFORE_CNT)VALUES('''||V_TabSchema||''','''| |V_TabName||''','||CNT||')';
PREPARE SQL FROM SQL_STR;
END LOOP CountCur_Loop1;
Getting below error:-
ERROR  [IBM][DB2/AIX64] SQL0206N "CNT" is not valid in the context where it is used. SQLSTATE=42703
the into cnt is only for immediate executed sql
have a look at PREPARE
or look in sample directory below sqllib for samples
Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified http://www.infocura.be