Using the foreignViews optional tool

The foreignViews optional tool creates schemas, table functions, and convenience views for all user tables in a foreign database. This can be useful for bulk-importing foreign data.

Before you run the foreignViews optional tool, make sure that your classpath contains the Derby jar files, including derbytools.jar.

You can load and unload the foreignViews tool by using the SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure. See the Derby Reference Manual for information about this procedure.

To load the foreignViews tool, use a statement like the following:

call syscs_util.syscs_register_tool( 'foreignViews', true,
    'foreignDatabaseURL', 'XYZ_' )

The two trailing arguments have the following meanings:

For example, suppose that the foreign database has two schemas, S1 and S2. S1 contains two user tables, T1 and T2. S2 contains two user tables, U1 and U2. Loading the tool as shown above will create the following objects in your Derby database:

The views hide the arguments to the table functions. You can then populate your local schema by using the following SELECT statements:

insert into S1.T1 select * from XYZ_S1.T1
insert into S1.T2 select * from XYZ_S1.T2
insert into S2.U1 select * from XYZ_S2.U1
insert into S2.U2 select * from XYZ_S2.U2

When you have finished bulk-importing the foreign data, you can drop this package of schemas, table functions and views as follows:

call syscs_util.syscs_register_tool( 'foreignViews', false,
    'foreignDatabaseURL', 'XYZ_' )
Related reference
Using the databaseMetaData optional tool
Using the luceneSupport optional tool
Using the simpleJson optional tool
Using the rawDBReader optional tool