$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 . 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: 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 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 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 You can use this instead of the standard "ns_db exec", "ns_db dml", and "ns_db select" commands. 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 The format of 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 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 ; 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 You can call this function to send the value of a column directly to the client connection. The 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.)