Setting Up the Persist Connection Pool Property

A persist connection pool is a database property that is used for specific types of queries (typically used to support Marketing queries). In some queries, all of the logical query cannot be sent to the transactional database because that database might not support all of the functions in the query. This issue might be solved by temporarily constructing a physical table in the database and rewriting the Oracle BI Server query to reference the new temporary physical table.

You could use the persist connection pool in the following situations:

• Populate stored procedures. Use to rewrite the logical SQL result set to a managed table. Typically used by Oracle's Siebel Marketing Server to write segmentation cache result sets.
• Perform a generalized subquery. Stores a nonfunction subquery in a temporary table and then rewrites the original subquery result against this table. Reduces data movement between the Oracle BI Server and the database and supports unlimited IN list values and might result in improved performance.

NOTE:  In these situations, the user issuing the logical SQL needs to have been granted the Populate privilege on the target database.

The persist connection pool functionality designates a connection pool with write-back capabilities for processing this type of query. You can assign one connection pool in a single database as a persist connection pool. If this functionality is enabled, the User name specified in the connection pool must have the privileges to create DDL (Data Definition Language) and DML (Data Manipulation Language) in the database.

Example of Using Buffer Size and Transaction Boundary

If each row size in a result set is 1 KB and the buffer size is 20 KB, then the maximum array size will be 20 KB. If there are 120 rows, there will be 6 batches with each batch size limited to 20 rows.

If you set the Transaction boundary field to 3, the server will commit twice. The first time the server commits after row 60 (3 * 20). The second time the server commits after row 120. If there is a failure when the server commits, the server will only rollback the current transaction. For example, if there are two commits and the first commit succeeds but the second commit fails, the server only rolls back the second commit. To make sure that the array-based insert runs successfully, it is recommended that you not set the transaction boundary greater than 10 and you set the buffer size to approximately 32 KB.

To assign a persist connection pool

1. In the Physical layer, double-click the database icon.
2. In the Database dialog box, click the General tab.
3. In the Persist Connection Pool area, click Set.
   If there is only one connection pool, it appears in the Persist Connection Pool field.
4. If there are multiple connection pools, in the Browse dialog box, select the appropriate connection pool, and then click OK.
   The selected connection pool name appears in the Persist connection pool field.
5. (Optional) To set write-back properties, click the Connection Pools tab.
6. In the connection pool list, double-click the connection pool.
7. In the Connection Pool dialog box, click the Write Back tab.
8. Complete the fields using Table 11 as a guide.
9. Click OK twice to save the persist connection pool.

To remove a persist connection pool

1. In the Physical layer, double-click the database icon.
2. In the Database dialog box, click the General tab.
3. In the Persist Connection Pool area, click Clear.
   The database name is replaced by not assigned in the Persist connection pool field.
4. Click OK.