> Administration Guide > Configuring ALDSP Resources

Administration Guide

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Configuring ALDSP Resources

This chapter describes how to configure an ALDSP dataspace including tasks such as creating administrative properties, managing memory, and enabling cache. It contains the following sections:

 

Configuring the Cache and Log for a Dataspace 

You can view and configure settings for a dataspace such as caching and logging using the General tab in the System Administration category.

To configure general dataspace settings:

  1. Select the System Administration category and then the dataspace from the navigation tree. The General tab appears as shown in Figure 4-1.
    2. Figure 4-1 General Dataspace Settings Page


    General Dataspace Settings Page

  2. Acquire the lock to make changes to the general configuration of the dataspace.
  3. You can enable data caching and logging level details using this page. For more information on data caching, refer to Configuring Query Results Cache. For more information on logging, refer to Working With Audit and Log Information.
  4. Click Save > Activate Changes.

 

Using the Physical Sources Category

The Physical Sources category allows you to configure and modify the resource end points, view the location of physical data sources, and create substitute SQL statements.

This section provides details about configuring each of these features using the Physical Sources category on the ALDSP Administration Console. It includes the following topics:

Viewing Physical Data Source Locations

You can view a list of data services and function libraries that use the defined relational databases. Click the Where Used tab to view the list of data services and the corresponding paths (Figure 4-2).

Figure 4-2 Physical Data Services Relational Dependencies

Physical Data Services Relational Dependencies

You can select a data service from the Resource List to view the metadata about the data service.

Modifying Data Source End Points

When you move dataspaces from development to production server, you may need to change the location of data sources or names of other artifacts. For example, if you are using sample data sources during development to protect confidential or otherwise secured information, you need to substitute a new data source with the actual data for the test version. You can make these changes through the Physical Sources category as shown in Figure 4-3.

Figure 4-3 Setting End Points for Relational Sources

Setting End Points for Relational Sources

By modifying the data source endpoints, you can change the name and location of a data source as well as the target names of subordinate artifacts. In the case of relational sources this includes names of catalogs, schemas, packages, tables, stored procedures, views, and relational functions. End point modifications are effective until they are further modified or reverted to the original value.

To reset the original value to the end point name:

  1. Acquire the lock by clicking Lock & Edit.
  2. Click Reset to original value. This option will not revert the value to the previous setting, instead it will directly revert it to the original name. If you assign some intermediate target names and click Reset to original value, the values revert to the same values as those in the Original Value column.
  3. Click Save > Activate Changes.
Note: If you change the end point for an artifact, some of the properties for the artifact should match with the old source. For example, the Vendor type and version properties for a relational data source should be identical with the old source.

Table 4-1 identifies the artifacts whose end point settings can be changed.

Table 4-1 Artifacts for which End Points can be Modified Through the ALDSP Administration Console
Data Source Type
Artifact
Relational
Data source name and location
 
Catalog
 
Schema
 
Package
 
Table
 
Views
 
Relational functions
 
Stored procedure
Web Service
Web service name and location
 
Service
 
Port
 
Operation
XML Content
Data source name and location
Delimited File Content
Data source name and location

Substituting SQL Statements

ALDSP uses SQL to access relational data sources. At compilation time, the built-in query optimizer determines the best execution strategy for backend sources. Then SQL queries are generated and submitted to underlying databases.

SQL queries generated by the relational wrapper are specific to each underlying database. While the SQL queries that are generated typically produce good results, there are cases when further optimization of the generated queries is desirable. In most RDBMS systems, such optimization is done through execution hints.

SQL statement substitution allows you to add hints to generated SQL queries by providing edited SQL statements that will be executed instead of the query that is generated by ALDSP by default.

WARNING: Unlike SQL statements generated by ALDSP, substituted SQL statements are passed to the underlying database without validation. For this reason, users are strongly advised against using this feature for any purpose other than providing hints to the database. It is also recommended that prior to deployment any substituted SQL statement be tested against its generated counterpart to make sure that the expected performance advantage is obtained.

Substitute SQL statements are created and registered in the ALDSP Administration Console using the Substituted SQL Statements tab available through the Physical Sources category as shown in Figure 4-4.

Figure 4-4 Substituted SQL Statement Dialog Box

How SQL Statement Substitution Works

ALDSP server maintains a substitution table between the original generated SQL queries and any replacement queries supplied by the user. Only SQL queries specified by user will be substituted.

The ALDSP administrator defines and maintains substitution queries through the ALDSP Administration Console.

The replacement query is executed instead of the original SQL query. The ALDSP runtime engine reads the SQL result set using type/column information of the original query. Potential problems related to incorrect substitution, which violates the conditions listed in Requirements for SQL Statement Substitution include the following problems:

