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. optional toolsforeignViews foreignViews optional tool toolsforeignViews optional tool

Before you run the foreignViews optional tool, make sure that your classpath contains the 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 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 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_' )