Last active
October 1, 2022 18:22
-
-
Save forstie/9159d1ac9bcc9703514497e32a0f29d2 to your computer and use it in GitHub Desktop.
This gist combines several IBM i (SQL) Services to produce an easy to consume UDTF. Pass in a user name and you'll get back all the QSYS and IFS objects they own, ordered by size descending.
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
create or replace function systools.objects_owned( | |
user_name varchar(10) FOR SBCS DATA) | |
RETURNS TABLE ( | |
object DBCLOB(16M) CCSID 1200, | |
object_type varchar(10) for sbcs data, | |
size bigint, | |
sizeform varchar(50) FOR SBCS DATA, | |
created timestamp, | |
changed timestamp, | |
used timestamp) | |
SPECIFIC SYSTOOLS.objects_owned | |
NOT DETERMINISTIC | |
no external action | |
not fenced | |
MODIFIES SQL DATA | |
CALLED ON NULL INPUT | |
SYSTEM_TIME SENSITIVE NO | |
SET OPTION ALWBLK = *ALLREAD , | |
ALWCPYDTA = *OPTIMIZE , | |
COMMIT = *NONE , | |
DECRESULT = (31, 31, 00) , | |
DFTRDBCOL = QSYS2 , | |
DLYPRP = *NO , | |
DYNDFTCOL = *NO , | |
DYNUSRPRF = *USER , | |
MONITOR = *SYSTEM , | |
SRTSEQ = *HEX , | |
USRPRF = *USER | |
-- category: IBM i Services | |
-- description: Objects owned by a user | |
-- minvrm: V7R3M0 | |
-- | |
return | |
with qsysobjs (lib, obj, type) as ( | |
select object_library, object_name, object_type | |
from table ( | |
qsys2.object_ownership(user_name) | |
) a | |
where path_name is null | |
), | |
qsysobjs_with_size (object, type, size, created, changed, used) as ( | |
select lib concat '/' concat obj, type, objsize, | |
objcreated, change_timestamp, last_used_timestamp | |
from qsysobjs q, lateral ( | |
select objsize, objcreated, change_timestamp, last_used_timestamp | |
from table ( | |
qsys2.object_statistics(lib, type, obj) | |
) | |
) z | |
), | |
ifsobjs (path, type) as ( | |
select path_name, object_type | |
from table ( | |
qsys2.object_ownership(user_name) | |
) a | |
where path_name is not null and object_type <> '*DIR' | |
), | |
ifsobjs_with_size (object, type, size, created, changed, used) as ( | |
select path, type, data_size, create_timestamp, change_timestamp, | |
last_used_timestamp | |
from ifsobjs i, lateral ( | |
select data_size, create_timestamp, | |
data_change_timestamp as change_timestamp, last_used_timestamp | |
from table ( | |
qsys2.ifs_object_statistics( | |
start_path_name => path, subtree_directories => 'NO') | |
) | |
) z | |
) | |
select object, type, size, ltrim(varchar_format(size, '999G999G999G999G999G999G999G999')) size, created, | |
changed, used | |
from qsysobjs_with_size | |
union all | |
select object, type, size, ltrim(varchar_format(size, '999G999G999G999G999G999G999G999')) size, created, | |
changed, used | |
from ifsobjs_with_size; |
Genial, gracias Scott !!!
Often needed! Great
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Cool 😎 .... actually super cool