db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suavi Ali Demir <dem...@yahoo.com>
Subject Re: Question about TableFunctions in Derby
Date Wed, 15 Jul 2009 04:31:29 GMT

Try something like this:

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

then parse the string passed to your table function and implement a way to push down those
predicates to your backend to only retrieve what is needed.

if syntax is ugly, you can accept it in your original sql form, but have a preprocessor that
converts it into this form before execution.

maybe to find out what columns are used, you don't have to parse it, if you prepare your original
sql, you can get resultsetmetadata to find out what columns are being returned. to find what
predicates are used, *maybe* you can execute and see which predicate values were attempted
to be accessed (derby may skip evaluation of some predicates in some cases so this is probably
not a good idea). and after you collect all this with a mock trial run, you can do the real
run that really connects to your backend. 

Regards,
Ali

--- On Tue, 7/14/09, Chris Goodacre <cgoodacre@yahoo.com> wrote:

> From: Chris Goodacre <cgoodacre@yahoo.com>
> Subject: Question about TableFunctions in Derby
> To: derby-user@db.apache.org
> Date: Tuesday, July 14, 2009, 8:35 PM
> 
> 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.
> 
> -chris
> 
> 


      

Mime
View raw message