AS/400 commands for getting row counts for all tables in a library

first we create a table that is going to house the results.

create table jd7333.tablecounts (tablename char(10) not null, rowcount integer) ;

Now we execute this command to get the statements that will generate the insert statements and do the counts. 

select distinct  'insert into jd7333.tablecounts (select ' || '''' || table_name || '''' || ', count(1) from LIBNAME.' || table_name || ');'  from qsys2.systables where table_schema = 'LIBNAME' and table_type <> 'L' and not exists (select 1 from jd7333.tablecounts where tablename = table_name);

job done, you can restart it ay anytime and it won’t do them all again.