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