query to check all SCD tables that have data for specific date It's easy to find all SCD tables through

select 
    * 
from 
    syscat.TABLES t 
    join
    syscat.COLUMNS s
    on t.tabname = s.tabname 
where 
    s.COLNAME = 'EFFECTIVE_DTE' and t.type='T'
    with ur;


problem is how to further apply SQLs on the resut tables sets and check if each contain data for specific date ? say:

select effective_dte, count(*) from TABLE1 group by TABLE1 where ...


is it possible to futher apply the queries to all result tables
You should be able to create a stored procedure that executes your query to get the table names. Then you loop over the results and execute your effective date query for each table individually.

You might even be able to do this without wrapping it into an stored procedure.