DS Admin related query

[Ritesh Vikram]

Hi
I had recently started working with DataStage tool. When we do audit check on our DS jobs we use the table DS_AUDIT. I wanted to know what all are other tables being used and what are their respective functionality required for the administration purpose?
Ritesh[/code]

[ray.wurlod]

Welcome aboard. :D

Very few of the other tables are used for administrative purposes; the main ones being UV.ACCOUNT and UV_* (which count as system tables) and SYS.MESSAGE. Directories with names beginning and ending in "&" are also similar to system tables.

Tables whose names begin with DS_ are related to design-time components, while tables whose names begin with RT_ are related to run_time information. VOC contains the vocabulary used in interactive DataStage commands, while VOCLIB is like an "overflow".

Any table whose name begins with D_ is a file dictionary (containing metadata) for a hashed file.

[ogmios]

You can use the following to see changes after a certain date:

Code: Select all

SELECT * FROM DS_AUDIT WHERE DTM > '2006-03-05 12:00:00';

I also have some queries to unnest the data in DS_AUDIT, but have to search for them a bit.

Ogmios

[ogmios]

A version that returns less clutter is:

Code: Select all

SELECT INSTANCE, MODIFIER, DTM, REASON 
FROM   UNNEST DS_AUDIT ON MODS A
WHERE  CLASS = '2' AND
       DTM > '2006-04-10 00:00:00'
AND    DTM = (SELECT MAX(DTM) 
              FROM UNNEST DS_AUDIT ON MODS B
              WHERE CLASS = '2' 
              AND A.INSTANCE = B.INSTANCE)
ORDER BY DTM

Fill in the date after > to whatever value you like as cutoff. The query above can run slow :D

Regards,
Ogmios