DSXchange

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="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

What i did was to keep the SQLS seperated by ';' in a text file and then used a sequence to read the file ( with ; as delimiter) and trigger a loop based on the number of records read. the loop ran a job whose only purpose was to fire the SQL to database.

wahi80 wrote:

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

Hi,
Check out the DRS stage it has an option for user-defined query file

Regards
Wah

have you ever tried running more than one SQL through DRS stage?

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

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

is DRS means Dynamic RDBMS?

The SQLs i have in the file will need to run with different tables. It is not for a single table. Does it works?

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.

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?

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?

Or...
And you want one row, not 'at least one', unless you want to (possibly) run the proc multiple times.

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.

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.