$Id: README,v 1.8 1999/12/22 14:46:29 mayoff release-1_0_1 $

DB2 Driver for AOLserver 3.0
Release 1.0

This is a database driver for DB2 Universal Database 6.1 and AOLserver
3.0. It was written by me, Rob Mayoff <mayoff@dqd.com>. Please let me
know if you find any bugs.  (I'd like to hear about use of this driver
in general.)

This driver may or may not work with DB2 version 5; I don't know.  It
will definitely not work with DB2 version 2; I used features that
(according to the manual) were introduced in version 5.

This driver does not implement all the functions supported by AOLserver
2.3 and earlier; it may work with those versions in a limited fashion.

/*--------------------------------------------------------------------*/

Compiling and Installing

To compile this driver, you'll need to have DB2 installed. You must set
INSTHOME to your instance home directory. (You may already have this
variable set correctly.)  You should have the DB2 include files
installed, which probably only happens if you have a DB2 development
environment installed.

You'll also need to have the AOLserver source code - NOT just the
binary distribution.  You should unpack the DB2 driver under the
AOLserver contrib directory.  It will create a subdirectory named
"dqd_db2".

You should be able to change to the dqd_db2 directory, "make install",
and encounter no errors.  If you encounter any errors, let me know.  The
DB2 driver will be installed in "../../root/bin", alongside the
AOLserver executable and the other loadable modules.

/*--------------------------------------------------------------------*/

Configuring DB2

I am not going to explain all about DB2 configuration here; you should
already be able to do that (or your DBA should).  However, you may need
to configure DB2 specially due to a problem with the DB2 client library.

Normally, if you are running the DB2 instance on the same machine as
your client software, then your client will talk to the database manager
using System V IPC. When I run in this configuration on my Linux host,
and use the control port to talk to the database more than once, I soon
see an error like this in the AOLserver log:

[14/Oct/1999:20:24:13][13144.9224][-nscp1-] Error: DB2 return code SQL_ERROR; sqlState = [40003]; nativeError = -1224; DB2 error message = "[IBM][CLI Driver][DB2/LINUX] SQL1224N  A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command. SQLSTATE=55032

After that error occurs, I have to restart AOLserver to get the database
working again.  I believe this is due to some bad interaction between
the DB2 client library and AOLserver's threads, or with the way the
control port works.

I don't know if this problem occurs on any platform other than Linux.
To work around it on Linux, you have to access your database via TCP
instead of System V IPC.  If you're accessing a database on another
Unix host, you're already doing that.  If you're accessing a local
database, though, then you're probably not using TCP.

To access a local database via TCP, you first have to add your machine
to the DB2 node directory.  I do it like this:

  catalog tcpip node local remote localhost server 50000

The "50000" is the TCP port number on which DB2 is listening.  You can
set this when you create the DB2 instance; 50000 is the default.  After
you catalog the node, you catalog the database.  I do it like this:

  catalog database test as test_tcp at node local authentication server

Now my "test" database is accessible via TCP using the name "test_tcp".

/*--------------------------------------------------------------------*/

Configuring AOLserver

The general procedure for configuring AOLserver to access a database is
described in the AOLserver Administrator's Guide:

    <http://www.aolserver.com/server/docs/3.0/html/driv-ch5.htm#38349>
    <http://www.aolserver.com/server/docs/3.0/html/con-ch3.htm#7935>

Here is an nsd.tcl excerpt for configuring the DB2 driver:

    ns_section "ns/db/drivers"
    ns_param db2 dqd_db2.so

    ns_section "ns/db/pools"
    ns_param main "Main database pool; uses DB2"

    ns_section "ns/db/pool/main"
    ns_param driver db2
    ns_param datasource test_tcp
    ns_param connections 5
    ns_param user db2user
    ns_param password ossifrag
    ns_param txnIsolation TXN_SERIALIZABLE
    ns_param autoCommit off

    ns_section "ns/server/server1/db"
    ns_param pools *
    ns_param defaultpool main

