Instantly share code, notes, and snippets.
Last active
September 22, 2025 18:01
-
Star
3
(3)
You must be signed in to star a gist -
Fork
0
(0)
You must be signed in to fork a gist
-
-
Save forstie/013fba6463ce6dc4a422f6222e19a25a to your computer and use it in GitHub Desktop.
Seeing programs that are configured to use adopted authority.sql
This file contains hidden or 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
| -- | |
| -- Subject: Seeing programs that are configured to use adopted authority | |
| -- Author: Scott Forstie | |
| -- Date : January, 2025 | |
| -- | |
| -- The request... | |
| -- I don’t find any examples for reporting on adopted authority. | |
| -- Specifically, we would like to know what pgms in non-IBM libraries use adopted authority. | |
| -- | |
| -- Features Used : This Gist uses object_statistics, object_privileges, and program_info | |
| -- | |
| -- | |
| -- Resources: | |
| -- https://www.ibm.com/docs/en/i/7.5?topic=words-reserved-schema-names | |
| -- https://www.ibm.com/docs/en/i/7.5?topic=services-object-statistics-table-function | |
| -- https://www.ibm.com/docs/en/i/7.5?topic=services-program-info-view | |
| -- | |
| stop; | |
| -- | |
| -- Which libraries are considered non-user libraries? (not the best solution) | |
| -- | |
| -- Note that Db2 for i libraries such as QSYS2, SYSIBM, SYSIBMADM, etc are not included in this non-user library listing | |
| -- In other words.... *ALLUSR returns some libraries that would be considered... IBM libraries | |
| -- | |
| SELECT a.objname AS ibm_library, a.* | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) a | |
| LEFT EXCEPTION JOIN TABLE ( | |
| qsys2.object_statistics('*ALLUSR', '*LIB') | |
| ) b | |
| ON a.objname = b.objname | |
| order by a.objname; | |
| stop; | |
| -- | |
| -- Which libraries are considered non-user libraries? (better solution) | |
| -- | |
| SELECT a.objname AS ibm_library, a.* | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) a | |
| WHERE a.objname LIKE 'Q%' OR a.objname LIKE 'SYS%'; | |
| stop; | |
| -- | |
| -- Who defined the non-user libraries? | |
| -- | |
| -- Note: Ideally, we'd see only QLPINSTALL, *IBM, QSYS, QSECOFR, | |
| -- and other Qxxxx operating system names here | |
| -- | |
| SELECT objdefiner, count(*) as library_count | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) | |
| WHERE objname LIKE 'Q%' or objname LIKE 'SYS%' | |
| group by objdefiner | |
| order by library_count desc; | |
| stop; | |
| -- | |
| -- Who owns the non-user libraries? | |
| -- | |
| -- Note: Ideally, we'd see only QLPINSTALL, *IBM, QSYS, QSECOFR, | |
| -- and other Qxxxx operating system names here | |
| -- | |
| SELECT objowner, count(*) as library_count | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) | |
| WHERE objname LIKE 'Q%' or objname LIKE 'SYS%' | |
| group by objowner | |
| order by library_count desc; | |
| stop; | |
| -- | |
| -- What are the user library names? | |
| -- | |
| SELECT a.objname AS lib_name, a.* | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) a | |
| WHERE a.objname not LIKE 'Q%' and a.objname not LIKE 'SYS%'; | |
| stop; | |
| -- | |
| -- Who defined the user libraries? | |
| -- | |
| SELECT objdefiner, count(*) as library_count | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) | |
| WHERE objname not LIKE 'Q%' and objname not LIKE 'SYS%' | |
| group by objdefiner | |
| order by library_count desc; | |
| stop; | |
| -- | |
| -- Where is adopted authority configured to be used, | |
| -- across all user libraries? | |
| -- | |
| WITH user_libs (lib_name) AS ( | |
| SELECT objname | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) | |
| WHERE objname NOT LIKE 'Q%' | |
| AND objname NOT LIKE 'SYS%' | |
| ) | |
| SELECT PROGRAM_LIBRARY, PROGRAM_NAME, OBJECT_TYPE, PROGRAM_TYPE, | |
| USE_ADOPTED_AUTHORITY, PROGRAM_OWNER, USER_PROFILE | |
| FROM user_libs, LATERAL ( | |
| SELECT * | |
| FROM qsys2.program_info | |
| WHERE PROGRAM_LIBRARY = lib_name | |
| AND user_profile = '*OWNER' | |
| ) | |
| ORDER BY PROGRAM_LIBRARY, PROGRAM_NAME; | |
| stop; | |
| -- | |
| -- Or if you prefer classic join over lateral join... | |
| -- | |
| WITH user_libs (lib_name) AS ( | |
| SELECT objname | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) | |
| WHERE objname NOT LIKE 'Q%' | |
| AND objname NOT LIKE 'SYS%' | |
| ) | |
| SELECT PROGRAM_LIBRARY, PROGRAM_NAME, OBJECT_TYPE, PROGRAM_TYPE, | |
| USE_ADOPTED_AUTHORITY, PROGRAM_OWNER, USER_PROFILE | |
| FROM user_libs, qsys2.program_info | |
| WHERE PROGRAM_LIBRARY = lib_name | |
| AND user_profile = '*OWNER' | |
| ORDER BY PROGRAM_LIBRARY, PROGRAM_NAME; | |
| stop; | |
| -- | |
| -- Where is adopted authority configured to be used, | |
| -- across all user libraries, where the library and | |
| -- program are configured to have *PUBLIC *USE? | |
| -- | |
| WITH user_libs (lib_name) AS ( | |
| SELECT objname | |
| FROM TABLE ( | |
| qsys2.object_statistics('*ALL', '*LIB') | |
| ) a | |
| INNER JOIN qsys2.object_privileges b | |
| ON 'QSYS' = b.SYSTEM_OBJECT_SCHEMA | |
| AND object_name = b.SYSTEM_OBJECT_NAME | |
| AND '*LIB' = b.object_type | |
| WHERE objname NOT LIKE 'Q%' | |
| AND objname NOT LIKE 'SYS%' | |
| -- *PUBLIC has *USE (or higher) privilege to the library | |
| AND b.user_name = '*PUBLIC' | |
| AND object_operational = 'YES' | |
| AND data_read = 'YES' | |
| AND data_execute = 'YES' | |
| ), | |
| adopting_pgms (lib_name, pgm_name, obj_type) AS ( | |
| SELECT PROGRAM_LIBRARY, PROGRAM_NAME, OBJECT_TYPE | |
| FROM user_libs, LATERAL ( | |
| SELECT * | |
| FROM qsys2.program_info | |
| WHERE (PROGRAM_LIBRARY = lib_name | |
| AND user_profile = '*OWNER') | |
| ) | |
| ORDER BY PROGRAM_LIBRARY, PROGRAM_NAME) | |
| SELECT lib_name, pgm_name, obj_type, b.* | |
| FROM adopting_pgms a | |
| INNER JOIN qsys2.object_privileges b | |
| ON a.lib_name = b.SYSTEM_OBJECT_SCHEMA | |
| AND a.pgm_name = b.SYSTEM_OBJECT_NAME | |
| AND a.obj_type = b.object_type | |
| -- *PUBLIC has *USE (or higher) privilege to the program | |
| WHERE b.user_name = '*PUBLIC' | |
| AND object_operational = 'YES' | |
| AND data_read = 'YES' | |
| AND data_execute = 'YES'; | |
| stop; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi Scott,
thanks a lot for the precious SQL code!
I have a test LPAR where i have only 26 programs that use adopted authority with *PUBLIC use but i'm getting 3120 records that are mostly duplicated.
Adding some DISTINCT fix the problem but then the SQL runs very slowly.
Is this "my" problem?
Thanks.