Abstract

Informix database server products, including both Standard Engine and Dynamic Server, have for decades been delivered with a character interface tool for database exploration and SQL execution. This is referred to as “DB-Access” in the documentation, and is invoked with “dbaccess” from the operating system command line or within scripts, as described in the latest DB-Access User’s Guide. This article covers some lesser known and very useful environment variables that improve its usability, and a way to enable command history and editing when used directly as an SQL CLI.

Content

Examples in this article were produced using the provided “stores_demo” database in a Docker container from ibmcom/informix-developer-database.

DBACCESS_COLUMNS

If you enter “dbaccess” alone, the following menu is displayed which is designed for use on a traditional 24×80 terminal by default:

DBACCESS:   Query-language  Connection  Database  Table  Session  Exit
Use SQL query language.

-------------------------------------------- Press CTRL-W for Help --------


You can start a menu-driven SQL session by pressing Q for Query-language and choosing the database:

SELECT DATABASE >>
Select a database with the Arrow Keys, or enter a name, then press Return.

------------------------------------------------ Press CTRL-W for Help --------

 stores_demo@informix
 sysadmin@informix
 sysmaster@informix
 sysuser@informix
 sysutils@informix

SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit
Enter new SQL statements using SQL editor.

----------------------- stores_demo@informix --- Press CTRL-W for Help --------


Press N for new, enter your SQL, press Esc when complete, then R to run the SQL:

NEW:    ESC    = Done editing      CTRL-A = Typeover/Insert     CTRL-R = Redraw
        CTRL-X = Delete character  CTRL-D = Delete rest of line

----------------------- stores_demo@informix --- Press CTRL-W for Help --------

SELECT FIRST 9 fname, lname, company FROM customer

SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit
Run the current SQL statements.

----------------------- stores_demo@informix --- Press CTRL-W for Help --------

fname           lname           company

Ludwig          Pauli           All Sports Supplies
Carole          Sadler          Sports Spot
Philip          Currie          Phil's Sports
Anthony         Higgins         Play Ball!
Raymond         Vector          Los Altos Sports
George          Watson          Watson & Son
Charles         Ream            Athletic Supplies
Donald          Quinn           Quinn's Sports
Jane            Miller          Sport Stuff


 9 row(s) retrieved.


If the output will not fit across within 80 characters, it reformats with columns on separate lines:

NEW:    ESC    = Done editing      CTRL-A = Typeover/Insert     CTRL-R = Redraw
        CTRL-X = Delete character  CTRL-D = Delete rest of line

----------------------- stores_demo@informix --- Press CTRL-W for Help --------

SELECT FIRST 1 * FROM customer

SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit
Run the current SQL statements.

----------------------- stores_demo@informix --- Press CTRL-W for Help --------


customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2
city          Sunnyvale
state         FL
zipcode       94086
phone         408-789-8075


 1 row(s) retrieved.


The same applies when running directly in CLI mode (database and hyphen supplied):

informix@ifx:~$ dbaccess stores_demo -

Database selected.

> SELECT FIRST 1 * FROM customer;

customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2
city          Sunnyvale
state         FL
zipcode       94086
phone         408-789-8075

1 row(s) retrieved.
>


That used to be very annoying when using a larger terminal window or in a script when saving a wider document.

From version 12.10.xC9, the fix is to set undocumented environment variable DBACCESS_COLUMNS:

informix@ifx:~$ export DBACCESS_COLUMNS=160
informix@ifx:~$ dbaccess stores_demo -

Database selected.

> SELECT FIRST 1 * FROM customer;

customer_num fname           lname           company              address1             address2             city            state zipcode phone

         101 Ludwig          Pauli           All Sports Supplies  213 Erstwild Court                        Sunnyvale       FL    94086   408-789-8075

1 row(s) retrieved.

>

Command Line History and Editing

When using DB-Access in a direct CLI session (not via the menu), you can enable similar facilities to the Linux Bash shell or Windows command lines, i.e. being able to press:

  • up and down arrow keys to scroll through previous commands;
  • left and right arrows to position within a command;
  • backspace or delete to remove parts of a command;
  • characters to be inserted at the current position.