The datasource parameter in the "ns/db/pool/main" section should be the
database alias.  You can list your available datasources by running the
command "db2 list database directory".  The output will look like this:

     System Database Directory

     Number of entries in the directory = 2

    Database 1 entry:

     Database alias                  = TEST
     Database name                   = TEST
     Local database directory        = /u/db2
     Database release level          = 9.00
     Comment                         =
     Directory entry type            = Indirect
     Catalog node number             = 0

    Database 2 entry:

     Database alias                  = TEST_TCP
     Database name                   = TEST
     Node name                       = LOCAL
     Database release level          = 9.00
     Comment                         =
     Directory entry type            = Remote
     Authentication                  = SERVER
     Catalog node number             = -1

The datasources available here are TEST and TEST_TCP.  (Case doesn't
matter for the datasource name.)  Remember to use the TCP datasource.

The DB2 driver supports two extra options in the "ns/db/pool/XXX"
section:

    ns_param txnIsolation <constant>

        This option controls the transaction isolation level. DB2
        does not support the ISO standard "SET TRANSACTION ISOLATION
        LEVEL" SQL command, so you must set it via this option. The
        possible values are: TXN_READ_UNCOMMITTED, TXN_READ_COMMITTED,
        TXN_REPEATABLE_READ, and TXN_SERIALIZABLE. These are the ANSI
        names for the levels. The IBM names for the levels are:

            TXN_READ_UNCOMMITTED = Uncommitted Read (UR)
            TXN_READ_COMMITTED   = Cursor Stability (CS)
            TXN_REPEATABLE_READ  = Read Stability (RS)
            TXN_SERIALIZABLE     = Repeatable Read (RR)

        (It's annoying that ANSI's Repeatable Read is different from IBM's
        Repeatable Read.)  The isolation levels are fully documented in the
        DB2 SQL Reference (using the IBM terms).

        The default is TXN_SERIALIZABLE, which fully isolates transactions.

    ns_param autoCommit <boolean>

	This option enables or disables autocommit mode.  If autoCommit
	is enabled, then every SQL command you execute via the DB2
	driver is committed immediately upon execution.  If autoCommit
	is disabled, then you must manually commit or roll back your
	transactions, for example using one of these commands:

	    ns_db exec $dbh commit
	    ns_db exec $dbh rollback

	The default is off, meaning you must manually commit or roll
	back your transactions.

/*--------------------------------------------------------------------*/

DB2 Driver Extended Commands

Tcl 7.6 does not support binary data - all variable values are
NUL-terminated strings. Also, the DB2 SQL parser does not support
statements longer than 65536 bytes. To circumvent these limitations, the
DB2 driver supports some extended commands.

dqd_db2 exec <dbhandle> <sql> <input-list>

    You can use this instead of the standard "ns_db exec", "ns_db dml",
    and "ns_db select" commands. <sql> is the SQL command to execute.
    It should be a DML command such as INSERT or UPDATE.  In place of
    input values, you may put question marks.  For each question mark,
    you must give a type and an input source in <input-list>  The format
    of <input-list> is:

    {type1 variable-or-file-name1 type2 variable-or-file-name2 ...}

    The possible types are:

    BIGINT        BINARY      BLOB           CHAR    CLOB       DATE
    DBCLOB        DECIMAL     DOUBLE         FLOAT   GRAPHIC    INTEGER
    LONGVARBINARY LONGVARCHAR LONGVARGRAPHIC NUMERIC REAL       SMALLINT
    TIME          TIMESTAMP   VARBINARY      VARCHAR VARGRAPHIC

    If the type is BLOB, CLOB, or DBCLOB, you may specify a filename,
    which must begin with a period or a slash.  For any type, you may
    specify a variable name (which must not begin with a period or
    slash).  Specify the variable name, not the value: use "varName",
    not "$varName".

    Example:

        set transcriptVar "some long string here ... blah blah blah"
        dqd_db2 exec $dbh "insert into user_table
            (user_id, transcript, retina_scan, voice_print)
            values(?, ?, ?, ?)" {
                integer     userId
                longvarchar transcriptVar
                blob        /tmp/retina.gif
                blob        /tmp/voice.wav
            }

    Of course, you'll probably have the filename in a variable, in which
    case you can do something like this:

        set userId 123
        set imageFile "/tmp/retina.gif"

        dqd_db2 exec $dbh "update user_table
            set retina_scan = ?
            where user_id = ?" [list \
                blob    $imageFile \
                integer userId     \
            ]

    The advantage of using a question mark placeholder for userId
    instead of interpolating it (e.g. "where user_id = $userId") is that
    with a placeholder you do not need to call ns_dbquotevalue.

    If you are using a value from a Tcl variable as a binary value (for
    a BLOB, BINARY, etc.) then the value is assumed to be encoded in
    hexadecimal.

