Last Change Date and User

[Tpaulsen]

Is there a way in DataStage to view who last updated an ETL job and when?

[nsm]

Nope.
All you can see is last Compiled if nobody ran the job after compilation.

[ramesh_dw]

View the job log in Datastage Director.. To find out the user, view the event details

R,
Ramesh

[chulett]

Looking in the Director log won't help with finding out who last modified an ETL job.

I believe that someone (Ray/Ken/Kim) posted some code to get information like that from the internal DataStage repository, does anyone remember seeing that? I did a quick search but couldn't turn anything up. That's about the only way to get information like that, but it is not publically exposed.

[Athorne]

Along the same lines, do you think that code could also find things like who has a job open? Always a pain to try an work on a job and see that message that another user has it open. If you work in a large development team it could be tough to find out who has it open.

[ogmios]

Search for DS_AUDIT

Ogmios

[ogmios]

To see who has a job open... use list_readu

Ogmios

[ray.wurlod]

The DS_AUDIT table is publicly accessible. It's just not exposed in the DataStage clients, apart from DTM being displayed in Manager.

From the Command window (Administrator client) or in the dssh environment on the server, execute this command to determine the column names.

Code: Select all

LIST.DICT DS_AUDIT

To find out who last modified an ETL job you will need to know that the DTM and MODIFIER columns are multi-valued (so you need to UNNEST the nested table), and that the class for jobs is '2'. Therefore, an appropriate query is as follows.

Code: Select all

SELECT INSTANCE, MODIFIER, DTM, REASON
FROM   UNNEST DS_AUDIT ON MODS
WHERE  CLASS = '2' 
AND    INSTANCE = 'MyJobName'
AND    DTM = (SELECT MAX(DTM)
              FROM UNNEST DS_AUDIT ON MODS
              WHERE CLASS = '2'
              AND INSTANCE = 'MyJobName') ;

Note that SQL identifiers (such as table names and column names) as well as job names are case sensitive.

[sandy_thomas]

Ray,

Is ther any way to get the session ID or IP address for the last change. In our Env most of the datastage users are common user id's. If i can get the IP address i can map to the meachine.

thanks a lot.

[ray.wurlod]

No.

If the user is still logged in, you can determine the identity of that user's client machine (from LIST_READU, or from a query on DS_LICENSE such as SELECT @ID FMT '56L' FROM DS_LICENSE;), but which of those common users is the culprit? It's not recorded in DS_AUDIT.

Best practice is NOT to use common login IDs.

[richdhan]

Ray,

We are working in a multi-user environment where 2 or 3 users work on the same job(one for development, one for review and one for testing). Is it possible to find the user who is currently using the job because we get the message "Job is being accessed by another user".

Can we use the same query that you had posted earlier to find that.

Code: Select all

SELECT INSTANCE, MODIFIER, DTM, REASON 
FROM   UNNEST DS_AUDIT ON MODS 
WHERE  CLASS = '2' 
AND    INSTANCE = 'MyJobName' 
AND    DTM = (SELECT MAX(DTM) 
              FROM UNNEST DS_AUDIT ON MODS 
              WHERE CLASS = '2' 
              AND INSTANCE = 'MyJobName') ;

What is the use of UNNEST and ON MODS keywords that you have used in this query.

Thanks in advance.
--Rich

[ray.wurlod]

DTM, MODIFIER and REASON are multi-valued columns within the DS_AUDIT table. Together they form a "nested table" called MODS.
You can see all this with LIST.DICT DS_AUDIT.

To expose the MODS table, one way is to use the UNNEST operator in the FROM clause. This "explodes" the other columns (the single-valued ones that are not in the MODS association ("association of multi-valued fields", or "set of associated multi-valued fields" is the official term for a nested table). This is the mechanism used by the "Normalize on" capability in the UniVerse, UniData and hashed file stages.

Another method for exposing the nested table is "dynamic normalization". In this case, the syntax is

Code: Select all

SELECT * FROM DS_AUDIT_MODS;

Because no rowunique identifier has been specified, a synthetic key called @ASSOC_ROW is generated as the ordinal row number in the exposed table. The primary key of the base table forms the remainder of the primary key of the result set.

Because DS_AUDIT is not updated until the job design (or whatever) is closed, this is not the right way to spot the culprit when it is "locked by another user".

The secret here is to display the locks. I prefer to do this from the UNIX prompt, because I can then use grep to pinpoint the culprit!

Code: Select all

$DSHOME/bin/list_readu | grep RU | grep jobname

I use a variant of this to ensure that nothing is locked for editing prior to taking a nightly export of the project.

Code: Select all

$DSHOME/bin/list_readu | grep RU | grep -v :

Hiding the lines with ":" causes connected client's locks against the DS_LICENSE file not to be displayed; they don't affect the export.