With Db2 PTF Group SF99703 level 22 and Db2 PTF Group SF99704 level 10 (aka TR9 and TR3 timed enhancements), the LIBRARY_INFO UDTF has optional input paramters to provide better performing queries for library specific questions.

Library sizes and more.sql

--  category:  IBM i Services
--  description:  Librarian -  Library Info 
--  minvrm: V7R3M0
--

create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default
'create or replace table coolstuff.library_sizes
      (library_name, schema_name, 
      
       -- qsys2.library_info() columns
       library_size, library_size_formatted, 
       object_count, library_size_complete, library_type, text_description,
       iasp_name, iasp_number, create_authority, object_audit_create, journaled,
       journal_library, journal_name, inherit_journaling, journal_start_timestamp,
       apply_starting_receiver_library, apply_starting_receiver,
       apply_starting_receiver_asp,
       
       -- qsys2.object_statistics() columns
       objowner, objdefiner, objcreated, objsize, objtext, objlongname,
       change_timestamp, last_used_timestamp, last_used_object, days_used_count, last_reset_timestamp,
       save_timestamp, restore_timestamp, save_while_active_timestamp, 
       user_changed, source_file, source_library, source_member,
       source_timestamp, created_system, created_system_version, licensed_program,
       licensed_program_version, compiler, compiler_version, object_control_level,
       ptf_number, apar_id, user_defined_attribute, allow_change_by_program,
       changed_by_program, compressed, primary_group, storage_freed,
       associated_space_size, optimum_space_alignment, overflow_storage, object_domain,
       object_audit, object_signed, system_trusted_source, multiple_signatures,
       save_command, save_device, save_file_name, save_file_library, save_volume, save_label,
       save_sequence_number, last_save_size, journal_images, omit_journal_entry, remote_journal_filter, 
       authority_collection_value
       )
  as
      (select objname as lib, objlongname as schema, library_size,
              varchar_format(library_size, ''999G999G999G999G999G999G999G999G999G999'')
                as formatted_size, object_count, library_size_complete, library_type, text_description,
       b.iasp_name, b.iasp_number, create_authority, object_audit_create, a.journaled,
       b.journal_library, b.journal_name, inherit_journaling, b.journal_start_timestamp,
       b.apply_starting_receiver_library, b.apply_starting_receiver,
       b.apply_starting_receiver_asp,
              objowner, objdefiner, objcreated, objsize, objtext, objlongname,
       change_timestamp, last_used_timestamp, last_used_object, days_used_count, last_reset_timestamp,
       save_timestamp, restore_timestamp, save_while_active_timestamp, 
       user_changed, source_file, source_library, source_member,
       source_timestamp, created_system, created_system_version, licensed_program,
       licensed_program_version, compiler, compiler_version, object_control_level,
       ptf_number, apar_id, user_defined_attribute, allow_change_by_program,
       changed_by_program, compressed, primary_group, storage_freed,
       associated_space_size, optimum_space_alignment, overflow_storage, object_domain,
       object_audit, object_signed, system_trusted_source, multiple_signatures,
       save_command, save_device, save_file_name, save_file_library, save_volume, save_label,
       save_sequence_number, last_save_size, journal_images, omit_journal_entry, remote_journal_filter, 
       authority_collection_value
          from table (
                 qsys2.object_statistics(''*ALLUSR'', ''*LIB'')
               ) as a, lateral (
                 select *
                   from table (
                       qsys2.library_info(library_name => a.objname,
                                          ignore_errors => ''YES'',
                                          detailed_info => ''LIBRARY_SIZE'')
                     )
               ) b)
      with data   on replace delete rows';
stop;
  
cl:SBMJOB CMD(RUNSQL SQL('begin execute immediate coolstuff.library_report_stmt; end') commit(*NONE)) JOB(LIBSIZES);
stop;

--
-- jobs submitted from this job
--
select *
  from table (
      qsys2.job_info(job_submitter_filter => '*JOB', job_user_filter => '*ALL')
    );

-- once the job ends, it won't be returned by job_info... then you can query the results
select * from coolstuff.library_sizes ls order by library_size desc;
 

Hi,
thank you for doing all of these examples, which I think are invaluable.
I would like to ask something. Is it possible to create a report via SQL Query, which is simlar to rtbdskinf - prtdskinf?

Hi,
You're welcome and thanks for the feedback!

For rtv/prtdskinf, we have not had this request.
Please open an RFE via the ideas portal.
https://ideas.ibm.com/?interaction=search&query=ibm+i

Hi,
I did.
https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3234
Thank you!