Optimize local journals

--
-- Search for journals that can be easily improved
--
-- Resources:
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqviewjournalinfo.htm
-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf 
--
select journal_library, journal_name, receiver_maximum_size, remove_internal_entries, asp_number,
       journal_aspgrp, attached_journal_receiver_name, attached_journal_receiver_library,
       message_queue, message_queue_library, delete_receiver_option, delete_receiver_delay,
       journal_type, journal_state, number_journal_receivers, total_size_journal_receivers,
       number_remote_journals, redirected_receiver_library, maximum_remote_journals_entries_behind,
       maximum_remote_journals_time_behind, maximum_remote_journals_retransmissions, journal_text,
       manage_receiver_option, manage_receiver_delay, remove_fixed_length_detail,
       receiver_maximum_size, minimize_esd_for_data_areas, minimize_esd_for_files, journal_cache,
       fixed_length_data_includes_job_name, fixed_length_data_includes_user_name,
       fixed_length_data_includes_program_name, fixed_length_data_includes_program_library,
       fixed_length_data_includes_system_sequence_number, fixed_length_data_includes_remote_address,
       fixed_length_data_includes_thread_id, fixed_length_data_includes_logical_unit_of_work_id,
       fixed_length_data_includes_transaction_id, journaled_object_limit, journaled_objects,
       journaled_files, journaled_members, journaled_data_areas, journaled_data_queues,
       journaled_ifs_objects, journaled_access_paths, journaled_commitment_definitions,
       journaled_libraries, journal_recovery_count, remote_journal_type, journal_delivery_mode,
       local_journal_name, local_journal_library, local_journal_system, local_journal_aspgrp,
       source_journal_name, source_journal_library, source_journal_system, source_journal_aspgrp,
       local_receiver_system, source_receiver_system, activation_time, estimated_time_behind,
       maximum_time_behind, maximum_behind_timestamp, journal_entry_filtering
  from qsys2.journal_info ji
  where journal_type = '*LOCAL'
        and (remove_internal_entries = 'NO'
          or receiver_maximum_size not in ('*MAXOPT2', '*MAXOPT3'))
  order by journal_library, journal_name;
  
stop;


-- Find any journals not using at least *MAXOPT2 or *RMVINTENT
--
-- *SYSDFT == Specifying this value is currently equivalent to specifying *MAXOPT2 and *RMVINTENT.
--
begin
  declare v_journal_name, v_journal_library varchar(10) ccsid 37;
  declare v_eof integer default 0;
  declare v_minfixlen varchar(3) ccsid 37;
  declare v_rcvsizopt varchar(10) ccsid 37;
  declare new_rcvsizopt varchar(100) ccsid 37;
  declare chgjrn_command varchar(1000) ccsid 37;
  
  declare journal_query_text varchar(1000)  ccsid 37 default
    'SELECT qsys2.delimit_name(Journal_Library), qsys2.delimit_name(Journal_Name), 
       receiver_maximum_size, MINFIXLEN 
       FROM QSYS2.JOURNAL_INFO 
         where journal_type = ''*LOCAL''
          and (remove_internal_entries = ''NO''
               or receiver_maximum_size not in (''*MAXOPT2'', ''*MAXOPT3''))
          order by journal_library, journal_name';
  declare older_journals cursor for older_journals_query;
  declare continue handler for sqlstate '02000' set v_eof = 1;
  prepare older_journals_query from journal_query_text;
  open older_journals;
  l1: loop
    fetch older_journals into v_journal_library, v_journal_name, v_rcvsizopt, v_minfixlen;
    if (v_eof = 1) then
      leave l1;
    end if;
    -- a) The size of the attached receivers is reduced by automatic removal of the internal system entries.
    set new_rcvsizopt = ' RCVSIZOPT(*RMVINTENT ';
    -- b) If not using *MAXOPT2 or *MAXOPT3, changed to *MAXOPT2
    if (v_rcvsizopt = '*MAXOPT3' or v_rcvsizopt = '*MAXOPT2') then
      set new_rcvsizopt = new_rcvsizopt concat v_rcvsizopt;
    else set new_rcvsizopt = new_rcvsizopt concat '*MAXOPT2';
    end if;
    -- c) Keep minfixlen setting
    if (v_minfixlen = 'YES') then
      set new_rcvsizopt = new_rcvsizopt concat ' *MINFIXLEN)';
    else set new_rcvsizopt = new_rcvsizopt concat ')';
    end if;
    set chgjrn_command = 'CALL QSYS2.QCMDEXC(''QSYS/CHGJRN JRN(' concat
      v_journal_library concat '/' concat v_journal_name concat
      ') JRNRCV(*GEN) ' concat new_rcvsizopt concat ' '')';
    -- 
    -- 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)
    -- 
    call systools.lprintf(chgjrn_command);
    execute immediate chgjrn_command;
  end loop;
  close older_journals;
end;