Ouput from an ExecSh-run script as input to a job

Oli · Post by **Oli** » Fri Jul 15, 2005 2:19 am

Hi,

First time posting, I had a search around but couldn't find the answer to this one, hope someone can help!

I am using the ExecSh routine to run a shell script (which it turns executes sql). The script and routine work correctly, and in the DS Log I see:

STGLoadADCQuality..JobControl (ExecSH): Executed command: /opt/DataStage/ADM/scripts/adc_scripts/STGLoadADCQuality/get_normie_partition_name.sh 728
*** Output from command was: ***
NORMALISED_EVENT_P0190

-Which is the correct return value.

However I then want to use this value as input to the next job in the sequence. In the job properties I assign the job parameter to:

PARTITION_NAME Get_Billing_Table_Partition_Name.$ReturnValue

(Get_Billing_Table_Partition_Name is the name of the rountine I just ran).

However in the log I see that the value passed was in fact zero:

STGLoadADCQuality -> (STGLoadADCNormQuality): Job run requested
Mode (row/warn limits) = 0/50
Job Parameters --->
PARTITION_NAME=0
....
....
...

So it seems to me that the 'Output' of the script is not carried over to the 'ReturnValue' of the ExecSh routine.

Reading some posts I can imagine a workaround by creating a text file with the value and then reading that in later, but it would obviously be preferable to use the routine return value method for ease of maintenance in the future.

Thanks,
Oli

ArndW · Post by **ArndW** » Fri Jul 15, 2005 2:27 am

When you Execute a shell you actually have 2 return values, one is the return code (0 if successful) and the other is the contents of the standard output. Because ExecSh is a before/after subroutine it only returns the contents of the system return code.

You should use the Call DSExecute("UNIX", {YourScript}, StandardOutput, SystemReturnCode)

Then remove extraneous text around the StandardOutput variable so that it contains only the string you wish to use.

Oli · Post by **Oli** » Fri Jul 15, 2005 3:08 am

Thanks for that.

I created a custom routine of type transform function, and in the code I set

Ans = Output

It seems this solved the initial problem, i.e. now I can pass a value, but I have hit another error, which may be to do with datatypes of syntax. The job parameter I am setting is of type String, however I get the following error and summary output:

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

STGLoadADCQuality..JobControl (@Coordinator): Summary of sequence run
10:54:20: Sequence started
10:54:20: Get_Start_Batch_Load_Id (ROUTINE DSU.SubtractArguments) started
10:54:20: Get_Start_Batch_Load_Id finished, reply=728
10:54:20: StartLoop_Activity_2 loop iteration 728 started
10:54:20: Get_Billing_Table_Partition_Name (ROUTINE DSU.ExecSHWithReturnValue) started
10:54:21: Get_Billing_Table_Partition_Name finished, reply=NORMALISED_EVENT_P0190
10:54:21: Get_Billing_Table_Partition_Date (ROUTINE DSU.ExecSHWithReturnValue) started
10:54:21: Get_Billing_Table_Partition_Date finished, reply=2005-07-08
10:54:21: Count_unique_rows_ADC_and_STG (JOB STGLoadADCNormQuality) started
10:54:21: Exception raised: @Count_unique_rows_ADC_and_STG, Error calling DSSetParam(PARTITION_DATE), code=-4 [ParamValue/Limitvalue is not appropriate]
10:54:21: Sequence failed

Thanks for your help so far.

ArndW · Post by **ArndW** » Fri Jul 15, 2005 3:13 am

Oli,

what data type did you declare this parameter to be? And the output of the execute command will have more than just the one value, it will have a number of field-marks (which are the carriage returns and line feeds in the output) that you need to strip out in order to get a single string; did you do this?

Oli · Post by **Oli** » Fri Jul 15, 2005 3:26 am

Hi Arnd,

My parameter type is string.

I think it is probably like you say about stripping off the unwanted characters. Can you give me an example how to do that?

Currently I am just using

Ans= Trim(Output)

Also, I am finding it difficult to output any additional log information so I can see what the real value of Ans is. Is this the right syntax?

If Trim(Ans) <> "" Then
Message = DSRMessage("DSTAGE_TRX_I_0005", "<L>*** Output from Ans was: ***<L>", ""):Ans
End

ArndW · Post by **ArndW** » Fri Jul 15, 2005 3:33 am

If you are sure that you are only outputing the one string from your shell script, then EREPLACE(@FM,'',TRIM(Output)) should do it; this replaces all occurrences of the @FM (field mark) with empty -> any cr/lf in the returned string are representing as a field mark; and this also messes up the DataStage log files when you try to see the values.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 15, 2005 4:11 am

Do it in a job sequence rather than trying to use a before-job subroutine; your search will reveal that a running job cannot change its own parameter values.

In the job sequence use an Execute Command activity to execute the command. In the Job Activity that runs the job can set the parameter using the output from the command. When you click "Insert Parameter" it will become obvious how; the activity variable $OutputValue for the upstream Execute Command activity is available for you to use.

Oli · Post by **Oli** » Fri Jul 15, 2005 6:07 am

Hi Arnd and Ray,

I tried both methods, but the same error occurs for both.

Arnd - I added in the trim statement, and I get the same error.

Ray - I was in fact running using a sequence already, but I was using a Routine Activity linked to a Job Activity. However I took your advice and changed the routine activity to an Execute Command. I associated the parameter (type String) in the susbsequent job to the 'CommandOutput' of the Execute Command activity. In the log the command appears to work

STGLoadADCQuality..JobControl (@Get_partition_name): Executed: /opt/DataStage/ADM/scripts/adc_scripts/STGLoadADCQuality/get_normie_partition_name.sh 728
Reply=0
Output from command ====>
NORMALISED_EVENT_P0190

but I get the same error message when the CommandOutput is read into the job parameter. Here is the log output:

STGLoadADCQuality..JobControl (@Coordinator): Summary of sequence run
13:50:34: Sequence started
13:50:34: Get_Start_Batch_Load_Id (ROUTINE DSU.SubtractArguments) started
13:50:34: Get_Start_Batch_Load_Id finished, reply=728
13:50:34: StartLoop_Activity_2 loop iteration 728 started
13:50:34: Get_partition_name (COMMAND /opt/DataStage/ADM/scripts/adc_scripts/STGLoadADCQuality/get_normie_partition_name.sh) started
13:50:34: Get_partition_name finished, reply=0
13:50:34: Get_partition_date (COMMAND /opt/DataStage/ADM/scripts/adc_scripts/STGLoadADCQuality/get_normie_date.sh) started
13:50:35: Get_partition_date finished, reply=0
13:50:35: Count_unique_rows_ADC_and_STG (JOB STGLoadADCNormQuality) started
13:50:35: Exception raised: @Count_unique_rows_ADC_and_STG, Error calling DSSetParam(PARTITION_NAME), code=-4 [ParamValue/Limitvalue is not appropriate]
13:50:35: Sequence failed

Note that now the reply=0, but I assume this is the errorCode, which is ok.

I am hoping this is just a syntax issue, so here are the inputs I use to the Execute Command:

Command:
/opt/DataStage/ADM/scripts/adc_scripts/STGLoadADCQuality/get_normie_partition_name.sh

Parameters:
#StartLoop_Activity_2.$Counter#

and the parameter assignment in the job looks like:

Name: PARTITION_NAME

Value Expression: Get_partition_name.$CommandOutput

(Type - string)

ArndW · Post by **ArndW** » Fri Jul 15, 2005 6:10 am

Can you add a call to DSLogWarn(#"':CONVERT(@FM,'.',Output):'"','') to your program so that we can see the contents of Output?

Oli · Post by **Oli** » Fri Jul 15, 2005 7:00 am

Hi,

Fixed! For your info:

I got some compilation errors with the SDWarn, but I used these statements instead:

Message1 = DSRMessage("DSTAGE_TRX_I_0005", "<L>*** Output from Ans was: ***<L>", ""):Ans:Ans
Call DSLogInfo(Message1, RoutineName)
Message2 = DSRMessage("DSTAGE_TRX_I_0005", "<L>*** Output from Output was: ***<L>", ""):Output:Output
Call DSLogInfo(Message2, RoutineName)

And deduced that in fact the 'Ans' had two carriage returns in it!

STGLoadADCQuality2..JobControl (ExecSHWithReturnValue):
*** Output from command was: ***
NORMALISED_EVENT_P0190

NORMALISED_EVENT_P0190

I changed the replace statement according to the Documentation :
Ans = Ereplace(TRIM(Output), @FM,"")

And now the sequence runs, returning the correct value as input to the subsequent job.

Thanks so much for pointing me in the right direction.

Cheers,
Oli