Get count of rows for all files in library with SQL

Question

This task has two options that comes to mind:

Option 1: Straighforward approach

1. Execute select table_name from qsys2.systables where table_schema = 'LIBRARY'
2. For each entry of the output execute select count(*) from LIBRARY.TABLENAME
3. Save result and repeat step 2.

Option 2: Dynamic sql statement

1. Try to wrap script result into begin and subsequent execute immediate sql; end; statement.

Unfortunately, with clause in this code cannot be used with begin and I've no idea how to rewrite entire script properly without it.

Can somebody come out with the solution which performance is better than the stated in option 1?

Desired output format:

╔════════════╦═══════╗
║ TABLE_NAME ║ COUNT ║
╠════════════╬═══════╣
║ a1         ║   432 ║
║ a2         ║     9 ║
║ a3         ║     0 ║
╚════════════╩═══════╝

---

UPD

Based on answer I've come to this procedure script, which is not working, however:

CREATE PROCEDURE LIB/CNT                                          
  LANGUAGE SQL                                                    
BEGIN                                                             
    CREATE TYPE CHARARRAY AS VARCHAR(10) ARRAY[500];              
    DECLARE NAMES CHARARRAY;                                      
    DECLARE i INTEGER;                                            
    SET i = 1;                                                    
    SET names = ARRAY[SELECT table_name FROM qsys2.systables WHERE
table_schema = 'LIBRARY'];                                        
    SET numNames = CARDINALITY(names);                            
    WHILE i <= numNames                                           
        SET stmt = 'SELECT ' || names[i] || ' AS TABLE_NAME,      
COUNT(*) FROM ' || names[i];                                      
        PREPARE s1 FROM stmt;                                     
        EXECUTE s1;                                               
    END WHILE;                                                    
END;

CREATE TYPE reference.

Error message: Token NAMES was not valid. Valid tokens: GLOBAL.

Adding global before names gives Token NAMES was not valid. Valid tokens: TEMPORARY.

Answer 1

You don't need to do anything, the information is already collected by the system...

SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME
      , NUMBER_ROWS, TABLE_SCHEMA, TABLE_NAME 
FROM systablestat 
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB';

For future reference, the standard catalogs have been around for awhile.
DB2 for i catalog views

Also the DB2 for i team continues to add "services" to make information available via SQL
DB2 for i - Services