SQL Dynamic Table Count Select Assistance

[Chris]

Can't seem to figure out how to do this and have been reading for some
time now......

I want to select a row count from a table name in SYSTABLES.

This statement does not return what I needed, but can help explain what
I'm looking for. I want the results to be the Row Count from a Table
Name out of the Sub-Select.

Select Count(*) from
(Select SYSTEM_TABLE_NAME where SYSTEM_TABLE_NAME like ('MYTABLES%)
and
SYSTEM_TABLE_SCHEMA = 'MYSCHEMA')

Any help would be appreciated.

Tx

Chris

[Knut Stolze]

Chris wrote:

Can't seem to figure out how to do this and have been reading for some
time now......

I want to select a row count from a table name in SYSTABLES.

Don't use the SYSIBM.SYS* tables. They are not documented and, thus, not
supported.

This statement does not return what I needed, but can help explain what
I'm looking for. I want the results to be the Row Count from a Table
Name out of the Sub-Select.

Select Count(*) from
(Select SYSTEM_TABLE_NAME where SYSTEM_TABLE_NAME like ('MYTABLES%)
and
SYSTEM_TABLE_SCHEMA = 'MYSCHEMA')

Any help would be appreciated.

I don't quite understand what you want to do. Do you want to get the number
of tuples of all tables starting with the name MYTABLES in schema MYSCHEMA?
If so, your above query won't work, of course. The above query (even if it
would be syntactically correct and queries SYSCAT.TABLES), scans the
SYSCAT.TABLES view, filters out all rows that satisfy your search criteria
and then counts how many rows that were.

What you could do if you want to get the cardinality of the tables is to
query the CARD column in SYSCAT.TABLES:

SELECT tabname, card
FROM syscat.tables
WHERE tabname LIKE 'MYTABLES%' AND
tabschema = 'MYSCHEMA'

Note that CARD is population when you collect statistics on the respective
table. Thus, the value is not guaranteed to be correct if you didn't
collect statistics or if the stats are out of date.

If you want to count the rows in the tables (not relying on the statistics),
you will have to query those tables directly. For that, you can first scan
SYSCAT.TABLES to identify the qualifying tables, then construct a query
against each of the tables and execute those queries with dynamic SQL.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[jefftyzzer]

If you're looking for list of all tables in a schema and their
cardinalities, you'll probably need to do this task within an SP. While
you're able to declare a cursor in inline SQL PL, you can't issue an
EXECUTE IMMEDIATE (for the dynamic SQL), nor would you be able to see
the result set (probably a bigger show-stopper :-).

What I'd do is declare an SP with DYNAMIC RESULT SETS 1, declare a
global temporary table (DGTT) with two columns--table name and count,
create a cursor against SYSCAT.TABLES, then loop through this table
list and execute a dynamic SQL query that does a SELECT COUNT(*)
against the table the cursor is pointing to. I'd then insert the table
name and count into the temp table. When the loop is finished, the last
thing I'd do is SELECT * from the DGTT.

--Jeff

Chris wrote:

Can't seem to figure out how to do this and have been reading for some
time now......

I want to select a row count from a table name in SYSTABLES.

This statement does not return what I needed, but can help explain what
I'm looking for. I want the results to be the Row Count from a Table
Name out of the Sub-Select.

Select Count(*) from
(Select SYSTEM_TABLE_NAME where SYSTEM_TABLE_NAME like ('MYTABLES%)
and
SYSTEM_TABLE_SCHEMA = 'MYSCHEMA')

Any help would be appreciated.

Tx

Chris

[Chris]

Tx, I figued I needed a SP.

Chris

[Serge Rielau]

DB2 only keep statistical information for CARDINALITY in SYSSTAT.TABLES.
If you want an accurate count you will need to query the table itself.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/