Re: [Sqlrelay-discussion] routing queries

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

I managed to find a solution to this problem, if there are any big time DB2
users out there.

If you need to run set commands on connection startup, you can edit the
db2cli.ini file under the DB2 user's home directory.  (
/home/db2guy/sqllib/cfg/db2cli.ini )

You can create a section for each catalog, or use the [COMMON] section to
apply settings to all connections.  Here's an example that fixed my issue:

[COMMON]
SysSchema=writeuser
CURRENTSCHEMA=writeuser
SchemaList="'SYSIBM','WRITEUSER','READUSER'"
;CurrentFunctionPath="'SYSFUN','WRITEUSER'"
CurrentFunctionPath=sysfun,writeuser
CurrentPackagePath=writeuser

I'm still not quite sure which lines actually did the trick.  The
documentation is all over the place on syntax.  After saving this file, just
restart SQL Relay and the new settings should take affect.  There are also
mentions of performance tweaks for this file too, so it might be worth
checking out more.

Here's a few reference links:

http://www.thefillmoregroup.com/blog/?p=80

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.cli.doc/doc/c0007882.html

--Cal


On Thu, Apr 29, 2010 at 9:17 AM, Cal Heldenbrand <ca...@fb...> wrote:

> 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
>