Created
September 24, 2020 17:43
Star
You must be signed in to star a gist
Someone asked me how to use SQL to understand who owns the most objects. The following is a progression of queries to shine a light on this topic.
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: Objects owned by QTMHHTP1 | |
-- minvrm: V7R3M0 | |
-- | |
select 'QTMHHTP1' as user, 'IFS' as object_type, count(*) as objects_owned | |
from qsys2.object_ownership | |
where authorization_name = 'QTMHHTP1' and path_name is not null | |
union all | |
select 'QTMHHTP1' as user, 'QSYS' as object_type, count(*) as objects_owned | |
from qsys2.object_ownership | |
where authorization_name = 'QTMHHTP1' and path_name is null; | |
stop; | |
-- | |
-- description: Object ownership - summary by count | |
-- minvrm: V7R3M0 | |
-- | |
select authorization_name as userName, 'IFS' as location, count(*) as objects_owned | |
from qsys2.object_ownership | |
where authorization_name not like 'QSYS%' and path_name is not null | |
group by authorization_name | |
union all | |
select authorization_name as userName, 'QSYS' as location, count(*) as objects_owned | |
from qsys2.object_ownership | |
where authorization_name not like 'QSYS%' and path_name is null | |
group by authorization_name | |
order by 2, 3 desc; | |
stop; | |
-- | |
-- description: Object ownership - Who are the top 10? | |
-- minvrm: V7R3M0 | |
-- | |
select authorization_name, | |
count(*) as objects_owned, | |
row_number() over () as ranking | |
from qsys2.object_ownership | |
where authorization_name not like 'QSYS%' | |
group by authorization_name | |
order by 2 desc | |
limit 10; | |
stop; | |
-- | |
-- description: Object ownership - top 10 by object type | |
-- minvrm: V7R3M0 | |
-- | |
with top10(un, cnt, ranking) as ( | |
select authorization_name, | |
count(*) as objects_owned, | |
row_number() over () as row_num | |
from qsys2.object_ownership | |
where authorization_name not like 'QSYS%' | |
group by authorization_name | |
order by 2 desc | |
limit 10 | |
) | |
select authorization_name as userName, object_type, ranking, count(*) as objects_owned | |
from top10, lateral ( | |
select * from qsys2.object_ownership | |
where authorization_name = un) | |
group by authorization_name, object_type, ranking | |
order by ranking, 4 desc | |
; | |
stop; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment