Users Guide
This page is intended to cover the basic-day-to-day usage of JSqsh. It assumes that you have already performed the steps covered in Installing and Getting Started.
Table of Contents
When you start up jsqsh it will drop you straight into its prompt (well, the first time, it will enter a setup wizard and then drop you into a prompt):
JSqsh Release 2.1.0, Copyright (C) 2007-2015
Type \help for available help topics. Using JLine.
1>
each time you type something into the prompt, jsqsh looks at the line you just entered and does the following:
- It looks at the first word you entered on the line to see if it is a command that it recognizes, if it is then it executes the command
- It looks at the end of the line you just entered to see if it ends with the statement terminator (a semicolon by default), if it is and if you are connected to a database, then the statement is executed (this behavior is covered in detail here).
- If neither of the above is true, then jsqsh assumes that you are in the middle of typing a statement that will eventually be sent to the database server and sticks the line you just entered into its current SQL Buffer (this is covered in the next section, The SQL Buffer).
An interesting thing to note is that jsqsh doesn't understand SQL at all! In fact, frankly, it doesn't even know or care if the thing you are connected to speaks SQL or some other language. As a result, jsqsh tries to take care that most of its commands start with a leading backslash (\) to try to make sure that it doesn't try to interpret something that you intended to send to the database.
For example, jsqsh has a command called \set used to set a jsqsh configuration variable (see Variables, below), like so:
1> \set prompt='hi $lineno> '
hi 1>
many database SQL dialects have a statement called SET, so it would have been a problem if jsqsh had a command with the same name.
To see a list of commands supported by jsqsh, use \help commands (see \help):
1> \help commands
Available commands. Use "\help <command>" to display detailed help for a given command
+--------------+----------------------------------------------------------------------+
| Command | Description |
+--------------+----------------------------------------------------------------------+
| \alias | Creates an alias |
| \buf-append | Appends the contents of one SQL buffer into another |
| \buf-copy | Copies the contents of one SQL buffer into another |
| \buf-edit | Edits a SQL buffer |
| \buf-load | Loads an external file into a SQL buffer |
...
And, each command has detailed built-in help as well:
1> \help \set
SYNOPSIS
\set [[-x] [-l] name=value]
DESCRIPTION
Sets a variable or displays all variables.
OPTIONS:
If arguments are provided to \set, then the value of
all variables is displayed.
-x, --export Will cause the variable to be exported to the environment
of any processes that are spawned by jsqsh during its
execution.
-l, --local Sets the value of the variable local to the current session
only. No other sessions will see it and it will mask
similarly named variables in other session.
All jsqsh commands take one or more command line options. Besides using \help <command> to see the detailed help for a given command, you can see the options a given command accepts by passing the -h or --help option to the command:
1> \set --help
Use: \set [-x] [-l] [var=value]
-x, --export Export the variable
-g, --gui Send all command output to a graphical window
-h, --help Display help for command line usage
-l, --local Define the variable locally to this session
All command options take either a short form (e.g. -l) or a long form (e.g. --local). In addition all commands take a --gui (or -g) option, that causes a window to pop up, displaying the output of the command in a separate graphical window instead of in the current terminal window. This is useful if you want to retain the output of one command while you are working, for example to keep a list of jsqsh commands handy, you can do:
1> \help --gui commands
will pop up a window containing the output:
Sometimes the command names in jsqsh can be rather verbose (like \buf-edit) or typing the backslash character can be anger some people. To alleviate the such anxieties, jsqsh allows an alias to be established for a given command. For example:
1> \alias sayonara=\quit
establishes an alias for the \quit command called "sayonara". So, now to quit out of jsqsh, you can do:
1> sayonara
shell $
An alias can also include command line options for the original command, so if you wanted to have a simple way to run a query but display the output in CSV format, you could do something like:
1> \alias cgo='\go -m csv'
1> select 1, 2 from sysibm.dual
2> cgo
1,2
JSqsh comes pre-wired with a number of convenience aliases:
1> \alias
+-------+---------+------------+
| Alias | Global? | Text |
+-------+---------+------------+
| :e | N | \eval |
| :r | N | \buf-load |
| \dbs | N | \databases |
| \edit | N | \buf-edit |
| \s | N | \session |
| emacs | N | \buf-edit |
| go | N | \go |
| quit | N | \quit |
| vi | N | \buf-edit |
+-------+---------+------------+
Alias definitions are not remembered after you query out of jsqsh, so you typically would add them to your sqshrc file.
The "sh" in jsqsh is short for shell. This is because jsqsh likes to pretend that it is like a unix shell, supporting the two big features that all good shells provide: the ability to redirect the output of a command to a file, or to pipe the output of a command to another program.
For example, if you want to run a query and send its output to a file, rather than to your screen, you can do:
1> select * from sysibm.systables
2> go -m csv > systables.csv
The go command will execute the select select, the -m csv option to "go" will cause the output to be rendered in comma separated value format, and the > systables.csv will send all output to a file called systables.csv.
As with a unix shell, all programs send most of their output to stdout ("standard output", the "normal" place to send output), and errors go to stderr (the "error output"), so if you did the above but, say, your SQL contained syntax errors you'd see:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go -m csv > systables.csv
SQL Exception(s) Encountered:
[State: 42601][Code: -104]: An unexpected token "." was found following "select * from sysibm". Expected tokens may include: "FROM".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.33
[State: 56098][Code: -727]: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens ".|select * from sysibm|FROM".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.67.33
You'll note that even though the output of the command was redirected to a file, the errors were still sent to the screen. This is because only the stdout was redirected to the file. If you wanted to capture both the output and the errors to the same file, you'd do the same thing you'd do in a unix shell and use "2>&1" (which means: send stderr to stdout), like so:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go -m csv > systables.csv 2>&1
For detailed help on how such redirection works, run \help redirection (or click here).
Along with the ability to redirect the output of a command to a file, jsqsh can instead send that output off to an external program to process using a pipe ("|"). So, for example, let's say you wanted to send the results of a query to a file off on another system, you could do something like:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go -m csv | ssh user@othersystem.com 'cat > systables.csv'
or if you are like me and cannot always be bothered with typing those pesky WHERE clauses, you can do:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go | grep SYSIBM
Much of jsqsh's behavior can be altered by changing configuration variables using the \set command. For example, to change how jsqsh represents the value for NULL, you can set the null variable:
[localhost][gray] 1> select NULL as "NULL" from sysibm.dual;
+--------+
| NULL |
+--------+
| [NULL] |
+--------+
[localhost][gray] 1> \set null='N/A'
[localhost][gray] 1> select NULL as "NULL" from sysibm.dual;
+------+
| NULL |
+------+
| N/A |
+------+
You can get a list of configuration variables using \help vars:
[localhost][gray] 1> \help vars
Available configuration variables. Use "\help <variable>" to display detailed help for a given variable
+---------------------+------------------------------------------------------------+
| Variable | Description |
+---------------------+------------------------------------------------------------+
| SID | Oracle instance to which connection is established |
| autocommit | Contains the connection's current auto-commit status |
| binstr_fmt | Changes the manner in which binary values are displayed |
| build_date | The jsqsh build date |
| classpath | Defines JDBC driver locations |
| connected | Indicates whether or not the current session is connected |
| database | Contains the connection's current database |
...
And, detailed help is available for each configuration value using \help <variable> or you can use the Variables Reference:
[localhost][gray] 1> \help null
VARIABLE
null - Controls the visual representation of nulls
DESCRIPTION
The ${null} variable is used when displaying null values. The default
value is "[NULL]", which will be shown whenever a null value is
encountered.
To view the value of a jsqsh variable use the \echo command:
[localhost][gray] 1> \echo $null
N/A
JSqsh does not retain the value of variables you have set between executions. To have variables set each time jsqsh starts, place calls to \set in your $HOME/.jsqsh/sqshrc file, covered below.
In addition, you can set variables during startup via the jsqsh command line option -v (or --var), like so:
shell$ jsqsh -vnull="N/A"
the complete set of jsqsh command line arguments can be found here.
When variables are used inside of a command execution, the rules for variable expansion are very much like a regular UNIX shell; a dollar sign ($) followed by the name of a variable will be replaced with its value:
1> \set x=20
1> \echo $x
20
And you can use curly braces to clearly delimit the beginning and end of the variable name:
1> \echo xx${x}xx
xx20xx
Also, as with a shell, the types of quotes that you provide to an argument of a command determine whether or not variable expansion occurs. Within double quotes variables will be expanded:
1> \set who=Scott
1> \echo "Hello, $who"
Hello, Scott
however, single quotes prevent variable expansion:
1> \echo 'Hello, $who'
Hello, $who
JSqsh performs variable expansion using a tool called Velocity. Velocity doesn't just do simple variable expansion but is a full "macro" language, meaning that it allows for conditional expressions and a whole variety of other features. This can allow you to do all sorts of interesting (if not difficult to read!) things like:
1> set current schema #if($schema) $schema #else sysibm #end;
This uses Velocity's IF/ELSE syntax so that if the $schema variable has been set, then that schema will become the current working schema, otherwise the SYSIBM schema will be used.
By default jsqsh will not expand variables within queries this is primarily because jsqsh does not know if the '$' character was already part of the language of the server you are currently connected too, and the use of variable expansion can lead to security issues if not carefully utilized. You can, however, enable variable expansion within queries using the $expand variable:
1> \set expand=true
1> \set who=Scott
1> select '$who' from sysibm.dual;
+-------+
| 1 |
+-------+
| Scott |
+-------+
As you type text at the prompt of jsqsh, each line you type is accumulated into a SQL buffer (strictly speaking JSqsh has no idea that you are even typing SQL) until one of the following conditions occurs:
- It looks at the first word you entered on the line to see if it is a command that it recognizes, like go or \help, if it is then it executes the command (run
\help commandsto see a list of all available jsqsh commands). - It looks at the end of the line you just entered to see if it ends with the statement terminator (a semicolon by default), if it is then the statement is executed (this behavior is covered in detail here).
For example, in the following:
1> select count(*)
2> from sysibm.systables
3> go
+--------+
| 1 |
+--------+
| 236 |
+--------+
1 row in results(first row: 0.0s; total: 0.0s)
The first two lines were accumulated into the SQL buffer until the go command was executed. The go command takes whatever was in the buffer and executes it through the current database connection.
It is important to note that jsqsh tries to be pretty smart about what you are doing, and does attempt to recognize if you in the middle of a comment or a quoted string and will not recognize commands or the statement terminator while within them, for example:
1> select 'in an open string
2> go
3>
the go will not execute the statement because you are currently inside of a string literal, similarly,
1> select count(*) from sysibm.systables /*
2> go
3>
the go will not run because you are inside of a comment.
While jsqsh supports command line editing (using the arrow keys to move around the current line or to recall previous lines), sometimes you want to edit the entire query that you are in the middle of entering. For this you can use the jsqsh \buf-edit command to pull up the current buffer in an external editor:
1> CREATE PROCEDURE P1 (IN V1 INT, OUT V2 INT)
2> LANGUAGE SQL
3> BEGIN
4> SET V2 = V5 * 2; -- Oops, "V5" isn't a valid variable
5> \buf-edit
The \buf-edit command figures out which editor to launch with your SQL by:
- It looks at the environment variable called EDITOR
- It that isn't set, it looks at the environment variable VISUAL
- If that isn't set, it runs "notepad.exe" on windows, or "vi" on all other systems
The editor will then be pulled up with the contents of the current SQL buffer, like so:
At which point you can edit and even finish your statement and save and quit out of the editor, at which point you will find yourself back at the jsqsh prompt, with the current SQL Buffer reflecting the change you just made:
1> CREATE PROCEDURE P1 (IN V1 INT, OUT V2 INT)
2> LANGUAGE SQL
3> BEGIN
4> SET V2 = V1 * 2; -- All better now
5>
Because \buf-edit it is a bit of a handful to type, jsqsh comes in with two built-in aliases for the command called vi and emacs:
1> select kount(*) from
2> vi
note that whether or not you use vi or emacs you are really calling \buf-edit and it is going through the steps described above to figure out which editor to launch!
Every time a statement is executed via the go command or via the statement terminator, jsqsh saves that statement away, remembering the last 50 statements executes (by default). You can view these previously executed statements using the \history command:
1> \history
(1) { call test_inout(?='scott is here') }
(2) { call test_inout(?='scott') }
(3) drop procedure test_inout
(4) create procedure test_mixed (IN i1 INT, OUT i2 DOUBLE, INOUT i3 varchar(30))
result sets 1
language sql
begin
declare c1 cursor with return to client
for select 'result set', i1, i3 from sysibm.dual;
set i2 = 34.2;
set i3 = 'fred was here';
open c1;
...
(5) { call test_mixed (5, ?, ?='blah') }
Note that for very long statements, like statement (4), above, jsqsh will only show the first 10 lines in order to keep the output of getting huge.
At any point, you can recall a previously executed statement into the current SQL buffer, using jsqsh's history recall syntax. For example, to retrieve statement #4 above, you can enter !4, like so:
1> !4
1> create procedure test_mixed (IN i1 INT, OUT i2 DOUBLE, INOUT i3 varchar(30))
2> result sets 1
3> language sql
4> begin
5> declare c1 cursor with return to client
6> for select 'result set', i1, i3 from sysibm.dual;
7>
8> set i2 = 34.2;
9> set i3 = 'fred was here';
10> open c1;
11> end
12>
JSqsh's history syntax recall syntax is:
-
!N: Recalls statement #N as listed in the output of the
\historycommand - !-N: Recalls the N'th previously executed buffer
- !..: Recalls the previously executed buffer. Additional dots may be provided to go further back
- !.: Refers to the current SQL buffer (examples of where this is useful below)
see buffers for more details.
A number of jsqsh commands work with SQL buffers. The following table provides a brief summary of these commands and how they interact with buffers.
| Command | Description |
|---|---|
| go | Executes the statement in the current SQL buffer, then moves that buffer into the history list |
| [[\buf-append | buf_append]] |
| [[\buf-copy | buf_copy]] |
| [[\buf-edit | buf_edit]] |
| [[\buf-load | buf_load]] |
| [[\history | history]] |
| [[\reset | reset]] |
Many of these commands take argument that is a form of the history recall syntax. For example, to load a file into the current buffer, you can do:
1> \buf-load myquery.sql !.
1> select * from sysibm.systables
2>
or, if you wanted to copy the contents of that file to the last SQL buffer:
1> \buf-load myquery.sql !-1
1>
As usual, use \help command to get detailed help and examples on these commands.
On supported platforms jsqsh provides the ability to edit the current line of text and to scroll up and down through previously entered lines of text using arrow keys. This facility is provided by a library called JLine2. JLine emulates many of the features of the popular GNU Readline library (this library provides command line editing for most linux shells).
I won't cover much about JLine2 since you should read about it from the project web site, however there are a few things that you should be aware of:
-
The library will automatically read your .inputrc file if it is present, so it should honor the style of editing that you have defined for your other shells.
-
You can change the editing mode from within jsqsh using the editing_mode variable:
1> \set editing_mode=vi
The Getting Started page covers the process of setting up a named connection using the JSqsh setup wizard. This section covers additional details about managing multiple concurrent connections (called sessions) and how to connect to a data source without the need to create a named connection.
When you establish a connection in jsqsh, you have started a session, for example:
1> \connect db2
Password: *******
[mydb2.mydomain.com][gray] 1>
I am now working in a session with the "db2" named connection. However, jsqsh supports the ability to work with multiple concurrent sessions using the -n (or --new-session) flag to \connect, like so:
1> \connect -n bigsql
Password: *******
Current session: 2 (jdbc:bigsql://mybigsql.mydomain.com:7052/default)
[mybigsql.mydomain.com][gray] 1>
And you can view your currently active sessions with the \session command:
[mybigsql.mydomain.com][gray] 1> \session
+-----+----------+--------------------------------------------------+
| Id | Username | URL |
+-----+----------+--------------------------------------------------+
| 1 | gray | jdbc:db2://mydb2.mydomain.com:51000/MYDB |
| * 2 | gray | jdbc:bigsql://mybigsql.mydomain.com:7052/default |
+-----+----------+--------------------------------------------------+
And you can switch between sessions using the \session command as well
[mybigsql.mydomain.com][gray] 1> \session 1
Current session: 1 (jdbc:db2://mydb2.mydomain.com:51000/MYDB)
and you can end a session (closing the connection) without quitting jsqsh with the \end command:
[mydb2.mydomain.com][gray] 1> \end
Current session: 2 (jdbc:bigsql://mybigsql.mydomain.com:7052/default)
[mybigsql.mydomain.com][gray] 1>
The Getting Started section covers in detail how to set up a named connection in jsqsh, which is a process that, once complete, makes it trivial to connect to a data source, however it does not work well in all situations, for example if you wanted to write a script that can be run anywhere with jsqsh, you do not want to require that connections be pre-defined in order to run the script.
JSqsh also allows connections to be established by providing all connection information directly via command line options--these are options that can be provided either when launching jsqsh or via the \connect command. Before I got into these command line options, though, it is important to understand how JDBC URL's work in jsqsh.
If you run the \drivers command, it will show a list of available JDBC drivers and their URL's:
1> \drivers
+-----------------------+----------+----------------------------------------------------+----------------------------------------------+
| Target | Name | URL | Class |
+-----------------------+----------+----------------------------------------------------+----------------------------------------------+
| IBM Big SQL v1 | bigsql | jdbc:bigsql://${server}:${port}#if($db)/${db}#end | com.ibm.biginsights.bigsql.jdbc.BigSQLDriver |
| * IBM DB2 | db2 | jdbc:db2://${server}:${port}/${db} | com.ibm.db2.jcc.DB2Driver |
| Apache Derby Server | derby | jdbc:derby://${server}:${port}/${db} | org.apache.derby.jdbc.ClientDriver |
| * Apache Hive | hive2 | jdbc:hive2://${server}:${port}#if($db)/${db}#end | org.apache.hive.jdbc.HiveDriver |
| MS SQL Server | mssql | jdbc:microsoft:sqlserver://${server}:${port} | com.microsoft.jdbc.sqlserver.SQLServerDriver |
| * MySQL | mysql | jdbc:mysql://${server}:${port}#if($db)/${db}#end | com.mysql.jdbc.Driver |
| Oracle | oracle | jdbc:oracle:thin:@${server}:${port}:${SID} | oracle.jdbc.OracleDriver |
...
You'll note that JDBC URL's refer to certain variable names, for example the "db2" driver is defined like so:
jdbc:db2://${server}:${port}/${db}
These variables need to be provided in order to establish a connection, and it is via command line options that they can be set. The following table lists command line options that are used for connecting and what variables they correspond to:
| Short Option | Long Option | Variable | Description |
|---|---|---|---|
| -d | --driver | ${driver} | The logical name of the JDBC driver to use |
| -S | --server | ${server} | The hostname, ip address, or identifier for the server to connect to |
| -p | --port | ${port} | The that the server is listening on |
| -U | --user | ${user} | Provides the username to connect with. If not provided you will be prompted for it |
| -P | --password | ${password} | Provides the password to connect with. If not provided you will be prompted for it |
| -D | --database | ${db} | Specifies the name of the database to connect to |
| -s | --SID | ${SID} | Specifies the SID to use (typically used for Oracle) |
| -w | --domain | ${domain} | The authentication domain to use |
So, since the DB2 driver, needs the ${server}, ${port}, and ${db} to connect with, I can connect directly via the jsqsh command line with:
$ jsqsh -d db2 -U gray -S mydb2.mydomain.com -p 51000 -D MYDB
or, using long options:
$ jsqsh --driver db2 --user gray --server mydb2.mydomain.com --port 51000 -database MYDB
Similarly, you may also provide the same arguments to the \connect command when already at the jsqsh prompt:
1> \connect -d db2 -U gray -S mydb2.mydomain.com -p 51000 -D MYDB
In addition, you can use command line options to override values provided by a named connection. For example, the following:
$ jsqsh --database OTHERDB mydb2
will use the "mydb2" named connection to connect, but it will ignore whatever database was defined for the named connection and instead use OTHERDB.
The setup wizard is generally the preferred way to work with named connections, but because it is easier than remembering a whole pile of command line options. But the options are there, none the less!
Picking up from the example in the previous section, once you have determined the correct set of options necessary to connect to your favorite database, you can then add that as a named connection with the --add option to \connect, like so:
1> \connect -d db2 -U gray -S mydb2.mydomain.com -p 51000 -D MYDB --add mydb2
1>
Note that with --add a connection will not be established, just saved away in your named connections. You can view the current set of named connections with the --list option:
1> \connect --list
+-----------+--------+----------------------------+--------+-----------+----------+----------+--------+--------+--------+------------+
| Name | Driver | Server | Port | SID | Username | Password | Domain | Class | URL | Properties |
+-----------+--------+----------------------------+--------+-----------+----------+----------+--------+--------+--------+------------+
| bigsql | bigsql | [NULL] | 7052 | [NULL] | gray | ******* | [NULL] | [NULL] | [NULL] | [NULL] |
| db2 | db2 | localhost | 50003 | [NULL] | db2inst1 | ******* | [NULL] | [NULL] | [NULL] | [NULL] |
| deepdb | db2 | larry.fred.com | [NULL] | [NULL] | db2inst1 | ******* | [NULL] | [NULL] | [NULL] | [NULL] |
| oracle | oracle | ora.cle.com | [NULL] | O12c2UTF8 | DBCERT | ******* | [NULL] | [NULL] | [NULL] | [NULL] |
+-----------+--------+----------------------------+--------+-----------+----------+----------+--------+--------+--------+------------+
Similarly, the \connect command has options to --remove and --update existing connections. See \help \connect for details.
By and large jsqsh should be usable without having to resort to peeking into its seedy underbelly and poking around into configurations files...but, sometimes it may be necessary or possibly easier.
All configuration files listed below live in:
$HOME/.jsqsh
(or your whatever your home directory is called on Windows).
The $HOME/.jsqsh/sqshrc is read each time jsqsh is started. The contents of this file are process just as if you had typed them in at the jsqsh prompt, so you can set any jsqsh variable, or execute any command from this file. The typical use case for this file is to set jsqsh configuration variables to your liking. For example, here's the contents of my file:
## The editor to use during \buf-edit, "vi", or "emacs" commands
\set EDITOR=/usr/bin/vi
## The character to use to execute commands
\set terminator=';'
## Allow variable expansion within SQL statements
\set expand=true
## Show a visual timer during query execution
\set timer=true
The double hash (##) is jsqsh's form of comment. Any line starting with this is ignored
The $HOME/.jsqsh/drivers.xml file contains customized driver definitions, after jsqsh launches and loads its internal database of driver definitions it then reads the contents of this file, which may define new drivers or override jsqsh's own definition of the driver.
When you edit a driver with the jsqsh setup wizard (jsqsh --setup), then you will find the new definition of the driver in this file. Unfortunately, I don't yet have proper documentation for the format of this file, so your best bet is to use the setup wizard to at least populate the file before editing.
The $HOME/.jsqsh/connections.xml file contains your named connection definitions. This file is edited each time you run the jsqsh setup wizard, or use one of the connection management options to the \connect command (as discussed here).
As with drivers.xml, the format of this file isn't yet properly documented, so it is recommended that you use the setup wizard to at least create an initial entry in this file, then you can edit by hand freely.