Dear Fellows,
I need to find out the user who has last accessed and modified a job. Can it be done. If yes how . Please provide detail steps.
Thanks
Regards
Ravinder Bahadur
OCP DBA
DB2 Certified User
DB2 DBA
How to locate the user which changed the job last
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This information is maintained by DataStage in a hashed file (table) called DS_AUDIT. The date/time modified is visible in Manager if Detail view is selected, however the modifier is not.
There are three associated multi-valued columns of information in DS_AUDIT relating to modification. These are DTM, MODIFIER and REASON. So, to retrieve the entire modification history for a particular job (say "MyJob") you could execute the following SQL statement, perhaps in the Administrator command window:
SELECT INSTANCE, DTM, MODIFIER FROM DS_AUDIT WHERE CLASS = '2' AND INSTANCE LIKE '%MyJob';
Note that identifier (column and table) names in DataStage SQL are case sensitive. The date/time modified is stored in chronological sequence; you can access this with extended SQL to unnest on the association name (MODS). For example:
SELECT INSTANCE,DTM,MODIFIER FROM UNNEST DS_AUDIT ON MODS WHERE @ID LIKE '%MyJob' AND DTM = (SELECT MAX(DTM) FROM UNNEST DS_AUDIT ON MODS WHERE @ID LIKE '%MyJob');
You can access DS_AUDIT through a UV stage using the UNNEST drop-down list.
In the very near future a utility will appear on this web site in the form of a job to generate a listing of modification times; it was developed for use with CompileAll and Version Control.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
There are three associated multi-valued columns of information in DS_AUDIT relating to modification. These are DTM, MODIFIER and REASON. So, to retrieve the entire modification history for a particular job (say "MyJob") you could execute the following SQL statement, perhaps in the Administrator command window:
SELECT INSTANCE, DTM, MODIFIER FROM DS_AUDIT WHERE CLASS = '2' AND INSTANCE LIKE '%MyJob';
Note that identifier (column and table) names in DataStage SQL are case sensitive. The date/time modified is stored in chronological sequence; you can access this with extended SQL to unnest on the association name (MODS). For example:
SELECT INSTANCE,DTM,MODIFIER FROM UNNEST DS_AUDIT ON MODS WHERE @ID LIKE '%MyJob' AND DTM = (SELECT MAX(DTM) FROM UNNEST DS_AUDIT ON MODS WHERE @ID LIKE '%MyJob');
You can access DS_AUDIT through a UV stage using the UNNEST drop-down list.
In the very near future a utility will appear on this web site in the form of a job to generate a listing of modification times; it was developed for use with CompileAll and Version Control.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There are several ways to direct the output to a file. Each involves a command issued in the DataStage environment prior to executing the SQL statement.
COMO ON filename
This directes output to the file "filename" in the &COMO& sub-directory, which is created automatically if necessary. Use COMO OFF to redirect output back to the "screen".
More flexibility is available with DIVERT.OUT:
DIVERT.OUT action [ filename record ] [ parameter ]
where
filename is the name of an existing Type 1/19 file (such as &UFD& for the current directory
record is the name of the "record" in the Type 1/19 file where the output is to be diverted (this is the name of a UNIX file)
action can be one of:
. ON start sending output to file
. OFF stop sending output to file
. FILE.OFF suspends sending output to file
. FILE.ON resumes sending output to file
. TTY.OFF turns off terminal display
. TTY.ON turns terminal display back on
parameter can be any of the following keywords:
. TTY.OFF turns off terminal display (used with ON action)
. TTY.ON turns on terminal display (used with OFF action)
. TRUNCATE overwrites ouput file if it exists
. APPEND appends to output file
My preferred approach is to create a "print channel" to file. In this case the file is in the &HOLD& sub-directory, which is created automatically if required.
SETPTR 0,132,66,3,3,3,BANNER filename,BRIEF
In this case, add the keyword LPTR to your SQL statement, for example
SELECT * FROM DS_AUDIT LPTR;
Output will be in the file "filename" in the &HOLD& sub-directory.
COMO ON filename
This directes output to the file "filename" in the &COMO& sub-directory, which is created automatically if necessary. Use COMO OFF to redirect output back to the "screen".
More flexibility is available with DIVERT.OUT:
DIVERT.OUT action [ filename record ] [ parameter ]
where
filename is the name of an existing Type 1/19 file (such as &UFD& for the current directory
record is the name of the "record" in the Type 1/19 file where the output is to be diverted (this is the name of a UNIX file)
action can be one of:
. ON start sending output to file
. OFF stop sending output to file
. FILE.OFF suspends sending output to file
. FILE.ON resumes sending output to file
. TTY.OFF turns off terminal display
. TTY.ON turns terminal display back on
parameter can be any of the following keywords:
. TTY.OFF turns off terminal display (used with ON action)
. TTY.ON turns on terminal display (used with OFF action)
. TRUNCATE overwrites ouput file if it exists
. APPEND appends to output file
My preferred approach is to create a "print channel" to file. In this case the file is in the &HOLD& sub-directory, which is created automatically if required.
SETPTR 0,132,66,3,3,3,BANNER filename,BRIEF
In this case, add the keyword LPTR to your SQL statement, for example
SELECT * FROM DS_AUDIT LPTR;
Output will be in the file "filename" in the &HOLD& sub-directory.