Last Change Date and User
Moderators: chulett, rschirm, roy
Last Change Date and User
Is there a way in DataStage to view who last updated an ETL job and when?
Re: Last Change Date and User
View the job log in Datastage Director.. To find out the user, view the event details
R,
Ramesh
R,
Ramesh
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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Andrew Thorne
Lead Software Engineer
PeopleSoft Technical Architecture
Enterprise Rent-A-Car
Lead Software Engineer
PeopleSoft Technical Architecture
Enterprise Rent-A-Car
Re: Last Change Date and User
Search for DS_AUDIT
Ogmios
Ogmios
In theory there's no difference between theory and practice. In practice there is.
Re: Last Change Date and User
To see who has a job open... use list_readu
Ogmios
Ogmios
In theory there's no difference between theory and practice. In practice there is.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Note that SQL identifiers (such as table names and column names) as well as job names are case sensitive.
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
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') ;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 9
- Joined: Tue Dec 09, 2003 2:20 pm
Ray is there any way to get the session ID for that change .
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
What is the use of UNNEST and ON MODS keywords that you have used in this query.
Thanks in advance.
--Rich
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') ;
Thanks in advance.
--Rich
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 isBecause 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!
I use a variant of this to ensure that nothing is locked for editing prior to taking a nightly export of the project.
Hiding the lines with ":" causes connected client's locks against the DS_LICENSE file not to be displayed; they don't affect the export.
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 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
Code: Select all
$DSHOME/bin/list_readu | grep RU | grep -v :
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.