*** UNDER CONSTRUCTION ***

Overview

• Demonstration of updating two different and independent data source in a single transaction;
• Demonstration of rolling back the transaction if one data source fails to update.

Requirements

• JBoss 7+ with a Teiid 7.7+ installation;

• Teiid Designer 7.7+;

• JDBC drivers, for the data sources, eg.

  • Oracle: oracle14.jar;

  • DB2: db2jcc4.jar;

  • HSQLDB: hsqldb.jar;

  • MySQL: mysql-connector-java-5.1.24.jar.

• Access to the PartsSupplier database on two different database management systems. For clarity, this article assumes hsqldb and MySQL.

Creating the Project

Open the Teiid Designer perspective in JBoss Developer Studio.

1. In the Guides View, under Action Sets, select Model JDBC Source and double-click Define Teiid Model Project.
2. Enter the project name as partsupplier and choose a location for the project.
3. Click Next> and if the Project References page is displayed, click Next> again (No projects need referencing).
4. In the Model Project Options page, only leave selected the source and views folders then click Finish.
5. Click OK to close the Define Model Project dialog.

Create the JDBC connection to the MySQL PartsSupplier Database

1. In the Guides View, double-click Create JDBC Connection.
2. Enter the name for the new connection as partsupplier-mysql.
3. Click Next> and enter the connection details for the MySQL PartsSupplier database.
4. Test the connection and if it succeeds, click Next>.
5. Review the connection properties and complete the wizard by clicking Finish>.

Create the MySQL PartsSupplier Source Model

1. In the Guides View, double-click Create source model for JDBC data source.
2. If not already selected, select the partsupplier-mysql connection profile.
3. Ensure the value of the JDBC Metadata Processor is set as JDBC (default).
4. The password should have been populated from the JDBC connection so click Next>.
5. Click through the Select Database Metadata window by clicking Next>.
6. In the Select Database Objects window, select the tables partsupplier.PARTS and partsupplier.SHIP_VIA and click Next>.
7. In the Specify Import Options window, leave the settings at they are and click Finish.
8. The new source model is created and displayed in the Model Explorer.

Create the JDBC connection to the HSQLDB PartsSupplier Database

1. In the Guides View, double-click Create JDBC Connection.
2. Enter the name for the new connection as partsupplier-hsqldb.
3. Click Next> and enter the connection details for the HSQLDB PartsSupplier database, eg.
   • Database: partsupplier
   • Database location: hsql://localhost/partsupplier (if you have configured the database with an appropriate listener)
   • User name: sa
   • Password: appropriate to the sa account if set at all
4. Test the connection and if it succeeds, click Next>.
5. Review the connection properties and complete the wizard by clicking Finish>.

Create the HSQLDB PartsSupplier Source Model

1. In the Guides View, double-click Create source model for JDBC data source.
2. If not already selected, select the partsupplier-hsqldb connection profile.
3. Ensure the value of the JDBC Metadata Processor is set as JDBC (default).
4. The password should have been populated from the JDBC connection so click Next>.
5. Click through the Select Database Metadata window by clicking Next>.
6. In the Select Database Objects window, select the tables partsupplier.PARTS and partsupplier.SHIP_VIA and click Next>.
7. In the Specify Import Options window, leave the settings at they are and click Finish.
8. The new source model is created and displayed in the Model Explorer.

Create the Relational View Model

1. Right-click on the partsupplier project's view model and select New -> Teiid Metadata Model.
2. In the New Model Wizard window, enter the following:
   • Model name: partsupplier-view;
   • Model class: Relational;
   • Model type: View;
   • Model builder: leave all options unselected.
3. Click Finish to create the new view diagram, which should be displayed in the editor.
4. Right-click in the view diagram and selected New Child -> Table....
5. In the Create Relational View Table window, enter the name Updates

