Search code examples
shelldatastage

DSSetParam -4 error when filling a parameter with a routine

I'm building a Sequence job that contains a UserVariables activity (ParamLoading) and a Job activity (ExtractJob), in that order. ParamLoading creates 4 user variables and invokes a routine to fill each one with the correspondng value, then invokes ExtractJob pasiing it the parametes previously loaded.

ParamLoading invokes a server routine (GetParams) which simply executes a shell script (ShellQuery) and captures the result; that shell script executes an SQL query against an Oracle database and prints the result on screen.

As far as tests go, ShellQuery works as expected and GetParams returns the expected value. But when GetParams is invoked from the sequence job (no matter if it's in ParamLoading or ExtractJob) the job fails with the following error:

Test2..JobControl (@JOB033_TBK_026_EXT_PTLF): Controller problem: Error calling DSSetParam(RUTA_ORIGEN), code=-4
[ParamValue/Limitvalue is not appropriate]

I've checked data types, parameter names, all, without success or even a message saying what might be happening.

Code of ShellQuery:

value=$(sqlplus -s $1/$2@$3/$4 <<!
        set heading off
        set feedback off
        set pages 0
        select param_value from cfg_params where filter='$5' and param_name='$6';
!)
echo $value

Code of GetParams:

  Call DSExecute("UNIX", Ruta_Programas:"getparams.sh ":Username:" ":Password:" ":Server:" ":ServiceId:" ":Filtro:" ":Parametro, Output, SystemReturnCode)
  Ans = Output
  Return(Ans)
Solution

  • Solved. For those struggling with a similar problem, GetParams was returning the captured value from ShellQuery adding a special character called "field delimiter", and given that the character is a 254 in ASCII, any job receiving the parameter would complain of an invalid value, which was the error.

    Changing the routine to the following solved it:

    Call DSExecute("UNIX", Ruta_Programas:"getparams.sh ":Username:" ":Password:" ":Server:" ":ServiceId:" ":Filtro:" ":Parametro, Output, SystemReturnCode)
Ans = EReplace(Output, @FM, "")
Return(Ans)

    Thanks to Matt Calderon for providing a clue for solving.