Subtle options in how objects are journaled can have a big impact on performance.

Optimize journaled database files

--
--
-- Find journaled database files that can be adjusted for improved performance
--
-- Resources:
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm
-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf
--
select 'TOYSTORE', objname as file, omit_journal_entry, journal_images, objtype, objowner,
       objdefiner, objcreated, objsize, objtext, objlongname, last_used_timestamp, journaled,
       journal_name, journal_library, journal_images, omit_journal_entry, remote_journal_filter,
       journal_start_timestamp, apply_starting_receiver,
       apply_starting_receiver_library last_used_object, days_used_count, last_reset_timestamp,
       iasp_number, iasp_name, objattribute, objlongschema, text, sql_object_type, objlib,
       change_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_timestamp, restore_timestamp,
       save_while_active_timestamp, save_command, save_device, save_file_name, save_file_library,
       save_volume, save_label, save_sequence_number, last_save_size
  from table (
      qsys2.object_statistics('TOYSTORE', '*FILE')
    )
  where journaled = 'YES'
        and objattribute = 'PF'
        and (omit_journal_entry = '*NONE'
        or journal_images = '*BOTH');
  
  
  stop;

--
-- Process a single, hardcoded library 
-- ===================================
--
-- Find any journals not using at least *MAXOPT2 or *RMVINTENT
--
-- *SYSDFT == Specifying this value is currently equivalent to specifying *MAXOPT2 and *RMVINTENT.
--
--
-- lprintf was added to systools on November 15, 2019 on IBM i 7.3 and IBM i 7.4
--
-- If you aren't on that Db2 PTF Group level, comment this call out or
-- build your own lprintf procedure
-- (steps found here: https://gist.github.com/forstie/314dde2d3e32ef9b7b83495e6d620fff)
--
begin
  declare v_file_name, v_library varchar(10) ccsid 37;
  declare v_eof integer default 0;
  declare v_omit_journal_entry, v_journal_images varchar(10) ccsid 37;
  declare chgjrnobj_command varchar(1000) ccsid 37;
  declare journaled_db_file_query_text varchar(1000) ccsid 37 default
  'select ''TOYSTORE'', qsys2.delimit_name(objname), omit_journal_entry, journal_images
    from table (
      qsys2.object_statistics(''TOYSTORE'', ''*FILE'')
    )
  where journaled = ''YES'' and objattribute = ''PF'' and
  (omit_journal_entry = ''*NONE'' or journal_images = ''*BOTH'') ';
  declare journaled_files_to_improve cursor for journaled_files_to_improve_query;
  declare continue handler for sqlstate '02000' set v_eof = 1;
  prepare journaled_files_to_improve_query from journaled_db_file_query_text;
  open journaled_files_to_improve;
  l1: loop
    fetch journaled_files_to_improve into v_library, v_file_name, v_omit_journal_entry,
                                          v_journal_images;
    if (v_eof = 1) then
      leave l1;
    end if;
    if (v_omit_journal_entry = '*NONE') then
      set chgjrnobj_command = 'CALL QSYS2.QCMDEXC(''qsys/CHGJRNOBJ OBJ((' concat v_library concat '/' concat v_file_name
            concat ' *FILE)) ATR(*OMTJRNE) OMTJRNE(*OPNCLOSYN) '')';
      call systools.lprintf(chgjrnobj_command);
      execute immediate chgjrnobj_command;
    end if;
    if (v_journal_images = '*BOTH') then
      set chgjrnobj_command = 'CALL QSYS2.QCMDEXC(''qsys/CHGJRNOBJ OBJ((' concat v_library concat '/' concat v_file_name
            concat ' *FILE)) ATR(*IMAGES) IMAGES(*AFTER) '')';
      call systools.lprintf(chgjrnobj_command);
      execute immediate chgjrnobj_command;
    end if;
  end loop;
  close journaled_files_to_improve;
end;
  
  stop;

--
-- Process all available user libraries
-- ====================================
--
select a.objname as lib, b.objname as file, b.omit_journal_entry, b.journal_images, b.objtype,
       b.objowner, b.objdefiner, b.objcreated, b.objsize, b.objtext, b.objlongname,
       b.last_used_timestamp, b.last_used_object, b.days_used_count, b.last_reset_timestamp,
       b.iasp_number, b.iasp_name, b.objattribute, b.objlongschema, b.text, b.sql_object_type,
       b.objlib, b.change_timestamp, b.user_changed, b.source_file, b.source_library,
       b.source_member, b.source_timestamp, b.created_system, b.created_system_version,
       b.licensed_program, b.licensed_program_version, b.compiler, b.compiler_version,
       b.object_control_level, b.ptf_number, b.apar_id, b.user_defined_attribute,
       b.allow_change_by_program, b.changed_by_program, b.compressed, b.primary_group,
       b.storage_freed, b.associated_space_size, b.optimum_space_alignment, b.overflow_storage,
       b.object_domain, b.object_audit, b.object_signed, b.system_trusted_source,
       b.multiple_signatures, b.save_timestamp, b.restore_timestamp, b.save_while_active_timestamp,
       b.save_command, b.save_device, b.save_file_name, b.save_file_library, b.save_volume,
       b.save_label, b.save_sequence_number, b.last_save_size, b.journaled, b.journal_name,
       b.journal_library, b.journal_images, b.omit_journal_entry, b.remote_journal_filter,
       b.journal_start_timestamp, b.apply_starting_receiver, b.apply_starting_receiver_library,
       b.authority_collection_value
  from table (
         qsys2.object_statistics('*ALLUSRAVL', '*LIB')
       ) a, lateral (
         select *
           from table (
               qsys2.object_statistics(a.objname, '*FILE')
             )
       ) b
  where b.journaled = 'YES' and a.objname not like 'Q%'
        and b.objattribute = 'PF'
        and (b.omit_journal_entry = '*NONE'
        or b.journal_images = '*BOTH');
  
  
  stop;

--
-- Process all available user libraries
-- ====================================
--
-- Note: This is a powerful example, user beware before executing!
--
begin
  declare v_file_name, v_library varchar(10) ccsid 37;
  declare v_eof integer default 0;
  declare v_omit_journal_entry, v_journal_images varchar(10) ccsid 37;
  declare chgjrnobj_command varchar(1000) ccsid 37;
  declare journaled_db_file_query_text varchar(1000) ccsid 37 default
  'select a.objname as lib, b.objname as file, b.omit_journal_entry, b.journal_images
  from table (
         qsys2.object_statistics(''*ALLUSRAVL'', ''*LIB'')
       ) a, lateral (
         select *
           from table (
               qsys2.object_statistics(a.objname, ''*FILE'')
             )
       ) b  where b.journaled = ''YES'' and a.objname not like ''Q%'' and b.objattribute = ''PF'' and
            (b.omit_journal_entry = ''*NONE'' or b.journal_images = ''*BOTH'') ';
  declare journaled_files_to_improve cursor for journaled_files_to_improve_query;
  declare continue handler for sqlstate '02000' set v_eof = 1;
  prepare journaled_files_to_improve_query from journaled_db_file_query_text;
  open journaled_files_to_improve;
  l1: loop
    fetch journaled_files_to_improve into v_library, v_file_name, v_omit_journal_entry,
                                          v_journal_images;
    if (v_eof = 1) then
      leave l1;
    end if;
    if (v_omit_journal_entry = '*NONE') then
      set chgjrnobj_command = 'CALL QSYS2.QCMDEXC(''qsys/CHGJRNOBJ OBJ((' concat v_library concat '/' concat v_file_name
            concat ' *FILE)) ATR(*OMTJRNE) OMTJRNE(*OPNCLOSYN) '')';
      call systools.lprintf(chgjrnobj_command);
      execute immediate chgjrnobj_command;
    end if;
    if (v_journal_images = '*BOTH') then
      set chgjrnobj_command = 'CALL QSYS2.QCMDEXC(''qsys/CHGJRNOBJ OBJ((' concat v_library concat '/' concat v_file_name
            concat ' *FILE)) ATR(*IMAGES) IMAGES(*AFTER) '')';
      call systools.lprintf(chgjrnobj_command);
      execute immediate chgjrnobj_command;
    end if;
  end loop;
  close journaled_files_to_improve;
end;