Over the last weekend, the annual
Tuning World Bodensee was guest at the
Messe Friedrichshafen (exhibition center and fair grounds). More than 100,000 people interested in
car tuning traveled to
Friedrichshafen. "Tuning" can be trying to get more performance out of engine or to customize the car to the personal style. With DB2, you can customize the
command line processor to your personal style and preferences. Let's have a look at the available tuning options.
All the recent versions of DB2 provide three environment variables to tune the editing experience in the interactive DB2 CLP:
DB2_CLP_EDITOR, DB2_HIST_SIZE, and DB2_CLPPROMPT. The first variable, DB2_CLP_EDITOR, is used to specify an external editor to be used for editing SQL statements. On my Linux system, I did the following:
export DB2_CLP_EDITOR=gedit
Now you can edit previous statements using the
EDIT command. "EDIT 1" would call the editor with the first statement in the command history, "E 1" would do the same. To know which statements are available, use the
HISTORY command or its short version "H". The maximum number of available commands is determined by the variable DB2_HIST_SIZE. It accepts numbers from 1 to 500.
export DB2_HIST_SIZE=100
To reduce the number of statements listed with the HISTORY commands, you can limit it: "H 10" would return the last 10 statements in history, "H R 5" would return the last five in reverse order. Instead of the option "R" you could also use the full word "REVERSE", e.g., "H REVERSE" or "HISTORY REVERSE". Editing commands is fun, but actually executing them is probably why they were edited. To execute a specific statement from the history, you can utilize
RUNCMD. The short version is just "R" and a valid parameter would be the number corresponding to a "historic" statement.
Both RUNCMD and EDIT, if not invoked with a number, will pick the newest statement in history. Both also accept negative numbers with "-1" being the most recent statement.
What is left is to "decorate" the command line processor in your personal style.
DB2_CLPPROMPT is used to modify the command prompt. It accepts different tokens and most characters. Here is my version which prints the current database name followed by "=> ":
export DB2_CLPPROMPT="DB: %d => "
Here is a small sample session with the bew prompt:
DB: => connect to hltest
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = HLOESER
Local database alias = HLTEST
DB: HLTEST => values 'Good Morning'
1
------------
Good Morning
1 record(s) selected.
DB: HLTEST => e -1
DB: HLTEST => values 'That''s it, good bye!'
Do you want to execute the above command ? (y/n) y
1
--------------------
That's it, good bye!
1 record(s) selected.
DB: HLTEST => h r
4 h r
3 values 'That''s it, good bye!'
2 values 'Good Morning'
1 connect to hltest
DB: HLTEST =>
