A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
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.
Blair Adamache, DB2 technology development, IBM
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
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 pageVerifying 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:
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 pageHelp
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 data is retrieved, updated or deleted here.
User response: No action required. Processing can continue.
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 pageScript 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:
DB2 [option ...] [Db2-command | sql-statement |
[? [Phrase | message | sqlstate | class-code]]
|-F||read from input file|
|-l||log Record command in history file||OFF|
|-n||remove newline characters||OFF|
|-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 pageWriting 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 pagePrototype
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
Sqlcaid:sqlca sqlcabc:136 sqlcode:100 sqlerrml:0
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)
Back to top of pageWhen 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:
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 pageCLP Design
DB2 Command Reference explains the CLP design in the 2nd Chapter. The CLP design consists of two processes:
Back to top of pageComparison 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:
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 pageWhen 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 pageA 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:
Back to top of pageWhat 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?
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)
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
Start building with 50+ products and up to 12 months usage for Elastic Compute Service