ROUTINE IS GIVING ISSUE PLEASE HELP

[informdaya@gmail.com]

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H

FLAG=0
InRowCount=0
OutRowCount=0
RejRowCount=0


Call DSLogInfo('Running Routine', RtnValidateLinks)
Call DSLogInfo('Using Param' : Arg1,RtnValidateLinks)
JobName=Field(Arg1,';',1)
StgLink_Name=Field(Arg1,';',2)
JobHandle=DSAttachJob(JobName , DSJ.ERRFATAL)

JobStatus=DSGetJobInfo(JobHandle , DSJ.JOBSTATUS)
JobStartTimestamp=DSGetJobInfo(JobHandle , DSJ.JOBSTARTTIMESTAMP)
JobEndTimestamp=JobStartTimestamp

If JobStatus=3
Then
JobRunStat='PENDING'
End
Else
JobRunStat='COMPLETE'
End

If JobStatus<>3
Then
InStg_Name=Field(StgLink_Name,',',1)
InLnk_Name=Field(StgLink_Name,',',2)
InRowCount=DSGetLinkInfo(JobHandle,InStg_Name,InLnk_Name,DSJ.LINKROWCOUNT)
Call DSLogInfo('Row Count On InputLink=':InRowCount,RtnValidateLinks)

OutStg_Name=Field(StgLink_Name,',',3)
OutLnk_Name=Field(StgLink_Name,',',4)
OutRowCount=DSGetLinkInfo(JobHandle,OutStg_Name,OutLnk_Name,DSJ.LINKROWCOUNT)
Call DSLogInfo('Row Count On OutPutLink=':OutRowCount,RtnValidateLinks)

JobEndTimestamp=DSGetStageInfo(JobHandle,OutStg_Name,DSJ.STAGEENDTIMESTAMP)

End

Command="db2 connect to ":DBName:" USER " :DBUser:" USING " :DBPassword
Call DSExecute("UNIX",Command,OsOutput,OsStatus)

If OsStatus<>0 then
GoTo ExitProc
End

SQLStatement1="insert into table values('":JobName:"','":JobStartTimestamp:"','":JobEndTimestamp:"','":JobRunStat:"',":INT(InRowCount):",":INT(OutRowCount):")"

SQLStatement2="commit"

Command1 = 'db2 -x ' : '"' : SQLStatement1 : '"'
Call DSExecute("UNIX",Command1,OsOutput1,OsStatus1)

Command2 = 'db2 -x ' : '"' : SQLStatement2 : '"'
Call DSExecute("UNIX",Command2,OsOutput2,OsStatus2)

ExitProc:
Ans=FLAG

when i enter
arg1 value in '' or in "" its giving error
if i enter without single or double quotes its working.

But i must enter it through single or double quotes because routine activity must take this through single or double quotes. Please help its urgent.
thanks.

[chulett]

While I'm sure people can puzzle it out, please start by detailing what you pass in for "arg1" and what the error is that you get so we don't have to. I'd also be curious why you set a FLAG to zero and pass it out as the ANS without ever changing it to a non-zero value when there's a problem.

[informdaya@gmail.com]

Now routine is excuting when i am testing. But when i put this to routine activity stage it is not working. Please help.

[Mike]

If your issue is that you want to include single or double quotes in a quoted string, check out the DQuote and SQuote string functions.

Mike

[informdaya@gmail.com]

Hi Mike,
Now routine is working when i am testing.
But when i am testing in a routine activity stage with same parameter its not working.

please help.
logs:
Arg1 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
Arg2 = 'XXXXX'
Arg3 = 'XXXXX'
Arg4 = 'XXXXXX'

Test completed.

Program "DSLogInfo": Line 52, Variable "CallerName" previously undefined. Empty string used.
DSLogInfo called from :
Message to be logged is...
> Running Routine
Program "DSLogInfo": Line 52, Variable "CallerName" previously undefined. Empty string used.
DSLogInfo called from :
Message to be logged is...
> Using Param 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
Program "DSLogInfo": Line 52, Variable "CallerName" previously undefined. Empty string used.
DSLogInfo called from :
Message to be logged is...
> Row Count On InputLink=2
Program "DSLogInfo": Line 52, Variable "CallerName" previously undefined. Empty string used.
DSLogInfo called from :
Message to be logged is...
> Row Count On OutPutLink=2
Result = 0

[ray.wurlod]

Variable RtnValidateLinks has not been assigned a value. You use this variable as the second argument in your DSLogInfo() calls. Were you to double click the Result cell in your Test you would see the same error messages.

[informdaya@gmail.com]

Hi Ray,
the warnings already resolved now.
Now my concern is i am not able excute it in routine activity stage.

i can test the routine and output is being written to database.

I am not able to find out why this routine is not executing in routine activity stage.

if i need to trace it in some place/file just let me know.

[ray.wurlod]

What do you mean by 'not executing'? Add an entry to the Logging Text field of the Routine activity, this will cause an event to be logged when the Routine Activity is invoked. If it is not being invoked then you have to determine whether any upstream trigger is preventing it from being invoked. If it IS being invoked, please report any other events logged by the Routine activity in the job log of the Sequence.

Also please report what you see when you double click on the Test cell of the test grid for the routine.

[chulett]

I removed your re-post of this issue - please continue your conversation here.

[informdaya@gmail.com]

See routine is executing and finishing sucessfully.
But in routine i am inserting one record per job execution.
So,here record is not inserted for this case[routine called from activity stage].

But if i test with passing proper arguments,record is being inserted in database.

Why could be the issue? in routine activity its triggering from "unconditional" from a job activity. I am puzzled by this datastage behaviour.

[chulett]

Add some calls to DSLogInfo() after your sql statements run, right now you are doing ZERO error checking there.

[informdaya@gmail.com]

I am added several DSLogInfo() in my code.
In place i have added it for OsStatus code.

During Routine Testing I am getting OsStatus return code =0
But in Routine Activity I am getting OsStatus return Code=4 ,
means DB2 command or SQL statement error . Iam still not able to find root cause of it.

[chulett]

Good luck, not much people here can do to help on that one.

I for one wouldn't be using the routine to interface with a database. Why not simply pass back the information you get from the API calls and then use a DB2 stage to process them into your audit table?

[informdaya@gmail.com]

i am not sure..what is going wrong.
how can i capture job statistics in table.
i was suppose call this routine everytime,whenerver one job call and it will capture job statistics in our table.

[ray.wurlod]

Put some DSLogInfo() calls in to report what the SQL statement is, and what the exit status of each db2 command is.

Check that DB2INSTANCE is correctly set.

Try using both db2 commands in a single call to DSExecute.

Code: Select all

Call DSExecute("UNIX", Command1 : " && " : Command2, Output, ExitStatus)