# 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:

- foreignDatabaseURL is a URL suitable for creating a connection to the foreign database by calling java.sql.DriverManager.getConnection(). For example:

      'jdbc:derby:db3;user=fred;password=fredpassword'

- `'XYZ_'` is a string prefixed to the names of all schemas created by this tool. This argument may be omitted. If it is omitted, the tool will create schemas which have the same names as the schemas in the foreign database.

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:

- schema `XYZ_S1`
- table function `XYZ_S1.T1`, which reads `S1.T1` from the foreign database
- table function `XYZ_S1.T2`, which reads `S1.T2` from the foreign database
- view `XYZ_S1.T1`, which wraps the corresponding table function
- view `XYZ_S1.T2`, which wraps the corresponding table function

- schema `XYZ_S2`
- table function`XYZ_S2.U1`, which reads `S2.U1` from the foreign database
- table function `XYZ_S2.U2`, which reads `S2.U2` from the foreign database
- view `XYZ_S2.U1`, which wraps the corresponding table function
- view `XYZ_S2.U2`, which wraps the corresponding table function

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_' )

Parent topic: Optional tools
Related reference
Using the databaseMetaData optional tool
Using the luceneSupport optional tool
Using the simpleJson optional tool
Using the rawDBReader optional tool