Hey,
I have a project in which there is a base query where I extract data to a sequential file in Dstage. Then up must run 3 seprate queries on that data. What is the most efficient way to do this?
Thanks,
Steve
Query sequential files?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
It is difficult to answer that without knowing what queries are you trying to run.
You can use stage variables and define constraints. But it would be nice on your part if you could provide the type of queries that you would like to run on this sequential file. This would help us in giving you a better solution.
Thanks,
Naveen.
You can use stage variables and define constraints. But it would be nice on your part if you could provide the type of queries that you would like to run on this sequential file. This would help us in giving you a better solution.
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 11
- Joined: Tue Jan 10, 2006 2:33 pm
type? umm, i'm not sure what you mean by type. The sub queries have multiple select statements, where clauses and order by's.naveendronavalli wrote:It is difficult to answer that without knowing what queries are you trying to run.
You can use stage variables and define constraints. But it would be nice on your part if you could provide the type of queries that you would like to run on this sequential file. This would help us in giving you a better solution.
Thanks,
Naveen.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The "most efficient" way will depend on exactly what you want to achieve, whether the queries are run in the same database, and a number of other factors. For example, can you form the join (or union) of the three queries in the database itself? That would probably be very efficient, and you may not even need DataStage - just export the result set to a text file. Most databases have this functionality.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 11
- Joined: Tue Jan 10, 2006 2:33 pm
The point of having the base query run first and get extracted is to not have to run it(its a HUGE query) 3 times. It takes about 2 or more hours to run.ray.wurlod wrote:The "most efficient" way will depend on exactly what you want to achieve, whether the queries are run in the same database, and a number of other factors. For example, can you form the join (or union) of the three queries in the database itself? That would probably be very efficient, and you may not even need DataStage - just export the result set to a text file. Most databases have this functionality.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Hi slavik,
Then if you don't want to use the same stage for the other three queries that are to be run after the base query.
You can use a temporary table or also a unidata or universe stage to run the other three queries.
I have created a template for this kind of job. You can download it at:
http://s38.yousendit.com/d.aspx?id=2S ... ANCNE3F9
First the base query runs and loads the data in to the Universe stage, then you query this universe table with your first query and so on and finally load it to the sequential file.
But make sure that these should be in seperate jobs, not in one job like I showed. I mean, the OCI-->Universe will be a seperate job, Universe(query1)--->Universe in a seperate job and so on.
However, I'm not sure about the efficiency of this design when compared to running the union of all queries as our guru Ray suggested.
But incase you try this design. Please let us know how it performed
Thanks,
Naveen.
Then if you don't want to use the same stage for the other three queries that are to be run after the base query.
You can use a temporary table or also a unidata or universe stage to run the other three queries.
I have created a template for this kind of job. You can download it at:
http://s38.yousendit.com/d.aspx?id=2S ... ANCNE3F9
First the base query runs and loads the data in to the Universe stage, then you query this universe table with your first query and so on and finally load it to the sequential file.
But make sure that these should be in seperate jobs, not in one job like I showed. I mean, the OCI-->Universe will be a seperate job, Universe(query1)--->Universe in a seperate job and so on.
However, I'm not sure about the efficiency of this design when compared to running the union of all queries as our guru Ray suggested.
But incase you try this design. Please let us know how it performed
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE