Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Fixed
-
10.10.1.1
-
None
-
Normal
Description
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_' )
Attachments
Attachments
Issue Links
- relates to
-
-
- Closed
-