Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    2

    Unanswered: Store Results from Execute Immediate

    I have a stored procedure in which I am tryong to set the count variable from a select statement but it is giving error

    set sqlStmt = 'SELECT COUNT(*) FROM GWIO.'||table_name;

    EXECUTE IMMEDIATE sqlStmt INTO count;

    table_name comes from a cursor

    Suggest me the right way to do this

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if you would at least publish the error
    the sp would be nice
    the platform and version would also help
    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
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    --#SET TERMINATOR /
    
    set serveroutput on/
    
    begin
      declare l_tabname varchar(128) default 'tables';
      declare l_cnt int;
      prepare s from 'set ? = (select count(*) from syscat.'||l_tabname||')';
      execute s into l_cnt;
      call dbms_output.put_line('rc: '||l_cnt);
    end/
    Regards,
    Mark.

  4. #4
    Join Date
    Apr 2014
    Posts
    2

    Full Code

    Here is the Full code for the SP, what I am trying to do is get the list of table from a particular schema and for every table get the row count


    CREATE PROCEDURE TableCountStats ()
    P1: BEGIN
    DECLARE table_name VARCHAR(255);
    DECLARE count INTEGER DEFAULT 0;
    DECLARE sqlStmt VARCHAR( 32672 );
    DECLARE vstmt STATEMENT;
    DECLARE GLOBAL TEMPORARY TABLE TABLE_COUNT_STATS(
    TABLE_NAME VARCHAR(255)
    ,ROW_COUNT INTEGER);
    -- Declare cursor
    DECLARE cursor1 CURSOR for
    SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'Employee';
    -- Cursor left open for client application
    OPEN cursor1;
    FETCH cursor1 INTO table_name;
    SET sqlStmt = 'SET(?) = SELECT COUNT(*) FROM GWIO.'||table_name;
    PREPARE vstmt FROM vsql;
    EXECUTE vstmt INTO count ;
    EXECUTE IMMEDIATE 'INSERT INTO TABLE_COUNT(TABLE_NAME,ROWCOUNT) VALUES ('||table_name||','||count||')';
    CLOSE cursor1;
    EXECUTE IMMEDIATE 'SELECT * FROM SESSION.TABLE_COUNT_STATS';
    END P1

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by avenirit View Post
    Here is the Full code for the SP, what I am trying to do is get the list of table from a particular schema and for every table get the row count


    CREATE PROCEDURE TableCountStats ()
    P1: BEGIN
    DECLARE table_name VARCHAR(255);
    DECLARE count INTEGER DEFAULT 0;
    DECLARE sqlStmt VARCHAR( 32672 );
    DECLARE vstmt STATEMENT;
    DECLARE GLOBAL TEMPORARY TABLE TABLE_COUNT_STATS(
    TABLE_NAME VARCHAR(255)
    ,ROW_COUNT INTEGER);
    -- Declare cursor
    DECLARE cursor1 CURSOR for
    SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'Employee';
    -- Cursor left open for client application
    OPEN cursor1;
    FETCH cursor1 INTO table_name;
    SET sqlStmt = 'SET(?) = SELECT COUNT(*) FROM GWIO.'||table_name;
    PREPARE vstmt FROM vsql;
    EXECUTE vstmt INTO count ;
    EXECUTE IMMEDIATE 'INSERT INTO TABLE_COUNT(TABLE_NAME,ROWCOUNT) VALUES ('||table_name||','||count||')';
    CLOSE cursor1;
    EXECUTE IMMEDIATE 'SELECT * FROM SESSION.TABLE_COUNT_STATS';
    END P1
    (Q1) Was the SP worked?
    If not,
    what error messages did you got?
    or what were the differences from your expected results?
    Quote Originally Posted by przytula_guy View Post
    if you would at least publish the error
    the sp would be nice
    the platform and version would also help
    (Q2) Why you insist on EXECUTE IMMEDIATE?

    (Q2-1) Wasn't the direct INSERT statement sufficient?
    EXECUTE IMMEDIATE 'INSERT INTO TABLE_COUNT(TABLE_NAME,ROWCOUNT) VALUES ('||table_name||','||count||')';
    Try like this...
    INSERT INTO TABLE_COUNT(TABLE_NAME , ROWCOUNT)
    VALUES (table_name , count);

    (Q2-2) EXECUTE IMMEDIATE desn't support SELECT statement.
    EXECUTE IMMEDIATE 'SELECT * FROM SESSION.TABLE_COUNT_STATS';
    Please see Description in
    EXECUTE IMMEDIATE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Description

    expression
    An expression returning the statement string to be executed. The expression must return a character-string type that is less than the maximum statement size of 2 097 152 bytes. Note that a CLOB(2097152) can contain a maximum size statement, but a VARCHAR cannot.

    The statement string must be one of the following SQL statements:
    ...
    and also please see Notes in
    PREPARE - IBM DB2 9.7 for Linux, UNIX, and Windows
    •Prepared statements can be referred to in the following kinds of statements, with the restrictions shown:
    In...
    The prepared statement...
    DESCRIBE
    can be any statement
    DECLARE CURSOR
    must be SELECT
    EXECUTE
    must not be SELECT
    (Q3) Mix of upper case and lower case in TABSCHEMA = 'Employee' worked?
    SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'Employee';

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Here is how I do it:

    Code:
    DECLARE V_COUNT INTEGER;
    DECLARE CRS_COUNT CURSOR FOR SQLSTMT1;
    
    SET sqlStmt = 'SELECT COUNT(*) FROM GWIO.'||table_name;
    PREPARE SQLSTMT1 FROM sqlStmt;
    OPEN CRS_COUNT;
    FETCH CRS_COUNT INTO V_COUNT;
    CLOSE CRS_COUNT;
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

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
  •