Java SQL Shell (JSqsh)

You can use IBM® Big SQL from any client tool that uses a JDBC or ODBC driver. One of those clients is the Java SQL Shell (JSqsh) command interface. When you run the jsqsh command from the command line, you open a command shell. You can type specific Db2 Big SQL commands or statements into this shell and view output from Db2 Big SQL queries.

The JSqsh interface is a generic JDBC client. You can use JSqsh with any data source for which a JDBC driver is available

More JSqsh documentation

For JSqsh setup information see Accessing Db2 Big SQL from the JSqsh client.

To learn more about the syntax and use of the JSqsh command, use the \help command. Additional online documentation is available at To get more help on a particular topic, type the \help with the topic of interest, such as in the following examples:
\help commands
\help vars

Running JSqsh

JSqsh is installed in /usr/ibmpacks/common-utils/current/jsqsh/bin. Change to that directory and type ./jsqsh to open the JSqsh shell:
cd /usr/ibmpacks/common-utils/current/jsqsh/bin
You can then run any JSqsh commands from the prompt.
Note: After performing a Db2 Big SQL or JSqsh upgrade, the first time you start the JSqsh client do not pass any parameters, such as a connection name. This allows the JSqsh configuration files to change format.

Connection setup

To use the JSqsh command shell, you can use the default connections or define and test a connection to the Db2 Big SQL server.
  1. The first time that you open the JSqsh command shell, a configuration wizard is started. When you are at the Jsqsh command prompt, type \drivers to determine the available drivers.
    1. On the driver selection screen, select the Db2 Big SQL instance that you want to run
      Note: Db2 Big SQL is designated as DB2 in this example:
           Name           Target               Class
      - ------- ------------------- --------------------------------------------
      2 *db2    IBM Data Server(DB2
    2. Verify the port, server, and user name. Run \setup and click C to define a password for the connection. The username must have database administration privileges, or must be granted those privileges by the Db2 Big SQL administrator.
    3. Test the connection to the Db2 Big SQL server.
    4. Save and name this connection.
  2. Generally, you can access JSqsh from /usr/ibmpacks/common-utils/current/jsqsh/bin with the following command:
    ./jsqsh --driver=db2 --user=<username>
  3. Open the saved configuration wizard any time by typing \setup while in the command interface, or ./jsqsh --setup when you open the command interface.
  4. Specify the following connection name in the JSqsh command shell to establish a connection:
    ./jsqsh name
  5. Use the \connect command when you are already inside the JSQSH shell to establish a connection at the JSqsh prompt:
    \connect name

Commands and queries

At the JSqsh command prompt, you can run JSqsh commands or database server commands. JSqsh commands usually begin with a backslash (\) character.

JSqsh commands accept command-line arguments and allow for common shell activities, such as I/O redirection and pipes.

For example, consider this set of commands:
1> select * from t1
2> where c1 > 10
3> \go -m csv > /tmp/t1.csv

Because the commands do not begin with a backslash character, the first two commands are assumed to be SQL statements, and are sent to the Db2 Big SQL server.

The \go command sends the statements to run on the server. The \go command has a built-in alias so that you can omit the backslash. Additionally, you can specify a trailing semicolon to indicate that you want to run a statement, for example:
1> select * from t1
2> where c1 > 10;

The --style option in the \go command indicates that the display shows comma-separated values (CSV). The \go form is most useful if you provide additional arguments to affect how the query is run. Changing the display style is an example of this feature.

The redirection operator (>) specifies that the results of the command are sent to a file called /tmp/t1.csv.

A set of frequently run commands does not require the leading backslash. Any JSqsh command can be aliased to another name (without a leading backslash, if you choose), by using the \alias command. For example, if you want to be able to type bye to leave the JSqsh shell, you establish that word as the alias for the \quit command:
\alias bye='\quit'
You can run a script that contains one or more SQL statements. For example, assume that you have a file called mySQL.sql. That file contains these statements:

select tabschema, tabname from syscat.tables fetch first 5 rows only;
select tabschema, colname, colno, typename, length from syscat.columns fetch first 10 rows only;
You can start JSqsh and run the script at the same time with this command:
/usr/ibmpacks/common-utils/current/jsqsh/bin/jsqsh bigsql < /home/bigsql/mySQL.sql
The redirection operator specifies to JSqsh to get the commands from the file located in the /home/bigsql directory, and then run the statements within the file.

Command and query edit

The JSqsh command shell uses the JLine2 library, which allows you to edit previously entered commands and queries. You use the command-line edit features to move the arrow keys and to edit the command or query on the current line.

The JLine2 library provides the same key bindings (vi and emacs) as the GNU Readline library. In addition, it attempts to apply any custom key maps that you created in a GNU Readline configuration file, (.inputrc) in the local file system $HOME/ directory.

In addition to individual line editing, the JSqsh command shell remembers the 50 most recently run statements, which you can view by using the \history command:
1> \history
(1) use tpch;
(2) select count(*) from lineitem
Previously run statements are prefixed with a number in parentheses. You use this number to recall that query by using the JSqsh recall operator (!), for example:
1> !2
1> select count(*) from lineitem
The ! recall operator has the following behavior:
Recalls the previously run statement.
Recalls the fifth query from history.
Recalls the query from two prior runs.
You can also edit queries that span multiple lines by using the \buf-edit command, which pulls the current query into an external editor, for example:
1> select id, count(*)
2> from t1, t2
3> where t1.c1 = t2.c2
4> \buf-edit

The query is opened in an external editor (/usr/bin/vi by default. However, you can specify a different editor on the environment variable $EDITOR). When you close the editor, the edited query is entered at the JSqsh command shell prompt.

The JSqsh command shell provides built-in aliases, vi and emacs, for the \buf-edit command. The following commands, for example, open the query in the vi editor:
1> select id, count(*)
2> from t1, t2
3> where t1.c1 = t2.c2
4> vi

Configuration variables

You can use the \set command to list or define values for a number of configuration variables, for example:
1> \set
If you want to redefine the prompt in the command shell, you run the following command with the prompt option:
1> \set prompt='foo $lineno> '
foo 1> 
Every JSqsh configuration variable has built-in help available:
1> \help prompt

If you want to permanently set a specific variable, you can do so by editing your $HOME/.jsqsh/sqshrc file and including the appropriate \set command in it.