Supporting Externalized End Points in Substituted Queries

In both the generated and substitute queries, a special syntax is used to support externalized end points (see “Modifying Data Source End Points” on page 4-5 for details). The following substituted queries show this syntax (emphasis added): 

SELECT /*+ FIRST_ROWS (10)*/ t1."BILL_TO_ID" AS c1, t1."C_ID" AS c2, t1."DATE_INT" AS c3, t1."ESTIMATED_SHIP_DT" AS c4, 
t1."HANDLING_CHRG_AMT" AS c5, t1."ORDER_DT" AS c6, t1."ORDER_ID" AS c7, t1."SALE_TAX_AMT" AS c8, 
t1."SHIP_METHOD_DSC" AS c9, t1."SHIP_TO_ID" AS c10, t1."SHIP_TO_NM" AS c11, t1."STATUS" AS c12, 
t1."SUBTOTAL_AMT" AS c13, t1."TOTAL_ORDER_AMT" AS c14, t1."TRACKING_NO" AS c15 
FROM {RTLAPPLOMS}.{CUSTOMER_ORDER} t1
Note: If you are adding SQL fragments (such as string literals) in your substituted SQL statement, you also need to use the convention of doubling opening curlie braces.
Note: For example: 
SELECT t1.ID FROM CUSTOMER() WHERE $i/ID > ‘a{bee}c’ return $i/ID

is translated to: 

