At some point I had decided not to post my delayed reply to the
original post; per mostly resolved. But for lack of any follow-up
on the "exit point" idea, which I believe none will be found to
assist, and for no clarifying remarks about customizing the journal
environment, I figured I would paste my original response as a reply
to this message... regardless that there may be some duplicated
information.
I am not sure what documentation contains that originally quoted
text, but the comment is at least somewhat misleading. For example,
of the UDFs, only the SQL UDF will be represented directly by an
object in addition to its entry in the catalog; the External UDF
refers to an object that may or may not exist, and that object is
even only optionally reflected as an SQL-described object. In any
case, the auditing for the objects themselves is without regard to
the SQL; i.e. general system object auditing exists independent of
the SQL. So if the issue was for an object gone missing, then
system object auditing for *DLT would record a T-DO journal entry
for the object deletion.
I infer however, that what went missing was not an _object_, but
merely the definition of the[\an external] UDF. The object
associated with the External UDF is unaffected by the DROP, so no
_object_ is deleted and thus no T-DO audit entry logged. So... the
only tracking then is the QSQJRN *JRN object in QSYS2 which journals
the SQL catalog TABLE SYSROUTINE; long name SYSROUTINES. Look for
the R-DL journal entries logged for that file, to locate the row
which has the expected data in the fields RTNNAME and RTNSCHEMA;
i.e. the data for the length of each, in the "Entry Specific Data"
of the journal entry. Unfortunately the entry may include only a
row number for defaulted *AFTER image logging; reviewing a system, I
think I may have made the default to log *BOTH however. Note that
the journal environment uses system managed with delete, so the
active receiver will be lost at an IPL. Use CHGJRN to change the
journal environment to be more conducive to future tracking of
actions prior to the current IPL [since that change request]; e.g.
change the receivers to not delete automatically, even attach to a
receiver in a different library if backup of the receivers is
valuable.
Because the library QSYS2 is [since v5r?] one of the pseudo-user
libraries, it is saved on a *ALLUSR save. If the definition of the
missing UDF is unknown and so required to recreate the UDF, then if
the CREATE FUNCTION was prior to the last save, the SYSROUTINE file
can be restored to QTEMP and its data reviewed. Note: Do *not*
restore the file directly into QSYS2 unless as part of DR.
Regards, Chuck
Wintermute, Sharon wrote:
As Vern pointed out, the journal QSQJRN contains the audit info
for the file SYSROUTINE. It tracks when it is created and
deleted. I could see where I recreated the UDF. Unfortunately,
the old journal receivers are gone since BRMS does not save
QRECOVERY objects.
So, I am going to see about using an exit point, just not there
yet.
Neill Harper wrote:
Please could you share, how you found out who deleted the udf
as this could be helpful in the future.
Vern Hamberg wrote:
These are not system objects. All of these are actually just
records in the various system tables in QSYS2 - names like
SYSTABLES, SYSFUNCS, etc. These are generally not actual
iSeries objects. Tables, yes, are implemented as physical
files, etc. Some of these records have references to real
system objects.
UDFs are in the afore-mentioned SYSFUNCS view. Maybe
something that'd recognize that a record has been deleted
from it. I don't know if there are journals on the tables
that the view is constructed over, but you could see.
Beyond that, I don't know quite an approach to use.
Wintermute, Sharon wrote:
In the IBM documentation it states:
SQL objects are schemas, journals, catalogs, tables,
aliases, views, indexes, constraints, triggers, sequences,
stored procedures, user-defined functions, user-defined
types, and SQL packages. SQL creates and maintains these
objects as system objects.
How do I audit these system objects? Someone deleted a UDF
causing havoc and now I need to figure out how to capture
that.
midrange.com
