Created
November 24, 2019 18:37
-
-
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…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- 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
commented
Jun 28, 2023
via email
Always a lot of love !! There's nothing you can't kiss aways :)
OK - I didn't miss anything. Except watching you having fun with
coding styles - now I can relax ;)
…On Wed, Jun 28, 2023 at 2:45 PM Scott Forstie ***@***.***> wrote:
***@***.**** commented on this gist.
------------------------------
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.
—
Reply to this email directly, view it on GitHub
<https://gist.github.com/forstie/bacc513cbcd960fee6511a9455bf68b0#gistcomment-4613006>
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAVIPHSARIZFXEN4QDX3R3LXNQRPBBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVA4TSNRVGIYDQMVHORZGSZ3HMVZKMY3SMVQXIZI>
.
You are receiving this email because you commented on the thread.
Triage notifications on the go with GitHub Mobile for iOS
<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675>
or Android
<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>
.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment