Maybe you can use this:
with cte0(table_schema,table_name,rn)
as (select table_schema
,table_name
,row_number() over(order by table_name) as rn
from qsys2.tables
where table_schema='MHDLIB'
)
,cte1(sql,rn)
as (select cast('select '''||table_name||''' as table_name,count(*) as c from '||table_schema||'.'||table_name
as clob) as sql
,rn
from cte0
where rn=1
union all
select a.sql||' union all select '''||b.table_name||''',count(*) from '||b.table_schema||'.'||b.table_name as sql
,b.rn
from cte1 as a
inner join cte0 as b
on b.rn=a.rn+1
)
select sql
from cte1
order by rn desc
fetch first 1 row only
;
This will return one value (sql) that is actually the query built automatically for you.