Skip navigation.
Downloads | Product Documentation | Support

Data Services Developer's Guide

# Testing Query Functions and Viewing Query Plans

You can use Test View to execute any data service read or relationship function for which data is available.

When you run a query in Test View results appear in an editable window in text or structured XML form. When updates are available for your data, you can immediately update your back-end data. Query results can also be used as complex parameters for other queries.

In creating support for query functions, BEA Aqualogic Data Services Platform (DSP) determines Test View options from your query function's signature. Several types of query function signatures are supported including queries with and without parameters, simple and complex parameters, and ad hoc queries.

The following major topics are covered in this chapter:

- Running Queries Using Test View
- Analyzing Queries Using Plan View
- Creating an Ad Hoc Query

# Running Queries Using Test View

In Test View you can select any read or navigation functions or procedures defined in your data service from a drop-down list.

Figure 7-1 Test View Options for a Function Accepting a Simple Parameter

If the query accepts complex parameters, the parameter entry dialog automatically adjusts, as shown in Figure 7-2.

Figure 7-2 Function Accepting a Complex Parameter As Input

## Using Test View

To use Test View, follow these steps:

1. Select the Test View tab, then chose a function from the pulldown menu. The menu contains the read and navigation functions in your current data service, as well as the data service's procedures.
2. Enter parameters, if any.
3. Click on Execute to run the query and view the results.
4. If you have back-end data write permission, you can make changes in your data as well. Click on Edit Results and make any necessary changes. Then click Submit to update your data.

You can review your generated query in the Output window. See Auditing Query Performance for details.

### Running a Query That Needs No Parameters

In the case of a query such as getAllCustomers(), no parameters are needed (Figure 7-3).

Figure 7-3 Query Without Parameters

When you click Execute the query will run.

Results are returned in text or XML form. Click on the + next to a complex element (in this case, a table representation) to see more detailed results.

#### Editing Results

When you have appropriate update permissions — as is commonly the case with "sand box" testing — you can directly edit results using the Edit command (Figure 7-4).

Figure 7-4 Editing Query Results

You also have the option of adding a record once you are in Edit mode.

Figure 7-5 Adding a Record to a Data Set

The structure for the root XML Type will be added to end of the data set. You will need to supply the content, of course. If you right-click on the root element of your new record, you can also add complex child elements.

When you are satisfied with the changes click Submit.

### Running a Query Function With Simple Parameters

When your query requires one or multiple simple parameters, Test View display each parameter in its own field, identified by name and required type.

Figure 7-6 Function with Two Input Parameters

See Running a Query That Needs No Parameters on page 7-4 for details or executing a query and editing and submitting results.

### Testing a Query Function With Complex Parameters

Enterprise-scale queries often require a complex parameter type as input. For example, an inventory query may require a set of parameters which are based on a Web service supplying details of orders received. It is usually easier to just pass the entire object than to specify a large set of individual parameters.

When your query requires a complex parameter, the function will be listed with a parameter as in:

>     getProfileView(arg)

The arg parameter indicates that a complex parameter type is needed.

For such parameters Test View displays a box (Figure 7-2) into which you can:

- Paste the results of the most recently run query (assuming the results match the input requirements of the current function).
- Paste a template of the complex parameter into which you can enter necessary (that is, required primary key) values.
- Identify to an XML file to serve as input.
- Enter your complex parameter directly.

#### Using Prior Results as Input

For any given data service you can use results from a previously run query as input. This is particularly useful when invoking navigation functions, since navigation functions generally require complex parameters.

Note: When pasting prior results it's important to keep in mind that queries returning multiple results (arrays) cannot be input to functions looking for a single object as a parameter. For example, a function that gets orders for a particular customer is likely to return multiple orders. Those results cannot be used as input to a function that returns information about a particular customer.

The following steps show how results of a singleton query can be repurposed as input for a complex parameter.

1. Assume that you have first run a simple query, selecting information on a particular order. Then you want to get additional information on the customer who placed the order.

Results shown below contain elements called for by the function:

>     getElecOrderByOrdID(ORDER_ID)

located in the RTLServices/ElecOrder data service.

2. In the Test View parameter area supply a valid order ID such as ORDER_1_0.

Figure 7-7 Executing a simple parameterized query

3. Your results now contain the required customer ID. Select the getCustomer() relationship function from the dropdown list of available functions.
4. Click on the Paste Result button. Your previous results appear as an editable complex parameter in XML format (Figure 7-8).

Figure 7-8 Using Query Results in a New Query

Note: Your results have been returned as a singleton element in an array (highlighted in blue in Figure 7-8). The array element needs to removed before you can successfully execute your navigation function.

5. Edit your results to remove the ArrayOfELEC_ORDER element. The outermost elements of your XML document will change from:

>     <ns0:ArrayOfELEC_ORDER xmlns:ns0="urn:retailerType">
>     			<ns0:ELEC_ORDER TYPE="ELEC">
>     				<OrderID>ORDER_1_0</OrderID>
>     				...
>     			</ns0:ELEC_ORDER>
>     </ns0:ArrayOfELEC_ORDER>

to:

>     		<ns0:ELEC_ORDER TYPE="ELEC" xmlns:ns0="urn:retailerType">
>     			<OrderID>ORDER_1_0</OrderID>
>     			...
>     		</ns0:ELEC_ORDER>

6. After making the necessary changes click Execute. Results of your new query are based on the Customer XML type appear (Figure 7-9).

Figure 7-9 Complex Parameterized Query Results

#### Using the XML Type to Identify Input Parameters

You can automatically enter a template of the XML type of your data service. In Figure 7-10, a customer ID (CUSTOMER3) and order ID (ORDER_3_0), are provided through the template. Results are also shown.

Figure 7-10 Using XML Type Template to Guide Data Input

Template parameters are useful when you know the key parameters required by your query.

See Running a Query That Needs No Parameters on page 7-4 for details or executing a query and editing and submitting results.

#### Testing DSP Procedures

In Test View procedures are selected and run from the Select Functions drop-down list box in the same was that functions are selected. Running a procedure under Test View shows results only if the procedure returns data or a confirming message as to whether the operation was successful, for example.

## Limiting Array Results

You can filter query results through Test View to n instances of a single element such as the first five of an array of 5,000 customers.

Figure 7-11 shows a function where the results for RTLServices/Address/getAddress() are limited to three Address elements. Without such a limitation, all customer records would be returned.

Figure 7-11 Limiting Elements in an Array Result

## Starting Client Transaction Option

The Client Transaction Option supports functions that query more than multiple (two or more) relational sources using XA transaction drivers. By default this option is not selected, meaning that the NotSupported EJB transaction method is used. If the option is checked, the Required transaction mode will be used instead.

For general information on the subject see Transactions in EJB Applications WebLogic Server documentation.

## Validating Results

Test View results are validated against the data service's schema file when the Validate Results checkbox (shown in Figure 7-11) is selected. When active the following conditions will be flagged as invalid:

- An illegal type mismatch between source elements and the return type. For example, if an element of type string is mapped to an element of type date, the query results are invalid since a string cannot be guaranteed to cast successfully to a date.
- An element or attribute that is required in the schema is removed from the return type.
- An element or attribute is added to the return type.

Invalid results are reported in the Output window. Such results can be addressed by correcting the return type or associating the return type with a new, corrected schema. See Validating and Saving Your Return Type.

Notes: Whenever you attempt to edit results of a query, those results are re-validated. The criteria is the same as that used for the Validate Results option.

Results are validated by calling the XMLBean validate() method, currently documented at the following URL:

>     http://workshop.bea.com/xmlbeans/reference/com/bea/xml/XmlObject.html#validate()

## Disregarding a Running Query

An executing query can be ended through the Data Services Platform Console or by ending your server process. However, you can start a new query by changing your selection in Test View.

