SQL - info about objects

By kimot, 24 December, 2022

Basic object info

SELECT objname, 
       objlib, 
       objtype, 
       objattribute, 
       objowner, 
       objtext, 
       objsize, 
       objcreated, 
       change_timestamp, 
       last_used_timestamp                                                 
FROM TABLE(QSYS2.OBJECT_STATISTICS('SOME_LIB','*ALL')) A  
ORDER BY change_timestamp;

Objects backup/restore information

SELECT objname, 
       objlib, 
       objtype, 
       objattribute, 
       objowner, 
       objtext, 
       objsize, 
       objcreated,  
       save_command, 
       save_device, 
       save_volume, 
       save_label, 
       save_sequence_number, 
       save_file_name, 
       save_file_library, 
       last_save_size, 
       save_timestamp, 
       restore_timestamp, 
       save_while_active_timestamp                                                  
FROM TABLE(QSYS2.OBJECT_STATISTICS('SOME_LIB','*ALL')) A  
ORDER BY change_timestamp;

Objects source information

SELECT objname, 
       objlib, 
       objtype, 
       objattribute, 
       objowner, 
       objtext, 
       objsize, 
       objcreated,  
       source_file, 
       source_library, 
       source_member, 
       created_system, 
       created_system_version                                                  
FROM TABLE(QSYS2.OBJECT_STATISTICS('SOME_LIB','*ALL')) A  
ORDER BY CHANGE_TIMESTAMP;

Objects journalling information

SELECT objname, 
       objlib, 
       objtype, 
       objattribute, 
       objowner, 
       objtext, 
       objsize, 
       objcreated,
       journaled, 
       journal_name, 
       joural_library, 
       journal_images, 
       omit_journal_entry, 
       remote_journal_filter, 
       journal_start_timestamp, 
       apply_starting_receiver, 
       apply_starting_receiver_library                                                  
FROM TABLE(QSYS2.OBJECT_STATISTICS('SOME_LIB','*ALL')) A  
ORDER BY CHANGE_TIMESTAMP;

Info about particular object

SELECT * 
FROM TABLE(QSYS2.OBJECT_STATISTICS('library_name', 'object_type_list', OBJECT_NAME => 'object_name')) A;

Info about number of rows or insert operations

SELECT table_schema, 
       table_name, 
       number_rows, 
       data_size, 
       insert_operations
FROM qsys2.systablestat 
WHERE SYSTEM_TABLE_SCHEMA = 'library_name' AND TABLE_NAME='file_name';

 

Tags