"Go" DB2 command-line processor and scripting

The command-line processor is the DB2 interface, which best embodies the power of DB2, as well as the simplicity and versatility of DB2. The author makes a detailed exposition of it through examples.

0 Reviews:

Blair Adamache, DB2 technology development, IBM

Close [x]

Blair Adamache is an old employee with 17 years of experience in the IBM Toronto Lab. He holds a master's degree in rhetoric (rhetoric), but promises to apply his abilities only to good services. Blair's current role is to manage the DB2 services team, which is responsible for ensuring that customers continue to enjoy the best software support in the database industry. Blair has years of experience dealing with users, marketing, service, development, and management. He is a technical editor of "DB2 Fundamentals Certification for Dummies (Hungry Minds)", one of the co-authors of the complete DB2 Reference (McGraw Hill), and is an IB M DB2 Developer's Corner has written many articles. The best way to find Blair because of technical problems is to send an email to COMP.DATABASES.IBM-DB2.

June 01, 2003

• Content
  • When to use the DB2 command-line processor
  • Verifying the installation and determining the problem
  • Help
  • Script writing
  • Writing DDL Scripts
  • Prototype
  • When to use DB2 Command Center
  • CLP Design
  • Comparison of the command window with the CLP on Windows
  • When not to use the command line processor
  • A typical example that needs to be avoided (or an example that is not worth copying)
  • What this example does
  • Comments

When to use the DB2 command-line processor

The IBM DB2 command-line processor sounds nothing special, but in fact it is the DB2 interface, which best embodies the power of DB2, as well as the simplicity and versatility of DB2. The command line is not cute (in fact, DB2 on Windows has a reputation for being "powerful but not cute". That was when DB2 was first released on NT, and there was no ControlCenter available at that time. Command exercise we remembered the Telnet on UNIX and the DOS command that we started using in 1981. However, if your SQL command is strong enough--or if you want to verify the installation of DB2 by entering a quick command--then the command line is ideal and is usually faster than submitting a request through a front end such as PowerBuilder or access. The CLP is the direct path to each of the DB2 programming interfaces. It allows anything that can be programmed to be invoked through an interface-or in a simple script (as we have written in DOS). BAT file). Similar tools such as the Spufi on the MVS, the ISQL on the VM, and the SQLDBSU on the VSE are very durable, which makes us confident that the CLP can be used for a long time.

This article will show you how to use the command line processor (CLP) to bring you benefits. If some of the work is not clear enough, you can refer to the manual ( DB2 Command Reference), which uses an entire chapter to describe the use of the CLP.

Back to top of page

Verifying the installation and determining the problem

When I first installed DB2 on OS/2 (a year ago DB2 was already available on NT), I already used SQL Database Manager on many other operating systems (dos,vm,vse,mvs™,as/400®). After the successful installation, I began to read about the information, when I was "cataloging nodes and databases (cataloging node and database)" This thing confused. The word "Catalog" has a verbal meaning compared to the SYSCAT and SYSIBM catalogues of the past. Sometimes I yell at DB2: "I don't want to catalog anything, I just want to make sure I install DB2 correctly by running a SELECT statement." "After a few hours, I calmed down and learned that only after a database has been created does the DB2 have a database directory; You do not need to catalog nodes and databases on your local machine-it is only necessary to catalog a client that is connected to the server. It's very easy to create a sample database-run Db2sampl. When verifying the installation, the command-line processor is the tool I need, which can run: DB2 SELECT COUNT (*) from SYSCAT. TABLES

On UNIX and OS/2, you only need to use a common operating system prompt and precede any DB2 commands or SQL statements with "DB2". On Windows, things are going to be a little cumbersome, which is explained later, but you can create a prompt for DB2 commands, SQL, and operating system commands (such as Dir and ren) by entering Db2cmd. You can even mix SQL, DB2 commands, and operating system parameters, such as scrolling the screen with |more, and delivering content to the file with the greater-than sign (>): DB2 select * from Employee>c:\\tmp\\emp.out |more

Note: For symbols that make sense for DB2 and the operating system, such as the greater-than sign (>) on Windows, the operating system is interpreted first. The SELECT * from EMPLOYEE WHERE SALARY >9999 statement will send the error message to a file named 9999. To draw a distinction between these special symbols; you should enter: SELECT * from EMPLOYEE WHERE SALARY ">" 9999

While your users may never use the CLP to access their data, the CLP is the most basic tool in the Toolbox for DBAs or application programmers: it's like a tuxedo for all occasions. For critical applications, the database is often the key, and if something goes wrong, the problem will be more difficult to determine. In the mainframe/microcomputer domain, the problem may be in the application or in the software system, but the problem can always be defined within a single computer. In the client-server domain, the problem may be hidden in the client system software (operating system, database client, or communication protocol), may be hidden in the client application code, may be hidden in the server application code (if you are using a trigger, user-defined functions, or stored procedures), It may also be hidden somewhere else on the server. 3-tier applications include Web servers, browsers, and third-tier hardware. What is the CLP's use here? The answer is that it can split the application out of this schema. If you find a problem, you can translate the failed request into a simple SQL, and then run the SQL from the CLP on the following machine:

1. Server
2. Client
3. Web Server

Depending on where there is no problem, where there is a failure, you can determine if the DB2 itself is faulty (it is likely that the failure form on the server that is submitting the SQL through the CLP is the same as the client application failure) or that the problem is with the communication (the request fails on the client and there is no problem on the server). Or the problem is on the WEB server (the problem is only in the farthest place).

Back to top of page

Help

The CLP is also an interface to DB2 help. If you receive a message without knowing what appropriate steps to take, enter it in the CLP:

C:\\sqllib>db2? sql0100 |more

There should be sql0100w No in the output of the FETCH, UPDATE, or DELETE statement, otherwise the result of the query is an empty table.

Explanation: One of the following situations is true:

• No This line matches the search criteria specified in the UPDATE or DELETE statement.
• The result of the SELECT statement is an empty table.
• A FETCH statement is executed when the cursor navigates to the last row of records in the result table.
• The result of the SELECT used in the INSERT statement is empty.

No data is retrieved, updated or deleted here.

User response: No action required. Processing can continue.

Sqlcode: +100

sqlstate:02000

You can take a look at the same help in the Messages manual, including actions that are recommended and can be used to avoid problems. The CLP will also provide syntax for the DB2 command (as an example, you can try DB2?). Backup).

Back to top of page

Script writing

You can run a script with the CLP, and any utility that can run in a normal operation (such as a nightly LOAD, RUNSTATS, or BACKUP command) can be a CLP script. Some options in the CLP can specify the input file (-f), a output file for the message (-Z), the ability to echo information about what to run on the screen (-V), the ability to set a statement terminator (-T), and if this option is set, the script can contain multiple DB2 commands and SQL statements. Usually I run the script like this: DB2-TVF filename.ddl-t, followed by the default Terminator (;), the-V option allows the contents of the loopback file to be echoed to the screen (so that you can check what I told the DB2 to do), the-F option tells the CLP to use an input file. Through DB2? Options can be helpful for all of the choices:

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.

off /tr>

Options	Description	Default Settings
-a	display SQLCA	OFF
-C	Auto-submit	on
-e	display sqlcode/sqlstate	OFF
-F	read from input file
-l	log Record command in history file	OFF
-n	remove newline characters	OFF
-o	show output	on
-P	show DB2 interactive command	on
-R	save output report to file	OFF
-S	stop execution when a command error occurs	OFF
-T	Set statement terminator	OFF
-v	loopback current command	OFF
-W	display fetch/select warning message	on
-X	omits the printing of column headings	OFF
-Z	saves all output to the output file	OFF

More advanced scripting, including scheduling (scheduling), is available in the Script Center in DB2 ControlCenter, and logs can be used to see what's going on. In prototype (prototyping) mode, the CLP is just a quick way to start a program. Note the-X option in the above output: we added this capability so that column headings are not output in Fixpak 1 of DB2 V7. If a customer asks for a column header, they may be concerned about column headings because they want the output to look good (or to use the output as input to another program). For me, this means that people are now using the CLP not just for prototypes.

Back to top of page

Writing DDL Scripts

If you follow this simple rule for each product and development database, you will be grateful to me: You can save all the DDL used to create or change database objects in one file. Even more exciting is that you can use an editor to input the DDL into a file and then run the file to the CLP by committing the file. If you do not follow these recommendations, the text of the statements used to recreate the views, check constraints, SQL procedures, and triggers in a database can also be obtained:

Select text from Syscat.views

Select Text,tabname from Syscat.checks

Select text from Syscat.procedures

Select text from Syscat.triggers

You can also run Db2look to collect most of this information by using the-e option (or by using the-t option and the-e option for a particular table). DB2 Command Reference wrote a document for Db2look. However, it is more professional to keep such information in advance in a file that is used to commit the DDL, rather than hastily recreating it halfway through a new project that relies on a schema of another database.

Back to top of page

Prototype

You do not have to save all of the DML (Select,insert,update,delete) in a file. After all, these DML will be in the program you write, and can be captured dynamically by using DB2 tools like the monitor and DB2 Query patroller. However, before you enter SQL into an application, you can prototype it using the CLP to see if you are satisfied with the results (and you can check the syntax). Use the-a option to view the SQL Communications area (SQLCA). For example, if the statement does not update anything, it may be syntactically correct, but there is no record line that meets the update condition due to a very restrictive where clause. In this case, you get a SQLCODE with a value 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 of page

When to use DB2 Command Center

DB2 Command Center is a graphically-ordered line (for those readers who prefer metaphysical logic, this is a specious statement, not a simple contradiction). Any command or SQL statement, or even an operating system command, can be submitted through command Center. Although the CLP calls the DB2 programming interface directly, Command Center passes through the DB2 call level Interface (that is, the CLI, which is basically a superset of ODBC). This means that when a failure occurs, you may get a CLI error message instead of a SQLCODE. This also means that if you suspect a CLI bug somewhere, you can try submitting SQL to Command Center via the CLP. If the CLP section is successful and the Command Center section is unsuccessful, then the problem is clearly part of the CLI. In DB2, this is important because all ODBC, Java, JDBC, and SQLJ accesses to DB2 are routed through the CLI. For this reason, Command Center is written in Java. The following advantages are compared to Clp,command Center:                  

1. You can scroll horizontally and vertically outside the bounds of the operating system command prompt: This includes a separate tab (or page) for the results of the commands you submit and the SELECT statement.
2. Full clipboard support for cut and paste.
3. You can access Script Center (in ControlCenter) and Visual Explain (to graphically display access plans for SQL statements-this is the best way to see which indexes are used). You can highlight SQL, and you can visually interpret an SQL statement by tapping Access Plan.
4. It's easier to show (or hide) multiple columns by expanding or shrinking the visible area with the mouse.
5. Provides a drop-down menu for retrieving and editing previously submitted commands (rather than operating system-dependent commands, such as up and down arrows in Windows and OS/2 operating systems).
6. Better history of commands and results submitted in a session-this facilitates the conversion of an interactive experience into a script (or the acquisition of all CREATE TABLE statements and depositing them into a file).
7. All CLP options are supported (e.g. display SQLCA).

Because the interfaces are different, some of the subtleties need to be known. The next statement will have very different results in Command Center and in 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;

This is related to the way that DB2 handles the session ownership of temporary tables used for user claims. Ownership is not immutable for each Java process that is launched in Command Center. The above select COUNT (*) statement will succeed in the CLP, but session.t1 cannot be found in Command Center.

Back to top of page

CLP Design

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

1. A front-end process (or a thread on Windows and OS/2) that handles communication with the operating system command prompt.
2. A back-end process that handles communication with the database. This ensures that after you connect to DB2, if you use Control-c or Control-break abort from a large selection (for example, select * from SYSCAT. TABLES), the output will be successfully aborted without disconnecting the connection to the DB2.

Back to top of page

Comparison of the command window with the CLP on Windows

So why DB2 asks you to use Db2cmd on Windows. EXE (or start a CLP with DB2 command Window, or with DB2 command line Processor, or Start menu)? On UNIX and OS/2, the link between the front-end process and the back-end process is simple: if the parent process dies, all its child processes will be terminated by the operating system. On Windows, the parent thread does not terminate its child threads at the time of death. We're not going to risk it. Generating a large number of phantom threads on Windows, we decided to use a cookie to link the front-end thread to the CLP on Windows. This requires the CLP to pass the db2cmd. EXE to start. This ensures that if the parent thread is killed, the child threads are not retained, thereby avoiding the waste of resources. Don't worry about specific options called "DB2 Command Processor". It will create a special prompt like this:

DB2 =

This way, you do not have to add DB2 before all the instructions sent to DB2. On the other hand, now you must precede the operating system commands with an exclamation point (!), just like in command Center.

Back to top of page

When not to use the command line processor

The CLP is useful for examining the syntax and the prototype SQL that you want the user to see. However, because the CLP is so multi-purpose and provides access to every DB2 SQL statement, command, and programming interface, it is generally more expensive to CLP than dynamic SQL, such as those provided by ODBC. In order to prototype the performance, you can use Db2batch, which is documented in DB2 Command Reference .

Back to top of page

A typical example that needs to be avoided (or an example that is not worth copying)

Here's a simple example of a lot of bad programming habits, and this example shows why the CLP is not very good for application logic. For simple scripting, the following example can be more powerful and even handle some error situations if it is written in PERL. However, it is very interesting for me to write and do some useful work. Prerequisite:

1. A sample database has been created.
2. The output from the DIR command matches the system where I tested (NT 4.0 with Servicepak 5, and Windows 2000).
3. You are in a DB2 Command Window on Windows.

Back to top of page

What this example does

Have you ever run out of disk space? The following example captures the output of a recursive directory listing on c:\\, imports one row for each file with a size of 1 MB or greater than 1 MB, and lists the 10 largest files in the C: disk. Do not delete pagefile. sys! You can only delete files that are explicitly known to be garbage (for example, large in c:\\temp.                  PDF file). What is the good part of this example?

1. It can help me clean up my hard drive.
2. It can work on both systems (I successfully completed the system test and released it!) ）

What's so bad about this example?

In a 500-page Readme (Readme) that is about to be released in the developer's Corner, you will see a document explaining the drawbacks of this example.

Study This example carefully: (This is how I run: DB2-TVF dirpub)

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

Connect to sample;

CREATE TABLE dirlist (SIZE_IN_MB int not NULL, name char (+) NOT NULL

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

Import from C:dir.out of ASC method L (28 30, 40 60)

Commitcount replace into dirlist;

SELECT * from Dirlist ORDER BY size_in_mb Desc

Fetch first ten rows only;

The 1th line C: All the files on the list are sent to Dir.out.

Line 2nd connects to the sample database.

Line 3rd creates a table with two columns: One column is the size of each file in megabytes, and one column is the first 21 characters in the file's file name. Now that the phrase "bytes" is displayed when Windows tells you about the directory, we use a check constraint to eliminate these unwanted rows.

The 4th row in the file dir.out is imported into table dirlist. We assume that the file size in megabytes is an integer from column 28th through column 30th, and that the contents from column 40th to column 60th are taken as file names. We submit every 1000 lines and replace the contents of dirlist to prevent this submission from being run multiple times.

Line 5th Gets the file name of the 10 largest file.

So, with 5 lines of code, we can clean up a hard disk partition (or at least see which files occupy all the space). Dealing with errors, allowing searching for other drives, and removing system files from the output can easily extend this tiny program to hundreds of of lines, and I don't want to write so much code. After all, the CLP's spirit is "fast and dirty (quick and dirty)", which means that 20% of the energy spent has satisfied 80% of the demand. If you are careful to apply this 80/20 rule to satisfy your users, you will always receive more projects.

"Go" DB2 command-line processor and scripting