A few months ago, I got this question from a Db2 for z/OS DBA: "Is there a way we can check to see if the UNLOAD utility has been executed for certain of our tables?" The environment in question was a Db2 12 for z/OS system, and because of that I had to provide an answer that was less-than-great from an ease-of-use persepective: "Yes, you can do that in a Db2 12 environment, but the process is going to be a bit cumbersome. You could set up a Db2 audit policy that would record utility execution. What that will do is cause Db2 to write certain trace records when utilities are executed. You'd need to use your Db2 monitor to format that trace information into a human-readable report (using IBM's OMEGAMON monitor for Db2 for z/OS, that would be by way of a Record Trace report); and, you'd have trace records written for EVERY utility execution during the reporting time interval - potentially, a lot of data to sift through; and, there would be trace records generated for every utility executed for ANY database object - not just the tables in which you're interested (more data to sift through)."
"Alternatively, you could set up a different audit policy that would limit trace output to the tables of interest, but that policy would cause Db2 to generate trace records for ALL operations performed involving that set of tables, whether associated with a utility or with an application process - again, potentially a lot of trace data to sift through."
Not the idea situation, but along comes Db2 13 for z/OS to make things better - in two steps.
Step 1: Once you've activated function level V13R1M501, you can take advantage of a new ZPARM parameter, UTILITY_HISTORY. The default value for that parameter is NONE - not because we (IBM) want to discourage you from using a new Db2 capability; rather, this is in keeping with the traditional approach for a new ZPARM: the initial default value preserves existing behavior. "Existing behavior" in this case is "no utility history," because utility history functionality was not available prior to Db2 13 function level 501. If you set UTILITY_HISTORY to UTILITY, something nice happens: every time a utility executes, Db2 will insert a row into the SYSIBM.SYSUTILITIES table (newly added when CATMAINT is executed to take the catalog to the V13R1M501 level). That row for a utility in SYSUTILITIES provides a lot of useful information, such as:
- The name of the utility (e.g., UNLOAD, or COPY, or REORG, or ...)
- The name of the job through which the utility was executed
- The user ID that invoked the utility
- The starting point of the utility, both timestamp-wise and logpoint-wise
- The timestamp at which the utility completed processing
- The elapsed time for the utility job
- The general-purpose CPU time and zIIP CPU time for the utility job
- The portion of CPU time (both general-purpose and zIIP) consumed with sort activity related to the utility job
- The utility job's return code
- Whether the utility job was terminated (and, if so, whether it was restarted)
- All the utilities that executed between midnight and 8:00 AM this morning
- All the utilities that are currently active or stopped
- All the utilities that finished with a return code of 8 or higher
- The top general-purpose CPU-consuming LOAD jobs over the past 7 days
- And more...
Hi Robert. As a DBA, I have been interested in Utility History as a useful tool for some time, and have been waiting for something like this. I've just advised my DBEs that I would like us to get there there (FL501/504) as soon as it's practical. However, I have one question about this feature.
ReplyDeleteWe have a daily job that runs an incremental copy on several older tables (becoming obsolete), and I noted that those daily utility runs don't show up in SYSCOPY because there are no changed pages. My question is, will these utility runs show up in SYSUTILITIES? Or will they be excluded for the same reason as they are in SYSCOPY?
Thanks much. Been following your writing for quite a long time.
There should be a row for such a utility execution in the SYSUTILITIES catalog table (if you have activated function level V13R1M501 or higher and have set the value of the UTILITY_HISTORY parameter in ZPARM to UTILITY). If you have activated function level V13R1M504 or higher and have set UTILITY_HISTORY to OBJECT, you should also get a row in the SYSOBJEVENTS catalog table for the object that was the target of the COPY job (see the Db2 for z/OS documentation page at https://www.ibm.com/docs/en/db2-for-zos/13?topic=4-utility-history-utility-history-subsystem-parameter).
ReplyDeleteRobert
Hi Robert
ReplyDeleteThe tables are useful but missing useful stuff like the full text of the utility statements and Db2 commands. I believe these are on the way. You also have to develop a way to cull these tables as there are a lot of rows. Another thing to get around is that REORG mapping tables turn up in SYSOBJEVENTS which causes clutter.
The queries in the Redbook I found underwhelming so here's some I've spun up:
See what objects are being REORG'd :
select
-- oe.dbname as dbname
-- ,oe.spacename
-- ,oe.objtype,
coalesce(tb.creator,ix.tbcreator) as tbcreator
,coalesce(tb.name,ix.tbname) as tbname
,count(*) as reorgrows
,count(distinct(date(STARTTS))) as days_run
,min(date(startts)) as min_start_date
,max(date(endts)) as max_end_date
,cast(max(ut.ELAPSEDTIME)/cast(60*1000000 as float) as decimal(21,2))
as max_ut_elapsed_minutes
,sum(ut.CPUTIME) as CPUTIME
,sum(ut.ZIIPTIME) as ZIIPTIME
FROM
sysibm.sysutilities ut
inner join
sysibm.sysobjevents oe
on ut.eventid=oe.eventid
left outer join
sysibm.sysindexes ix
on ix.dbname=oe.dbname
and ix.indexspace=oe.spacename
left outer join
sysibm.systables tb
on tb.dbname=oe.dbname
and tb.tsname=oe.spacename
where
ut.name='REORG'
and oe.dbname is not null
group by
-- oe.dbname
-- ,oe.spacename
-- ,oe.objtype,
coalesce(tb.creator,ix.tbcreator)
,coalesce(tb.name,ix.tbname)
order by sum(ut.CPUTIME) desc
WITH UR
;
How often are we REORGing?
ReplyDeleteselect
ut.EVENTID
,coalesce(tb.dbname,ix.dbname)
,coalesce(tb.tsname,ix.indexspace)
,coalesce(tb.creator,ix.tbcreator)
,coalesce(tb.name,ix.tbname)
,oe.objtype
,count(*)
FROM
sysibm.sysutilities ut
inner join
sysibm.sysobjevents oe
on ut.eventid=oe.eventid
left outer join
sysibm.sysindexes ix
on ix.dbname=oe.dbname
and ix.indexspace=oe.spacename
left outer join
sysibm.systables tb
on tb.dbname=oe.dbname
and tb.tsname=oe.spacename
where
ut.name='REORG'
and oe.dbname is not null
group by
ut.EVENTID
,coalesce(tb.dbname,ix.dbname)
,coalesce(tb.tsname,ix.indexspace)
,coalesce(tb.creator,ix.tbcreator)
,coalesce(tb.name,ix.tbname)
,oe.objtype
WITH UR
;
REORG for a particular tablespace:
ReplyDeleteselect
ut.JOBNAME
,ut.USERID
,ut.UTILID
,ut.name
--,oe.INSTANCE, -- SMALLINT
,oe.DBNAME -- VARCHAR(24)
,oe.SPACENAME -- VARCHAR(24)
,oe.PARTITION -- SMALLINT
,oe.OBJTYPE -- CHAR(1)
,coalesce(tb.creator,ix.tbcreator) as tbcreator
,coalesce(tb.name,ix.tbname) as tbname
,oe.COUNT -- BIGINT
--,oe.ELAPSEDTIME -- BIGINT
,cast(ut.ELAPSEDTIME/cast(60*1000000 as float) as decimal(21,2))
as UT_elap_min
-- ,oe.INSERTEDBY -- VARCHAR(9)
,oe.EVENTTS as obj_ts -- TIMESTAMP(6)
,ut.startts
,ut.endts
,ut.CPUTIME
,ut.ZIIPTIME
,ut.SORTCPUTIME -- BIGINT
,ut.SORTZIIPTIME -- BIGINT
--,ut.EVENTID -- BIGINT
--,ut.INSERTEDBY -- VARCHAR(9)
--,ut.RETURNCODE -- INTEGER
--,ut.CONDITION -- CHAR(1)
--,ut.RESTART -- CHAR(1)
,ut.NUMOBJECTS -- INTEGER
,ut.LISTNAME -- VARCHAR(18)
--,ut.STARTLOGPOINT -- CHAR(10)
--,ut.GROUP_MEMBER -- VARCHAR(24)
--,ut.SORTNAME -- VARCHAR(24)
FROM
sysibm.sysutilities ut
inner join
sysibm.sysobjevents oe
on ut.eventid=oe.eventid
left outer join
sysibm.sysindexes ix
on ix.dbname=oe.dbname
and ix.indexspace=oe.spacename
left outer join
sysibm.systables tb
on tb.dbname=oe.dbname
and tb.tsname=oe.spacename
where
--nd oe.dbname is not null
oe.dbname ='DSN06555'
and oe.spacename ='F1CRRFTG'
and startts > current timestamp - 3 day
order by startts desc
Thanks very much for sharing these queries, Frank. You might want to consider developing a presentation that would include these and perhaps other queries on the utility history tables, along with your own experiences and suggestions related to using those tables, and submitting that presentation as a proposed session for an IDUG conference. I think a session of that nature would be helpful to a lot of Db2 for z/OS people.
DeleteRobert
There are also some features in Db2 Administration Tool that allow to nicely observe the utility history https://www.ibm.com/docs/en/db2admintool/13.1?topic=administration-utility-history
DeleteThanks for that information, Viacheslav!
DeleteRobert