Server Routine

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Apy
Participant
Posts: 18
Joined: Fri Oct 14, 2016 6:26 am

Server Routine

Post by Apy »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Apy
Participant
Posts: 18
Joined: Fri Oct 14, 2016 6:26 am

Post by Apy »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Apy
Participant
Posts: 18
Joined: Fri Oct 14, 2016 6:26 am

Post by Apy »

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"
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
boxtoby
Premium Member
Premium Member
Posts: 138
Joined: Mon Mar 13, 2006 5:11 pm
Location: UK

Post by boxtoby »

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)
Bob Oxtoby
Apy
Participant
Posts: 18
Joined: Fri Oct 14, 2016 6:26 am

Post by Apy »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So says the "C" man. :wink: You really shouldn't have to explicitly do that here from what I recall.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Apy
Participant
Posts: 18
Joined: Fri Oct 14, 2016 6:26 am

Post by Apy »

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 :)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Apy
Participant
Posts: 18
Joined: Fri Oct 14, 2016 6:26 am

Post by Apy »

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?
Post Reply