Administration Guide

Configuring Access to Relational Databases

Before a BEA Liquid Data for WebLogic query can access data in a relational database, the relational database must be configured as a Liquid Data data source. Once configured according to the instructions in this chapter, relational databases with data source descriptions will show up as data sources in any Liquid Data EJB client, such as the Data View Builder, that connects to this Liquid Data server.

Configuring a relational database source description for Liquid Data consists of several discrete tasks: configuring a WebLogic Server (WLS) JDBC connection pool, then configuring the relational database as a WLS JDBC data source that uses that JDBC connection pool, and finally adding a Liquid Data source description for the relational database that uses the JDBC resources.

The following sections are included:

• Creating a JDBC Connection Pool
• Creating a JDBC Data Source
• Creating a Relational Database Data Source Description
• Summary of Configured Data Sources
• Modifying a Relational Database Source Description
• Removing a Relational Database Source Description

 

---

Connection Pool URLs and Driver Names for JDBC Data Sources

To configure JDBC connection pools for your data sources, you need to provide the URL to your database in the appropriate format for the database type and the full package name of the JDBC driver used by the database. Formats for the database URL and driver class name vary depending on the type of database you are using.

The following table provides URL formats and driver class names for supported databases.

 

 

---

Creating a JDBC Connection Pool

For complete information on how to create Java Database Connectivity (JDBC) Connection Pools in WebLogic Server, see JDBC (http://download.oracle.com/docs/cd/E13222_01/wls/docs81/ConsoleHelp/jdbc.html) in the WebLogic Server documentation.

In order to add a relational database data source description to Liquid Data, you first need to create a JDBC connection pool in WLS for the data source to use. Creating a JDBC connection pool consists of first creating the pool and then deploying it on a target server.

To create and deploy a JDBC connection pool:

In the left pane, expand the Services node.
Expand the JDBC node.
Click on Connection Pools.

A table of existing connection pools, if any, is shown.

Click the Configure a new JDBC connection pool text link.
On the General tab, provide information about the JDBC connection pool you want to create as described in the following table.

Table 7-2 JDBC Connection Pool Configuration

Field	Description
Name	Name of the JDBC connection pool. This can be any name by which you choose to identify the pool. JDBC Data Source that uses this pool must use the exact pool name used here. For example, we can create a connection pool name for a Wireless data source called MyWireless_POOL.
URL	URL for the database where your data source resides. The URL is passed to the driver to create the physical database connections. For our example Oracle database, we use the following URL: jdbc:oracle:thin:@<hostname>:1521:SID Note: The required URL format varies depending on database type. See Connection Pool URLs and Driver Names for JDBC Data Sources for a complete list of URL formats and drivers for each supported database type.
Driver Classname	Full package name of the JDBC 2-tier driver class used to create the physical connections between the WebLogic Server and the DBMS for this Connection Pool. For our Oracle example, we use the following Oracle driver classname: oracle.jdbc.driver.OracleDriver Note: Driver class names vary depending on database type. See Connection Pool URLs and Driver Names for JDBC Data Sources for a complete list of URL formats and drivers for each supported database type.
Properties	Sets the list of properties passed to the 2-tier JDBC Driver to use when creating physical database connections. The list consists of attribute=value tags, separated by semi-colons. WLS Administration Console view will automatically reformat properties and add other details about the specified database when you click Create. The following examples are based on our Broadband, CRM, and Wireless scenario: user=broadband; password=broadband user=crm; password=crm user=wireless; password=wireless (You can add these as comma separated attribute=value pairs as shown above or one per attribute=value pair per line separated by a return.)
Password	Optional—use only if you are implementing security. Password attribute passed to the tier-2 JDBC driver when creating physical database connections; If set, this value overrides any password defined in Properties. The value is stored in an encrypted form in the config.xml file and when displayed on the administration console. Use this method to avoid storing passwords in clear text in config.xml.


 
Click Create.

The new JDBC connection pool you created is shown in the table.

In the table of connection pools, click on the name of the new JDBC connection pool you just created.

The Configuration and Monitoring tabs for that pool are displayed.

Click on the Configuration tab.
Click on the Connections tab and set the Maximum Capacity.

Note: This is not a required step, but to facilitate running and testing the data sources, we recommend re-setting Maximum Capacity on the connection pool to some number greater than 1. For complete information on how to create Java Database Connectivity (JDBC) Connection Pools in WebLogic Server, see JDBC in the WebLogic Server documentation.

Click on the Targets tab.

The name of your Liquid Data server should be listed under Available Servers.

Select the Liquid Data server in Available and click the right arrow button to move the server into the Chosen list.
Click Apply.

 

---

Creating a JDBC Data Source

For complete information on how to create a JDBC data source in WebLogic Server, see JDBC in the WebLogic Server documentation.

Once you have created a JDBC connection pool, the next step in configuring a Liquid Data relational database data source is to create a JDBC data source in WLS using the JDBC connection pool that you just configured.

Creating a JDBC data source consists of first creating the data source and then deploying it on a target server. You will need to configure this new data source to use the JDBC connection pool you just created.

 

To create and deploy a JDBC data source:

In the left pane, expand the Services node.
Expand the JDBC node.
Click on Data Sources.

A table of existing data sources, if any, is shown.

Click the Configure a new JDBC Data Source connection pool text link.
On the General tab, provide information about the JDBC data source you want to create as described in Table 7-2. (The fields described in the table are required—other optional fields are also displayed on this tab.)
Click Create.

The new JDBC data source you created is shown in the table.

In the table of JDBC data sources, click on the name of the new JDBC data source you just created.

The Configuration and Monitoring tabs for that JDBC data source are displayed.

Click on the Configuration tab.
Click on the Targets tab.

The name of your Liquid Data server should be listed under Available Servers.

Select the Liquid Data server in Available and click the right arrow button to move the server into the Chosen list.
Click Apply.

 

---

Creating a Relational Database Data Source Description

Once you have created a JDBC connection pool and JDBC data source for the relational database, you can create a data source description that tells Liquid Data how to connect to the relational database.

Note: You must log in with modify access before you can add a data source description. For more information, see Defining Liquid Data Roles and Groups.

To create a data source description for a relational database:

In the left pane, click the Liquid Data node.
In the right pane, click the Configuration tab.
Click the Data Sources tab.
Click the Relational Databases tab.
Click the Configure a new Relational Database source description text link.

The configuration tab for creating a new relational database Liquid Data source description is displayed.

Figure 7-4 Configuring a Liquid Data Source Description for a Relational Database

" title="Configuring a Liquid Data Source Description for a Relational Database" width="628" height="564" align="middle" border="0" hspace="0" vspace="0">


 

Fill in the fields as described in the following table:

Note: All names and values that you provide are case-sensitive.

Table 7-5 Liquid Data Relational Database Data Source Description 

Field	Description
Name	Logical name of the database—it is a Liquid Data source description used to register the relational database data source with the Liquid Data server. You can choose any meaningful name.
Data Source Name	Data source name, which must match the JNDI name for the JDBC data source created in Creating a JDBC Data Source.
User Name	An optional field specifying a WebLogic user name. When this field is blank, the WebLogic user name authenticated in the application (for example, a web application that uses Liquid Data to access data) is passed down to the JDBC connection pool. When you specify a WebLogic user name in this field, the specified name is passed down to the JDBC connection pool instead of the application user name. This is useful if the JDBC connection pool has a security policy associated with it and you do not want to configure your JDBC connection pool security policies with your application user name credentials. When you use this field, only the specified WebLogic user needs access to the JDBC connection pool for database query access.
Password	An optional field specifying the WebLogic password corresponding to the user name specified in the Liquid Data Relational Data Source User Name field.
Schema	Name of the schema (or schemas) you want to use for this Liquid Data data source. While this field is not required, BEA recommends that you specify a schema for your databases. The schema will limit the scope of the schema elements available to liquid data. On some databases, if you do not specify a schema, you might also run into JDBC or database limits such as the maximum number of open cursors. If you run into those types of limit, you must either specify a schema or increase the number of cursors for the JDBC and/or database configuration. You can specify multiple schemas by entering comma-separated schema names, as in the following example which specifies both the WIRELESS and BROADBAND schemas: WIRELESS,BROADBAND Specifying multiple schema names allows you to join across those schema (or select from tables in both schema) in a single query. For example, if you create a data source referencing both the WIRELESS and BROADBAND schema, and then create a query that uses elements in both schema, Liquid Data generates a single query to the database server. Conversely, if you create two separate data sources, one referencing the WIRELESS schema and one referencing the BROADBAND schema, and you create a query referencing tables in both schema, Liquid Data sends separate queries to each schema. The requirements for setting the schema name vary depending on the relational database you are using: Oracle—Schema name corresponds to the name of the Oracle schema, which is typically the name of an Oracle user ID. If you do not specify a schema, all of the schema available to the Oracle user ID configured in the JDBC connection pool are shown. PointBase—Schema name corresponds to a database name. The schema is required for PointBase (without it, no schema elements are available to Liquid Data). Microsoft SQL Server—Schema name corresponds to the catalog owner, such as dbo. Same as the database owner. Schema name must match the catalog or database owner for the database to which you are connecting. DB2—Schema name corresponds to the catalog owner of the database, such as db2admin. (DB2 often has many databases.) Sybase—Schema name corresponds to the database owner. Schema name must match the database owner for the database to which you are connecting. Informix—Not needed for Informix data sources.
Catalog	Optional or required (depending on the RDBMS you are using)—Name of the catalog you want to use for this Liquid Data data source. Leave blank to use all catalogs or if the RDBMS system you are using does not support the notion of catalogs. The requirements for setting the catalog name vary depending on the RDBMS you are using: Oracle—Leave blank; do not specify a catalog parameter. PointBase—Leave blank; do not specify a catalog parameter. PointBase has only one catalog called PointBase. Microsoft SQL Server—Catalog name is the database name. DB2—Leave blank; do not specify a catalog parameter. Sybase—Catalog name is the database name. Informix—Leave blank; do not specify a catalog parameter. The user name specified in the JDBC Connection Pool must have sufficient privileges in the RDBMS to use this catalog. (See Creating a JDBC Connection Pool.)
Table Pattern	Optional—A pattern used to filter the tables by name. Special characters are: _ An underscore character is used to match any single character. % A percent sign is used to match of zero or more characters. You can also enter a comma separated list to specify multiple filter patterns. For example, the following list: CUSTOMER, %PROD%, ORDERS_ would match the following tables: CUSTOMER, PRODUCT, PRODUCTS, NEWPRODUCTS, ORDERS1, ORDERS9 but would not match the following tables: CUSTOMERS, PRO_DUCT, ORDERS_1
Shared Connection	Toggle to set shared connection on (checked) or off (cleared). On—If you have a shared connection, it means that all the EJB instances share a single JDBC connection per data source. Off—Without a shared connection, the Liquid Data server can use multiple JDBC connections per data source.
Isolation Level	Sets the transaction isolation level. On—If Shared Connection is selected (checked), setting the transaction isolation level has no effect. The Liquid Data server always uses the JDBC default (TRANSACTION_READ_COMMITTED). Off—If Shared Connection is not selected (cleared), you can select one of the following transaction isolation levels: TRANSACTION_READ_UNCOMMITTED—The transaction can view uncommitted updates from other transactions. TRANSACTION_READ_COMMITTED—The transaction can view only committed updates from other transactions. This is the default setting. TRANSACTION_REPEATABLE_READ—Once the transaction reads a subset of data, repeated reads of the same data return the same values, even if other transactions have subsequently modified the data. TRANSACTION_SERIALIZABLE—Simultaneously executing this transaction multiple times has the same effect as executing the transaction multiple times in a serial fashion. For information on JDBC transaction isolation levels, see "transaction-isolation" and "isolation-level" in weblogic-ejb-jar.xml Document Type Definitions (under the subheading "5.1 weblogic-ejb-jar.xml Deployment Descriptor Elements") in the WebLogic Server documentation.
Maximum Connections	Optional—Specifies maximum number of connections the Liquid Data server can use to access this data source. The default is 0, which indicates that you are not limiting the number of connections. Has no effect if the value of Shared Connections is set to on (checked). Note: If the number of concurrent queries to the RDBMS data source exceeds the value in Maximum Connections (unless the value is 0, specifying no maximum), additional query executions will fail with a "No Connection Available" response.
SQL Call Description File	If you have stored procedures or you want to configure your own SQL queries as data sources you can access through Liquid Data, you must create and specify a SQL Call Description File (SCDF) that defines the stored procedures and/or SQL query. The SCDF file must reside in the <ld_repository>/sql_calls directory. For details on configuring access to your stored procedures and SQL queries, see Defining Stored Procedures and SQL Queries in Building Queries and Data Views.


 
Click Create.

The Administration Console displays the new relational database data source description in the summary table.

Note: You must configure access to this data source description, as described in Configuring Secure Access to Data Source Descriptions.

 

---

Summary of Configured Data Sources

The summary table shows a list of configured data sources of a particular type and a subset of configuration information for quick scanning. From the summary list, you can do the following:

• Find the entry for a particular data source in the table on the tab of the Liquid Data Administration Console corresponding to the data source type (for example, Relational Databases).
• Click on the data source name to modify its configuration.
• If you want to set a security policy for the data source (and security in enabled for your Liquid Data domain), click Define Security Policy or Edit Security Policy on the data source to which you want to assign security. If the object already has a policy defined, the link is labeled "Edit Security Policy"; if the object has no defined policy, the link is labeled "Define Security Policy." For more information, see Configuring Secure Access to Data Source Descriptions.
• Remove an existing data source by clicking the trash can next to it.

Note: You can also view all data sources from the All Data Sources configuration tab on the Liquid Data node in the Administration Console, as described in Viewing and Accessing All Configured Data Sources.

 

---

Modifying a Relational Database Source Description

You can changed the configured settings in a data source description for a relational database. For example, you might want to make a simple change to the row prefetch settings, or you might want to make more fundamental changes, such as changing the JDBC connection pool and data source used by the Liquid Data source, or changing the target servers or clusters in which the data source is deployed.

For most configuration changes, you will need to verify the operation of any queries that depend on the changed data source configuration. To make fundamental changes in underlying JDBC connection pools and data sources, you will also need to ensure that you set up the new JDBC connection pools and data sources first, before you re-assign the existing Liquid Data sources to them. For more information, see Creating a JDBC Connection Pool and Creating a JDBC Data Source.

Modifying Data Source Description Settings

Note: You must log in with modify access before you can modify a data source description. For more information, see Defining Liquid Data Roles and Groups.

To make simple changes to the data source description settings for a relational database, such as the row prefetch settings:

In the left pane, click the Liquid Data node.
In the right pane, click the Configuration tab.
Click the Data Sources tab.
Click the Relational Databases tab.

A table of configured Liquid Data data sources is shown.

Click on the data source description that you want to modify.
Change the settings as needed.
Click Apply.
Verify the operation of any existing queries that depend on the data source configuration you just changed.

Modifying JDBC Connection Pools or JDBC Data Sources

If you are making changes to JDBC connection pools or JDBC data sources:

Un-deploy the JDBC connection pool or JDBC data source you are modifying by selecting the pool or data source you want to modify, clicking on the associated Targets tab, moving the server from Chosen to Available, and clicking Apply.
Make any changes to JDBC connection pools or data sources as needed (or create new ones) and re-deploy these. For more information, see Creating a JDBC Connection Pool and Creating a JDBC Data Source.

 

---

Removing a Relational Database Source Description

You can remove a data source description that you no longer need. Removing a data source description does not remove the actual relational database to which it refers, nor does it remove the associated WebLogic Server JDBC Data Source or JDBC connection pool.

Note: You must log in with modify access before you can remove a data source description. For more information, see Defining Liquid Data Roles and Groups.

To remove a data source description for a relational database:

In the left pane, click the Liquid Data node.
In the right pane, click the Configuration tab.
Click the Data Sources tab.
Click the Relational Databases tab.

A table of configured Liquid Data data sources is shown.

Find the data source that you want to remove and click the trash can next to it.
When prompted, click Yes to confirm removal.

The Administration Console removes the selected data source description.

Contact BEA |  Feedback |  Privacy  |