executing a set of sql queries in a job
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
executing a set of sql queries in a job
Is there a way that i can execute a set of sql queries from a single job. I mean i have the queries in a text file and i want to execute them on the oracle database. I can do that manually..but i want to create a job and keep it in the sequencer? i/p is file with queries and o/p stage should be database
Re: executing a set of sql queries in a job
[quote="kirankota79"]Is there a way that i can execute a set of sql queries from a single job. I mean i have the queries in a text file and i want to execute them on the oracle database. I can do that manually..but i want to create a job and keep it in the sequencer? i/p is file with queries and o/p stage should be database[/quote]
Hi,
Check out the DRS stage it has an option for user-defined query file
Regards
Wah
Hi,
Check out the DRS stage it has an option for user-defined query file
Regards
Wah
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Re: executing a set of sql queries in a job
have you ever tried running more than one SQL through DRS stage?wahi80 wrote:Hi,kirankota79 wrote:Is there a way that i can execute a set of sql queries from a single job. I mean i have the queries in a text file and i want to execute them on the oracle database. I can do that manually..but i want to create a job and keep it in the sequencer? i/p is file with queries and o/p stage should be database
Check out the DRS stage it has an option for user-defined query file
Regards
Wah
Re: executing a set of sql queries in a job
Yep DBAs in our team maintain a SQL in a file. So we just have to run that file. In DRS Stage you should define the option user-defined query file and the in sql part give FILE=path/filename
Regards
Wah
Regards
Wah
Re: executing a set of sql queries in a job
Im not sure how many sqls were there in the file, but I have definitely used DRS to run a query which is stored in the file
Also if I remember there is OCI stage in server edition which has similar option like DRS
Also if I remember there is OCI stage in server edition which has similar option like DRS
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
Place all these querries in a sql procedure and call the procedure using a stored procedure stage. The job design can be like ODBC stage to Store procedire stage. ODBC stage can have a sql query to start the job. The query can be select 1 from dual. Let me know if u have any questions on this.
Pranay
Seatte, WA
Seatte, WA
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
the first stage would be a ODBC stage with a user defined SQl. The SQL can be anything that returns atlest one row and can trigger teh job. Lets say it can be select 1 from Dual. The column defination can be a single column temp. Pass thsi column to the next stored procedure stage. in the stored proc use a input parameter whch can be mapped to thsi column. This parameter has to be used as a dummy. This stored proc can have all the sql scripts that u want to execute. The problem with this approch is that ur return value from teh database to datastage would be true always. and u have no way to verify from datastage that ur scripts ran fine.
Hope this explanation is clear to u.
Hope this explanation is clear to u.
kirankota79 wrote:as you said it should be like
odbc stage------> store procedure stage right?
Where you can use the sql procedure in the odbc stage? should i need to select user defined sql and include the procedure there?
Then what column definition i should use?
Or...
And you want one row, not 'at least one', unless you want to (possibly) run the proc multiple times.
Code: Select all
RowGenerator --> StoredProcedure
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
pranay wrote:the first stage would be a ODBC stage with a user defined SQl. The SQL can be anything that returns atlest one row and can trigger teh job. Lets say it can be select 1 from Dual. The column defination can be a single column temp. Pass thsi column to the next stored procedure stage. in the stored proc use a input parameter whch can be mapped to thsi column. This parameter has to be used as a dummy. This stored proc can have all the sql scripts that u want to execute. The problem with this approch is that ur return value from teh database to datastage would be true always. and u have no way to verify from datastage that ur scripts ran fine.
Hope this explanation is clear to u.
kirankota79 wrote:as you said it should be like
odbc stage------> store procedure stage right?
Where you can use the sql procedure in the odbc stage? should i need to select user defined sql and include the procedure there?
Then what column definition i should use?
do i need to create the procedure on the database first? Is it not possible to use the sql procedure directly in the stored procedure stage and run the job against the tables.
you need to create the stored proc in the database first and then call it through datastage.
kirankota79 wrote:pranay wrote:the first stage would be a ODBC stage with a user defined SQl. The SQL can be anything that returns atlest one row and can trigger teh job. Lets say it can be select 1 from Dual. The column defination can be a single column temp. Pass thsi column to the next stored procedure stage. in the stored proc use a input parameter whch can be mapped to thsi column. This parameter has to be used as a dummy. This stored proc can have all the sql scripts that u want to execute. The problem with this approch is that ur return value from teh database to datastage would be true always. and u have no way to verify from datastage that ur scripts ran fine.
Hope this explanation is clear to u.
kirankota79 wrote:as you said it should be like
odbc stage------> store procedure stage right?
Where you can use the sql procedure in the odbc stage? should i need to select user defined sql and include the procedure there?
Then what column definition i should use?
do i need to create the procedure on the database first? Is it not possible to use the sql procedure directly in the stored procedure stage and run the job against the tables.
Pranay
Seatte, WA
Seatte, WA
is ur problem resolved.
pranay wrote:you need to create the stored proc in the database first and then call it through datastage.
kirankota79 wrote:pranay wrote:the first stage would be a ODBC stage with a user defined SQl. The SQL can be anything that returns atlest one row and can trigger teh job. Lets say it can be select 1 from Dual. The column defination can be a single column temp. Pass thsi column to the next stored procedure stage. in the stored proc use a input parameter whch can be mapped to thsi column. This parameter has to be used as a dummy. This stored proc can have all the sql scripts that u want to execute. The problem with this approch is that ur return value from teh database to datastage would be true always. and u have no way to verify from datastage that ur scripts ran fine.
Hope this explanation is clear to u.
do i need to create the procedure on the database first? Is it not possible to use the sql procedure directly in the stored procedure stage and run the job against the tables.
Pranay
Seatte, WA
Seatte, WA