Tuesday, 23 September 2014

OBIEE 11g –Use of Dynamic and Static Variables in Connection Pool


Use of dynamic and static variable for stating relational connection pool for data import and access purpose is a common trend used across multiple projects. This blog is an attempt to explain how to use dynamic and static variables in connection pool, and list down its advantages and limitations
Connection Pool with Static Variables
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=VALUEOF(DB_HOST))(PORT=VALUEOF(DB_PORT))))(CONNECT_DATA=(SID=VALUEOF(DB_SID))))
Following STATIC VARIABLES are defined within repository
DB_HOST ='APP-HU9IPS3NTO3'
DB_PORT ='1521'
DB_SID='ORCL'

Connection Pool with Init block and Dynamic Variables
Instead of editing connectivity parameters in each connection pool, update RPD and SESSION VARAIBLE that hold connection information
Initialization Block
Verify DUAL ORCL DSN

Initialization Script  
SELECT '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=VALUEOF(DB_HOST))(PORT=VALUEOF(DB_PORT))))(CONNECT_DATA=(SERVICE_NAME=VALUEOF(DB_SID))))' FROM DUAL
Session Variable
DSN and it is Default Value
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pdborcl)))'
Connection Pool
"VALUEOF (NQ_SESSION.DSN)"  - uses the SESSION VARIABLE within connection pool
Limitations
Use of static variables within connection pool does not work for IMPOERT METADATA, while it data access from repository and via URL works fine.
Oracle Support Document
OBIEE 11g about the Use of Static Variables in the Connection Pool (Doc ID 1925713.1)
Inference  
By using session variable and default value the import metadata should work as expected. I hope you find this information useful.

No comments:

Post a Comment