CODEWITHSUNDEEP
sql-serveretldatastage
LatinCanuck
LatinCanuck

Reputation: 463

Datastage - run user defined sql query file using odbc connector

Using DataStage, I have to read a sequential file, which contains one sql statement, run that sql statement and output the results in a sequential file.

This is what I've tried : Using an Oracle connector, I simply set the option to "Read Selected statement from File", I entered the location of the file that contains the SQL stmt, then I set the "Runtime column propagation" and finally created an output sequential file. This worked great, the output file with the select stmt data results was created properly.

But now, the specs have changed and it is no longer an oracle connector I need but an ODBC connector which does not have the convenient "Read Selected statement from File" option.

I was thinking of doing the following :

The parts I'm having trouble with is part 1 to 4. I'm pretty much a beginner with datastage, if anyone can provide me with guidance it would be greatly appreciated.

Upvotes: 1

Views: 7039

Answers (1)

Ashish Devassy
Ashish Devassy

Reputation: 318

ODBC connector does have the option of reading the select statement from a file. What version of Datastage are you using ?

Another work around (which I consider Not the best of practices) is to put the query in a parameter Set and then call this parameter set in the Datastage job.

How you do it.

  1. Declare a parameter Set (lets say PS1) with one string variable Query
  2. Now you would need to login to the LINUX box (and go to the location of the parameter set It would look something like
    /opt/IBM/InformationServer/Server/Projects//ParameterSets/PS1>)
  3. Create a text file with your query in it. (lets call it ValueFile)
  4. Now on you ODBC connector - where you have the query - insert the parameter set variable (PS1.query)
  5. During run time you can select the value file.

I know its a little tricky , but should work fine.

Ashish

Upvotes: 1

Related Questions