Sequence job aborting

prasson_ibm · Post by **prasson_ibm** » Wed Mar 13, 2013 3:49 am

Hi,
This issue which i have posted is discussed many times in forum and i have applied some suggestion,but it seems it does not work for me.

I have a sequence which running a job(Multiple Instence) from where i get the BATCH_ID,and i am passing valune of batch_id as parpameter to rest of downstream jobs in a sequence.For this i am calling server routine in Basic transforme GetUserStatus which calling the functions DSSetUserStatus.

While passing userstatus to parameter,i am writing below code:-

Code: Select all

P_BATCHID=Trim(Convert(@FM,"",Sp_Gen_Seq_Num_Job.$UserStatus))

Yesterday when i ran this sequence, it ran successfully but today all sequences are again aborting with error message:-

Code: Select all

Exception raised: @Main_Job, Error calling DSSetParam(P_BATCHID), code=-4 [ParamValue/Limitvalue is not appropriate]

Can someone help me to resolve this issue.

ShaneMuir · Post by **ShaneMuir** » Wed Mar 13, 2013 3:58 am

What value are you passing the parameter P_BATCHID and what data type is the parameter?

prasson_ibm · Post by **prasson_ibm** » Wed Mar 13, 2013 4:01 am

Hi,
Datatype of P_BATCHID is Bigint and value i am storing into memory by calling routine and passing to downstream jobs using userstatus activity variable.

ShaneMuir · Post by **ShaneMuir** » Wed Mar 13, 2013 4:44 am

Does the routine only select the one value?

Is it possible to pass out the value that is being passed to the log?
Can you put an execute command stage in and just echo the parameter value as it passed to the job?

Does any part of the sequence past the running of the routine run correctly?

Is it always the same job which fails?

chulett · Post by **chulett** » Wed Mar 13, 2013 7:12 am

As noted, calling DSLogInfo() in the routine to record the value being passed will go a long way towards solving this.

prasson_ibm · Post by **prasson_ibm** » Wed Mar 13, 2013 7:26 am

Hi,
Actually the job which is calling the routine DSSetUserStatus,is multiple instence job,so sometimes if i run only one sequence,its working fine,correct batch_id is passed to downstrean jobs but when i run multiple sequences at one time,which will trigger the job having routine with different invocation id,is giving me the issue.As a result either sequence is aborting with above error or wrong batch id is passed to downstream jobs.

So here i have a question,can we call routine with DSSetUserStatus with multipe instence?

chulett · Post by **chulett** » Wed Mar 13, 2013 7:35 am

Doesn't really answer why the error is being thrown unless the value is being corrupted somehow. And you'd only know that if you log it.

As to your question - yes, you can call it in a MI job. However, should you? Keep in mind the fact that there's only really one job under the covers and if you run multiple instances at the same time you'll only end up with the most recent value there. So I'd suggest you find another mechanism.

prasson_ibm · Post by **prasson_ibm** » Wed Mar 13, 2013 8:07 am

Hi Cullet,
Thanks for your reply and i also relaized that i have to change the mecanism...

Now i am writing batch_id to a flat file and then using execute command i am extracting it and then using CommandOutput activity variable,i am passing batchid to downstream stages and its working perfrectly fine now.

so do i still need to apply below code to P_BATCHID parameter?

Trim(Convert(@FM,"", TransType_Exec.$CommandOutput))

chulett · Post by **chulett** » Wed Mar 13, 2013 8:32 am

Yes. Of course you could always remove it and check for yourself.