8 Replies Latest reply on Oct 16, 2019 1:52 PM by 3849347

    How to configure BIEE 11 Connection Pool for JDBC?

    3849347

      Dear Specialists,

       

      Please could you guide me on the following topic:

      In order to test different usage scenario, looking for the last millisecond of performance, I want to connect to biee 11 connection pool using JDBC.

      I already have test's with OCI and ODBC but now I want a scenario with JDBC.

       

      How to configure BIEE 11 Connection Pool for JDBC?

       

       

      Thanks.

      Michel.

        • 2. Re: How to configure BIEE 11 Connection Pool for JDBC?
          3849347

          Christian

           

          I really appreciate your answer.

           

          I had already found this link and some others, but I still do not understand the totality of how this can be accomplished.

           

          Can I create a connection pool connection using jdbc (sources oracle18, vertica, apache drill and others using JDBC)?

           

          I see that biee is capable of using jdbc but I don't see how I do this setting in the connection pool?

          If you can help me out by doing that, I appreciate it.

           

           

          Look this document https://docs.oracle.com/middleware/11119/biee/BIEIT/odbc_data_source.htm#BIEIT1738

          in this words, I reed the readm.txt and magic is realized. But I dont understand this.

          >

          8.2.2 About Integrating with the Oracle BI Server Using JDBC

          In addition to using ODBC, you can also integrate with the Oracle BI Server using JDBC. For full information, see the README.TXT file contained in the bijdbc.jar file. You can find the bijdbc.jar file in the following directory:

          ORACLE_HOME/bifoundation/jdbc

          <<<<

          See README.TXT

          <<<<

          Oracle BI JDBC URL specification and Driver Configuration.

           

           

          This document specifies the URL format for Oracle Business

          Intelligence JDBC type 4 driver. This driver inteded for application

          to connect to Oracle BI EE Server. It is not for the Oracle relational

          database.

           

           

          Revision History

          -------------------------------------------------------------------------------

          Date Author Comment

           

          03/01/2006 Hsing-Chen Tsai Draft of the spec

          06/13/2006 Hsing-Chen Tsai Add cluster spec

          08/23/2006 Hsing-Chen Tsai Add SSL option

          01/11/2008 Kenneth Eng Add additional properties

          02/28/2008 Kenneth Eng Add details of failover logic

          05/16/2011 Basavaraj Kirunge  ConnectionPoolDataSource Usage Details

          07/26/2011 Added logging example

           

           

           

          URL Format

          -------------------------------------------------------------------------------

          The URL is case insensitive. To connect to the Oracle BI Server, user

          can specify the URL in following specification.

           

          <URL>:= <Prefix>:[//<Host>:<Port>/][<Property Name>=<Property Value>;]*

          Examples:

          jdbc:oraclebi://localhost:9703/catalog=SalesSubjectArea;user=username;password=password;

          jdbc:oraclebi://localhost:9703/NQ_SESSION.PREFERRED_CURRENCY=Currency 1;

          jdbc:oraclebi://localhost:9703/USER=username;PASSWORD=password;LOGFILEPATH=/tmp;loglevel=finest;

          In this example it �ll create the log file in /tmp/bijdbc.log with finest detail.

           

          <Prefix>:= jdbc:oraclebi

           

          <Host>:= The hostname of the analytics server it is connecting. It can

          be IP Address or hostname.  Default is localhost

           

          <Port>:= The port number that server is listening on. It can be

          application name in the future if the server supports multiple

          application on the same server machine. Default is 9703

           

          <Property Name>:= <Catalog> | <Repository> | <User> | <Password> |

          <SSL> |<SSLKeyStoreFileName> | < SSLKeyStorePassword> |

          <TrustAnyServer> |

          <TrustStoreFileName > |  <  TrustStorePassword> |

          <LogLevel> | <LogFilePath> |

          <PrimaryCCS> | <PrimaryCCSPort> |

          <SecondaryCCS> | <SecondaryCCSPort> |

          <MaxReconnectAttempts> | <RpcClientExpirationTime> |

          <MaxRpcClientCount> | <MaxRpcClientCreateAttempts> |

          <HeartbeatInterval> | <MaxHeartbeatAttempts>

           

           

           

          Valid Property Values

          -------------------------------------------------------------------------------

          <Catalog>

          It can be any catalog name that's available on the server. If it's

          empty, then it will be default to the default catalog specified by

          server. If the catalog name is not found in the server, it will still

          use the default catalog and gives a warning during connect.

           

          <Repository>

          It can be any logical repository name that's available on the server. If it's

          empty, then it will be default to the default repository specified by

          server. For multi-tenancy mode, tenantguid:tenantservice should be used. NQ_SESSION.TENANTGUID and NQ_SESSION.TENANTSERVICE takes the priority if both are specified.

           

          <User>

          Specifies the username of the BI Server. Default is Administrator

           

          <Password>

          Specifies the password for BI Server for the username. The password

          will be encrypted using 3DES.

           

          <SSL>

          True/False, default is False. Specifies if JDBC driver will use SSL or

          not. If it's true, driver will check if SSLKeyStoreFileName is

          readable, if not it will throw an error message.

           

          <SSLKeyStoreFileName>

          The file name that store SSL Keys. This file need to exist in the

          local file and readable by the driver.

           

          <SSLKeyStorePassword>

          The password to open up the file pointed by SSLKeyStoreFileName.

           

          <TrustAnyServer>

          True | False, default is False.  Specifies if need to check trust

          store for the server, if SSL is True. If TrustAnyServer is set to

          false, driver will check if TrustStoreFileName is readable.

           

          <TrustStoreFileName>

          If TrustAnyServer is set to false, this property is needed to specify

          the trust store file name

           

          <TrustStorePassword>

          If TrustAnyServer and TrustStoreFileName are specified, this specifies

          the password to open up the file specified by TrustStoreFileName.

           

          <LogLevel>

          SEVERE | WARNING | INFO | CONFIG | FINE | FINER | FINEST

           

          <LogFilePath>

          This is a path to a directory. Only used if LogLevel is set.

          The driver will log to a file named bijdbc.log in the specified path.

          The logger name is "oracle.bi".

           

          <PrimaryCCS>

          This property specifies the primary CCS machine name instead of using

          the "host" to connect. If this property is specified, the

          "host" property value is ignored. Then jdbc driver will try to

          connect to the CCS to obtain the load-balanced machine. Default is

          localhost.

           

          <PrimaryCCSPort>

          This property specifies the primary CCS port number running on the

          PrimaryCCS machine. Default is 9706.

           

          <SecondaryCCS>

          This property specifies the secondary CCS machine name instead of

          using the "host" to connect. If this property is specified, then the

          jdbc driver will try to connect to the CCS to obtain the load-balanced

          machine. Default is localhost.

           

          <SecondaryCCSPort>

          This property specifies the secondary CCS port number running on the

          secondary machine. Default is 9706.

           

          <MaxReconnectAttempts>

          This property specifies the number of attempts to establish an initial

          connection or to reconnect a failed connection to an active server

          node. Default is 3.

           

          <RpcClientExpirationTime>

          This property specifies the length of time in seconds for unused RPC

          Clients to remain in the pool for future use. Default is 60 seconds.

           

          <MaxRpcClientCount>

          This property specifies the maximum number of RPC Client that is

          available in the pool. Default is 100.

           

          <MaxRpcClientCreateAttempts>

          This property specifies the maximum number of attempts to open a new

          RPC Client when there is no available RPC Client in the pool. Default

          is 3.

           

          <HeartbeatInterval>

          This property specifies the interval in seconds between each heartbeat

          message sent to the server for each connection. Default is 60 seconds.

           

          <MaxHeartbeatAttempts>

          This property specifies the maximum number of times a heartbeat

          message can fail before a server is confirmed to be down. Default is

          3.

           

           

           

          URL Error Checking

          -------------------------------------------------------------------------------

          The driver is responsible for verify the URL entered by

          application. If the application does not specify the wrong file name

          for logging or the driver doesn't have permission to write to the

          file, driver may fail to give connection and throws SQLException.

           

           

           

          JDBC Driver Class Name

          -------------------------------------------------------------------------------

          To invoke the driver using the DriverManager interface, specify the

          following line in the program:

           

          Class.forName("oracle.bi.jdbc.AnaJdbcDriver");

           

           

          JDBC Test Driver

          -------------------------------------------------------------------------------

          In the bijdbc.jar, a test client called JDBCCmd is included for 1.5

          compliant version. A JVM 1.5 is required. The usage of the JDBCCmd is

          not for production usage, rather for providing the client a way to

          quickly test the driver. The usage of the JDBCCmd is:

           

          java -jar bijdbc.jar -u <url> -s <src script directory>

          -o <output script directory>

           

          The source script directory contain text file that contains SQLs. The

          SQLs are separated by semicolon, and can be multiple lines.

           

           

           

          Additional Connection Properties for Authentication

          -------------------------------------------------------------------------------

          To access the BI server using proxy support, you can utilize the JDBC

          Connection properties and supply the KEYWORD: IMPERSONATE.

           

          Class.forName("oracle.bi.jdbc.AnaJdbcDriver");

          String url = "jdbc:oraclebi://htsaidc5000:9703/catalog=snowflakeSales;USER=Administrator;PASSWORD=;";

          Properties myprops = new Properties();

          myprops.setProperty("IMPERSONATE", "USER1");

          conn = DriverManager.getConnection(url, myprops);

           

          After this is done, the username will be treated as USER1 when running

          a report in Oracle BI Server. The report will be run as if user login

          as USER1. However, the caller of the BI server does not need to

          provide the password for user1. This typically is done for trusted

          middle tier server.

           

          To assert the impersonation is setup correctly, with a correct

          repository configuration, you can get the NQ_SESSION.USER variable by

          the following SQL.

           

          public void testSessionValues() throws SQLException {

                  Statement stmt = getConnection().prepareStatement("call NQSGetSessionValues('NQ_SESSION.USER')");

                  ResultSet s = stmt.getResultSet();

                  if (s.next()) {

                      assertTrue(s.getString(1).equalsIgnoreCase("USER1"));

                  }

                 

              }

           

           

           

          Failover Logic

          -------------------------------------------------------------------------------

          The failover process is transparent to the JDBC caller, i.e. the

          caller will receive the expected results from its query as long as

          failover is successful.

           

          If a server failed during a JDBC call, the failover process is

          performed in the following steps by the JDBC driver:

          1. Obtain an active server node from the cluster controller server.

          2. Open a new session on the active server node with the

          previously-set session values.

          3. Re-execute the logic to complete the JDBC call depending on the

          stage of the request when the server failed.

           

           

          Code example with comments on failover

           

          Statement stmt = DriverManager.getConnection(url).createStatement();

          ResultSet rs = stmt.executeQuery("select * from sales;");

          rs.next(); // <-- Server fails at this point during fetching of the results.

                     // Failover is triggered within rs.next(). After opening a new

                     // session on the new server, the last query is re-executed by the

                     // JDBC driver on the new server before the results are fetched.

           

          A failure during failover results in a SQLException will be thrown if

          failover did not succeed due to reasons such as:

          1. Inability to connect to a cluster controller server

          2. Inability to connect to a new server node

           

           

           

          Web Logic

          -------------------------------------------------------------------------------

          There's a weblogic deployment template located in

          common/templates/applications/oracle.bijdbc_template_11.1.1.jar

           

           

          ConnectionPoolDataSource Usage Details

          -------------------------------------------------------------------------------

          Using ConnectionPoolDataSource significantly improves performance, and scalability in 3-tier and 2-tier environment.

          ConnectionPoolDataSource can be used in cases where client frequently creates and releases connections to bi server, ConnectionPoolDataSource doesn't release a physical connection

          when closed, it will reuse it on next connection request.

          1. Using it on 2-tier Environment

           

            oracle.bi.jdbc.AnaJdbcConnectionPoolDataSource cpds = new oracle.bi.jdbc.AnaJdbcConnectionPoolDataSource();

            cpds.setMachineName("hostname");

            cpds.setDatabaseName("database");

            cpds.setDescription("Oracle BI Server");

            cpds.setPortNumber(Port Number);

            cpds.setUser("User_Name");

            cpds.setPassword("Password");

            try{

                 PooledConnection conn = cpds.getPooledConnection("User_Name","Password");

                 Connection physcon1 = conn.getConnection();

                

                 // Uses physcon1 for running queries/creating Statements

                

                 // close connection

              pyscon1.close();

              conn.close

                }

             Catch(SQLException sql){

            

             }

            

             //Close ConnectionPoolDataSource

             cpds.close();

            

          2. Using it on 3-Tier Environment (Registering with JNDI)

           

             Create a DataSource using class oracle.bi.jdbc.AnaJdbcConnectionPoolDataSource on application server (like weblogic server) that provides JNDI services

             This steps registers this object with a JNDI name that can be queried by client code as follows.

            

                   Context ctx = createContext();

                   String jndiName = "jdbc/AppOBISDS";   //jndi name used to register on application server

                   try {

                       DataSource dataSource = (DataSource) ctx.lookup(jndiName);

                       Connection physcon = dataSource.getConnection();

                      // run queries on physcon

                  

                   //close connection

                   physcon.close();

                   dataSource.close();

                   }catch(SQLException sql){

                   }

            

            

            

          <<<<

           

          I can't clearly see how to set this up in the connection pool.

           

           

           

          Thank you very much! Michel.

          • 3. Re: How to configure BIEE 11 Connection Pool for JDBC?
            Gianni Ceresa

            Hi,

            The link to the doc you posted is to connect from a 3rd party tool to the BI Server to run queries against the BI Server.

             

            If you said you already tested OCI and ODBC it sounds more like you want to use a JDBC driver as source inside the RPD, it's a totally different topic.

            So what direction are you looking for? JDBC as a source to be used inside the RPD or connecting to the BI Server via JDBC to query the BI Server?

             

            PS: OBIEE 11g is a tons of versions, could you be a bit more explicit on the version you are dealing with?

            1 person found this helpful
            • 4. Re: How to configure BIEE 11 Connection Pool for JDBC?
              Christian Berg

              +1 - was I mistaken about the direction you want to go? As Gianni said using the BI server as a JDBC source or using a JDBC source inside OBI?

              1 person found this helpful
              • 5. Re: How to configure BIEE 11 Connection Pool for JDBC?
                3849347

                Gentlemen Gianni  and  Christian

                 

                I'm sorry if I was not clear.

                 

                To clarify, I am looking for a way to configure the Connection Pool within RPD to query data in a source using JDBC (sources can be Oracle, Vertica, Apache Drill and others).

                Using a JDBC source inside OBI (in rpd connection pool configuration instead of oci and odbc).

                 

                I know this works natively on biee12c. But at this moment I can't do the upgrade I would love to do.

                So the focus is biee version is 11.1.1.1.9

                 

                Thank you very much for your time

                • 6. Re: How to configure BIEE 11 Connection Pool for JDBC?
                  Gianni Ceresa

                  OBIEE 11.1.1.9 is so old I don't remember if it was already possible (and don't have an Admintool to see if the entry was there already in the menu).

                   

                  If you look at OBIEE tips and tricks: Using Apache Drill as a JDBC data source in OBIEE 12c  there is an interesting piece:

                  I will be experimenting with an approach based on a new feature of OBIEE 12c: the ability to have a JDBC/JNDI based data source in the repository

                   

                  Not remembering and not having an OBIEE 11g around, if I take what has been written, it is a 12c thing only to use a JDBC as source in the RPD.

                  Look at the blog post and check if in your Admintool, once a RPD is open, you have something like the "Load Java sources" entry in the menu. That would be a sign that it really is only a 12c thing.

                   

                  BI Publisher already had JDBC sources (as that's what the tool uses mainly), in case that could be an alternative.

                  1 person found this helpful
                  • 7. Re: How to configure BIEE 11 Connection Pool for JDBC?
                    Christian Berg

                    Beaten by Gianni, but to confirm: OBI can use JDBC as a source in the RPD as of 12c. 11g doesn't support it.

                    • 8. Re: How to configure BIEE 11 Connection Pool for JDBC?
                      3849347

                      Srs Christian and Giani,

                       

                      Thank you very much for your answers!

                      I couldn't find a way to use biee11 to source jdbc.

                      As BIP is not the tool because we depend on a complex and extensive physical and semantic layer.

                      I am starting the installation of biee12 and made the customer happy by punctuating the opportunities it brings.

                       

                      Thank You, Michel.