Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    8

    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 ( )
    LANGUAGE SQL
    NOT DETERMINISTIC
    EXTERNAL ACTION
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    INHERIT SPECIAL REGISTERS
    BEGIN
    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';
    OPEN CountCur;
    CountCur_Loop1:LOOP
    FETCH CountCur INTO V_TabSchema,V_TabName;
    IF SQLCODE = 0
    THEN
    SET SQL_STR ='select count(*) into CNT from '|| V_TabSchema||'.'||V_TabName;
    PREPARE SQ FROM SQL_STR;
    execute SQ;
    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;
    EXECUTE SQL;
    ELSE
    CLOSE CountCur;
    LEAVE CountCur_Loop1;
    END If;
    END LOOP CountCur_Loop1;

    END;
    Getting below error:-
    ERROR [42703] [IBM][DB2/AIX64] SQL0206N "CNT" is not valid in the context where it is used. SQLSTATE=42703

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SET SQL_STR ='select count(*) into CNT from '|| V_TabSchema||'.'||V_TabName;

    SET SQL_STR ='INSERT INTO NGCORE_TMP.PHYISICAL_BACKOUT_REC_CNT(SCHEMA_NM,TAB LE_NM,BEFORE_CNT)VALUES('''||V_TabSchema||''','''| |V_TabName||''','||CNT||')';
    It might be better to combine these two statements into one.

    Resulting statement string looks like...
    INSERT INTO NGCORE_TMP.PHYISICAL_BACKOUT_REC_CNT
    ( SCHEMA_NM , TABLE_NM , BEFORE_CNT )
    VALUES( '<TabSchema>' , '<TabName>' , (select count(*) from <TabSchema>.<TabName>) );
    or
    INSERT INTO NGCORE_TMP.PHYISICAL_BACKOUT_REC_CNT
    ( SCHEMA_NM , TABLE_NM , BEFORE_CNT )
    SELECT '<TabSchema>' , '<TabName>' , count(*)
    FROM <TabSchema>.<TabName>;

    Note: <TabSchema> and <TabName> should be filled by the values of V_TabSchema and V_TabName.
    Last edited by tonkuma; 07-05-12 at 03:35. Reason: Add second sample.

  4. #4
    Join Date
    Jul 2012
    Posts
    8

    Thnx tonkuma, Now it is working fine

    Thnx tonkuma, Now it is working fine. I did as per your suggestion..
    Cheers..

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •