Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: procedure to generate table count into column

    Hello

    I have a table that has two columns. One column "Table Name" has a list of table names and the other column "Table Count" is for record count of those table names.


    I need to generate a query to set the record count of the tables(values in table name column) in the Table Count column.

    Can I write a procedure and set the values by calling the procedure or can I do it any other. If so how should the query or procedure be like ?

    Thanks in Advance

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Not a direct answer: how about doing a RUNSTATS and then looking at the cardinality in the system catalog views?

    Regarding the actual question: you need dynamic SQL in the procedure to build the SELECT COUNT(*) queries against the different tables, then execute this query and fetch the result. That's straight-forward SQL. Give it a try and if you get stuck, we can work on the details.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2008
    Posts
    3
    I am having problem in executing this SQL. Can you please let me know how to write dynamic sql for this procedure as I have never done this before

  4. #4
    Join Date
    Jul 2008
    Posts
    3
    My procedure.. but having problems in creation

    CREATE PROCEDURE DB.MYPROC(IN TABLE_NAME VARCHAR(50))
    LANGUAGE SQL
    DECLARE LV_SQLSTR VARCHAR(200);
    BEGIN NOT ATOMIC
    SET LV_SQLSTR = UPDATE DB.MYTABLE SET STG_SOURCE_COUNT = (SELECT COUNT(*) from DB.+TABLE_NAME+) WHERE STG_SOURCE = +TABLE_NAME+;
    EXEC SQL ECECUTE :LV_SQLSTR;
    END EXEC
    END

    Please do let me know the correc tprocedure and execution.. Thanks in advance

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the error message you get?

    I few basic SQL issues you have are:
    • You didn't post your error message.
    • You are missing single quotes around the UPDATE statement.
    • String concatenation is done in SQL using || or the CONCAT operator - and not with '+'.
    • Remove the EXEC SQL and END EXEC. Those are only needed when you embed SQL statements in a host language (like COBOL or C) and a precompiler transforms them to regular statements.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I guess it is nice to have CPU resources to burn. Most of us are trying to elimnate the use of CPU by improving/removing tasks on our database systems. The easiest way as was mentioned is to run the runstats utiltiy and query the catalog for this information. This way you are benefited with up to date statistics for the optimizer and you get your table counts as you are wanting.
    If you are bent on having up to the minute counts for some reason, you may want to consider an Insert trigger on each table. Something along the lines of when a row is inserted, then update your count table where the table name equals the table being inserted to and the count is now the row count column + 1.
    Dave

Posting Permissions

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