This class contains a table function which can be used to bulk-import data from a foreign database. Because the table function is a RestrictedVTI, it can also be used to periodically and efficiently integrate data streams from a foreign database.
If you need to siphon data out of the foreign database on an ongoing basis, you can restrict the data you SELECT. Note that the local views are backed by RestrictedVTIs. That means that the actual query sent to the foreign database will only involve the columns you SELECT. In addition, the query will include the WHERE clause, provided that it is simple enough (see the javadoc for RestrictedVTI):
The following script shows how to use this table function:
-- create a foreign database with a table in it connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword'; call syscs_util.syscs_create_user( 'test_dbo', 'test_dbopassword' ); create table employee ( firstName varchar( 50 ), lastName varchar( 50 ), employeeID int primary key ); insert into employee values ( 'Billy', 'Goatgruff', 1 ); insert into employee values ( 'Mary', 'Hadalittlelamb', 2 ); connect 'jdbc:derby:memory:db;shutdown=true'; -- now create the database where we will do our work connect 'jdbc:derby:memory:db1;create=true'; -- register a table function with the shape of the foreign table create function employeeFunction ( schemaName varchar( 32672 ), tableName varchar( 32672 ), connectionURL varchar( 32672 ) ) returns table ( firstName varchar( 50 ), lastName varchar( 50 ), employeeID int ) language java parameter style derby_jdbc_result_set no sql external name 'org.apache.derby.vti.ForeignTableVTI.readForeignTable' ; -- create a convenience view to factor out the function parameters create view foreignEmployee as select firstName, lastName, employeeID from table ( employeeFunction ( 'TEST_DBO', 'EMPLOYEE', 'jdbc:derby:memory:db;user=test_dbo;password=test_dbopassword' ) ) s; -- now select from the view as though it were a local table select * from foreignEmployee; select lastName from foreignEmployee where employeeID = 2;
| |