Forum OpenACS Q&A: OpenACS and DB2

1: OpenACS and DB2

Posted by Claudio Pasolini on 09/18/06 03:10 PM

I'd like to inform the community that I succeeded connecting an OpenACS 5.1.3 (with PostgreSQL 7.4 and Aolserver 4.0.10) to a DB2 database runnning on an AS/400.

I started from a Dossy's article explaining the components necessary. We need:

the nsodbc module and this patch
a Unix ODBC manager
a DB2 driver
a modified config.tcl

unixODBC

The installation was very easy and as a result I got an isql shell (similar to psql) and the client libraries against which to compile nsodbc.

StarSQL comes with its own ODBC manager and so there was no need to install unixODBC manager. It was sufficient to compile nsodbc specifying ODBC=/usr/share/starsql/odbc/lib

The config.tcl must be modified loading the new module, adding and specifying a new database pool.

After starting aolserver I had initially a problem, because the process is started by root but the necessary STARSQL environment variables were only set in the regular user.

Afterward I had to do a one line modification to the proc db_bootstrap_set_db_type in 20-db-bootstrap-procs.tcl beacuse the bootstrap process tries to get an handle from all availablepools and fails because the db type of the new pool is unknown. (I simply avoided getting an handle from the new pool)

The only limitations are that:

AFAIK it is not possible to use our db_ API but we have to stick with the old ns_db
StarSQL is not Open Source, but at least I have to say that their support is very good.

2: Re: OpenACS and DB2 (response to 1)

Posted by Claudio Pasolini on 09/18/06 04:28 PM

I had never before used the -dbn switch of the db_* API and tried it with the new db2 database.

I added the following section to my config.tcl:
ns_section ns/server/${server}/acs/database ns_param database_names [list main db2] ns_param pools_main [list pool1 pool2 pool3] ns_param pools_db2 [list pool4]
and observed that the pools are correctly allocated during the bootstrap, but trying a db_foreach I get the following error:
invalid command name "ns_odbc_bind" while executing "ns_odbc_bind select nsdb1 {select ANCOCO, INCIT, INCAP from CED.FCAP fetch first 10 row only}" ("uplevel" body line 1) invoked from within "uplevel $ulevel [list ns_odbc_bind $type $db $sql]" ("nsodbc" arm line 2) invoked from within "switch $driverkey { oracle { return [uplevel $ulevel [list ns_ora $type $db $sql] $args] } ..." invoked from within "db_exec select $db $full_statement_name $sql"

Reading the comments in 00-database-procs it seems that the odbc should be supported, isn't it?

3: multi-db db_* API support for ODBC (response to 2)

Posted by Andrew Piskorski on 09/20/06 12:26 AM

Claudio, when I wrote the first version of the multi-db support (with the -dbn switch, etc.) on ACS 4.2 back in 2001, I was using ODBC with AOLserver for an experimental project, so I made multi-db work with ODBC. The basic SQL functionality did work.

Key bit for you: At that time, I added bind variable emulation to the ODBC AOLserver driver (based totally on the PostgreSQL db driver code), and then used it in multi-db. I think that driver was nsodbc, although there are/were several other AOLserver ODBC db drivers as well. The "ns_odbc_bind" failure you're seeing sounds like you're using a version of the driver which does not contain bind variable emulation. (I don't remember whether I ever contributed it to the SourceForge AOLserver CVS - quite likely not.)

Also, when I later ported the multi-db code to OpenACS, I was no longer using ODBC at all, and I have never used it since. (I set up PostgreSQL just so I could test against it, and tested against Oracle since I used it, but I don't think I tested with ODBC at all at that point.) AFAIK, no one else has ever used the multi-db code with ODBC either, it has only been used for real with Oracle and PostgreSQL.

So, given it's totally unused orphan status for the last five years, I'm not suprised that OpenACS multi-db support for ODBC is broken. So once you fix or work-around the "ns_odbc_bind" thing, you might encounter additional problems as well, but probably only small ones since the multi-db code has been stable for a long time.

4: Re: multi-db db_* API support for ODBC (response to 3)

Posted by Andrew Piskorski on 09/20/06 12:46 AM

Some relevant links:

The Sept. 2001 discussion that prompted me to add the bind variable emultion to nsodbc.
The current latest version of the code still contain my 2003-04-09 comments on to what extent nsodbc is supported, etc. That info is still current AFAIK.