Inserting information into DS_AUDIT

[etlbets]

Is it possible to include comments in the "Reason for modification" in DS_AUDIT. I am trying to include which record number is associated with the jobs found in in DS_AUDIT for a external ticketing system. Used for our SOX audits.

Current command being used
SORT DS_AUDIT WITH DTC BY.DSND DTC INSTANCE DTC CREATOR ID-SUPP MODS.

If I could join DS_AUDIT with another table in Universe that would work as well.

Thanks

Bren

[chulett]

If I could join DS_AUDIT with another table in Universe that would work as well.

Well, that is certainly possible. Join tables there just like you would anywhere else, use standard SQL syntax rather than the UniVerse Retrieve syntax that I assume that "sort" is part of.

[ray.wurlod]

Yes it's easy to do, but the fact that your audit table is editable would violate your SOX requirements, at least for a vigilant auditor. Therefore the separate table is probably better. It would need to be keyed by KEY, INSTANCE and DTM.

Make use of dynamic normalization to find the appropriate row in the nested table to edit, if you're going to update DS_AUDIT directly.

[etlbets]

Yes it's easy to do, but the fact that your audit table is editable would violate your SOX requirements, at least for a vigilant auditor. Therefore the separate table is probably better. It would need to be keyed by KEY, INSTANCE and DTM.

Make use of dynamic normalization to find the appropriate row in the nested table to edit, if you're going to update DS_AUDIT directly.

Ray, I am not an expert with Universe would you have an example of how you would join tables and how I would go about updating the tables so that it could only be written once. How does one get the dynamic normalization. Is this thru Universe commands or DS admin.

Thanks

Bren

[chulett]

As noted, use standard SQL statements to join tables just like you would in any other database.

[kduke]

The key is 2 part on jobs. The second part is the job name. You would need to write an I-descriptor to create a job name field to join to DS_JOBS and then maybe to DS_JOBOBJECTS to get short description or long description. If you want to read this in DataStage then you need a few more tricks.

[ray.wurlod]

Oh, yuk. An old Pickie approach!

You can do it all with SQL. The second table is a regular table, as noted.

Dynamic normalization is accomplished by specifying the table name and the association name, separated by an underscore. For example:

Code: Select all

SELECT * FROM DS_AUDIT_MODS ;

This will only yield the primary key of the base table (KEY or @ID) and the multi-valued fields in the association. If you want full normalization the way the UniVerse stage does it, then you use an UNNEST operator. For example:

Code: Select all

SELECT * FROM UNNEST DS_AUDIT ON MODS;

In this case all columns from the base table are available (much as they would be in a Pick-style BY-EXP expansion).

In either case the "table" (either DS_AUDIT_MODS or UNNEST DS_AUDIT ON MODS) may be given an alias name and/or joined to another table with an appropriate key structure.

[kduke]

Mr. Pick is complaining about Pick style. OMG!

[ray.wurlod]

Never was. Was a Primate.

[chulett]

[groan]