Three Drivers for the Teradatas under the sky,
Seven for the HANA-lords in their halls of stone,
Nine for MaxDB Men doomed to die,
One for the Dark Lord on his dark throne
In the Land of Mordor where the Shadows lie.
One Driver Manager to rule them all, One Driver Manager to find them,
One Driver Manager to bring them all and in the darkness SQLBindCol them
Sometines, getting the BI Platform working with ODBC drivers feels like trying to make our way through Mordor. You get surrounded by error messages like "incompatible driver" or "a driver failed to load".
Fear not, with a few tricks from friendly wizards, you can master such issues... and become The Lord of the ODBC Drivers.
Microsoft designed ODBC, and as such, they do provide a comprehensive implementation for the Windows platform. It really does feel like "one Driver Manager to rule them all".
Each release of Windows comes with a Microsoft-provided driver manager. As far as I know, it is the only ODBC driver manager available for this operating system, and all drivers play nicely with it.
Windows even ships a UI to manage ODBC drivers and DSNs, so things seem really simple. Any hobbit can master ODBC in that world.
This looks too good to be true... and it is. With the rise of 64-bit platforms, Windows started shipping a 64-bit version of the operating system. Architectures cannot coexist inside a process, so everything in the Windows core now comes in two flavours: 32-bit and 64-bit. This applies to ODBC as well, so, just like the ring did belong to Bilbo and Frodo, there is now one 64-bit driver manager and one 32-bit driver manager. There is also a catch: every driver must be of the same architecture than the application that consumes it. A 32-bit application can only consume 32-bit ODBC drivers, a 64-bit application can only consume 64-bit drivers.
The SAP BI stack is split in these two flavours: client products are 32-bit products, while server products are 64-bit products. This means that on a client machine, you need to install and configure 32-bit drivers and DSNs, and on a server machine you need to install and configure 64-bit drivers and DSN.
Installation procedures for drivers vary from driver to driver, so do simply be careful about what you are installing. Be on the lookout for information about the architecture you are installing.
DSN configuration is, as before, performed in the ODBC control panel, except there are now 2 control panels: 32-bit and 64-bit. On Windows 10 they are easy to find: simply type "odbc" when opening the start menu, and the system finds them both, and marks them clearly.
On earlier releases of Windows, things are a bit more confusing. The ODBC control panel icon in the Administrative tools refers to the 64-bit control panel. The 32-bit control panel must be launched manually, it is the odbcad32.exe program in c:\windows\SysWOW64 (don't ask, the fact that the 32-bit version is in a folder named "64" is probably the works of one of Sauron's sbires).
Welcome to the land where you are surrounded by command lines, environment variables, and error messages.
Unixes do have ODBC driver managers... except there is no standard. Two major driver managers exist for these platforms: DataDirect and unixODBC, each one having many versions. Each ODBC driver is built (and often shipped) with a specific driver manager version, and it is unsafe to attempt using a driver with another driver manager than the official one.
Since the operating system does not come with a driver manager, it will seach for one in the paths referenced in the LD_LIBRARY_PATH variable. The driver manager library is generally named libodbc.so.
So the first orc (hum... danger) resides in a missing driver manager. This is surfaced as an error message telling you that a database driver could not be loaded. To troubleshoot this, note the SAP BI driver name (generally libdbd_xxx.so), connect to your BOE server, fire up your magic bash, and...
You should get a list of dependent libraries, including a few missing dependencies, which confirms the error above.
Hopefully you (or your administrator) have already installed the desired middleware, let's assume it resides in /opt/my_middleware. You need to identify the exact path to the libodbc.so file in that folder, using the find command if needed (find /opt/my_middleware -name "libodbc.so"). For the sake of the example, we will use /opt/my_middleware/lib.
The solution to the issue is then to change the LD_LIBRARY_PATH to have the system load your driver manager:
You can then check that the solution works by trying out the ldd_libdbd_xxx.so again. If all dependencies are resolved, restart your BOE server (./stopservers and then ./startservers), and let your users admire your wizardry.
Now that we have tacked the danger of the missing driver manager, we can look into the opposite scenario: an army of driver managers and drivers, all driver managers competing to be THE one.
Clients (like the BI platform) load the ODBC driver manager, and then request a connection to a DSN. These DSNs are defined in a configuration file, generally named .odbc.ini. The file also contains paths to the actual odbc driver, which in turn gets loaded by the ODBC driver manager. The driver would then load the ODBC library too as it relies on some of its functions.
The driver and driver manager may come from different (incompatible) versions/distributions, and in this case you would start getting error messages. This scenario is summarized in the schema below.
Diagnosis is pretty much the same as for the previous scenario: repeat the steps up to libdbd_xxx.so to assess that the ConnectionServer driver can load. Make a note of where libodbc.so is loaded from.
From the .odbc.ini, get the name of the actual ODBC driver that your connection uses. There should be a driver manager close to it, either shipped by the vendor or installed by your administrator. In a correct setup this driver manager must be the one that you identified in the previous step.
If this is not the case, the solution is, as before, to change the LD_LIBRARY_PATH to load the correct driver manager:
And again, restart your server and enjoy your newly-gained pover over ODBC!
As we have seen, ODBC is a great technology, and it is no surprise that those who created it (Microsoft) provided the implementation that is simplest to use. However, even though Unixes may seem like Mordor with their dark command lines, it can all make sense, and you can get it to work!
This article is the second one in my blog, and I would like this blog to be of use to you. As you may have gathered, my focus is on data access for the SAP BI products. If there are topics you would like discussed here, or exotic datasources you would like tried out (see my previous post on drill), please use the comments to let me know !
Seven for the HANA-lords in their halls of stone,
Nine for MaxDB Men doomed to die,
One for the Dark Lord on his dark throne
In the Land of Mordor where the Shadows lie.
One Driver Manager to rule them all, One Driver Manager to find them,
One Driver Manager to bring them all and in the darkness SQLBindCol them
Sometines, getting the BI Platform working with ODBC drivers feels like trying to make our way through Mordor. You get surrounded by error messages like "incompatible driver" or "a driver failed to load".
Fear not, with a few tricks from friendly wizards, you can master such issues... and become The Lord of the ODBC Drivers.
The Shire, aka Windows
Microsoft designed ODBC, and as such, they do provide a comprehensive implementation for the Windows platform. It really does feel like "one Driver Manager to rule them all".
Each release of Windows comes with a Microsoft-provided driver manager. As far as I know, it is the only ODBC driver manager available for this operating system, and all drivers play nicely with it.
Windows even ships a UI to manage ODBC drivers and DSNs, so things seem really simple. Any hobbit can master ODBC in that world.
This looks too good to be true... and it is. With the rise of 64-bit platforms, Windows started shipping a 64-bit version of the operating system. Architectures cannot coexist inside a process, so everything in the Windows core now comes in two flavours: 32-bit and 64-bit. This applies to ODBC as well, so, just like the ring did belong to Bilbo and Frodo, there is now one 64-bit driver manager and one 32-bit driver manager. There is also a catch: every driver must be of the same architecture than the application that consumes it. A 32-bit application can only consume 32-bit ODBC drivers, a 64-bit application can only consume 64-bit drivers.
The SAP BI stack is split in these two flavours: client products are 32-bit products, while server products are 64-bit products. This means that on a client machine, you need to install and configure 32-bit drivers and DSNs, and on a server machine you need to install and configure 64-bit drivers and DSN.
Installation procedures for drivers vary from driver to driver, so do simply be careful about what you are installing. Be on the lookout for information about the architecture you are installing.
DSN configuration is, as before, performed in the ODBC control panel, except there are now 2 control panels: 32-bit and 64-bit. On Windows 10 they are easy to find: simply type "odbc" when opening the start menu, and the system finds them both, and marks them clearly.
On earlier releases of Windows, things are a bit more confusing. The ODBC control panel icon in the Administrative tools refers to the 64-bit control panel. The 32-bit control panel must be launched manually, it is the odbcad32.exe program in c:\windows\SysWOW64 (don't ask, the fact that the 32-bit version is in a folder named "64" is probably the works of one of Sauron's sbires).
Mordor, aka Unixes
Welcome to the land where you are surrounded by command lines, environment variables, and error messages.
Unixes do have ODBC driver managers... except there is no standard. Two major driver managers exist for these platforms: DataDirect and unixODBC, each one having many versions. Each ODBC driver is built (and often shipped) with a specific driver manager version, and it is unsafe to attempt using a driver with another driver manager than the official one.
Since the operating system does not come with a driver manager, it will seach for one in the paths referenced in the LD_LIBRARY_PATH variable. The driver manager library is generally named libodbc.so.
So the first orc (hum... danger) resides in a missing driver manager. This is surfaced as an error message telling you that a database driver could not be loaded. To troubleshoot this, note the SAP BI driver name (generally libdbd_xxx.so), connect to your BOE server, fire up your magic bash, and...
- cd to <your BOE setup>
- source setup/env.sh
- cd dataAccess/connectionServer/drivers/lib64
- ldd libdbd_xxx.so
You should get a list of dependent libraries, including a few missing dependencies, which confirms the error above.
Hopefully you (or your administrator) have already installed the desired middleware, let's assume it resides in /opt/my_middleware. You need to identify the exact path to the libodbc.so file in that folder, using the find command if needed (find /opt/my_middleware -name "libodbc.so"). For the sake of the example, we will use /opt/my_middleware/lib.
The solution to the issue is then to change the LD_LIBRARY_PATH to have the system load your driver manager:
export LD_LIBRARY_PATH="/opt/my_middleware/lib:$LD_LIBRARY_PATH"
You can then check that the solution works by trying out the ldd_libdbd_xxx.so again. If all dependencies are resolved, restart your BOE server (./stopservers and then ./startservers), and let your users admire your wizardry.
Now that we have tacked the danger of the missing driver manager, we can look into the opposite scenario: an army of driver managers and drivers, all driver managers competing to be THE one.
Clients (like the BI platform) load the ODBC driver manager, and then request a connection to a DSN. These DSNs are defined in a configuration file, generally named .odbc.ini. The file also contains paths to the actual odbc driver, which in turn gets loaded by the ODBC driver manager. The driver would then load the ODBC library too as it relies on some of its functions.
The driver and driver manager may come from different (incompatible) versions/distributions, and in this case you would start getting error messages. This scenario is summarized in the schema below.
Diagnosis is pretty much the same as for the previous scenario: repeat the steps up to libdbd_xxx.so to assess that the ConnectionServer driver can load. Make a note of where libodbc.so is loaded from.
From the .odbc.ini, get the name of the actual ODBC driver that your connection uses. There should be a driver manager close to it, either shipped by the vendor or installed by your administrator. In a correct setup this driver manager must be the one that you identified in the previous step.
If this is not the case, the solution is, as before, to change the LD_LIBRARY_PATH to load the correct driver manager:
export LD_LIBRARY_PATH="/opt/my_middleware/lib:$LD_LIBRARY_PATH"
And again, restart your server and enjoy your newly-gained pover over ODBC!
Final words
As we have seen, ODBC is a great technology, and it is no surprise that those who created it (Microsoft) provided the implementation that is simplest to use. However, even though Unixes may seem like Mordor with their dark command lines, it can all make sense, and you can get it to work!
Addendum
This article is the second one in my blog, and I would like this blog to be of use to you. As you may have gathered, my focus is on data access for the SAP BI products. If there are topics you would like discussed here, or exotic datasources you would like tried out (see my previous post on drill), please use the comments to let me know !
- SAP Managed Tags:
1 Comment
