DSXchange

Posted: **Thu Dec 15, 2016 11:42 pm**

Hi Guys,

I have written a server routine to capture DSJ.LINKROWCOUNT based on Input stage and link name.
Routine type is Before/After subroutine. I am calling this routine in Parallel job as Before/After routine and the job is getting aborted without any specific error mentioned in the log.

Question is
1) Can we test before/after subroutine before calling into the job?
2) If not, is there any way to test this routine where I can get the specific error in the routine code.
3) Any other feasible way to achieve this task.

Below is the code for your reference -

Code: Select all

ErrorCode = 0 ; * set this to non-zero to stop the stage/job 

$INCLUDE DSINCLUDE JOBCONTROL.H 
FileName = "Basepath/count.txt"

OpenSeq FileName to FileVar

Else

Create FileVar

Else

ErrorCode=1

WeofSeq FileVar

End
End

WeofSeq FileVar

Cnt= DSGetLinkInfo (DSJ.ME,"Stg_Xfr","Lnk_Cnt",DSJ.LINKROWCOUNT)

WriteSeqF cnt to FileVar
End

Posted: **Fri Dec 16, 2016 8:08 am**

I'm curious...

1) What is "Basepath"?
2) Are the double-quotes you included actually in the routine?
3) What ends up in the job's log? Can you copy/paste it here please?

Where you built the routine there should also be a 'test' button/option, I don't recall if it lets you test Before/After types but I don't see why it wouldn't. I'm also curious if this is a one-time thing, i.e. just for this job, or if you plan on hooking this on the end of every job.

Posted: **Fri Dec 16, 2016 12:50 pm**

Answer to your questions -

1) Basepath is nothing but the disk path where the count in the file "count.txt" will be captured.
2) No, Quotes are not part of the routine.
3) Can't give as of now. I will post that soon. Its just a abort message without any specific detail.

It doesn't enable Test button in case of Before/After routine. We can do that for Transform function routine. As of now, I have created this routine for testing purpose and calling in only one parallel job. code is in compiled state.

Hope this information helps.

Posted: **Fri Dec 16, 2016 1:50 pm**

I'll remove the quotes.

Answer #1 doesn't really help, it's obvious that it is a "disk path" but what is it exactly - a parameter you pass to the routine? Now, if the directory is literally called "Basepath" making that a relative path, I would emphatically suggest you switch to using a full path.

Posted: **Fri Dec 16, 2016 11:54 pm**

Answer #1, Its not a parameter but its a value. I have given "Basepath" in the code posted on forum since I don't want to mention the actual path used in the code. Anyway, this doesn't have anything to do with the functionality of the routine rather it is just to capture the count.

Answer #3, Message comes as "Warning" with the text "Attempting to cleanup ABORT raised in Job jobname..AfterJob"

Posted: **Sat Dec 17, 2016 7:43 am**

Sorry but of course it's part of validating the functionality of the routine. Would have been good to mention that you'd obfuscated your secret pathname and then I wouldn't even have mentioned it. Setting it up incorrectly could certainly cause the routine to fail, hence my question.

Once the job aborts, reset it and then look for a new message in the log labelled "From previous run...". Let us know if that shows up and what's in it.

Posted: **Sat Dec 17, 2016 6:51 pm**

There is no Test functionality for before/after subroutines. The usual way to test is to create a transform function routine that invokes the before/after subroutine.

Can you please format your code so that we can see whether there any mismatched statements?

Posted: **Mon Dec 19, 2016 10:44 am**

You ask "3) Any other feasible way...."

If you mean is there any other way of retreiving job stats you could look at the database XMETA.DSODB which contains all this information. DSODB is quite easy to understand and here's some SQL you can try:

(Hope it helps!)

Code: Select all

select fjr.runid, je.jobid, je.jobname, je.projectname, fjr.runstarttimestamp, fjr.runendtimestamp, fjr.runendtimestamp-fjr.runstarttimestamp as runtime,
link.linkname, link.totalrows
from 
-- Get latest version of the job
    (select max(compilationtimestamp) as compilationtimestamp
    from dsodb.jobexec
    where jobname = 'j010_Extract_PROPERTY_and_STREET'
    and projectname = 'SPRING_TW') mje
