Sequence - Possibility of Shell Script

ds_search2008 · Post by **ds_search2008** » Wed Jan 07, 2009 7:08 am

In one of the scenarios there is a sequential file containing account codes

Sequential file Name: LKP_ACTCODE
File Data:

CA
SA
OA
...

I have 3 DS jobs
CurrentAccount, SavingsAccount, OtherAccount.

There is one sequence that includes all the 3 DataStage jobs. The sequence should call LKP_ACTCODE file then look for the data. If the data is CA the currentAccount job should run, SA then SavingsAccount...
Can you kindly let me know whether this scenario could be achieved using shell script?

chulett · Post by **chulett** » Wed Jan 07, 2009 8:17 am

Sure, shell script is one way. Use the Execute Command stage to run it and then as many triggers to Job Activity stages as you have values, each constrained to a particular value.

ds_search2008 · Post by **ds_search2008** » Thu Jan 08, 2009 5:23 am

Thanks for your time and reply Craig

I'm not sure how to insert the job design into this post

Job design:
Executecommand------->Job Activity1----
stage -------->Job Activity2---- Sequencer1
-------->Job Activity3---
The output link from all three job activity stages are directed to one sequencer stage.

In Execute Command Activity I have set the following:

Command : sh -c
Parameters: #path#/#catFname#

where path and Cat Filename are parameters set under Job parameters options.

In Job Activity--->Trigger Tab

I have set the
Expression Type as Custom (Conditional)
Expression :
Trim(Convert(@FM,"",StageName.$CommandOutput)) = "CA"

Similarly for all three job activity --Trigger tab-->Expressions with different account codes.

The sequence is working fine. But I'm facing one issue, the sequential file from where I'm looking up the account codes do not have a code CA for current account. However the current account job is running. I'm not sure what's wrong with my logic. Could you please advise.

Many Thanks

kandyshandy · Post by **kandyshandy** » Thu Jan 08, 2009 5:38 am

Look at the sequence log to see what is the parameter value passed to your current account job. That will assist you to find out the issue.

LenGreenwood · Post by **LenGreenwood** » Thu Jan 08, 2009 6:18 am

The trigger tab of a job activity stage is an expression that fires after the job has run. Sounds like your expressions need to be on the triggers of the Execute Command stage, not the job activity. Those fire after the command is executed, to decide whether execution proceeds to the next stage (the job activities, in this case).

ds_search2008 · Post by **ds_search2008** » Thu Jan 08, 2009 8:23 am

Sounds like your expressions need to be on the triggers of the Execute Command stage, not the job activity

Len thanks for your comments. I had already tried this option and I found that none of the jobs are running if I do so. Then I tried to set the expression within each job activity.

Look at the sequence log to see what is the parameter value passed to your current account job. That will assist you to find out the issue.

Kandy thanks for your suggestion. Only one job parameter is being passed through the jobs and I have tested each one of the jobs separately using the parameter and checked the job logs and sequence log. I don't see any warning or error messages in sequence log.

kandyshandy · Post by **kandyshandy** » Thu Jan 08, 2009 8:42 am

Like Len said triggers page of an activity is actually meant for the next activity to be executed.

After you run the sequence, you can see in the sequence log about the parameter values passed to the individual jobs in the sequence. So look what is the value passed to 'current account' job. If you are not familiar, then double click first entry of sequence log and then continue clicking next button to see every entry one by one.

I did not ask you look for warning/error messages.

chulett · Post by **chulett** » Thu Jan 08, 2009 8:54 am

Absolutely - those trigger expression need to be on the triggers from the Execute Command stage to each Job Activity stage, the triggers from there to the Sequencer should all be "OK" triggers.

Double-check the log entry for the Execute Command stage, post the contents of that log entry so we can see what value(s) it returned.

ds_search2008 · Post by **ds_search2008** » Thu Jan 08, 2009 9:58 am

Absolutely - those trigger expression need to be on the triggers from the Execute Command stage to each Job Activity stage, the triggers from there to the Sequencer should all be "OK" triggers.

Double-check the log entry for the Execute Command stage, post the contents of that log entry so we can see what value(s) it returned.

Craig, I have made the above said changes in my job design.

Execute command stage Log entries

SeqActJobs..JobControl (@EXC_Read_Seq_AcCode): Executed: sh -c /ajserver/inputs/docs_in/cat LKP_ACTCODE
Reply=127
Output from command ====>
LKP_ACTCODE: /ajserver/inputs/docs_in/cat: not found

SeqActJobs..JobControl (@EXC_Read_Seq_AcCode): Command sh -c did not finish OK, reply = '127'

SeqActJobs -> (SavingsAccount): Job run requested
Mode (row/warn limits) = 0/0
Job Parameters --->
$DBUserId=$PROJDEF
$DBPwd=$PROJDEF
$DBSchema=$PROJDEF
$DB=$PROJDEF
Cust_Id=10000
DSJobController=SeqActJobs

SeqActJobs..JobControl (DSRunJob): Waiting for job SavingsAccount to start
SeqActJobs..JobControl (DSWaitForJob): Waiting for job SavingsAccount to finish
SeqActJobs..JobControl (DSWaitForJob): Job SavingsAccount has finished, status = 1 (Finished OK)
SeqActJobs..JobControl (@SavingsAccount): Checkpointed run of job 'SavingsAccount'
SeqActJobs..JobControl (@Coordinator): Removed checkpoint record at successful completion of sequence
SeqActJobs..JobControl (@Coordinator): Summary of sequence run
10:19:40: Sequence started (checkpointing on)
10:19:40: EXC_Read_Seq_AcCode (COMMAND sh -c) started
10:19:40: EXC_Read_Seq_AcCode finished, reply=127
10:19:40: SavingsAccount (JOB SavingsAccount) started
10:19:43: SavingsAccount (JOB SavingsAccount) finished, status=1 [Finished OK]
10:19:43: Sequence finished OK
Finished Job SeqActJobs.

