DB2 For Linux,UNIX,andWindows - Unibo.it
DB2 for Linux, UNIX, and Windows Version 9 Release 7 Command Reference Updated July, 2012 SC27-2439-03
DB2 for Linux, UNIX, and Windows Version 9 Release 7 Command Reference Updated July, 2012 SC27-2439-03
Note Before using this information and the product it supports, read the general information under Appendix D, “Notices,” on page 1215. Edition Notice This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements 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/order v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/ planetwide To 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 any way 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 Contract with IBM Corp.
Contents About this book . . . . . . . . . . . vii Who should use this book . . . . . . . . . vii How this book is structured . . . . . . . . . vii Highlighting conventions . . . . . . . . . viii How to read the syntax diagrams . . . ix Chapter 1. Command line processor (CLP) . . . . . . . . . . . . . . . . 1 Command line processor features . . . . . . db2 - Command line processor invocation . . . Command line processor options . . . . . . Command line processor return codes . . . . Invoking command help from the command line processor . . . . . . . . . . . . . . Invoking message help from the command line processor . . . . . . . . . . . . . . . 2 . 8 . 10 . 18 . 18 . 19 Chapter 2. Using command line SQL statements and XQuery statements . . 21 Chapter 3. Command line processor plus (CLPPlus) . . . . . . . . . . . 29 Installing CLPPlus on existing IBM data server clients . . . . . . . . . . . . . . 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 CLPPlus LDAP support in CLPPlus . . . . . . . CREATE DATABASE in CLPPlus . . . . . Running a script file in CLPPlus . . . . . Skipping and interrupting CLPPlus commands Comments 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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 30 31 33 35 35 37 38 39 43 44 45 46 47 49 53 54 54 55 56 Chapter 4. How to read command syntax help . . . . . . . . . . . . . 59 Chapter 5. CLP commands . . . . . . 63 ACTIVATE DATABASE . ADD CONTACT . . . ADD CONTACTGROUP . ADD DBPARTITIONNUM . . . . Copyright IBM Corp. 1993, 2012 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 66 67 68 ADD 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 CONFIGURATION GET DATABASE MANAGER MONITOR SWITCHES . . . . . . . . . . . . . GET DESCRIPTION FOR HEALTH INDICATOR GET HEALTH NOTIFICATION CONTACT LIST GET HEALTH SNAPSHOT. . . . . . . . GET INSTANCE . . . . . . . . . . . GET MONITOR SWITCHES . . . . . . . GET RECOMMENDATIONS FOR HEALTH INDICATOR. . . . . . . . . . . . . GET ROUTINE . . . . . . . . . . . . GET SNAPSHOT . . . . . . . . . . . HELP . . . . . . . . . . . . . . . HISTORY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 73 76 78 81 90 110 113 115 118 119 121 123 125 129 131 133 134 152 154 156 158 161 162 172 173 174 175 177 178 180 181 182 192 194 196 202 204 205 206 207 208 215 . 221 224 225 . 226 . 229 . 230 . . . . . 233 237 239 255 256 iii
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 GROUP REFRESH LDAP . . . . . . . . . . . REGISTER . . . . . . . . . . . . . REGISTER XMLSCHEMA . . . . . . . . REGISTER XSROBJECT . . . . . . . . . REORG INDEXES/TABLE . . . . . . . . REORGCHK . . . . . . . . . . . . RESET ADMIN CONFIGURATION . . . . . RESET ALERT CONFIGURATION . . . . . RESET DATABASE CONFIGURATION. . . . RESET DATABASE MANAGER CONFIGURATION . . . . . . . . . . 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 . . . . . . iv Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 285 286 293 295 297 298 301 303 304 306 308 310 314 316 319 320 323 325 330 332 375 381 382 384 411 413 414 415 419 422 423 427 433 442 444 447 449 451 468 479 481 483 . . . . . . . . . . . . . . . . . 485 487 489 491 511 512 522 523 538 541 543 545 548 549 551 552 554 START 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 DATABASE UPDATE ALTERNATE SERVER FOR LDAP DATABASE . . . . . . . . . . . . . UPDATE CLI CONFIGURATION . . . . . UPDATE COMMAND OPTIONS. . . . . . UPDATE CONTACT . . . . . . . . . . UPDATE CONTACTGROUP . . . . . . . UPDATE DATABASE CONFIGURATION . . . UPDATE DATABASE MANAGER CONFIGURATION . . . . . . . . . . UPDATE HEALTH NOTIFICATION CONTACT LIST . . . . . . . . . . . . . . . UPDATE HISTORY . . . . . . . . . . UPDATE LDAP NODE . . . . . . . . . UPDATE MONITOR SWITCHES . . . . . . UPDATE XMLSCHEMA. . . . . . . . . UPGRADE DATABASE . . . . . . . . . . . . . . . . . . . . . . . 562 565 568 570 574 575 577 578 579 580 581 582 584 586 591 . . . . . . 593 594 596 598 599 600 . 604 . . . . . . 606 607 609 611 613 614 Chapter 6. CLPPlus commands. . . . 617 . . . . . . !. . . . . . / . . . . . @ . . . . . @@ . . . . . ACCEPT . . . APPEND . . . BREAK . . . BTITLE . . . CHANGE . . CLEAR . . . COLUMN . . COMPUTE . . CONNECT . . COPY . . . . DEFINE . . . DEL . . . . DESCRIBE . . DISCONNECT . EDIT . . . . EXECUTE . . EXIT . . . . EXPLAIN PLAN GET . . . . HELP . . . . HOST . . . . IMPORT CLPPlus INPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618 619 620 621 622 623 625 626 627 629 631 633 638 640 642 644 645 647 652 653 654 657 659 661 662 663 664 666
LIST . . . . . . . PAUSE . . . . . . PRINT. . . . . . . PROMPT . . . . . . QUIT . . . . . . . REMARK. . . . . . REORGCHK . . . . REPFOOTER . . . . REPHEADER . . . . RUN . . . . . . . SAVE . . . . . . . SET. . . . . . . . SPOOL . . . . . . SHOW . . . . . . START . . . . . . TTITLE . . . . . . UNDEFINE . . . . . WHENEVER OSERROR . WHENEVER SQLERROR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667 669 670 671 672 673 674 676 677 679 680 681 689 690 691 692 694 695 697 Chapter 7. System commands . . . . 701 dasauto - Autostart DB2 administration server . . dascrt - Create a DB2 administration server . . . dasdrop - Remove a DB2 administration server . . dasmigr - Migrate the DB2 administration server dasupdt - Update DAS . . . . . . . . . . db2 deinstall - Uninstall DB2 database products, features, or languages . . . . . . . . . . db2 install - Install DB2 database product . . . . db2 local ps - DB2 process status for Linux/UNIX db2acsutil - Manage DB2 snapshot backup objects db2addicons - Create main menu entries for DB2 tools . . . . . . . . . . . . . . . . db2admin - DB2 administration server . . . . . db2adutl - Managing DB2 objects within TSM . . db2advis - DB2 design advisor . . . . . . . db2audit - Audit facility administrator tool . . . db2batch - Benchmark tool . . . . . . . . . db2bfd - Bind file description tool . . . . . . db2ca - Start the Configuration Assistant . . . . db2caem - Capture activity event monitor data tool db2cap - CLI/ODBC static package binding tool db2cat - System catalog analysis . . . . . . . db2cc - Start control center . . . . . . . . . db2cfexp - Connectivity configuration export tool db2cfimp - Connectivity configuration import tool db2chglibpath - Modify the embedded runtime library search path . . . . . . . . . . . db2chgpath - Change embedded runtime path . . db2ckbkp - Check backup . . . . . . . . . db2cklog - Check archive log files command . . . db2ckmig - Database pre-migration . . . . . . db2ckrst - Check incremental restore image sequence . . . . . . . . . . . . . . . db2ckupgrade - Check database for upgrade . . . db2cli - DB2 interactive CLI . . . . . . . . db2cmd - Open DB2 command window . . . . db2cptsa - Install or update DB2 HA scripts . . . db2dart - Database analysis and reporting tool . . db2daslevel - Show DAS level . . . . . . . . db2dclgn - Declaration generator . . . . . . . 702 703 704 705 707 709 712 715 717 721 722 724 734 741 750 760 761 762 766 768 770 772 774 775 777 779 783 785 786 788 791 805 806 807 813 814 db2diag - db2diag logs analysis tool. . . . . . db2drdat - DRDA trace . . . . . . . . . . db2drvmp - DB2 database drive map . . . . . db2empfa - Enable multipage file allocation . . . db2envar.bat - Set environment of the current command window . . . . . . . . . . . db2eva - Event analyzer . . . . . . . . . . db2evmon - Event monitor productivity tool . . . db2evtbl - Generate event monitor target table definitions . . . . . . . . . . . . . . db2exfmt - Explain table format . . . . . . . db2exmig - Migrate explain tables . . . . . . db2expln - SQL and XQuery Explain . . . . . db2extsec - Set permissions for DB2 objects . . . db2flsn - Find log sequence number . . . . . . db2fm - DB2 fault monitor . . . . . . . . . db2fmcu - DB2 fault monitor controller. . . . . db2fodc - DB2 first occurrence data collection . . db2fopt - Specify query optimizer parameters . . db2fs - First steps . . . . . . . . . . . . db2gcf - Control DB2 instance . . . . . . . . db2gov - DB2 governor . . . . . . . . . . db2govlg - DB2 governor log query . . . . . . db2gpmap - Get distribution map . . . . . . db2hc - Start health center . . . . . . . . . db2iauto - Autostart instance . . . . . . . . db2iclus - Microsoft cluster server . . . . . . db2icrt - Create instance . . . . . . . . . . db2idrop - Remove instance . . . . . . . . db2IdentifyType1 - Type-1 index identification tool db2ilist - List instances . . . . . . . . . . db2imigr - Migrate instance . . . . . . . . db2inidb - Initialize a mirrored database . . . . db2inspf - Format inspect results . . . . . . . db2iprune - Reduce installation image size . . . db2isetup - Start instance creation interface . . . db2iupdt - Update instances . . . . . . . . db2iupgrade - Upgrade instance . . . . . . . db2jdbcbind - DB2 JDBC package binder . . . . db2ldcfg - Configure LDAP environment . . . . db2level - Show DB2 service level . . . . . . db2licm - License management tool . . . . . . db2listvolumes - Display GUIDs for all disk volumes . . . . . . . . . . . . . . . db2logsForRfwd - List logs required for rollforward recovery . . . . . . . . . . . db2look - DB2 statistics and DDL extraction tool db2ls - List installed DB2 products and features db2move - Database movement tool . . . . . . db2mqlsn - MQ listener . . . . . . . . . . db2mscs - Set up Windows failover utility. . . . db2mtrk - Memory tracker . . . . . . . . . db2nchg - Change database partition server configuration . . . . . . . . . . . . . db2ncrt - Add database partition server to an instance . . . . . . . . . . . . . . . db2ndrop - Drop database partition server from an instance . . . . . . . . . . . . . . . db2nrcfg - Non-root install configuration tool . . db2nrupdt - Non-root-installed instance update db2nrupgrade - Upgrade non-root instance . . . Contents 817 833 835 837 838 839 841 843 845 848 849 858 860 862 864 866 877 879 880 883 885 886 887 888 889 892 896 898 901 902 904 907 908 910 912 916 919 921 922 923 926 927 928 940 942 951 955 959 963 965 967 968 969 970 v
db2osconf - Utility for kernel parameter values . . 971 db2pd - Monitor and troubleshoot DB2 database 974 db2pdcfg - Configure DB2 database for problem determination behavior. . . . . . . . . . 1058 db2perfc - Reset database performance values 1064 db2perfi - Performance counters registration utility . . . . . . . . . . . . . . . 1066 db2perfr - Performance monitor registration tool 1067 db2rbind - Rebind all packages . . . . . . . 1068 db2relocatedb - Relocate database . . . . . . 1070 db2rfe - Enable root features for non-root install 1076 db2rfpen - Reset rollforward pending state . . . 1078 db2rmicons - Remove DB2 tools from main menu 1079 db2rspgn - Response file generator . . . . . . 1080 db2sampl - Create sample database . . . . . 1081 db2schex - Active Directory schema extension 1084 db2set - DB2 profile registry . . . . . . . . 1086 db2setup - Install DB2 . . . . . . . . . . 1090 db2snapcore - DB2 snapcore command for Linux and Solaris . . . . . . . . . . . . . . 1092 db2start - Start DB2 . . . . . . . . . . . 1094 db2stat - DB2 process status for Windows . . . 1095 db2stop - Stop DB2 . . . . . . . . . . . 1096 db2support - Problem analysis and environment collection tool . . . . . . . . . . . . . 1097 db2swtch - Switch default DB2 copy and database client interface copy . . . . . . . . . . . 1111 db2sync - Start DB2 synchronizer . . . . . . 1113 db2systray - Start DB2 system tray . . . . . . 1114 db2tapemgr - Manage log files on tape . . . . 1116 db2tbst - Get table space state . . . . . . . 1119 db2tdbmgr - Migrate tools catalog database . . . 1120 db2top - DB2 monitoring tool . . . . . . . 1121 db2trc - Trace . . . . . . . . . . . . . 1126 db2trcoff - Off trace options for db2trc . . . . 1139 db2trcon - On trace options for db2trc . . . . . 1140 db2unins - Uninstall DB2 database products, features, or languages . . . . . . . . . . 1142 db2untag - Release container tag . . . . . . 1144 db2updserv - Show product updates . . . . . 1145 db2updv97 - Update database to Version 9.7 fix pack . . . . . . . . . . . . . . . . 1146 db2val - DB2 copy validation tool . . . . . . 1148 db2xdbmig - Migrate XSR objects . . . . . . 1150 db2xprt - Format trap file . . . . . . . . . 1151 disable MQFunctions - Disable WebSphere MQ functions . . . . . . . . . . . . . . 1152 doce deinstall - Uninstall DB2 Information Center 1153 doce install - Install DB2 Information Center . . 1154 enable MQFunctions - Enable WebSphere MQ functions . . . . . . . . . . . . . . 1156 installDSDriver - Extract IBM Data Server Driver components . . . . . . . . . . . . . 1159 vi Command Reference installFixPack - Update installed DB2 database products. . . . . . . . . . . . . . setup - Install DB2 . . . . . . . . . . . 1161 . 1165 Chapter 8. DB2 Text Search commands . . . . . . . . . . . . 1167 db2ts ALTER INDEX . . . . . . db2ts CLEANUP FOR TEXT . . . . db2ts CLEAR COMMAND LOCKS. . db2ts CLEAR EVENTS FOR INDEX . db2ts CREATE INDEX . . . . . . db2ts DISABLE DATABASE FOR TEXT db2ts DROP INDEX . . . . . . . db2ts ENABLE DATABASE FOR TEXT db2ts HELP . . . . . . . . . db2ts START FOR TEXT . . . . . db2ts STOP FOR TEXT . . . . . . db2ts UPDATE INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1198 Appendix A. Naming conventions Naming conventions . . . . . . . 1168 1172 1173 1175 1177 1183 1186 1188 1190 1192 1193 1194 1197 Appendix B. File type modifiers and delimiters . . . . . . . . . . . . 1199 File type modifiers for the export, import and load utilities . . . . . . . . . . . . Delimiter considerations for moving data . . . 1200 . 1201 Appendix C. Overview of the DB2 technical information . . . . . . . 1203 DB2 technical library in hardcopy or PDF format Ordering printed DB2 books . . . . . . . . Displaying SQL state help from the command line processor . . . . . . . . . . . . . . Accessing different versions of the DB2 Information Center . . . . . . . . . . . Displaying topics in your preferred language in the DB2 Information Center . . . . . . . . Updating the DB2 Information Center installed on your computer or intranet server . . . . . . Manually updating the DB2 Information Center installed on your computer or intranet server . . DB2 tutorials . . . . . . . . . . . . . DB2 troubleshooting information . . . . . . Terms and Conditions . . . . . . . . . . 1204 1206 1207 1207 1208 1209 1210 1212 1212 1213 Appendix D. Notices . . . . . . . . 1215 Index . . . . . . . . . . . . . . 1219
About this book This book provides information about the use of system commands and the IBM DB2 command line processor (CLP) to execute database administrative functions. Who should use this book It is assumed that the reader has an understanding of database administration and a knowledge of Structured Query Language (SQL). How this book is structured This book provides the reference information needed to use the CLP, system, and DB2 Text Search commands. The major subject areas discussed in the chapters of this book are as follows: Using the command line processor (CLP) v Chapter 1, “Command line processor (CLP),” explains how to invoke and use the command line processor, and describes the CLP options. v Chapter 2, “Using command line SQL statements and XQuery statements,” provides information on how to use SQL statements from the command line. Using the command line processor plus (CLPPlus) v Chapter 3, “Command line processor plus (CLPPlus),” describes how to install, invoke, and use the command line processor plus interface. Command usage help v Chapter 4, “How to read command syntax help,” describes how to invoke command help screens and explains the command help screen syntax conventions that are employed. CLP commands v Chapter 5, “CLP commands,” describes all of the database manager commands, listed alphabetically. CLPPlus commands v Chapter 6, “CLPPlus commands,” alphabetically lists and describes all of the commands that can be used in the CLPPlus interface. System commands v Chapter 7, “System commands,” describes all of the commands, listed alphabetically, that can be entered at an operating system command prompt or in a shell script to access the database manager. DB2 Text Search commands v Chapter 8, “DB2 Text Search commands,” describes all of the Text Search commands, listed alphabetically, that can be entered at an operating system command prompt prefixed with db2ts. Appendixes v Appendix A, “Naming conventions,” describes the conventions used to name objects such as databases and tables. Copyright IBM Corp. 1993, 2012 vii
How this book is structured v Appendix B, “File type modifiers and delimiters,” describes the file type modifiers for the load, import and export utilities. In addition, delimiter considerations for moving data is also presented. Highlighting conventions The following highlighting conventions are used in this book. Bold Indicates commands, keywords, and other items whose names are predefined by the system. Commands written in uppercase are CLP commands, whereas commands written in lowercase are system commands. Italics Indicates one of the following: v Names or values (variables) that must be supplied by the user v General emphasis v The introduction of a new term v A reference to another source of information Monospace Indicates one of the following: v Files and directories v Information that you are instructed to type at a command prompt or in a window v Examples of specific data values v Examples of text similar to what might be displayed by the system v Examples of system messages v Samples of programming code viii Command Reference
How to read the syntax diagrams SQL syntax is described using the structure defined as follows: Read the syntax diagrams from left to right and top to bottom, following the path of the line. The symbol indicates the beginning of a syntax diagram. The symbol indicates that the syntax is continued on the next line. The symbol indicates that the syntax is continued from the previous line. The symbol indicates the end of a syntax diagram. Syntax fragments start with the symbol and end with the symbol. Required items appear on the horizontal line (the main path). required item Optional items appear below the main path. required item optional item If an optional item appears above the main path, that item has no effect on execution, and is used only for readability. optional item required item If you can choose from two or more items, they appear in a stack. If you must choose one of the items, one item of the stack appears on the main path. required item required choice1 required choice2 If choosing one of the items is optional, the entire stack appears below the main path. required item optional choice1 optional choice2 Copyright IBM Corp. 1993, 2012 ix
How to read the syntax diagrams If one of the items is the default, it will appear above the main path, and the remaining choices will be shown below. default choice required item optional choice optional choice An arrow returning to the left, above the main line, indicates an item that can be repeated. In this case, repeated items must be separated by one or more blanks. required item repeatable item If the repeat arrow contains a comma, you must separate repeated items with a comma. , required item repeatable item A repeat arrow above a stack indicates that you can make more than one choice from the stacked items or repeat a single choice. Keywords appear in uppercase (for example, FROM). They must be spelled exactly as shown. Variables appear in lowercase (for example, column-name). They represent user-supplied names or values in the syntax. If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax. Sometimes a single variable represents a larger fragment of the syntax. For example, in the following diagram, the variable parameter-block represents the whole syntax fragment that is labeled parameter-block: required item parameter-block parameter-block: parameter1 parameter2 parameter3 parameter4 Adjacent segments occurring between “large bullets” (*) may be specified in any sequence. required item item1 x Command Reference * item2 * item3 * item4
How to read the syntax diagrams The above diagram shows that item2 and item3 may be specified in either order. Both of the following are valid: required item item1 item2 item3 item4 required item item1 item3 item2 item4 How to read the syntax diagrams xi
How to read the syntax diagrams xii Command Reference
Chapter 1. Command line processor (CLP) Copyright IBM Corp. 1993, 2012 1
Command line processor features Command line processor features The command line processor operates as follows: v The CLP command (in either case) is typed at the command prompt. v The command is sent to the command shell by pressing the ENTER key. v Output is automatically directed to the standard output device. v Piping and redirection are supported. v The user is notified of successful and unsuccessful completion. v Following execution of the command, control returns to the operating system command prompt, and the user can enter more commands. v When the CLP is called with a file input option, it will automatically set the CLIENT APPLNAME special register to CLP filename. You can start the command line processor by either: v typing the db2 command, or, v on Linux operating systems, click Main Menu and, select IBM DB2 Command Line Processor. Certain CLP commands and SQL statements require that the server instance is running and a database connection exists. Connect to a database by doing one of the following: v Issue the SQL statement: db2 connect to database v Establish an implicit connection to the default database defined by the DB2 registry variable DB2DBDFT. If a command exceeds the character limit allowed at the command prompt, a backslash (\) can be used as the line continuation character. When the command line processor encounters the line continuation character, it reads the next line and concatenates the characters contained on both lines. Alternatively, the -t option can be used to set a different line termination character. The command line processor recognizes a string called NULL as a null string. Fields that have been set previously to some value can later be set to NULL. For example, db2 update database manager configuration using tm database NULL sets the tm database field to NULL. This operation is case sensitive. A lowercase null is not interpreted as a null string, but rather as a string containing the letters null. Customizing the Command Line Processor It is possible to customize the interactive input prompt by using the DB2 CLPPROMPT registry variable. This registry variable can be set to any text string of maximum length 100 and can contain the tokens %i, %ia, %d, %da and %n. Specific values will be substituted for these tokens at run-time. Table 1. DB2 CLPPROMPT tokens and run-time values 2 Command Reference DB2 CLPPROMPT token Value at run-time %ia Authorization ID of the current instance attachment
Command line processor features Table 1. DB2 CLPPROMPT tokens and run-time values (continued) DB2 CLPPROMPT token Value at run-t
db2_install - Install DB2 database product.712 db2_local_ps - DB2 process status for Linux/UNIX 715 db2acsutil - Manage DB2 snapshot backup objects 717 db2addicons - Create main menu entries for DB2 tools .721 db2admin - DB2 administration server .722 db2adutl - Managing DB2 objects within TSM . . 724
For the first time ever, DB2 functionality which has previously been available on Linux, Unix, and Windows (LUW) is now available for Mac OS X. These DB2 products are available free of charge through the . DB2 Express-C program. The DB2 Express-C program gives you access to a DB2 data server (DB2 Express-C) and DB2 Client for Mac OS X.
IBM DB2 10.1 for Linux,UNIX,andWindows Data Movement Utilities Guide and Reference SC27-3869-00. IBM DB2 10.1 for Linux,UNIX,andWindows Data Movement Utilities Guide and . Set Integrity Pending state v the target table has triggers and you want them fired References Importing data Table 4. Export utility Method Export utility
Linux, UNIX, and Windows Stanislaw Bartkowski Ciaran De Buitlear Adrian Kalicki Michael Loster Marcin Marczewski Anas Mosaad Jan Nelken Mohamed Soliman Klaus Subtil Marko Vrhovnik Karol Zimnol Learn DB2 HADR setup, administration, monitoring, and preferred practices Use PowerHA, MSWFC, Tivoli SA MP with DB2, and DB2 HADR Protect data with DB2
Linux in a Nutshell Linux Network Administrator’s Guide Linux Pocket Guide Linux Security Cookbook Linux Server Hacks Linux Server Security Running Linux SELinux Understanding Linux Network Internals Linux Books Resource Center linux.oreilly.comis a complete catalog of O’Reilly’s books on Linux and Unix and related technologies .
Oracle, SQL Server, and Informix Warehouse and Content Management improvements DB2 Evolution IBM DB2 Family Hosts DB2 UDB for OS/390 DB2 for VSE & VM DB2 UDB for AS/400 Personal OS/2 Win 95, 98 Win NT/2000 Linux Enterprise - Extended AIX Linux Win NT/2000 Solaris HP-UX NUMA-Q Workgroup Linux Win NT/2000 OS/2 AIX Solaris HP-UX Enterprise OS/2 .
With Db2 11.1, IBM introduced the concept of Modification Packs. A Modification Pack (also referred to as Mod or MP) introduces new functions to the Db2 product. For the IBM Db2 Modification Packs and Fix Packs, we mostly use abbreviations such as Db2 11.1 Mod 2 Fix Pack 2, or even shorter, simply Db2 11.1 MP2 FP2.
DB2 Logs, but Were Afraid to Ask Paul Pendle, Rocket Software Session: 16906. Agenda DB2 Logs Introduction DB2 Logging Components Log Performance How to Leverage the DB2 Log DIY Log Analysis DB2 Log Analysis Tool. DB2 Log Introduction Central to every updating transaction
8 UK BANKING SECTOR – AN INVESTMENT OPPORTUNITY UK Finance Meanwhile UK consumers exhibit one of the highest card usage levels in Europe. Increasingly consumers in the UK are turning to cards to make payments, whether by debit or credit, contactless or chip & PIN, online or in-store, on day to day purchases or on major spends. Overall 47 per cent of all payments were made by card in 2018 and .
