CustomerBackend via unixODBC to DB2

[pvanos]

Platform: OTRS 3.3.8 installed on CentOS6.5, with ActivePerl (perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi)

I would like to access a Customer Backend database on DB2 (and a different one on mssql, but that's another item).
So, I installed the V10.5 IBM Data Server Driver for ODBC and CLI (and not the IBM Data Server Client nor the IBM Data Server RunTime Client).
This was installed in /opt/otrs/db2_cli_odbc_driver/ as described in http://www-01.ibm.com/support/knowledge ... 23864.html
Special remarks :
1) it is put inside the homedir of the otrs user
2) there is no install involved, only tar -xzvf
3) modify the default permissions (rwx on /bin), and corrected ownership (otrs:apache -> should be propagated from the otrs-installation otrs.Permissions.pl
4) create the correct :
• /etc/odbcinst.ini
• /etc/odbc.ini
• /opt/otrs/db2_cli_odbc_driver/odbc_cli/clidriver/cfg/db2cli.ini
• /etc/environment (added the paths for ODBCINI=/etc/odbc.ini DB2DSDRIVER_CFG_PATH=/opt/otrs/db2_cli_odbc_driver/odbc_cli/clidriver/cfg DB2CLIINIPATH=/opt/otrs/db2_cli_odbc_driver/odbc_cli/clidriver/cfg DB2_CLI_DRIVER_INSTALL_PATH=/opt/otrs/db2_cli_odbc_driver/odbc_cli/clidriver/bin OTRS_HOME=/opt/otrs) Maybe not all are needed. Put all space separated variables each on a new line).

When querying the DB2 database from a command line, I get a nice result on my screen, so the IBM DB2 ODBC driver works !!!:
su - otrs
cd /opt/otrs/db2_cli_odbc_driver/odbc_cli/clidriver/bin
echo "SELECT * from BLADIBLA.CUSTOMER_USER where PHONE = '8452'" | ./db2cli execsql -dsn bladibla

The lowercase bladibla is actually pointing to (and equal to) a section name (between square brackets) in /etc/odbc.ini

In addition I installed the unixODBC Driver Manager (see http://www.easysoft.com/products/data_a ... xodbc.html)
I can query all datasource names (DSN):
odbcinst -q -s
and also the drivers:
odbcinst -q -d

But where it goes wrong is when the Customer Backend config part is added to /opt/otrs/Kernel/Config.pm
After restarting the machine:
ERROR: OTRS-otrs.PendingJobs.pl-10 Perl: 5.10.1 OS: linux Time: Fri Sep 26 14:45:02 2014

Message: Module Kernel/System/DB/db2.pm not found/could not be loaded!

Traceback (1940):
Module: Kernel::System::DB::new (OTRS 3.3.8) Line: 153
Module: Kernel::System::CustomerUser::DB::new (OTRS 3.3.8) Line: 105
Module: Kernel::System::CustomerUser::new (OTRS 3.3.8) Line: 105
Module: Kernel::System::Ticket::new (OTRS 3.3.8) Line: 151
Module: /opt/otrs/bin/otrs.PendingJobs.pl (unknown version) Line: 52

Can't connect to database! at /opt/otrs/Kernel/System/CustomerUser/DB.pm line 105, <DATA> line 522.

The thing is that the module Kernel/System/DB/db2.pm indeed does NOT exist.
But WHY is it attempting to go there anyway ?
The idea is not to use any DBD:DB2 module (that was deliberately not installed, as it requires the installation of the heavy !!full!! IBM Data Server Client)
but to go via the DBI:ODBC driver instead !!

An extract out of my Config.pm :
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser2} = {
Name => 'bladibla',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the required settings
DSN => 'dbi:ODBC:bladibla',
Type => 'db2', # only for ODBC connections
User => 'MickyMouse',
Password => 'DontTell',
Table => 'BLADIBLA.CUSTOMER_USER',
CaseSensitive => 0,
},
# customer unique id
CustomerKey => 'login',
(...REMAINDER OMITTED...)

Is there anything missing (or that I should modify) in the default OTRS database connection scripts in order for OTRS to be aware of the unixODBC driver manager ?

Paulus

[jojo]

OTRS 3.3.x does not have an DB2 driver. Not native not via ODBC

[crythias]

I cannot vouch whether it will work, but it *appears* that the db2.pm from 3.2 https://github.com/OTRS/otrs/blob/rel-3 ... /DB/db2.pm may be able to be dropped-in, based upon last touch.

I can't confirm this, nor am I able to test or provide support on it if it doesn't work.

[pvanos]

@ crythias
as user otrs I created a new db2.pm in /opt/otrs/Kernel/System/DB/ copied the contents of the site you mentioned, changed the owner and permissions to match the other .pm files.
Then I restarted the machine, and got rid of the message the db2.pm file could not be found, but:
now I have a new one :
DBI connect('DRIVER=db2_odbc_driver','dcmdb',...) failed: [unixODBC][IBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".
(SQL-08001) at /opt/otrs/Kernel/System/DB.pm line 252
ERROR: OTRS-otrs.UnlockTickets.pl-10 Perl: 5.10.1 OS: linux Time: Mon Sep 29 12:35:02 2014

Message: [unixODBC][IBM][CLI Driver] SQL10007N Message "0" could not be retrieved. Reason code: "3".
(SQL-08001)

Traceback (1815):
Module: Kernel::System::DB::new (OTRS 3.3.8) Line: 223
Module: Kernel::System::CustomerUser::DB::new (OTRS 3.3.8) Line: 105
Module: Kernel::System::CustomerUser::new (OTRS 3.3.8) Line: 105
Module: Kernel::System::Ticket::new (OTRS 3.3.8) Line: 151
Module: /opt/otrs/bin/otrs.UnlockTickets.pl (unknown version) Line: 55

Can't connect to database! at /opt/otrs/Kernel/System/CustomerUser/DB.pm line 105, <DATA> line 522.

Searching further on this, I found http://unixodbc.13851.n7.nabble.com/Hug ... td949.html, but this is about Driver = /opt/ibm/db2/V9.7/lib64/libdb2.sol
Still, in the end, the solution at that time was allegedly related to the wordings in the connection string....

I obtained this message with either of the following variants for the DSN string in Config.pm:
DSN => 'DBI:ODBC:NameOutOf/etc/odbc.ini',
or
DSN=> 'DBI:ODBC:DRIVER=NameOutOf/etc/odbcinst.ini',

Apart from this line, the lines with Type => 'db2', User => 'uuuu', Password => 'pppppp', Table => 'TTTTTTTTTT.CUSTOMER_USER', and CaseSensitive => 0, were in both cases following the DSN line.

@jojo
If it is correct that there is no DB2 driver via ODBC, for OTRS,
why is it that I can connect to DB2 using the ODBC driver, when using the db2cli application ?
In addition our current version 3.2.1 (running on a Windows 2003 machine, using the Windows ODBC driver for DB2) is working with a DB2 customerUser backend ?
OTRS is in this case using the operating system's ODBC driver to access the remote DB2 database, here as well ?
So why wouldn't version 3.3.8 be capable to do the same (okay, it has to use a linux ODBC driver rather than a Windows ODBC driver) ?

Many thanks for your reactions ! I really appreciate it.

[jojo]

DB2 support was removed on OTRS 3.3.

[crythias]

Like I said, I can't provide support. I can search but I can't tell you what to do with it. Also, no support is no support.