SQL/MP Reference Manual - HP

924 pages
0 downs

Extension: PDF

Please download to get full document.

View again

of 924
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
HP NonStop SQL/MP Reference Manual Abstract This manual describes HP NonStop™ SQL/MP, the HP relational database management system that uses SQL to describe and…
HP NonStop SQL/MP Reference Manual Abstract This manual describes HP NonStop™ SQL/MP, the HP relational database management system that uses SQL to describe and manipulate data in a NonStop SQL/MP database. The manual includes information about SQLCI, the conversational interface to NonStop SQL/MP. Product Version NonStop SQL/MP G07 and H01 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.03 and all subsequent H-series RVUs, G06.27 and all subsequent G-series RVUs, and D46.00 and all subsequent D-series RVUs, until otherwise indicated by its replacement publications. Part Number Published 523352-013 April 2013 Document History Part Number Product Version Published 523352-009 NonStop SQL/MP G07 and H01 July 2008 523352-010 NonStop SQL/MP G07 and H01 August 2008 523352-011 NonStop SQL/MP G07 and H01 November 2008 523352-012 NonStop SQL/MP G07 and H01 August 2010 523352-013 NonStop SQL/MP G07 and H01 April 2013 Legal Notices  Copyright 2013 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein. Export of the information contained in this publication may require authorization from the U.S. Department of Commerce. Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation. Intel, Itanium, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries. Java is a U.S. trademark of Oracle and/or its affiliates. Motif, OSF/1, UNIX, X/Open, and the "X" device are registered trademarks and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries. Open Software Foundation, OSF, the OSF logo, OSF/1, OSF/Motif, and Motif are trademarks of the Open Software Foundation, Inc. OSF MAKES NO WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. OSF shall not be liable for errors contained herein or for incidental consequential damages in connection with the furnishing, performance, or use of this material. © 1990, 1991, 1992, 1993 Open Software Foundation, Inc. This documentation and the software to which it relates are derived in part from materials supplied by the following: © 1987, 1988, 1989 Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment Corporation. © 1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free Software Foundation, Inc. © 1987, 1988, 1989, 1990, 1991 Hewlett-Packard Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International Business Machines Corporation. © 1988, 1989 Massachusetts Institute of Technology. © 1988, 1989, 1990 Mentat Inc. © 1988 Microsoft Corporation. © 1987, 1988, 1989, 1990, 1991, 1992 SecureWare, Inc. © 1990, 1991 Siemens Nixdorf Informationssysteme AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. © 1989, 1990, 1991 Transarc Corporation. This software and documentation are based in part on the Fourth Berkeley Software Distribution under license from The Regents of the University of California. OSF acknowledges the following individuals and institutions for their role in its development: Kenneth C.R.C. Arnold, Gregory S. Couch, Conrad C. Huang, Ed James, Symmetric Computer Systems, Robert Elz. © 1980, 1981, 1982, 1983, 1985, 1986, 1987, 1988, 1989 Regents of the University of California. Printed in the US Hewlett-Packard Company—523352-013 i HP NonStop SQL/MP Reference Manual Index Examples Tables Legal Notices What’s New in This Manual xxix Manual Information xxix New and Changed Information xxx About This Manual xxxiii Related Manuals xxxiii Notation Conventions xxxv HP Encourages Your Comments xxxvii A. Access Options A-1 Access Options on DML Statements A-1 Access Options on DDL Statements A-2 Summary: Effect of Access Options on Concurrency A-3 ADD DEFINE Command A-4 Considerations—ADD DEFINE A-5 Examples—ADD DEFINE A-5 AGGREGATE Functions A-6 Alias A-6 ALLOCATE File Attribute A-7 Considerations—ALLOCATE A-7 ALTER CATALOG Statement A-8 Considerations—ALTER CATALOG A-8 Example—ALTER CATALOG A-9 ALTER COLLATION Statement A-10 Considerations—ALTER COLLATION A-10 Examples—ALTER COLLATION A-11 ALTER DEFINE Command A-11 Considerations—ALTER DEFINE A-12 Example—ALTER DEFINE A-12 ALTER INDEX Statement A-13 Contents HP NonStop SQL/MP Reference Manual—523352-013 ii A. (continued) A. (continued) Considerations—ALTER INDEX A-19 Examples—ALTER INDEX A-26 ALTER PROGRAM Statement A-27 Considerations—ALTER PROGRAM A-28 Example—ALTER PROGRAM A-29 ALTER TABLE Statement A-29 Considerations—ALTER TABLE A-37 Examples—ALTER TABLE A-48 ALTER VIEW Statement A-50 Considerations—ALTER VIEW A-51 Example—ALTER VIEW A-52 APPEND Command A-52 Considerations—APPEND A-53 APPENDCANCEL Command A-56 Considerations—APPENDCANCEL A-57 APPENDRESTART Command A-58 Considerations—APPENDRESTART A-59 AS Clause A-60 Decorations A-64 Examples—AS A-65 AS DATE/TIME Clause A-67 Examples—AS DATE/TIME A-69 ASCII Character Set A-70 AUDIT File Attribute A-74 Considerations—AUDIT A-74 AUDITCOMPRESS File Attribute A-75 Considerations—AUDITCOMPRESS A-75 Audited Tables A-76 AVG Function A-76 Considerations—AVG A-77 Examples—AVG A-77 B. BACKUP Utility B-1 BASETABS Table B-1 BEGIN DECLARE SECTION Directive B-2 Examples—BEGIN DECLARE SECTION B-2 BEGIN WORK Statement B-3 Contents HP NonStop SQL/MP Reference Manual—523352-013 iii B. (continued) B. (continued) Example—BEGIN WORK B-3 BETWEEN Predicate B-4 Considerations—BETWEEN B-4 Examples—BETWEEN B-4 BLOCKSIZE File Attribute B-5 Considerations—BLOCKSIZE B-5 BREAK FOOTING Command B-6 Considerations—BREAK FOOTING B-6 Example—BREAK FOOTING B-7 BREAK ON Command B-8 Considerations—BREAK ON B-8 Examples—BREAK ON B-9 BREAK TITLE Command B-10 Considerations—BREAK TITLE B-10 Example—BREAK TITLE B-11 BUFFERED File Attribute B-11 Considerations—BUFFERED B-11 C. CANCEL Command C-1 Consideration—CANCEL C-1 Example—CANCEL C-1 CASE Expression C-2 Considerations—CASE Expression C-3 Examples—CASE Expression C-4 CAST Function C-4 Valid Considerations—CAST C-5 Examples—CAST C-6 CATALOG Command C-7 Considerations—CATALOG C-7 Example—CATALOG C-7 Catalogs C-8 Operations on Catalog Tables C-9 CATALOGS Table C-11 CENTER_REPORT Option C-12 Consideration—CENTER_REPORT C-12 Example—CENTER REPORT C-12 Character Data Types C-13 Contents HP NonStop SQL/MP Reference Manual—523352-013 iv C. (continued) C. (continued) Character Expressions C-14 Considerations—Character Expressions C-15 Examples—Character Expressions C-17 Character Sets C-17 ISO 8859 Character Sets C-18 Kanji Character Set C-18 KSC5601 Character Set C-19 CHAR_LENGTH Function C-20 Considerations—CHAR_LENGTH Function C-20 Examples—CHAR_LENGTH Function C-20 CLEANUP Command C-21 Considerations—CLEANUP C-22 CLEANUP Exception Cases C-24 Example—CLEANUP C-25 CLEARONPURGE File Attribute C-26 Considerations—CLEARONPURGE C-26 CLOSE Statement C-26 Considerations—CLOSE C-27 Example—CLOSE C-27 Clustering Keys C-28 Example—CLUSTERING KEYS C-28 COLLATE Clause C-29 Collation Definitions C-30 Comment and Escape Characters in Collation Definitions C-30 The LC_COLLATE Section of a Collation Definition C-32 The LC_CTYPE Section of a Collation Definition C-35 The LC_TDMCODESET Section of a Collation Definition C-37 Considerations—Collation Definitions C-38 Examples—Collation Definitions C-38 Collations C-43 Column Identifier C-44 Example—Column Identifiers C-44 Columns C-45 COLUMNS Table C-46 COMMENT Statement C-48 Considerations—COMMENT C-48 Examples—COMMENT C-49 Comments C-50 Contents HP NonStop SQL/MP Reference Manual—523352-013 v C. (continued) C. (continued) Example—Comments C-50 COMMENTS Table C-51 COMMIT Option C-52 Considerations—COMMIT Option C-55 Example—COMMIT Option C-55 COMMIT WORK Statement C-56 Considerations—COMMIT WORK C-57 Example—COMMIT WORK C-57 Comparison Predicate C-58 Considerations—Comparison Predicate C-58 Examples—Comparison Predicate C-60 COMPUTE_TIMESTAMP Function C-62 Considerations—COMPUTE_TIMESTAMP C-62 Example—COMPUTE_TIMESTAMP C-63 CONCAT Clause C-63 Considerations—CONCAT C-64 Example—CONCAT C-64 Concurrency C-65 Effect of VSBB on Concurrency C-68 Constraints C-69 CONSTRNT Table C-70 CONTINUE Statement C-70 Considerations—CONTINUE C-71 Examples—CONTINUE C-72 CONTROL EXECUTOR Directive C-73 Considerations—CONTROL EXECUTOR C-73 Example—CONTROL EXECUTOR C-73 CONTROL QUERY Directive C-74 Considerations—CONTROL QUERY C-76 Examples—CONTROL QUERY C-76 CONTROL TABLE Directive C-77 Considerations—CONTROL TABLE C-85 Examples—CONTROL TABLE C-92 CONVERT Command C-94 CONVERT Behavior C-99 Enscribe Files and DDL Record Definitions C-100 DDL Primary Keys and Alternate Keys C-100 DDL Clause Mapping C-101 Contents HP NonStop SQL/MP Reference Manual—523352-013 vi C. (continued) C. (continued) Conversion of DDL Elementary Items C-103 DDL Groups C-107 Physical File Attributes of Tables and Indexes C-107 Partition Attributes of Tables and Indexes C-108 Examples—CONVERT C-108 CONVERTTIMESTAMP Function C-113 Example—CONVERTTIMESTAMP C-113 COPY Command C-113 Considerations—COPY C-122 Enscribe Field Formats C-126 Field Conversions C-126 Examples—COPY C-127 Correlation Names C-128 COUNT Function C-130 Considerations—COUNT C-130 Example—COUNT C-130 CPRLSRCE Table C-131 CPRULES Table C-131 CREATE CATALOG Statement C-132 Considerations—CREATE CATALOG C-134 Examples—CREATE CATALOG C-135 CREATE COLLATION Statement C-137 Consideration—CREATE COLLATION C-138 Example—CREATE COLLATION C-138 CREATE CONSTRAINT Statement C-139 Considerations—CREATE CONSTRAINT C-140 Examples—CREATE CONSTRAINT C-141 CREATE INDEX Statement C-142 Considerations—CREATE INDEX C-147 Examples—CREATE INDEX C-150 CREATE SYSTEM CATALOG Command C-152 Considerations—CREATE SYSTEM CATALOG C-152 Examples—CREATE SYSTEM CATALOG C-153 CREATE TABLE Statement C-154 Considerations—CREATE TABLE C-161 Examples—CREATE TABLE C-162 CREATE VIEW Statement C-166 Considerations—CREATE VIEW C-169 Contents HP NonStop SQL/MP Reference Manual—523352-013 vii C. (continued) C. (continued) Examples—CREATE VIEW C-171 CURRENT Function C-173 Example—CURRENT C-173 CURRENT_TIMESTAMP Function C-174 Considerations—CURRENT_TIMESTAMP C-174 Example—CURRENT_TIMESTAMP C-174 Cursors C-175 Cursor Position C-176 Cursor Stability C-176 C89 C-176 D. Data Dictionary D-1 Data Types D-1 DATE Data Type D-8 Example—DATE Data Type D-8 DATE_FORMAT Option D-8 Example—DATE_FORMAT D-8 Date-Time Data Types D-9 Date-Time Functions D-10 Date-Time Literals D-10 Examples—Date-Time Literals D-13 DATEFORMAT Function D-14 Example—DATEFORMAT D-14 DATETIME Data Type D-15 Considerations—DATETIME DATA TYPE D-15 Example—DATETIME D-16 DAYOFWEEK Function D-17 Example—DAYOFWEEK D-17 DCL (Data Control Language) Statements D-18 DCOMPRESS File Attribute D-18 Considerations—DCOMPRESS D-18 DDL (Data Definition Language) Statements D-20 DDL Statements D-20 Deadlocks D-22 DECIMAL_POINT Option D-22 Considerations—DECIMAL_POINT D-22 Examples—DECIMAL_POINT D-23 Contents HP NonStop SQL/MP Reference Manual—523352-013 viii D. (continued) D. (continued) DECLARE CURSOR Statement D-23 Considerations—DECLARE CURSOR D-24 Examples—DECLARE CURSOR D-25 DEFAULT Clause D-26 Example—DEFAULT D-27 DEFINEs D-27 Using DEFINEs D-28 Using DEFINEs From SQLCI D-30 Using DEFINEs With SQL Programs D-31 DEFINE Attributes D-32 DEFINEs of Class CATALOG D-33 DEFINEs of Class DEFAULT D-33 DEFINEs of Class MAP D-33 Summary of DEFINE Attributes D-34 Examples—DEFINEs Used With SQL Programs D-37 DELETE DEFINE Command D-38 Examples—DELETE DEFINE D-38 DELETE Statement D-39 Considerations—DELETE D-40 Examples—DELETE D-40 DESCRIBE INPUT Statement D-41 Example—DESCRIBE INPUT D-43 DESCRIBE Statement D-43 Example—DESCRIBE D-45 Detail Alias D-46 DETAIL Command D-47 Considerations—DETAIL D-50 Examples—DETAIL D-51 DISPLAY STATISTICS Command D-52 Considerations—DISPLAY STATISTICS D-52 Example—DISPLAY STATISTICS D-53 DISPLAY USE OF Command D-54 Considerations—DISPLAY USE OF D-54 Examples—DISPLAY USE OF D-56 DML Statements D-58 DOWNGRADE CATALOG Command D-58 Considerations—DOWNGRADE CATALOG D-59 Examples—DOWNGRADE CATALOG D-60 Contents HP NonStop SQL/MP Reference Manual—523352-013 ix D. D. (continued)DOWNGRADE SYSTEM CATALOG Command D-61 Considerations—DOWNGRADE SYSTEM CATALOG D-61 Example—DOWNGRADE SYSTEM CATALOG D-62 DROP Statement D-63 Considerations—DROP D-63 Example—DROP D-66 DROP SYSTEM CATALOG Command D-66 Considerations—DROP SYSTEM CATALOG D-66 Examples—DROP SYSTEM CATALOG D-67 DSL Statements D-67 DSLACK File Attribute D-68 Considerations—DSLACK D-68 DUP Command D-69 Considerations—DUP D-74 Examples—DUP D-78 Dynamic SQL D-80 Summary of Dynamic SQL Statements D-80 Determining When to Use Dynamic SQL D-80 Features of Dynamic SQL D-81 E. EDIT Command E-1 Examples—EDIT E-1 Embedded SQL E-2 END DECLARE SECTION Directive E-3 Example—END DECLARE SECTION E-3 ENV Command E-3 Considerations—ENV E-3 Example—ENV E-4 ERROR Command E-4 Examples—ERROR E-5 Error Messages E-6 EXECUTE Statement E-7 Considerations—EXECUTE E-9 Examples—EXECUTE E-10 EXECUTE IMMEDIATE Statement E-11 Considerations—EXECUTE IMMEDIATE E-11 Example—EXECUTE IMMEDIATE E-11 Contents HP NonStop SQL/MP Reference Manual—523352-013 x E. (continued) E. (continued) EXISTS Predicate E-12 Examples—EXISTS E-12 EXIT Command E-13 Example—EXIT E-13 EXPLAIN Directive E-13 Considerations—EXPLAIN E-13 Examples—EXPLAIN E-20 Expressions E-21 Numeric, Date-Time, and Interval Expressions E-22 Considerations—Expressions E-23 Examples—Expressions E-26 EXTEND Function E-28 Considerations—EXTEND E-29 Examples—EXTEND E-29 EXTENT File Attribute E-30 Considerations—EXTENT E-31 F. FC Command F-1 Considerations—FC F-1 Examples—FC F-2 FETCH Statement F-3 Considerations—FETCH F-4 Examples—FETCH F-4 File Attributes F-6 File Attributes of SQL Objects F-7 File Organizations F-8 FILEINFO Command F-9 Considerations—FILEINFO F-11 BRIEF Display for SQL Objects and Guardian Files F-11 DETAIL Display for Objects (Except Views) and Guardian Files F-14 DETAIL Display for Views F-20 BRIEF and DETAIL Display for OSS Files F-21 EXTENTS Display F-21 STATISTICS Display F-22 Examples—FILEINFO F-22 FILENAMES Command F-25 Example—FILENAMES F-25
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks