Java SQL Shell (JSqsh)

To learn more about the syntax and use of the JSqsh command, use the \help command. Additional online documentation is available at https://github.com/scgray/jsqsh. 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

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
./jsqsh

You can then run any JSqsh commands from the prompt.

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 com.ibm.db2.jcc.DB2Driver

   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>
         --password=<user_password>

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

For example, consider this set of commands:

1> select * from t1
2> where c1 > 10
3> \go -m csv > /tmp/t1.csv

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;

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.

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

The ! recall operator has the following behavior:

!!

Recalls the previously run statement.

!5

Recalls the fifth query from history.

!-2

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

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