Abstract

The Db2 LUW database product uses the Db2 CLP to communicate with the Db2 engine at the OS level. Particularly, when working on the database server most administrators frequently make use of the command-line tool. However, when running SQL queries, the output is often larger than the terminal width, which makes it difficult to properly interpret the data returned. An example is the following:

select * from syscat.tables

Ways to overcome this problem are:

• Use a subset of columns being queried, e.g. instead of the full table width, you run the following SQL statement:
  select tabschema, tabname, card from syscat.tables

• Truncate the output of large character fields, e.g., by use of function SUBSTR():
  select substr(tabschema,1,8), substr(tabname,1,32), card
  from syscat.tables

In the end, all these methods are cumbersome, since you need to know the column names and must arduously tailor SQL statements to display result sets in a somewhat readable way. Instead, imagine now we can reap the benefit from fixed header rows and columns and from scrollable fields, as in the example below:

How can we achieve this?

The answer to this question is given by the spreadsheet calculator sc, which is briefly described below. Apart from addressing the items listed above, sc provides a lot of features like searching, sorting, filtering, i.e., anything you would like to do in a spreadsheet.

The principle is simple: just convert the output of Db2 CLP with an awk script into a format understood by sc. For convenience, the shell script wrapper clpsc.ksh eases the handling. The scripts are published in the clpsc project.

How to set it up?

Installing sc

Before you start with the clpsc scripts, you should have installed sc. I recommend using the version on GitHub that is currently released. Either you clone the source tree, or you download the sources as a zip and unpack them locally. The README provides a short description of how to install sc. To build sc, run the following commands from the source directory:

./configure
make

By default, the installation goes into /usr/local/bin, which is usually owned by the root user. Therefore, the last step should be run as root:

make install

Prerequisites for the build process:

• You need a parser generator during the build process. If you do not have yacc (the default), use bison. You can change your preference in the Makefile after having run configure.

• You must have installed the libraries and the development package of ncurses.

Installing clpsc

Similar to sc, you need to get the sources from https://github.com/IBM/clpsc. Just unpack the sources, go into the root of the source directory, and run

./installation.ksh

This places the configuration, including awk script and macros, in the .clpsc directory in the home directory of the user running the installation. The wrapper script, clpsc.ksh, is placed in the bin directory under $HOME.

If you run the installation with root privileges, the wrapper script is placed in /usr/local/bin, and the other data is placed below /opt/CLPSC. At the first execution of clpsc.ksh as an ordinary user, it will create the configuration in the subdirectory .clpsc of the home directory of the user.

Structure of the SQL output of the Db2 CLP

The output of an SQL query may look like this:

The output starts with the field names of the result set, followed by a line that visualises field lengths with dashes. The Db2 CLP displays the data using their maximum display field length. LOB data is truncated at 8192 bytes length. 

Note that character data is displayed left-aligned, while number fields are displayed right-justified.

The output ends with a line indicating the number of rows returned.

Create an sc document

Written in the late 1970s, sc is a feature-rich program that includes many functionalities found in modern graphical spreadsheet calculators. It runs in a terminal window and organizes the screen using cells with an addressing scheme similar to actual office tools.

In the top left corner, you can see the current cursor location and the details of the current cell.

Basic functionalities of sc

The features of sc include the availability of functions and the use of colours. In the current cell, you can enter a number (using command “=”), a string (left justified using “<”, right justified using “>” or centred using “\”). Use “x” to delete the content of the current cell.

You can also use functions. In the screenshot above, field C6 contains the sum of the fields C2 up to C5 using the command:

=@sum(c2:c5)

In the top left corner of the terminal window, you can find all information about the current cell:

• Cell address

• Formatting information: length, scale, formatting type (0 for fixed point)

• Value of the cell or formula

As an example, to enter a value into the current cell as left-justified string, simply type:

< .

The cursor will jump to the top line, where you then can enter the value.

You can use vi-style editing. Press ESC to get into vi command mode, and move through the string using ‘h’ and ‘l’. With another ESC you discard your changes and get back into the spreadsheet. With ‘a’ or ‘i’ you get back from command mode into insert mode.

You also have a ‘go to’ and a search functionality with key ‘g’. To go to a cell address like B1, type:

g b1

To search for a numerical value or for strings, type e.g.:

g 3.10
g "example"

So, for numerical values, specify the search term as is. To search for string content, enclose the search term in double quotes. You can search for substrings of string cell content.

Another useful function allows you to change the width of a column. Place the cursor on the column that you want to modify, and type:

f

You can then make the column wider or narrower using the arrow keys ◄ ►. When finished, press the RETURN key.

sc limitations

Since we talk about capabilities, we should also look at the limitations. The most essential are the following:

• In sc, the row numbers are given as 2-byte signed integers. Therefore, the maximum number of rows is 32767.

• Columns are labelled by letters. sc uses labels with one and two letters for a column. This gives you a maximum number of 702 columns.

The clpsc tool uses a maximum of 32765 rows and 700 columns. All data beyond this range is simply cut off.

How does clpsc.ksh work?

The Db2 CLP output is structured in a way that allows you to easily extract field names and field lengths. With this information, the data of the result set of an SQL query can be easily mapped to the corresponding fields.

The clpsc.ksh script uses such an approach and converts the output of the Db2 CLP into a format that is understood by sc.

As an example, if you have configured a default Db2 instance and database, you can run the following query:

$ clpsc.ksh "select * from syscat.tables where tabschema like 'SYS%'"

You can set the default via the Db2 environment variables DB2INSTANCE and DB2DBDFT. When you submit a query using clpsc.ksh, the result set is displayed as an sc document:

There are several parts in the output with different colours that deserve some explanation.

The data part of the screen is split into the following areas:

• SQL statement

• Header row (field names) that scroll only horizontally

• Fixed columns that scroll only vertically

• Non-fixed area that can be scrolled horizontally and vertically

The columns are represented in alternating colours with different colour sets for header area, fixed, and non-fixed columns.

Numeric values are displayed right-justified, while clpsc.ksh displays character content left-justified. As a result, data in adjacent columns sometimes appear without separating spaces. The colouring scheme lets you easily distinguish between the different content in such a case as shown below:

In this screenshot, the row numbers and column identifiers indicate that the data in the non-fixed area was scrolled both in horizontal and vertical directions while the header and the first columns remain fixed.

Configuration

After installation, the tool works without further configuration.

The configuration settings are located in directory .clpsc under your home directory. You will find a file, clpscrc, that contains the tool’s settings. Note that updates to the clpsc package overwrite this file. Any change you have made will be lost. You can always define your own settings in the file clpscUser. The template clpscUser.template contains example settings.

You can define an alternate configuration directory with the environment variable CLPSCDIR. In this case, you need to copy the configuration directory .clpsc to your ${CLPSCDIR}.

To display the SQL statement text with the result set, use the following configuration which is the default:

clpopts="-v"

Configuring the awk converter

To configure how the data is displayed, you can use the parameter convopts. This parameter contains the configuration of the awk conversion script. You can define the following default values:

Use the following syntax for the configuration of parameter convopts:

convopts=parameter1=value1[:parameter2=value2[..]]

I recommend using the following pattern:

convopts=${convopts}:parameter1=value1[:parameter2=value2[..]]

Configuration parameters of the awk converter

• maxlen is an important parameter. The levels of sc in Linux distributions allow for a field length of ~1000 characters. If you configure maxlen higher than that, you easily end up with a hanging sc or crash with a signal. The sc sources from GitHub have a maximum length of 10000. Since the Db2 CLP displays data up to a maximum length of 8192, you can configure 10000 characters and get the result set as displayed by the Db2 CLP.

• The function keys, configured using fkey<n>, can be set to macros that come with clpsc. See below for an example configuration. Section F key macros below gives you more details about this feature.

• When the result set is displayed, each column is displayed with a width matching the largest entry in the column. However, there is a maximum that is configured by parameter width. Of course, you can change the width of any column using the key f and the arrow keys as explained above. The default value is 30.

For example, you can define your own settings in a user configuration file clpscUser like this:

fixedColours="@black;@white|@white;@blue"
convopts="${convopts}:freezeColNum=2:maxlen=10000:fixedColours=${fixedColours}:fkey7=runSQL.ksh:fkey8=changeParams.ksh:fkey5=fixedCol.ksh:fkey6=setColours.ksh"

Environment and command-line options

The clpsc.ksh script has a couple of command-line options and environment variables that help in using it. In fact, the script does not require you to set up a valid Db2 environment. It is sufficient if you have the following:

