Restricting stages
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
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
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
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?
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?
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
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
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
strSQL = "echo select 'FLAG-'||flag from mytable; | sqlplus " : dbLogin : "/" : dbPassword : "@" : dbName
DSExecute(Unix, strSQL, strFlag, strStatus)
Vincent McBurney
Data Integration Services
www.intramatix.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
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)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: