Restricting stages

[chulett]

Depends on what you are doing in the "subsequent stages", I would think. Other than that, what you describe can be handled by normal constraints.

You may want to look at the Link Paritioner and Collector stages if you are running 6.x, or put the subsequent stuff in another job.

-craig

[vmcburney]

Could you clarify your constriant? It sounds like you are only interested in making one decision and choosing a path based on that decision. If you use a constraint you will be running that statement against every row of data, it may be more efficient to run your condition statement in a sequence file or batch job and split your server job into two seperate jobs.

Create a routine that opens the input file, retrieves the first row, grabs the field and checks the value and returns a value that indicates which path needs to be taken.

Create a sequence job which starts with the retrieval routine which then links to a Condition stage, the condition stage checks the routines ReturnedValue then leads to two paths, each running a different job.


Vincent McBurney
Data Integration Services
www.intramatix.com

[sumitgulati]

Hi McBurney,

The suggestion given by you works. Now lets suppose a situation where we have to query a table (that is present in Oracle) continuously till the value of the flag(a column in the table) becomes 'Y'.
If the query returns flag value 'N' then we need to query the table again. If the flag value is 'Y' we have to trigger the subsequent Stages.
We have to achieve this in a single Job. Is it possible?

[vmcburney]

You could do this with a sleep command embedded in a loop. The following statements can be put inside a routine and retrieves the flag from an Oracle table in what I hope is a 10 minute loop. You can pass in the login details:

loop until strFlag = "N"
strLogin = dbLogin : "/" : dbPassword : "@" : dbName
Call DSExecute(Unix, "echo select flag from mytable | sqlplus " : strLogin, strFlag, Status)

if strFlag = "N" then sleep 600
repeat

You can add a bit of polish to it, send an info message every 60 minutes letting the operator know that the job is still running and waiting for a flag. Pass in the SQL details as parameters and reuse the routine elsewhere. Also need some error handling on that execute statement.


Vincent McBurney
Data Integration Services
www.intramatix.com

[sumitgulati]

Thanks McBurney. It worked.

Sumit

[vmcburney]

Appears as though your quotes are in the wrong spot.

strSQL = "echo select 'FLAG-'||flag from mytable; | sqlplus " : dbLogin : "/" : dbPassword : "@" : dbName

DSExecute(Unix, strSQL, strFlag, strStatus)


Vincent McBurney
Data Integration Services
www.intramatix.com

[ray.wurlod]

I suspect it's the way the operating system shell is working with the soft (double) and hard (single) quotes.

What happens if you execute:
echo "select 'FLAG-'||flag from mytable;" | sqlplus dbLogin : "/" : dbPassword : "@" : dbName
at the UNIX shell prompt?

Then what happens if you execute:
echo "select 'FLAG-'||flag from mytable;"
at the UNIX shell prompt?

Finally, what happens if you execute:
echo "select 'FLAG-'||flag from mytable;"
at the UNIX shell prompt? (Here, the hard quotes are "escaped".)

The answers to these questions will show what the shell is doing with the quote characters.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[ray.wurlod]

DSExecute is a subroutine, and therefore must be invoked with a Call statement, which your code lacks. The last line of your code should be:
Call DSExecute('Unix', Cmd, strFlag, strStatus)

It's not a bad idea to initialize the arguments that will return values. For example:
strFlag = ""
intStatus = 0
Call DSExecute('Unix', Cmd, strFlag, intStatus)

[ray.wurlod]

Never guess. Diagnose.
Include a statement in your code to log the command that is being executed. For example (after Cmd is assigned its value):
Call DSLogInfo("Command is " : Quote(Cmd), "Debugging")