DB-DB Sample Query Readme

A copy of this readme is included with the DB-DB sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/db-db/readme.htm. (The default install home for Liquid Data is bea/weblogic700.)

This readme includes the following topics:

• What this Query Demonstrates
• How to Run the Query
• If You Want to Re-create the Query . . .

What this Query Demonstrates

This query demonstrates how to use Liquid Data to construct a query that integrates the data from two distributed heterogeneous database sources.

For example, a wireless service provider has customer information stored in one database server. All the promotion plans are managed by another database server. This sample query illustrates how to use Liquid Data to extract qualified promotion information for a customer. Traditionally, an application developer needs to write a set of data access methods and some logic join code to complete the information extraction and assembly. In Liquid Data, we demonstrate to use one single XQuery statement to declaratively accomplish the same task in much cleaner way.

How to Run the Query

1. Start the Liquid Data Samples server.
2. Start the Data View Builder.
3. In the Data View Builder, open the following project file: <WL_HOME>/liquiddata/samples/buildQuery/db-db/e2e-promotion.qpr
4. Click the Test tab. (This shows the generated query statement.)
5. Click the "Run Query" button and view the XML result.

If You Want to Re-create the Query . . .

Configure the Data Sources in the Administration Console

1. Configure the Wireless relational database data source as follows:
   • JDBC connection pool: MyWirelessPool (username=wireless, password=wireless)
   • JDBC data source: MyWirelessDS
   • Liquid Data "Relational Database" data source: MyWireless-LD-DS (Schema=WIRELESS)
2. Configure the CRM relational database data source as follows:
   • JDBC connection pool: MyCRMPool (username=crm, password=crm)
   • JDBC data source: MyCRMDS
   • Liquid Data "Relational Database" data source: MyCRM-LD-DS (Schema=CRM)

Build the Query in the Data View Builder

1. Create a new project.
2. Open the Data Sources you just defined: MyWireless-LD-DS, and MyCRM-LD-DS
3. Add promotionData.xsd from the repository as the target schema
4. Map the FIRST_NAME, LAST_NAME, CUSTOMER_ID, STATE from SOURCE MyWireless-LD-DS->db->CUSTOMER element to the TARGET PromotionInfo->Customer_promotion->CUSTOMER.
5. Map the PROMOTION_NAME from SOURCE MyCRM-LD-DS->db->PROMOTION to the TARGET PromotionInfo->Customer_promotion->PROMOTION_PLAN->PROMOTION_NAME.
6. Map the PLAN_NAME, START_DATE, TO_DATE, PRICE from SOURCE MyCRM-LD-DS->db->PROMOTION_PLAN to the rest corresponding elements in the TARGET schema PromotionInfo->Customer_promotion->PROMOTION_PLAN.
7. Define the equal join condition on element CUSTOMER_ID between Source MyCRM-LD-DS->db->Promotion and SOURCE MyWireless-LD-DS->db->CUSTOMER.
8. Define the equal join condition on element STATE between Source MyCRM-LD-DS->db->PROMOTION and Source MyCRM-LD-DS->db->PROMOTION_PLAN.
9. Run the query. (Click the Test tab and click Run Query button.)

Reference

• You can view the Wireless data base schema file at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/WIRELESSP.sql
• You can view the CRM database schema at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/CRMP.sql
• You can view the target schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/promotionData.xsd
• You can view the query statement at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/stored_queries/promotion.xq