Skip to content

Instantly share code, notes, and snippets.

@forstie
Created November 24, 2019 18:37
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save forstie/bacc513cbcd960fee6511a9455bf68b0 to your computer and use it in GitHub Desktop.
With the latest Db2 PTF Groups for IBM i 7.3 and 7.4, you can now identify and fix those *PGM and *SRVPGM's that use SQL and were built incorrectly. This example finds those cases where *OWNER will be used for static SQL, but *USER will be used for dynamic SQL. The procedure swaps the dynamic user profile setting to *OWNER. This utility approach…
--
-- description: Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select user_profile, dynamic_user_profile, program_schema, program_name, program_type,
module_name, program_owner, program_creator, creation_timestamp, default_schema,
"ISOLATION", concurrentaccessresolution, number_statements, program_used_size,
number_compressions, statement_contention_count, original_source_file,
original_source_file_ccsid, routine_type, routine_body, function_origin,
function_type, number_external_routines, extended_indicator, c_nul_required,
naming, target_release, earliest_possible_release, rdb, consistency_token,
allow_copy_data, close_sql_cursor, lob_fetch_optimization, decimal_point,
sql_string_delimiter, date_format, date_separator, time_format, time_separator,
dynamic_default_schema, current_rules, allow_block, delay_prepare, user_profile,
dynamic_user_profile, sort_sequence, language_identifier, sort_sequence_schema,
sort_sequence_name, rdb_connection_method, decresult_maximum_precision,
decresult_maximum_scale, decresult_minimum_divide_scale, decfloat_rounding_mode,
decfloat_warning, sqlpath, dbgview, dbgkey, last_used_timestamp, days_used_count,
last_reset_timestamp, system_program_name, system_program_schema, iasp_number,
system_time_sensitive
from qsys2.sysprogramstat
where system_program_schema = 'SCOTTF'
and dynamic_user_profile = '*USER' and program_type in ('*PGM', '*SRVPGM')
and ((user_profile = '*OWNER')
or (user_profile = '*NAMING'
and naming = '*SQL'))
order by program_name;
stop;
--
-- description: Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select qsys2.delimit_name(system_program_schema) as lib,
qsys2.delimit_name(system_program_name) as pgm,
program_type as type
from qsys2.sysprogramstat
where system_program_schema = 'SCOTTF'
and dynamic_user_profile = '*USER'
and program_type in ('*PGM', '*SRVPGM')
and ((user_profile = '*OWNER')
or (user_profile = '*NAMING'
and naming = '*SQL'))
order by program_name;
stop;
--
--
-- description: Find misaligned use of SQL's Dynamic User Profile and swap the setting
-- minvrm: V7R3M0
--
CREATE OR REPLACE PROCEDURE coolstuff.swap_dynusrprf(target_library varchar(10))
BEGIN
DECLARE v_eof INTEGER DEFAULT 0;
DECLARE Prepare_Attributes VARCHAR(100) default ' ';
declare sql_statement_text clob(10K) ccsid 37;
declare v_lib varchar(10) ccsid 37;
declare v_pgm varchar(10) ccsid 37;
declare v_type varchar(7) ccsid 37;
DECLARE obj_cursor CURSOR FOR
select qsys2.delimit_name(system_program_schema) as lib,
qsys2.delimit_name(system_program_name) as pgm,
program_type as type
from qsys2.sysprogramstat
where program_schema = target_library
and dynamic_user_profile = '*USER'
and program_type in ('*PGM', '*SRVPGM')
and ((user_profile = '*OWNER')
or (user_profile = '*NAMING'
and naming = '*SQL'))
order by program_name;
OPEN obj_cursor;
loop_through_data: BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
BEGIN
SET v_eof = 1;
END;
l3 : LOOP
FETCH obj_cursor INTO v_lib, v_pgm, v_type;
IF (v_eof = 1)
THEN
LEAVE l3;
END IF;
-- Swap the SQL DYNUSRPRF setting
CALL QSYS2.SWAP_DYNUSRPRF(v_lib, v_pgm, v_type);
call systools.lprintf('DYNUSRPRF swapped for: ' concat v_lib concat '/' concat v_pgm concat ' ' concat v_type);
END LOOP; /* L3 */
CLOSE obj_cursor;
END loop_through_data;
END;
stop;
-- Process all the misaligned SQL DynUsrPrf settings for a specific library
call coolstuff.swap_dynusrprf('SCOTTF');
@NielsLiisberg
Copy link

This is GREAT!!

I can see you are using cursors a lot in your examples. And I am wondering why you are not using the
FOR select .. DO .. END FOR;
( my favorite btw) ..

What am i missing?

@forstie
Copy link
Author

forstie commented Jun 28, 2023

Finally, this little Gist got some love. Thank you.
As far as "why" did I use LOOP?
I'm not sure. Sometimes I like to vary the style of SQL, just for fun.
My fave looping structure is WHILE.
I recognize that FOR is likely the most popular.

@NielsLiisberg
Copy link

NielsLiisberg commented Jun 28, 2023 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment