![]() ![]() ![]() ![]() ![]() ![]() |
This chapter describes how to configure an Oracle Data Service Integrator dataspace including tasks such as creating administrative properties, managing memory, and enabling cache. It contains the following sections:
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:
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 Oracle Data Service Integrator Administration Console. It includes the following topics:
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).
You can select a data service from the Resource List to view the metadata about the data service.
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.
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:
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.
Oracle Data Service Integrator 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 Oracle Data Service Integrator by default.
WARNING: | Unlike SQL statements generated by Oracle Data Service Integrator, 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 Oracle Data Service Integrator Administration Console using the Substituted SQL Statements tab available through the Physical Sources category as shown in Figure 4-4.
Oracle Data Service Integrator 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 Oracle Data Service Integrator administrator defines and maintains substitution queries through the Oracle Data Service Integrator Administration Console.
The replacement query is executed instead of the original SQL query. The Oracle Data Service Integrator 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:
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
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.
There are several requirements regarding the substituted SQL query:
Note: | For queries using sub-queries, the column aliases need to be preserved by only the outermost subquery and not the inner subqueries. |
To create a substitute SQL query:
The system automatically tracks creation and last modified dates. An example for using the substitute query is available at 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.
http://localhost:7001/odsiconsole
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
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
Configuring server resources optimally depends on the physical resources of the machine on which you deploy Oracle Data Service Integrator, the anticipated load, and the type of dataspace you are deploying. Although the cached query plan count accelerates processing, it also consumes memory.
Oracle Data Service Integrator Administration Console allows you to control server resources using the following options:
For more information on tuning performance for WebLogic Server, refer to the WebLogic Server Performance and Tuning guide.
When memory management is enabled, Oracle Data Service Integrator uses memory-managed sort and join operators. A memory-managed operator uses the disk to limit memory consumption in the presence of large datasets.
Note: | The disk files are created in the <tmpdir>/serverName>/<dataSpaceName> folder. The tmpdir is set by the java.io.tmpdir system property, unless you overwrite this using the aldsp.tmpdir system property. The <tmpdir>/serverName>/<dataSpaceName> folder is deleted when the dataspace is no longer active or when the dataspace is deleted. Therefore, ensure that this folder is not shared by multiple Oracle Data Service Integrator servers with same name and with the same dataspaces. |
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 two sort operators and three 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 Oracle Data Service Integrator-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:
WebLogic Server prioritizes work and allocates threads based on administrator-defined parameters and actual run-time performance and throughput. Using Work Managers, you can configure scheduling guidelines and associate them with one or more applications, or with particular application components. This enables you to configure how an application prioritizes the execution of its work.
Using Oracle Data Service Integrator, you can similarly use a Work Manager associated with a dataspace to specify scheduling guidelines. This enables you to configure the minimum or maximum number of threads allocated to a dataspace, for example. You could also define a Work Manager to specify the request class which enables you to ensure that high priority work is scheduled before less important work, even if the lower priority work was submitted first.
You can create a custom Work Manager for an Oracle Data Service Integrator dataspace project using the WebLogic Server Administration Console. When creating a Work Manager, you must use the following format when assigning a name to the Work Manager:
wm/dataspace_name-default-workmanager
where dataspace_name is the name of the Oracle Data Service Integrator dataspace project.
It is recommended that you create and configure a custom Work Manager before creating the associated dataspace project. If you create the dataspace project before creating the Work Manager, you will need to restart WebLogic Server to have the dataspace become associated with the custom Work Manager. You can then use the WebLogic Server Administration Console to modify the parameters of the Work Manager, as required.
WebLogic Server uses the global default Work Manager if a custom Work Manager does not exist. For more information about using WebLogic Server Administration Console to manage Work Managers and their associated constraints and request classes, see Using Work Managers to Optimize Scheduled Work.
Multiple Oracle Data Service Integrator dataspace projects cannot share the same Work Manager, but you can create two or more Work Managers that share the same underlying constraints. You might want to do this if you have two dataspace projects that need to access a particular JDBC connection pool, for example, and you want to prevent simultaneous access to the JDBC connection pool from exceeding the size of the pool.
To share Work Manager constraints in this case, do the following:
An administrative property is a user-defined property that you can configure using the Oracle Data Service Integrator 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 Chapter 5, “Securing Oracle Data Service Integrator 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 Oracle 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:
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")
You can change this value later, if required.
The property appears in the Edit Administrative Property table.
Note: | The default value for the property is used in any get-property() call using the deleted property. |
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. |
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.
You can monitor active updates the same way as active queries.
In some instances, Oracle Data Service Integrator may not be able to read data from a database table because another dataspace has locked the table, causing queries issued by Oracle Data Service Integrator 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:
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:
SQL SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
You can preload Oracle Data Service Integrator projects and the dataspaces they contain whenever an Oracle Data Service Integrator-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.
setDomainEnv.cmd
file located in: <BEA_HOME>\user_projects\domains\
base_domain\bin
-Dcom.bea.dsp.oam.console.common.warmupTree=true
![]() ![]() ![]() |