DSXchange

Hi,
i'd like to use an oraoci8 plug_in with the option "User Defined SQL Query File" because i have a very complicated query.
i set the options... but:
where can i specify the name and the directory of file with the query?

tanks in advance

Mario Loreti

eoyylo wrote:Hi,
i'd like to use an oraoci8 plug_in with the option "User Defined SQL Query File" because i have a very complicated query.
i set the options... but:
where can i specify the name and the directory of file with the query?

tanks in advance

Mario Loreti

:D, No such thing. You enter the query in the stage itself.

Ogmios

Sure there is! And yes, when you specify SQL Query File in the stage what you input is the full path to the file - in the same place that you would put any Custom SQL. You can also right-click on the stage and specify 'Grid Style', then where you put it is a little more obvious.

I believe you type the entire path (directory and filename) in the space provided (where you would normally type the sql)

Remember, the file will need to be on the DataStage server (not the client).

Never used it before, can't seem to find it in the Oracle Stage on my version. Under the SQL tab I only get "Generated", "User-defined", "Before", "After", and 2 DDL tabs (or I may need a new pair of glasses ).

Anyway I guess it would make moving jobs to production harder.

Ogmios

It's always been there as far as I remember... back to 3.5 anyways.

And no, not harder if you do it right - just one more thing to parameterize.

I've seen it used, it's handy if you want a DBA team to be responsible for your complex SQL queries. They own and maintain and optimise the SQL scripts and save them to a directory on the DataStage server, they don't need to do anything with with DataStage, they just have to make sure the column select list doesn't change. They can fix up statements without requiring a recompile of the DataStage job.

For simple queries I would avoid them, why have code sitting outside of DataStage? Just another thing to get lost or forgotten during delivery.

vmcburney wrote:For simple queries I would avoid them, why have code sitting outside of DataStage? Just another thing to get lost or forgotten during delivery.

I wholeheartedly agree! I've used it (once!) in the past for dynamically generated SQL - one job generates the SQL statement and writes it to a file and the next runs using that SQL File. Not something to be doing on a regular basis unless you've got no other way to manage it. IMHO, of course.

As a final (maybe) note, job parameters are not allowed in User-defined SQL Query files...