Derby
  1. Derby
  2. DERBY-6042

Document the syscs_util.syscs_register_tool procedure added by DERBY-6022.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.10.1.1
    • Fix Version/s: 10.10.1.1
    • Component/s: Documentation
    • Labels:
      None
    • Urgency:
      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_' )

      1. DERBY-6042-2.zip
        9 kB
        Kim Haase
      2. DERBY-6042-2.diff
        18 kB
        Kim Haase
      3. DERBY-6042.zip
        9 kB
        Kim Haase
      4. DERBY-6042.stat
        0.2 kB
        Kim Haase
      5. DERBY-6042.diff
        18 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Kim Haase added a comment -

          Closing, since changes appeared in 10.10.1.1 documentation set.

          Show
          Kim Haase added a comment - Closing, since changes appeared in 10.10.1.1 documentation set.
          Hide
          Rick Hillegas added a comment -

          Thanks, Kim. +1

          Show
          Rick Hillegas added a comment - Thanks, Kim. +1
          Hide
          Kim Haase added a comment -

          Committed patch DERBY-6042-2.diff to documentation trunk at revision 1437218.

          Show
          Kim Haase added a comment - Committed patch DERBY-6042 -2.diff to documentation trunk at revision 1437218.
          Hide
          Kim Haase added a comment - - edited

          Thanks again, Rick. I'm attaching DERBY-6042-2.diff and DERBY-6042-2.zip, with the single change to src/ref/rrefregistertoolproc.dita that you noticed.

          I'll go ahead and commit this second patch, unless I hear otherwise.

          Show
          Kim Haase added a comment - - edited Thanks again, Rick. I'm attaching DERBY-6042 -2.diff and DERBY-6042 -2.zip, with the single change to src/ref/rrefregistertoolproc.dita that you noticed. I'll go ahead and commit this second patch, unless I hear otherwise.
          Hide
          Rick Hillegas added a comment -

          Thanks, Kim. That was the only improvement which jumped out at me.

          Show
          Rick Hillegas added a comment - Thanks, Kim. That was the only improvement which jumped out at me.
          Hide
          Kim Haase added a comment -

          Thanks, Rick – I'll file a second patch. Anything else to fix, in the tools topics perhaps?

          Show
          Kim Haase added a comment - Thanks, Rick – I'll file a second patch. Anything else to fix, in the tools topics perhaps?
          Hide
          Rick Hillegas added a comment -

          Hi Kim,

          Yes, that wording sounds better. Thanks.

          Show
          Rick Hillegas added a comment - Hi Kim, Yes, that wording sounds better. Thanks.
          Hide
          Kim Haase added a comment -

          Thanks! Actually, I was wondering about that wording. "Means that the tool is" sounds like a description of an existing condition, whereas the boolean value is really an instruction, isn't it? So I'm wondering if it should say,

          "A value of true tells Derby to load the tool. A value of false tells Derby to unload the tool."

          Or something like that?

          Show
          Kim Haase added a comment - Thanks! Actually, I was wondering about that wording. "Means that the tool is" sounds like a description of an existing condition, whereas the boolean value is really an instruction, isn't it? So I'm wondering if it should say, "A value of true tells Derby to load the tool. A value of false tells Derby to unload the tool." Or something like that?
          Hide
          Rick Hillegas added a comment -

          Thanks for the new documentation, Kim. Looks great to me. One correction:

          rrefregistertoolproc.html

          o Description of the REGISTER argument: "tool is not loaded" -> "tool is unloaded"

          Show
          Rick Hillegas added a comment - Thanks for the new documentation, Kim. Looks great to me. One correction: rrefregistertoolproc.html o Description of the REGISTER argument: "tool is not loaded" -> "tool is unloaded"
          Hide
          Kim Haase added a comment -

          Attaching DERBY-6042.diff, DERBY-6042.stat, and DERBY-6042.zip, with four new topics and corresponding changes to map files:

          A src/ref/rrefregistertoolproc.dita
          M src/ref/refderby.ditamap
          M src/tools/derbytools.ditamap
          A src/tools/rtoolsoptdbmetadata.dita
          A src/tools/rtoolsoptforeignviews.dita
          A src/tools/ctoolsopttools.dita

          I created an umbrella topic for the optional tools in the Tools Guide, just in case more come along at some point.

          All comments are welcome!

          Show
          Kim Haase added a comment - Attaching DERBY-6042 .diff, DERBY-6042 .stat, and DERBY-6042 .zip, with four new topics and corresponding changes to map files: A src/ref/rrefregistertoolproc.dita M src/ref/refderby.ditamap M src/tools/derbytools.ditamap A src/tools/rtoolsoptdbmetadata.dita A src/tools/rtoolsoptforeignviews.dita A src/tools/ctoolsopttools.dita I created an umbrella topic for the optional tools in the Tools Guide, just in case more come along at some point. All comments are welcome!

            People

            • Assignee:
              Kim Haase
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development