SQL Relay Command Line Programs
Starting SQL Relay
Before attempting to start SQL Relay, you must create a configuration file, see SQL Relay Configuration Guide for detailed instructions.
Assuming you have created a configuration file...
Use the sqlr-start program to start the SQL Relay server processes.
sqlr-start [-id ID] [-config CONFIGFILE] [-localstatedir LOCALSTATEDIR]
When run with the -id option, sqlr-start starts one instance of sqlr-listener, one instance of sqlr-scaler and the number of sqlr-connection daemons specified in the config file for the given ID.
When run without the -id option, sqlr-start starts sqlr-listener, sqlr-scaler and sqlr-connection daemons as appropriate for each instance in the config file(s) whose enabled parameter is set to "yes".
The optional -config CONFIGFILE argument may be used to specify a config file, directory containing a set of config files, a url that resolves to a config file, or a comma-separated list of any of the above. See the sections on Specifying Configuration Files and Remote Configuration Files in the SQL Relay Configuration Guide for more info.
If the optional -localstatedir LOCALSTATEDIR argument is used, it is passed to the programs started by sqlr-start as appropriate.
Stopping SQL Relay
The sqlr-stop command is provided to kill running instances of SQL Relay. It accepts an optional argument: the id of the instance to kill.
sqlr-stop [-id ID]
Running it with an ID kills SQL Relay processes that were started with the supplied ID. Running it with no arguments will kill all SQL Relay processes.
Command Line Clients
Several command line utilities are provided for use with SQL Relay. The syntax for each is as follows:
sqlrsh -host HOST -port PORT -socket SOCKET -user USER -password PASSWORD [-script SCRIPT | -command COMMAND] [-quiet] [-format plain|csv] [-resultsetbuffersize rows] sqlr-export -host HOST -port PORT -socket SOCKET -user USER -password PASSWORD (-table TABLE | -sequence SEQUENCE) > exportfile.xml sqlr-import -host HOST -port PORT -socket SOCKET -user USER -password PASSWORD -file exportfile.xml
or, if there's a configuration file that the client can read:
sqlrsh -id ID [-config CONFIGFILE] [-script SCRIPT | -command COMMAND] [-quiet] [-format plain|csv] [-resultsetbuffersize rows] sqlr-export -id ID [-config CONFIGFILE] (-table TABLE | -sequence SEQUENCE) > exportfile.xml sqlr-import -id ID [-config CONFIGFILE] -file exportfile.xml
The -host, -port, -socket, -user, and -password arguments specify which listener to connect to and what authentication to use.
The -id argument refers to an entry in a configuration file from which host, port, socket, user and passwords can be read.
The -config argument works the same way as described above for sqlr-start.
sqlrsh is an interactive query tool similar to sqlplus, mysql, psql, isql and the like. Run it and type help; at the prompt for detailed usage instructions. When sqlrsh starts up, it reads and executes two rc files, the system rc file (most likely /usr/local/firstworks/etc/sqlrshrc) and a personal rc file .sqlrshrc in your home directory. These files should contain sqlrsh commands, ending in semicolons, seperated by carraige returns. Any sqlrsh commands may be used in the rc files, even queries. If a SCRIPT is supplied with the -script option, then the script is run and sqlrsh exits. The script can contain any sqlrsh commands, incluing queries. If a COMMAND is supplied with the -command option, then the command is run and sqlrsh exits. The command can be single sqlrsh command (or query) or a list of semicolon-delimited commands (or queries).
The sqlr-export command exports the specified table or sequence to a file from which sqlr-import can later re-import it.
The sqlr-import command reads the specified file and imports the data contained in the file into the table or sequence specified in the file. Note, that sqlr-import does not create tables or sequences, it just imports data into an already-existing table or sequence.
sqlr-export and sqlr-import may be used to export data from one database and import it into another database, even if the databases are dissimilar (eg. one is PostgreSQL and the other is MySQL), if the structures of the tables in both databases are compatible.
On Windows the command-line clients can be run from the Windows Command Line. Adding C:\Program Files\Firstworks\bin to the PATH environment variable makes this easier, though is is not essential. You could just change directories to C:\Program Files\Firstworks\bin and run the programs from there.
The Status Monitor
The sqlr-status program displays statistics about a running SQL Relay instance. You can run it as follows, replacing instance with the name of the SQL Relay instance that you would like to read statistics from:
sqlr-status -id instance
Currently it generates output like:
Instance State : Enabled Open Database Connections: 2 Opened Database Connections: 2 Open Database Cursors: 2 Opened Database Cursors: 4 Open Client Connections: 0 Opened Client Connections: 0 Times New Cursor Used: 0 Times Cursor Reused: 0 Total Queries: 0 Total Errors: 0 Forked Listeners: 0 Scaler's view: Connections: 2 Connected Clients: 0 Mutexes: Connection Announce : not acquired (0) Shared Memory Access : acquired (1) Connection Count : acquired (1) Session Count : acquired (1) Open Connections/Forked Listeners : acquired (1) Triggers: Accept Available Connection (l-w, c-s) : triggered (1) Done Accepting Available Connection (c-w, l-s) : not triggered (0) Evaluate Connection Count (s-w, l-s) : not triggered (0) Done Evaluating Connection Count (l-w, s-s) : not triggered (0) Connection Has Started (s-w, c-s) : not triggered (0) Counts: Busy Listener Count : 0 Raw Semaphores: +-------------------------------------------------------+ | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | +---+---+---+---+---+---+---+---+---+---+-----+----+----+ | 0 | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | +-------------------------------------------------------+
Here are descriptions of the statistics:
- Open Database Connections: The number of database connections that are currently open.
- Opened Database Connections: The total number of database connections that have been opened since the instance was started.
- Open Database Cursors: The number of database cursors that are currently open.
- Opened Database Cursors: The total number of database cursors that have been opened since the instance was started.
- Open Client Connections: The number of SQL Relay clients that are currently connected to the SQL Relay server.
- Opened Server Connections: The total number of SQL Relay clients that have connected to this instance of the SQL Relay server since the instance was started.
- Times New Cursor Used: The number of times a cursor couldn't be reused.
- Times Cursor Reused: The number of times a cursor could be reused.
- Total Queries: The total number of queries that have been run through this instance.
- Total Errors: The total number of queries that generated errors.
- Forked Listeners: The total number of child listener processes that are running. This roughly corresponds to the number of clients that are waiting to access the database.
The rest of the stats are useful when reporting suspected bugs but are much more value to SQL Relay developers than users.