1. Import PartsSupplier from two RDBMSes
2. So you can easily use the SQL provided in this test script, name the imported PartsSupplier models, PartsOne & PartsTwo
3. Create a Relational View model named Updates
4. Add to Updates a base table named UpdateParts
5. Use this SQL to define the transformation:

   SELECT
       TwoTransParts_Oracle.SUPPLIER_PARTS.SUPPLIER_ID,
       TwoTransParts_Oracle.SUPPLIER_PARTS.PART_ID, TwoTransParts_Oracle.SUPPLIER_PARTS.QUANTITY,
       TwoTransParts_Oracle.SUPPLIER_PARTS.SHIPPER_ID, TwoTransParts_DB2.SHIP_VIA.SHIPPER_NAME
   FROM
        TwoTransParts_Oracle.SUPPLIER_PARTS, TwoTransParts_DB2.SHIP_VIA
   WHERE
        TwoTransParts_DB2.SHIP_VIA.SHIPPER_ID = TwoTransParts_Oracle.SUPPLIER_PARTS.SHIPPER_ID;
   
6. Check the Supports Update option

7. Click Yes to set all child columns to Updateable

   (Note the transformation editor now has the     SELECT, UPDATE,INSERT, and DELETE tabs.)

8. Go to the INSERT tab
9. Uncheck the Use default option

10. Enter the insert procedure SQL:

    CREATE PROCEDURE
    BEGIN
      INSERT INTO TwoTransParts_Oracle.SUPPLIER_PARTS
        (TwoTransParts_Oracle.SUPPLIER_PARTS.SUPPLIER_ID, TwoTransParts_Oracle.SUPPLIER_PARTS.PART_ID,
         TwoTransParts_Oracle.SUPPLIER_PARTS.QUANTITY, TwoTransParts_Oracle.SUPPLIER_PARTS.SHIPPER_ID)
      VALUES ("INPUT".SUPPLIER_ID, "INPUT".PART_ID, "INPUT".QUANTITY, "INPUT".SHIPPER_ID);
         VARIABLES.ROWS_UPDATED = INSERT INTO TwoTransParts_DB2.SHIP_VIA
        (TwoTransParts_DB2.SHIP_VIA.SHIPPER_ID, TwoTransParts_DB2.SHIP_VIA.SHIPPER_NAME)
    END
    
11. Switch to the DELETE tab
12. Uncheck the Use default option

