[Sqlrelay-discussion] routing queries

[Cal H. <ca...@fb...>]

Hi all,

I've read through the docs and haven't seen a way to accomplish what I need
with our setup, but perhaps someone could make some suggestions.  We use DB2
with Q replication<http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/admin/cqrqrepl.htm>,
in which a read-write master replicates to read only slaves.  In order to
enforce a read only environment, we had to create a new username on the
slaves that doesn't allow writes.  (The read-write username is still needed,
since Qrep uses that)

Right now my DB2 client randomly selects a slave server for a particular set
of queries, connects as our "readuser" and then runs these set commands:

set schema writeuser
set current path=writeuser
set current function path=sysfun,writeuser

That does some magic to allow the readuser to access the tables and
functions of the writeuser.  In switching to SQL Relay with routing, I need
to find some method to run these queries when the connection is first
created to a read only server, before the client sends a query.

Is this possible?   Currently, I just have an instance setup for each read
only server, and I'm using the same logic in my client to pick the server
and run the set queries.  It's not perfect though, so I'd like to have SQL
Relay do this job.  :-)

Thanks,

--Cal