This is achieved via the Linux “rlwrap” tool. If not present:

Note that this is already built into the standard DB-Access on Windows.

You can then run a CLI DB-Access session as follows, demonstrating that a command could be corrected and rerun without having to retype it, and that the history persists between sessions:

informix@ifx:~$ rlwrap dbaccess stores_demo -

Database selected.

> SELECT FIRST 2 fname, lname FROM customers;

  206: The specified table (customers) is not in the database.
  111: ISAM error:  no record found.
Error in line 1
Near character position 42

> SELECT FIRST 2 fname, lname FROM customer;

fname           lname

Ludwig          Pauli
Carole          Sadler

2 row(s) retrieved.

>

Database closed.

informix@ifx:~$ rlwrap dbaccess stores_demo -

Database selected.

> SELECT FIRST 2 fname, lname FROM customer;

fname           lname

Ludwig          Pauli
Carole          Sadler

2 row(s) retrieved.

>

Alias to enhance dbaccess

You can go further and create an alias for “dbaccess” to apply the CLI enhancements automatically. I have this line in my “.bashrc” file which is then applied whenever a new shell starts:

informix@ifx:~$ grep dbaccess ~/.bashrc
alias dbaccess='DBACCESS_COLUMNS=$COLUMNS rlwrap dbaccess'


The variable COLUMNS is maintained by Bash and some other Unix shells, containing the current width of the terminal window. We can use that to set DBACCESS_COLUMNS appropriately when starting “dbaccess”. However, if you change the terminal window width mid-session, you would need to exit and restart “dbaccess” for it to be updated.

Environment variables can be set in-line with name=value pairs before a shell command. The alias then has “rlwrap” in front of “dbaccess” for command editing and history as previously described.

With that in place, when you run “dbaccess”, you are actually invoking the “alias”, so those two enhancements will be enabled automatically.

DBACCNOIGN

When running a script containing multiple SQL statements separated by semi-colons, should an error be encountered, the script is:

  • aborted when run in the DB-Access menu;
  • continues when run as an automated script by default.

For example:

SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit
Modify the current SQL statements using the SQL editor.

----------------------- stores_demo@informix --- Press CTRL-W for Help --------

SELECT * FROM wrong_table INTO TEMP temp_table;
SELECT * FROM temp_table;



   206: The specified table (wrong_table) is not in the database.
   111: ISAM error:  no record found.

informix@ifx:~$ dbaccess -e stores_demo wrong_table.sql

Database selected.

SELECT * FROM wrong_table INTO TEMP temp_table;

  206: The specified table (wrong_table) is not in the database.
  111: ISAM error:  no record found.
Error in line 1
Near character position 27

SELECT * FROM temp_table;

  206: The specified table (temp_table) is not in the database.
  111: ISAM error:  no record found.
Error in line 2
Near character position 24

Database closed.

informix@ifx:~$


Firstly, notice two things in the shell command above:

  • The “-e” option echoes commands as they are run.
  • A script can be automated when the database and file name are supplied.

Should you need an automated script to abort on any error, set DBACCNOIGN=1 (“DB-Access no ignoring of errors”), which is very long-standing and documented here. That changes the behaviour as follows:

informix@ifx:~$ export DBACCNOIGN=1
informix@ifx:~$ dbaccess -e stores_demo wrong_table.sql

Database selected.

SELECT * FROM wrong_table INTO TEMP temp_table;

  206: The specified table (wrong_table) is not in the database.
  111: ISAM error:  no record found.
Error in line 1
Near character position 27

Database closed.

informix@ifx:~$

Caveats

It may not be possible to install “rlwrap” on some operating systems.

There are alternatives to “dbaccess” such as SQLCMD by Jonathon Leffler from SourceForge.

Full GUIs are more productive for complex SQL statement development, the best being Server Studio.

Conclusion

DB-Access exists on any Informix system. The enhancements described in this article makes it a much better tool in many scenarios.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Contact us

If you have any questions or would like to find out more about this topic, please contact us.

About the Author: