WIXLIB

DB2for Linux, UNIX, and Windows Version 9 Release 7Command ReferenceUpdated July, 2012SC27-2439-03

DB2for Linux, UNIX, and Windows Version 9 Release 7Command ReferenceUpdated July, 2012SC27-2439-03

NoteBefore using this information and the product it supports, read the general information under Appendix D, “Notices,” onpage 1215.Edition NoticeThis document contains proprietary information of IBM. It is provided under a license agreement and is protectedby copyright law. The information contained in this publication does not include any product warranties, and anystatements provided in this manual should not be interpreted as such.You can order IBM publications online or through your local IBM representative.v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/orderv To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwideTo order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU(426-4968).When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in anyway it believes appropriate without incurring any obligation to you. Copyright IBM Corporation 1993, 2012.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.

ContentsAbout this book . . . . . . . . . . . viiWho should use this book . . . . . . . . . viiHow this book is structured . . . . . . . . . viiHighlighting conventions . . . . . . . . . viiiHow to read the syntax diagrams . . . ixChapter 1. Command line processor(CLP) . . . . . . . . . . . . . . . . 1Command line processor features . . . . . .db2 - Command line processor invocation . . .Command line processor options . . . . . .Command line processor return codes . . . .Invoking command help from the command lineprocessor . . . . . . . . . . . . . .Invoking message help from the command lineprocessor . . . . . . . . . . . . . . 2. 8. 10. 18. 18. 19Chapter 2. Using command line SQLstatements and XQuery statements . . 21Chapter 3. Command line processorplus (CLPPlus) . . . . . . . . . . . 29Installing CLPPlus on existing IBM data serverclients . . . . . . . . . . . . . .Starting CLPPlus . . . . . . . . . .CLPPLUS command . . . . . . . . .CLPPlus console types. . . . . . . . .Window mode and UTF-8 character support .DSN aliases in CLPPlus . . . . . . . .Kerberos authentication in CLPPlus . . . .SERVER ENCRYPT authentication in CLPPlusLDAP support in CLPPlus . . . . . . .CREATE DATABASE in CLPPlus . . . . .Running a script file in CLPPlus . . . . .Skipping and interrupting CLPPlus commandsComments in CLPPlus. . . . . . . . .Escape characters in CLPPlus . . . . . .Bind variables in CLPPlus . . . . . . .Shell and environment variables in CLPPlus .CLPPlus traces and record logging . . . .DB2 commands supported by CLPPlus . . .CLPPlus restrictions . . . . . . . . .CLPPlus troubleshooting hints and tips . . .2930313335353738394344454647495354545556Chapter 4. How to read commandsyntax help . . . . . . . . . . . . . 59Chapter 5. CLP commands . . . . . . 63ACTIVATE DATABASE .ADD CONTACT . . .ADD CONTACTGROUP .ADD DBPARTITIONNUM. Copyright IBM Corp. 1993, 2012.64666768ADD XMLSCHEMA DOCUMENT . . . . .ARCHIVE LOG . . . . . . . . . . . .ATTACH . . . . . . . . . . . . . .AUTOCONFIGURE . . . . . . . . . .BACKUP DATABASE . . . . . . . . . .BIND . . . . . . . . . . . . . . .CATALOG DATABASE . . . . . . . . .CATALOG DCS DATABASE . . . . . . .CATALOG LDAP DATABASE . . . . . . .CATALOG LDAP NODE . . . . . . . .CATALOG LOCAL NODE . . . . . . . .CATALOG NAMED PIPE NODE . . . . . .CATALOG ODBC DATA SOURCE . . . . .CATALOG TCPIP/TCPIP4/TCPIP6 NODE . .CHANGE DATABASE COMMENT . . . . .CHANGE ISOLATION LEVEL . . . . . .COMPLETE XMLSCHEMA . . . . . . .CREATE DATABASE . . . . . . . . . .CREATE TOOLS CATALOG . . . . . . .DEACTIVATE DATABASE . . . . . . . .DECOMPOSE XML DOCUMENT . . . . .DECOMPOSE XML DOCUMENTS . . . . .DEREGISTER . . . . . . . . . . . .DESCRIBE . . . . . . . . . . . . .DETACH . . . . . . . . . . . . . .DROP CONTACT . . . . . . . . . . .DROP CONTACTGROUP . . . . . . . .DROP DATABASE . . . . . . . . . .DROP DBPARTITIONNUM VERIFY. . . . .DROP TOOLS CATALOG . . . . . . . .ECHO . . . . . . . . . . . . . . .EDIT . . . . . . . . . . . . . . .EXPORT . . . . . . . . . . . . . .FORCE APPLICATION . . . . . . . . .GET ADMIN CONFIGURATION . . . . . .GET ALERT CONFIGURATION . . . . . .GET CLI CONFIGURATION . . . . . . .GET CONNECTION STATE . . . . . . .GET CONTACTGROUP . . . . . . . . .GET CONTACTGROUPS . . . . . . . .GET CONTACTS . . . . . . . . . . .GET DATABASE CONFIGURATION . . . .GET DATABASE MANAGER CONFIGURATIONGET DATABASE MANAGER MONITORSWITCHES . . . . . . . . . . . . .GET DESCRIPTION FOR HEALTH INDICATORGET HEALTH NOTIFICATION CONTACT LISTGET HEALTH SNAPSHOT. . . . . . . .GET INSTANCE . . . . . . . . . . .GET MONITOR SWITCHES . . . . . . .GET RECOMMENDATIONS FOR HEALTHINDICATOR. . . . . . . . . . . . .GET ROUTINE . . . . . . . . . . . .GET SNAPSHOT . . . . . . . . . . .HELP . . . . . . . . . . . . . . .HISTORY. . . . . . . . . . . . . 196202204205206207208215. 221224225. 226. 229. 230.233237239255256iii

