How to get child rows in DB2 for a specific subset of parent rows?

I have a parent row and child rows on 7-10 separate tables, with a unique key tying the rows on the child tables to the parent row. For a search feature, I need to retrieve the parent and all the matching child rows, based on a query against the data in the parent table. There are up to 5000 matching parent rows. Currently we are re-running the query against each of the child tables, like:

select data from child 
where key in (select key from parent where search_criteria)

This seems pretty inelegant especially as we continue to add more child tables. Is there a better way to do this in DB2?

开发者_如何学JAVA

Some of my thoughts:

• Will DB2 reuse the results of that select? What about if there is a high volume of queries?
• I know the keys after the parent query, so I could pass them in the SQL, but that SQL would get crazy with 5000 keys, and presumably exceed the SQL limit.
• Would it be worth it to store the keys as a temp table, or would the setup / teardown be excessive?

---

This sounds like an ideal scenario to use a RPG program and use a stored procedure that calls/consumes it. This way, you're not making so many round trips. Pass your search criteria to the stored procedure and the stored proc invokes the RPG program. Let RPG chain the 'files' together, which would speed up your query.