dqd_db2 getrow <dbhandle> <output-list>

    You can call this instead of the regular "ns_db getrow" function.
    Instead of passing an ns_set to receive the column values, you pass
    a list of variable names and/or filenames. For example:

        ns_db exec $dbh "select transcript, retina_scan, voice_print
            from user_table where user_id = $userId"
        dqd_db2 getrow $dbh {transcriptVar /tmp/retina.gif /tmp/voice.wav}

    The DB2 driver assumes that items in the input list are filenames
    if they begin with a dot or slash; otherwise they are taken to be
    variable names.  Note that you must NOT specify types in
    <output-list>; you only specify variable and file names.  However,
    you may only use filenames for columns that are LOB types.

    Of course, you can use the extended exec and getrow commands
    together. For example:

        set userId 123
        set imageFile "/tmp/retina.gif"

        dqd_db2 exec $dbh "select retina_scan from user_table
            where user_id = ?" {integer userId}
        dqd_db2 getrow $dbh $imageFile

    This extended version of getrow has the same return values as the
    regular getrow: "0" means no more rows (and the variables and
    filenames in the output list are not modified); "1" means a row was
    returned.

    If you fetch a binary value into a Tcl variable, then the value will
    be hexadecimal-encoded.

dqd_db2 sendcolumn <dbhandle> <columnIndex>

    You can call this function to send the value of a column directly to
    the client connection. The <columnIndex> should be zero You need to
    have already executed a SELECT statement on the database handle. For
    example:

        set userId 123
        dqd_db2 exec $dbh "select retina_scan from user_table
            where user_id = ?" userId
        ns_return 200 image/gif ""
        dqd_db2 sendcolumn $dbh 0

    The sendcolumn command has the same return values as the getrow
    command: "0" means that no more rows are available and nothing was
    written to the connection; "1" means a row was available and the
    column value (possibly of zero length) was written to the
    connection.

    This command always sends the column value in its native binary
    format. This is fine for text and binary columns such as VARCHARs,
    CLOBs and BLOBs. However, this is probably not what you want for
    columns with other types such as INTEGER, TIMESTAMP, etc. For
    those columns you probably want to send the value as a textual
    representation. To do that you must get the column value into an
    Ns_Set or Tcl variable using "ns_db getrow" or "dqd_db2 getrow", and
    then send it using ns_write. Alternatively, you can convert it to a
    text representation in your SELECT statement (for example using the
    CHAR scalar function) and then use sendcolumn, like this:

	ns_db exec $dbh "select char(birthdate) from personnel
	    where employee_id = $id"
	dqd_db2 sendcolumn $dbh 0

LOB Limits:
    The "ns_db getrow" and "dqd_db2 getrow" commands will only fetch up
    to 10MB into a Tcl variable. If you try to fetch a LOB larger than
    that, it will be truncated and a warning will be printed to the log.
    If you need to fetch more than 10MB of LOB data, you must either
    fetch the value to a file using "dqd_db2 getrow" or send the value
    directly to the client using "dqd_db2 sendcolumn".  (Or you can
    recompile the driver with a larger value for DB2_maxDataSize.)

