can i query on flat files

sunitha_cts · Post by **sunitha_cts** » Mon Jun 22, 2009 6:30 am

Hi,

I am using flat files as source ,can i do query on the flat files.
Thanks
Sunitha

miwinter · Post by **miwinter** » Mon Jun 22, 2009 6:32 am

Can you elaborate on 'query'?

If you mean, as in SQL query, no... you can't....

sunitha_cts · Post by **sunitha_cts** » Mon Jun 22, 2009 6:36 am

Hi,

Actuallu i am using as/400 coming in the form of flat files,so how could the source be taken i mean which stage or databse to do query in the flat files.
Thanhs
sunitha

sunitha_cts · Post by **sunitha_cts** » Mon Jun 22, 2009 6:38 am

Hi,

Query here means take wanted records do a transformation.
Thanks
sunitha

sunitha_cts · Post by **sunitha_cts** » Mon Jun 22, 2009 6:40 am

Hi,
then how can we query flat files.
Thanks
Sunitha

chulett · Post by **chulett** » Mon Jun 22, 2009 6:44 am

You don't "query" flat files, you read them. After that, you use whatever stage is appropriate for the "transformation", the original source doesn't matter at that point.

If you still need help, try explaining what exactly it is you need to do - without using the word "query". You might want to explain what "take wanted records" means, as well.

priyadarshikunal · Post by **priyadarshikunal** » Mon Jun 22, 2009 6:59 am

If you really want to query on flat file, you need an application which will parse your query apply them to a flat file??

can you think about one?

yes there are some databases (no need to say they also have limitations)

like Oracle- You can FTP it to oracle server and then define an external table pointing to that file.

[Channelling Craig]
Then you can query on external tables (flat files) to your heart's content. However you cannot query on flat files directly. You just read them.
[/Channelling Craig]

Thats exactly what Oracle does with the external tables. And its just changes the perception of any one. The same logic can be applied in datastage too. Read files and do the transformations,filteration and get the desired result.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Jun 22, 2009 7:04 am

Priyadarshi,

Using ODBC drivers for text files may be simpler option.

Creating external tables in Oracle is not straightforward. You have several hurdles to clear - such as create oracle directory, grant priv to the user, create external table ddl, grant select to the user.

Also this must be visible to the database rather than direct tns connection.

So not worth the hassle. I will recommend direct read and processing via DS.

sunitha_cts · Post by **sunitha_cts** » Mon Jun 22, 2009 7:04 am

Hi,

I am getting data in form of flat files , from as/400 where data is in form of flat files .This data has to be loded again into flat files in target ,but in between th source flat files and target flatfiles we have to perform transformation ,so how can we exactly apply the transformation ,if their is case we havwe to join two flat files ,how should we do.
Thanks
sunitha

chulett · Post by **chulett** » Mon Jun 22, 2009 7:07 am

Depends on what you mean by 'merge' so best to define that. Perhaps use one in a Lookup stage and join to the other in your input stream?

priyadarshikunal · Post by **priyadarshikunal** » Mon Jun 22, 2009 7:22 am

Sainath.Srinivasan wrote:Priyadarshi,

Using ODBC drivers for text files may be simpler option.

Creating external tables in Oracle is not straightforward. You have several hurdles to clear

Yes, using ODBC drivers is quite straight forward for text file specially csv files but oracle gives more flexibility. It was an example and there are many ways to achive it. But all depends on the requirement.

Also congrats for 2000 posts as well as premium poster logo with your name.

sunitha_cts wrote:I am getting data in form of flat files , from as/400 where data is in form of flat files .This data has to be loded again into flat files in target ,but in between th source flat files and target flatfiles we have to perform transformation ,so how can we exactly apply the transformation ,if their is case we havwe to join two flat files ,how should we do.

After reading flat files you can apply all kind of transformation on that data. As mentioned by Craig you can use join/lookup/merge.

Sreenivasulu · Post by **Sreenivasulu** » Mon Jun 22, 2009 11:31 am

As sainath said you can use flat tables as external table for query.But its little bit time consuming

Regards
Sreeni

Sreenivasulu · Post by **Sreenivasulu** » Mon Jun 22, 2009 11:32 am

As sainath said you can use flat tables as external table for query.But its little bit time consuming

Regards
Sreeni

ray.wurlod · Post by **ray.wurlod** » Mon Jun 22, 2009 5:29 pm

You can use them anywhere.

As with every other link*, the data end up in a virtual data set.

The only possible input to and the only possible output from an Orchestrate operator is a data set (virtual or physical).

* The single exception is a sparse lookup.