where
The sequence name : SeqActJobs
Name of the Sequential file containing account codes: LKP_ACTCODE
One of the job name that is included in sequence : SavingsAccount
Name of the Execute command stage : EXC_Read_Seq_AcCode

Now only one job in the sequence is running. Other jobs included in the sequence are found to be ignored.
In the sequence-->Job properties-->parameters I have used the parameters

$Path String $PROJDEF
catFname String cat LKP_ACTCODE(Default value)
Cust_Id Integer

In Execute command stage properties I have used
command: sh -c
Parameters: #$Path#/#catFname#

Kindly could you please look into this and advise.

Many Thanks

chulett · Post by **chulett** » Thu Jan 08, 2009 10:03 am

ds_search2008 wrote:SeqActJobs..JobControl (@EXC_Read_Seq_AcCode): Executed: sh -c /ajserver/inputs/docs_in/cat LKP_ACTCODE
Reply=127
Output from command ====>
LKP_ACTCODE: /ajserver/inputs/docs_in/cat: not found

SeqActJobs..JobControl (@EXC_Read_Seq_AcCode): Command sh -c did not finish OK, reply = '127'

You did notice this, yes? Correct your syntax and then let us know what happens.

SAMEENA · Post by **SAMEENA** » Thu Jan 08, 2009 10:14 am

Hi,

In Execute Command Activity please set the following:

Command : cat
Parameters: <path>/<filename>

<path> - the location where your file is stored.
<filename> - name of the file

In the trigger of the Execute command activity -
For Current Account ,
select expression type as custom and in expression put <Execute Command stage name>.$CommandOutput='CA'

Similarly define trigger for other two.

Note - This would work if your file has only one value in it at one time.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 08, 2009 3:28 pm

ds_search2008 wrote:
$Path String $PROJDEF
catFname String cat LKP_ACTCODE(Default value)
Cust_Id Integer

You can't use "cat LKP_ACTCODE" as a file name in a pathname. This is also the cause of the "LKP_ACTCODE not found" error.

ds_search2008 · Post by **ds_search2008** » Fri Jan 09, 2009 12:43 am

Now in the sequence-->Job properties-->parameters I have used the parameters

$Path String $PROJDEF
Fname String LKP_ACTCODE(Default value)
Cust_Id Integer

In Execute command stage properties I have used
command: cat
Parameters: #$Path#/#Fname#

Recent Job log
SeqActJobs..JobControl ((@EXC_Read_Seq_AcCode): Executed: cat /ajserver/inputs/docs_in/cat LKP_ACTCODE
Reply=0
Output from command ====>
CA

SeqActJobs..JobControl (@Coordinator): Note: Sequencer 'Sequencer' was entered, but never exited

SeqActJobs..JobControl (@Coordinator): Summary of sequence run
01:18:21: Sequence started (checkpointing on)
01:18:21: EXC_Read_Seq_AcCode (COMMAND cat) started
01:18:21: EXC_Read_Seq_AcCode finished, reply=0
01:18:21: CurrentAccount (JOB CurrentAccount) started
01:18:23: SavingsAccount (JOB SavingsAccount) started
01:18:25: CurrentAccount (JOB CurrentAccount) finished, status=1 [Finished OK]
01:18:26: SavingsAccount (JOB SavingsAccount) finished, status=1 [Finished OK]
01:18:26: Sequence finished OK

Hope I have almost done with this job. Currently the account code in LKP_ACTCODE file is "CA" my sequence runs two jobs CurrentAccount and SavingsAccount.

I checked the Execute command activity

Triggers

Name: ACTCD_CA
Expression Type: Custom(conditional)
Expression: Convert(@FM,"",EXC_Read_Seq_AcCode.$CommandOutput) = 'CA'

Name: ACTCD_SA
Expression Type: Custom(conditional)
Expression: Convert(@FM,"",EXC_Read_Seq_AcCode.$CommandOutput) = 'SA'

Name: ACTCD_OA
Expression Type: Custom(conditional)
Expression: Convert(@FM,"",EXC_Read_Seq_AcCode.$CommandOutput) = 'OA'

All the three job activities--->Triggers tab
output link names --Expression type are set to - OK - (Conditional)

I'm not sure why savingsaccount job is running in parallel.

LenGreenwood · Post by **LenGreenwood** » Fri Jan 09, 2009 4:51 am

Hmm - it sounds as if the sequence should now be designed to do what you want, so we are back to why 2 triggers fire & 2 jobs run instead of one. If you can post a DSX export of the sequence here I could take a look. Or, you might look yourself at the generated code which will be in the job control tabe of the job Properties dialog after the sequence is compiled - there should be a bit that says something like this:-

Code: Select all

**************************************************
L$V0S0$START:
*** Activity "Execute_Command_0": Execute command

....and the code that implements the triggers will be a bit further down.

By the way, that message that says:

SeqActJobs..JobControl (@Coordinator): Note: Sequencer 'Sequencer' was entered, but never exited

...is probably because the sequencer stage mode property is set to "All" and it should be "Any". ("All" is the default.)

ds_search2008 · Post by **ds_search2008** » Fri Jan 09, 2009 6:04 am

Thanks a lot Len.

The Sequencer mode is set to "Any" only. I have check it. However I'm receiving
SeqActJobs..JobControl (@Coordinator): Note: Sequencer 'Sequencer' was entered, but never exited
log message.

"job Properties dialog after the sequence is compiled

Now let me check this and see.

Many thanks for your time and help