DSXchange

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

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

Hi Ray,
Thanks for your reply. I got the response. I would also like to know if I can spool the output to some text file.



Ravinder Bahadur
OCP DBA
DB2 Certified User
DB2 DBA

Use
"* como on filename *"
in the command line

after this you execute the command, the executed command and other related stuff will be stored in the system directory & como &. you can verify it in the command line.

with regards,
T Sankar

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.

Hi,

you can try the commands what you use in command,
try the same in the command stage.

with regards,
T Sankar