Derby
  1. Derby
  2. DERBY-4962

Create a table function which efficiently migrates data out of foreign databases and which can be used for ongoing data integration

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.9.1.0
    • Component/s: Tools
    • Labels:
      None

      Description

      DDLUtils and other tools are good at copying the shape of foreign schemas into Derby. It would be good to have a tool which efficiently bulk-copies the foreign data into Derby. This would be faster than having to dump the foreign data into flat files and then import those files into Derby. In addition, many data integration applications need to siphon new data out of foreign database on an ongoing basis. A tool based around a RestrictedVTI would be able to push the column projection and WHERE clause into the foreign database in order to speed up the siphoning. I will attach a Restricted VTI which can be used for the following tasks:

      1) Initial bulk-loading of Derby from a foreign database.

      2) Efficient on-going data integration.

        Issue Links

          Activity

          Rick Hillegas created issue -
          Hide
          Rick Hillegas added a comment -

          Attaching ForeignTableVTI.java. This is a RestrictedVTI supporting the bulk-import and data integration use-cases. Here is the class header comment, which explains how to use the machinery. I have tested this VTI against some legacy Cloudscape databases.

          -------------------------------------------------------------------------

          To use this machinery, first declare the helper procedures which create and drop the foreign views:

          create procedure registerForeignTables
          (
          in foreignSchemaName varchar( 32672 ),
          in foreignDriverName varchar( 32672 ),
          in connectionURL varchar( 32672 ),
          in debug boolean
          )
          language java parameter style java modifies sql data
          external name 'ForeignTableVTI.registerForeignTables';

          create procedure deregisterForeignTables
          (
          in foreignSchemaName varchar( 32672 ),
          in foreignDriverName varchar( 32672 ),
          in connectionURL varchar( 32672 ),
          in debug boolean
          )
          language java parameter style java modifies sql data
          external name 'ForeignTableVTI.deregisterForeignTables';

          Then create a schema for holding the foreign views and switch to that schema:

          create schema foreignschema;
          set schema foreignschema;

          Register views against all of the tables in a foreign schema. In this example, the foreign database is a Cloudscape database and you register views and table functions against all of the tables in its APP schema:

          call app.registerForeignTables
          (
          'APP',
          'COM.cloudscape.core.JDBCDriver',
          'jdbc:cloudscape:olddbs/SalesDatabase',
          true
          );

          Now bulk import data from the foreign views using INSERT INTO...SELECT statements. The following example bulk-copies all rows from a foreign table called "salesorders" into a local table by the same name:

          insert into app.salesorders select * from salesorders;

          If you need to siphon data out of the foreign database on an ongoing basis, you can restrict the data you SELECT. Note that the local views are backed by RestrictedVTIs. That means that the actual query sent to the foreign database will only involve the columns you SELECT. In addition, the query will include the WHERE clause, provided that it is simple enough (see the javadoc for RestrictedVTI):

          insert into app.neworderids select orderid from salesorders where orderid > 54321;

          When you are done with the foreign data, you can drop the views and table functions:

          call app.deregisterForeignTables
          (
          'APP',
          'COM.cloudscape.core.JDBCDriver',
          'jdbc:cloudscape:olddbs/SalesDatabase',
          true
          );

          Show
          Rick Hillegas added a comment - Attaching ForeignTableVTI.java. This is a RestrictedVTI supporting the bulk-import and data integration use-cases. Here is the class header comment, which explains how to use the machinery. I have tested this VTI against some legacy Cloudscape databases. ------------------------------------------------------------------------- To use this machinery, first declare the helper procedures which create and drop the foreign views: create procedure registerForeignTables ( in foreignSchemaName varchar( 32672 ), in foreignDriverName varchar( 32672 ), in connectionURL varchar( 32672 ), in debug boolean ) language java parameter style java modifies sql data external name 'ForeignTableVTI.registerForeignTables'; create procedure deregisterForeignTables ( in foreignSchemaName varchar( 32672 ), in foreignDriverName varchar( 32672 ), in connectionURL varchar( 32672 ), in debug boolean ) language java parameter style java modifies sql data external name 'ForeignTableVTI.deregisterForeignTables'; Then create a schema for holding the foreign views and switch to that schema: create schema foreignschema; set schema foreignschema; Register views against all of the tables in a foreign schema. In this example, the foreign database is a Cloudscape database and you register views and table functions against all of the tables in its APP schema: call app.registerForeignTables ( 'APP', 'COM.cloudscape.core.JDBCDriver', 'jdbc:cloudscape:olddbs/SalesDatabase', true ); Now bulk import data from the foreign views using INSERT INTO...SELECT statements. The following example bulk-copies all rows from a foreign table called "salesorders" into a local table by the same name: insert into app.salesorders select * from salesorders; If you need to siphon data out of the foreign database on an ongoing basis, you can restrict the data you SELECT. Note that the local views are backed by RestrictedVTIs. That means that the actual query sent to the foreign database will only involve the columns you SELECT. In addition, the query will include the WHERE clause, provided that it is simple enough (see the javadoc for RestrictedVTI): insert into app.neworderids select orderid from salesorders where orderid > 54321; When you are done with the foreign data, you can drop the views and table functions: call app.deregisterForeignTables ( 'APP', 'COM.cloudscape.core.JDBCDriver', 'jdbc:cloudscape:olddbs/SalesDatabase', true );
          Rick Hillegas made changes -
          Field Original Value New Value
          Attachment ForeignTableVTI.java [ 12467837 ]
          Rick Hillegas made changes -
          Status Open [ 1 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Kathey Marsden made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          Hide
          Kathey Marsden added a comment -

          Resolving in 10.9 since the tool was created in that timeframe. I wonder though should this be checked in somewhere?

          Show
          Kathey Marsden added a comment - Resolving in 10.9 since the tool was created in that timeframe. I wonder though should this be checked in somewhere?
          Kathey Marsden made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Fix Version/s 10.9.0.0 [ 12316344 ]
          Resolution Fixed [ 1 ]
          Hide
          Rick Hillegas added a comment -

          Hi Kathey,

          I think it's a useful program but I'm not clear on where it would go in our toolset. Thanks.

          Show
          Rick Hillegas added a comment - Hi Kathey, I think it's a useful program but I'm not clear on where it would go in our toolset. Thanks.
          Rick Hillegas made changes -
          Link This issue is related to DERBY-6022 [ DERBY-6022 ]
          Rick Hillegas made changes -
          Assignee Rick Hillegas [ rhillegas ]
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12541978 ] Default workflow, editable Closed status [ 12802616 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development