db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Goodacre <cgooda...@yahoo.com>
Subject Question about TableFunctions in Derby
Date Wed, 15 Jul 2009 03:35:44 GMT

I've read the Derby developer's guide and Rick Hillegas's informative white paper (http://developers.sun.com/javadb/reference/whitepapers/sampleTableFunctions/doc/TableFunctionsWhitePaper.html)
on Table Functions, but am still struggling with the following issue:

I am trying to create an RDB abstraction for a large CICS/VSAM-based legacy system and blend
it with our newer, RDB-based tier.  This seems like a good application of TableFunctions. 
The VSAM data is made available to me via an IP-based proprietary messaging interface.  There
are lots of different files here, but due to some historical forces, most of the data I'm
interested in resides in 4 VSAM files.

Unfortunately, each of those VSAM files has over a 1000 fields in it.

Now eventually, it might be possible to fully model a single VSAM file into (for the sake
of argument) 50 tables; each table/row representing a small slice of a single VSAM record.

In the meantime, for both this proof-of-concept and as a migration path to our existing clients,
I'd like to represent each VSAM file as a table (subject to the 1024 column SQL limitation
per table).  This will be a highly-denormalized and decidedly non-relational view of the
data, but it will be easy to demonstrate and immediately recognizable to our customers.

However, I can't seem to get around the problem of data granularity.  

For example, if my customer executes:

select house_number, street, city from table (legacy_realty_data()) where price < 500000

I don't appear to have any visibility to the actual query inside my legacy_realty_data TableFunction,
so I have to go get all 1000 fields for however many listings are present where price<
500000 even though only three columns will be requested.  Am I missing something?  Aside
from having the user repeat the columns as parameters to the table function (which looks awkward
to say the least), I can't see a way around this based on my limited knowledge of Derby.

Is there a way to only retrieve the columns that the user is querying for?

Looking forward to your help/advice.


View raw message