RHEL : Working with ODBC and ODBCINI

 Most of us are aware that ODBC drivers are the most used connectivity option on Linux when you want to connect to non-Oracle databases. As the Oracle databases are mostly connected by using the Oracle client installation or Default configuration available as part of the installation of the application, in any case, tnsnames.ORA will hold the database details as required.

Similarly, for ODBC we have two files odbcinst.ini and odbc.ini, each having its own purpose. For now I'm considering that we are on Red Hat Linux and the ODBC manager used is unixODBC 2.3.1

Find OS Version on RHEL using the below command

cat /etc/redhat-release



Find the default ODBC Manager version and where it is setup use below command

odbcinst –j



odbcinst.ini

This initialization file will have the details of the drivers that are available on the server, for example, the default ones are as below.

[PostgreSQL]

[MySQL]
You can get the drivers available in the file by using the below command.

odbcinst –q –d

Let’s say you want to configure Amazon RedShift drivers on the server, first step is to install the driver on the server after the installation add the below to the odbcinst.ini file

[RedShiftDB]

Description=Amazon Redshift ODBC Driver (64-bit)

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so


Once we add this and run the odbcinst –q –d command we will now see below drivers

[PostgreSQL]

[MySQL]

[RedShiftDB]

ODBC.ini

This is the file where we actually configure the DSN with server details of the database’s we intend to connect or access from the Linux server.

In our Amazon RedShift case add the below with your respective details.

[RedShiftDB]

Description=Amazon Redshift ODBC

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

ServerName=abcdefgh.redshift.amazonaws.com

Port=5439

Database=redshiftdatabase1

locale=enUS

QueryTimeout=2000

ProxyHost=

ProxyPort=

We can verify the sources from the odbc.ini file using the below command

odbcinst –q –s

we can see the below output

[RedShiftDB]

Now it’s time we set these files as the system variables in bash_profile so that they work as expected,

OBDCINI – location of odbc.ini

AMAZONREDSHIFTODBC – location of file amazon.redshiftodbc.ini

LD_LIBRARY_PATH – library paths where your ODBC manager has installed, ex. /usr/lib64/


See the below examples

export ODBCSYSINI=/shared/odbc

export ODBCINI=/shared/odbc/odbc.ini

export AMAZONREDSHIFTODBCINI=/shared/odbc/amazon.redshiftodbc.ini

export LD_LIBRARY_PATH=/usr/lib64/:$LD_LIBRARY_PATH


Now lets test this using the isql command. This will help us to connect to anyof the DSN we added in odbc.ini, the command can be used as below


Isql –v RedShiftDB username password

OBIEE 12c : Changing the system component port numbers

One should be aware of the default ports used in OBIEE 12c, as soon as you install it.9514 for BI Server Component and 9507 for presentation server. This is because of the port range specified during the installation most of us will not have any problem with this. But sometimes when you have other instances with different versions of OBIEE or other applications installed in the same server as OBIEE 12c, there is a chance that we might have the port conflict.

My case, my other versions were all using 9031and 9033 for BI Server and Presentation Server respectively. So to get uniformity we were asked to change the OBIEE 12c ports as per the other OBIEE instances.

After a bit of digging on the Internet, I was able to find the process, which turns out to be pretty simple. Below are the steps

• Stop the component for which you want to change the port.

Stop.sh -I obis1

• Locate the file bienv-components.xml from below location 

Backup [DOMAIN_HOME]/config/fmwconfig/bienv/core/

• Change the port which you intended to my case it’s the BI Server component (obis1)

<component instanceId="obis1">
  <endpoint>
    <componentType>OBIS</componentType>
    <port>9031</port>
    <initialMachine>server.domain</initialMachine>
    <sslMode>shared</sslMode>
  </endpoint>
  <endpoint>
    <componentType>OBIS_MONITOR</componentType>
    <port>9591</port>
    <initialMachine>server.doimain/initialMachine>
    <sslMode>shared</sslMode>
   </endpoint>
</component>   

• Start the component using the below command

Start.sh –I obis1

• This doesn’t change the values in Enterprise manager yet, as to synchronize we will need a full restart of Admin server. Can perform the same using stop.sh and start.sh

Note: The port that you give in the file should be available for use at OS level else your service will fail.

OBIEE 12c : Starting and Stopping the services is too slow

We have recently installed a OBIEE 12c on RHEL 7 server. As part of initial test we have done multiple restarts of the server using the default scripts start.sh and stop.sh. The thing that I observed is its taking very long time in doing the start and stop operations. I did some investigation on this and to my surprise I found an interesting update in OBIEE 12c.

As we are already aware that we no longer have the OPMN to monitor the system components, I was under the impression that they are monitored by Admin server. To my surprise I learnt that Node Manager is the one looking after these BI System Components. From this it’s clear that we have the delay because of the Node Manager and to fix this we need to increase the JVM Heap size.

We will do this using MEM_ARGS parameter which is a variable used to override the standard memory arguments passed to java.

Let’s see how we can do that in 12c

• Locate the file “commBaseEnv.sh” in below location

[ORACLE_HOME]/oracle_common/common/bin

• Open the file and Make the change for below line. Below shows the default values. Set some higher values, be sure to check your free memory and do the changes accordingly.

.....

case $JAVA_VENDOR in

Oracle)

if [ "${VM_TYPE}" = "JRockit" ]; then

JAVA_VM=-jrockit

MEM_ARGS="-Xms128m -Xmx256m"

UTILS_MEM_ARGS="-Xms32m -Xmx1024m"

else

JAVA_VM=-server

MEM_ARGS="-Xms32m -Xmx200m" 

UTILS_MEM_ARGS="-Xms32m -Xmx1024m" 

fi

....

...

...

else

case $JAVA_VENDOR in

Oracle)

if [ "${VM_TYPE}" = "JRockit" ]; then

JAVA_VM=-jrockit

MEM_ARGS="-Xms128m -Xmx256m"

UTILS_MEM_ARGS="-Xms32m -Xmx1024m"

else

JAVA_VM=-server

MEM_ARGS="-Xms32m -Xmx200m" 

UTILS_MEM_ARGS="-Xms32m -Xmx1024m" 

fi

VERIFY_NONE="-Xverify:none"

• Restart the services using the start.sh and stop.sh as usual. This should resolve the slow start and stop issue.

OBIEE 12c : Change the default Locale from US to UK

All the Oracle Software’s comes with default as English-United States locale, this is something that gets some of us confused, because we don’t have any idea what a locale means. Frankly it doesn’t matter as it is mostly end user specific and Developers doesn’t bother about this very often. However end user happiness being the ultimate agenda of all the Software projects we are more inclined to enquire and acquire knowledge on this area.

Locale settings are something that are derived based on the region specific formats followed in the real time and are set to be followed by application, let’s take the OBIEE locales available in the localedefinitions.xml file which defines locale settings of different regions across the globe.

Some Example of Locale Settings

• Date Format 

                   UK - DD-MM-YYYY
                   US - MM-DD-YYYY

• Language

                    US - English (en)
                    France - French (fr)

Similar to the above we have date separator, Time Separator, Date Order, First day of week etc which may vary based on a country or region.

In OBIEE 12c, there are two files which hold information on these locale settings, localedefenitions.xml and localemapppings.xml located in below folder

[Oracle_Home]/bi/bifoundation/web/display

you can verify the default by logging on to the analytics and in my account you will find the below details by default.

If you want to change the default Locale to a different one to that of United States, please follow the below steps

• Open the localemappings.xml file from above Location
• Locate and change the below lines as shown to change it to English - United Kingdom

• Restart the complete OBIEE services