DS_AUDIT retention, schema, and content

gsherry1 · Post by **gsherry1** » Fri Jul 07, 2006 1:36 pm

Hello Forum,

I am researching using the DS_AUDIT table as a source of information to meet some security requirements given to me. I have the following questions:

1. How long is the entries in the DS_AUDIT table retained? How is the retention period configured?

2. How do I get the DDL/Schema for the DS_AUDIT table? How do I get the schema for a universe table in general. I tried 'describe DS_AUDIT;' and it didn't work.

3. Why is it when I query the DS_AUDIT table, it get structured output instead of just rows?

4. I found that this table seems to track edits/saves, imports, installation activities. Is there other things that are tracked here?

Your input is valued.

Thanks,

Greg

DSguru2B · Post by **DSguru2B** » Fri Jul 07, 2006 1:45 pm

In the command prompt of administrator, type
LIST DICT DS_AUDIT.
That will give you the structure of that table/hashed file.
As far as my knowledge goes, i believe the entries in DS_AUDIT stay there for a long time
Yes, this table can tell which person edited/deleted which job when. Some developers hate this

ray.wurlod · Post by **ray.wurlod** » Fri Jul 07, 2006 10:30 pm

1. How long is the entries in the DS_AUDIT table retained? How is the retention period configured?
Entries in DS_AUDIT are retained for ever unless manually purged. There is no utility provided in DataStage for purging DS_AUDIT.

2. How do I get the DDL/Schema for the DS_AUDIT table? How do I get the schema for a universe table in general. I tried 'describe DS_AUDIT;' and it didn't work.

Code: Select all

LIST.DICT DS_AUDIT

It's not Oracle, so why expect the Oracle syntax to work? Note that this is not an SQL query, so there is no trailing semi-colon.

3. Why is it when I query the DS_AUDIT table, it get structured output instead of just rows?
The three "modified" fields are multi-valued, reflecting the fact that a design-time object might be modified more than once. A multi-valued field is a column that may contain zero or more values as a list.
DS_AUDIT only stores the most recent deletion and creation, however, so these fields are not multi-valued but single-valued. In the default report the single values and the first value in each multi-valued list are aligned.
You can unnest the multi-valued fields using UNNEST DS_AUDIT ON MODS rather than DS_AUDIT as the table name in your SELECT query.

4. I found that this table seems to track edits/saves, imports, installation activities. Is there other things that are tracked here?
Creation and deletion of design-time objects.

chulett · Post by **chulett** » Sat Jul 08, 2006 7:22 am

We've been looking at doing some automated reporting from DS_AUDIT, which has exposed my crew to the concept of multi-valued fields for the first time. I figured there was no 'retention period' or purge mechanism built it, especially after going through the contents. They did have a question I wasn't sure on and this provides the perfect opportunity to ask it.

Is there any limit, practical or physical, on the number of multi-value 'entries' that a field of that type can store?

Thanks!

kcbland · Post by **kcbland** » Sat Jul 08, 2006 7:28 am

Multi-value fields (attributes) are simply strings, so they can be quite large. The entire row (record) can be quite large, megabytes if you want. The problem with large numbers of multivalues in an attribute is just retrieval time, but don't think that's a big deal in this case.

Keep in mind in the multivalue world the use of multivalue attributes and subvalues is the means to avoid joins because normalized child tables are really stored within the parent in those multivalue attributes. The entire method is the heart of the model and the language. They've tweaked it fairly well by now.

chulett · Post by **chulett** » Sat Jul 08, 2006 7:34 am

Thanks Ken. This installation has been in place for 3+ years with alot of activity, so they've definitely noticed the 'can be quite large' part and had to deal with it.

And I've tried to pass along the 'normalized child tables are really stored within the parent' concept by comparing it (conceptually) to a View over the parent/child in an RDBMS. Seems to have helped, but they still think of it as a form of magic.

ray.wurlod · Post by **ray.wurlod** » Sat Jul 08, 2006 8:04 am

Any sufficiently advanced form of technology is indistinguishable from magic (Arthur C. Clarke). In this case it was sufficiently advanced in the late 1960s!

chulett · Post by **chulett** » Sat Jul 08, 2006 8:07 am

Right on! I almost quoted Clark there but decided you could use the opportunity to bump your post count up.

kcbland · Post by **kcbland** » Sat Jul 08, 2006 1:54 pm

I like to analogize Multi-Value by saying you store arrays instead of values. Being an aerospace engineer by education, we did a lot of matrix math, so I was quite comfortable with the idea of thinking in 3-D matrix form for numbers, now it's just dates and quantities and dollars.

Actually, I did cheat some in college because the PICK derivative system I used at work (NCR ADDS Mentor 7000) allowed me to do some heat transfer calculations quite easily in PICK BASIC. Think of a sheet of metal where you divide the sheet into a grid and calculate heat transference from one point to the next propagating across the sheet. Storing the results as a matrix becomes quite simple, because you can store values easily where each attribute is a multivalue list of the points values across the line on the sheet. It's easy to store many records according to time. Trying to do the calculations in FORTRAN was easy, but building a whole system for storing and reading back data in FORTRAN was't easy.

At least I could do the code first in PICK BASIC and get the answers right, then worry about getting the FORTRAN to match the result of the PICK BASIC. Trying to become FORTRAN expert is not something that's going to happen in a heat transfer class.

Ahhh, the good old days.

ray.wurlod · Post by **ray.wurlod** » Sat Jul 08, 2006 3:32 pm

This White Paper describes the nested relational model and the technical advantages it has over fully normalized models.