Hi,
I have a task to verify that data in tables exists against huge amount of tables (>1000).
This operation should be processed as fast as possible. (Main criteria)
I'm using .Net provider and z/OS v8.

For now I see following decision:
To the server I will send 1 statement that contains several following queries:
SELECT 1 FROM a FETCH FIRST ROW ONLY FOR READ ONLY WITH UR;
SELECT 2 FROM b FETCH FIRST ROW ONLY FOR READ ONLY WITH UR;
....
(To prevent script is too complex error I will split it up by 500)

If you see more productive approaches then you are welcome in this topic.
Thanks in advance.

How about read the catalog views and only find tables that are empty?
Dave

Catalog views don't have such information.
(Correct me if I'm wrong)
There are info about statistics only but it can be not actual.

I think you can get the table count from catalogs provided if your stats are up to date.

If you have statistics collected on the tables, you can query the catalog and find tables without any rows. But you are right that those statistics could be outdated. However, in such a situation you probably have a different problem anyway: if the stats are not up to date, you have a good chance that the optimizer is picking sub-optimal access paths. So it is usually a good idea to make sure the statistics are more or less up to date.

What you could do is this: for all important tables you keep the stats up to date and rely on the stats. For all other tables, you have to query the table itself.