bea.com | products | dev2dev | support | askBEA
 Download Docs   Site Map   Glossary 
Search
e-docs > Liquid Data for WebLogic > Administration Guide > Configuring Access to Relational Databases

Administration Guide

 Previous Next Contents Index View as PDF  

Configuring Access to Relational Databases

Before a BEA Liquid Data for WebLogicTM 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 topic, 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 topics are included:

 

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.

Table 7-1 Connection Pool URL Formats and Driver Class Names for Supported Databases

Database

URL Format

Driver Class Name

PointBase

jdbc:pointbase://<hostname>:<portnum>/
LDDB

com.pointbase.jdbc.
jdbcUniversalDriver

Oracle

jdbc:oracle:thin:@<hostname>:<portnum>:SID

oracle.jdbc.driver.OracleDriver

Microsoft SQL Server

weblogic:mssqlserver4:CRM@<hostname>:
<portnum>

weblogic.jdbc.mssqlserver4.
Driver

Sybase

jdbc:sybase:Tds:<hostname>:<portnum>/<dbname>

com.sybase.jdbc.SybDriver

DB2

jdbc:db2://<hostname>/<dbname>

COM.ibm.db2.jdbc.net.DB2Driver

 

 

Creating a JDBC Connection Pool

For complete information on how to create Java Database Connectivity (JDBC) Connection Pools in WebLogic Server, see Managing JDBC Connectivity in the BEA WebLogic Server Administration Guide.

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:

  1. In the left pane, expand the Services node.

  2. Expand the JDBC node.

  3. Click on Connection Pools.

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

  4. Click the Configure a new JDBC connection pool text link.

  5. 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:

    (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.)

    ACL Name

    Optional—use only if you are implementing security.

    Sets the ACL used to control access to this Connection Pool. Permissions available to this ACL are reserve and admin. reserve permission allows users to get logical connections from this Connection Pool. admin allows all other operations on this Connection Pool, including: reset, shrink, shutdown, disable, and enable. Lack of an ACL allows any user open access (provided that user passes other WLS security controls).

    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.

     

  6. Click Create.

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

  7. 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.

  8. Click on the Configuration tab.

  9. 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 Managing JDBC Connectivity in the BEA WebLogic Server Administration Guide.

  10. Click on the Targets tab.

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

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

  12. Click Apply.

 

Creating a JDBC Data Source

For complete information on how to create a JDBC data source in WebLogic Server (WLS), see Managing JDBC Connectivity in the BEA WebLogic Server Administration Guide.

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:

  1. In the left pane, expand the Services node.

  2. Expand the JDBC node.

  3. Click on Data Sources.

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

  4. Click the Configure a new JDBC Data Source connection pool text link.

  5. On the General tab, provide information about the JDBC data source you want to create as described in the following table. (The fields described in the table are required—other optional fields are also displayed on this tab.)

    Table 7-3 WebLogic Server JDBC Data Source Configuration  

    Field

    Description

    Name

    Name of the data source. This can be any name you choose to use for the data source, such as MyWirelessDS.

    JNDI Name

    JNDI path to where this data source is bound. Applications that look up the JNDI path will get a javax.sql.DataSource instance that corresponds to this data source.

    This can be any name you choose to use for the data source, such as MyWirelessDS.

    Pool Name

    Name of the connection pool the data source is associated with. The pool name you provide here must match exactly the name of the connection pool you created in the previous task (Creating a JDBC Connection Pool).

    For our example, the pool name is MyWireless_POOL.

     

  6. Click Create.

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

  7. 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.

  8. Click on the Configuration tab.

  9. Click on the Targets tab.

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

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

  11. 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 Administration Console Security.

To create a data source description for a relational database:

  1. In the left pane, click the Liquid Data node.

  2. In the right pane, click the Configuration tab.

  3. Click the Data Sources tab.

  4. Click the Relational Databases tab.

  5. 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-1 Configuring a Liquid Data Source Description for a Relational Database


     

  6. Fill in the fields as described in the following table.

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

Table 7-4 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.

Username

Not supported.

Password

Not supported.

Schema

Name of the schema you want to use for this Liquid Data data source.

The requirements for setting the schema name vary depending on the relational database you are using:

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:

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:

Shared Connection

Toggle to set shared connection on (checked) or off (cleared).

Isolation Level

Sets the transaction isolation level.

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 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.

 

  1. Click Create.

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

Note: If security is enabled on your Liquid Data server, 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:

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 Administration Console Security.

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

  1. In the left pane, click the Liquid Data node.

  2. In the right pane, click the Configuration tab.

  3. Click the Data Sources tab.

  4. Click the Relational Databases tab.

    A table of configured Liquid Data data sources is shown.

  5. Click on the data source description that you want to modify.

  6. Change the settings as needed.

  7. Click Apply.

  8. 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:

  1. 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.

  2. 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 Administration Console Security.

To remove a data source description for a relational database:

  1. In the left pane, click the Liquid Data node.

  2. In the right pane, click the Configuration tab.

  3. Click the Data Sources tab.

  4. Click the Relational Databases tab.

    A table of configured Liquid Data data sources is shown.

  5. Find the data source that you want to remove and click the trash can next to it.

  6. When prompted, click Yes to confirm removal.

    The Administration Console removes the selected data source description.

 

Back to Top Previous Next