DS_AUDIT retention, schema, and content

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
gsherry1
Charter Member
Charter Member
Posts: 173
Joined: Fri Jun 17, 2005 8:31 am
Location: Canada

DS_AUDIT retention, schema, and content

Post by gsherry1 »

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
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

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 :wink:
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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!
-craig

"You can never have too many knives" -- Logan Nine Fingers
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :wink:

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. :lol:
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Any sufficiently advanced form of technology is indistinguishable from magic (Arthur C. Clarke). In this case it was sufficiently advanced in the late 1960s!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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. :roll:
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

This White Paper describes the nested relational model and the technical advantages it has over fully normalized models.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply