Rebecca Bond discusses a few of the options available in IBM DB2's new CLPPLUS command line processor that you can use to connect to databases and to define, edit, and run statements, scripts, and commands.
Even when it doesnt involve security, I enjoy playing games
and solving puzzles, especially if they involve DB2. Todays game? What can
we learn about DB2s new CLPPLUS command line processor in two hours or less?
This is a switch. Im going to begin at the end of this
contest and then walk you through the game play to show you the winning
approach. If youre one of those people who likes to read the last chapter
first, this article is for you.
The Win at the Finish:
The CLPPLUS command processor, which is a newer, more
intense DB2 CLP command line processor, behaves like I do when I have exceeded
my 18 cup a day coffee quota. If this thing was running against me in a half
marathon, Id have to have an hour head start to beat it to the finish line. If
you use the command line at all, there is much to this game that will assure a
The Game Begins:
To begin the game, I have installed DB2 9.7 Enterprise
Server Edition and upgraded to Fixpack 1 to take advantage of some newer
features. I dont like to play solitaire without a full deck, and I dont like
to learn software that isnt the most current.
IBMs site says that the Command Line Processor Plus (CLPPlus) provides a command-line user interface that you can use to connect to databases and to define, edit, and run statements, scripts, and commands.
Sounds good so far, but where is the winning exploit (because all games have
some strategy element, right?). Wait, I dont want to cheat. Dont tell me if
you know this one.
Ill start by ... well...starting. Since Im working on a
Windows platform today, I can just use the menu option:
Or, I can open a Windows DOS command and type clpplus at the prompt. If you
have any trouble starting your CLPPLUS sessions, refer to the troubleshooting tips
DB2 Database for Linux, UNIX, and Windows Information Center. Search on the
topic Troubleshooting CLPPlus Issues.
Now Ill connect to my sample database. There is more than
one way to do this, but Ill just take the simple approach by typing CONNECT
and following the prompts for now:
Notice something? This is a good game exploit. There was
no catalog database statement and since I was using the sample database and the
default port, I only had to enter my id and password and I was automatically
connected to the database. When Im ready to disconnect from the database, all
it takes is a DISCONNECT keyword.
If I want to run an OS command, all I have to do is precede
it with the keyword HOST. Maybe I want to see what files are in my directory
so I would type:
SQL> HOST dir
To do the same thing on UNIX, Id type
SQL> host ls
Now I need to know more about the options, so Ill ask for
Notice that if I want, I can just ask for Help on a specific
topic, or I can type HELP INDEX and
get the full list. Thats a nice feature until I get more comfortable with the
commands. I wish I had that option when I was first learning chess.
Being a DBA, Im curious about what DB2 information I can
gather from CLPPLUS. The list is:
SQL> GET DATABASE CONFIGURATION
SQL> GET DATABASE MANAGER CONFIGURATION
SQL> UPDATE DATABASE CONFIGURATION
SQL> UPDATE DATABASE MANAGER CONFIGURATION
SQL> RESET DATABASE CONFIGURATION
SQL> RESET DATABASE MANAGER CONFIGURATION
When I use these commands, I discover that the ones that
have DEFERRED VALUES such as DBM CFG and DB CFG display those as well. The
shortcuts that I am so used to (DBM CFG for Database Manager Configuration)
also work and the commands arent case sensitive so get dbm cfg works too.
From what I read in the documentation, there are some great
logging and tracing features. I wanted to log my client session so I tried:
SQL> Set logmode clpplus
When I finished logging and wanted to disable it, I entered:
SQL> Set logmode none
The clpplus.log file was in my current directory and
contained some very detailed information about my activity when the logmode was
set. I can see this being an extremely useful feature for debugging purposes. However,
what I really wanted was to log my CLPPLUS session from an input/output
viewpoint. That feature is available as SPOOL.
To turn on logging of my CLPPLUS session, Ill type:
SQL> SPOOL mycmds
With this on and a named file parameter provided, CLPPLUS will
log my current session commands to a file in my current directory called
mycmds. That is exactly what I wanted.
Thinking about setting the logmode made me think about SET,
so I did a HELP SET and pulled back a long list of options for SET, along with
relevant descriptions. The SET CLPPLUS command works only at the session
level, so once I SET, the value only lasts temporarily while Im in that
One of the advertised benefits of using CLPPLUS makes it
sound like a good strategic maneuver that DBAs can use to easily format screen output.
Im not a fan of having to use CHAR or SUBSTR to parse my output. To try the
formatting features out, Im going to create a table for testing purposes. To
enhance the game play, Im going to put a typo in my syntax so that I can test
out what happens when I use the CLPPLUS EDIT
SQL> create table locksmith.text (col1 char(80), col2 char(80))"
ERROR near line 1: (this didnt parse correctly, that double quote has to go)
SQL0010N The string constant beginning with """ does not have an ending string delimiter.
[The slash symbol (/) provides the completion indicator to the editor]
But this is easy enough to fix.
At this point, my default editor, NOTEPAD, pops up with the
syntax so that I can edit it and remove the offending double quote and save the
Ive fixed my typo and saved my file. Now, all I have to is
type RUN to create the table.
SQL > run
Ill load some data for testing purposes, but before I do,
Ill set AUTOCOMMIT on for this session (the default is off, except for DDL
statements which are automatically committed.).
SQL> set autocommit on
(While you were in the kitchen getting popcorn, I went ahead
and took my turn. I inserted two rows into our locksmith.text table.)
Now, I want to play with the formatting options.
First, Ill look at
WRAP. Setting WRAP off seems to allow truncation of a line in my output:
With WRAP on,
Better, but I dont like the headings, so Im going to add
some more formatting.
This looks good, but there are so many more options
available. Unfortunately, all games at some point have to end and we must now
declare the match a win, lose or draw and move on. I call a tie.
But before we put all the game pieces away to play another
day, lets look at the rule book to discover some game play options we didnt
get to cover. You can look forward to exploring these the next time you want
to play the new game Ive now dubbed CLPPLUS SMART DBA CHALLENGE
Some other notable CLPPLUS Game Goodies are:
A buffer can be used to store scripts, script fragments, SQL statements,
SQL PL statements, or PL/SQL statements for editing and then execution. Text in
the buffer can be listed, printed, edited, or run as a batch script.
A comprehensive set of processor commands can be used to define
variables and strings that can be stored in the buffer.
Ability to store buffers or buffer output to a file.
Support for executing system-defined routines.
Since the game is almost finished and its time to go home,
I will be kind to my database and issue the DISCONNECT command which closes my
database connection, but keeps me resident in the CLPPLUS session. When Im
through with all my CLPPLUS games and fun and before I head out to solve some
urgent security puzzler, Ill issue the QUIT command to close out my CLPPLUS
session until the next game.
Oh, and before our next match, for more information on how
to assure a winning game, you might want to check out the CLPPLUS video at YouTube .
[And, a big Shout Out to my friend, Serge Rielau, IBM SQL
Architect extraordinaire, for taking time from his very busy schedule to review
this article. Read his excellent article
on running Oracle applications on DB2].
Command Line Processor Plus
IBM CLPPlus commands
See All Articles by Columnist