© 2003 International Business Machines Corporation. All rights reserved.
IBM® DB2® Universal DatabaseTM for Linux, UNIX®, and Windows® (herein referred to as DB2 UDB) gives you two methods for entering commands from its command line
interface. When using the DB2 UDB Command Line Processor (DB2 UDB CLP) in Interactive mode, you don't have to prefix your DB2 UDB commands or SQL queries with the keyword db2. If you are not familiar with the different modes in which the DB2 UDB CLP runs, check out DB2 Planning: DB2 V8.1 Family Fundamentals Certification Preparation: Part 1 of 6.
Take a look at Figure 1 in which a SELECT *... statement was entered in the DB2 UDB CLP running in Interactive mode. Do you know what database or instance this particular table (STAFF) is located in? Likely not (though you could likely guess in this particular case); however, DB2 UDB does!
Figure 1. Default CLP view does not tell you which instance you are connected to
Now look at the exact same query below in Figure 2. Can you answer my question now? Notice anything different?
Figure 2. CLP is customized to show you the instance and database you are connected to
DB2 UDB V8.1 with Fix Pack 1 (FP1) delivers a neat new feature that lets you customize the DB2 UDB CLP prompt when running in Interactive mode (for the remainder of this article, references to the DB2 UDB CLP are to this utility running in Interactive mode).
You can use this new customization feature to add your own text and variables that reflect the context of the current instance attachment and/or database connection to the display. This article will introduce you to this new customization feature.
Customizing the DB2 UDB CLP in interactive mode
DB2 V8.1 with FP1 adds the ability to have customizable DB2 UDB CLP interactive prompts. DB2 UDB CLP prompts can now reflect the context of the current instance-attachment and database-connection, along with specific character messages. Without this feature, using the DB2 CLP in interactive mode gives you the hardcoded prompts shown in Figure 3.
Figure 3. Hardcoded prompts in CLP
Before we go on: A twenty-second lesson on DB2 UDB registry variables
You can use DB2 UDB registry variables to alter your environment. These variables can be set such that they are dynamic in nature and applied to the current run-time environment without the need to be included in a system startup profile. There are some DB2 UDB registry variables that can only be set to be part of your system's startup.
DB2 UDB V8.1 provides a DB2 Profile Registry for control over your environment. In previous releases, when you changed environment levels, DB2 UDB required a reboot. With a few exceptions, DB2 UDB V8.1 environments can now be controlled exclusively by registry variables stored in the DB2 Profile Registry.
Use the db2set command to update DB2 UDB registry variables; this information is stored immediately in the profile registries. The DB2 Profile Registry applies the updated information to the DB2 UDB server instances and applications started after the changes are made.
Environment-level variables can also be assigned for a temporary session using the set command (most of the variables set this way will show up in the DB2 UDB Registry; however, some, like DB2INSTANCE, will not).
If you want an environment variable to be permanently set you should set it in your workstations startup profile, using the db2set command. The set command sets environment variables only for the duration of the command line session, while the db2set command line sets it permanently in the DB2 Profile Registry.
To view a list of all supported registry variables, enter the following command:
db2set -lr |
To change the value of a DB2 UDB registry variable, enter the following command:
db2set registry_variable_name=new_value |
To view a list of all DB2 UDB registry variables that are set, enter the following command:
db2set -all |
The output from this command should look something like Figure 4.
Figure 4. Output from db2 set -all command
Notice that environment-level variables are preceded by [e], while instance and global variables are preceded by [i] and [g], respectively.
Setting the DB2_CLPPROMPT registry variable
To customize the DB2 UDB CLP command prompt, use the new DB2 UDB registry variable: DB2_CLPPROMPT.
You can set DB2_CLPPROMPT to any text-string of length less-than or equal-to 100 characters. This customized string can contain optional tokens that will be replaced at runtime. If this registry variable is changed within a DB2 UDB CLP session, the new values will not take effect until the user exits and re-enters this processor.
In its most basic form, you can customize the DB2 UDB CLP to simply display a string of characters. Figure 5 and Figure 6 show the setting of the DB2_CLPPROMPT registry variable and the subsequent invocation of the DB2 UDB CLP.
Figure 5. Setting the command prompt registry variable
Figure 6. The result
Now, while this example is interesting, it isn't very useful. The DB2_CLPPROMPT registry variable comes with associated variables that can be used to return information to the DB2 UDB CLP with respect to the current or default instance attachment and the currently connected (or default) database.
The variables that are supported are shown in Table 1.
Table 1.Variables supported for CLP at runtime
| Variable | Value at runtime |
|---|---|
%ia | If an instance attachment exists, the authid of the current instance attachment; otherwise a null string. |
%i | If an instance attachment exists, the local alias of instance currently attached; if there isn't a local instance attachment, the value of the DB2INSTANCE or DB2INSTDEF registry variables; otherwise a null string. |
%da | If a database connection exists, the authorization ID of the current database connection; otherwise a null string. |
%d | If a database connection exists, the local alias of database currently connected; otherwise the value of the DB2DBDFT registry variable; otherwise a null string. |
%n | A newline character. |
For example, to set the DB2 UDB CLP prompt to resolve to:
(Instance <instance_name>, Database <database name>): |
enter the following command:
db2set db2_clpprompt=" (Instance:%i, Database: %d):" |
You can verify this setting in the DB2 UDB profile registry by entering the db2set all command.
Figure 7 shows you this sequence of commands, including what it looks like after you start a CLP session in the Interactive mode.
Figure 7. Setting DB2_CLPPROMPT and verifying its use
Notice in this case, I used a DB2 UDB CLP that was not in Interactive mode and this is why the Interactive mode was launched in the same window (I did this to give you a feel for the different ways you can invoke a DB2 UDB CLP).
In Figure 7, you can see that there is no value for the variable. This variable is blank because there are no database connections or default databases defined in my environment.
If you connect to a database, this variable would be updated, as shown in Figure 8.
Figure 8. Instance and database name appear on prompt
If I were to disconnect from this database, the customized string would reflect this action. If I then attach to a different instance, it would be dynamically reflected in the DB2 UDB CLP as well, as shown in Figure 9.
Figure 9. CLP dynamically updates when connecting to a new DB2 UDB instance
There are many ways to combine variables for the DB2_CLPPROMPT variable. I will leave it to you to experiment not only with the different variables that are available to you, but their respective outcomes with different environment settings.
But how about a little "end of article" test?
Based on the following:
db2set db2_clpprompt="db2 (%ia@%i, %da@%d):" |
What would you expect to see in the following situations?
- DB2INSTANCE is set to TESTING. DB2DBFT is not set. There is no attached instance attachment, and no database connections exist. Click for answer.
- DB2INSTANCE is set to DB2. DB2DBFT is set to SAMPLE. There is an instance attachment to the DB2 instance with authorization ID PAULZ. Click for answer.
- DB2INSTANCE is set to DB2. DB2DBFT is set to SAMPLE. There is only a database connection to the SAMPLE database with authorization ID PAULZ. Click for answer.
Paul C. Zikopoulos, BA, MBA, is an IDUG keynote and award-winning speaker with the IBM Global Sales Support team. He has more than seven years of experience with DB2 and has written numerous magazine articles and books about it. Paul has written articles on DB2 for many magazines and has co-authored the books: DB2 - The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 For Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at paulz_ibm at msn.com.
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
