Re: SQL UDFS

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.