Sequence Job - retrieving Logs
Moderators: chulett, rschirm, roy
Sequence Job - retrieving Logs
Hi,
We run data stage server jobs using perl script. We have written a generic perl script which takes the job name and parameters as argument and executes the datastage job using the dsjob command.It also captures the log of the job and writes in a seperate file.
Now the problem is that when i run sequence jobs ,the log which is captured is of the sequence job and not of the indidual server jobs which the sequence job is triggering.
Is there any way of capturing the logs of all the server jobs which the sequence job is calling ? can i do this using some kind of generic script, Which will hold good for any sequence job?
Thanks in advance,
Dhiraj
We run data stage server jobs using perl script. We have written a generic perl script which takes the job name and parameters as argument and executes the datastage job using the dsjob command.It also captures the log of the job and writes in a seperate file.
Now the problem is that when i run sequence jobs ,the log which is captured is of the sequence job and not of the indidual server jobs which the sequence job is triggering.
Is there any way of capturing the logs of all the server jobs which the sequence job is calling ? can i do this using some kind of generic script, Which will hold good for any sequence job?
Thanks in advance,
Dhiraj
-
- Participant
- Posts: 19
- Joined: Fri Apr 02, 2004 10:13 am
Here is some unix code you could use or modify.
***********************************
dsjob -logsum Proj Jobseq > TEMPFIL1
for AbortJob2 in `cat TEMPFIL1 | egrep "\(Aborted\)|\(Finished with warnings\)" | cut -f 4 -d " "`
do
dsjob -logsum Proj Jobseq > TEMPFIL3
done
**************************************
Naren
***********************************
dsjob -logsum Proj Jobseq > TEMPFIL1
for AbortJob2 in `cat TEMPFIL1 | egrep "\(Aborted\)|\(Finished with warnings\)" | cut -f 4 -d " "`
do
dsjob -logsum Proj Jobseq > TEMPFIL3
done
**************************************
Naren
What about trying something like this:
Write a DataStage BASIC routine to find all the jobs called from a particular sequencer, and then goes to the log of those jobs to get entries and puts them in separate sequential files or one file with the jobname as key. The sequencer can then call the routine at the very end, passing its own name to the routine. Then if needed the Perl script can just go to the sequential files with the log entries.
It would take more than 30 minutes to write that and test it, but would be a cool piece of reusable code if you did it.
This is something Kim could do in BASIC, but it would be nice to see your starting point in Perl too.
Write a DataStage BASIC routine to find all the jobs called from a particular sequencer, and then goes to the log of those jobs to get entries and puts them in separate sequential files or one file with the jobname as key. The sequencer can then call the routine at the very end, passing its own name to the routine. Then if needed the Perl script can just go to the sequential files with the log entries.
It would take more than 30 minutes to write that and test it, but would be a cool piece of reusable code if you did it.
This is something Kim could do in BASIC, but it would be nice to see your starting point in Perl too.
Byron Paul
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure
Here it is.
I've given the script which retrieves the log.Can any one please help?
Paul,
I am not sure of the universe tables and the relationship between them.
can u explain how we can retrieve that information using basic.i mean the details about the sequence job and all the server jobs related to it.
sub processLog
{
$columns="";
use Text::Wrap qw(wrap $columns); # Set up output wrapping
$columns=76;
$NBR=""; # DS Msg Number
$TYPE=""; # DS Msg Type
$DATE=""; # DS Msg Date/time
#------------------------------------------------------------------
# make local copies of the passed parameters
#------------------------------------------------------------------
local ($pgm, $login, $proj, $job, $detail) = @_;
#------------------------------------------------------------------
# Get an array of DS summary log messages
#------------------------------------------------------------------
@dslog = getSummaryLog($pgm, $login, $proj, $job);
#------------------------------------------------------------------
# Process each message in the log.
#------------------------------------------------------------------
foreach ( @dslog )
{
chomp($_);
#------------------------------------------------------------------
# If a Summary Message ( A line of msg text)
# The first field is a tab.
#------------------------------------------------------------------
if ($_ =~ /^[\t]/)
{
#------------------------------------------------------------------
# Convert all ';' to '; ' ( DataStage stacks info with a ;
# creating lines too long to wrap )
#------------------------------------------------------------------
$_ =~ s/;/;\040/g;
#------------------------------------------------------------------
# If there are more lines in the detail get them now
#------------------------------------------------------------------
if ($_ =~ /\(...\)/)
{
#------------------------------------------------------------
# If it is a link report print only is there is an error
#------------------------------------------------------------
if ($_ =~ /LinkReport/)
{
#-------------------------------------------------------
# If it is a link report print only is there is an error
# $detail parm is greater that 4.
#-------------------------------------------------------
if ($detail > 4 )
{
print "Link Report: \n";
printDetail($pgm, $login, $proj, $job, $NBR);
}
else
{
print wrap("", "\t", "$_\n");
}
}
else
{
printDetail($pgm, $login, $proj, $job, $NBR);
}
}
else
{
print wrap("", "\t", "$_\n");
}
print "--------------------------------------";
print "--------------------------------------\n";
}
else
#------------------------------------------------------------------
# If a Summary Header (Msg Number, type and date)
#------------------------------------------------------------------
{
($NBR, $TYPE, $DATE) = split(/\t/,$_);
print wrap("", "\t", "NO: $NBR\tTYPE: $TYPE\tDATE: $DATE\n");
}
}
}
#****************************************************************************
# 6.1 Subroutine getSummaryLog
#
# Builds and array of summary log messages from datastage for the last
# execution of a specific job.
#
# dsjob -file .DSnetrcn bcrs8 -logsum dsproj jobname
#
# Parameters:
# $_[0] - The datastage pgm full path
# $_[1] - login information
# $_[2] - the DataStage Project
# $_[3] - the DataStage Job
#
# Returns: None
#
#****************************************************************************
sub getSummaryLog
{
$ending_sub = 0; # Index for ds log array
@dslog = (" ", " "); # an array of log messages
$prevline = " "; # Temp holds previous line
$line = ""; # Temp holds current line
local ($pgm, $login, $proj, $job ) = @_; # copy lines
#------------------------------------------------------------------
# execute the following DataStage command and pipe the results
# to a filehandle.
#------------------------------------------------------------------
$DSCall = $pgm . " " . $login . " -logsum " . $proj . " " . $job;
open(DSLOG,"$DSCall |") ||
die "Cannot open DSLOG to get log records: $!";
#------------------------------------------------------------------
# Load an array of selected log lines. The log can contain many
# executions of the job. Only load the messages from the latest
# execution. The string 'Starting Job' will cause the logic to
# start loading at the beginning of the array
#
# The log summary function passes two entries for each entry in
# the datastage log. The first line contains the line number,
# the date, and the time. The second line contains the text of
# the log message.
#------------------------------------------------------------------
while ($line = <DSLOG>)
{
if ($line =~ /Starting Job/)
{
#--------------------------------------------------------------
# 'Starting Job' always on 2 line of pair so put the previous
# line in the first position of the array and set index to next
# position
#--------------------------------------------------------------
$dslog[0] = $prevline;
$ending_sub = 1;
}
#--------------------------------------------------------------
# Move line to next position in array, save off the line in
# case of a new execution of the job and increment the index
# to the next array position.
#--------------------------------------------------------------
$dslog[$ending_sub] = $line;
$prevline = $line;
$ending_sub++;
}
close DSLOG;
#------------------------------------------------------------------
# If no lines are processed assume the command was bad and abend
#------------------------------------------------------------------
if ( $ending_sub == 0 )
{
sleep 5; # Allow for last write
print "$PGM: getSummaryLog: ERROR - Bad Datastage command\n";
printf "$PGM: Command: %s\n", encryptPassword($DSCall);
exitRoutine(8);
}
return @dslog
}
#****************************************************************************
# 6.2 Subroutine printDetail
#
# Prints the detail portion of log message.
#
# dsjob -file .DSnetrcn bcrs8 -logsum dsproj jobname
#
# Parameters:
# $_[0] - The datastage pgm full path
# $_[1] - login information
# $_[2] - the DataStage Project
# $_[3] - the DataStage Job
#
# Returns: None
#
#****************************************************************************
sub printDetail
{
local ($pgm, $login, $proj, $job, $nbr ) = @_; # copy parms
$line=""; # temp to keep line
$count=0; # keep count of lines
#------------------------------------------------------------------
# execute the following DataStage command and pipe the results
# to a filehandle.
#------------------------------------------------------------------
$DSCall = $pgm . " " . $login . " -logdetail " . $proj . " "
. $job . " " . $nbr;
open(DSLOG,"$DSCall |") ||
die "Cannot open DSLOG to get log records: $!";
while ($line = <DSLOG>)
{
#------------------------------------------------------------------
# If a Detail Message ( A line of msg text)
# The first field is a tab.
#------------------------------------------------------------------
if ($line =~ /^[\t]/)
{
#------------------------------------------------------------------
# Convert all ';' to '; ' ( DataStage stacks info with a ;
# creating lines too long to wrap ) and print the output.
#------------------------------------------------------------------
$line =~ s/;/;\040/g;
print wrap("", "\t", "$line\n");
}
$count++;
}
close DSLOG;
#------------------------------------------------------------------
# If no lines are processed assume the command was bad and abend
#------------------------------------------------------------------
if ( $count == 0 )
{
sleep 5; # Allow for last write
print "$PGM: printDetail: ERROR - Bad Datastage command\n";
printf "$PGM: Command: %s\n", encryptPassword($DSCall);
exitRoutine(8);
}
}
I've given the script which retrieves the log.Can any one please help?
Paul,
I am not sure of the universe tables and the relationship between them.
can u explain how we can retrieve that information using basic.i mean the details about the sequence job and all the server jobs related to it.
sub processLog
{
$columns="";
use Text::Wrap qw(wrap $columns); # Set up output wrapping
$columns=76;
$NBR=""; # DS Msg Number
$TYPE=""; # DS Msg Type
$DATE=""; # DS Msg Date/time
#------------------------------------------------------------------
# make local copies of the passed parameters
#------------------------------------------------------------------
local ($pgm, $login, $proj, $job, $detail) = @_;
#------------------------------------------------------------------
# Get an array of DS summary log messages
#------------------------------------------------------------------
@dslog = getSummaryLog($pgm, $login, $proj, $job);
#------------------------------------------------------------------
# Process each message in the log.
#------------------------------------------------------------------
foreach ( @dslog )
{
chomp($_);
#------------------------------------------------------------------
# If a Summary Message ( A line of msg text)
# The first field is a tab.
#------------------------------------------------------------------
if ($_ =~ /^[\t]/)
{
#------------------------------------------------------------------
# Convert all ';' to '; ' ( DataStage stacks info with a ;
# creating lines too long to wrap )
#------------------------------------------------------------------
$_ =~ s/;/;\040/g;
#------------------------------------------------------------------
# If there are more lines in the detail get them now
#------------------------------------------------------------------
if ($_ =~ /\(...\)/)
{
#------------------------------------------------------------
# If it is a link report print only is there is an error
#------------------------------------------------------------
if ($_ =~ /LinkReport/)
{
#-------------------------------------------------------
# If it is a link report print only is there is an error
# $detail parm is greater that 4.
#-------------------------------------------------------
if ($detail > 4 )
{
print "Link Report: \n";
printDetail($pgm, $login, $proj, $job, $NBR);
}
else
{
print wrap("", "\t", "$_\n");
}
}
else
{
printDetail($pgm, $login, $proj, $job, $NBR);
}
}
else
{
print wrap("", "\t", "$_\n");
}
print "--------------------------------------";
print "--------------------------------------\n";
}
else
#------------------------------------------------------------------
# If a Summary Header (Msg Number, type and date)
#------------------------------------------------------------------
{
($NBR, $TYPE, $DATE) = split(/\t/,$_);
print wrap("", "\t", "NO: $NBR\tTYPE: $TYPE\tDATE: $DATE\n");
}
}
}
#****************************************************************************
# 6.1 Subroutine getSummaryLog
#
# Builds and array of summary log messages from datastage for the last
# execution of a specific job.
#
# dsjob -file .DSnetrcn bcrs8 -logsum dsproj jobname
#
# Parameters:
# $_[0] - The datastage pgm full path
# $_[1] - login information
# $_[2] - the DataStage Project
# $_[3] - the DataStage Job
#
# Returns: None
#
#****************************************************************************
sub getSummaryLog
{
$ending_sub = 0; # Index for ds log array
@dslog = (" ", " "); # an array of log messages
$prevline = " "; # Temp holds previous line
$line = ""; # Temp holds current line
local ($pgm, $login, $proj, $job ) = @_; # copy lines
#------------------------------------------------------------------
# execute the following DataStage command and pipe the results
# to a filehandle.
#------------------------------------------------------------------
$DSCall = $pgm . " " . $login . " -logsum " . $proj . " " . $job;
open(DSLOG,"$DSCall |") ||
die "Cannot open DSLOG to get log records: $!";
#------------------------------------------------------------------
# Load an array of selected log lines. The log can contain many
# executions of the job. Only load the messages from the latest
# execution. The string 'Starting Job' will cause the logic to
# start loading at the beginning of the array
#
# The log summary function passes two entries for each entry in
# the datastage log. The first line contains the line number,
# the date, and the time. The second line contains the text of
# the log message.
#------------------------------------------------------------------
while ($line = <DSLOG>)
{
if ($line =~ /Starting Job/)
{
#--------------------------------------------------------------
# 'Starting Job' always on 2 line of pair so put the previous
# line in the first position of the array and set index to next
# position
#--------------------------------------------------------------
$dslog[0] = $prevline;
$ending_sub = 1;
}
#--------------------------------------------------------------
# Move line to next position in array, save off the line in
# case of a new execution of the job and increment the index
# to the next array position.
#--------------------------------------------------------------
$dslog[$ending_sub] = $line;
$prevline = $line;
$ending_sub++;
}
close DSLOG;
#------------------------------------------------------------------
# If no lines are processed assume the command was bad and abend
#------------------------------------------------------------------
if ( $ending_sub == 0 )
{
sleep 5; # Allow for last write
print "$PGM: getSummaryLog: ERROR - Bad Datastage command\n";
printf "$PGM: Command: %s\n", encryptPassword($DSCall);
exitRoutine(8);
}
return @dslog
}
#****************************************************************************
# 6.2 Subroutine printDetail
#
# Prints the detail portion of log message.
#
# dsjob -file .DSnetrcn bcrs8 -logsum dsproj jobname
#
# Parameters:
# $_[0] - The datastage pgm full path
# $_[1] - login information
# $_[2] - the DataStage Project
# $_[3] - the DataStage Job
#
# Returns: None
#
#****************************************************************************
sub printDetail
{
local ($pgm, $login, $proj, $job, $nbr ) = @_; # copy parms
$line=""; # temp to keep line
$count=0; # keep count of lines
#------------------------------------------------------------------
# execute the following DataStage command and pipe the results
# to a filehandle.
#------------------------------------------------------------------
$DSCall = $pgm . " " . $login . " -logdetail " . $proj . " "
. $job . " " . $nbr;
open(DSLOG,"$DSCall |") ||
die "Cannot open DSLOG to get log records: $!";
while ($line = <DSLOG>)
{
#------------------------------------------------------------------
# If a Detail Message ( A line of msg text)
# The first field is a tab.
#------------------------------------------------------------------
if ($line =~ /^[\t]/)
{
#------------------------------------------------------------------
# Convert all ';' to '; ' ( DataStage stacks info with a ;
# creating lines too long to wrap ) and print the output.
#------------------------------------------------------------------
$line =~ s/;/;\040/g;
print wrap("", "\t", "$line\n");
}
$count++;
}
close DSLOG;
#------------------------------------------------------------------
# If no lines are processed assume the command was bad and abend
#------------------------------------------------------------------
if ( $count == 0 )
{
sleep 5; # Allow for last write
print "$PGM: printDetail: ERROR - Bad Datastage command\n";
printf "$PGM: Command: %s\n", encryptPassword($DSCall);
exitRoutine(8);
}
}
You guys need to add code tags with Alt C. Here is the SQL.
At UNIX you need to quote it right and add uv in front of it. You will need to be in the Project directory. You want only the lines where DEPEND_TYPE is 0. I do not know how to add this to the where clause because it is multivalued. Let Ray explain.
Code: Select all
SELECT
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',32,'X')" AS DEPEND_TYPE FMT '3R',
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L'
FROM
DS_JOBS
WHERE
NAME = 'SeqMemberDim'
;
Mamu Kim
thanks kim!
is there any way we can do this in the script ? can i execute this SQL from a script, will i require any driver for it? or is there any other way of achieving my goal from script? the reason why i want to this from a script is that , my company want's to restrict the use of basic routines, which they believe are difficult to maintain.
also is there any place or docs where i can find information on datastage's internal tables like ds_jobs etc. and relationships between these tables.
Thanks and Regards
dhiraj
is there any way we can do this in the script ? can i execute this SQL from a script, will i require any driver for it? or is there any other way of achieving my goal from script? the reason why i want to this from a script is that , my company want's to restrict the use of basic routines, which they believe are difficult to maintain.
also is there any place or docs where i can find information on datastage's internal tables like ds_jobs etc. and relationships between these tables.
Thanks and Regards
dhiraj
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The "driver" is the uv command. For example, assuming that the DataStage bin directory is in PATH and that your current working directory is the project directory:
Metadata for the DataStage repository tables is not in the public domain; these definitions are deliberately withheld by the vendor, who reserves at all times the right to change them (and has done, in the past). The limited metadata that are available can be viewed with the LIST.DICT command (This is not an SQL statement, so does not have a terminating semi-colon.)
Some of them can be determined by systematic hacking (by someone with UniVerse database skills). It was by this means, for example, that it was determined that the "is multi-instance" flag was at field number 59 in the ROOT record for the job within the DS_JOBOBJECTS table.
The answer to the obvious next question is no.
Code: Select all
uv "SELECT COUNT(*) FROM DS_JOBS WHERE NAME NOT LIKE '\\%';"
Code: Select all
LIST.DICT DS_JOBS
Some of them can be determined by systematic hacking (by someone with UniVerse database skills). It was by this means, for example, that it was determined that the "is multi-instance" flag was at field number 59 in the ROOT record for the job within the DS_JOBOBJECTS table.
The answer to the obvious next question is no.
Last edited by ray.wurlod on Fri Aug 20, 2004 12:32 am, edited 1 time in total.
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.
Thanks Ray. Thanks Kim.
1) Can u please explain the query which retrieves the names of all linked jobs to a given sequence job, given below
SELECT
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',32,'X')" AS DEPEND_TYPE FMT '3R',
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L'
FROM
DS_JOBS
WHERE
NAME = 'SeqMemberDim'
i understand that trans funtion is used to retrieve a value of field in a hash table. but then what does
'J\':@RECORD<5>:'\ROOT'
mean?
are we trying to join the two tables?
2) is there any way by which i can filter the resulting records based on DEPEND_TYPE =0 in the sql itself. (as these are multivalued columns)
3) How do we find out what version of universe, datastage is running on?
I am using datastage V7.1
thanks a lot,
Dhiraj
1) Can u please explain the query which retrieves the names of all linked jobs to a given sequence job, given below
SELECT
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',32,'X')" AS DEPEND_TYPE FMT '3R',
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L'
FROM
DS_JOBS
WHERE
NAME = 'SeqMemberDim'
i understand that trans funtion is used to retrieve a value of field in a hash table. but then what does
'J\':@RECORD<5>:'\ROOT'
mean?
are we trying to join the two tables?
2) is there any way by which i can filter the resulting records based on DEPEND_TYPE =0 in the sql itself. (as these are multivalued columns)
3) How do we find out what version of universe, datastage is running on?
I am using datastage V7.1
thanks a lot,
Dhiraj
Code: Select all
SELECT
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',32,'X')" AS DEPEND_TYPE FMT '3R',
EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L'
FROM
DS_JOBS
WHERE
NAME = 'SeqMemberDim'
;
2) You need to use a WHEN to filter multivalues. I have never tried a WHEN in a SQL SELECT in Universe. A pick style select would do it but then you would need to associate the DEPEND_TYPE and the DEPEND_JOBS. I would have to look that up. Ray posted ASSOC on EVAL statements awhile back. I tried adding it to the WHERE and it did not work. Do a search for ASSOC with Ray as the author.
Pick style
Code: Select all
LIST DS_JOBS EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',32,'X')" AS DEPEND_TYPE FMT '3R' EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L' WITH NAME = "SeqMemberDim" AND WHEN DEPEND_TYPE = "1"
3) The Universe is version 9. I am not sure exactly which minor version. It is my understanding they will not upgrade to version 10.
Mamu Kim
I tried several ways and Universe breaks down.
This is the closest I could get.
should work but gives:
You want this then you need to create dictionary items for DEPEND_TYPE and DEPEND_JOB.
This is the closest I could get.
Code: Select all
SELECT
DS_JOBS.NAME,
DS_JOBS.CATEGORY,
EVAL DS_JOBOBJECTS."@RECORD<32>" FMT '3R',
EVAL DS_JOBOBJECTS."@RECORD<31>" FMT '35L'
FROM
DS_JOBS,
DS_JOBOBJECTS
WHERE
DS_JOBS.NAME = 'SeqMemberDim'
and DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO
and DS_JOBOBJECTS.@ID LIKE '%ROOT'
;
Code: Select all
SELECT
DS_JOBS.NAME,
DS_JOBS.CATEGORY,
EVAL DS_JOBOBJECTS."@RECORD<32>" AS DEPEND_TYPE MULTI.VALUE FMT '3R',
EVAL DS_JOBOBJECTS."@RECORD<31>" AS DEPEND_JOBS MULTI.VALUE FMT '35L' ASSOC.WITH DEPEND_TYPE
FROM
DS_JOBS,
DS_JOBOBJECTS
WHERE
DS_JOBS.NAME = 'SeqMemberDim'
and DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO
and DS_JOBOBJECTS.@ID LIKE '%ROOT'
and DEPEND_TYPE = 1
;
Code: Select all
DataStage/SQL: Internal problem with ALIAS, submit GTAR.
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: