DB2's Command Line Processor and Scripting

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Blair Adamache (adamache@us.ibm.com)IBM Toronto Lab

12 Oct 2001

This article shows you how to use the Command Line Processor (CLP) to your advantage. But, if your command of SQL is strong -- or you want to enter one quick command to verify the installation of DB2 -- the command line is ideal, and usually faster that submitting similar requests through a front-end. The CLP is a direct route to every DB2 programming interface.

© Copyright International Business Machines Corporation 2001. All Rights Reserved.

When To Use the DB2 Command Line Processor

The IBM DB2 Command Line Processor sounds pedestrian, but in fact is the interface to DB2 that optimally demonstrates the power of DB2, along with its simplicity and universality. Command lines are not pretty (in fact, DB2 on Windows gained a reputation as "powerful not pretty" when it was first released on NT before the Control Center was available). Command lines remind us of Telnet on UNIX, and the original DOS prompt from 1981. But, if your command of SQL is strong -- or you want to enter one quick command to verify the installation of DB2 -- the command line is ideal, and usually faster that submitting similar requests through a front-end like PowerBuilder or Access. The CLP is a direct route to every DB2 programming interface. It makes anything that can be called programmatically callable interatively -- or in a simple script (like the .BAT files we used to write in DOS). The durability of similar tools like SPUFI on MVS, ISQL on VM, and SQLDBSU on VSE have convinced me that the CLP is here to stay.

This article will show you how to use the Command Line Processor (abbreviated to CLP) to your advantage. When in doubt about how something works, check out the manual (the DB2 Command Reference), which has an entire chapter on using the CLP.

Back to top

Installation Verification and Problem Determination

I had used SQL Database managers on many other operating systems (DOS, VM, VSE, MVS", AS/400®) when I first installed DB2 on OS/2®, one year before it became available on NT. After a successful install, I began to read about -- and be confused by -- cataloging nodes and databases. Catalog had some new "verbified" meaning beyond the loveable SYSCAT and SYSIBM catalogs from the past. At some point I shouted at DB2: "I don't want to catalog anything, I want to run a SELECT statement just to ensure I installed correctly!" In a moment of calmness a few hours later, I learned that DB2 has no catalogs until you create a database; that you don't need to catalog nodes or databases on a local machine -- only when you're on a client connecting to a server. Creating the sample database is easy enough -- run db2sampl. And the Command Line Processor was the tool I needed to verify the installation by running:

DB2 SELECT COUNT(*) FROM SYSCAT.TABLES

Now I was cooking with gas, to paraphrase my high school history teacher. On UNIX and OS/2, you can just use an ordinary operating system prompt and prefix any DB2 command or SQL statement with "DB2." On Windows, things are a little more involved, as explained later, but you can create an operating system prompt capable of DB2 commands, SQL, and operating system commands (like dir and ren) by typing DB2CMD. You can even mix SQL, DB2 commands, and operating system parameters like |more to scroll by screen and the greater than sign (>) to pipe to a file:

DB2 select * from employee>c:\tmp\emp.out|more

Beware: symbols that have meaning to both DB2 and the operating system, like the greater than sign (>) on Windows, get interpreted by the operating system first. SELECT * FROM EMPLOYEE WHERE SALARY > 9999 will pipe error messages to a file named 9999. Delimit these special symbols; you should enter:

SELECT * FROM EMPLOYEE WHERE SALARY ">" 9999

While your users may never use the CLP to access their data, as a DBA or application programmer, the CLP is the most basic tool in the toolbox: your claw hammer for all occasions. Databases tend to be the lynchpins of critical applications, and when something goes wrong, problem determination is tough. In the mainframe/minicomputer world, the problem could be in the application or the system software, but it could always be isolated to one computer. In the client-server world, a problem could hide itself in the client system software (operating system, database client, or communications protocol), client application code, the server application code (if you're using triggers, user-defined functions or stored procedures), or somewhere else on the server. Three-tier applications bring a web server, browser, and third tier of hardware into the picture. How does the CLP help? It cuts the application out of the picture. When you have a problem, translate the failing request into simple SQL, and run the SQL from the CLP on:

1. The server
2. The client
3. The web server

Depending on where it succeeds and where it fails, you can determine if DB2 itself is failing (likely if the function fails in the same way at the server with SQL submitted through CLP as it fails in the client application), if the problem is in communications (the request fails from the client and succeeds at the server), or at the Web server (it fails only at the outermost point).

Back to top

Help

The CLP is also an interface to DB2 help. If you receive a message and don't know the appropriate steps to take, enter it at the CLP:

C:\SQLLIB>db2 ? sql0100 |more
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the
result of a query is an empty table.

Explanation: One of the following conditions is true:

• No row was found that meets the search conditions specified in an UPDATE or DELETE statement.
• The result of a SELECT statement was an empty table.
• A FETCH statement was executed when the cursor was positioned after the last row of the result table.
• The result of the SELECT used in an INSERT statement is empty.

No data was retrieved, updated, or deleted.
User Response: No action is required. Processing can
continue.
sqlcode: +100
sqlstate: 02000

You should see the same help that's in the Messages manual, including suggested actions to avert the problem if any are available. The CLP will also provide syntax for DB2 commands (try DB2 ? backup for example).

Back to top

Scripting

You can use the CLP to run scripts, and any utilities that are run with regular operational processing (such as nightly LOAD, RUNSTATS or BACKUP commands) can be a CLP script. CLP options identify the input file (-f), an output file for messages (-z), the ability to echo what's running to the screen (-v), and can set a statement termination character (-t) so the script can contain multiple DB2 commands and SQL statements. I typically run scripts as follows: DB2 -tvf filename.ddl -t sets the default termination character (;), -v echoes the contents of the file to the screen (so I can check what I told DB2 to do) and -f tells the CLP to use an input file. Help for all options is available with DB2 ? options:

   C:\SQLLIB>db2 ? options

db2 [option ...] [db2-command | sql-statement |

   [? [phrase | message | sqlstate | class-code]]]

option: -a, -c, -e{c|s}, -finfile, -lhistfile, -n, -o, -p, -rreport, -s, -t,

   -td;, -v, -w, -x, -zoutputfile

More advanced scripting, including scheduling, is available from the script center in the DB2 Control Center (and you can use the journal to see what happened). The CLP is just a quick way to get going when you're in the prototyping mode. Note -x in the output above: we added the ability to suppress column headings in fixpak 1 of DB2 v7. If a customer asked for this, they presumably care about column headings because they want the output to look nice (or serve as input to another program). To me, this suggests that people are using the CLP for more than prototyping.

Back to top

Scripting DDL

If you follow this simple rule for every production and development database, you'll thank me on your deathbed: save all DDL you ever run to create or alter database objects in a file. Better yet, enter the DDL in a file with an editor, and run it by submitting the file to the CLP. If you've already ignored this advice, the text of statements to help re-create views, check constraints, SQL procedures, and triggers in a database can be retrieved as follows:

select text from syscat.views
select text,tabname from syscat.checks
select text from syscat.procedures
select text from syscat.triggers

You can also gather most of this running db2look with the -e option (or the -t and the -e options for specific tables). The DB2 Command Reference documents db2look. However, having the information saved up front in a file used to submit the DDL is more professional than hurriedly trying to recreate it when you're in the middle of a new project that relies on another database's schema.

Back to top

Prototyping

You don't have to save all the DML (SELECTs, INSERTs, UPDATEs, DELETEs) in a file. After all, these will be in the programs you write, and can be captured on the fly using DB2 tools like the monitor and DB2 Query Patroller. However, before putting SQL into an application, you can use the CLP to prototype it and see if you're happy with the results (as well as checking syntax). Use the -a option to see the SQL Communications Area (SQLCA). For example, if your statement is updating nothing, maybe it's syntactically correct, but no rows qualify for the update due to a very restrictive where clause. In this case, you get a positive SQLCODE of 100:

C:\sqllib>db2 -a update employee set salary=5 where salary "<" -5


SQLCA Information

sqlcaid : SQLCA   sqlcabc: 136   sqlcode: 100   sqlerrml: 0

sqlerrmc:

sqlerrp : SQLRIEXU

sqlerrd : (1) -31743   (2) 1    (3) 0

          (4) 0        (5) 0    (6) 0

sqlwarn : (1)    (2)    (3)    (4)    (5)    (6)

          (7)    (8)    (9)    (10)   (11)

sqlstate: 02000

Back to top

When To Use the DB2 Command Center

The DB2 Command Center is a graphical command line. (This is a paradox, rather than a simple contradiction, for those readers who enjoy metaphysical logic.) Any command or SQL statement, and even operating system commands, can be submitted through the Command Center. While the CLP calls DB2 programming interfaces directly, the Command Center goes through DB2's Call Level Interface (the CLI, which is basically a superset of ODBC). This means that when things fail, you may get a CLI error message rather than an SQLCODE. It also means that if you suspect a CLI bug, submit the SQL through the CLP and then the Command Center. If it succeeds in the CLP and fails in the Command Center, the problem is definitely at the CLI level. This is important in DB2, because all ODBC, Java, JDBC and SQLJ access to DB2 goes through the CLI. For that matter, the Command Center is written in Java. The Command Center has these advantages over the CLP:

1. Horizontal and vertical scrolling outside the bounds of the operating system command prompt: this includes separate tabs (or pages) for the command you submitted and the results of SELECT statements.
   
   
2. Full clipboard support for cut and paste.
   
   
3. Access to the Script Center (in the Control Center) and Visual Explain (to graphically display access plans for SQL statements - the best way to see which indexes get used). Highlight the SQL and click on Access Plan to visually explain an SQL statement.
   
   
4. The ability to more easily view (or hide) wide columns by widening or shrinking the viewable area with the mouse.
   
   
5. A pulldown menu for retrieving and editing previously submitted commands (rather than relying on the operating system's commands, like up-arrow and down-arrow on Windows and OS/2).
   
   
6. Better history of the commands and results submitted in that session - which is great for turning interactive experiments into scripts (or taking all those CREATE TABLE statements and saving them in a file).
   
   
7. Support for all CLP options (like displaying the SQLCA).
   
   

Because the interface differs, there are some subtleties of which to beware. The following statements will get very different results in the Command Center and the CLP:

create user temporary tablespace usetemp
    pagesize 4k managed by system using ('C:\usetemp');
declare global temporary table t1 (col1 int) not logged;
select count(*) from session.t1;

It has to do with the way session ownership for user-declared temporary tables is handled by DB2. The ownership is not constant for each Java process initiated in the Command Center. The select count(*) above will succeed in the CLP, but be unable to find session.t1 in the Command Center.

Back to top

CLP Design

The DB2 Command Reference, in Chapter 2, explains the CLP design. It consists of two processes:

1. A front end process (or thread on Windows and OS/2) to handle communication with the operating system command prompt - the stuff you see.
   
   
2. A back end process to handle communication with the database. This ensures that after you connect to DB2, if you interrupt the output from a large select (such as SELECT * FROM SYSCAT.TABLES) with Control-C or Control-Break, the output gets interrupted successfully without breaking the connection to DB2.
   
   

Back to top

Command Windows versus CLP on Windows

So why does DB2 require you to start a CLP with DB2CMD.EXE on Windows (or with the "DB2 Command Window" or "DB2 Command Line Processor" from the start menu)? The link between the front- and back-end processes is simple enough on UNIX and OS/2: if the parent process dies, all its children are terminated by the operating system. On Windows, parent threads do not terminate their children when they end. Rather than risk lots of phantom threads on Windows, we decided to link the front-end and back-end threads for the CLP on Windows with a cookie. This requires the CLP to be started with DB2CMD.EXE. It ensures that if you kill the parent thread, the child thread doesn't hang around and waste resources. Don't worry about the specific option called "DB2 Command Processor." It creates a special prompt that looks like this:

With this, you don't have to precede everything you send to DB2 with the directive: DB2. On the other hand, you now have to precede operating system commands with an exclamation mark (!), just like in the Command Center.

Back to top

When Not To Use the Command Line Processor

The CLP is great for prototyping SQL to check for syntax and the results you want the user to see. But, because the CLP is so universal, offering complete access to every DB2 SQL statement, command and programming interface, it generally has more overhead than dynamic SQL, such as that submitted through ODBC. To prototype for performance, use db2batch, which is documented in the DB2 Command Reference.

Back to top

A Useful Example to Avoid (or a Useless Example to Copy)

Here's a simple example full of bad programming practices that shows why the CLP is not great for application logic. For simple scripting, the example below could be more powerful and even handle some error conditions if written in PERL. However, it was fun to write and did some useful work for me. Prerequisites:

1. You have created the sample database
   
   
2. The output you get for DIR matches the systems I tested on (NT 4.0 with ServicePak 5, and Windows 2000) character for character.
   
   
3. You're in a DB2 Command Window on Windows
   
   

Back to top

What the Example Does

Ever run out of disk space? The example below captures the output of a recursive directory listing on C:\, imports a row for every file of one megabyte or larger, and lists the ten largest files on your C: drive. Don't delete PAGEFILE.SYS! For that matter, delete only the files that you know are garbage (such as large .PDF files in C:\TEMP). What's good about the example?

1. It helped me clean up my hard disk
   
   
2. It worked on two systems (at which point I successfully exited system test and shipped it!)
   
   

What's bad about the example?

Shortcomings will be documented in a 500-page readme to be issued soon at a developer's domain near you.

Behold the example:
(I run this like so: db2 -tvf dirpub)

!dir c:\*.* /s>dir.out;

connect to sample;

create table dirlist (size_in_mb int not null, name char(21) not null

    CONSTRAINT CHECKBYTES CHECK (name not like ' bytes%'));

import from c:dir.out of asc method l (28 30, 40 60)

    commitcount 1000 replace into dirlist;

select * from dirlist order by size_in_mb desc

    fetch first 10 rows only;

Line 1 sends a list of all files on C: to dir.out.

Line 2 connects to the sample database.

Line 3 creates a table with two columns: the size in megabytes of each file, and the first 21 characters in the name of each file. Since the phrase "bytes" shows up when Windows tells you about directories, we use a check constraint to eliminate these bogus rows.

Line 4 imports lines from the file dir.out into the table dirlist. We assume that the size in megabytes is an integer in columns 28 through 30, and grab the contents of columns 40 through 60 as the file name. We commit every 1000 rows, and replace the contents of dirlist in case you run this multiple times.

Line 5 grabs the ten file names with the largest sizes.

So, with five lines of code, we can clean up a hard disk partition (or at least see which files take all the space). Handling error conditions, allowing this to search other drives, and eliminating system files from the output, could easily expand this little program into several hundred lines that I don't want to write. After all, the spirit of the CLP is "quick and dirty": get something done where 20% of the effort meets 80% of the requirement. If you carefully apply the 80/20 rule, you'll always have another project to tackle just when your users think they're happy.

IBM and VisualAge are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

IBM copyright and trademark information

Click here for more information on these publications, or to order copies.

About the author

		Blair Adamache is a seventeen-year veteran of the IBM Toronto Lab. He has a Master's degree in Rhetoric, but promises to use his powers only in the service of good. In his current role Blair manages the DB2 Service Team, with the responsibility of ensuring that DB2 customers continue to enjoy the best software support in the database industry. Blair has many years of experience with customers through stints in marketing, service, development, and management. He is the technical editor of "DB2 Fundamentals Certification for Dummies (Hungry Minds)," a co-author of "The Complete DB2 Reference (McGraw Hill)," and has written many articles for the IBM DB2 Developer Domain. The best way to reach Blair for technical questions is a post to comp.databases.ibm-db2.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top