Datastage - ExecuteCommandActivty - ParamValue/LimitValue not Appropriate [Solved]

Being new to Datastage, I had myself pulling my hair out with this issue. 

Scenario: We have a file with a date in linux server which would act as the Extract Date for one of our ETL sources. 

So say we have this file, extractdate.txt in a specific folder in Linux Server and would contain a datetimestamp value like '01-JAN-2019 03:47:30 AM'. 

We would then read from this flat file using the 'Execute Command' activity and pass it on to  a parameter (ExtractDate) in the next sequential job activity load in a Sequencer Job. Something like below, 

Datastage Sequencer Job

Issue: The problem with the job now was that I was not able to successfully set the parameter in JobActivity with the output from the Execute Command Activity Step. 

Code in ExecuteCommand Activity was a simple cat command as below, 

cat /location/extractdate.txt

The output of the same, I am trying to write into a parameter in the Job_Activity object property as below,

While I try to execute with the above setup, I am facing the error below,

Error calling DSSetParam(prmCNIRTP), code=-4 [ParamValue/Limitvalue is not appropriate]

Solution: The solution is to rap the value expression in Job Activity as below instead of how I have given in the screenshot above,

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

This solved it for me. The above statement removes any special characters in the flat file. Special characters are highly likely when you read from flat files. 

References: 

1. https://tutel.me/c/programming/questions/15089213/paramvaluelimitvalue+is+not+appropriate+error+while+using+trim+function+in+datastage 

Filter column with '&' value in Oracle SQL Developer

We had a data dump into oracle, where in a column value had an '&' in it. Say for example a company name like 'Johnson & Johnson'.

What happens now is SQL Developer expects a substitution variable when it encounters an ampersand.

If you have a table and need to filter on the same, you can go with the below script

SELECT * FROM ORGANIZATION WHERE ORG_NAME IN ('Johnson '||chr(38)||' Johnson'

This should prevent SQL Developer from asking you for a substitution variable.

Script to terminate BODS Job on condition

Scenario: We have a metadata table for extract runs, before running an extract job, we need to check whether the previous run was successful. This can be done in Data Services using scripting.

Solution:

Use the following script

if ( sql('DS_Datastore_Name','SELECT EXTRACT_STATUS FROM EXTRACT_RUN WHERE EXTRACT_NAME ='Extract 1') != 'Completed'  )
begin
raise_exception_ext( 'Job terminated due to error in previous run. Correct the previous load and re-run again',1);
end

In the above code, we write a script where in we use an if condition and inside the if condition with the help of a SQL function, we write code to check if the previous run was completed or not.  If not completed, it goes inside the if condition where we have written the below code,

raise_exception_ext( 'Job terminated due to error in previous run. Correct the previous load and re-run again',1);

This code will make sure that a exception is raised and there by failing the job.