Before you define the database interface and connect to an Oracle database in PowerBuilder, follow these steps to prepare the database for use:
Install and configure the required database server, network, and client software.
Install the native Oracle database interface for the version of Oracle you want to access.
Verify that you can connect to the Oracle server and database outside PowerBuilder.
(ORA driver only) Determine whether you want to use connection pooling or session pooling.
Preparing an Oracle database for use with PowerBuilder involves these basic tasks.
You must install and configure the database server, network, and client software for Oracle.
To install and configure the database server,
network, and client software:
Make sure the Oracle database software is installed on your computer or on the server specified in your database profile.
For example, with the Oracle O90 interface you can access an Oracle9i or Oracle 10g database server.
You must obtain the database server software from Oracle Corporation.
For installation instructions, see your Oracle documentation.
Make sure the supported network software (such as TCP/IP) is installed and running on your computer and is properly configured so that you can connect to the Oracle database server at your site.
The Hosts and Services files must be present on your computer and properly configured for your environment.
You must obtain the network software from your network vendor or database vendor.
For installation and configuration instructions, see your network or database administrator.
Install the required Oracle client software on each client computer on which PowerBuilder is installed.
You must obtain the client software from Oracle Corporation. Make sure the client software version you install supports all of the following:
The operating system running on the client computer
The version of the database that you want to access
The version of PowerBuilder that you are running
Oracle 10g Instant Client is free client software that lets you run applications without installing the standard Oracle client software. It has a small footprint and can be freely redistributed.
Make sure the Oracle client software is properly configured so that you can connect to the Oracle database server at your site.
For information about setting up Oracle configuration files, see your Oracle Net documentation.
If required by your operating system, make sure the directory containing the Oracle client software is in your system path.
In the PowerBuilder Setup program, select the Typical install or select the Custom install and select the Oracle database interfaces you require.
For a list of the Oracle database interfaces available, see “Supported versions for Oracle”.
Make sure you can connect to the Oracle database server and log in to the database you want to access from outside PowerBuilder.
Some possible ways to verify the connection are by running the following Oracle tools:
Accessing the database server Tools such as Oracle TNSPING (or any other ping utility) check whether you can reach the database server from your computer.
Accessing the database Tools such as Oracle SQL*Plus check whether you can log in to the Oracle database you want to access and perform database operations. It is a good idea to specify the same connection parameters you plan to use in your PowerBuilder database profile to access the database.
Oracle client interface (OCI) pooling for PowerBuilder applications is created when you connect to an Oracle server for the first time. The pooling is identified by the server name and character set which are passed in the DBPARM parameters SQLCA.ServerName and NLS_Charset, respectively. If two Oracle connections are connected to the same Oracle server but use different character sets, the connections must reside in different connection or session pools. All pooling-related DBPARM parameters must be set before the initial database connection from PowerBuilder.
Session pooling means that the application creates and maintains a group of stateless sessions to the database. These sessions are passed to thin clients as requested. If no session is available, a new one is created. When the client is done with the session, the client releases it to the pool. With session pooling, the number of sessions in the pool can increase dynamically.
Session pooling does not support external authentication using an OS account. If a Login ID is not specified in a database connection using an existing session pool, the Login ID of the session pooling creator is used for the connection.
CNNPool parameter maintained for backward compatibiity
The O90 and O10 database drivers that you can use in PowerBuilder to
connect to the 9.x and 10.x versions of the Oracle DBMS support
connection pooling with the DBPARM parameter CNNPool. For backward
compatibility purposes, this parameter is also supported by the
ORA driver that you use with Oracle 11g. However,
if the Pooling parameter is used with this driver, the CNNPool parameter
is ignored.
Deciding on pooling type Table 10-3 describes the circumstances under which you should make your pooling selection.
Choose |
When database sessions are |
|---|---|
Session pooling |
Stateless (reusable by middle tier threads) and the number of back-end server processes can cause database scaling problems. |
Connection pooling |
Stateful (not reusable by middle tier threads) and the number of back-end server processes can cause database scaling problems. The number of physical connections and back-end server processes is reduced by using connection pooling. Therefore many more database sessions can be utilized for the same back-end server configuration. |
No pooling |
Stateful (not reusable by middle tier threads) and the number of back-end server processes will never be large enough to cause scaling issues for the database. EAServer components and MTS components do not support either type of pooling for Oracle databases. |
Setting pooling parameters The database profile dialog box for an Oracle 11g connection includes a Pooling tab that lets you select the pooling parameters listed in Table 10-4.
Pooling parameter |
Description |
|---|---|
Pooling Type |
You can select Session Pooling, Connection Pooling, or None (default). Sets the Pooling DBPARM. |
Runtime Connection Load Balancing |
This check box selected by default. It is ignored when you select Connection Pooling or None for the Pooling Type. Sets the RTConnBalancing DBPARM. |
Homogeneous Session |
This check box is not selected by default and is valid for session pooling only. When selected, all sessions in the pool are authenticated with the user name and password in effect when the session pool was created. The user name and password in later connection requests are ignored. Proxy sessions cannot be created in homogeneous sessioon mode. Sets the SessionHomogeneous DBPARM. |
Minimum Number of Sessions |
Integer for the minimum number of database connection sessions; value is 1 by default. Sets the CSMin DBPARM. This value is ignored when the SessionHomogeneous DBPARM is set to false. |
Maximum Number of Sessions |
Integer for the maximum number of database connection sessions; value is 100 by default. Sets the CSMax DBPARM. |
Increment |
Integer for database connection increments per session; value is 1 by default. Sets the CSIncr DBPARM. This value is ignored when the SessionHomogeneous DBPARM is set to false. |
Pool Creator |
User name used to create the connection or session pool when the pool is not already created. Sets the PoolCreator DBParm to a string for the user name prior to the database connection. If you do not provide a value for the PoolCreator DBParm, the Transaction object’s LogID and LogPass properties are used to create the pooling. |
Password |
Password used to create the connection or session pool when the pool is not already created. Sets the PoolPwd DBParm to a string for the password for the pool creator. |
