Sequence Job - retrieving Logs

[dhiraj]

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

[kduke]

Dhiraj

Post your script please. I might be able to explain how to get all the jobs attached to a sequence.

[nkumar_home]

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

[datastage]

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.

[dhiraj]

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);
}
}

[kduke]

You guys need to add code tags with Alt C. Here is the SQL.

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'
;

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.

[dhiraj]

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

[ray.wurlod]

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:

Code: Select all

uv "SELECT COUNT(*) FROM DS_JOBS WHERE NAME NOT LIKE '\\%';"

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

Code: Select all

LIST.DICT DS_JOBS

(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.

[kduke]

If you cannot add this to your script then let me know. It should not be hard to parse with perl. I know I can do it in Korn shell. I do not know perl as well as Korn.

Ray is correct on everything listed. A lot of hacking made this possible and some good Universe skills.

[dhiraj]

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

[kduke]

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' 
; 

1) EVAL is used to create a dictionary item on the fly. TRANS is a BASIC function which will do a join on the fly. The ID of DS_JOBOBJECTS is like J\777\ROOT where 777 is the job number. The job number is stored in DS_JOBS in field 5. Therefore @RECORD<5> is the job number. Field 32 is the dependent type where a 1 is jobs. Field 31 is the job names. You could do an EVAL to do a normal join. This is more effecient. Ray posted an even more effecient method.

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"

You may need to change the single quotes to double. Try it.

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.

[kduke]

I tried several ways and Universe breaks down.

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
;

should work but gives:

Code: Select all

DataStage/SQL: Internal problem with ALIAS, submit GTAR.

You want this then you need to create dictionary items for DEPEND_TYPE and DEPEND_JOB.

[ray.wurlod]

Just off the top of my head, isn't MULTI.VALUE for RetrieVe and MULTIVALUED for SQL?

(They may be synonyms - I'm not in a position to check just now.)

[kduke]

The same error because they are synonyms.

[dhiraj]

what is an dictionary item?

"You want this then you need to create dictionary items for DEPEND_TYPE and DEPEND_JOB."

how do we create it ?

thanks,

dhiraj