|Tuning World Bodensee via Wikipedia|
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:
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.
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 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
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 =>