IMPORT . . . . . . . . . . . . . .INITIALIZE TAPE. . . . . . . . . . .INSPECT . . . . . . . . . . . . . .LIST ACTIVE DATABASES. . . . . . . .LIST APPLICATIONS . . . . . . . . .LIST COMMAND OPTIONS . . . . . . .LIST DATABASE DIRECTORY . . . . . .LIST DATABASE PARTITION GROUPS . . .LIST DBPARTITIONNUMS. . . . . . . .LIST DCS APPLICATIONS . . . . . . . .LIST DCS DIRECTORY . . . . . . . . .LIST DRDA INDOUBT TRANSACTIONS . . .LIST HISTORY . . . . . . . . . . . .LIST INDOUBT TRANSACTIONS . . . . .LIST NODE DIRECTORY . . . . . . . .LIST ODBC DATA SOURCES . . . . . . .LIST PACKAGES/TABLES . . . . . . . .LIST TABLESPACE CONTAINERS . . . . .LIST TABLESPACES . . . . . . . . . .LIST UTILITIES . . . . . . . . . . .LOAD . . . . . . . . . . . . . . .LOAD QUERY . . . . . . . . . . . .MIGRATE DATABASE . . . . . . . . .PING . . . . . . . . . . . . . . .PRECOMPILE . . . . . . . . . . . .PRUNE HISTORY/LOGFILE . . . . . . .PUT ROUTINE . . . . . . . . . . . .QUERY CLIENT . . . . . . . . . . .QUIESCE. . . . . . . . . . . . . .QUIESCE TABLESPACES FOR TABLE . . . .QUIT . . . . . . . . . . . . . . .REBIND . . . . . . . . . . . . . .RECOVER DATABASE . . . . . . . . .REDISTRIBUTE DATABASE PARTITION GROUPREFRESH LDAP . . . . . . . . . . .REGISTER . . . . . . . . . . . . .REGISTER XMLSCHEMA . . . . . . . .REGISTER XSROBJECT . . . . . . . . .REORG INDEXES/TABLE . . . . . . . .REORGCHK . . . . . . . . . . . .RESET ADMIN CONFIGURATION . . . . .RESET ALERT CONFIGURATION . . . . .RESET DATABASE CONFIGURATION. . . .RESET DATABASE MANAGERCONFIGURATION . . . . . . . . . .RESET MONITOR . . . . . . . . . . .RESTART DATABASE . . . . . . . . .RESTORE DATABASE . . . . . . . . .REWIND TAPE. . . . . . . . . . . .ROLLFORWARD DATABASE . . . . . . .RUNCMD . . . . . . . . . . . . .RUNSTATS . . . . . . . . . . . . .SET CLIENT . . . . . . . . . . . .SET RUNTIME DEGREE . . . . . . . .SET SERVEROUTPUT . . . . . . . . .SET TABLESPACE CONTAINERS . . . . .SET TAPE POSITION . . . . . . . . .SET UTIL IMPACT PRIORITY . . . . . .SET WORKLOAD . . . . . . . . . . .SET WRITE . . . . . . . . . . . . .START DATABASE MANAGER . . . . . .ivCommand 91511512522523538541543545548549551552554START HADR . . . . . . . . . . . .STOP DATABASE MANAGER . . . . . .STOP HADR . . . . . . . . . . . .TAKEOVER HADR . . . . . . . . . .TERMINATE . . . . . . . . . . . .UNCATALOG DATABASE . . . . . . . .UNCATALOG DCS DATABASE . . . . . .UNCATALOG LDAP DATABASE . . . . .UNCATALOG LDAP NODE . . . . . . .UNCATALOG NODE . . . . . . . . .UNCATALOG ODBC DATA SOURCE . . . .UNQUIESCE . . . . . . . . . . . .UPDATE ADMIN CONFIGURATION . . . .UPDATE ALERT CONFIGURATION . . . .UPDATE ALTERNATE SERVER FOR DATABASEUPDATE ALTERNATE SERVER FOR LDAPDATABASE . . . . . . . . . . . . .UPDATE CLI CONFIGURATION . . . . .UPDATE COMMAND OPTIONS. . . . . .UPDATE CONTACT . . . . . . . . . .UPDATE CONTACTGROUP . . . . . . .UPDATE DATABASE CONFIGURATION . . .UPDATE DATABASE MANAGERCONFIGURATION . . . . . . . . . .UPDATE HEALTH NOTIFICATION CONTACTLIST . . . . . . . . . . . . . . .UPDATE HISTORY . . . . . . . . . .UPDATE LDAP NODE . . . . . . . . .UPDATE MONITOR SWITCHES . . . . . .UPDATE XMLSCHEMA. . . . . . . . .UPGRADE DATABASE . . . . . . . . 594596598599600. 604.606607609611613614Chapter 6. CLPPlus commands. . . . 617. . . . . .!. . . . . ./ . . . . .@ . . . . .@@ . . . . .ACCEPT . . .APPEND . . .BREAK . . .BTITLE . . .CHANGE . .CLEAR . . .COLUMN . .COMPUTE . .CONNECT . .COPY . . . .DEFINE . . .DEL . . . .DESCRIBE . .DISCONNECT .EDIT . . . .EXECUTE . .EXIT . . . .EXPLAIN PLANGET . . . .HELP . . . .HOST . . . .IMPORT CLPPlusINPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .command. . . 45647652653654657659661662663664666