Rick Hillegas created DERBY-6042:
------------------------------------
Summary: Document the syscs_util.syscs_register_tool procedure
added by DERBY-6022.
Key: DERBY-6042
URL: https://issues.apache.org/jira/browse/DERBY-6042
Project: Derby
Issue Type: Improvement
Components: Documentation
Affects Versions: 10.10.0.0
Reporter: Rick Hillegas
We should document the new syscs_util.syscs_register_tool() procedure and the
new optional tools which it loads and unloads. The following changes make sense
to me:
--
-- Reference Manual
--
-- New section on the syscs_util.syscs_register_tool() procedure
--
This procedure loads and unloads optional tools packages. By default, only the
DBO can run this procedure.
Syntax:
SYSCS_UTIL.SYSCS_REGISTER_TOOL
(
IN TOOLNAME VARCHAR(128),
IN REGISTER BOOLEAN,
IN OPTIONALARGS VARCHAR(128) ...
);
No result set is returned by this procedure.
TOOLNAME
Name of the optional tool. One of: 'databaseMetaData' or 'foreignViews'
REGISTER
True means the tool is loaded. False means it is unloaded.
OPTIONALARGS
Optional arguments specific to each tool.
The following optional tools are supported:
databaseMetaData
This optional tool creates functions and table functions to wrap the methods in
java.sql.DatabaseMetaData. This lets you use DatabaseMetaData methods in
queries. This includes the ability to join and filter the ResultSets returned
by DatabaseMetaData methods. This tool does not require any optional arguments.
To create the metadata functions and table functions, do the following:
call syscs_util.syscs_register_tool( 'databaseMetaData', true )
To drop the functions and table functions, do the following:
call syscs_util.syscs_register_tool( 'databaseMetaData', false )
The Tools Guide provides more information on how to use this tool.
foreignViews
This optional tool creates schemas, table functions, and convenience views for
all user tables in a foreign database. The table functions and views are useful
for bulk-importing foreign data into Derby. This tool takes two additional
arguments:
CONNECTION_URL
This is a connection URL string suitable for creating a connection to the
foreign database via DriverManager.getConnection().
SCHEMA_PREFIX
This is an optional string prefixed to all of the schema names which the tool
creates. This argument may be omitted. If it is omitted, then the tool will
create schemas which have the same names as the schemas in the foreign database.
To create views on the foreign data, do the following:
call syscs_util.syscs_register_tool( 'foreignViews', true,
'foreignDatabaseURL', 'XYZ_' )
To drop the views on the foreign data, do the following:
call syscs_util.syscs_register_tool( 'foreignViews', false,
'foreignDatabaseURL', 'XYZ_' )
The Tools Guide provides more information on how to use this tool.
--
-- Tools Guide
--
-- New section on the optional databaseMetaData tool
--
This is an optional tool loaded and unloaded by the
syscs_util.syscs_register_tool() system procedure. Loading this tool creates
functions and table functions corresponding to most of the methods in the
java.sql.DatabaseMetaData interface. To load this tool, do the following:
call syscs_util.syscs_register_tool( 'databaseMetaData', true )
That command creates metadata functions and table functions in the current
schema. The functions and table functions have the same names as the
corresponding DatabaseMetaData methods which they wrap. Once you have loaded
this tool, you can filter and join these functions to create powerful metadata
queries. For instance, the following query lists the column names and datatypes
for all columns in tables created by users:
select t.table_schem, t.table_name, c.column_name, c.type_name
from table( getTables( null, '%', '%' ) ) t,
table( getColumns( null, '%', '%', '%') ) c
where c.table_schem = t.table_schem
and c.table_name = t.table_name
and t.table_type = 'TABLE'
order by table_schem, table_name, column_name
A couple DatabaseMetaData methods take array arguments. Because those arguments
can't be represented as Derby types, the arguments are eliminated. This means
that the trailing "types" arguments to getTables() and getUDTs() have been
eliminated. In addition, the following DatabaseMetaData methods don't have
corresponding metadata routines:
o getRowIdLifetime() is eliminated because Derby does not provide an
implementation of java.sql.RowIdLifetime.
o getSchemas() is eliminated because Derby does not support overloads. The more
general getSchemas( String, String ) method is included.
o supportsConvert() is eliminated because Derby does not support overloads. The
more general supportsConvert( int, int ) is included.
When you are done joining metadata results, you can drop this package of
functions and table functions:
call syscs_util.syscs_register_tool( 'databaseMetaData', false )
--
-- Tools Guide
--
-- New section on the optional foreignViews tool
--
This is an optional tool loaded and unloaded by the
syscs_util.syscs_register_tool() system procedure. Loading this 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. To load
this tool, do the following...
call syscs_util.syscs_register_tool( 'foreignViews', true,
'foreignDatabaseURL', 'XYZ_' )
...where the trailing 2 arguments have these meanings:
o foreignDatabaseURL is an URL suitable for creating a connection to the
foreign database via java.sql.DriverManager.getConnection(). E.g.:
'jdbc:derby:db3;user=fred;password=fredpassword'
o XYZ_ is a string prefixed to the names of all schemas created by this tool.
This argument may be omitted. If it is omitted, then the tool will create
schemas which have the same names as the schemas in the foreign database.
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 via the following SELECTs:
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 are done bulk-importing the foreign data, you can drop this package of
schemas, table functions and views:
call syscs_util.syscs_register_tool( 'foreignViews', false,
'foreignDatabaseURL', 'XYZ_' )
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