## Auditing Query Performance

You can audit query performance by activating Audit for your application. This is a one-time operation which is accomplished through the Data Services Platform Console.

When a query function is invoked through Test View and DSP auditing is enabled, basic validation and performance information appears in the WebLogic Workshop Output window (View —> Windows —> Output). You can find the most recent query results at the bottom of the Output pane.

Note: For details on enabling auditing and tuning audit options see Audit and Log Information in the DSP Administration Guide.

By default an audit includes such information as query compilation and execution time, user, server, and so forth (Figure 7-12).

Figure 7-12 Output Window Audit Results

# Analyzing Queries Using Plan View

Two types of information are available to help you analyze the design and performance of your query.

- Auditing information which appears in the Results window (see Auditing Query Performance).
- Query Plan View

Query Plan View helps in understanding how a query is designed. In addition to being able to view the plan, you can also print it (using the right-click menu option) or save it to a file in XML format.

## Using Query Plan View

The interface for Query Plan View is quite similar to that used for testing your query functions. You select a function or procedure from a drop down list and then click the Show Query Plan button (Figure 7-13).

Figure 7-13 Query Plan Right-Click Options

A query plan identifies the following query components:

- Joins
- Outer join
- Select statements
- Data sources
- Custom function calls
- Order-bys
- Remove duplicates
- Source access operator

Figure 7-14 Query Plan Fragment for RTLApp's Customer Data Service getCustomerByCustID(cust_id) Function

There are several ways that a query plan can be viewed:

- Tree view. A collapsible graphical presentation of the query plan.
- XML view. A collapsible XML document view of the query plan.
- Text view. Presents the information as text.

### Query Plan Information and Warnings

The query plan shows both informational and warning messages. When a section of the plan is flagged with a warning, the plan segment is highlighted in red. If you mouse over the segment, the warning message appears.

Informational messages also can appear with plan segments. Such segments are highlighted in yellow. The following table identifies the conditions associated with informational and warning messages.

Table 7-15 Informational and Warning Messages Associated With Query Plan Segments

Warning Message Type | Informational Message Type
--- | ---
Typematch. Typematch issues that will be resolved by the compiler (may affect performance) | Audit. Auditing has been set for this particular function (will affect performance).
No where clause. There is no predicate associated with the query function (will affect performance). | Cache. Function is cached (may enhance performance).
| SQL pushdown generation details.

### Printing Your Query Plan

A right-mouse option allows you to print a query plan to a printer or a file. Right-click on any node in the plan and select either the print or print to a file option.

If you print to a file the filename will be of type XML. The name of the file will be the function name followed by the letters _qp, as in:

>     getCustomerView_qp.xml

The file can be saved anywhere in your application.

## Analyzing a Sample Query

The following query is from the Data Services Platform RTLApp:

>     (RTLServices/OrderDetailView/getElecOrderDetailView(order_id, customer_id)

From the function signature you know that the query returns data related to order details after it is passed an order ID and a customer ID.

The following pseudocode describes the query:

for electronic orders matching CustomerID and OrderID
return order information and ship-to information
for credit card information matching an AddressID
return credit information and bill-to address information
for electronic line item information matching the line item in the order
return line item information

A compressed version of the query plan is shown in Figure 7-16.

Figure 7-16 Query Plan for getElecOrderDetailView()

The let statements represent mappings or projections in the data service. This can be useful when trying to trace performance issues.

The join conditions are identified in the plan as a left-outer join driven by a complex parameter. By definition, joins have left and right sides, each of which can contain additional joins. One of the best uses of the query plan is to see how the query logic works up the various data threads to return results, as shown in Figure 7-17.

Figure 7-17 Top Down Schematic of getElecOrderDetailView() Function

## Working With Your Query Plan

Two options are available in Query Plan.

- Expand All. This right-click menu option expands the currently selected element and any children. If applied to the top-most element in the plan, all elements are expanded.

### Match highlighting. When you click on a variable name any elements (open or closed) containing a match for that variable are highlighted. This feature helps you trace variables in the query plan.

### Identifying Problematic Conditions Through the Query Plan

When you show a query plan for a particular function, you may notice red or yellow highlighting of particular routines. These correspond to warnings or informational messages from the plan interpreter. For example, if a for statement is missing a where clause (potentially leading to slow performance or retrieval of a massive amount of data) a red warning will appear adjacent to the statement.

Figure 7-18 Query Plan Viewer Flagging a For Statement with a Missing Where Clause

## Simply mouse-over the highlighted section of the plan to view the information or warning.

# Creating an Ad Hoc Query

It can be useful to quickly enter and test queries. You can do this through any data service's Test View. Simply pull town the list of available functions and select the ad hoc query option (shown in Figure 7-19).

Figure 7-19 Selecting Ad Hoc Query Option From Test View

As the name implies, an ad hoc query is not limited to the currently selected data service. Any data service in the scope of the current application can be utilized.

Note: An ad hoc query remains available whenever the data service active when it was created is open to Test View. However, the ad hoc query is not visible in Source View and can only be saved by copying it to an external application.

## Sample Ad Hoc Queries

In Figure 7-20 a small query has been entered. Although the constructor function for the current data service was used (DataServices/CustomerDB/CUSTOMER), this was unnecessary.

Figure 7-20 Creating an Ad Hoc Query

If your query requires simple or complex parameters, these can be exposed using the Show Parameters button.

A Results pane below the Execute button will contain the data returned by the query (if any).

In the RTLApp sample application you can copy the code in Listing 7-1 into an ad hoc query pane. This query is designed to take several minutes to complete. It can also be used to experiment with monitoring and stopping executing queries through the Data Services Platform Console.

Note: In order to execute this query the applications Check Access Control option in Data Services Platform Console's General tab must be deselected. See DSP Administration Guide for details.

Listing 7-1 Sample Ad Hoc Query Executable From RTLApp's Test View

>     import schema namespace ns2="urn:retailerType" at "ld:DataServices/RTLServices/schemas/CustomerProfile.xsd";

>     declare namespace ns9="ld:DataServices/RTLServices/Customer";

>     declare function ns9:getCustomerSlowly() as element(ns2:CUSTOMER_PROFILE)* {

>         for $CUSTOMER_PROFILE in ns9:getCustomer(),
>             $c1 in ns9:getCustomer()[CustomerID lt $CUSTOMER_PROFILE/CustomerID],
>             $c2 in ns9:getCustomer()[CustomerID gt $CUSTOMER_PROFILE/CustomerID],
>             $c3 in ns9:getCustomer()[CustomerID eq $CUSTOMER_PROFILE/CustomerID],
>             $c4 in ns9:getCustomer()[CustomerID lt $CUSTOMER_PROFILE/CustomerID],
>             $c5 in ns9:getCustomer()[CustomerID gt $CUSTOMER_PROFILE/CustomerID],
>             $c6 in ns9:getCustomer()[CustomerID eq $CUSTOMER_PROFILE/CustomerID],
>             $c7 in ns9:getCustomer()[CustomerID = $CUSTOMER_PROFILE/CustomerID],
>             $c8 in ns9:getCustomer()[CustomerID != $CUSTOMER_PROFILE/CustomerID],
>             $c9 in ns9:getCustomer()[CustomerID = $CUSTOMER_PROFILE/CustomerID],
>             $c10 in ns9:getCustomer()[CustomerID !=

>     $CUSTOMER_PROFILE/CustomerID],
>             $c11 in ns9:getCustomer()[CustomerID eq $CUSTOMER_PROFILE/CustomerID],
>             $c12 in ns9:getCustomer()[CustomerID eq $CUSTOMER_PROFILE/CustomerID]
>     return $CUSTOMER_PROFILE
>     };

>     ns9:getCustomerSlowly()

Once an ad hoc query has been entered, its query plan can be reviewed. See Analyzing Queries Using Plan View.