13. Enter the delete procedure SQL:

    CREATE PROCEDURE
    BEGIN
      DECLARE bigdecimal VARIABLES.ShipId;
      IF (HAS CRITERIA ON (TwoTransView.ttv.SHIPPER_ID, TwoTransView.ttv.SUPPLIER_ID, TwoTransView.ttv.PART_ID)
      BEGIN
        VARIABLES.ShipId =
          SELECT SHIPPER_ID      FROM TwoTransParts_Oracle.SUPPLIER_PARTS
          WHERE TRANSLATE CRITERIA ON
            (TwoTransView.ttv.SHIPPER_ID, TwoTransView.ttv.SUPPLIER_ID, TwoTransView.ttv.PART_ID);
          VARIABLES.ROWS_UPDATED = DELETE FROM TwoTransParts_DB2.SHIP_VIA WHERE SHIPPER_ID = VARIABLES.ShipId;
          DELETE FROM TwoTransParts_Oracle.SUPPLIER_PARTS WHERE TRANSLATE  
            CRITERIA ON (TwoTransView.ttv.SUPPLIER_ID, TwoTransView.ttv.PART_ID, TwoTransView.ttv.SHIPPER_ID);
      END
    END
    
14. On the UPDATE tab, uncheck the Update Enabled option.
15. Save the transformation and the model

The VDB

1. Create a new VDB
2. Add the Updates model to the VDB
3. To the PartsOne model, add:   
   • the translator: oracle
   • JNDI Name is XA_PartsOra
4. To the PartsTwo model, add:   
   • the translator: db2
   • JNDI Name is: XA_PartsDB2
5. Save the VDB
6. Deploy the VDB to a server

Test Queries

• Using a JDBC query tool, execute these queries
• Login to the VDB with this option: autoCommitTxn=ON

Initial Baseline

Make note of the counts.

1. SELECT COUNT(*) FROM TwoTransParts_Oracle.SUPPLIER_PARTS WHERE part_id = 'P302' AND supplier_id = 'S102'
   

2. SELECT COUNT(*) FROM TwoTransParts_db2.ship_via WHERE SHIPPER_ID = 94
   

3. SELECT COUNT(*) FROM TwoTransView.ttv WHERE SHIPPER_ID = 94
   

Insert a Row

Do this to demonstrate the VDB and sources are correctly wired.

1. INSERT INTO TwoTransView.ttv (SUPPLIER_ID, PART_ID, QUANTITY, SHIPPER_ID, SHIPPER_NAME) VALUES ('S102', 'P302', 135, 94, 'Shadowman Shipping')
   

   Verify the counts have increased by one

   • SELECT COUNT(*) FROM TwoTransParts_Oracle.SUPPLIER_PARTS WHERE part_id = 'P302' AND supplier_id = 'S102'
     

   • SELECT COUNT(*) FROM TwoTransParts_db2.ship_via WHERE SHIPPER_ID = 94
     

   • SELECT COUNT(*) FROM TwoTransView.ttv WHERE SHIPPER_ID = 94
     

2. Delete the row just inserted to demonstrate the delete function is also functioning correctly.

   DELETE FROM TwoTransView.ttv WHERE (SUPPLIER_ID = 'S102') AND (PART_ID = 'P302') AND (SHIPPER_ID = 94)
   

   • SELECT COUNT(*) FROM TwoTransParts_Oracle.SUPPLIER_PARTS WHERE part_id = 'P302' AND supplier_id = 'S102'
     

   • SELECT COUNT(*) FROM TwoTransParts_db2.ship_via WHERE SHIPPER_ID = 94
     

   • SELECT COUNT(*) FROM TwoTransView.ttv WHERE SHIPPER_ID = 94
     

Rollback Test

1. Insert a row into the Oracle to cause a rollback on our test insert.

   INSERT INTO TwoTransParts_Oracle.SUPPLIER_PARTS (SUPPLIER_ID, PART_ID, QUANTITY, SHIPPER_ID) VALUES ('S102',     'P302', 135, 94)
   

2. Now verify the insert fails.

   INSERT INTO TwoTransView.ttv (SUPPLIER_ID, PART_ID, QUANTITY, SHIPPER_ID, SHIPPER_NAME) VALUES ('S102', 'P302', 135, 94, 'Shadowman Shipping')
   

   • SELECT COUNT(*) FROM TwoTransParts_db2.ship_via WHERE SHIPPER_ID = 94
     

   • SELECT COUNT(*) FROM TwoTransView.ttv WHERE SHIPPER_ID = 94
     

3. Delete the row added in preparation for testing the other source.

   DELETE FROM     TwoTransParts_Oracle.SUPPLIER_PARTS WHERE (SUPPLIER_ID = 'S102') AND (PART_ID = 'P302')
   

4. Insert a row in the DB2 source to cause a rollback on our test insert.

   INSERT INTO TwoTransParts_DB2.SHIP_VIA (SHIPPER_ID, SHIPPER_NAME) VALUES(94, 'Bogus Shipping')
   

5. Now verify our insert fails .

   INSERT INTO TwoTransView.ttv (SUPPLIER_ID, PART_ID, QUANTITY, SHIPPER_ID, SHIPPER_NAME) VALUES ('S102', 'P302', 135, 94, 'Shadowman Shipping')
   

6. Verify the row counts remained unaffected.

   • SELECT COUNT(*) FROM TwoTransParts_Oracle.SUPPLIER_PARTS WHERE part_id = 'P302' AND supplier_id = 'S102'
     

   • SELECT COUNT(*) FROM TwoTransView.ttv WHERE SHIPPER_ID= 94
     

7. Final cleanup: Delete the row added earlier

   DELETE FROM TwoTransParts_DB2.SHIP_VIA WHERE SHIPPER_ID = 94