| Previous | Table of Contents | Next |
It is clear that enforcing this business rule in our new distributed environment is a real challenge. While it is relatively simple to tell an Oracle system not to delete a row from its CUSTOMER table if rows for that customer exist in the ORDER table, it is not simple to enforce this rule when the CUSTOMER table resides in a Sybase database and the order table resides within Oracle. The solution is to remove the database RI rules from each database, remembering to manually replicate the RI rules using procedural code within the application. This essentially creates your own customized RI within the application.
The next step is to import the data into the newly defined table in one of two ways. The easiest method uses Oracle’s import/export utility to import the customer table into SQL INSERT statements. The second, and more complicated way, extracts the table into a flat file, adds column delimiters, and then uses Oracle’s SQL*Loader to add the data into the new database. Of course, Oracle’s import utility can only be used when the import file is formatted according to the format required by Oracle’s import facility.
For example, a flat-file data extract could be manipulated to state the following:
INSERT INTO CUSTOMER VALUES ('Burleson','343 State St','Rochester','NY');
INSERT INTO CUSTOMER VALUES ('Joe Chelko','123 4th st.','New York','NY');
Let’s assume that a CUSTOMER table has been added to an instance called Triton, and that an ORDERS table resides in an instance called Phobos. How does the user make these tables function as if they resided in a unified database? When a distributed request is made to tables within different architectures, the query is partitioned into separate subqueries and executed against each database engine. The processor governing the distributed request acts as the consolidator, merging the result sets and performing any postretrieval processes such as ORDER BY or GROUP BY clauses that must manipulate all of the returned data.
In our example, we join the tables for a customer, pulling the customer information from Sybase and the order information from Oracle. Directly addressing SQL across different database products generates the following sample query:
SELECT cust_name,
customer_street_address,
order_date
FROM
CUSTOMER@triton,
ORDERS@phobos
WHERE
ORDERS.cust_number=CUSTOMER.cust_number AND
CUSTOMER.cust_name like 'Burleson';
NOTE: This SQL uses node names to identify the physical location of the tables. This is not standard SQL syntax, but it illustrates the need to join diverse tables.
For a more realistic test, let’s retrieve information from all of the four tables. Listing 1.1 joins the CUSTOMER table with the ORDERS table where cust_name = ‘Burleson’ and then joins the ORDERS table entries with the ORDER_LINE table. Finally, the SQL joins ORDER_LINE with the PRODUCT table, retrieving product information.
Listing 1.1 A sample distributed query.
SELECT CUSTOMER.cust_name,
CUSTOMER.customer_street_address,
ORDERS.order_date,
PRODUCT.product_cost,
PRODUCT.product_name
FROM
CUSTOMER@triton,
ORDERS@phobos,
ORDER_LINE@phobos,
PRODUCT@phobos
WHERE
CUSTOMER.cust_number = ORDERS.cust_number
AND
ORDERS.order_number = ORDER_LINE.order_number
AND
ORDER_LINE.prod_number = PRODUCT.prod_number
AND
CUSTOMER.cust_name like 'Burleson';
This query can be easily issued from a remote PC using the SQL*Net software. Another access route to these tables is an already-defined tool that accesses protocols for each database, such as the popular UniFace tool.
Accessing a remote database node from a PC platform uses similar steps. Simply punch the relational table into a flat file, and transfer this file to the PC using either FTP or some other file transfer utility. At this point, the flat file can be loaded into a PC-based database for local reference.
The steps to populate a relational table on a PC platform differ from the steps for a midrange database. Most PC databases do not support CREATE TABLE SQL. The table is defined using the online GUI screens. To define the table to FoxPro, choose File|New, and define the tables using the GUI interface. Now we can manually define a PRODUCT table with identical column names and field sizes. Fortunately, adding rows to a FoxPro table is very simple. Using the PC’s text editor, insert a delimiter character between each field in the flat file in the PC text editor.
Choose a character that does not exist in the data, such as the caret (^) or at sign (@). If your database support adding literals to queries, characters can be added at extraction time:
SELECT cust_name||"^"||cust_city||"^"||cust_state from CUSTOMER;
The massaged file appears as follows:
Burleson^343 State St^Rochester^NY Joe Chelko^123 4th st.^New York^ NY
This flat file can now be easily imported into FoxPro. From the FoxPro command prompt, enter the following commands:
CLOSE DATA USE PRODUCT APPEND from c:\myfile.dat TYPE SDF DELIMITED with '^'
This APPEND command takes the data from the flat file and moves it into the FoxPro table. Incidentally, even though FoxPro does not support SQL INSERT statements, it is one of the easiest databases for data migration. Also, because FoxPro’s “Rushmore” technology is so fast, many sites move systems directly from mainframes into FoxPro. Systems can move directly from mainframes onto FoxPro data servers and gain improved response-time. I have proven this point from personal experience: I migrated from an IBM 3090 to FoxPro, each one performed faster (on a stand-alone PC) than its counterpart on the mainframe.
This section focuses on database server techniques for creating high-performance, client/server Oracle applications. Without an effective database design, no amount of tuning allows the system to achieve optimal performance. Hence, it is critical to a database design that we derive the most from all of the available servers.
As we know, five types of data relationships must be considered when designing any Oracle database:
The effective client/server designer’s role represents these types of relationships in a sensible way and ensures acceptable server performance.
| Previous | Table of Contents | Next |