Query Manager Profile Auditing

There are two main parts to this solution:

Part 1
A new journal entry (X2) would be sent to the QAUDJRN any time a QM profile changes.
The journal entry would have the before and after profile information so we could tell exactly what changed in the profile.

Part 2
Normally, the way audit journal entries are externalized is that each has an associated output file.
A particular audit entry type is output into that output file which is tailored to the information in that journal entry.
However, output files are expensive because of language translation costs and are much more difficult and expensive to PTF.
Also, the Query Manager profile changes each release as we add more SQL statements that one can authorize.
This makes an outfile solution even more expensive as it would have to continually be updated.
Thus, to keep the cost down and to make it possible to PTF this support into IBM i 6.1 and 7.1, we do not plan to create an output file that maps this journal entry.

The audit journal entry is externalized using a DB2 for i supplied view in QSYS2, similar to how we provided the current values of profiles using the QSYS2.SQLQMprofiles view.

• The view entry returns a set of data that is available for all journal entries that identifies who and when made the change:
  • Journal entry timestamp
  • Current user
  • Job name, job user, job number
  • Thread
• Most of the values stored in the QM profile only have two possible values. For example the values for authority to use the INSERT statement are Y or N.
• The following QM profile values have more than two possible values:
  • Default Library
  • Default object creation authority
  • Relational database connection
  • Sample size of Query
  • Maximum records allowed on an interactive run
  • Default collection for QM tables
  • Query Data Output Type
  • Table and library for output
  • Job description and library for batch run
  • Commitment control lock level
  • Default printer name

For example, assume a user changed both the default library from MJASRC to MJATST and also changed to authorize use of the INSERT statement.
The query result associated with that journal entry would look something like: