5. FILES › 5.4 DB2 User Activity (DB2DSU) File › 5.4.3 Usage Considerations

5.4.3 Usage Considerations

This section identifies any special considerations and/or
techniques related to using the DB2DSU file.  Additionally,
several sample retrieval examples are provided to facilitate
the use of this file.

In the examples, we use a SAS macro to specify the DD name
part of the CA MICS file name.  These macros are a standard
part of CA MICS and are available for all files.  The macro
name has the form _iiit, where "iii" is the Information Area
name and "t" is the Time-span.  In addition, many of the
examples use the _fffSUM macro, where "fff" is the file
identifier, to summarize the data as indicated by the _BY and
_BREAK macro definitions.

Special Considerations/Techniques

 1.  Care must be exercised in using the special date and
     time data elements contained in each CA MICS file.  As
     a file's granularity increases in higher Time-spans,
     certain fields loose significance and should not be
     referenced.

     o HOUR should not be used in MONTHS and YEARS.
     o DAY and DAYNAME should not be used in WEEKS, MONTHS,
       or YEARS.
     o WEEK should not be used in MONTHS or YEARS.
     o MONTH should not be used in YEARS.

 2.  Data elements STARTTS and ENDTS have different
     meanings when used in the DETAIL time-span versus their
     meaning in the DAYS, WEEKS, MONTHS, and YEARS
     time-spans.  The ENDTS and STARTTS, when appearing in
     the DAYS, WEEKS, MONTHS, and YEARS time-spans, indicate
     the span of time over which the data has been summarized
     with STARTTS indicating the lowest date and time and
     ENDTS indicating the greatest date and time for the
     summarized data.

Retrieval Examples

     These examples assume that element DB2AUTH is a
     sort/sequence element on the DSU file.  If it is stored
     as a DB2ACTn element, substitute the correct DB2ACTn
     element name where appropriate.

 1.  List all users who issued a CREATE TABLE statement
     yesterday and the number of statements issued listed in
     order from highest to lowest.

     DATA TEST1;
     SET _DB2D.DB2DSU01;
     IF DSUCRTAB>0;
     MACRO _BY DB2AUTH %      /*
     MACRO _BREAK DB2AUTH %
     PROC SORT DATA=TEST1 OUT=TEST1; BY _BY;
     DATA TEST1;
     SET TEST1;
     _DSUSUM;
     PROC SORT DATA=TEST1 OUT=TEST1; BY DESCENDING DSUCRTAB;
     PROC PRINT DATA=TEST1;
     VAR DB2AUTH DSUCRTAB;
     TITLE USERS EXECUTING CREATE TABLE STATEMENTS;

 2.  Show the distribution of DB2 various statements by user
     over the last four weeks.

     DATA TEST2;
     SET _DB2W.DB2DSU01
         _DB2W.DB2DSU02
         _DB2W.DB2DSU03
         _DB2W.DB2DSU04
         ;
     MACRO _BY DB2AUTH %
     MACRO _BREAK DB2AUTH %
     PROC SORT DATA=TEST2 OUT=TEST2; BY _BY;
     DATA TEST2;
     SET TEST2;
     _DSUSUM;
     PROC FREQ DATA=STUFF; BY DB2AUTH;
     TABLES
       DSUQUERY DSUINSRT DSUUPDTE DSUDELET
       DSUDESC DSUCTABS DSUCRSYN DSUCRDAB
       DSUCRSTG DSUDEFVU DSUDRPIX DSUDRPTA
       DSUDRPTS DSUDRPDB DSUDRPSY DSUDRPST
       DSUDRPVU DSUALTST DSUALTTS DSUALTTA
       DSUALTIX DSUCMTON DSULOCK DSUGRANT
       DSUREVOK
       ;