inner join dsodb.jobexec je
on mje.compilationtimestamp = je.compilationtimestamp
inner join
-- Get latest run of the job
(select jr.runid, jr.jobid, jr.runstarttimestamp, jr.runendtimestamp 
from 
    (select jobid, max(runendtimestamp) as runendtimestamp
    from dsodb.jobrun
    group by jobid) mjr
    inner join dsodb.jobrun jr 
    on mjr.runendtimestamp = jr.runendtimestamp) fjr
on je.jobid = fjr.jobid
-- Get output links
inner join
(select jrl.runid, jrl.linkid, jrl.totalrows, jl.linkname from dsodb.jobrunlink jrl
    inner join
    (select linkid, linkname from dsodb.joblink
     where upper(linkname) like 'LNKIN%' or upper(linkname) like 'LNKOUT%') jl
     on jrl.linkid = jl.linkid) link
on fjr.runid = link.runid
order by upper(link.linkname)

Posted: **Thu Dec 22, 2016 7:33 am**

Thanks Guys for the sharing the different approach to debug this.
I have the working code now.
Now adding few more question as I have added few more requirements

1) I am going to use this routine in multiple jobs, hence want to append the count instead of overwriting the file. Using SEEK command I am able to achieve this but it appends in the same line. Any idea how I can append the count in the next line?

2) Do I need to use WEOFSEQ and CLOSESEQ in this code? If Yes/No then please mention Why and where. Might be silly question but I don't have answer for this.

Code: Select all

ErrorCode = 0 ; * set this to non-zero to stop the stage/job 

$INCLUDE DSINCLUDE JOBCONTROL.H 
FileName = "Basepath/count.txt" 

OpenSeq FileName to FileVar 

Else 

Create FileVar 

Else 

ErrorCode=1 

End 
End 


Cnt= DSGetLinkInfo (DSJ.ME,"Stg_Xfr","Lnk_Cnt",DSJ.LINKROWCOUNT) 
SEEK FileVar,-1,2
Else
Call DSLogInfo("Append Unsuccessful" : DSJ.ME)
End
WriteSeqF cnt to FileVar Then 
End

Posted: **Thu Dec 22, 2016 7:42 am**

It's been quite a while but I believe you only need to use WEOFSEQ (write End Of File) when you want to 'truncate' a sequential file, typically from a specific spot in the file. You should always CLOSE any file you open and I believe your issue noted in #2 is from not properly closing the file.

Posted: **Thu Dec 22, 2016 8:40 am**

write the ascii value 10 or 13 or both after each line (depends on OS which is correct, but modern text editors really do not care which). I think you can use the byteval command (from memory?) to poke this into a string to write it out. There may be other ways to do that, this is one. I take the direct approach to such things.

Posted: **Thu Dec 22, 2016 8:59 am**

So says the "C" man.

You really shouldn't have to explicitly do that here from what I recall.

Posted: **Fri Dec 23, 2016 12:04 am**

So, the code works in a simpler way. I just did a small change and here it is....
Changed line :

SEEK FileVar,0,2

Since, I am writing into same file using different jobs at the same time, Is there any possibility that file might get locked. I have tested this scenario but did not get this issue. Just wanted to know your thoughts.

Anyway,Thanks for the wonderful suggestions

Posted: **Fri Dec 23, 2016 3:48 am**

The file is locked by OPENSEQ and the lock is released by CLOSESEQ.

Unless your OPENSEQ statement has a LOCKED clause any other invocation will wait until the lock is released.

Posted: **Fri Dec 23, 2016 6:43 am**

Just to clear my understanding, If LOCKED clause is not mentioned , any other invocation will wait until the file lock is released. Please confirm.

What if the LOCKED clause is mentioned? How other invocation will behave?
Is there any chance of abort for other job invocation due to same files accessed by multiple invocations at a time?

DSXchange

Server Routine

Server Routine