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

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.

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

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

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.

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