Exec. Command stage to import sequential file info

[a2love]

Hi,

I am trying to use Exec command stage to pull data into a parameter as a list for use in an SQL DB2 query.

My command is:

Code: Select all

type \\Nho04\myfilepath.txt

Which is found and has imported my data.

The query is as follows:

Code: Select all

SELECT CTR_POL_NUM, CTR_RSA_TOT_PREM
  FROM  DMIDBO.CTR_TRANSACTION                     
  WHERE CTR_SOURCE_SYSTEM IN ('HC' , 'HF')         
  AND CTR_POL_NUM IN (#ppFNDPolicyList#) 
GROUP BY CTR_POL_NUM                               
WITH UR                                            
;   

And I have successfully imported the data using the exec command stage, however it seems to not like putting the data into my above parameter (which is defined as a List).

Here is the error:

Code: Select all

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

Is what I am trying to do even possible?? Should I be using a script in the command line instead of just reading my sequential file? can this parameter handle multiple values?

Thanks for any help in advance,

Adam

[DSguru2B]

It has line terminators that are converted to @FM by DataStage. Do a field() and get the everything before the first @FM.

[chulett]

And I have successfully imported the data using the exec command stage, however it seems to not like putting the data into my above parameter (which is defined as a List).

Make it a String - List sounds highly inappropriate for what you are doing. Any value that you send in that is not in your List of valid values will generate the -4 error.

[a2love]

Ok that makes sense about the list, thanks for the input I was unaware of how exactly the list setting worked.

I have formatted my input .txt file to have all values on one line... i.e. '12345','54321',

This parameter is now defined as a string as well. I am still recieving the same error.

Where exactly/how should i use the format() command? this may be a dumb question but I'm pretty new.

And are there any alternate methods to this approach? I have recieved a suggestion to use routine activity with sql code to grab the information. Is that possible? If so, any tactics on how to accomplish that?


Thanks,
Adam

[chulett]

Now you need to follow DSGuru's advice re: the @FM characters. If you are using a Sequence job and $CommandOutput to populate the parameter, try changing it to $CommandOutput<1> and see if that does the trick by itself.

[a2love]

It seems to not like the <1>.

My expression is Execute_Command_8.$CommandOutput<1>

and it give me a compile error of 'Expected: Expression'.

I tried Execute_Command_8.$CommandOutput[1]
It compiled but errored on execution with the same error:

Code: Select all

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

[chulett]

What exact version of DataStage are you running?

[a2love]

7.5x2

[chulett]

I guess your version doesn't support that syntax. Try this instead and see if it will take it:

Code: Select all

EReplace(Execute_Command_8.$CommandOutput,@FM,"")

[a2love]

I can grab the data fine now...

Code: Select all

db2testing_exec_command..JobControl (@Execute_Command_8): Executed: type \\Nho04\micpriv\Adam\CPC_Datasets\ER_CPC_Unmatched_HUON_PolicyNumbers.txt
Reply=0
Output from command ====>
'0136530000','0322430008','0414643009','0532516008',

however I am still getting the same error. My parameter is defined as a string, yet the string I pass in using Exec. command seems to always give me this error:

Code: Select all

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

It seems my jobDB2FNDtest stage in my sequence cannot import the following into it's project parameter:

Code: Select all

Left(EReplace(Execute_Command_8.$CommandOutput,@FM,""), Len(Execute_Command_8.$CommandOutput)-1)

This above value expression did not yield any compile errors... but on run it caused that "not appropriate" issue. Any ideas?

[DSguru2B]

Your errors are due to the LF or @FM as i suggested before. If you can run the above without compilation errors, you should be able to run Field(). Also, dont do this at the triggers. Do it inside job activity while providing expression for your job parameter.