Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Subtle options in how objects are journaled can have a big impact on performance.
--
--
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment