DSXchange

Hi- We are using Datastage 8.1. I am using ODBC connector stage to read data. But am unable to see "Read Select statement from file" option in the stage. Is this option available only in later versions of Datastage but not in 8.1?

Thanks

I can't check at the moment but it isn't always a discrete option. In some stages you specify "User Defined" and then put "{FILE} <path to sql file>" in where the SQL itself would normally be. Should be documented if that's how it works there.

I have tried specifying the file path in the Select statement box of ODBC connector stage but it didn't work.
The Oracle connector has the specific option to pick the select query from a file. So, if am using ODBC connector is there any other way to pick the query from a file?

Only whatever is documented - did you check? And since I can't tell from your reply, you have to actually include {FILE} at the front of the pathname rather than just the path - did you? Or it might be "{FILE}=", don't recall off the top of my head.

I have mentioned this in the Select statement-

#SOURCE_QRY_PATH#/selectdynamic.sql

I am passing the source query path during runtime. As I have said this is working in Oracle connector but not in ODBC connector. Plz lemme know if this is correct?

As noted (twice now) you must include the text {FILE} before the pathname... otherwise all you have is an invalid SQL statement.

Examples here: viewtopic.php?t=124375

Thanks for the info. I have tried the below approaches but it's not working.

{FILE}#SOURCE_QRY_PATH#
{FILE}/#SOURCE_QRY_PATH#

Is there any other way to get this resolved?

Define 'not working'... what happens? What error message(s) do you see? What settings are you using in the stage?

I a using ODBC connector stage to read data rom oracle 10g table and writing the data into dataset.

ODBC Connector stage ---> Dataset

Select stmt in ODBC- {FILE}/#SOURCE_QRY_PATH#

Using Run time column propagation.

run time value passing to the parameter SOURCE_QRY_PATH - /home/dsadm/select.sql

Interesting... you managed to not answer any of the questions I asked.

Sorry. This is the error message am getting.

ODBC_Connector_80: ODBC function "SQLPrepare" reported: SQLSTATE = HY000: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver]General error. setStmtType failed

... and exactly what settings are you using in the stage?

am using normal connection settings, transaction record cnt- 2000, session array size- 2000 and am not defining any before/afte SQL.

I don't see anything in the documentation that indicates that the ODBC Connector supports reading SQL from a file. Unless someone here knows otherwise, time to involve your official support provider it would seem, see what they say your options are.