Parameter Limit in Job Activities?

[iq_etl]

We've got a sequence containing a Job Activity that passes 7 parameters (with the last 5 being optional). This works fine.

However, once we add a 6th optional parameter (8 parameters total) the sequence fails to finish as it can't find the job to run in the Job Activity.

Is there a limit to the number of parameters that can be used in a Job Activity? Does adding too many cause a string of data to be too long and the job name can't be found?

Thanks!

[priyadarshikunal]

I don't think there is any limit and if there is, it won't be as low as this. What is the problem and what is the error.

[ArndW]

You are nowhere close to any limit on the number of parameters that a DataStage job may have.

Could you post the complete error message(s) to this thread?

[iq_etl]

ok, I think I now know what the problem is, so need a solution.

Our Job sequence has been designed to be dynamic so that our scheduler program can execute the sequence which will:
1) Job Activity (Pre_Row_Count): capture a pre-load count of the desired table using a dynamic DS job
2) Job Activity (Load_Job): run a DS job to delete and load a table
3) Notification Activity: if the first two have warnings.
4) Exception Activity: if any activity bombs

So, what is happening is Job Activity #2 has a number of optional parameters in it, but some jobs don't use them all. For example, if there is PARAM01, PARAM02, PARAM03, PARAM04, and PARAM05, but the current job only passes two parameters I get the following error:

[Sequence Name].[Job Name].JobControl (@Coordinator): Summary of sequence run
11:20:51: Sequence restarted after failure
11:20:51: Pre_Row_Count (JOB orcl_precount) skipped
11:20:51: Load_Job ([Job Name]) started
11:20:51: Exception raised: @Load_Job, Error calling DSSetParam(PARAM03), code=-3 [ParamName does not reference a known parameter of the job]
11:20:51: Exception handler started
11:20:51: Send_Exception_Email (ROUTINE DSSendMail) started
11:20:51: Send_Exception_Email finished, reply=0
11:20:51: Sequence finished OK

How can I allow the number of parameters to be optional?

Thanks!

[ArndW]

In this case, you cannot. You could always define your PARAM03 in the called job and not use it in that job.

[ArndW]

I should have said, you cannot do this in a job activity.

If you need a fine degree of control on your parameters, then you need to program it in BASIC yourself. Although the source code for what a notification activity is visible (go into your Sequence in the designer, open up the detail window and look at the job control tab), it is machine-generated with funky variable names that are hard to understand, but with a bit of looking you can see exactly which API calls you need to use.

[Developer9]

Observed the similar scenario:

I have master sequencer"SEQMaster" calls sub-sequencer "SQ001"

After I remove the parameter ($SAP_param) from the sequencer SQ001 and ran the master sequencer SEQMaster :

I got below message :
SEQMaster..JobControl (@SQ001): Controller problem: Error calling DSSetParam($SAP_param), code=-3
[ParamName does not reference a known parameter of the job]

Solution :After compiling the "SEQMaster" ..Sequence ran with no errors(above).

I guess this is do with the compiling so as to update its parameters in master sequencer SEQMaster.

Thanks

[iq_etl]

I should have said, you cannot do this in a job activity.

If you need a fine degree of control on your parameters, then you need to program it in BASIC yourself. Although the source code for what a notification activity is visible (go into your Sequence in the designer, open up the detail window and look at the job control tab), it is machine-generated with funky variable names that are hard to understand, but with a bit of looking you can see exactly which API calls you need to use.

I think this will be the solution. I would think that creating, say, 8 parameter variables in the Administrator Client for the project, I could then group the 8 into a parameter set in the project. Then, as developers are creating DS jobs that require 1-8 parameters, they can simply include that parameter set in their job.

What I'm guessing I'll also need to do is include that parameter set of 8 in the Job Activity. The tricky part will be messaging the source code to provide access to the 8 parameters in the parameter set given in the job Activity.

Is this making any sense? Seems doable.

[priyadarshikunal]

Or it may be a little bit tricky but you can assign the values to user variables in user variable activity, you can assign '' to them if not required and use user variable activity for parameter assignment in job activity.

[iq_etl]

Ok, so far pretty good, but I'm getting an odd error.

I've created a parameter set with PARAM01 - PARAM08 in it. My DS job is using this parameter set.

However, the sequence is bombing with the following error:

Exception raised: @Load_Job, Error calling DSSetParam(PARAM01), code=-3 [ParamName does not reference a known parameter of the job]

PARAM01 is in my parameter set, so I'm not sure why it isn't being found.

When I add PARAM01 in the 'Parameters' tab of the DS job, it is naturally found. That said, I want to avoid our developers from having to add PARAM01 - PARAM08 in each job just to get the sequence to work correctly. I'm hoping that any DS jobs that require optional parameters can simply include the parameter set with PARAM01-PARAM08 in it. This would save a lot of time.

Any ideas?

[asorrell]

Are you using parameterset.parametername as the syntax for the name? It's a guess since I can't test it right now, but as far as I know that's the syntax used for any parameter in a parameter set.

[iq_etl]

Thanks, all, we've got our one sequencer now executing our DS jobs whether they have parameters or not.

I added the 8 parameters to a parameter set that all of our jobs use, made sure they were being referenced correctly (thanks for the tip, Andy!), and all is good.

[kduke]

You can ask the job to list parameters with dsjob command or BASIC routines. Then do not set a parameter that is not in a job. I pretty sure I posted a script that does this. If not I could probably find it and post it.