SELECT t1.ID FROM {CUSTOMER} t1 WHERE t1.ID > ‘a{{bee}c’

Depending on your requirement, specify replacement queries using the same name placeholders as the original query. At the end of the SQL generation stage the original names are replaced with the current end-point names. The original names are used if no end-point setting is found.

Requirements for SQL Statement Substitution

There are several requirements regarding the substituted SQL query:

Creating Substitute SQL Query Statements

To create a substitute SQL query:

  1. Click Lock & Edit to acquire the lock.
  2. Select the Physical Sources category from the category list and then select the relational databases option from the navigation pane.
  3. Navigate and select the relational data source for which you want to create the substitute query and then select the Substituted SQL Statement tab.
  4. Click New. This displays the page where you can specify the SQL statement substitution rule as shown in Figure 4-5.
    5. Figure 4-5 Rules for SQL Statement Substitution


    Rules for SQL Statement Substitution

  5. Specify the following details on this page:

The system automatically tracks creation and last modified dates. An example for using the substitute query is available at SQL Statement Substitution Example.

SQL Statement Substitution Example

The order in which SQL statement substitutions are established is not fixed. Therefore, the example in this section and the steps involved are only one approach to creating and testing SQL statement substitution.

  1. Setup your environment with these actions:
  1. Set the base SQL statement audit property to Always (Figure 4-6), which means that the base SQL statement will always be returned. (See also Setting Individual Auditing Properties.)
    2. Figure 4-6 Setting the basesql Property to Always be Returned


    Setting the basesql Property to Always be Returned

  2. Select your relational data source in the ALDSP Administration Console (Figure 4-4).
  3. Select the Substituted SQL statements option.
  4. Click New and enter the following in the resulting dialog box:
  1. Click Save > Activate Changes.
  2. In your Eclipse IDE dataspace, run your query (such as CUSTOMER) in Test. Notice (Figure 4-7) that a basesql version of generated SQL statement is created.
    3. Figure 4-7 Output from RTLApp CUSTOMER_ORDER() Query with basesql Result Highlighted


    Output from RTLApp CUSTOMER_ORDER() Query with basesql Result Highlighted

  3. On the Console tab scroll down until you locate the basesql version of the query you just generated (also shown in Figure 4-7). Copy this version of the query to your clipboard. A sample query appears below:
    4. SELECT t1."BIRTH_DAY" AS c1, t1."CUSTOMER_ID" AS c2, t1."CUSTOMER_SINCE" AS c3, 
          t1."DEFAULT_SHIP_METHOD" AS c4, t1."EMAIL_ADDRESS" AS c5, t1."EMAIL_NOTIFICATION" AS c6, 
          t1."FIRST_NAME" AS c7, t1."LAST_NAME" AS c8, t1."LOGIN_ID" AS c9, t1."NEWS_LETTTER" AS c10, 
          t1."ONLINE_STATEMENT" AS c11, t1."SSN" AS c12, t1."TELEPHONE_NUMBER" AS c13 
        FROM {RTLCUSTOMER}.{CUSTOMER} t1
  4. Return to the ALDSP Administration Console, Substituted SQL Statements area and paste the basesql statement into the field labeled Generated SQL Statement.
  5. Paste the basesql statement into the field labeled Substituted SQL statement.
  6. Edit the substituted statement based on supported hints provided by the underlying database. A sample edited query restricting results to the first 10 rows in an Oracle database (emphasis added) — appears below:
    7. SELECT /*+ FIRST_ROWS (10)*/ t1."BIRTH_DAY" AS c1, t1."CUSTOMER_ID" AS c2, t1."CUSTOMER_SINCE" AS c3, 
          t1."DEFAULT_SHIP_METHOD" AS c4, t1."EMAIL_ADDRESS" AS c5, t1."EMAIL_NOTIFICATION" AS c6, 
          t1."FIRST_NAME" AS c7, t1."LAST_NAME" AS c8, t1."LOGIN_ID" AS c9, t1."NEWS_LETTTER" AS c10, 
          t1."ONLINE_STATEMENT" AS c11, t1."SSN" AS c12, t1."TELEPHONE_NUMBER" AS c13 
        FROM {RTLCUSTOMER}.{CUSTOMER} t1
  7. Click Save > Activate Changes.
  8. Return to the Eclipse IDE and re-run your query in Test mode. Notice in the Output pane that your substitute query appears in the SQL Statement area.
  9. Select the CUSTOMER () query from the Plan view. Click Show Query Plan. Notice that the resulting plan contains the substituted SQL as well as the named of the substituted SQL statement.
    10. Figure 4-8 Query Plan Displaying Substituted SQL Query


    Query Plan Displaying Substituted SQL Query

 

Setting the Server Resources

Configuring server resources optimally depends on the physical resources of the machine on which you deploy ALDSP, the anticipated load, and the type of dataspace you are deploying. Although the cached query plan count accelerates processing, it also consumes memory.

ALDSP Administration Console allows you to control server resources using the following options:

To set the server thread count:

  1. Select the Runtime tab from the System Administration category.
  2. Acquire the lock.
  3. In the Server Resources section, specify the value for the maximum number of query plans cached and the maximum number of threads for a single query, as shown in Figure 4-9.
    4. Figure 4-9 ALDSP Administration Console: Runtime Tab


    ALDSP Administration Console: Runtime Tab

  4. Click Save > Activate Changes.

For more information on tuning performance for WebLogic Server, refer to the WebLogic Server Performance and Tuning guide.

Note: ALDSP 3.2 enables you to use Work Managers to manage the resources of a dataspace. See “Dataspace Deployment Using Work Managers” in the ALDSP 3.2 New Features Supplement

 

Item-based Memory Management

When memory management is enabled, ALDSP will use memory-managed sort and join operators. A memory-managed operator uses the disk to limit memory consumption in the presence of large datasets.

Each operator is only allowed to have up to a set maximum number of items in memory at a time. If the number of items to be processed exceeds the maximum then the operator must use the disk to complete its task. Here "items" are things that are being operated upon (joined or sorted).

Note: Different query workloads usually involve different size items.

For example, consider a query plan that contains 2 sort operators and 3 join operators. Assume that the maximum number of items per operator is 40,000. Regardless of the overall amount of data being processed by the query, this query plan will result in at most (2 + 3) * 40,000 = 200,000 items being held in memory at a time.

The maximum number of operators refers to the overall number of operators that may be concurrently running across all query plans being processed at a given time by the ALDSP-enabled server.

The maximum number of operators and the maximum number of items together provide a means to control the overall memory consumption of the server and can help guard against out-of-memory exceptions. When needed, these values should be adjusted based on workload and data characteristics, as the item count is only a coarse metric for memory consumption because item sizes affect the actual memory used as well.

To enable and configure memory management:

  1. Click the Runtime tab from the System Administration category.
  2. Acquire the lock.
  3. From the Memory Management section (Figure 4-9), select Enable Memory Management.
  4. Specify the limit for the maximum number of operators per dataspace using the Maximum Operators box. This allows you to restrict the memory usage by operators per dataspace.
  5. Specify the limit for the maximum units that can be sorted or joined (items) by a single operator in memory. If this limit exceeds, then the item is stored in the temporary file system space.
  6. Click Save > Activate Changes.

 

Using Administrative Properties 

An administrative property is a user-defined property that you can configure using the ALDSP Administration Console. The value of an administrative property can be used in XQuery functions, either in data service functions or XQuery functions for security.

Note: For information on XQuery functions for security, see Securing ALDSP Resources.

An administrative property allows you to specify function parameters that can be easily changed by the administrator, without modifying the body of either the data service function or XQuery function for security.

Any data service within a dataspace can use the administrative property value. The property value can be accessed using XQuery with the BEA function get-property(). The function takes the name of the property as an argument and returns the value as a string. It also takes an argument that serves as the default value for the parameter. This value is used if the property is not configured in the console.

The following example illustrates an XQuery Function Library function that uses an administrative property: 

declare function f1:getMaximumAccountViewable() as xsd:decimal { 
   let $amount := fn-bea:get-property("maxAccountValue", "1000.00") 
                                   cast as xsd:decimal
   return $amount
};

To manage administrative properties:

  1. Click the name of the dataspace in the Navigation pane.
  2. Click the Administrative Properties tab from the System Administration category. The list of property names currently defined appears in the table, as illustrated in Figure 4-10.
    3. Figure 4-10 Administrative Properties Tab


    Administrative Properties Tab

  3. Acquire the lock by selecting Lock & Edit.
  4. To add a property, complete the following:
    1. Enter a name for the property in the Property Name field of the Add Administrative Property table.

      2. The name must match the name property passed to the get-property() function used to access the properties value. For example: 

      fn-bea:get-property("maxAccountValue", "1")
    2. Optionally, enter an initial value for the property.

      3. You can change this value later, if required.

    3. Click Add Property.

      4. The property appears in the Edit Administrative Property table.

  5. To change a property value:
    1. Acquire the lock.
    2. Enter a new value in the Property Value field of the Edit Administrative Property table.
    3. Click Save > Activate Changes.
  6. To delete a property:
    1. Acquire the lock and select the property from the Edit Administrative Property table.
    2. Click Delete.
    3. Click Activate Changes to confirm deletion of the property.
Note: The default value for the property is used in any get-property() call using the deleted property.

 

Monitoring Active Queries and Updates

Using the Operations category in the console, you can monitor long-running active queries and updates for a dataspace. The Operations category pertains to the runtime monitoring of deployed artifacts. In other words, the Operations category depends on the core (deployed) session. By contrast, other categories such as Service Explorer and Security relate to the session in progress.

Figure 4-11 illustrates an active ad hoc query running on the server for the RTLApp dataspace.

Note: Active queries and updates can be monitored only at the dataspace level.
Figure 4-11 Monitoring the Status of Active Ad Hoc Queries

Monitoring the Status of Active Ad Hoc Queries

If an active query or an update is running for a long time on the server then the information is displayed in the table. This table lists the XQuery functions under the Function Name field.

If a query is taking longer than the expected time to retrieve data, you can also kill a query by clicking Kill Query.

In case of ad hoc queries, you can view the ad hoc query by clicking the function name in the Function field. This allows you to view the ad hoc query that is running on the server as shown in Figure 4-11.

Figure 4-12 Ad Hoc Query Displayed on ALDSP Administration Console

Ad Hoc Query Displayed on ALDSP Administration Console

You can monitor active updates the same way as active queries.

 

Setting the Transaction Isolation Level

In some instances, ALDSP may not be able to read data from a database table because another dataspace has locked the table, causing queries issued by ALDSP to be queued until the dataspace releases the lock. To prevent this, you can set the transaction isolation to read uncommitted in the JDBC connection pool on your WebLogic Server.

To set the transaction isolation level:

  1. Start the WebLogic Administration Console in a web browser by opening the following URL:
    2. http://<HostName>:<Port>/console

    For example, to start the Administration Console for a local instance of WebLogic Server (running on your own machine), type the following URL in a web browser address field: 

    
http://localhost:7001/console/
  2. Expand Services > JDBC > Data Sources > <datasourcename>.
  3. Select the Connection Pool tab as illustrated in Figure 4-13.
    4. Figure 4-13 WebLogic Administration Console Connections Tab


    WebLogic Administration Console Connections Tab

  4. Expand the Advanced section. The page expands to include the Advanced Options section.
  5. Acquire the lock.
  6. In the Init SQL field, enter the following:

    7. SQL SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  7. Click Save > Activate Changes.

 

Preloading ALDSP Projects and Dataspaces

You can preload ALDSP projects and the dataspaces they contain whenever an ALDSP-enabled server is started by adding a property to the setDomainEnv.cmd file. If you have many projects and/or dataspaces, doing this can significantly improve initial Console performance.

To add this system property:

  1. Stop the ALDSP-enable server if it is running.
  2. Open the setDomainEnv.cmd file located in: <aldsp_home>\samples\domains\aldsp\bin

    3. where <aldsp_home> is the home directory for ALDSP, for example, c:\bea\aldsp_3.0

  3. Add the following as a VM startup property: 
    4. -Dcom.bea.dsp.oam.console.common.warmupTree=true
  4. Save and close this file.
  5. Start or restart your server.

  Back to Top       Previous  Next