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:
- On Linux, install it with “rpm”, “yum”, or “apt-get”.
- On AIX, download and install it from the AIX Toolbox for Open Source Software.
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.
