[Sqlrelay-discussion] SQL Relay with DB2 9.5

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Hello,

I just stumbled onto your project, and I find the feature list of SQL Relay
very exciting.  This is the first time I've tried to compile and run it, and
I'm having a problem with the DB2 9.5 client.

My environment is a mix of CentOS/RHEL 4 and 5, with my current test machine
running RHEL 4, kernel 2.6.9 (x86_64).  The DB2 client is 9.5.3, with server
version 9.5.4.  I also have a mix of 9.7.x clients, but I haven't tested
those yet.

To get all of this to compile, I modified your acsite.m4 file and changed
the hard coded DB2 9.1 paths to point at my 9.5 installation, then reran
aclocal/autoconf.  Everything appears to have compiled and linked correctly.

***** DB2 **********************
db2 version... 9
db2 includes... -I/opt/ibm/db2/V9.5/include
db2 libs... -L/opt/ibm/db2/V9.5/lib64 -ldb2
******************************

When I try to run the sqlr-start binary, I get this output:

----------------------------------------------------------------------------------------------------------------------------------
$ ./sqlr-start -id dev

Starting listener:
  sqlr-listener -id dev -config /usr/local/firstworks/etc/sqlrelay.conf
-debug
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.9475

Starting 5 connections to dev :
  sqlr-connection-db2 -id dev -connectionid dev -config
/usr/local/firstworks/etc/sqlrelay.conf -debug
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9477
  sqlr-connection-db2 -id dev -connectionid dev -config
/usr/local/firstworks/etc/sqlrelay.conf -debug
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9479
  sqlr-connection-db2 -id dev -connectionid dev -config
/usr/local/firstworks/etc/sqlrelay.conf -debug
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9481
  sqlr-connection-db2 -id dev -connectionid dev -config
/usr/local/firstworks/etc/sqlrelay.conf -debug
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9483
  sqlr-connection-db2 -id dev -connectionid dev -config
/usr/local/firstworks/etc/sqlrelay.conf -debug
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9485

Starting scaler:
  sqlr-scaler -id dev -debug  -config
/usr/local/firstworks/etc/sqlrelay.conf

sqlr-scaler error:
        The file
/usr/local/firstworks/var/sqlrelay/tmp/pids/sqlr-listener-dev was not found.
        This usually means that the sqlr-listener is not running.
        The sqlr-listener must be running for the sqlr-scaler to start.

sqlr-scaler failed to start.
----------------------------------------------------------------------------------------------------------------------------------

I have also tried to re-run the sqlr-listener and sqlr-scaler manually, but
it seems I still don't get a connection.

The debug output also hasn't been too helpful:

----------------------------------------------------------------------------------------------------------------------------------
$ cat sqlr-listener.9475
creating shared memory and semaphores
id filename:
/usr/local/firstworks/var/sqlrelay/tmp/ipc/dev
creating shared memory...
creating semaphores...
----------------------------------------------------------------------------------------------------------------------------------
$ cat sqlr-connection.9477
getting unix socket...
opening /usr/local/firstworks/var/sqlrelay/tmp/sockseq
locking...
got sequence number: 46
writing new sequence number: 47
unlocking...
done getting unix socket
attaching to shared memory and semaphores
id filename:
/usr/local/firstworks/var/sqlrelay/tmp/ipc/dev
attaching to shared memory...
attaching to semaphores...
done attaching to shared memory and semaphores
logging in...
done logging in
initializing cursors...
0
1
2
3
4
done initializing cursors
setting autocommit...
done setting autocommit
incrementing connection count...
acquiring connection count mutex
done acquiring connection count mutex
1
signalling scaler to read
done signalling scaler to read
releasing connection count mutex
done releasing connection count mutex
done incrementing connection count
creating /usr/local/firstworks/var/sqlrelay/tmp/ipc/dev-dev
waiting for available database...
database is available
database is available
initializing session...
done initializing session...
announcing availability...
registering for handoff...
handoffsockname: /usr/local/firstworks/var/sqlrelay/tmp/sockets/dev-handoff
trying...
done registering for handoff
acquiring announce mutex
done acquiring announce mutex
handoff=pass
signalling listener to read
done signalling listener to read
waiting for listener
done waiting for listener
releasing announce mutex
done releasing announce mutex
done announcing availability...
waiting for client...
pass failed
done waiting for client
decrementing session count...
acquiring session count mutex
done acquiring session count mutex
-1
releasing session count mutex
done releasing session count mutex
done decrementing session count
waiting for available database...
database is available
database is available
initializing session...
done initializing session...
announcing availability...
registering for handoff...
handoffsockname: /usr/local/firstworks/var/sqlrelay/tmp/sockets/dev-handoff
trying...

----------------------------------------------------------------------------------------------------------------------------------

My config file is:

----------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

<instance id="dev" port="9000" socket="/tmp/db2-dev.socket"
        dbase="db2" connections="5" maxconnections="10"
        maxqueuelength="0" growby="1" ttl="60" endofsession="commit"
        sessiontimeout="60" runasuser="db2user" runasgroup="db2user"
        cursors="5" authtier="listener" handoff="pass"
        listenertimeout="20"
        maxlisteners="20"
        debug="listener_and_connection"

>
        <users>
                <user user="db2user" password="pass" />
        </users>
        <connections>
                <connection connectionid="dev"

string="db=devdb;user=db2user;password=pass;autocommit=yes"
                        metric="1" behindloadbalancer="no"
                />
        </connections>

</instance>

</instances>

----------------------------------------------------------------------------------------------------------------------------------

And here is the catalog and node information for the "devdb" catalog:

 Database alias                       = DEVDB
 Database name                        = FLEXPROD
 Node name                            = DEVDB
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

 Node name                      = DEVDB
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = 192.168.74.29
 Service name                   = 51000

When I manually try to start up the two failed services, they seem to run,
but output no debugging information.  Running the sqlrsh command line client
gives me:

----------------------------------------------------------------------------------------------------------------------------------
$ sqlrsh -id dev
SQLRShell - Version 0.22
        Connected to: localhost:9000 as db2user

        type help; for a help.

0> debug on;
0> select * from sysibm.sysdummy1;
Result Set Buffer Size: 100
Connecting to listener...
Unix socket: /tmp/db2-dev.socket
Inet socket: localhost:9000
Setting Error
Couldn't connect to the listener.
Couldn't connect to the listener.

        Rows Returned   : 0
        Fields Returned : 0
        System time     : 10000

----------------------------------------------------------------------------------------------------------------------------------

Thanks for any help, and let me know if you need more information!

--Cal