When using the DB2 command line processor
IBM DB2 command line processor sounds like nothing special, but in fact it is the DB2 interface, it best embodies the power of DB2 and DB2's simplicity and versatility. Command line is not pretty (in fact, Windows on the DB2 has been a "strong but the Beloved" reputation. It was first published on DB2 on NT when the Control Center was not yet available). UNIX command exercise reminds us of the Telnet, there are 1981 used DOS commands. However, if your SQL commands strong enough - or you want to enter a quick command to verify the installation of DB2 situation - then the command line is ideal, but usually higher than through the Access such as PowerBuilder or submit a request to the front faster . CLP is the gateway to each of the direct path of DB2 programming interface. It makes any thing can be programmed to call through interface call - or a simple script (as we had to write off in DOS. BAT files) in the call. As MVS on SPUFI, VM and VSE ISQL on the SQLDBSU These are very durable and similar tools, which makes us confident that CLP will have a very long time.
This article will show you how to use the command line processor (CLP) for your benefit. If the principle is not enough for some to understand, then, can refer to the manual (DB2 Command Reference), with a whole chapter of this manual describes the use of CLP.
Verify installation of the situation and determine where the problem lies
When I first OS / 2 when you install DB2 (DB2 in this one year ago has been used in NT), I have many other operating systems (DOS, VM, VSE, MVS 鈩? AS/400 庐 ) on the used SQL Database Manager of the. After successfully installing, I started to read about information, when I was "cataloging nodes and databases (catalog node and database)," confused the matter. catalog the word and the last likable SYSIBM catalog SYSCAT and compared with the verb of the mean. Sometimes, I will complain loudly against DB2: "I do not want to catalog anything, I just want to run a SELECT statement to ensure that I properly installed the DB2." A few hours later, I calm down, understand that only created After the DB2 database will have a database directory; you do not need the local machine will be cataloged nodes and databases - only in a connection to the server when the client needs cataloging. Create a sample database is easy - run db2sampl on it. Verify installation of the situation, the command line processor is a tool I need, when you can run:
DB2 SELECT COUNT (*) FROM SYSCAT.TABLES
In UNIX and OS / 2, you only need to use a common operating system prompt, and in any DB2 command or SQL statement by adding "DB2". In Windows, things will take some work, which will be explained later, but you can enter DB2CMD to create an appropriate DB2 commands, SQL and operating system commands (such as dir and ren) in the prompt. You can even mix of SQL, DB2 commands, and operating system parameters, such as use | more to scroll the screen, use the greater than (>) to transport the contents of the file:
DB2 select backup bin bin_old conf config crawler.tar.gz crawler_bin.tar.gz data eshow eshow_sitemap.html generate.sh google.html google.html.md5 log maint news: 10 news: 11 news: 12 news: 13 news: 14 news: 15 news: 16 news: 17 news: 18 news: 2 news: 3 news: 4 news: 5 news: 6 news: 7 news: 8 news: 9 outboundLinksMgr.sql seeds sitemap.html svn tasks tmp xml2dict-2008.6- tar.gz xml2dict-read-only from employee> c: \ tmp \ emp.out | more
Note: DB2 and operating system for those who have a sense of symbols, such as Windows on the greater than (>), the operating system will be the first to answer. SELECT backup bin bin_old conf config crawler.tar.gz crawler_bin.tar.gz data eshow eshow_sitemap.html generate.sh google.html google.html.md5 log maint news: 10 news: 11 news: 12 news: 13 news: 14 news : 15 news: 16 news: 17 news: 18 news: 2 news: 3 news: 4 news: 5 news: 6 news: 7 news: 8 news: 9 outboundLinksMgr.sql seeds sitemap.html svn tasks tmp xml2dict-2008.6-tar . gz xml2dict-read-only FROM EMPLOYEE WHERE SALARY> 9999 statement, the error message will be transmitted to a file named 9999. To draw the boundaries of these special symbols; you should enter:
SELECT backup bin bin_old conf config crawler.tar.gz crawler_bin.tar.gz data eshow eshow_sitemap.html generate.sh google.html google.html.md5 log maint news: 10 news: 11 news: 12 news: 13 news: 14 news : 15 news: 16 news: 17 news: 18 news: 2 news: 3 news: 4 news: 5 news: 6 news: 7 news: 8 news: 9 outboundLinksMgr.sql seeds sitemap.html svn tasks tmp xml2dict-2008.6-tar . gz xml2dict-read-only FROM EMPLOYEE WHERE SALARY ">" 9999
Although your users may never use the CLP to access their data, the DBA or application programmer who, CLP in the toolbox is the most basic tool: it is applicable on all occasions such as the tuxedo. For critical applications, often the database is a key point, if a local error, then the problem would be more difficult to determine. In the mainframe / mini computer field, the problem may be in the application may also be out in the software system, but the problem can always be defined within a single computer. While the client - server space, the problem may be hidden on the client system software (operating systems, 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 triggers, user-defined function, or stored procedure), may also be hidden in other places on the server. 3 - tier applications, including Web server, browser and third-tier hardware. CLP in here for? The answer is that it can move an application from the architecture differently. If you find a problem, then the fault can be translated into a simple request SQL, and from the machine on the CLP to run the SQL:
Server
Client computer
Web Servers
According to Where No problem, where there is failure, you can determine that DB2 itself faulty (most likely through SQL-CLP presented in the form of a failed server and client applications in the form of the fault is the same), or the problem In the communication on the (request for the client out of trouble, but there is no problem on the server), or the problem is with the Web server (the problem only lies in the furthest place).
Help
CLP is also a help to the DB2 interface. If you receive a message, but do not know what appropriate steps should be taken, then enter in the CLP:
C: \ SQLLIB> db2? Sql0100 | more
FETCH, UPDATE or DELETE statement, the output should be SQL0100W No such line; otherwise the query result is an empty table.
Explanation: The following case is true in a situation where:
No this line found in the UPDATE or DELETE statement specified search criteria.
SELECT statement's result is an empty table.
When positioning the cursor to the last row of the table records the results, it will execute a FETCH statement.
Used in INSERT SELECT statement the result is empty.
There is no search, update, or delete data.
User response: No action. Processing can continue.
sqlcode: +100
sqlstate: 02000
You can see the same in the Messages manual help, including suggestions to avoid problems and can be used with the action. CLP will also provide for the DB2 command syntax (as an example, you can try DB2? Backup).
Scripting
You can use the CLP to run the script, any operation can be treated with conventional run the utility program (such as night running LOAD, RUNSTATS or BACKUP commands), can be a CLP script. CLP's some options you can specify the input file (-f), one for the message output file (-z), it can send back to the screen on what to run on the information (-v), it can set a statement terminator (-t), if you set this option, the script can contain multiple DB2 commands and SQL statements. Usually, I will run this script: DB2-tvf filename.ddl-t, followed by the default terminator (;),- v option allows to send files back to the contents to the screen (so you can check to do I tell the DB2 what),-f option tells CLP using an input file. By DB2? Options available on all options to help:
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.
Option Description Default setting
-A Display SQLCA OFF
-C auto-commit ON
Display SQLCODE / SQLSTATE OFF
-F file Read the contents from the input OFF
-L logging history file, the command OFF
Delete newline character display output ON OFF-o
-P Display interactive command db2 ON
-R Save output report to file OFF
-S Stop execution when an error occurs the command OFF
-T set the statement terminator OFF
-V Echo current command OFF
-W Display FETCH / SELECT warning messages ON
-X is omitted, print column headings OFF
-Z Save all output to the output file OFF
In the DB2 Control Center, where in the script center provides a more advanced scripting, including the timing (scheduling), and can use the logs to see what happened. In the prototype (prototyping) mode, CLP is just a quick way to start the program. Note that the output of the-x option: we added the ability to allow for the fixpak 1 in DB2 v7 is not output column headings. If the customer requests the output column headings, they may be concerned about the column headings, because they want some of the output look better (or another program to output as the input). For me, this means that people will now CLP not only for the prototype.
DDL script written
If the database for each product and development You follow this simple rule, you will be grateful to me: You can all be used to create or change a database object DDL is stored in a file. Even more exciting is that you can use an editor to enter into a DDL file, and then submit this file to the CLP to run the file. If you do not adopt the proposal, then re-used to create a database view, check constraint, SQL process and triggers the text of the statement also can 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 use the-e option (or for a specific table using the-t option and the-e option) will run db2look to collect most of the information. DB2 Command Reference for the preparation of the document db2look. However, this information will be stored in advance in a DDL file used to submit more than one database in a model depends on a new project to re-create the middle of rush it is to look professional some more.
Prototype
You do not have all of the DML (SELECT, INSERT, UPDATE, DELETE) is stored in a file. After all, these DML will be written in your program, and can be used as monitors and DB2 Query Patroller DB2 tools such as dynamic capture. However, in the SQL input to an application, you can first use the CLP to prototype testing to see if they are satisfied with the results (and can check the syntax). Use the-a option to view the SQL Communications Area (SQLCA). For example, if the statement is not anything to update, perhaps in the syntax of this statement is correct, but no rows meet the updated condition was due to use of the restrictive clause where a very strong result. In this case, you will get a value of 100 in SQLCODE:
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
When using the DB2 Command Center
DB2 Command Center is a graphical command line (for those readers who prefer the logic of metaphysics, this is a specious argument, rather than simply self-contradictory). Any command or SQL statement, or even the operating system commands, can be submitted through the Command Center. While the direct call to DB2 CLP programming interface, but the Command Center to be returned by DB2's Call Level Interface (or CLI, which is basically a superset of ODBC). This means that when a fault occurs, you may get a CLI error message, rather than a SQLCODE. This also means that if you suspect that somewhere CLI bug, you can try to be submitted by CLP SQL to Command Center. If the CLP is partly successful, but some failure in the Command Center, then the problem obviously lies in that part of CLI. In DB2, this is important, because all the DB2 ODBC, Java, JDBC and SQLJ access should be through the CLI. For this reason, Command Center is to use Java. Than CLP, Command Center has the following advantages:
Operating system command prompt in the external border scroll horizontally and vertically: This includes orders and for you to submit the results of SELECT statement, the independent tab (or page).
On the cut and paste the full clipboard support.
Can access the Script Center (in Control Center in) and Visual Explain (in order to graphically display the access plan on the SQL statement - this is to see which indexes are the best way to use). Can highlight the SQL, by clicking on the Access Plan to visually explain a SQL statement.
Can more easily expand or shrink with the mouse through the visible area to show (or hide) the number of columns.
Provides a drop-down menu, used to retrieve and edit previously submitted an order (rather than relying on the operating system commands, such as Windows and OS / 2 operating system, the up arrow and down arrow).
In the session presented the results of a better command and history - which is conducive to the interactive experience into the script (or get all the CREATE TABLE statement and to place a file).
Support all of the CLP options (such as display SQLCA).
Because the interfaces are different, some of the subtleties need to know. The next statement in the Command Center and in the CLP will be very different results:
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 statement and DB2 on a temporary table for user session handling of the ownership. Start in the Command Center for each Java process, ownership is not static. The above select count (*) statement will be successful in the CLP, but can not find the Command Center in session.t1.
CLP Design
DB2 Command Reference in Chapter 2 explains the design of CLP. CLP design composed by two processes:
A front-end process (or in the Windows and OS / 2 on the thread), to handle communication with the operating system command prompt.
A back-end process for handling communications with the database. This ensures that you connect to DB2, if the use of Control-C or Control-Break suspended from a large selection (such as SELECT backup bin bin_old conf config crawler.tar.gz crawler_bin.tar.gz data eshow eshow_sitemap.html generate. sh google.html google.html.md5 log maint news: 10 news: 11 news: 12 news: 13 news: 14 news: 15 news: 16 news: 17 news: 18 news: 2 news: 3 news: 4 news: 5 news: 6 news: 7 news: 8 news: 9 outboundLinksMgr.sql seeds sitemap.html svn tasks tmp xml2dict-2008.6-tar.gz xml2dict-read-only FROM SYSCAT.TABLES) output, the output will be successfully terminated, will not disconnect the connection to DB2.
Command window and Windows on the comparison of CLP
Why, then, DB2 requires that you use Windows on DB2CMD.EXE (or use the DB2 Command Window, or use the DB2 Command Line Processor, or to use the start menu) then start a CLP? In UNIX and OS / 2, the front-end process and back-end link between the process is very simple: If the parent dies, all its child processes will be the operating system to terminate. In Windows, the death of the parent thread does not terminate the child thread. Here we do not adventure produced a large number of Windows on phantom thread but decided to use a cookie to the CLP on the link for the Windows front-end and back-end thread thread. This requires the CLP through DB2CMD.EXE to start. This ensures that if the father killed the thread, then the child thread will not be retained, thus avoiding the waste of resources. Do not worry called "DB2 Command Processor" specific options. It creates a special like this prompt:
db2 =>
This way, you do not have all the instructions sent to DB2 are combined prior to DB2. On the other hand, now you must add an exclamation point before the operating system commands (!), As in the Command Center in the same.
When not to use the command line processor
Used to check for grammar and you want users to see the results of the prototype SQL is, CLP useful. However, CLP is so multi-purpose, able to provide to each DB2 SQL statements, commands and programming interfaces for access channel, so generally speaking, than the dynamic SQL (such as those available through the ODBC dynamic SQL) to CLP will produce more large overhead. In order to test the performance of the prototype, you can use db2batch, which in the DB2 Command Reference have documented.
A typical example of the need to avoid (or say an example worth copying)
Here is a simple example, many of them full of bad programming habits, this example shows why the CLP for the application logic is not very good. For simple scripting, the following examples can be more powerful and can even deal with some errors, and if it is written in PERL so. However, for me, write and do some useful work is very interesting. Prerequisites:
Have created the sample database.
The output from the DIR command and I get where the test system (with ServicePak 5 of NT 4.0, and Windows 2000) to match.
You are in Windows, in a DB2 Command Window.
What this example
Not have encountered the situation of running out of disk space. The following examples capture in C: \ on a recursive directory listing of the output, for each size of 1 MB or larger than 1 MB of file into line, and lists the C: disk 10 largest files. Do not delete PAGEFILE.SYS! Can only remove those who clearly know junk files (such as C: \ TEMP in the large. PDF files). This case there is any good place?
It can help me clean up my hard drive.
It can work in both systems (I successfully completed system testing and release it!)
This example, what is wrong places?
Developer Corner in the upcoming release of a 500-page readme file (readme), you will see on this example of the shortcomings documented.
Carefully study this example:
(I like this run: 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 backup bin bin_old conf config crawler.tar.gz crawler_bin.tar.gz data eshow eshow_sitemap.html generate.sh google.html google.html.md5 log maint news: 10 news: 11 news: 12 news: 13 news: 14 news : 15 news: 16 news: 17 news: 18 news: 2 news: 3 news: 4 news: 5 news: 6 news: 7 news: 8 news: 9 outboundLinksMgr.sql seeds sitemap.html svn tasks tmp xml2dict-2008.6-tar . gz xml2dict-read-only from dirlist order by size_in_mb desc
fetch first 10 rows only;
1 about to C: on the list of all files sent to the dir.out.
Line 2 connected to the sample database.
Line 3 creates a table with two columns: one is a MB as a unit the size of each file, a file name of each file in the first 21 characters. Since Windows tells you information on the directory will display the phrase "bytes", then we use a check constraint to eliminate these unwanted lines.
4 dir.out about to file into the table in the row in dirlist. We assume that in MB file size for the unit is from 28 to 30 of an integer, and from 40 to 60 of the content used as a file name. Every time we introduce the 1000 and replaces the contents of dirlist to prevent multiple run such a referral.
Line 5 was the 10 largest file name.
So, with 5 lines of code, we can clean up a hard disk partition (or at least see out which files take up all the space). Handling error conditions, allowing search other drives, and remove the output of the system files, these functions can be easily extended to hundreds of this little program line, I do not write so many compiled code. After all, CLP's spirit is "quick and dirty (quick and dirty)", which means that 20% of energy to meet the needs of 80%. If you carefully apply the 80/20 rule, so that users are satisfied, you can always receive more projects.
About the author
Blair Adamache is the IBM Toronto lab, a 17-year-old employee work experience. He has rhetoric (Rhetoric) master's degree, but the promise applied only to their ability to good service. Blair's current role is to manage the DB2 service group, the group responsible for ensuring that customers continue to enjoy the industry's best software support database. Blair has many years of experience in dealing and users involved in marketing, services, development and management. He is the "DB2 Fundamentals Certification for Dummies (Hungry Minds)" technical editor, "The Complete DB2 Reference (McGraw Hill)" The co-author, and for the IBM DB2 Developer Corner has written many articles. Blair because of technical problems and find the best way is to email comp.databases.ibm-db2.
相关链接:
Report FTP ClientsFLV TO PSPDesktop ShopPhotoshop Production - Wire And SparkGames Action introductionKelon Did Not Deny That Gu Chujun Was Under SurveillanceOGM To MP4OGM to MKVFile COMPRESSION evaluationSelected Fifth One, Peking University Guanghua New Year's Forumhow to troubleshoot your computer errorsPhotoshop rendering realistic beautiful necklace3ds max cattle PRODUCTION throughout Korean beauty Song Hye KyoHow to get all the screen colors red, green and blue valuesJSF will not repeat the mistakes EJB