Created
February 9, 2020 18:31
-
-
Save forstie/8c1a47722104953d083507119a409006 to your computer and use it in GitHub Desktop.
This example takes a previous example and extends it. The idea here is that you want to proactively manage user consumption of storage. For the top storage consumers, return a report that lists their largest objects (either in QSYS or IFS) and provide some contextual detail. This solution includes simple controls to allow the caller to specify t…
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
-- #SQLcandoit #IBMiServices #Db2fori | |
-- ====================================================================== | |
-- | |
-- Establish a UDTF that finds the largest N objects for a specific user | |
-- | |
-- ====================================================================== | |
create or replace function systools.N_largest_objects_owned ( | |
user_name varchar(10) for sbcs data, limit_size bigint default 10 | |
) | |
returns table ( | |
object varchar(10000) for sbcs data, | |
size varchar(50) for sbcs data, | |
created timestamp, | |
changed timestamp, | |
used timestamp | |
) | |
specific systools.N_largest_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 | |
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, size, created, changed, used) as ( | |
select lib concat '/' concat obj concat ' ' concat type as object, 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 in ('*DOC', '*STMF', '*DSTMF', '*USRSPC') | |
), | |
ifsobjs_with_size (object, size, created, changed, used) as ( | |
select path concat ' ' concat 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 | |
), | |
alltogether (object, size, created, changed, used) as ( | |
select object, varchar_format(size, '999G999G999G999G999G999G999G999G999G999G999G999') size, | |
created, changed, used | |
from qsysobjs_with_size | |
union all | |
select object, varchar_format(size, '999G999G999G999G999G999G999G999G999G999G999G999') size, | |
created, changed, used | |
from ifsobjs_with_size | |
) select object, size, created, changed, used | |
from alltogether | |
order by size desc | |
limit limit_size; | |
-- What are the 12 largest objects owned by Tim | |
-- select object, size, created, changed, used | |
-- from table ( | |
-- systools.n_largest_objects_owned('TIMMR', 12) | |
-- ); | |
CREATE OR REPLACE FUNCTION systools.topN ( | |
how_many_users bigint default 10, | |
how_many_objects bigint default 10 | |
) | |
RETURNS TABLE ( | |
user_name varchar(10) for sbcs data, | |
object varchar(10000) for sbcs data, | |
size varchar(50) for sbcs data, | |
created timestamp, | |
changed timestamp, | |
used timestamp | |
) | |
NOT DETERMINISTIC | |
EXTERNAL ACTION | |
MODIFIES SQL DATA | |
NOT FENCED | |
SET OPTION COMMIT = *NONE | |
BEGIN | |
DECLARE local_sqlcode INTEGER; | |
DECLARE local_sqlstate CHAR(5) for sbcs data; | |
DECLARE v_message_text VARCHAR(70) for sbcs data; | |
DECLARE v_user_name varchar(10) for sbcs data; | |
DECLARE v_object varchar(10000) for sbcs data; | |
DECLARE v_size varchar(50) for sbcs data; | |
DECLARE v_created timestamp; | |
DECLARE v_changed timestamp; | |
DECLARE v_used timestamp; | |
DECLARE object_count bigint; | |
DECLARE user_count bigint; | |
declare not_found condition for '02000'; | |
declare at_end integer default 0; | |
DECLARE topN_objects_cursor_stmttext varchar(10000) for sbcs data default | |
'select object, size, created, changed, used | |
from table(systools.N_largest_objects_owned(?, ?))'; | |
DECLARE topN_objects_cursor CURSOR FOR topN_objects_cursor_statement; | |
DECLARE topN_users_cursor CURSOR FOR | |
select a.authorization_name | |
from qsys2.user_storage a | |
inner join qsys2.user_info b | |
on b.user_name = a.authorization_name | |
where a.user_name in (select user_name | |
from qsys2.user_info | |
where user_creator <> '*IBM') | |
group by a.authorization_name, b.text_description, b.accounting_code, | |
b.maximum_allowed_storage | |
order by sum(a.storage_used) desc | |
limit how_many_users; | |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION | |
BEGIN | |
GET DIAGNOSTICS CONDITION 1 | |
local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate; | |
SET v_message_text = 'systools.topN() failed with: ' CONCAT local_sqlcode | |
CONCAT ' AND ' CONCAT local_sqlstate; | |
SIGNAL SQLSTATE 'QPC01' SET MESSAGE_TEXT = v_message_text; | |
END; | |
PREPARE topN_objects_cursor_statement from topN_objects_cursor_stmttext; | |
-- Open a cursor that returns the N user profile names with the largest storage consumption | |
OPEN topN_users_cursor; | |
set user_count = 1; | |
l1: LOOP | |
IF user_count > how_many_users THEN | |
LEAVE l1; | |
END IF; | |
set user_count = user_count + 1; | |
FETCH FROM topN_users_cursor INTO v_user_name; | |
-- Open a cursor which returns the N largest objects for the user | |
OPEN topN_objects_cursor using v_user_name, how_many_objects; | |
SET object_count = 0; | |
SET at_end = 0; | |
w1: while (object_count < how_many_objects) do | |
begin | |
declare continue handler for sqlexception set at_end = 1; | |
declare continue handler for not_found set at_end = 1; | |
SET object_count = object_count + 1; | |
FETCH FROM topN_objects_cursor into v_object, v_size, v_created, v_changed, v_used; | |
if (at_end = 1) then | |
leave w1; | |
end if; | |
PIPE ( | |
v_user_name,v_object, v_size, | |
v_created, v_changed, v_used | |
); | |
end; | |
end while; | |
CLOSE topN_objects_cursor; | |
END LOOP; /* L1 */ | |
CLOSE topN_users_cursor; | |
return; | |
END; | |
stop; | |
--- Show the 5 largest objects for the 7 users who are consuming the most storage | |
select user_name, object, size, created, changed, used | |
from table ( | |
systools.topn(how_many_users => 7, how_many_objects => 5) | |
); | |
stop; | |
-- Alternative... run the report in batch | |
cl: crtlib coolstuff; | |
cl: SBMJOB CMD(RUNSQL SQL(' | |
create or replace table coolstuff.topNreport as ( | |
select user_name, object, size, created, changed, used | |
from table ( | |
systools.topn(how_many_users => 7, how_many_objects => 5) | |
) | |
) with data on replace delete rows') commit(*NONE) ) INLASPGRP(*CURRENT) JOB(TOPNREPORT) JOBMSGQFL(*PRTWRAP) LOG(4 00 *SECLVL); | |
stop; | |
select count(*) isitrunning | |
from table ( | |
qsys2.active_job_info(subsystem_list_filter => 'QBATCH') | |
) | |
where job_name like '%TOPNREPORT%'; | |
select user_name, object, size, created, changed, used | |
from coolstuff.topnreport ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello Scott,
Our system Auxiliary storage is increasing and reaching its limit. Currently we are at 89% consumption. We looked into top consumers but we are not able to figure it out what job/thread is causing the increase. It is slowly increasing but nothing is standing out.
Do you have any article here that will guide me how to look for the culprit who is slowly chewing up AUX space.