Created
November 27, 2020 18:15
-
-
Save forstie/c23766b36cc2c38a4849db7fae552366 to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,
I did.
https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3234
Thank you!