Skip to content

Instantly share code, notes, and snippets.

@forstie
Created September 24, 2020 17:43
  • 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/133eba4f794058662fbed75d52668ab7 to your computer and use it in GitHub Desktop.
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.
--
-- 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