• A local Db2 instance

• Access to the Db2 profiles in the sqllib directory of the local Db2 instance

• Access permission to the Db2 database being queried

Note that clpsc.ksh can display Db2 CLP output in any environment (i.e. without any Db2 software installed). See option if described later on in this section.

The script uses the environment variables DB2INSTANCE and DB2DBDFT as default instance and database name. These variables are superseded by the environment variables CLPSCDB2INST and CLPSCDBNAME, respectively. The default schema is the current OS user. You can define another default using the environment variable CLPSCSCHEMA.

You can display available command-line options, including default information, using the option help:

For example, you can specify the Db2 instance and the database name via command line like this:

$ clpsc.ksh dbinstance=<Db2 instance name> dbname=<database name> "select * from syscat.tables where tabschema like 'SYS%'"

With option if=<file>, you can specify an input file. For example, if you save the result set of an SQL statement submitted via Db2 CLP into a local file, say db2clp.out, you can view the result set using the following command line:

$ clpsc.ksh if=db2clp.out

Using this option does not require access to a Db2 CLP.

If you use option if=<file> to read data from a file, you lose the strength of the SQL language. However, option filter lets you limit the displayed data using regular expressions. The syntax to filter on a single column is:

filter=<column name>:{+-}<pattern>[:{+-}<pattern>[...]]

Each filter condition starts with + or -, indicating the pattern results are to be included or excluded from the result set. For filtering on several columns, concatenate the filter conditions with a colon as a separator:

filter=<filter condition>[:<filter condition>[:...]]

You can use, for example, the following SQL statement:

select * from syscat.tables where tabschema like 'SYS%' and tabschema <> 'SYSSTAT' and year(create_time) = '2025'

This statement displays all table entries from SYSCAT.TABLES with a table schema starting with SYS, but not table schema SYSSTAT. In addition, the table must have a creation timestamp of the year 2025. Using filter conditions, the syntax may look like this:

filter='TABSCHEMA:+SYS.*:-SYSCAT:CREATE_TIME:+2025.*'

Option fcn is useful to define a number of fixed columns other than the default. However, you can also adjust this number dynamically using an F key, as described below.

F key macros

With sc, you can use macros that allow you to use a limited set of sc commands. In addition, the macros can be bound to the F keys of your keyboard for easy use while moving around in a document. The clpsc.ksh script comes with a set of macros that you can use for several purposes. See the currently available functionalities below together with the macro names and the default F key configured:

For example, to change the set of fixed columns, go to a column that you would like to be the first non-fixed column, and press the F key configured for macro fixedCol.ksh (F9 by default). The visible effect is the change of the colours that reflects the new set of fixed columns. As shown above, you can change the F key assignments for the macros. Ensure that your settings comply with your environment. As an example, your terminal emulation might use F1 to display terminal help information. Also, F1 is the default key of sc to display the manual pages.

The picture below shows that after having pressed the F key, the cursor is in the first non-fixed column. In the second line, you see the current default database connection details:

However, the macro fixedCol.ksh only performs its task if the document is not scrolled in the horizontal direction. If so, you will get the following error:

The macro setColours.ksh refreshes the alternating colour scheme. This is useful if columns have been deleted from or inserted into the spreadsheet document.

The macro runSQL.ksh allows you to run a new SQL statement and to display its result set.

Finally, the macro changeParams.ksh allows you to change parameters and to turn on/off the macro trace. Currently, the parameters that can be changed this way are the database name and the database schema being used. Trace data go to the Logs subdirectory of the configuration directory.

Summary

For environments running Db2 LUW, the clpsc script is a convenient tool to work with Db2 CLP output. You can scroll through the data while you always have the field names (header) and a set of fixed columns displayed that you can change dynamically. You have all functionalities of the underlying spreadsheet calculator sc to search through the data or to modify the output, and, for example, add a column that displays the quotient of two existing data columns.

You can use F keys to run another SQL statement, change the name of the database being queried, and/or the default database schema.

Also, you can save the output of the Db2 CLP in a file and display it using clpsc.ksh in a remote environment, even if the Db2 software is not installed there.

This makes the clpsc.ksh script a useful helper tool when working with SQL result sets that are much larger than your terminal size.