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.
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.
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 About This Manual xxxiii Related Manuals xxxiii Notation Conventions xxxv HP Encourages Your Comments xxx xxxvii A.
Contents A. A.
Contents B. 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.
Contents C. C.
Contents C. C.
Contents C. C.
Contents C. 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.
Contents D. D.
Contents D. D.
Contents E. 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 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 E-22 F.
Contents F. F. (continued) FILES Command F-26 Example—FILES F-26 FILES Table F-27 Filesets F-29 Examples—Filesets F-29 FORMAT File Attribute F-30 Considerations—FORMAT F-30 FREE RESOURCES Statement F-30 Considerations—FREE RESOURCES F-31 Examples—FREE RESOURCES F-31 Functions F-32 FUP Command F-33 FUP Commands and SQL Objects F-33 Considerations—FUP F-35 Examples—FUP F-35 G.
Contents H. H. HEADING Clause H-1 Consideration—HEADING H-1 Example—HEADING H-1 HEADINGS Option H-2 Example—HEADINGS H-2 HELP Command H-2 Considerations—HELP H-3 Examples—HELP H-3 HELP TEXT Statement H-4 Consideration—HELP TEXT H-4 Example—HELP TEXT H-4 HISTORY Command H-5 Example—HISTORY H-5 Host Identifiers H-5 Host Programs H-6 Host Variables H-6 I.
Contents I. I.
Contents L. L.
Contents M. M.
Contents N. N.
Contents P. P.
Contents P. P. (continued) Primary Keys P-27 Print Item P-28 PROGID File Attribute P-28 Program Invalidation P-28 Operations That Invalidate a Program P-29 Preventing Program Invalidation Caused by DDL Operations PROGRAMS Table P-31 Protection View P-32 PURGE Command P-33 Considerations—PURGE P-34 Examples—PURGE P-36 PURGEDATA Command P-36 Considerations—PURGEDATA P-38 Examples—PURGEDATA P-39 Q.
Contents R. R.
Contents S. S.
Contents S. S.
Contents S. S.
Contents T. T.
Contents U. U. (continued) UPGRADE CATALOG Command U-11 Considerations—UPGRADE CATALOG U-12 Examples—UPGRADE CATALOG U-12 UPGRADE SYSTEM CATALOG Command U-13 Considerations—UPGRADE SYSTEM CATALOG U-13 Example—UPGRADE SYSTEM CATALOG U-13 UPSHIFT Function U-14 Considerations—UPSHIFT U-14 Examples—UPSHIFT U-14 USAGES Table U-15 User-Defined Keys U-16 Utilities U-16 V.
Contents W. W. WHENEVER DIRECTIVE W-1 Consideration—WHENEVER Directive W-2 WHERE CLAUSE W-2 WINDOW OPTION W-2 Consideration—WINDOW W-3 Examples—WINDOW W-3 WITH SHARED ACCESS OPTION W-4 Considerations—WITH SHARED ACCESS W-5 Example—WITH SHARED ACCESS W-8 Z.
Contents Z. Z.
Contents Tables (continued) Tables (continued) Table C-1. Table C-2. Table C-3. Table C-4. Table C-5. Table C-6. Table C-7. Table C-8. Table C-9. Table C-10. Table D-1. Table E-1. Table F-1. Table F-2. Table F-3. Table I-1. Table K-1. Table L-1. Table N-1. Table N-2. Table N-3. Table P-1. Table P-2. Table R-1. Table R-2. Table R-3. Table R-4. Table R-5. Table S-1. Table S-2. Table S-3. Table S-4. Table T-1. Table T-2. Table U-1. Table V-1. Table V-2. Catalog Tables and Indexes C-8 The SQL.
Contents HP NonStop SQL/MP Reference Manual—523352-013 xxviii
What’s New in This Manual Manual Information 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.
What’s New in This Manual New and Changed Information New and Changed Information Changes to the 523352-013 manual: Updated the section Examples—CREATE INDEX on page C-151. Updated the section MAXEXTENTS File Attribute on page M-2. Changes to the 523352-012 manual: Updated Considerations—PURGEDATA on page P-38. Update header size for Format 2 key-sequenced tables on page L-13. Added consideration for LOAD command on page L-45. Updated Lock Holder on page L-50.
What’s New in This Manual Changes to the G06.30 Manual Modified the description for setting the PROBABALISTIC option under UPDATE STATISTICS Statement on page U-7. Changes to the G06.30 Manual Modified the renaming tables description under Considerations—ALTER TABLE on page A-38. Modified the directives under Considerations—CONTROL TABLE on pages C-85 and C-86. Updated the description for setting the ERROR ABORT parameter on page S-40 under SET SESSION Command.
What’s New in This Manual Changes to the G06.
About This Manual This manual is the main reference text for NonStop SQL/MP, the HP relational database management system based on SQL. This manual includes reference information about the programmatic and conversational interfaces to NonStop SQL/MP, in addition to information about utilities used to install and maintain a NonStop SQL/MP database. This manual is structured as an encyclopedia-style reference text.
About This Manual Related Manuals Manual Title Description SQL/MP Version Management Guide Describes the rules that govern the version management for the NonStop SQL/MP relational database management system. SQL/MP Programming Manual for C and SQL/MP Programming Manual for COBOL Describe the NonStop SQL/MP programmatic interfaces for C and COBOL, respectively.
About This Manual Notation Conventions Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: To view the related manuals, click on Related Manuals. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
About This Manual General Syntax Notation Required Choice {| |}. Required choice indicators enclose multiple required syntax items. A vertically aligned group of items enclosed in required choice indicators represents a list of selections from which you must choose one or more, in any order, but cannot repeat a selection. Optional Choice [| |]. Optional choice indicators enclose multiple optional syntax items.
About This Manual Change Bar Notation a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] CONTROLLER [ , attribute-spec ]... Change Bar Notation Change bars are used to indicate substantive differences between this manual and its preceding version. Change bars are vertical rules placed in the right margin of changed portions of text, figures, tables, examples, and so on.
About This Manual HP Encourages Your Comments HP NonStop SQL/MP Reference Manual—523352-013 xxxviii
A Access Options Access options on DDL or DML statements determine the locking or access mode that SQL uses when executing the statements. Access options affect the consistency of the data your application views and the degree of concurrency your program has with other programs that use the same data. Access Options on DML Statements The DML statements SELECT, INSERT, UPDATE, and DELETE include access options that control lock duration and have row-level granularity.
Access Options on DDL Statements STABLE access provides sufficient consistency for any process that does not require a repeatable read capability. REPEATABLE Access Option on DML Statements The REPEATABLE access option locks all data accessed through the DML statement and holds the locks on data in audited tables until the end of the transaction.
Summary: Effect of Access Options on Concurrency performed without the WITH SHARED ACCESS option allow read-only access for concurrent DML operations during most of the DDL operation. For more information, see WITH SHARED ACCESS OPTION on page W-4. See the entry for the specific DDL statement you plan to use to determine if that statement allows the WITH SHARED ACCESS option. Summary: Effect of Access Options on Concurrency Concurrency is access to the same data by two or more processes at the same time.
ADD DEFINE Command Because the previous table does not show lock duration, STABLE and REPEATABLE access options appear similar for Transaction B. For more information on lock duration, about modifying default locking, and about the effects of locking on concurrency, see Locking on page L-48. ADD DEFINE Command ADD DEFINE is an SQLCI command that creates DEFINEs in the current SQLCI session. (ADD DEFINE is similar to the TACL command ADD DEFINE and the OSS command add_define.
Considerations—ADD DEFINE Setting the CLASS attribute establishes a new set of attributes for the DEFINE and sets each attribute associated with that CLASS to its initial value. If you specify CLASS after you specify other attributes, the values you specified for the previous attributes are erased. For more information about DEFINE attributes, see DEFINEs on page D-27. Considerations—ADD DEFINE You cannot use ADD DEFINE unless the DEFMODE setting is ON.
AGGREGATE Functions ADD DEFINE =ODETAIL_TABLE, ADD DEFINE =PARTS_TABLE, FILE $VOL3.SALES.ODETAIL ; FILE $VOL4.SALES.PARTS ; AGGREGATE Functions NonStop SQL/MP provides these aggregate functions: AVG Function Computes the average of a set of numbers. COUNT Function Counts the number of rows that result from a query or the number of rows that contain a distinct value in a specific column. MAX Function Determines a maximum value. MIN Function Determines a minimum value.
ALLOCATE File Attribute ALLOCATE File Attribute ALLOCATE is a Guardian file attribute that reserves disk space for a file, or frees disk space previously reserved for a file that does not contain data. ALLOCATE applies to key-sequenced, relative, and entry-sequenced tables and indexes. Allocating disk space in advance ensures that space is available when needed and avoids processing errors caused by full or fragmented disks during normal allocation-on-demand.
ALTER CATALOG Statement ALLOCATE affects the number of extents, but not the size of extents. The EXTENT file attribute determines the extent size. ALTER CATALOG Statement ALTER CATALOG is a DDL statement that alters security attributes for an entire catalog. {| {| ALTER CATALOG catalog {| {| { [NO]CLEARONPURGE } |} NOPURGEUNTIL date |} OWNER group,user |} SECURE "rwep" |} catalog is the name of the catalog to alter (or an equivalent DEFINE).
Example—ALTER CATALOG Security information for a catalog is stored in catalog tables and file labels for the catalog. ALTER CATALOG changes the information in the catalog and the associated file labels on a disk. Changing ownership or security for a catalog can affect users of objects described in the catalog, so be careful when you narrow the set of users with read or write access.
ALTER COLLATION Statement ALTER COLLATION Statement ALTER COLLATION is a DDL statement that renames a collation or alters security attributes for a collation. {| RENAME new-name |} ALTER COLLATION collation {| OWNER group,user |} {| SECURE "rwep" |} collation is the name of the collation to alter (or an equivalent DEFINE). If SMF is installed on your node, collation must be either a virtual name or a direct name. RENAME new-name specifies a new Guardian name (or an equivalent DEFINE) for the object.
Examples—ALTER COLLATION 4. User B tries to compile SQL statements that reference user B's own table, but the compilation fails because user B does not have authority to access the collation.
Considerations—ALTER DEFINE you specify other attributes, the values you specified for the previous attributes are erased. For more information about DEFINE attributes, see DEFINEs on page D-27. RESET reset-list restores the value of each attribute listed in reset-list to its initial value. You cannot reset a required attribute, so you cannot use this clause with CLASS MAP and CLASS CATALOG DEFINEs.
ALTER INDEX Statement ALTER INDEX Statement ALTER INDEX is a DDL statement that renames, changes security, or changes file attributes for an entire index; drops or adds an index partition; or changes file attributes for an index partition.
ALTER INDEX Statement security-spec is: {| { CLEARONPURGE | NO CLEARONPURGE } {| NOPURGEUNTIL date |} {| SECURE "rwep" |} |} attribute-spec is: {| {| {| {| {| {| {| {| {| { ALLOCATE int | DEALLOCATE } { AUDITCOMPRESS | NO AUDITCOMPRESS } { BUFFERED | NO BUFFERED } LOCKLENGTH int MAXEXTENTS int RESETBROKEN { SERIALWRITES | NO SERIALWRITES } TABLECODE int { VERIFIEDWRITES | NO VERIFIEDWRITES } |} |} |} |} |} |} |} |} |} move-spec is: {| {| {| {| {| {| {| {| CATALOG catalog-name PHYSVOL volume-name EXTE
ALTER INDEX Statement name is the name of an index or an index partition to alter or move (or an equivalent DEFINE). If name is a partition and you use clauses that apply to an entire index, SQL interprets name as identifying all partitions of the index. RENAME new-name changes the file and subvolume portions of the name of an index (including all partitions) to those in the Guardian name (or equivalent DEFINE) new-name, updating all catalog references to the index to reflect the change.
ALTER INDEX Statement contains data. If you want to drop a partition that contains data, use the PARTONLY option of the PURGEDATA command. For more information on index issues, see PURGEDATA Command on page P-36.
ALTER INDEX Statement The EXTENT, DSLACK, ISLACK, SLACK, and FORMAT options are not supported for a move or a merge of a partition into an existing partition. name specifies a valid partition of the index. NonStop SQL/MP determines the actual source partition during an execution. Check that the values you specify result in an index or a partition large enough to hold data being moved from an existing index or partition.
ALTER INDEX Statement {| [PARTONLY] {| {| {| { ALLOCATE int | DEALLOCATE } MAXEXTENTS int RESETBROKEN RECOVER INCOMPLETE SQLDDL OPERATION |} |} |} |} changes the ALLOCATE, MAXEXTENTS, RESETBROKEN attributes, or requests a recovery operation to change the INCOMPLETE SQLDDL OPERATION flag for the partition specified in name. The keyword PARTONLY is optional and has no effect.
Considerations—ALTER INDEX Considerations—ALTER INDEX To alter an index, you must be a generalized owner of the index and the underlying table. In addition, you must have authority to read and write to the affected catalogs. ALTER INDEX executes only if the specified index or partition is accessible. Unless you are altering file attributes for a partition, all partitions of the index must be accessible. Only one DDL statement can operate on a given SQL object (or partition of an SQL object) at a time.
Considerations—ALTER INDEX MOVE can perform a simple move, a merge (into an existing partition), a one-way or two-way split (to new partitions) or a one-way move (to an existing partition). Note that an index or an index partition cannot be open, even for read access, during an ALTER INDEX MOVE operation, or else the operation fails. A simple move moves the partition to another volume: MOVE TO dest-part [WITH SHARED ACCESS] name specifies the partition being moved.
Considerations—ALTER INDEX Reference Manual. You can also run DCOM on the disk to reclaim unused space. For more information on DCOM, see the Guardian Disk and Tape Utilities Reference Manual. If you are splitting a partition because the file is full (error 45), increase MAXEXTENTS for that file, if possible. Otherwise, perform a two-way split.
Considerations—ALTER INDEX All partitions of the index must be accessible when you add a new partition to an index. ALTER INDEX returns an error if you attempt to add a partition while another process has a partition locked or while another process is attempting to execute a DDL operation on the same partition. For more information about the errors returned, see DDL (Data Definition Language) Statements on page D-20.
Considerations—ALTER INDEX ALTER INDEX operations that use WITH SHARED ACCESS generally take longer to complete than those that do not. However, because WITH SHARED ACCESS operations allow concurrent read and write access to the source partition, they cause far less application downtime than equivalent operations without WITH SHARED ACCESS.
Considerations—ALTER INDEX If ALTER INDEX fails during a merge or one-way move operation with the WITH SHARED ACCESS option, use the SQL FILEINFO utility to see if the D or F flag is present for the target partition: The D flag, INCOMPLETE SQLDDL OPERATION, indicates that you need to request an ALTER INDEX name PARTONLY RECOVER INCOMPLETE SQLDDL OPERATION, followed by a FUP RELOAD command for the target partition.
Considerations—ALTER INDEX Dropping partitions of indexes All partitions of an index must be accessible when you drop any partition of the index, but partitions other than the partition being dropped can be accessed by other processes while the ALTER INDEX executes. Dropping an index partition invalidates a program that uses the index, unless the program was compiled with CHECK INOPERABLE PLANS and the table associated with the index has the SIMILARITY CHECK option enabled.
Examples—ALTER INDEX relative and entry-sequenced tables, the partition format is always 1 because such tables cannot have a FORMAT2ENABLED partition array. The format and file parameters, such as extent size of the new index partition, must be consistent with the index format for the ALTER INDEX command to succeed. For example, if you explicitly specify a partition as FORMAT 2 when the partition array is STANDARD or EXTENDED you will receive an error.
ALTER PROGRAM Statement This example creates two new partitions of an index and moves data to them from an existing partition, which it deletes (a two-way split): ALTER INDEX $DISK1.SALES.
Considerations—ALTER PROGRAM expanded new name must be unique among objects in the network. Both program and new-name must have the same volume and node name. If the program is managed by SMF, new-name must be either a virtual or direct name. Only the virtual name changes; the physical name on the physical volume is preserved. Considerations—ALTER PROGRAM To alter security attributes for a program or rename a program, you must be a generalized owner of the program file.
Example—ALTER PROGRAM Example—ALTER PROGRAM This example statements give the program file ASERV to a new owner (12,201) and set the PROGID file attribute. The first statement sets the owner ID, which automatically turns off the PROGID attribute. The second statement turns on the PROGID attribute.
ALTER TABLE Statement ALTER TABLE name { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { RENAME new-name {| security-spec |} {| attribute-spec |} SIMILARITY CHECK { ENABLE | DISABLE } ADD COLUMN col-name data-type [ DEFAULT def [ NOT NULL ] ] [ HEADING string | NO HEADING ] COLUMN col-name { HEADING string | NO HEADING } PARTITION ARRAY { STANDARD | EXTENDED | FORMAT2ENABLED } DROP PARTITION part-name {[FROM KEY val [UP TO LAST KEY]]} { TO dest-part [ move-spec ] } { [WIT
ALTER TABLE Statement attribute-spec is: {| {| {| {| {| {| {| {| {| {| { ALLOCATE int | DEALLOCATE } { AUDIT | NO AUDIT } { AUDITCOMPRESS | NO AUDITCOMPRESS } { BUFFERED | NO BUFFERED } LOCKLENGTH int MAXEXTENTS int RESETBROKEN { SERIALWRITES | NO SERIALWRITES } TABLECODE int { VERIFIEDWRITES | NO VERIFIEDWRITES } |} |} |} |} |} |} |} |} |} |} move-spec is: {| {| {| {| {| {| {| {| CATALOG catalog-name PHYSVOL volume-name EXTENT { size1 | ( size1 [, size2 ] ) } MAXEXTENTS int FORMAT 1 | FORMAT 2 DSLACK
ALTER TABLE Statement name cannot specify a catalog table other than CATALOGS, PROGRAMS, TRANSIDS, or USAGES. (You can use security-spec to change security attributes for these tables, but you cannot use any other ALTER TABLE clause with any catalog table.) RENAME new-name changes the file and subvolume portions of the name of a table (including all its partitions) to those in the Guardian name (or equivalent DEFINE) new-name, updating all catalog references to reflect the change.
ALTER TABLE Statement Authorizing similarity checks (SIMILARITY CHECK ENABLE) on a table whose version is older than 310 increases the version of the table (and the version of objects that depend on the table) to 310. Such a table cannot be registered in an older version catalog or accessed by older versions of the SQL/MP software.
ALTER TABLE Statement Format 2-enabled partition array allows a partition to grow to one terabyte or the size of a single disk volume, whichever is smaller. PARTITION ARRAY applies to partitions created later for a table, even if the table is not currently partitioned. Altering the base table causes all associated indexes to be altered automatically to the value specified for the base table. You can use the PARTITION ARRAY clause in SQLCI or in dynamic SQL statements.
ALTER TABLE Statement move-spec sets the catalog name and these file attributes for the table or partition destpart: CATALOG Sets the catalog name PHYSVOL Sets a physical volume for the new partition that overrides SMF EXTENT Sets extent sizes MAXEXTENTS Sets maximum extents FORMAT 1 | FORMAT 2 Sets partition format.
ALTER TABLE Statement The option clauses allow you to name the operation, control EMS reporting for the operation, specify a time window for the beginning of the commit phase of the operation (the phase in which DML and utilities operations on the file are temporarily restricted), and specify the timeout period for lock requests and the handling of retryable errors during the commit phase of the operation.
Considerations—ALTER TABLE add-spec specifies options for a partition added with the ADD PARTITION clause. The FIRST KEY clause specifies the primary or clustering key value for the first key allowed in a new partition of a table with key-sequenced file organization. It is required for key-sequenced files. val is a literal compatible with the data type of the key column that specifies the key value. For clustering keys, specify multiple vals, in order.
Considerations—ALTER TABLE Only one DDL statement can operate on a given SQL object (or partition of an SQL object) at a time. An error occurs if you attempt to execute an ALTER TABLE statement while another process is executing a DDL operation on the same object. The specific error depends on the DDL operation involved and the phase of the operation at which the conflict occurs. For more information, see DDL (Data Definition Language) Statements on page D-20.
Considerations—ALTER TABLE The owner of a protection view must have authority to read and write to the view and the underlying table unless the security string for the view specifies the super ID (-) for the authority the owner lacks. SQL issues a warning if it changes the security string of protection views as the result of an ALTER TABLE.
Considerations—ALTER TABLE For example, an existing row receives the value 0001-01-01:12:00:00.000000 in the new column if the data type is DATETIME YEAR TO FRACTION, receives the value 0001-01-01 in the new column if the data type is DATE, receives the value 12:00:00 in the new column if the data type is TIME, and so forth. Any row added after the ADD COLUMN operation finishes that does not contain a value for the column receives a default value based on the current timestamp at the time the row is added.
Considerations—ALTER TABLE For a one-way split, the subvolume name and simple file name for the new partition (whether specified explicitly or by default) must be identical to the subvolume name and simple file name for every other partition of the same object; ALTER TABLE uses those names if you specify only a node name and volume name or specify only a volume name (which causes the node to default to the local node).
Considerations—ALTER TABLE simple file name for every other partition of the same object. ALTER TABLE uses those names if you specify only a node name and volume name or specify only a volume name (which causes the node to default to the local node). The two-way split does not support the WITH SHARED ACCESS option.
Considerations—ALTER TABLE Other partitions of the table are available for INSERT, UPDATE, or DELETE operations, so processes can make read and write requests for those partitions. For information about specifying on-demand opens, see the OPEN ACCESSED PARTITIONS clause and the SKIP UNAVAILABLE PARTITION clause under CONTROL TABLE Directive on page C-77. Without WITH SHARED ACCESS, you might want to stop activity on a table when you intend to move or split one of the partitions to the table.
Considerations—ALTER TABLE You can partition tables of any file organization, but you cannot partition a key-sequenced table that has a system-defined primary key (as opposed to a user-defined primary key) unless it also has a clustering key. ALTER TABLE operations that use WITH SHARED ACCESS generally take longer to complete than those that do not.
Considerations—ALTER TABLE When you add a partition to a table, the PARTNS catalog table and associated IXPART01 index might become full. To correct the situation, distribute object and partition definitions across multiple catalogs. For more information about partition limits and the PARTNS table, see Limits on page L-6. If the SLACK space in the source file is less than the value chosen for the target file, a MOVE operation can fail with a file full error.
Considerations—ALTER TABLE Similar to other DDL operations, all partitions of the table must be available for the operation to complete. Only key-sequenced tables can be Format 2-enabled tables. If you attempt to specify FORMAT2ENABLED for a relative or entry-sequenced table, you receive an error. If you attempt to convert a Format 1-enabled table to Format 2-enabled where the existing number of partitions is greater than that supported by Format 2, you will receive an error.
Considerations—ALTER TABLE create a new partition. If you specify the FORMAT clause on these types of operations you will receive a warning and the operation continues. Dropping partitions of tables All partitions of a table must be accessible when you drop any partition of the table, but partitions other than the partition being dropped can be accessed by other processes while the ALTER TABLE executes.
Examples—ALTER TABLE accommodate database growth. Because each partition must have access to the location and key range of other partitions, REUSE PARTITON updates the label of all the partitions with the new key range. After REUSE PARTITION is performed, the reused partition is empty. If an error is encountered during REUSE, the data in the reused partition might not be recoverable. It is recommended that you back up data before performing REUSE PARTITION.
Examples—ALTER TABLE This example alters the maximum number of extents for a specific partition: ALTER TABLE \SYS2.$VOL2.INVENT.
ALTER VIEW Statement ALTER VIEW Statement ALTER VIEW is a DDL statement that changes the name, owner, or security attributes of a view, changes the heading for a column of the view, or enables or disables similarity checks for the view.
Considerations—ALTER VIEW SIMILARITY CHECK { ENABLE | DISABLE } authorizes or prohibits similarity checks on a protection view. (You cannot specify this clause for a shorthand view.) Authorizing similarity checks (SIMILARITY CHECK ENABLE) on a view whose version is older than 310 increases the version of the view (and the version of objects that depend on the view) to 310. Such a view cannot be registered in an older version catalog or accessed by older versions of NonStop SQL/MP.
Example—ALTER VIEW Example—ALTER VIEW These four examples rename, resecure, change the owner of, and change a column heading for a view, ALTER ALTER ALTER ALTER VIEW VIEW VIEW VIEW NAME1 NAME2 NAME2 NAME2 RENAME NAME2; SECURE "nnno"; OWNER 12,72; COLUMN DESCR HEADING "Product/Descriptions"; APPEND Command APPEND is an SQLCI utility that appends data from an SQL table or Guardian file (such as a Guardian process, device, unstructured disk file, or Enscribe file) to an entry-sequenced or key-sequenced SQ
Considerations—APPEND append-option is one or more options that configure the APPEND operation. The append-option list is identical to load-option in the LOAD command, except you cannot APPEND data to an Enscribe file. Therefore, Enscribe-specific LOAD options are not valid for an APPEND operation. For a full description of each append-option, see LOAD Command on page L-18. Considerations—APPEND APPEND requires authority to read in-file and write to out-file.
Considerations—APPEND A recovery file contains information needed to restore the target table to its original state if the APPEND operation fails. Before appending any data to a table or table partition, APPEND checks for the presence of a recovery file. If the file does not exist or exists but contains incomplete recovery data, the APPEND command executes normally, as described below. If the file exists and contains recovery data or is not a recovery file, APPEND fails.
Considerations—APPEND APPENDCANCEL operation executes, the target table contains exactly the same data it had before the initial APPEND operation was started. (Any new data added by the interrupted APPEND operation is removed from the target table.) For more information about these commands, see APPENDRESTART Command on page A-58 and APPENDCANCEL Command on page A-56. If you repeat an APPEND command that terminated successfully on an entrysequenced table, the target table will contain duplicate data.
APPENDCANCEL Command Field Conversions For any APPEND operation, the data type of each source field must be compatible with the data type of its corresponding target field. The details of data type compatibility and Enscribe-to-SQL and SQL-to-Enscribe field conversions are identical for APPEND and LOAD; see Data type compatibility and field conversions on page L-36 for details.
Considerations—APPENDCANCEL Considerations—APPENDCANCEL APPENDCANCEL has the same authorization requirements as the APPEND command. For more information about authorization requirements for APPENDCANCEL, see APPEND Command on page A-52. APPENDCANCEL verifies that the information in recovery-file accurately describes the target table (or partition). Using the information in recovery-file, APPENDCANCEL restores the target table to the state it was in before the APPEND operation began.
APPENDRESTART Command APPENDRESTART Command APPENDRESTART is an SQLCI utility that recovers from an interrupted APPEND operation and completes the APPEND operation. The APPENDRESTART utility restores the target table to its original state (before the APPEND operation began). APPENDRESTART proceeds with the APPEND operation, adding data to the end of the target table.
Considerations—APPENDRESTART Considerations—APPENDRESTART APPENDRESTART has the same authorization requirements as the APPEND command. For more information about authorization requirements for APPENDRESTART, see APPEND Command on page A-52. APPENDRESTART verifies that the information in recovery-file accurately describes both the source file and the target table (or partition).
AS Clause AS Clause The AS clause is an SQLCI report writer clause that specifies a display format for a print item. You can use the AS clause in the BREAK FOOTING, BREAK TITLE, DETAIL, PAGE FOOTING, PAGE TITLE, REPORT FOOTING, and REPORT TITLE report writer commands. { { { { { AS { { { { { { { display-descriptor } } "scale-sign-descriptor display-descriptor" } } " "[" [ decoration [, decoration ] ... ] } [ modifier [, modifier ] ... ] "]" } display-descriptor" } } " "[" [ decoration [, decoration ] ...
AS Clause Table A-2. Display Descriptors for Character Items Form and Usage Example Value Printed A A4 A3 “[LJ] A3” “[RJ] A3” WORD WORD WORD WORD WORD |WORD| |WORD| |WOR| |WOR| |ORD| Multiline character field, n print positions wide with w print positions per line. If n is 0, use width of item; if n is more than 255, you must specify w. C0.8 Customer has a low credit rating. (VARCHAR string) |Customer| | has a l| |ow credi| |t rating| |.
AS Clause Table A-3. Display Descriptors for Numeric Items (page 2 of 2) Form and Usage Example Value Printed Integer field, w print positions wide that contains m digits. Right justify, blank fill, leading zeros. I8 I8.2 I8.6 100 -1 100 | 100| | -01| | 000100| M”99/99/99” M'Z,ZZ9.99' M M<9,999> M<9,999> M<$Z,ZZ9.99> M<$Z,ZZ9V99> 112388 32.009 666 666 66666 920.00 920.00 |11/23/88| | 32.01| | 666| |0,666| |*****| |$ 920.
AS Clause Table A-5. Modifiers (page 2 of 2) Form and Usage Example Value Printed Specifies whether data in C format is split at a blank if possible. “[F] C24.8” Manager is on leave. |Manager | |is on | |leave. | “[FL'.'] A8” “[RJ,FL'>']A8” “[FL'*']M<$Z,ZZ9.99” THEN HERE 127.39 |THEN.... | |>>>>HERE | |$**127.39| “[OC'+']I2” “[OC'>']F5.2” 100 100000.
Decorations Decorations decoration is condition location char-string condition is one or more of: M Add char-string if value is negative P Add char-string if value is positive Z Add char-string if value is zero O Add char-string if overflow condition occurs Only P is allowed with character print items. Specify multiple conditions without separators. location specifies where the character prints: An Display at print position n of the field. (The leftmost position is 1.
Examples—AS Order for Processing Decorations SQL processes decorations from left to right: 1. Tests data to determine whether it is positive, negative, or zero. 2. If the P location is specified, adds the character string to the item value. 3. Formats according to the A, I, or F descriptor. 4. Applies decorations for alphanumeric and fixed-point descriptors. 5. Tests for overflow.
Examples—AS Zero value: ZAn char-string Prints at position n ZF char-string Prints immediately left of a right-justified value or immediately right of a left-justified value ZP char-string Prints immediately right of a value Overflow value: OAn char-string Prints at position n. These are examples of edit descriptors with decorations: Format Item Value Printed Item “[MF'<',MP'>',ZPP' '] F12.2” 1000.00 | “[MF'<',MP'>',ZPP' '] F12.2” -1000.00 | <1,000.00>| “[MA1'CR',MPF'$'] F12.2” 1000.
AS DATE/TIME Clause AS DATE/TIME Clause The AS DATE/TIME clause specifies a format for printing a date and time in the BREAK FOOTING, BREAK TITLE, DETAIL, PAGE FOOTING, PAGE TITLE, REPORT FOOTING, and REPORT TITLE report writer commands. Print items printed with AS DATE/TIME must be in Julian timestamp format. To convert dates and times in other formats to Julian timestamps, use the COMPUTE_TIMESTAMP function.
AS DATE/TIME Clause Format Values DA Monday, Tuesday, ... Sunday DA3 Mon, Tue, ... Sun D2 01, 02, ... 31 DB2 1, 2, ... 31 D3 001, 002, ... 366 DB3 1, 2, ... 366 DOB2 1st, 2nd, ... 31st DAO First, Second, ... Thirty-First Y2 00, 01, ... 86, 87, 88, ... YB2 0, 1, ... 86, 87, 88, ... Y4 1900, 1901, ... 1986, 1987, 1988, ... 2000, 2001, 2002, 2003,2004, ... "time-string" is a string that specifies the method format a time value.
Examples—AS DATE/TIME Format Values C2 00, 01, ... 99 CB2 0, 1, ... 99 T3 000, 001, ... 999 TB3 0, 1, ... 999 IN LCT specifies that the value of the item in the database is encoded as local civil time. IN GMT specifies that the value of the item in the database is encoded as Greenwich mean time, the default. The value formatted for display is always represented as local civil time. The range of timestamps for IN GMT starts at 0000/1/1:12:00:00 and ends at 9999/12/31:12:00:00.
ASCII Character Set ASCII Character Set The ASCII character set is a subset of the nine single-byte ISO character sets (ISO 8859/1 through ISO 8859/9) that are used by SQL. Table A-6 is a list of the characters in the ASCII character set along with their internal representations and meanings. Table A-6.
ASCII Character Set Table A-6.
ASCII Character Set Table A-6.
ASCII Character Set Table A-6.
AUDIT File Attribute Table A-6.
AUDITCOMPRESS File Attribute The value stored in the VIEWS table is Y (for AUDIT), N (for NO AUDIT), or M (for mixed). For a given table, all partitions are audited, or all partitions are nonaudited. If you alter the AUDIT file attribute for a table, SQL automatically sets the BUFFERED file attribute for that table (but not for its dependent indexes): If you specify AUDIT, SQL also sets BUFFERED. If you specify NO AUDIT, SQL also sets NO BUFFERED.
Audited Tables might leave the AUDITCOMPRESS attribute off. You can use the ALTER TABLE statement to turn the attribute back on. Audited Tables Audited tables are tables audited by TMF. TMF monitors all transactions against audited tables in preparation for possible transaction backout or TMF recovery operations.
Considerations—AVG DISTINCT column specifies a set of distinct column values from each row of the result table to average. The column cannot be a column from a view that corresponds to an expression in the view definition. If you specify DISTINCT in more than one AVG function in the same statement, the functions must reference the same column. Considerations—AVG AVG is evaluated after eliminating all null values from the aggregate set. If the result set is empty, AVG returns a null.
Examples—AVG HP NonStop SQL/MP Reference Manual—523352-013 A-78
B BACKUP Utility BACKUP is a Guardian utility program, executed from TACL, that copies Guardian files and SQL objects from disk to magnetic tape. (A complementary utility program, RESTORE, copies Guardian files and SQL objects from the magnetic tape to the disk.) For more information about BACKUP, see the Guardian Disk and Tape Utilities Reference Manual. BASETABS Table The BASETABS table is a catalog table that describes attributes that apply to tables, but not to views.
BEGIN DECLARE SECTION Directive BEGIN DECLARE SECTION Directive BEGIN DECLARE SECTION is a host program directive that starts a host program Declare Section for declaring host variables to use in SQL statements. Guidelines for the use of Declare Sections vary with the host language. For more information, see the SQL/MP programming manual for your host language.
BEGIN WORK Statement BEGIN WORK Statement BEGIN WORK is a transaction control statement that starts a user-defined TMF transaction. A user-defined TMF transaction groups a set of operations on audited objects and files so that changes made by the operations can be committed (with the COMMIT WORK statement) or rolled back (with the ROLLBACK WORK statement) as a unit. TMF transactions do not protect nonaudited tables. For more information on TMF transactions, see TMF Transactions on page T-6.
BETWEEN Predicate BETWEEN Predicate BETWEEN is a predicate that determines whether a value is within a range of values. row-value-spec [NOT] BETWEEN row-value-spec AND row-value-spec row-value-spec is: {expression[ ,expression] ... } { (expression[ ,expression] ... ) } Considerations—BETWEEN BETWEEN is a comparison predicate.
BLOCKSIZE File Attribute This example finds those items for which the part cost is less than $5 or more than $800: PARTCOST NOT BETWEEN 5.00 AND 800.00 This example finds those names between Jody Selby and Gene Wright. The name Barbara Swift meets the criteria; the name Mike Wright does not. LAST_NAME, FIRST_NAME BETWEEN "SELBY", "JODY" AND "WRIGHT", "GENE" BLOCKSIZE File Attribute BLOCKSIZE is a Guardian file attribute that specifies the number of bytes in a block.
BREAK FOOTING Command BREAK FOOTING Command BREAK FOOTING is an SQLCI report writer command that specifies the text at the end of a group of break column values. You can use BREAK FOOTING only from the select-in-progress prompt, not from the SQLCI prompt. BREAK FOOTING break-column (print-list) [CENTER]; print-list is: print-item [, print-item] ... break-column identifies a break column (a column named in a BREAK ON command).
Example—BREAK FOOTING bytes, including the field widths of all print items and the number of spaces between items. Example—BREAK FOOTING This example uses break columns to format a report. The report includes a break footing. >> SELECT D.DEPTNUM, DEPTNAME, EMPNUM, JOBCODE, LOCATION +> FROM PERSNL.EMPLOYEE E, PERSNL.DEPT D +> WHERE E.DEPTNUM = D.DEPTNUM +> ORDER BY D.DEPTNUM; S> BREAK ON COL 1, COL 2; S> DETAIL D.DEPTNUM, DEPTNAME, EMPNUM, JOBCODE; S> BREAK FOOTING D.
BREAK ON Command BREAK ON Command BREAK ON is an SQLCI report writer command that groups detail lines together by the value of a specified column. You can use BREAK ON only from the select-in-progress prompt, not from the standard SQLCI prompt. BREAK ON col [SUPPRESS] [, col [SUPPRESS]]... ; [NOSUPPRESS] [ [NOSUPPRESS]] The default is SUPPRESS. col identifies a column of the select list or a named detail column, the value of which is used to group lines.
Examples—BREAK ON Examples—BREAK ON This example groups detail lines by job codes within groups of departments. When finished, enter CANCEL at the select-in progress prompt (S>): >> SET LIST_COUNT 0; >> SELECT LAST_NAME, FIRST_NAME, JOBCODE, DEPTNUM +> FROM PERSNL.
BREAK TITLE Command BREAK TITLE Command BREAK TITLE is an SQLCI report writer command that specifies the text at the beginning of a group of break column values. You can use BREAK TITLE only from the select-in-progress prompt, not from the SQLCI prompt. BREAK TITLE break-column (print-list) [CENTER]; print-list is: print-item> [, print-item] ... break-column identifies a break column (a column named in a BREAK ON command).
Example—BREAK TITLE Example—BREAK TITLE These commands select data and identify DEPTNUM as a break column: >> +> +> +> S> S> SELECT D.DEPTNUM, DEPTNAME, EMPNUM, JOBCODE FROM PERSNL.EMPLOYEE E, PERSNL.DEPT D WHERE E.DEPTNUM = D.DEPTNUM ORDER BY D.DEPTNUM; DETAIL D.DEPTNUM, EMPNUM, JOBCODE; BREAK ON COL 1; Before the first detail line is printed, and each time the value of DEPTNUM changes, the break title defined in this command appears in the report: S> BREAK TITLE D.
Considerations—BUFFERED single I/O operation. However, buffering can cause loss of data on nonaudited files if a failure occurs while updated rows are stored in cache memory but not yet written to disk. Audited files should always be buffered, because auditing itself protects against loss of data. (Specifying NO BUFFERED for an audited file would unnecessarily reduce performance.
C CANCEL Command CANCEL is an SQLCI report writer command that cancels the current SELECT and returns to the standard SQLCI prompt. Selected rows and current report formatting commands become unavailable, except through the FC command. CANCEL ; Consideration—CANCEL CANCEL does not delete reports.
CASE Expression CASE Expression The CASE expression is a conditional expression. SQL evaluates the conditions in the CASE expression and sets the CASE expression to a value based on the condition that is true. If none of the search conditions are true, SQL sets the CASE expression to the value specified in the ELSE clause or, if ELSE is not specified, SQL sets the CASE expression to NULL. CASE WHEN search-condition-1 THEN WHEN search-condition-2 THEN WHEN search-condition-3 THEN . . .
Considerations—CASE Expression target-value if present, specifies a value or expression for which a result is returned. When you specify target-value, you use an abbreviated form of the CASE expression, typically used for value comparisons. The data type of each value-n in the statement should be comparable to the data type of target-value. In addition, the collation of each value-n should be the same or comparable to the collation of target-value.
Examples—CASE Expression WHEN target-value = value-3 THEN result-3 ... WHEN target-value = value-n THEN result-n ELSE value-x END Examples—CASE Expression This example decodes movie_type and returns NULL if movie_type does not match any of the listed values: SELECT movie_name, CASE movie_type WHEN 1 THEN "Horror" WHEN 2 THEN "Comedy" WHEN 3 THEN "Drama" ELSE NULL END FROM movies; This example returns last_name, first_name, and a value based on salary that depends on the value of employee.
Valid Considerations—CAST When casting data to a CHAR or VARCHAR data type, the resulting data value is left justified. Otherwise, the resulting data value is right justified. Further, when you are casting to a CHAR or VARCHAR data type, you must specify the length of the target value. Valid Considerations—CAST You can convert: An exact or approximate numeric value to any other numeric data type. An exact or approximate numeric value to any character string data type.
Examples—CAST Multibyte datatype to any non-character datatype Any non-character datatype to multibyte datatype Examples—CAST The PROJECT table contains a column START_DATE of data type DATE and a column SHIP_TIMESTAMP of data type TIMESTAMP. Use CAST to return the number of days for completion of a project: SELECT projdesc, start_date, ship_timestamp, (CAST (ship_timestamp AS DATE) - start_date) DAY FROM persnl.
CATALOG Command CATALOG Command CATALOG is an SQLCI command that sets the default catalog in an SQLCI session. CATALOG [ catalog ] ; catalog is a catalog name (a Guardian subvolume name) with or without a node and volume qualifier. It cannot be a DEFINE. If you omit catalog, SQLCI resets the default to the value of the CATALOG attribute of the =_DEFAULTS DEFINE that SQLCI inherited from the process that started the session. If you omit the node and volume qualifier, SQLCI uses the current defaults.
Catalogs Catalogs An SQL/MP catalog is a set of tables and indexes that describe SQL objects. Tables in the set are called catalog tables and SQL creates them—along with their indexes— when you execute a CREATE CATALOG statement. Each SQL/MP catalog (the set of catalog tables and their indexes) resides on its own Guardian subvolume, and the name of that subvolume is also the name of the catalog. The name has the same form as the subvolume portion of a Guardian file name: [\node.][$volume.
Operations on Catalog Tables Table C-1.
Operations on Catalog Tables that contain statistics are updated only when you issue an UPDATE STATISTICS command.
CATALOGS Table CATALOGS Table The CATALOGS table is a catalog table that describes all the catalogs on a node. CATALOGS is part of the system catalog for the node and does not exist in user-created catalogs. The CATALOGS table is always located on a subvolume named SQL, even if the system catalog is installed on a subvolume of a different name. As a result, it is sometimes called the SQL.CATALOGS table. It is always on the same volume as the system catalog for the node. Table C-2. The SQL.
CENTER_REPORT Option CENTER_REPORT Option CENTER_REPORT is an option of the SQLCI report writer SET LAYOUT command that controls whether reports are centered within the left and right margins. CENTER_REPORT { OFF } { ON } Consideration—CENTER_REPORT If you specify ON, the report writer centers the whole report as a block of text.
Character Data Types Character Data Types SQL includes both fixed-length character data and varying-length character data. Either type of character data can be associated with a character set. Any character data type is compatible with any other character data type that is associated with the same character set, but not with numeric, date-time, or interval data types, and not with character data that is associated with a different character set.
Character Expressions Character Expressions A character expression specifies a value and can be a simple string literal or a column name that specifies the value of a column in a row of a table. The expression can include string operators and function calls that return string results.
Considerations—Character Expressions host-variable-name is the name of a host variable that contains a value with a character data type. string-function-invocation is a call to a function such as UPSHIFT, TRIM, or SUBSTRING that returns a string as a result. concatenation-operator specifies this operator: || The concatenation operator concatenates two string operands and produces a string as a result. If either of the character strings has a VARCHAR data type, the result has a VARCHAR data type.
Considerations—Character Expressions Guidelines for using the concatenation operator The sum of lengths of the character string operands cannot exceed the maximum allowed length for their data type. If their length exceeds the maximum allowed for a character data type, SQL truncates the string to the right and issues a warning. Strings with different character sets cannot be concatenated together. If either of the two character string operands is a null value, the result is a null value.
Examples—Character Expressions Examples—Character Expressions This example on concatenation results in “Robert Smith”: "Robert " || "Smith" Note blanks between names are included in the original string literals.
ISO 8859 Character Sets ISO 8859 Character Sets The ISO 8859 character sets are a standard set of nine single-byte character sets defined by ISO (the International Organization for Standardization) in a series called ISO 8859. The first in the series is called ISO 8859/1, the second is ISO 8859/2, and so on through ISO 8859/9. In NonStop SQL/MP, you use the keywords ISO88591, ISO88592, ISO88593, and so forth to specify a character set within the ISO 8859 series.
KSC5601 Character Set Neither byte of any Kanji character contains any binary value less than %H40, as shown: First-byte range %H81 .. %H9F, %HE0 .. %HFC Second-byte range %H40 .. %H7E, %H80 ..
CHAR_LENGTH Function CHAR_LENGTH Function The CHAR_LENGTH function returns the number of characters in a string. CHAR[ACTER]_LENGTH ( character-string ) where character-string is: { { { { { { string-literal column-name param-name host-var-name UPSHIFT function character-expression } } } } } } character-string specifies the string for which the length is to be returned. Considerations—CHAR_LENGTH Function SQL returns the result as a two-byte signed integer with a scale of zero.
CLEANUP Command This example returns 20: CHAR_LENGTH ( EMPNAME ) This example returns 21—not 100—because it is a VARCHAR value: CHAR_LENGTH ( ADDRESS ) CLEANUP Command CLEANUP is an SQLCI utility command that allows a user with super ID authority to delete damaged SQL objects, SQL programs in Guardian files, and catalogs and associated file labels and shadow labels from the local node. Caution. Use CLEANUP only when absolutely necessary to delete damaged objects.
Considerations—CLEANUP If you omit the exclamation point when you use CLEANUP interactively, this prompts normally appear: DO YOU WISH TO CLEANUP THE ENTIRE FILESET ... (name of fileset) ...
Considerations—CLEANUP Catalog description and file label purging are two different and independent activities.
CLEANUP Exception Cases that depends on a collation but not the collation, CLEANUP purges the dependent object and deletes the relationship to the collation. CLEANUP cleans up objects only on the local node. To delete all partitions of a damaged object distributed over several nodes, you must run CLEANUP on each of the nodes involved. Caution. Never use this command to purge SQL objects and catalogs: SQLCI CLEANUP $*.*.
Example—CLEANUP Purge a catalog's name entry in the system catalog's CATALOGS table when a filesystem error occurs during access to the system CATALOGS table. When CLEANUP is used to purge a catalog, CLEANUP attempts to remove the name entry for the catalog from the system CATALOGS table. Any failure in this operation (caused by an event such as a system catalog file being flagged as CRASHOPEN) does not affect the outcome of the current CLEANUP operation.
CLEARONPURGE File Attribute If $VOL1 is removed (instead of $VOL2, as in the previous example), the catalog descriptions cannot be removed using DROP or PURGE because the tables are not accessible. You can delete the catalog descriptions by entering: >> CLEANUP ($VOL1.PERSNL.DEPT, $VOL1.PERSNL.JOB, $VOL1.PERSNL.EMPLOYEE) FROM CATALOG $VOL2.CAT; If the DEPT, JOB, and EMPLOYEE tables are the only SQL objects on $VOL1, you can accomplish the same operation: >> CLEANUP $VOL1.PERSNL.* FROM CATALOG $VOL2.
Considerations—CLOSE cursor is the name of an open cursor to close. :cursor-variable is a host variable of type CHAR or VARCHAR that stores the name of an open cursor to close. Considerations—CLOSE There are no authorization requirements for closing a cursor. Closing a cursor defined with REPEATABLE access does not affect locks. Locks on audited tables are released when the TMF transaction finishes or aborts; locks on nonaudited tables must be released with UNLOCK TABLE.
Clustering Keys Clustering Keys A clustering key is the user-defined portion of a primary key that is determined partly by the user and partly by the system. Values for a clustering key do not need to be unique, as required for user-defined primary keys. Only key-sequenced tables can have clustering keys. To define a clustering key, specify one or more columns in the CLUSTERING KEY clause of the CREATE TABLE statement.
COLLATE Clause COLLATE Clause The COLLATE clause associates an existing collation with a character expression, with a column of a character data type that is being added to a table, or with an index that is being created. For information about using the COLLATE clause on a character expression, see Character Expressions on page C-14. For information about using the COLLATE clause with a data type specification for a CREATE TABLE or ALTER TABLE statement, see Data Types on page D-1.
Collation Definitions Collation Definitions A collation definition is a description of a collating sequence that can be written in an EDIT file and processed by the CREATE COLLATION statement to create an SQL collation. The simplest possible collation definition consists of an LC_COLLATE section that includes an ordered list of elements in the collation.
Comment and Escape Characters in Collation Definitions To change the comment character or escape character, specify a new character in angle brackets in a comment_char or escape_char clause at the beginning of the collation definition.
The LC_COLLATE Section of a Collation Definition The LC_COLLATE Section of a Collation Definition The LC_COLLATE section defines multicharacter elements of the collation and specifies the order of character and multicharacter elements within the collation. The LC_COLLATE section is the only required section in a collation definition. The LC_COLLATE section can appear only once in a collation definition. Each of the six types of lines that make up the section must begin on a new physical line.
The LC_COLLATE Section of a Collation Definition is a stream of 2 to 30 characters, enclosed in a set of angle brackets, that represents the new element.
The LC_COLLATE Section of a Collation Definition and this element in the collation definition. Neither the preceding element nor this element can be an ellipsis. If element is UNDEFINED, it specifies all characters in the character set not previously included in the ordered list (either directly or with an ellipsis). You can use UNDEFINED only as the last element before order_end. The only weight allowed with UNDEFINED is IGNORE.
The LC_CTYPE Section of a Collation Definition The LC_CTYPE Section of a Collation Definition The LC_CTYPE section defines character classes and case conversion rules. The LC_CTYPE section can appear only once in a collation definition. Except for the line that begins with class-name, each of the five types of lines shown in the syntax diagram can appear only once in the section. Each type of line must begin on a new physical line. LC_CTYPE [ charclass class-name [; class-name ] ...
The LC_CTYPE Section of a Collation Definition A class-element between two char symbols in the list can also be an ellipsis, signifying the series of characters between the characters represented by the char symbols within the character set. Specify a class-name clause for each class-name you list on the charclass clause. toupper ( lower, upper ) [ ; ( lower, upper ) ] ... specifies a set of char pairs that defines the relationship between lowercase and uppercase characters.
The LC_TDMCODESET Section of a Collation Definition The LC_TDMCODESET Section of a Collation Definition The LC_TDMCODESET section specifies the character set for the collation. If you do not specify the LC_TDMCODESET clause, SQL uses the ISO88591 character set for the collation. The LC_TDMCODESET section can appear only once in a collation definition. Each of the three types of lines shown in the syntax diagram can appear only once in the section. Each type of line must begin on a new physical line.
Considerations—Collation Definitions Considerations—Collation Definitions Collation limits A collation can have up to: 500 strings 100 character classes 8192 tokens (keywords, identifiers, punctuation elements, integers, and strings) Collations for Pathmaker applications Collations for Pathmaker-SQL applications must specify the hexadecimal character ff as the last entry in the collation list.
Examples—Collation Definitions positions in the collating sequence. The letter b collates the same as itself, although the weight symbol is omitted. LC_COLLATE order_start forward A A B B ... ... Z Z a A b order_end END LC_COLLATE This example also demonstrates the use of ellipsis and weight in an order list of an LC_COLLATE section, but uses them differently than the previous example.
Examples—Collation Definitions LC_COLLATE # This case insensitive collating sequence sorts most of # the accented forms of a, e, i, o, and u equal to the # unaccented form. # # # Upshift for a, e, i, o, u -grave -acute -circumflex is A, E, I, O, U. Upshift for e-umlaut is E. Upshift for i-umlaut is I, and upshift for y-acute is Y. # The actual collating sequence starts here: order_start forward \d032 \d032 # 32 is the space character \d160 \d032 # NBSP (non breaking space) <0> <0> ... ...
Examples—Collation Definitions \d210 ... \d213 \d242 ... \d245 \d217 ... \d219 \d249 ... \d251 \d221 \d253 \d255 \d198 \d230 \d216 \d248 \d197 \d229 \d222 \d254 \d033 ... \d198 \d230 \d216 \d248 \d197 \d197 \d222 \d222 ... \d047 \d173 \d058 ... \d063 \d064 \d091 ... \d096 \d123 ... \d126 \d127 <-> <:> ... <@> <[> ... <•> <{> ...
Examples—Collation Definitions \d223 "" # sharp-s sorts as string of s and s \d163 \d163 # upper-half specials and controls \d215 \d215 # multiply sign \d159 \d159 \d170 \d170 \d186 \d186 UNDEFINED IGNORE order_end END LC_COLLATE LC_CTYPE charclass alphas alphas; numerics; hexdigits; specials ;...;;\ ;...;;\ \d192;...;\d214;\d216;...;\d246;\d248;...;\d255 numerics <0>;<1>;\d050;\x33;\ <4>;...;<9> hexdigits <0>;...;<9>;\ ;...;;\ ;...
Collations Collations A collation is an SQL object that contains rules for collating sequence (the sequence in which characters are ordered for sorting), case (whether characters are uppercase or lowercase), and character class and character string equivalence (whether character variants should be handled as equivalents or whether character variants should be handled as one letter). NonStop SQL/MP supports collations for single-byte character sets, but not for doublebyte character sets.
Column Identifier Column Identifier A column identifier is used in some SQLCI report writer statements to specify a column in the result of a SELECT command or a named column in the detail list. { { { { column-name COL number alias detail-alias } } } } column-name is the name of a column specified in the select list. You must qualify an unqualified name if it is the same as any other unqualified name in the select list. For example, if the select list includes EMPLOYEE.DEPTNUM and DEPT.
Columns Columns A column is a vertical component of a table, the relational representation of a field in a record. A column contains one data value for each row of the table. Each SQL column has a name that is an SQL identifier that is unique within the table or view that contains the column. A qualified column name is a column name qualified by the name of the table or view to which the column belongs, or by a correlation name.
COLUMNS Table COLUMNS Table The COLUMNS table is a catalog table that describes the columns of the tables in the TABLES catalog table. Table C-3 describes the contents of the COLUMNS table. Table C-3.
COLUMNS Table Table C-3.
COMMENT Statement 23) were added in version 2, and the columns CPRULESNAME through CHARACTERSET (24 through 26) were added in version 300. The COLUMNS table has a set of column entries for each partition of a partitioned table. All CHAR and VARCHAR fields in the COLUMNS table except for HEADING and HEADINGTEXT use uppercase characters. Guardian names in the table are fully qualified.
Examples—COMMENT To delete a comment, replace all existing comments with a comment that consists of an empty string. For example: COMMENT ON VIEW PERSNL.MGRLIST IS "" CLEAR; SQL stores comments as rows in the COMMENTS catalog table. You access them by querying the table. Each new comment for an object is stored as a row with a sequence number one greater than the highest existing sequence number for a comment on that object. An object can have 10,000 comments.
Comments Comments You can include comments in SQL catalogs, in SQLCI input lines, or in embedded SQL lines. To add or delete comments about an SQL object from an SQL catalog, use the COMMENT statement, which is described in a separate entry. To indicate that an SQLCI line or an embedded SQL line is a comment, precede the comment with two hyphens (--): -- comment SQL considers all text between two hyphens and the end of the physical line to be a comment.
COMMENTS Table COMMENTS Table The COMMENTS table is a catalog table that stores comments and help text for objects defined in the catalog. Each comment or help text line is a row of the table. Table C-4 describes the contents of the COMMENTS table. Table C-4.
COMMIT Option COMMIT Option COMMIT is an option available on some potentially long-running DDL statements that specifies the start time, the timeout period for lock requests, and the handling of retryable errors for the final phase of the operation. COMMIT also includes a ROLLBACK option that directs SQL to cancel changes to the database and terminate the operation instead of proceeding with the final phase. COMMIT is also an option on the CONTINUE statement.
COMMIT Option WHEN READY directs SQL to begin the commit phase as soon as the operation is ready to do so. [ BY ] REQUEST directs SQL to return warning 1619 when the operation is ready to commit and then maintain its ready-to-commit state by performing audit fix-up work as needed until the user responds with a CONTINUE statement that specifies a new COMMIT option. Unless you specified the COMMIT BY REQUEST in effect in an ONCOMMITERROR option, SQL returns warning 1618 just before warning 1619.
COMMIT Option yyyy is a 4-digit year value. hh is a 2-digit hour value (00, 01, ... , 23). nn is a 2-digit minute value (00, 01, ... , 59). If you specify a date but omit time, the time 00:00 is used. If you specify a time with no date, the current date is used. The default for AFTER time is the current date and time. The default for BEFORE time is 00:00:2100.
Considerations—COMMIT Option ROLLBACK [WORK] cancels changes made to the database during the operation and terminates the operation. Considerations—COMMIT Option Each COMMIT option completely replaces the previous one in effect for the operation. For example, if you execute a DDL statement that includes these options: ...
COMMIT WORK Statement *** WARNING from SQL [1618]: The ALTER_TABLE statement is ready to commit. *** WARNING from SQL [1619]: To continue processing, please enter a commit or rollback with a CONTINUE command. D> . . . . The user can respond immediately or enter other commands first. (See CONTINUE Statement on page C-70 for restrictions on other commands.) D>CONTINUE ALTER_TABLE COMMIT AFTER 02:00 +> TIMEOUT NEVER +> ONCOMMITERROR COMMIT BY REQUEST; . . Time passes until after 2:00 am, .
Considerations—COMMIT WORK To unlock all audited tables in any condition, ADD a DEFINE named =_SQL_AUDITONLY_CLOSE_CR and run the COMMIT WORK AUDITONLY command again. Considerations—COMMIT WORK BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction. For more information, see TMF Transactions on page T-6. COMMIT WORK does not cause a write to disk.
Comparison Predicate Comparison Predicate The comparison predicate compares the values of two expressions, the values of two sets of expressions, or the value of an expression and a single value that is the result of a subquery.
Considerations—Comparison Predicate A HEX 20 is always used for padding, regardless of whether a single-byte or double-byte character set is associated with the expression. Both fixed-length and variable-length strings are padded in this way. For example, SQL considers the string “JOE” equal to a value JOE stored in a column of data type CHAR or VARCHAR of width three or more.
Examples—Comparison Predicate expression comparison-operator subquery and the subquery returns no values, the predicate evaluates to null. For example, this predicate evaluates to null because the subquery returns no value (there is no part number with more than 1500 units in stock): PARTNUM = (SELECT PARTNUM FROM ODETAIL WHERE QTY_ORDERED > 1500) Examples—Comparison Predicate These are some simple comparison predicates: CUSTNUM = 3210 The customer number is equal to 3210.
Examples—Comparison Predicate To evaluate TIME1 > TIME2, the range of DATETIME fields for each value is extended to a range that includes all the fields from both values.
COMPUTE_TIMESTAMP Function COMPUTE_TIMESTAMP Function COMPUTE_TIMESTAMP is an SQLCI function that returns a Julian timestamp in Greenwich mean time for a specified date and time. The data type of the returned value is NUMERIC(18) or LARGEINT. COMPUTE_TIMESTAMP works in the report writer commands BREAK FOOTING, BREAK TITLE, DETAIL, PAGE FOOTING, PAGE TITLE, REPORT FOOTING, and REPORT TITLE. It also works in the SQLCI commands EXECUTE and SET PARAM.
Example—COMPUTE_TIMESTAMP Example—COMPUTE_TIMESTAMP This SQLCI command sets the parameter ?D to the Julian timestamp for the date and time in parentheses: SET PARAM ?D COMPUTE_TIMESTAMP (2/8/93 13:25:00:00:00); CONCAT Clause CONCAT is an SQLCI report writer clause that specifies print items to display without intervening or trailing spaces. CONCAT works in the BREAK FOOTING, BREAK TITLE, DETAIL, PAGE FOOTING, PAGE TITLE, REPORT FOOTING, and REPORT TITLE report writer commands.
Considerations—CONCAT For more information about these descriptors, see Display Descriptors for Character Items on page A-61. Note that the Cn.[w] format described under AS is not allowed in the CONCAT clause. STRIP directs the report writer to strip trailing blanks from the values in the list before concatenating them. SPACE number specifies the number of spaces between the items in the list. Each space occupies one single-byte print position, regardless of the character set used.
Concurrency Concurrency Concurrency is access to the same data by two or more processes at the same time. The degree of concurrency available (that is, whether a process that requests access to data that is already being accessed is given access or placed in a wait queue) depends on the purpose of the access (read or update), on the access mode, and on whether virtual sequential block buffering (VSBB) is used for the access.
Concurrency Table C-5.
Concurrency Table C-5. Summary of Concurrent DDL and DML Operations (page 3 of 3) Scan phase A A Wait Wait Change timestamp A1 Wait Wait Wait A Allowed Wait Started operation waits for the operation in progress to complete. The waiting operation might also time out. 1 DDL operation aborts the DML operation 2 Allowed except during commit phase Table C-6.
Effect of VSBB on Concurrency Table C-7. Limits on Concurrent Utility and DML Operations DML Operations SELECT BROWSE SELECT SHARED SELECT EXCLUSIVE DELETE/INSERT UPDATE Without SHARE option A A A1 N With SHARE option A A A A DUP a table A N N N Without SHARE option A A A1 N With SHARE option A A A A Utility Operations COPY from a table LOAD from a table A N Allowed Not allowed 1 Intermittent conflict can occur.
Constraints For sequential insert operations, the disk process acquires a range protector lock on the row that follows the last row inserted. If the last row inserted is at the end of the file, the range protector lock is placed at the end of the file; consequently, other servers cannot insert rows at the end of the table or view. For inserts into a key-sequenced table that uses a SYSKEY column or a timestamp as the primary key, VSBB is the usual method for insert operations.
CONSTRNT Table CONSTRNT Table The CONSTRNT table is a catalog table that describes the constraints placed on tables. Table C-8 describes the contents of the CONSTRNT table. Table C-8.
Considerations—CONTINUE { COMMIT [WORK] options } { ROLLBACK [WORK] } is a commit option that controls the start time for the final phase of the operation and specifies the timeout period for lock requests and the handling of retryable errors during the commit phase of the operation. The commit option can also direct SQL to cancel changes made by the operation and terminate the operation. For more information, see COMMIT Option on page C-52.
Examples—CONTINUE An SQLCI process that does not use a terminal as an IN file (for example, one which reads SQLCI commands from a disk file) also protects you from modifying DEFINEs but does not protect you from terminating the SQLCI process. Terminating the SQLCI process while an operation in progress is waiting for a CONTINUE statement (either by reaching the end of the IN file or by executing an EXIT command) leaves SQL objects in the inconsistent state described earlier in this consideration.
CONTROL EXECUTOR Directive CONTROL EXECUTOR Directive CONTROL EXECUTOR is a DCL directive that allows or prohibits parallel execution of queries. Parallel execution can decrease the elapsed time for processing a query. CONTROL EXECUTOR PARALLEL EXECUTION { ON | OFF } ON executes queries in parallel using multiple SQL executors if parallel execution is possible and efficient. OFF executes queries using one SQL executor. OFF is the default.
CONTROL QUERY Directive WHERE CUSTOMER.CUSTNUM = ORDERS.CUSTNUM STABLE ACCESS; CONTROL QUERY Directive CONTROL QUERY is an SQL compiler directive that controls plans for queries. Options specify whether to resolve names at execution time or at SQL startup time, whether to include hash join algorithms among algorithms considered for executing queries, and whether to optimize query response time for returning a few rows or all rows.
CONTROL QUERY Directive HASH JOIN { OFF | ENABLE | SYSTEM } specifies whether to allow SQL to use hash joins (joining algorithms based around hash tables built largely in memory) when the optimizer expects such joins to improve query performance.
Considerations—CONTROL QUERY If you define a table with a clustering key and use a protection view, you will not be able to use MDAM. For more information, see Protection View on page P-32. Considerations—CONTROL QUERY Static control statements affect only those SQL statements that follow them and are located within the same scope. For example, in the global area or a specific procedure. Dynamic control statements have temporal scoping.
CONTROL TABLE Directive CONTROL TABLE Directive CONTROL TABLE is a DCL directive that specifies performance-related options for DML accesses to a table or view. CONTROL TABLE affects decisions the SQL compiler makes about how to execute DML statements. CONTROL TABLE affects the selection of access paths, join methods, join sequences, lock types, and block buffering and block splitting algorithms.
CONTROL TABLE Directive control options do not apply to references that use only the table name (even if the name expands to the same fully qualified name) nor to references that use a DEFINE name (even if the table associated with the DEFINE is the same table). Similarly, if table is a DEFINE name, the control options apply to subsequent references that use the same DEFINE name. The control options do not apply to references that specify a table name, even if the DEFINE value is the table name.
CONTROL TABLE Directive ACCESS PATH { SYSTEM | PRIMARY | INDEX index } [ MDAM ON [ USE {value|DEFAULT} [KEY] COLUMN[S] ] [ ACCESS { SPARED | DENSE| SYSTEM} ] ] controls the access path for a DELETE, SELECT, or UPDATE, or for the SELECT portion of an INSERT-SELECT. SYSTEM directs SQL to choose the access path PRIMARY specifies the primary access path for the table INDEX specifies index as the access path The default is ACCESS PATH SYSTEM.
CONTROL TABLE Directive USE communicates to SQL how many columns of the key should be used by MDAM. DENSE specifies an adaptive dense algorithm for all columns unless SQL determines that a dense algorithm is not appropriate, as with character or float data types. DENSE is the preferred algorithm when column values are generally sequential. SQL increments each value to obtain the next value. SPARSE specifies a sparse algorithm.
CONTROL TABLE Directive are not in the same order (such as in a repartitioning parallel plan), SQL returns an error and does not choose the key-sequenced merge join method. Because JOIN METHOD applies only to the inner table of a join operation, it is ignored for a table that has a join sequence of 1. JOIN METHOD is normally used only with the JOIN SEQUENCE option. JOIN SEQUENCE { SYSTEM | sequence-number } specifies the sequence in which SQL processes the table for the join.
CONTROL TABLE Directive OPEN { ALL | ACCESSED } [ PARTITIONS ] specifies whether to defer opening indexes and remaining partitions in a table until access to the objects is required: ALL opens all indexes and partitions the first time any partition is accessed ACCESSED opens indexes and partitions only as needed (called “on-demand opens”) The default is OPEN ACCESSED.
CONTROL TABLE Directive SEQUENTIAL BLOCKSPLIT [ FOR INSERT ] { ON | ENABLE } specifies the method of splitting blocks when an INSERT requires a block split: ON splits blocks as if inserts are sequential ENABLE selects the block split algorithm depending on whether sequential inserts are detected The default is SEQUENTIAL BLOCKSPLIT ENABLE. Note.
CONTROL TABLE Directive For an example, see information about local autonomy under Considerations— CONTROL TABLE on page C-85. SYNCDEPTH { 0 | 1 } controls the method of writing to the disk process for nonaudited tables and views: 0 prevents the disk process from sending checkpoint messages. Might slightly improve performance but makes modifications less reliable because an error during an update that modifies several rows halts processing of the statement.
Considerations—CONTROL TABLE If the time elapses before the file system can grant a request to lock data, the statement fails and SQL returns file-system error 40 (Operation timed out) or error 73 (File/Record locked). (This option does not apply to catalog tables.) value waits the specified number of seconds (a number in the range 0.01 to 21474836.47) or wait indefinitely (if value is -1) DEFAULT waits 60 seconds If users often encounter timeouts, increase the time.
Considerations—CONTROL TABLE to table A in the final directive overrides the specific references in the preceding directives: CONTROL TABLE A AS B ACCESS PATH INDEX INDEX2; CONTROL TABLE A AS C ACCESS PATH INDEX INDEX3; CONTROL TABLE A ACCESS PATH INDEX INDEX1; In a host language program, specific scoping rules might apply to the use of the CONTROL TABLE statement. For more information, see the SQL/MP programming manual for your host language.
Considerations—CONTROL TABLE If an index for an UPDATE includes a column being updated, such as UPDATE table SET index-column = index-column + 1 specifying that index as an access path can cause an operation that never ends (the “Halloween problem” in database literature). SQL issues an error message if it compiles an UPDATE statement that can lead to the Halloween problem and a CONTROL TABLE directive prevents it from selecting an alternate access path.
Considerations—CONTROL TABLE For example, assume that a table, CUSTOMER, has three partitions, with first keys 1, 100, and 200 in the CUSTNUM column, and these DEFINE values: $VOL1.SALES.CUSTOMER $VOL2.SALES.CUSTOMER $VOL3.SALES.CUSTOMER =CUST1 =CUST2 =CUST3 If partition =CUST2 is unavailable at execution time, the query >> SELECT * FROM =CUST1 WHERE CUSTNUM BETWEEN 50 AND 300; normally fails completely.
Considerations—CONTROL TABLE underlying table) within the same process flushes the buffer and interrupts the sequential operation. For INSERT and UPDATE operations, any errors the disk process encounters while flushing the buffer are returned to the statement that triggers the buffer flush, rather than to the INSERT or UPDATE statement. For more information, see the discussion on page C-90.
Considerations—CONTROL TABLE The conditions listed in this table trigger flushing the INSERT/UPDATE buffer or invalidating the READ buffers. If a problem such as a path error or disk full error occurs during the flush, the disk process returns the error. Flushes Buffer for INSERTs and UPDATEs Invalidates Buffer for READs A DML operation occurs on a table, interrupting a sequential operation that has already begun. X X The buffer is full. X N.A.
Considerations—CONTROL TABLE Prevents Buffering for UPDATES Prevents Buffering for INSERTS A table has more than one cursor defined by the same process, or an alternate access path is selected. X N.A. SEQUENTIAL READ OFF is in effect for the table. X N.A. SEQUENTIAL UPDATE OFF is in effect for the table. X N.A. SEQUENTIAL INSERT OFF is in effect for the table. N.A. X An INSERT or UPDATE operation is mixed with other operations instead of occurring in sequence.
Examples—CONTROL TABLE Avoid having a process open multiple cursors on a table when any of the cursors is used to update that table. If this is unavoidable, consider using CONTROL TABLE SEQUENTIAL READ OFF. Examples—CONTROL TABLE This example sets SYNCDEPTH to 0 for the nonaudited table DEPT. You might use such a directive before selecting and displaying all rows of the table, for example, but not before a query that changes data. CONTROL TABLE PERSNL.
Examples—CONTROL TABLE UNLOCK statements to make sure the compiler considers the locking mode when selecting an access path for DEPT. CONTROL TABLE SALES.CUSTOMER TABLELOCK ON; LOCK TABLE PERSNL.DEPT IN EXCLUSIVE MODE; CONTROL TABLE PERSNL.DEPT TABLELOCK ON; ... UNLOCK TABLE PERSNL.DEPT; CONTROL TABLE PERSNL.
CONVERT Command CONVERT Command CONVERT is an SQLCI utility that creates an EDIT file containing SQL commands to convert an Enscribe file described in a Data Definition Language (DDL) dictionary to an SQL table described in a specific catalog. You can invoke the EDIT file commands by using the OBEY command to create: a table containing rows that correspond to the record definition of the Enscribe file and columns that correspond to the fields in each record.
CONVERT Command { CHARACTER { { { { { { { { { { { { { NATIONAL { { { { { { { { { { { { map is: { { { { { { { { { { { { ISO88591 ISO88592 ISO88593 ISO88594 ISO88595 ISO88596 ISO88597 ISO88598 ISO88599 KANJI KSC5601 UNKNOWN } } } } } } } } } } } } { { { { { { { { { { { { { ISO88591 ISO88592 ISO88593 ISO88594 ISO88595 ISO88596 ISO88597 ISO88598 ISO88599 KANJI KSC5601 UNKNOWN DEFAULT } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } simple-fileset-list TO files catalog-spec
CONVERT Command MAP NAME[S] { map } { ( map [ ,map ] ... ) } overrides the default names (and locations) used to generate names for secondary partitions of tables and for indexes. map is: simple-fileset-list TO files simple-fileset-list is a simple fileset list that specifies the secondary partitions and alternate-key files of the Enscribe file to map. For more information about simple fileset lists, see Filesets on page F-29. TO files specifies the names and locations for the new partitions and indexes.
CONVERT Command COMMENTS writes all qualifier names and their corresponding level numbers as comments in the EDIT file specified in the SOURCE option (or the default EDIT file, CNVSRC). DICTIONARY subvol specifies the name of the subvolume (or an equivalent DEFINE) that contains the DDL record definition ddl-record-name. The default is the current default subvolume. FILE IS enscribe-file specifies the name (or an equivalent DEFINE) of the Enscribe file to convert.
CONVERT Command The default is VARCHARS. REDEFINE ( redef-spec [ , redef-spec ] ... ) specifies that original items (groups or fields) are to be converted to columns based on redefinitions of the items. Unless you include the REDEFINE option, all items are converted according to the definition of the original items, and the REDEFINES clause in the DDL record definition is ignored.
CONVERT Behavior For more information about how the conversion is performed, see Conversion of DDL Elementary Items on page C-103. NATIONAL { ISO88591 | ISO88592 | ... | DEFAULT } specifies the character set for PIC N fields. If you omit the NATIONAL option, all items are converted according to the definitions of the original items. The character set can be one of the single-byte character sets ISO 8859/1 through ISO 8859/9, or one of the double-byte character sets Kanji or KSC5601.
Enscribe Files and DDL Record Definitions Executing the EDIT file with OBEY requires authority to read the EDIT file; authority to write to the affected catalogs (for creating the table and indexes); authority to read the DDL dictionary, the Enscribe source file, and the catalog in which the table is described; and authority to read and write to the table (for loading the table and indexes).
DDL Clause Mapping CONVERT uses the alternate key specifications in the Enscribe file to define indexes on the table. Because SQL requires a separate index file for each alternate key, a CREATE INDEX command is generated for each alternate key named in the DDL record definition. Indexes are created on the same volume and subvolume as the table. Index names are created by appending numbers to the table name. The name of the tables, therefore, should be shorter than eight characters.
DDL Clause Mapping VALUE IS "default-string" VALUE IS ALL "default-character" The two forms of the DDL VALUE IS clause used with PIC N fields are: VALUE IS N"default-string" VALUE IS ALL N"default-character" If the CHARACTER option, the NATIONAL option, or the NATIONAL DEFAULT option is not specified, the VALUE IS clause is converted: DEFAULT "default-string" DEFAULT "default-character-repeated" default-string specifies a default value for the column.
Conversion of DDL Elementary Items Conversion of DDL Elementary Items CONVERT converts each elementary field item in the record definition to a column definition. The table that is created as a result of the conversion can contain at most 450 columns. CONVERT uses the name of a DDL field as the name for the corresponding column but replaces any hyphens (-) in the name with underscores (_). If the data type of a DDL field is equivalent to an SQL data type, the column is assigned the equivalent data type.
Conversion of DDL Elementary Items Conversion of DDL Character Strings Option DDL Data Type SQL Data Type None PIC A(j) PIC X(j) PIC X(j) PIC X(j) PIC N(k) PIC X(m) TYPE CHARACTER j CHAR(j) PIC A(j) CHAR(x) CHARACTER SET set PIC X(j) CHAR(x) CHARACTER SET set TYPE CHARACTER j CHAR(x) CHARACTER SET set NATIONAL set PIC N(k) CHAR(y) CHARACTER SET set NATIONAL DEFAULT PIC N(k) NCHAR(k) CHARACTER set set An SQL-supported character set j The number of single-byte characters k The number
Conversion of DDL Elementary Items DDL NonStop SQL/MP TYPE BINARY 32,scale NUMERIC (9,scale) TYPE BINARY 32, scale UNSIGNED NUMERIC (9,scale) UNSIGNED TYPE BINARY 64 LARGEINT TYPE BINARY 64,scale NUMERIC (18,scale) nines is: { 9 (int [V9 (scale)] } { V9 (scale) } Types DDL NonStop SQL/MP PIC nines PIC nines or if precision is greater than 9, PIC S nines PIC S nines PIC S nines PIC nines S PIC S nines PIC T PIC S9 PIC T9(int) PIC S9(int+1) PIC TV9(scale) PIC S9V9(scale) PIC T9(int)
Conversion of DDL Elementary Items DDL NonStop SQL/MP TYPE LOGICAL 1 PIC X TYPE LOGICAL 2 SMALLINT TYPE LOGICAL 4 INTEGER This special DDL group is converted to the SQL VARCHAR data type: 02 A-VARCHAR. 03 LEN PIC S9(4) COMP. 03 VAL PIC N(len). The field names in the special DDL group have these meanings: A-VARCHAR is the name of the special DDL VARCHAR group. Any valid DDL group name can be used in place of A-VARCHAR. LEN is a numeric field representing the actual length of the string.
DDL Groups With VARCHARS Option CONVERT Option SQL Data Type PIC N Field SQL Data Type PIC X, PIC A or CHARACTER Field Without VARCHARS Option None CHAR(m) CHARACTER set CHAR(j) CHAR(x) CHARACTER SET set NATIONAL set CHAR(y) CHARACTER SET set NATIONAL DEFAULT NCHAR(k) set An SQL-supported character set j The number of single-byte characters k The number of double-byte characters m The number of characters; m is twice the corresponding k x The number of characters; x equals j for single-byte cha
Partition Attributes of Tables and Indexes Partition Attributes of Tables and Indexes If you convert a partitioned Enscribe file to a partitioned table, these rules apply: The partitions of the table are derived from the partitions of the file. Each table partition, other than the first one, is created on the same subvolume as the corresponding file partition, unless you use the MAP NAMES option to override this naming pattern.
Examples—CONVERT subvolume $VOL1.SALES. ORDERTAB resides on the current default subvolume and is described by the record definition ORDER in a DDL dictionary. The SQL catalog in which you want ORDERS to be described also resides on the subvolume $VOL1.SALES. This command creates an EDIT file named CNVSRC that contains the commands needed to perform the conversion: >> CONVERT RECORD ORDER TO TABLE $VOL1.SALES.ORDERS >+ CATALOG $VOL1.
Examples—CONVERT SOURCEREC ORDER, USESQLNULLS; Note that the record definition has been altered by eliminating the DDL group names and by creating a column for each elementary item in each group. CONVERT makes these column names unique by appending 2 to the MONTH, DAY, and YEAR columns derived from the DDL DELDATE group. You can change these names by editing the file.
Examples—CONVERT string --WARNING The default value is truncated. G VARCHAR(8) NOT NULL , -- WARNING - PIC N PRIMARY KEY C ) If the DDL default string is longer than eight characters, CONVERT truncates the default string to eight bytes long. This example illustrates more features of the CONVERT command. The DDL record definition follows: RECORD SCHEDULE. FILE IS "$VOL3.DDL.SCHEDULE" KEY-SEQUENCED. 02 EMP-SCHEDULE. 03 EMPNUM PIC 9(5). 03 EMPNUM-KEY REDEFINES EMPNUM. 05 DEP-KEY PIC X(2).
Examples—CONVERT -03 DAY-SCHED OCCURS 1/5 TIMES -04 DAYNUM DAYNUM_1 PIC X(2) -04 SHIFTS OCCURS 1/2 TIMES -05 START-HOUR START_HOUR_1_1 PIC 9(2) -05 END-HOUR END_HOUR_1_1 PIC 9(2) -05 START-HOUR START_HOUR_1_2 PIC 9(2) -05 END-HOUR END_HOUR_1_2 PIC 9(2) ... DAYNUM_5 PIC X(2) -04 SHIFTS OCCURS 1/2 TIMES -05 START-HOUR START_HOUR_5_1 PIC 9(2) ... END_HOUR_5_2 PRIMARY KEY ( DEP_KEY , EMP_KEY ) ) ORGANIZATION KEY SEQUENCED ...
CONVERTTIMESTAMP Function CONVERTTIMESTAMP Function CONVERTTIMESTAMP is a function that converts a Julian timestamp to a DATETIME value. It returns a value of DATETIME that has the range of fields YEAR TO FRACTION(6). CONVERTTIMESTAMP ( julian-timestamp ) julian-timestamp is an expression that evaluates to a Julian timestamp, which is a LARGEINT value.
COPY Command COPY in-file [ [, out-file [ [,] option ] ... ] ] ; [ ,, option [ [,] option ] ... ] option is: { { { { { control-option in-option out-option display-option move-option } } } } } control-option is: { { { { { { { { { { { { { { { { { { { ALLOWERRORS [ ON | OFF | num ] COUNT num-records FIRST { { { { { ordinal-record-num KEY record-spec KEY (key-value [, key-value]... ) key-specifier ALTKEY (key-value [, key-value ] ...
COPY Command out-option is: { { { { { { { { { { BLOCKOUT out-block-length } EBCDICOUT } FOLD } PAD pad-character } RECOUT out-record-length } { REWINDOUT | NO REWINDOUT } } SKIPOUT num-eofs } { UNLOADOUT | NO UNLOADOUT } } VAROUT } SHAREOUT } display-option is: { { { { { { O[CTAL] D[ECIMAL] H[EX] BYTE A[SCII] NO HEAD } } } } } } move-option is: { { { { { { { { { { { { { SOURCEDICT dictionary-name SOURCEREC ddl-record-name TARGETDICT dictionary-name TARGETREC ddl-record-name MOVE { source-name TO targ
COPY Command out-file is the name (or an equivalent DEFINE) of the table, file, tape, process, printer, spooler, or terminal to which to copy the data. If you copy to a file or table, the file or table must exist before you execute the COPY. If you omit out-file, SQL uses the current OUT file. ALLOWERRORS [ ON | OFF | num ] specifies action when conversion errors occur.
COPY Command If you do not specify this option for an Enscribe ASCII numeric decimal field, a conversion error occurs for any record in which the field contains blanks. UNSTRUCTURED (for copying from a table or disk file only) directs COPY to handle the data as a sequence of bytes, ignoring any record structures normally recognized for the table or file. The UNSTRUCTURED option lets you examine only the one partition named as the source file in the command.
COPY Command tables and Enscribe files to Enscribe files. The SQLNULLABLE option for Enscribe to SQL loading automatically enables the USESQLNULLS option. in-option specifies characteristics of the input file. It is identical to the in-option for the LOAD command. For information on in-option clauses, see LOAD Command on page L-18. out-option specifies characteristics of the output file.
COPY Command In a conversion between ASCII and EBCDIC, the symbols representing each character are the same in ASCII and EBCDIC except for: ASCII EBCDIC Exclamation point Logical OR Left square bracket Cent sign Right square bracket Exclamation point Circumflex Logical NOT sign The conversion is done without regard to the data types of fields or columns of the input, so undesired changes to the data can occur if you use EBCDICOUT with input that is not composed of simple character data.
COPY Command out-block-length is greater than 4096, out-record-length is 4096. If you do not specify out-block-length and if out-file is an unstructured disk file, if in-file is an SQL table, VAROUT is not specified, and no display option was specified, out-record-length is the length of the logical record specified by TARGETREC, or—if TARGETREC is not specified—the length of the logical record implied by the description of the input table. Otherwise, out-record-length is 132.
COPY Command { UNLOADOUT | NO UNLOADOUT } (for copying to magnetic tapes only) specifies whether the tape is unloaded when rewinding occurs. The default is UNLOADOUT (the tape is unloaded when it is rewound). VAROUT (for copying to Enscribe files only) writes variable-length, blocked records. Each Enscribe variable-length record is preceded by a one-word indicator containing the record length in bytes.
Considerations—COPY SHAREOUT opens the output file with SHARED access. If you omit SHAREOUT, the output file is opened with EXCLUSIVE access. display-option (for copying to Enscribe files only) specifies the format for displaying the file: O[CTAL] Display in octal and ASCII D[ECIMAL] Display in decimal and ASCII H[EX] Display in hexadecimal and ASCII.
Considerations—COPY From one table to another—each source row is inserted as a row in the target table. You might want to use COPY instead of LOAD for these reasons: You can copy data within a user-defined TMF transaction. You can copy data to an unstructured file or a nondisk file. You can append or insert data without erasing existing data. These rules govern the transfer of data across character sets. A COPY that violates these rules terminates with an error.
Considerations—COPY The display includes the file name, the ordinal number of each row or record, the length of each record (in decimal bytes), and the ASCII representation of each line. If you use COPY to write to an audited file or table, the write always takes place within a TMF transaction. COPY starts a transaction if a user-defined transaction is not in progress.
Considerations—COPY When a source column is undefined and the target column is defined with the NO DEFAULT clause, an error occurs. The source row must supply a value for every column of the target row that is defined with the NO DEFAULT clause. In addition, a target row must satisfy any constraint defined on the table to be inserted in the table. The TRUNC option determines whether values are truncated. For more information, see INSERT Statement on page I-14.
Enscribe Field Formats In general, if a source record from a non-SQL source does not end exactly at a field boundary, an error occurs. These exceptions apply: If the record ends in the middle of a VARCHAR field, the end of the record defines the end of the VARCHAR data. If the file is an EDIT file and the record ends in the middle of a field, SQL adds enough blanks to the end of the input record to fill the field. In such a case, blanks must be acceptable in that column of the source record.
Examples—COPY Examples—COPY Suppose that in addition to the table EMPLOYEE described in the catalog $VOL1.PERSNL, you have created an identical table EMPLOYEE described in a catalog named $VOL2.TESTC. You must qualify the table names enough to identify the location of each one uniquely. COPY determines which catalogs to use, such as, >> COPY $VOL1.PERSNL.EMPLOYEE, $VOL2.TEST.EMPLOYEE; If the default subvolume is $VOL1.PERSNL, this command performs the same function: >> COPY EMPLOYEE, $VOL2.TEST.
Correlation Names The CUSTNAME value is truncated because the column length is less than the field length. The default value is used for the CREDIT column in each new row of CUSTOMER because no source field maps to this column. This example demonstrates the MOVE option. This command copies data from the table $VOL1.SALES.PARTS to the Enscribe file $TESTVOL.SALES.PARTS, copying only the PARTNUM and PARTDESC columns to the PARTNUMR and PARID columns respectively: >> COPY $VOL1.SALES.PARTS, $TESTVOL.
Correlation Names A correlation name can be explicit or implicit. An explicit correlation name is an SQL identifier associated with a table or view in the FROM clause of a SELECT statement, in the select-statement of an INSERT statement, or in a subquery. The name must be unique within the FROM clause. An explicit correlation name is known only to the statement in which you define it. You can use the same identifier as a correlation name in another statement.
COUNT Function COUNT Function COUNT is a function that counts the number of rows that result from a query or the number of rows that contain a distinct value in a specific column. The result of COUNT is data type LARGEINT. The result can never be null. COUNT { ( * ) } { (DISTINCT column-name) } * specifies that COUNT should not exclude null values from the aggregate set. If the set is empty, COUNT returns zero.
CPRLSRCE Table CPRLSRCE Table The CPRLSRCE table is a catalog table that contains source definitions for each collation described in the CPRULES table. Table C-9 lists the contents of the CPRLSRCE table. Table C-9.
CREATE CATALOG Statement CREATE CATALOG Statement CREATE CATALOG is a DDL statement that creates a new catalog. Each new catalog includes a complete set of catalog tables and indexes. For information about the contents of a catalog, see Catalogs on page C-8. CREATE CATALOG [ catalog ] [ SECURE "rwep" ] [ PHYSVOL volume-name ] [ attribute-spec ] attribute-spec is: { EXTENT { (pri-ext-size[,sec-ext-size]) } { ext-size } { MAXEXTENTS integer } Note.
CREATE CATALOG Statement attribute-spec specifies physical file attributes for the file that holds catalog tables and indexes.
Considerations—CREATE CATALOG Considerations—CREATE CATALOG CREATE CATALOG requires authority to write to the SQL.CATALOGS table, because SQL adds an entry to that table for the new catalog. The owner of the new catalog is the user whose process created the catalog. However, the operations allowed on the tables and indexes that make up the catalog itself (as described under Catalogs on page C-8) are more limited than those allowed on ordinary tables and indexes, even for the owner.
Examples—CREATE CATALOG Specify a direct subvolume for catalog Specify a virtual volume and subvolume for catalog and a physical volume that belongs to the virtual volume in PHYSVOL. Do not include node name in your volume name. If you specify a virtual volume for catalog and omit the PHYSVOL option, SQL can distribute catalog tables among multiple physical volumes in the virtual volume.
Examples—CREATE CATALOG This example creates a catalog named PERSNL on node \SYS1 and volume $VOL1, with EXTENT (100,200). MAXEXTENTS will be calculated by SQL: CREATE CATALOG \SYS1.$VOL1.PERSNL EXTENT (100,200); This example creates a catalog named PERSNL on node \SYS1 and volume $VOL1, with EXTENT (100,200) and MAXEXTENTS 150: CREATE CATALOG \SYS1.$VOL1.
CREATE COLLATION Statement CREATE COLLATION Statement CREATE COLLATION is a DDL statement that creates a collation. CREATE COLLATION name { FROM source } { LIKE coll [ WITH COMMENTS ] } [ CATALOG catalog ] [ PHYSVOL volume-name ] name is a Guardian name (or an equivalent DEFINE) that is the name of the new collation. If SMF is installed on your node, the volume portion of name can be a virtual or direct volume.
Consideration—CREATE COLLATION Consideration—CREATE COLLATION CREATE COLLATION requires read and write authority for the catalog in which the new collation is registered. The FROM clause requires read authority for the source file. The LIKE clause requires read authority for the existing collation coll and for the associated catalog tables. Example—CREATE COLLATION This example statement creates a collation named TRANSL2 from the definition in the EDIT file $DATA.COLL.
CREATE CONSTRAINT Statement CREATE CONSTRAINT Statement CREATE CONSTRAINT is a DDL statement that defines a constraint for a table. When a constraint is in effect, all rows in the table, either directly or through a view, must satisfy the constraint. CREATE CONSTRAINT constraint ON table CHECK condition [DEFERRED] constraint is the name of the constraint. constraint must be an SQL identifier that is unique for the associated table.
Considerations—CREATE CONSTRAINT enables concurrent DML operations on the table to complete without waiting behind the locks. Considerations—CREATE CONSTRAINT To create a constraint, you must be a generalized owner of the underlying table. You must also have authority to read the table and authority to write to affected catalogs. CREATE CONSTRAINT requires an exclusive open on table, including any partitions. The operation fails if the table is inaccessible or if other users have the table open.
Examples—CREATE CONSTRAINT Examples—CREATE CONSTRAINT This example creates a constraint to ensure that only values greater than $10,000 are entered in the SALARY column: CREATE CONSTRAINT ASAL ON \SYS1.$VOL1.PERSNL.EMPLOYEE CHECK SALARY > 10000; This example creates a constraint that enforces a relationship between two items in a row. In this case, the constraint ensures that a delivery date for an order is not earlier than the date the order was taken: CREATE CONSTRAINT DATE_CONSTRNT ON SALES.
CREATE INDEX Statement CREATE INDEX Statement CREATE INDEX is a DDL statement that creates an index based on one or more columns of a table. CREATE [ UNIQUE ] INDEX index ON table ( col [ ASC[ENDING] ] [ collate-spec ] [ DESC[ENDING] ] [ , col [ ASC[ENDING] ] [ collate-spec ] ] ...
CREATE INDEX Statement UNIQUE specifies that values in the column or set of columns that make up the index field cannot be the same for two or more rows of the table. For indexes with multiple columns, the value of the columns as a group determines uniqueness, not the values of the individual columns. SQL cannot create a UNIQUE index if any col specified for the index is a column that allows null values or if the underlying table has duplicate row values for the group of indexed columns.
CREATE INDEX Statement COLLATE { collation | CHARACTER SET } specifies an alternate collating sequence for the column within the index. You can use this clause only if the associated column is of a data type that allows a collating sequence as part of its definition.
CREATE INDEX Statement In all other cases, the default is INVALIDATE. KEYTAG key-specifier specifies a two-byte key specifier unique among indexes for the table that is stored in every row of the index. If you omit the KEYTAG clause, SQL generates a keytag for the table. System-generated keytags are sequential numbers, beginning with one. User-specified keytag values can be either two bytes of character data or a SMALLINT UNSIGNED value in the range 1 through 65535.
CREATE INDEX Statement [ [ WITH SHARED ACCESS [ [ [ [ NAME operation-name ] ] REPORT [ TO collector | ON | OFF ] ] ] { COMMIT [WORK] commit-options } ] { ROLLBACK [WORK] } ] specifies that the table being indexed be available for read and write access by DML statements and read access by utilities throughout most of the create index operation.
Considerations—CREATE INDEX ICOMPRESS* Controls key compression in index blocks. ISLACK Sets percent of slack in index blocks. Default is value of the SLACK attribute. LOCKLENGTH Sets number of leading bytes in the key to use for generic locks. Default is 0, which specifies the entire key. MAXEXTENTS Sets maximum extents. Default is 160. NOPURGEUNTIL Sets date after which drop is allowed. Default allows immediate drop. SERIALWRITES* Specifies serial or parallel writes.
Considerations—CREATE INDEX If you specify WITH SHARED ACCESS, CREATE INDEX does not lock out INSERT, DELETE, and UPDATE operations to the table being indexed except for a relatively brief period during the final phase. CREATE INDEX does not preempt other processes to acquire its lock even then. In addition, WITH SHARED ACCESS includes a COMMIT option that allows you to control when the operation starts the commit phase and whether to retry errors such as time outs during lock requests.
Considerations—CREATE INDEX When performed on a base table whose partitions have valid TMF online dumps, a CREATE INDEX operation that uses WITH SHARED ACCESS generates audit information for each of the new index partitions. The index partitions might not audit to the same audit trail as the source. In addition, a CREATE INDEX operation that uses WITH SHARED ACCESS turns off the AUDITCOMPRESS file attribute for the table being indexed for the duration of the operation.
Examples—CREATE INDEX the table and its index’s partitions must be cataloged in version 350 or later catalogs for the CREATE INDEX catalog to succeed. Examples—CREATE INDEX This example creates an index on the LAST_NAME and FIRST_NAME columns of table EMPLOYEE: CREATE INDEX \SYS1.$VOL1.PERSNL.EMPLOYE0 ON \SYS1.$VOL1.PERSNL.EMPLOYEE (LAST_NAME, FIRST_NAME) CATALOG \SYS1.$VOL1.
Examples—CREATE INDEX This example prevents the addition of duplicate employee names to the employee table by creating a unique index on the LAST_NAME and FIRST_NAME columns: CREATE UNIQUE INDEX PERSNL.XEMPNAM ON PERSNL.EMPLOYEE (LAST_NAME FIRST_NAME) CATALOG PERSNL; This example creates a nonpartitioned index using a simple parallel sort with subsorts. The DEFINEs set up a sort operation with four subsort processes.
CREATE SYSTEM CATALOG Command CREATE SYSTEM CATALOG Command CREATE SYSTEM CATALOG is an SQLCI command that allows the local super ID to create the system catalog, including the SQL.CATALOGS table, when NonStop SQL/MP is first installed on a node. CREATE SYSTEM CATALOG [ [ catalog-name ] [ PHYSVOL volume-name ] ]; catalog-name specifies the location of the system catalog on the local node. catalog-name is the volume and subvolume name of the new system catalog (or an equivalent CLASS CATALOG DEFINE).
Examples—CREATE SYSTEM CATALOG The security defined for the CATALOGS table is the default security for the super ID. Use ALTER TABLE to alter the security as needed to grant other users authority to read and write to the table to create catalogs. If you execute CREATE SYSTEM CATALOG when a system catalog already exists on the node, SQLCI reports an error.
CREATE TABLE Statement CREATE TABLE Statement CREATE TABLE is a DDL statement that creates a table. CREATE TABLE requires you to specify a table name and a description of each column in the table, but allows you to specify many other attributes of the table as well. A typical table definition also includes a description of the primary key or clustering key for the table (which affects data retrieval and storage for the table) and the name of the catalog to receive the description of the table.
CREATE TABLE Statement attribute-spec is: {| {| {| {| {| {| {| {| {| [| {| {| {| {| {| {| {| {| ALLOCATE integer { AUDIT | NO AUDIT } { AUDITCOMPRESS | NO AUDITCOMPRESS } BLOCKSIZE integer { BUFFERED | NO BUFFERED } { CLEARONPURGE | NO CLEARONPURGE } { DCOMPRESS { 1 | 2 } | NO DCOMPRESS } EXTENT { ( pri-ext-size[,sec-ext-size] ) } { ext-size } FORMAT {1|2} { ICOMPRESS | NO ICOMPRESS } LOCKLENGTH integer MAXEXTENTS integer NOPURGEUNTIL date RECLENGTH integer { SERIALWRITES | NO SERIALWRITES } TABLECODE int
CREATE TABLE Statement You can use the CREATE TABLE command with the LIKE option to create a table with a similar schema as a partition of an existing table. You can also use the PARTITION clause with LIKE to create a partitioned table where all partitions of the new table will be like the specified partition of the original table. Each partition of the new table will be created using the same partition format as the specified partition of the original table.
CREATE TABLE Statement DEFAULT default | NO DEFAULT specifies a default value for the column or specifies that the column does not have a default value. default can be a literal of one of the special values CURRENT, SYSTEM, or NULL. For information, see DEFAULT Clause on page D-26. You must specify the DEFAULT clause if you specify the NOT NULL clause because the default is DEFAULT NULL. NOT NULL specifies that the column cannot contain any null values.
CREATE TABLE Statement PHYSVOL volume-name If SMF is installed on your node, the PHYSVOL option directs SQL to override SMF and place the table or primary table partition on the physical volume volume-name. For volume-name, specify either a physical volume or equivalent DEFINE. Do not specify the node name in your volume name. This option is available only if you specify a virtual volume for table.volume-name must belong to the virtual volume you specify.
CREATE TABLE Statement PARTITION ARRAY { STANDARD | EXTENDED | FORMAT2ENABLED } specifies the type of partition array created for the underlying table and all associated indexes: EXTENDED specifies the extended partition array available for versions 320 and later of NonStop SQL/MP STANDARD specifies the type of array used by default by NonStop SQL/MP FORMAT2ENABLED specifies a Format 2 enabled table that can contain any combination of Format 1 or Format 2 partitions.
CREATE TABLE Statement SIMILARITY CHECK { ENABLE | DISABLE } authorizes or prohibits similarity checks on the table. The default is SIMILARITY CHECK DISABLE. Tables that authorize similarity checks (SIMILARITY CHECK ENABLE) have version 310 or later. Such tables cannot be registered in older catalogs or accessed by older versions of NonStop SQL/MP. attribute-spec specifies physical file attributes for the file that holds the table.
Considerations—CREATE TABLE SERIALWRITES Specifies serial or parallel writes. Default is serialwrites. TABLECODE Sets tablecode. Default is 0. VERIFIEDWRITES Controls verification of writes to disk. Default is no verification. For more information, see the entry for a specific attribute. Considerations—CREATE TABLE CREATE TABLE requires authority to write to the catalogs that receive the description of the table and any partitions of the table.
Examples—CREATE TABLE Only key-sequenced tables can be Format 2 enabled and might have partitions that are either Format 1 or 2. Relative and entry-sequenced tables can only be Format 1 enabled and must have Format 1 partitions. If you attempt to specify FORMAT 2 or PARTITION ARRAY FORMAT2ENABLED for a relative or entry-sequenced table, you will receive an error. Specifying FORMAT 1 for relative or entry-sequenced partitions is allowed.
Examples—CREATE TABLE This example creates a table with column headings. The EMP table contains three columns, EMPNUM, EMPNAME, and SALARY, that are assigned the headings “Employee/Number,” “Employee Name,” and “Monthly Salary,” respectively. The primary key is EMPNUM.
Examples—CREATE TABLE \SYS1.$VOL3.SALES.ODETAIL CATALOG \SYS1.$VOL1.SALES EXTENT (16368,64) MAXEXTENTS 919 FIRST KEY 040000 , ... --indicates 20 more \SYS5.$VOL23.SALES.ODETAIL --partition CATALOG \SYS5.VOL1.SALES --specifications EXTENT (16368,64) MAXEXTENTS 919 FIRST KEY 980000 ) LOCKLENGTH 6 EXTENT (16368,64) MAXEXTENTS 919 NOPURGEUNTIL OCT 31 2003, 23:59 NO AUDIT; Some of the attributes specified apply to the entire table and some only to the primary partition.
Examples—CREATE TABLE , C VARCHAR(300) NO DEFAULT NOT NULL , PRIMARY KEY A ) PARTITION ( $VOL2.SUBV1.PARTTIME FIRST KEY TIMESTAMP "1989-12-1:12:00:00.000000" ); This example column definitions show various combinations of DEFAULT, NULL, and NOT NULL clauses and their effects. Column PARTNUM can contain null values. Because no DEFAULT clause is specified, the column is initialized to a null value when a row is inserted without supplying a value for PARTNUM: ( ... PARTNUM NUMERIC (4) UNSIGNED ...
CREATE VIEW Statement SALESREP PIC X(4) NO DEFAULT NOT NULL, CUSTNUM PIC X(4) NO DEFAULT NOT NULL, PRIMARY KEY ( ORDERNUM ) ) PARTITION ($VOL2.SALES.ORDERS FIRST KEY “D”, $VOL3.SALES.ORDERS FIRST KEY “K”, $VOL4.SALES.ORDERS FIRST KEY “S”) FORMAT 2; This example creates a Format 2 enabled table with four partitions. The first three partitions are Format 1 and the last is Format 2: CREATE TABLE $VOL1.SALES.
CREATE VIEW Statement [ ( new-name [ , new-name ] ... ) ] specifies names for the columns of the view and, optionally, headings for the columns. If you do not specify this clause, columns in the view have the same names as the columns in the select list of select-statement.
CREATE VIEW Statement The FROM clause can refer to one table (with a correlation name, if desired), but cannot refer to another view. The WHERE clause can refer only to columns in its select list. The select list cannot include expressions or functions and cannot refer to duplicate column names. The statement cannot be combined with another SELECT statement using a UNION operator and cannot include subqueries, the keyword DISTINCT, or the GROUP BY or HAVING clause.
Considerations—CREATE VIEW You must also ensure that purge authority includes the users with authority to purge the underlying table. For a shorthand view, only purge authority has meaning, although you must specify a complete security string. Anyone with authority to read the underlying tables and views can also read the shorthand view. For information, see Security on page S-11.
Considerations—CREATE VIEW The CREATE VIEW statement, including any name expansion from the use of asterisks in column, view, and table specifications, can have a maximum of 3,000 bytes. The data types of the columns of the view are inherited from the columns of the table or view in which they are defined. A view can have as many as 200 to 400 columns, depending on the size of the column definitions. The column definitions for the view must fit in a file label.
Examples—CREATE VIEW Examples—CREATE VIEW This example creates a shorthand view that includes part numbers and supplier numbers for parts in which fewer than ten are in stock: CREATE VIEW GETPARTS (PNUM, SNUM) AS SELECT P.PARTNUM, SUPPNUM FROM PARTLOC P, PARTSUPP S WHERE P.PARTNUM = S.PARTNUM AND QTY_ON_HAND < 10 CATALOG $VOL1.INVENT; This example creates a protection view on the table EMPLOYEE that contains rows with employee numbers greater than 1000.
Examples—CREATE VIEW This statement creates a view that joins the CUSTOMER and ORDERS tables in a better way than in the previous example. The CREATE VIEW statement uses a WHERE clause to join the two tables only at rows in which CUSTNUM values are equal. The view can never contain more rows than the number of rows in the largest table. CREATE VIEW GOOD (C_CUSTNUM,O_CUSTNUM,CUSTNAME,STATE,ORDERNUM) AS SELECT C.CUSTNUM,O.CUSTNUM,CUSTNAME,STATE,ORDERNUM FROM SALES.CUSTOMER C, SALES.ORDERS O WHERE C.
CURRENT Function CURRENT Function CURRENT is a function that returns the current local date, time, or both as a value of type DATETIME. SQL evaluates CURRENT only once in an SQL statement. If you use CURRENT more than once in the same statement, each reference returns the same value.
CURRENT_TIMESTAMP Function CURRENT_TIMESTAMP Function CURRENT_TIMESTAMP is an SQLCI function that returns a Julian timestamp in Greenwich mean time for the current date and time. The data type of the returned value is NUMERIC(18) or LARGEINT. CURRENT_TIMESTAMP works in the SQLCI commands BREAK FOOTING, BREAK TITLE, DETAIL, EXECUTE, PAGE FOOTING, PAGE TITLE, REPORT FOOTING, REPORT TITLE, and SET PARAM. It does not work in DML statements or other SQL statements.
Cursors Cursors A cursor is a named mechanism defined by a SELECT statement and used in a host language program. An opened cursor can be thought of as scanning the set of records specified by the SELECT operation. The program processes a cursor like a sequential file, fetching rows one by one. The row being fetched is at the current position of the cursor. The program can use the current cursor position to designate a row to delete or update. A cursor name is an SQL identifier.
Cursor Position Cursor Position Cursor position is similar to record position in a sequential file. Operations cause the cursor to be positioned: OPEN Before the first row FETCH On the retrieved row (the current position) DELETE Between rows UPDATE No change (the current position) CLOSE No position Your SELECT determines the order in which rows are returned through a cursor. To specify the order, include an ORDER BY clause; otherwise, the order is undefined.
D Data Dictionary The SQL/MP data dictionary is the set of all the catalogs on a network, together with the disk file labels for all the objects described in the catalogs. A catalog is a set of tables and indexes that describes SQL/MP objects. For information, see Catalogs on page C-8. Disk file labels are stored in directories on disk volumes. Each disk volume has a directory that contains one file label for each file on the volume.
Data Types { { { { { { { { { DATETIME [ start-date-time TO ] end-date-time DATE TIME TIMESTAMP INTERVAL start-field [(sf-prec)] [ TO end-field ] } } } } } } } } } charset is: { { { { { { CHAR[ACTER] SET { { { { { { ISO88591 ISO88592 ISO88593 ISO88594 ISO88595 ISO88596 ISO88597 ISO88598 ISO88599 KANJI KSC5601 UNKNOWN } } } } } } } } } } } } CHAR[ACTER] specifies a column with character data.
Data Types the column is a single-byte or double-byte character set, and on whether the data type declaration for the column includes the VARYING clause. Data Type KeySequenced Relative or EntrySequenced Single-byte unvarying 4061 4072 Single-byte VARYING 4059 4070 Double-byte unvarying 2030 2036 Double-byte VARYING 2029 2035 CHAR[ACTER] SET { ISO88591 | ISO88592 | ...
Data Types collation is the name of an existing collation (or an equivalent DEFINE) that specifies a collating sequence that is associated with the same character set as the column CHARACTER SET specifies a collating sequence based on the binary value of characters in the column Specifying a collation for the column might effect the performance of certain queries using the column because SQL cannot perform hash joins or hash groupings on columns associated with collations. The default is CHARACTER SET.
Data Types SIGNED or UNSIGNED indicates whether the column values are signed or unsigned. If digits is 10 or more, the values must be SIGNED. The default is NUMERIC (1,0) SIGNED. { SMALLINT | INT[EGER] | LARGEINT } [SIGNED | UNSIGNED] defines a binary integer column: SMALLINT Two bytes, SIGNED or UNSIGNED; stores integers in the range unsigned 0 to 65535 or signed -32768 to +32767.
Data Types SIGNED or UNSIGNED indicates whether the column values are signed or unsigned. The sign is stored as the first bit of the leftmost byte. If digits is 10 or more, the values must be signed. The default is DECIMAL (1,0) SIGNED. PIC[TURE] [S]{ 9(integer) [ V[9(scale)] ] } { V9(scale) } [ DISPLAY [ SIGN IS LEADING ] ] [ COMP ] specifies a numeric column. If you specify COMP, the column is binary and equivalent to the data type NUMERIC.
Data Types TIME is equivalent to DATETIME HOUR TO SECOND. TIMESTAMP is equivalent to DATETIME YEAR TO FRACTION(6). INTERVAL start-field [ (sf-prec) ] [ TO end-field ] specifies a column that represents a duration of time as a year-month or day-time interval.
DATE Data Type DATE Data Type An item with data type DATE represents a date according to the Gregorian calendar. Values of data type DATE are equivalent to values of data type DATETIME declared as: DATETIME YEAR TO DAY. For information, see DATETIME Data Type on page D-15. Example—DATE Data Type These examples are literals of data type DATE in (respectively) default, USA, and European format: DATE "2004-01-22" DATE "01/22/2004" DATE "22.01.2004" For information, see Date-Time Literals on page D-10.
Date-Time Data Types Date-Time Data Types An item of a date-time data type represents a point in time. It can include a date, a time, or a date and time. There are four date-time data types: DATETIME DATE TIME TIMESTAMP The term “date-time data type” refers to all four of these data types. The term “DATETIME data type” refers only to the first of these data types.
Date-Time Functions Date-Time Functions Date-time functions are functions that you can use in expressions that involve columns defined with the date-time data types (DATETIME, DATE, TIME, and TIMESTAMP). You can use the date-time functions anywhere an arithmetic expression is allowed.
Date-Time Literals A date-time literal value can be enclosed in double quotation marks (as shown in the diagram below) or in single quotation marks, and can appear in default, USA or European format.
Date-Time Literals default-date is: { yyyy[-mm[-dd] } { mm[-dd] } { dd } <--DEFAULT format usa-date is: { [mm/[dd/]]yyyy } { mm[/dd] } { dd } <--USA format european-date is: { [[dd.]mm.]yyyy } { [[dd.]mm } { dd } <--EUROPEAN format default-time is: { hh[:mm[:ss[.msssss]]] { mm[:ss[.msssss]] { ss[.msssss] { msssss usa-time is: { { { { hh[:mm[:ss[.msssss]]] mm[:ss[.msssss]] ss[.msssss] msssss } } } } <--DEFAULT format } [ am|pm ]<--USA format } } } european-time is: { { { { hh[.mm[.ss[.
Examples—Date-Time Literals date is: { yyyy-mm-dd } { mm/dd/yyyy } { dd.mm.yyyy } time is: { hh:mm:ss } { hh:mm:ss [ am | pm ] } { hh.mm.ss } timestamp is: { yyyy-mm-dd:hh:mm:ss.msssss } { mm/dd/yyyy:hh:mm:ss.msssss [ am | pm ] } { dd.mm.yyyy:hh.mm.ss.msssss } DATETIME "dt" [ start-field TO ] end-field specifies a constant of data type DATETIME. The start-field to end-field clause specifies the range and precision of DATETIME fields included in the constant dt.
DATEFORMAT Function DATEFORMAT Function DATEFORMAT is a function that formats a date-time value in DEFAULT, USA, or EUROPEAN format. DATEFORMAT returns a value of type CHAR. You can use DATEFORMAT wherever an arithmetic expression is allowed. { DEFAULT } DATEFORMAT ( date-time-expression, { USA } ) { EUROPEAN } date-time-expression is an expression that evaluates to a value of type DATETIME, DATE, TIME, or TIMESTAMP. { DEFAULT } { USA } { EUROPEAN } specifies a display format for a date-time value.
DATETIME Data Type DATETIME Data Type An item of data type DATETIME represents a point in time. It can include a date, a time, or a date and time. DATETIME [ start-field TO ] end-field start-field and end-field are: { { { { { { { YEAR MONTH DAY HOUR MINUTE SECOND FRACTION precision } } } } } } } start-field must precede end-field and only end-field can include the precision option. start-field specifies the first field for a range of DATETIME fields.
Example—DATETIME Range and meaning of fields within DATETIME values Field Range Bytes* Meaning YEAR 0001 to 9999 2 Year MONTH 1 or 01 to 12 1 Month in year DAY 1 or 01 to 31** 1 Day in month HOUR 0 or 00 to 23 1 Hour in day MINUTE 0 or 00 to 59 1 Minute in hour SECOND 0 or 00 to 59 1 Second in minute FRACTION 0 to 999999 4 Microsecond in seconds * Bytes is the number of bytes used to store the field in a column that includes the field.
DAYOFWEEK Function DAYOFWEEK Function DAYOFWEEK is a function that reads a date-time expression and returns a type INTEGER value in the range 1 through 7 that represents the day of the week expressed by the date-time value. The value 1 represents Sunday, 2 represents Monday, and so forth. DAYOFWEEK ( date-time-expression ) date-time-expression is an expression that evaluates to a value of type DATETIME, DATE, TIME, or TIMESTAMP.
DCL (Data Control Language) Statements DCL (Data Control Language) Statements DCL (Data Control Language) is the set of SQL statements and directives that control parallel processing, name resolution, and performance-related considerations such as access paths, join methods, and locks and cursors. This table summarizes the DCL statements and directives.
Considerations—DCOMPRESS The key must begin with the first column in the table. The key columns must be contiguous. The key columns must be in ascending order.
DDL (Data Definition Language) Statements You cannot use DCOMPRESS on a file with a clustering key. You cannot use the COLLATE option on a key column if you will be using DCOMPRESS.
DDL Statements Statement Description (page 2 of 2) CREATE COLLATION Statement Creates a collation CREATE CONSTRAINT Statement Creates a constraint CREATE INDEX Statement Creates an index CREATE TABLE Statement Creates a table CREATE VIEW Statement Creates a view DROP Statement Drops a catalog, collation, constraint, index, SQL program in a Guardian file, table, or view HELP TEXT Statement Specifies help text for a column of a table or view UPDATE STATISTICS Statement Updates statistics abo
Deadlocks Deadlocks Deadlock is a block to data flow caused by processes contending for the same locked data. For example, deadlock occurs when process A locks one row and waits for another row locked by process B, while process B waits for the row locked by process A. When deadlock causes a process to fail to acquire a requested lock within the timeout period, one of two errors occurs: Error 73 indicates the request was canceled successfully, and the transaction continues.
Examples—DECIMAL_POINT Examples—DECIMAL_POINT This example prints a price in European format, using an F9.2 display descriptor and a comma as the decimal character: S> SET STYLE DECIMAL_POINT ","; S> DETAIL PRICE; S> LIST NEXT 1; PRICE -----------1300,95 This example uses a period in a mask format to print a price, again using a comma as the decimal character: S> SET STYLE DECIMAL_POINT ","; S> DETAIL PRICE AS M"9999.
Considerations—DECLARE CURSOR You must use this clause if you update rows (if stmt includes UPDATE WHERE CURRENT OF), but it is optional if you delete rows (if stmt includes DELETE WHERE CURRENT OF). You cannot repeat or qualify column names, or specify SYSKEY or a user-defined primary key for a key-sequenced table. :cursor-var CURSOR FOR :select-stmt-var (dynamic SQL only) specifies host variables that contain the names of the cursor and the SELECT statement to associate.
Examples—DECLARE CURSOR cursor declaration, you can include an ORDER BY clause if you are sure that SQL will choose a plan that satisfies the specified order without sorting the rows. DECLARE CURSOR does not acquire locks. Locks are acquired when you execute a FETCH on the cursor or—if the SELECT requires a sort—when you open the cursor. The access option you specify in the SELECT applies to rows you access with the cursor.
DEFAULT Clause DEFAULT Clause The DEFAULT clause specifies a default value for a column (a value to use as the value of the column when a row is inserted without one). You can specify a default value for any column you define with CREATE TABLE or ALTER TABLE. { DEFAULT { { { literal CURRENT SYSTEM NULL } } } } The default is NULL. literal is a literal of a data type compatible with the data type defined for the associated column.
Example—DEFAULT SYSTEM specifies that the default value depends on the data type of the column: Data Type Default Value Character Fixed-length A string of blanks Variable-length A zero-length string Date-time Same as CURRENT option Interval 0 Numeric 0 NULL specifies the null value as the default. Specifying NULL as the default adds two bytes to the size of the column. You cannot specify NULL if you also specify the NOT NULL clause in the command that creates the column.
Using DEFINEs DEFINE names are easier to understand than Guardian names. For example, the name =CUSTOMERS is simpler than an actual file name such as \SYS1.$VOL2.SALES.CSTMERS. DEFINE names provide location independence. For example, if you code with DEFINE names, you can rename database objects, move database objects, or change the database that a program accesses without changing source code. NonStop SQL/MP includes a set of DEFINEs that specify values for SQL operations.
Using DEFINEs If DEFMODE is OFF, DEFINEs are ignored and you cannot create new DEFINEs. You can still modify, delete, and display information about existing DEFINEs, but such DEFINEs have no effect because they are not propagated to other programs. (The =_DEFAULTS system DEFINE is a special DEFINE that is an exception to this rule and that is always propagated. For information, see =_DEFAULTS DEFINE on page Z-3.) Use these commands to work with DEFINEs from SQLCI.
Using DEFINEs From SQLCI DEFINEREADATTR Returns an attribute value for a DEFINE or for the working attribute set DEFINERESTOREWORK Restores the working attribute set from the background set DEFINESAVEWORK Saves the working attribute set in the background set DEFINESETATTR Alters the value of an attribute in the working set, or resets the attribute DEFINESETLIKE Sets all attributes of the working set to match those of an existing DEFINE DEFINEVALIDATEWORK Checks the working set for consistency an
Using DEFINEs With SQL Programs Using DEFINEs With SQL Programs These rules apply to the use of DEFINEs in SQL statements within programs: You use TACL or OSS shell commands to supply DEFINEs at compilation for DEFINE names used in preprocessor or host language statements and in the SQLCOMP or c89 command.
DEFINE Attributes DEFINEs you create from an executing program remain in effect until you alter them, delete them, or terminate the program. DEFINEs you inherit from another process and then modify within an executing program revert to their previous attribute values (that is, the values they had when you started the program) when the program terminates. Any changes you make to inherited attributes within the program apply only within the program.
DEFINEs of Class CATALOG SUBSORT Specifies parameters for parallel sort processes SPOOL Sets parameters for the spooler TAPE Specifies the attributes of a file on a labeled tape, such as block size and density MAP is the default. The CATALOG, DEFAULTS, and MAP classes are particularly useful with SQL and are discussed in more detail in this section. DEFINEs of Class CATALOG DEFINEs of class CATALOG specify a logical name for a particular SQL catalog.
Summary of DEFINE Attributes statements as the logical name of a table, view, index, collation, partition, or program, altering the DEFINE (but not the SQL statement) when you want to point to a different physical entity. For example, this command adds a DEFINE that assigns the logical name =ORDERS to the table whose name is \SYS1.$VOL2.SALES.ORDERS: ADD DEFINE =ORDERS, CLASS MAP, FILE \SYS1.$VOL2.SALES.ORDERS; While this DEFINE is in effect, you can refer to the table as =ORDERS in SQL statements.
Summary of DEFINE Attributes Table D-1. Attributes of DEFINEs (by Class) (page 2 of 3) Class Attributes Required/ Optional SUBVOL1 subvol-name Optional RELSUBVOL1 subvol-name Optional ... SORT SUBVOL20 subvol-name Optional RELSUBVOL20 subvol-name Optional BLOCK block-size Optional CPU cpu-number Optional CPUS subsort-cpu-list Optional MODE mode-type Optional NOTCPUS cpu-list-not-subsort Optional NOSCRATCHON (volume-name Optional [, volume-name]...
Summary of DEFINE Attributes Table D-1. Attributes of DEFINEs (by Class) (page 3 of 3) Class Attributes Required/ Optional SUBSORT BLOCK size Optional CPU cpu-number Optional NOSCRATCHON (volume-name[, volume-name]...) Optional PRI process-priority Optional PROGRAM file-name Optional SCRATCH file-name Optional SCRATCHON (volume-name[, volume-name]...
Examples—DEFINEs Used With SQL Programs Examples—DEFINEs Used With SQL Programs This example uses the DEFINE name =PARTS to identify a table in an INVOKE statement in a COBOL program. The DEFINE for =PARTS must exist when you run the preprocessor before the COBOL compilation. EXEC SQL INVOKE =PARTS AS PARTS-REC LEVEL (01,04) END-EXEC. This example uses the DEFINE name =PARTS to identify a table in an INSERT statement.
DELETE DEFINE Command DELETE DEFINE Command DELETE DEFINE is an SQLCI command that deletes DEFINEs. (DELETE DEFINE is similar to the TACL command DELETE DEFINE and the OSS shell command del_define.) { DELETE DEFINE { { { define } (define [ , define ] ... ) } ; ** } =* } define is the name of a DEFINE to delete. ** or =* specifies that all DEFINEs are to be deleted.
DELETE Statement DELETE Statement DELETE is a DML statement that deletes rows from a table or protection view. DELETE does not delete a table or protection view, even if you delete the last row in the table or view. Use DROP to delete a table or view. DELETE FROM name [[ WHERE search-cond ] [ [FOR] {STABLE } ACCESS ]] [ [ {REPEATABLE} ]] [ ] [ WHERE CURRENT OF cursor-name ] name is the name of the table or protection view (or an equivalent DEFINE) from which to delete rows.
Considerations—DELETE Considerations—DELETE DELETE requires authority to read and write to the table or view being deleted and to any table or view in a subquery of the search condition. Rows must be locked to be deleted. The locking used depends on the access option you specify in the WHERE clause or the access option you specify in the SELECT portion of the associated DECLARE CURSOR statement.
DESCRIBE INPUT Statement consistency but reduces concurrency), so it would be best to execute it at a time when only a few users need concurrent access to the database. DELETE FROM INVENT.PARTSUPP WHERE PARTNUM BETWEEN 6400 AND 6700 AND PARTCOST > 1600.00 REPEATABLE ACCESS; This example on DELETE removes all suppliers not in Texas from the table PARTSUPP: DELETE FROM INVENT.PARTSUPP WHERE SUPPNUM IN (SELECT SUPPNUM FROM INVENT.
DESCRIBE INPUT Statement NAMES INTO :names-buffer specifies the host variable name of a names buffer declared in an INCLUDE SQLDA statement or elsewhere in your program into which DESCRIBE returns the names of the input parameters. If you specify the NAMES INTO clause, DESCRIBE INPUT sets the VAR_PTR field for each entry in the SQLDA to the address of the corresponding entry in the names buffer.
Example—DESCRIBE INPUT IND_PTR field of each SQLVAR entry contains the address of the corresponding indicator parameter entry. Example—DESCRIBE INPUT This C statement returns descriptions of input variables in the prepared statement identified by :stmt_name to an SQLDA structure pointed to by :*input_sqlda_ptr.
DESCRIBE Statement If the SQLDA is not large enough to describe all the SELECT columns, DESCRIBE returns only the descriptions of the first n columns in the SELECT, where n is the number of entries in the SQLDA. (In either case, DESCRIBE does not modify the NUM_ENTRIES field in the SQLDA, which indicates the number of entries the SQLDA can hold.) For information about the contents and use of an SQLDA, see the SQL/MP programming manual for your host language.
Example—DESCRIBE Example 3: STATEMENT SELECT C1 FROM =TABLE_NAME_1 WHERE C1 > 100; NAMES-BUFFER (OUTPUT) {15}TABLE_NAME_1.C1 Example 4: STATEMENT SELECT C1 FROM =TABLE_NAME_1 T1 WHERE T1.C1 > 100; NAMES-BUFFER (OUTPUT) {05}T1.C1 For information about the contents and use of a names buffer, see the SQL/MP programming manual for your host language.
Detail Alias Detail Alias A detail alias is a name assigned to a print item using the NAME clause of the DETAIL command. You can use a detail alias to refer to the print item in report formatting commands such as TOTAL and SUBTOTAL, but not within the DETAIL command itself. A detail alias is not the same as an alias, which is a name assigned to a column in the select list of the SELECT command using the report writer NAME command.
DETAIL Command DETAIL Command DETAIL is an SQLCI report writer command that defines the contents and format of a detail line. (A detail line is the output from a single row returned by a SELECT command, although it can be printed as more than one row in a report.) You can use DETAIL only from the select-in-progress prompt, not from the SQLCI prompt. DETAIL [ print-list ] ; print-list is: print-item [ , print-item ] ...
DETAIL Command num-exp is an numeric expression to evaluate and print in the detail line. The expression cannot include the AVG, COUNT, MIX, MAX, or SUM functions. It can specify columns with column names, aliases, or COL number, but not with detail aliases. It can include the report functions LINE_NUMBER, COMPUTE_TIMESTAMP, CURRENT_TIMESTAMP, and PAGE_NUMBER (which are not allowed in numeric expressions other than in print items).
DETAIL Command NAME detail-alias specifies an SQL identifier unique among column names in the select list as the detail alias for the print item. You can refer to a detail alias in other report formatting commands such as TOTAL and SUBTOTAL, but you cannot refer to a detail alias in the DETAIL command itself. NEED [ number ] advances to the next page before printing the next print item unless at least number (an integer in the range 1 through 32,767) more lines fit on the page. The default is 1.
Considerations—DETAIL number is an integer that corresponds to a print position between the left and right margins. The default is 1. You can tab backward (specify a print position earlier in the line than the current print position), but if you overlap print items, the most recent item you specify overwrites older ones (or portions of older ones). Considerations—DETAIL The detail line for the default report is: DETAIL COL 1, COL 2, COL 3, ...
Examples—DETAIL Examples—DETAIL This example specifies a report in the format shown: >> SET LIST_COUNT 0; >> SELECT * FROM SALES.
DISPLAY STATISTICS Command Some sample lines of this report are: FRESNO STATE BANK 2300 BROWN BLVD FRESNO, CALIFORNIA 93921 DISPLAY STATISTICS Command DISPLAY STATISTICS is an SQLCI command that displays statistics about the last DML or PREPARE the statement you executed.
Example—DISPLAY STATISTICS Records accessed does not indicate the specific number of physical disk reads or writes because SQL uses disk caching to reduce the number of physical read and write operations. Records used is the number of rows that satisfy the query. Number of disk reads Message count Message count is usually the number of blocks passed from the disk process to the file system. Sometimes an additional message is needed to ensure that the last row was processed.
DISPLAY USE OF Command DISPLAY USE OF Command DISPLAY USE OF is an SQLCI utility command that displays a list of SQL objects and registered SQL object programs depend on the object specified. DISPLAY USE OF object [ AT { \node } ] [ { (\node [,\node] ... ) } ] [ , { BRIEF | STANDARD } ] ; object is the name (or an equivalent DEFINE) of a collation, index, table, or view for which to specify the dependent objects.
Considerations—DISPLAY USE OF DISPLAY USE OF lists only registered SQL programs. Unregistered SQL programs cannot be listed because neither such programs nor their dependency relationships are described in catalogs.
Examples—DISPLAY USE OF PV Protection view SV Shorthand view TA Table S A status column that indicates whether any special condition was encountered during the search for the initial or dependent object. A blank indicates no special condition was encountered. The meanings of the status codes follow: * The object was listed previously; its dependent objects will not be repeated. ? A system error occurred. @ The AT option did not include this node.
Examples—DISPLAY USE OF >> DISPLAY USE OF \SYS1.$VOL.PERSNL.EMPLOYEE, BRIEF; Object Name Type S ------------------------------------- ---- 0 \SYS1.$VOL1.PERSNL.EMPLOYEE TA 1 \SYS1.$VOL1.PERSNL.EMPLIST PV 1 \SYS1.$VOL1.PERSNL.XEMPDEPT IN 1 \SYS1.$VOL1.PERSNL.XEMPNAME IN 1 \SYS1.$DATA.ZYQ39483.Z000002H PG PATH/usr/empinfo/reports/app.
DML Statements DML Statements A DML statement is used to select, update, insert, or delete rows in one or more tables.
Considerations—DOWNGRADE CATALOG with a PCV newer than version. (Delete any such objects or programs before you execute DOWNGRADE CATALOG.) In addition, no catalog specified can include objects with a version newer than the version of the SQL/MP software executing the DOWNGRADE CATALOG command. Catalogs cannot be a system catalog. (Use DOWNGRADE SYSTEM CATALOG to convert a system catalog.) If SMF is installed on your node, catalogs cannot specify any catalog or system catalog on a $*.ZYS*. subvolume.
Examples—DOWNGRADE CATALOG the SQL/MP software that executes it is replaced with an older version. However, if the purpose of the DOWNGRADE CATALOG is to prepare for installation of an older version of SQL/MP software, you will need to re-SQL-compile programs with a newer PFV after the older software is in place. (A runtime error occurs if you attempt to execute a program with a PFV newer than the version of the installed SQL/MP software.
DOWNGRADE SYSTEM CATALOG Command DOWNGRADE SYSTEM CATALOG Command DOWNGRADE SYSTEM CATALOG is an SQLCI utility command that allows a user with super ID authority to convert a local system catalog to an older version so the system catalog can be accessed by an older version of SQL/MP software. DOWNGRADE SYSTEM CATALOG TO version ; version is the catalog format version for the downgraded system catalog.
Example—DOWNGRADE SYSTEM CATALOG DOWNGRADE SYSTEM CATALOG does not invalidate a program registered in the downgraded system catalog that has a PFV newer than version because such a program can execute regardless of the catalog downgrade unless the SQL/MP software that executes the program is replaced with an older version.
DROP Statement DROP Statement DROP is a DDL statement that deletes a catalog, collation, constraint, index, SQL-program Guardian file, table, or view, and deletes comments associated with the dropped object. DROP { { { { { { { CATALOG [ catalog ] } COLLATION collation } CONSTRAINT constraint ON table } INDEX index } PROGRAM file } TABLE table } VIEW view } CATALOG [ catalog ] specifies the name (or an equivalent DEFINE) of an empty catalog to delete.
Considerations—DROP because of the drop. In addition, you cannot drop an object until after the time and date specified for the NOPURGEUNTIL attribute of the object. To drop a table, view, or program, you must also have authority to purge the object being dropped. To drop a partitioned object, all partitions must be accessible. Additional requirements for dropping other types of objects are described later in this entry.
Considerations—DROP table (including all partitions) and the catalogs of all SQL object program files that use the table are accessible. Caution. Using the DROP statement to delete a table is permitted if you have PURGE authority, there are no locks outstanding on the table, and if you are beyond the NOPURGEUNTIL date. To adequately protect important tables, use the NOPURGEUNTIL attribute, specifying a date well into the future, or change PURGE authority to “-” (SUPER only).
Example—DROP You cannot drop an SQL-program Guardian file within a user-defined transaction. (You can use the OSS unlink command to delete an SQL-program OSS file within a user-defined transaction, but the operation is not performed as part of the transaction.) If SMF is installed on your node, the object to drop must have either a virtual or direct name. This restriction does not apply to DROP CONSTRAINT.
Examples—DROP SYSTEM CATALOG You cannot enter the DROP SYSTEM CATALOG command, however, while the SQLCI2 program is running, as it normally is after you enter a command during the current SQLCI session. If you attempt to enter the DROP SYSTEM CATALOG command while SQLCI2 is running, the command terminates abnormally and an error message appears. To enter the command correctly, you can use either of two methods: Exit from SQLCI.
DSLACK File Attribute DSLACK File Attribute DSLACK is a Guardian file attribute that specifies the minimum percentage of space to leave for future insertions when loading data blocks. DSLACK applies only to key-sequenced tables and to indexes. DSLACK percent The default is the value of the SLACK file attribute. The default for SLACK is 15 percent. percent is an integer from 0 to 99 that specifies the percent of empty space to leave in each data block when loading the file.
DUP Command DUP Command DUP is an SQLCI utility command that copies tables (optionally with the associated views and indexes), views, collations, SQL programs in Guardian files, and Enscribe files. DUP cannot copy a catalog table. DUP is useful for moving tables to different nodes or volumes and for duplicating tables for testing. DUP resembles the FUP DUP command in function and syntax, but you cannot use FUP DUP on SQL objects.
DUP Command error. If source-file-list implicitly specifies secondary partitions, DUP ignores the secondary partitions. If SMF is installed on your node, source-fileset-list must not specify an object or file on a $*.ZYS*. subvolume. Duplication of views and indexes depends on the INDEXES and VIEWS options described later in this entry. target-fileset is a fileset that specifies names and locations for the new objects and files.
DUP Command An asterisk (*) in the fileset specification indicates that the portion of the name in which the asterisk appears should be the same as the corresponding portion of the name of the object or file being duplicated. For example, MAP NAMES $WHS2.INVENT.PARTLOC TO $TEST.*.* specifies that the new table that duplicates $WHS2.INVENT.PARTLOC is to be $TEST.INVENT.PARTLOC. (The meaning of the asterisk differs from the usual meaning of an asterisk in a fileset specification.
DUP Command { collation-name { } } { ( collation-name [ , collation-name ] ... )} FOR simple-fileset-list collation-name identifies a collation and FOR simple-fileset-list specifies a simple fileset list that includes the names of any new objects that reference the collation. The first collation specified whose simple name matches the simple name of a collation referenced in the new object being created is mapped to the new object.
DUP Command If you omit the LISTALL option, LISTALL is the default. If you specify NO LISTALL, DUP suppresses the display of confirmations. SAVEALL [ ON | OFF ] specifies a setting for both the SAVEID and the SOURCEDATE options. ON Set SAVEID and SOURCEDATE to ON OFF Set SAVEID and SOURCEDATE to OFF If you specify SAVEALL and specify either SAVEID or SOURCEDATE separately, the settings must match. The default is SAVEALL OFF.
Considerations—DUP The default is TARGET NEW. INDEX[ES] [ IMPLICIT | OFF ] specifies whether to duplicate indexes of duplicated tables: IMPLICIT Duplicate indexes of duplicated tables OFF Do not duplicate indexes of duplicated tables The default is INDEXES IMPLICIT.
Considerations—DUP or indexes being duplicated as part of duplicating a table. For an example, see the target-fileset specification Considerations item on page D-76. Rules for views target-fileset or the MAP NAME option you specify determines how DUP translates table and view names (in the FROM clause of the SELECT command in the view definition) to the new view definition.
Considerations—DUP objects. If collations are the only objects in source-fileset-list, DUP copies the collations and then updates the CPRULES and CPRLSRCE catalog tables. Rules for Enscribe files If an Enscribe file references its alternate-key files, DUP modifies such references in the new file based on the MAP NAME option you specify. If you omit the MAP NAME option, DUP does not modify the references.
Considerations—DUP Be careful that you define the MAP NAMES and CATALOGS parameters correctly for dependent tables, indexes, views, and programs. An incorrect mapping scheme can leave the objects invalid or cause the RESTORE process to fail. For example, suppose that base table $A.A.TABLE has two dependent objects: a protection view located on $A.XX.PVIEW and an index located on $A.ZZ.IXTAB.
Examples—DUP *.*.* TO *.*.OLDPARTS ) CATALOGS ( \REMOTE.$VOL1.CAT FOR \REMOTE.*.*.*, $VOL1.CAT FOR *.*.* ); This command does not work as expected because DUP changes the \LOCAL.*.*.* part of the MAP NAMES specification to *.*.*. Then, because the local specification is given first, both local and remote partition names map to \LOCAL.*.*.OLDPARTS. DUP $VOL1.TESTSUBV.PARTS, MAP NAMES ( \LOCAL.*.*.* TO \LOCAL.*.*.OLDPARTS, \REMOTE1.*.*.* TO \REMOTE1.*.*.OLDPARTS ) CATALOGS ( \LOCAL.$VOL1.CAT FOR \LOCAL.*.*.
Examples—DUP table will be described in the current default catalog because the CATALOG option is not specified. The JOB table has no dependent objects. >> DUP $VOL1.PERSNL.JOB, $NEWVOL.PERSNL.JOB LISTALL; This command does the same thing: >> DUP $VOL1.PERSNL.JOB, $NEWVOL.*.* LISTALL; This example copies table $VOL1.PERSNL.JOB to the remote volume \SYS2.$NEWVOL, using the same table name as the original. The CATALOG option specifies a catalog on the remote node in which to describe the new table.
Dynamic SQL The PERSNL subvolume contains the EMPLOYEE, DEPT, and JOB tables and the EMPLIST and MGRLIST views. This commands duplicate all tables and their indexes, but only the MGRLIST view: >> DUP $VOL1.PERSNL.*, *.NWPERS.*, VIEWS OFF NO LISTALL; >> DUP $VOL1.PERSNL.M*, $VOL1.NWPERS.
Features of Dynamic SQL Flexibility to construct SQL statements at run time: for example, an interactive interface that is similar to SQLCI, but is designed for an inexperienced user. Restriction of access to data in a table: for example, the program might code an UPDATE statement for certain columns in a table, but allow the user to enter any selection criteria (WHERE clause) at run time.
Features of Dynamic SQL HP NonStop SQL/MP Reference Manual—523352-013 D-82
E EDIT Command EDIT is an SQLCI command that invokes the EDIT text editor. [ EDIT [ [ [ file [!] ] ] " [ file [!] ] [ ; edit-cmd ] ... " ] ; [ file [!] ] " [ ; edit-cmd ] ... " ] file is a Guardian name that specifies the file to EDIT. [!] directs EDIT to create file if it does not already exist. edit-cmd is a text editor command. See the EDIT User's Guide and Reference Manual. Note that you must use a form of the command that includes quotation marks if you specify edit-cmd.
Embedded SQL Embedded SQL Embedded SQL, or programmatic SQL, is the application programming interface for SQL. It consists of a set of SQL statements and declarations you can include in programs written in C, COBOL, Pascal, or TAL. The language in which you write an embedded SQL program is called the host language or host programming language in SQL/MP documentation. A separate SQL/MP programming manual exists for each of the four host languages supported by NonStop SQL/MP.
END DECLARE SECTION Directive END DECLARE SECTION Directive END DECLARE SECTION is a host program directive that ends a host program Declare Section for declaring host variables to use in SQL statements. For more information about declaring host variables, see the SQL/MP programming manual for your host language.
Example—ENV You can use the CATALOG, LOG, OUT_REPORT, SYSTEM, and VOLUME commands to change current default values for the SQLCI session. You can change the OUT file with the OUT command (or with the OUT run option when you start an SQLCI session). You can specify a different SQL message file for an SQLCI session by setting the =_SQL_MSG_node DEFINE before you start the SQLCI session. Changing the DEFINE after you start SQLCI, however, does not change the message file.
Examples—ERROR type specifies the type of error and can be one of these: AUD Audit-fixup process DP Disk process FS File system OS Guardian operating system SIO Sequential I/O SQL SQL command SORT Sort If you omit type, SQLCI displays the error text for all operations that generate the specified error number. number is a positive or negative number that identifies the error you want described. If you omit number, SQLCI displays information about the most recent error.
Error Messages Recovery: Create a new table with the correct number of columns that has the same contents as the old table. >> Error Messages SQL returns error and warning information through SQLCI and through programmatic interfaces. Each SQL error message is associated with a negative number and each SQL warning message is associated with a positive number.
EXECUTE Statement EXECUTE Statement EXECUTE is a dynamic SQL or SQLCI statement that executes an SQL statement previously compiled by the PREPARE statement. EXECUTE { stmt-name } { :stmt-variable } [ USING [?param=] value [, [?param=] value ]... ] [ USING :variable [ , :variable ] ...
EXECUTE Statement Unnamed parameter values are substituted for parameters in the SQL statement by position. The i-th value in the USING clause or in the SQLDA is the value for the i-th formal parameter. Any parameter values you specify in the USING clause override values you previously specified in SET PARAM commands, but only for this execution of the statement. param (used in SQLCI) is the name of a parameter to be assigned the value that immediately follows.
Considerations—EXECUTE RETURNING { :variable } { USING DESCRIPTOR :out-sqlda } (used only if a dynamic INSERT RETURNING is executed) directs SQL to return the SYSKEY for the last record inserted. :variable is a host variable in which to return the key. It must be of an appropriate type for the key (INTEGER UNSIGNED for tables with entry-sequenced or relative organization; LARGEINT SIGNED for tables with key-sequenced organization).
Examples—EXECUTE Examples—EXECUTE This example uses PREPARE and EXECUTE to compile the statement stored in the variable :DYNSTMT and execute it using parameter values stored in the variables :PARTNUM, :PRICE, and :PARTDESC: EXEC SQL PREPARE OPERATION FROM :DYNSTMT; EXEC SQL EXECUTE OPERATION USING :PARTNUM, :PRICE, :PARTDESC; This SQLCI example uses PREPARE to compile a statement once, then executes the statement multiple times with different parameter values: PREPARE FINDEMP FROM "SELECT * FROM PE
EXECUTE IMMEDIATE Statement EXECUTE FINDSUP USING 25; EXECUTE FINDSUP USING 6, ?PNUM = 5504; EXECUTE FINDSUP USING 8; EXECUTE IMMEDIATE Statement EXECUTE IMMEDIATE is a dynamic SQL statement used in a host program to compile and execute an SQL statement whose text is contained in a host variable. EXECUTE IMMEDIATE :host-var :host-var identifies a host variable declared as an alphabetic or alphanumeric data item; host-var must contain the SQL statement as a string literal.
EXISTS Predicate EXISTS Predicate EXISTS is a predicate that determines whether any rows satisfy conditions in a subquery. The EXISTS predicate evaluates to true if the subquery finds at least one row that satisfies the search condition. [ NOT ] EXISTS subquery In an EXISTS predicate, the result of subquery can be a table of more than one column. An EXISTS subquery is typically correlated with an outer query.
EXIT Command EXIT Command EXIT is an SQLCI command that ends an SQLCI session. Pressing Ctrl-Y is the same as typing EXIT. E[XIT] [ ; ] Control returns to the process from which you started SQLCI, usually the command interpreter. If a user-defined transaction is in progress, SQLCI prompts you to specify whether you want to commit or roll back the transaction.
Considerations—EXPLAIN EXPLAIN reports and for a detailed explanation of what to look for when you analyze EXPLAIN output, see the SQL/7MP Query Guide. Note that an EXPLAIN report is based on information available at the time you generate the report. If access paths or statistics change before you execute a query for which you obtained an EXPLAIN report, SQL might use a different execution plan. For example, the EXPLAIN execution plan for a SELECT statement reflects the access paths that exist at the time.
Considerations—EXPLAIN [ [ [ [ [ [ [ [ [ [ [ [ [ [ [ [Key Sequenced] Merge] Join (This section appears only for parallel ] plans and is described under "Parallel Execution Plan" in the text that follows the figure. Typical lines are shown below, though the details of section contents vary depending on the plan.) ] ] ] ] ] ] Each operation is performed in parallel for this step.] ] Each ESP [ from previous step ] will read one of ... ] partition partition partition ...
Considerations—EXPLAIN Sort key columns : [ Expected rowcount : ] (Appears if sort is for GROUP BY) Sort cost : ----------------------------------------------------------Operation n : Hash (Appears for hash operations) Requested : Hash rows in the : Purpose : Hash key columns : Hash cost : Total cost : ------------------------------------------------------------Table E-1. EXPLAIN Plan Header Lines (page 1 of 5) Item Action Access path Identifies the path used for retrieving rows from the base table.
Considerations—EXPLAIN Table E-1. EXPLAIN Plan Header Lines (page 2 of 5) Item Action Column processing Indicates the number of columns to be retrieved and the total number of columns in the tables or views from which they are retrieved. DP2 aggr. Indicates that aggregate functions are evaluated by DP2. End key pred. Specifies the predicates used to position to the last row to scan, including collations explicitly used in the predicates. Executor aggr.
Considerations—EXPLAIN Table E-1. EXPLAIN Plan Header Lines (page 3 of 5) Item Action Operation cost Indicates the relative cost of performing the operation. Lower numbers indicate more efficient and less costly operation.
Considerations—EXPLAIN Table E-1. EXPLAIN Plan Header Lines (page 4 of 5) Item Action Query plan n Specifies the number of the query and whether the plan involves parallel execution. Requested Specifies whether the sort or hash operation was requested by the optimizer or explicitly requested by the user. SBB for [Insert] [Update] Specifies whether virtual sequential block buffering (VSBB) is used for insert or update operations.
Examples—EXPLAIN Table E-1. EXPLAIN Plan Header Lines (page 5 of 5) Item Action Total cost The total cost of executing the statement. Cost is a relative measure of the resources needed. Lower numbers indicate less costly execution. This measure is useful only for comparing different ways of specifying the same query (for example, using a join instead of a subquery). Total Cost cannot not be used to compare the efficiency of executing different queries.
Expressions These examples shows three different ways to request an EXPLAIN report for a query that uses multiple lines in SQLCI: Example 1. >>EXPLAIN SELECT U1,U2, MIN(U3), MAX(U3) +>FROM \SYS.$VOL.CAT.T +>GROUP BY 1 , 2; Example 2. >>EXPLAIN " SELECT U1,U2, MIN(U3), MAX(U3) " +>&" FROM \SYS.$VOL.CAT.T " +>&" GROUP BY 1 , 2"; Example 3. >>PREPARE SAVQ FROM SELECT U1,U2, MIN(U3), MAX(U3) +>FROM \SYS.$VOL.CAT.T +>GROUP BY 1 , 2; .. --- SQL command prepared.
Numeric, Date-Time, and Interval Expressions Numeric, Date-Time, and Interval Expressions A numeric, date-time, or INTERVAL expression consists or one or more numeric, date-time, or INTERVAL operands connected by arithmetic operators, as shown in this diagram. operand [ [ arith-operator operand ] ...
Considerations—Expressions start-field and end-field specify the range of DATETIME fields for the operand, as described under DATETIME Data Type on page D-15. If the range includes fields not in value of the column, literal, function, host variable, or parameter, SQL extends the value to include the new fields by using the same initial values as for an extension using the EXTEND function. If the range omits fields not in the original value, SQL truncates those fields.
Considerations—Expressions Dividing by 0 causes an error. Exponentiation is allowed only with numeric data types but the operands can be of any numeric type. If the first operand is 0, the second operand must be greater than 0, and the result is 0. If the second operand is 0, the first operand cannot be 0, and the result is 1. If the first operand is negative, the second operand must be an integer. Exponentiation is subject to rounding error. Results should be considered to be approximate.
Considerations—Expressions SQL evaluates the expression: First, perform the multiplication, (COL1 * 100.00). The resulting scale is (0 + 2) = 2. The resulting precision is (18 + 3 + 2) = 23. The precision is greater than 18, so it is set to 18. The resulting magnitude is (18 - 2) = 16. Next, perform the division, (result / COL2). The resulting precision is 18, the resulting scale is (18 - 16 - 0) = 2, and the resulting magnitude is (18-2) = 16. Third, perform the subtraction, (100.
Examples—Expressions this expression (evaluated left to right) generates an SQL error because the calculation must use February 30: DATETIME "1989-01-30" YEAR TO DAY + INTERVAL "1" MONTH + INTERVAL "7" DAY In contrast, this expression (which adds the same values as the previous one, but in a different order) generates the value 1989-03-06: DATETIME "1989-01-30" YEAR TO DAY + INTERVAL "7" DAY + INTERVAL "1" MONTH Addition or subtraction of a date-time value and an INTERVAL value results in a DATETIME value
Examples—Expressions In this example and in all these examples, date-time and INTERVAL values are from this table: Table Definition: CREATE TABLE PROJECTS ( PROJECT_NAME PIC X(10) START_DATE DATETIME YEAR TO MINUTE END_DATE DATETIME YEAR TO MINUTE WAIT_TIME INTERVAL DAY(2) Table Data: PROJECT_NAME START_DATE END_DATE ------------ ---------------- ---------------920 1988-02-21:20:30 1989-03-21:20:30 134 1970-01-01:00:00 1978-03-21:20:30 922 1940-02-21:12:30 1941-03-21:20:30 955 1990-10-14:14:30 1991-01-2
EXTEND Function appear, the WAIT_TIME column must be defined with the range DAY TO HOUR. The inserted row is: 945 1989-10-20:00:00 1990-10-21:00:00 30 This example doubles an INTERVAL value. The result is 5 years, 2 months. INTERVAL “2-7” YEAR TO MONTH * 2 This example divides an INTERVAL value by another. The result is 36.
Considerations—EXTEND [start-date-time TO ] end-date-time is a range of DATETIME fields (for example, YEAR TO DAY). If the range is not specified, the system uses YEAR TO FRACTION(6). Considerations—EXTEND Any fields in date-time-expression that are not in the specified range are truncated. If the range contains fields to the left of the fields in date-time-expression, the additional fields receive values based on the current date or time.
EXTENT File Attribute EXTENT File Attribute EXTENT is a Guardian file attribute that sets the size of the extents (units of contiguous disk space) that will be allocated for a file or a partition of a file. EXTENT applies to key-sequenced, relative, and entry-sequenced tables and to indexes. EXTENT is set when a file or partition is created. Each partition of a partitioned file has its own EXTENT attribute that can differ from the EXTENT attribute for other partitions of the file.
Considerations—EXTENT Considerations—EXTENT A file's extent size must be at least as large as its block size and must be a multiple of the block size and a multiple of page size (2048 bytes). If you specify extent sizes that do not meet these conditions, SQL uses the next block size or the next full page size. For example, 0 PAGE rounds up to 1 PAGE.
Considerations—EXTENT HP NonStop SQL/MP Reference Manual—523352-013 E-32
F FC Command FC is an SQLCI command with which you can retrieve, edit, and reexecute a command in the history buffer. For more information, see HISTORY Command on page H-5. FC [ text ] [ ; ] [ number ] [ -number ] text specifies the most recent version of a command in the history buffer. The command must begin with the text that you specify; you need only the characters necessary to identify the command. The text can be in uppercase or lowercase characters.
Examples—FC characters (Must begin with a nonblank character) replaces existing characters with the specified characters one-for-one, beginning with the character immediately above the first character specified. To terminate a command and to specify more than one editing command on a line, separate the editing commands with a double slash (//). When you have no further changes to make, press the Return key. The command prints again to allow you to edit it again.
FETCH Statement . DDDDDDDDDDDDDDDDD +>; The first five rows of the table SUPPLIER are listed. Suppose that you have executed several commands since you entered the SELECT command. You can either use the HISTORY command to determine the SELECT command number, or you can enter: >> FC SEL If the history buffer contains several SELECT commands, you must be more specific; for example, FC SELECT SUPP.
Considerations—FETCH Use this option in dynamic SQL if you have no previous knowledge of the returned columns and use DESCRIBE to retrieve their descriptions. Considerations—FETCH FETCH requires read access to any tables or views associated with the cursor. Updating fetched rows requires write access to the table or view. Successive executions of FETCH retrieve successive rows in the result table.
Examples—FETCH Variable declarations: ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. Declare host variables: 01 HVAR1 02 HVAR2 03 HVAR3 EXEC SQL END DECLARE SECTION END-EXEC. ... Main code: EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT COL1, COL2, COL3 FROM =PARTS WHERE COL1 >= :HOSTVAR1 ORDER BY COL1 BROWSE ACCESS END-EXEC. ... EXEC SQL OPEN CURSOR1 END-EXEC. EXEC SQL FETCH CURSOR1 INTO :HVAR1, :HVAR2, :HVAR3 END-EXEC. EXEC SQL CLOSE CURSOR1 END-EXEC. These steps demonstrate a dynamic SQL FETCH.
File Attributes 5. Fill in the SQLDA and names buffer with the descriptions of the parameter values (input parameters) in the SQL statement: EXEC SQL DESCRIBE INPUT S1 INTO :SDAI NAMES INTO :NAMESINPUT; 6. Fill in the SQLDA and names buffer with the descriptions of the SELECT columns (output variables) in the SQL statement: EXEC SQL DESCRIBE S1 INTO :SDAO NAMES INTO :NAMESOUTPUT; 7. Declare a cursor, C1, for the statement S1.
File Attributes of SQL Objects File Attributes of SQL Objects ALLOCATE Controls amount of disk space allocated. Default is to allocate space as needed. AUDIT Controls TMF auditing. Default is AUDIT. AUDITCOMPRESS Controls whether unchanged columns are included in audit records. Default is to include only changed columns. BLOCKSIZE Sets size of data blocks. Default is 4096. BUFFERED Turns buffering on or off. Default is on. CLEARONPURGE Controls disk erasure when file is dropped.
File Organizations SLACK Sets percent of slack in blocks if not specified by DSLACK or ISLACK. Default is 15 percent. TABLECODE Sets tablecode. Default is 0. VERIFIEDWRITES Controls verification of writes to disk. Default is no verification. File Organizations SQL DDL statements create and modify tables and indexes and the physical Guardian files that hold tables and indexes.
FILEINFO Command For information about choosing the most effective file organizations for your tables, see the SQL/MP Installation and Management Guide. FILEINFO Command FILEINFO is an SQLCI utility that displays information about the versions and physical characteristics of tables, indexes, views, collations, Enscribe files, and OSS files. FILEINFO is similar to FUP INFO, although its displays are slightly different. FILEINFO qualified-fileset-list [[,]fileinfo-option] ...
FILEINFO Command user-ID is a user's numeric ID such as that displayed by the OSS ls -n command. user-ID can be in the range from 1 to 65,535. For a Guardian user-ID, this number equates to (256*group-id + member-ID). If you specify a user, FILEINFO displays information for files owned by that user. If you specify only the keyword USER, FILEINFO displays information only for files that you own. If you omit the USER option, FILEINFO displays information about all files in qualified-fileset-list.
Considerations—FILEINFO Considerations—FILEINFO FILEINFO without the STATISTICS option has no authorization requirements. With the STATISTICS option, FILEINFO requires read authority for the files for which information is displayed. FILEINFO returns error -9132 if it encounters an SQL object (but not an SQL program or catalog) that has a version greater than the version of the SQL/MP software on the node from which you issued the FILEINFO command.
BRIEF Display for SQL Objects and Guardian Files Table F-1. FILEINFO BRIEF Display Information Line (page 2 of 3) Open State CODE The open state of the file is described by one or more of these codes: D An ALTER TABLE or ALTER INDEX operation using the WITH SHARED ACCESS option did not complete successfully. To recover, use the RECOVER INCOMPLETE SQLDDL OPERATION option for the ALTER TABLE or ALTER INDEX statement, followed by a FUP RELOAD operation.
BRIEF Display for SQL Objects and Guardian Files Table F-1.
DETAIL Display for Objects (Except Views) and Guardian Files DETAIL Display for Objects (Except Views) and Guardian Files This listing shows all the detail that can appear if you specify the DETAIL option when you request FILEINFO for a table, index, collation, or Guardian file. The information that actually appears in your listing depends on the file organization of the item you inquire about and on whether the item is a table, index, collation, or file.
DETAIL Display for Objects (Except Views) and Guardian Files 16. PART (part-num, $volume, pri-ext PAGES, sec-ext PAGES, MAXEXTENTS max-ext, FORMAT format-type,firstkey-value) 17. ODDUNSTR REFRESH AUDIT BUFFERSIZE BUFFERED AUDITCOMPRESS VERIFIEDWRITES SERIALWRITES INCOMPLETE SQLDDL OPERATION UNRECLAIMED FREESPACE 18. OWNER group-id, owner-id SECURITY (RWEP):rwep, PROGID, CLEARONPURGE, LICENSE NOPURGEUNTIL:expire-time 19. SECONDARY PARTITION 20.
DETAIL Display for Objects (Except Views) and Guardian Files INVALID indicates that an SQL program is not valid and might need to be SQL compiled. SHADOW LABEL indicates that the file is a shadow label. SQL CHARACTER PROCESSING RULES OBJECT indicates that the object is a collation. 2. CATALOG identifies the catalog in which the object is defined. 3. VERSION indicates a NonStop SQL/MP version number. This information is supplied only for SQL objects. 4.
DETAIL Display for Objects (Except Views) and Guardian Files 12. This section describes the primary key of a key-sequenced file or other structured file types: IBLOCK is the length of an index block of an Enscribe file. KEY key-descriptor is one or more sets of these items; the number of sets depends on the number of columns in the key: COLUMN col-num, OFFSET key-offset, LENGTH key-length, { ASC } { DESC } COLUMN number indicates the position of the key column in the row.
DETAIL Display for Objects (Except Views) and Guardian Files If a file uses multibyte characters, the FIRST KEY value might contain characters unsupported by your terminal, causing unpredictable results in the screen display. 15. This section describes file attributes and flags: ODDUNSTR Enscribe odd unstructured file. REFRESH File label is updated when file control block changes. AUDIT File is audited. BUFFERSIZE Default internal transfer size of unstructured file.
DETAIL Display for Objects (Except Views) and Guardian Files 18. This section lists dates and times of file activity. MODIF indicates when the file was last modified and one of more of these open states, if applicable: BROKEN File is marked as broken. CORRUPT Error occurred during a utility operation such as restoring or duplicating. DEFINITION INVALID Data or definition of the object is invalid. LABEL QUESTIONABLE File is in a crash-label state. OPEN File is open.
DETAIL Display for Views 22. For tables and indexes, PARTITION ARRAY indicates the type of array used for the base table and any associated indexes. Possible values are: EXTENDED Longer partition array available on versions 320 and later of NonStop SQL/MP. STANDARD Partition array used by default by NonStop SQL/MP. FORMAT2ENABLED Partition array that allows partitions to be any combination of Format 1 or Format 2 partitions. FORMAT2ENABLED implies that the partition array is also EXTENDED. 23.
BRIEF and DETAIL Display for OSS Files LABEL QUESTIONABLE, DEFINITION INVALID, REDO NEEDED, and UNDO NEEDED are open states. See the description of the MODIF field of the DETAIL format for tables, indexes, collations, and Guardian files in the preceding list (item 17).
STATISTICS Display STATISTICS Display This figure shows the STATISTICS display. TOTAL BLOCKS 1 1 0 1 LEVEL 1 DATA FREE BITMAP TOTAL RECS 1 1 AVG # RECS 1.0 1.
Examples—FILEINFO PVIEW A partitioned protection view that depends on PARTLOC PROG12 An SQL object program file SVIEW A shorthand view This command displays information for all the objects on $VOL1.INVENT (including the catalog tables) in the default BRIEF format: >> FILEINFO $VOL1.INVENT.*; CODE $VOL1.INVENT BASETABS 572A COLUMNS 575A COMMENTS 582A CONSTRNT 580A ESTABLE 10 ... PARTLOC O A PARTLOCI O A PROG12 100P PROGRAMS 581A PVIEW SVIEW ...
Examples—FILEINFO FREE BITMAP 0 1 This command displays EXTENTS information about PARTLOC: >> FILEINFO $VOL1.INVENT.PARTLOC, EXTENTS; $VOL1.INVENT.PARTLOC 30 Oct 1994, 11:00 EXTENT # OF PAGES STARTING PAGE PART 0 16 71199 $VOL1 0 16 141363 $WHS2 This command displays BRIEF information about collations by specifying the file attribute “COLLATION” in the WHERE clause of the qualified fileset list for FILEINFO: >> FILEINFO \A.$A.A.* CODE EOF \A.$A.
FILENAMES Command FILENAMES Command FILENAMES is an SQLCI utility command that displays a set of file names that match a pattern specified with wild-card characters. You can restrict the list to objects described in specified catalogs. FILENAMES [ qualified-fileset-list ] ; qualified-fileset-list specifies the files for which information is to be displayed. For more information, see Qualified Fileset List on page Q-1.
FILES Command FILES Command FILES is an SQLCI utility command that displays the names of files that are on one or more subvolumes. FILES [ subvol-spec ] ; [ ( subvol-spec [ , subvol-spec ] ... ) ] subvol-spec is the name of a Guardian subvolume or a name with wild-card characters that matches the names of several Guardian subvolumes. If you do not specify any subvol-spec, FILES displays the files on the current default subvolume.
FILES Table FILES Table The FILES table is a catalog table that describes the attributes of files that contain tables and indexes. Table F-2 describes the contents of the FILES table. Table F-2.
FILES Table Table F-2. The FILES Table (page 2 of 2) Column Name Data Type Description 17 EOF INTEGER SIGNED Relative byte address of first unused byte of last block in partition or file; updated by UPDATE STATISTICS. Superceded by EOF2.
Filesets Filesets A fileset is a set of objects and files specified as a Guardian name that optionally includes these wild-card characters in the volume, subvolume, or file-id portions of the name: ? matches any single character. For example, TBL? matches TBL1 or TBLX but not TBL48. * matches any 0 to 8 characters. For example, * matches any 0 to 8-character name. *VOL* matches NEWVOL, OLDVOL1, VOL45, and so forth.
FORMAT File Attribute FORMAT File Attribute FORMAT is a file attribute that specifies the format of a partition. A Format 1 partition is limited to one gigabyte. A Format 2 partition might grow to one terabyte, or the size of a single disk volume. { FORMAT 1 | FORMAT 2 } If the table’s PARTITION ARRAY VALUE has been set to STANDARD or EXTENDED, the default format is FORMAT 1. If the table’s PARTITION ARRAY VALUE has been set to FORMAT2ENABLED, the default format is FORMAT 2.
Considerations—FREE RESOURCES Considerations—FREE RESOURCES If CONTROL TABLE SEQUENTIAL INSERT/UPDATE is set to ENABLE or ON, FREE RESOURCES also flushes the insert/update buffer for audited and nonaudited tables. On audited tables, FREE RESOURCES releases locks acquired only within the current TMF transaction using STABLE access. FREE RESOURCES releases locks that apply only to data being read.
Functions SQLCI-defined transaction ends. The FREE RESOURCES statement (without AUDITONLY) releases the locks. >> >> +> -- VOLUME $VOL1.PERSNL; INSERT INTO JOB VALUES (650, "ADMIN ASSISTANT") REPEATABLE ACCESS; 1 row(s) inserted. ... >> FREE RESOURCES; -- SQL operation complete. This example shows FREE RESOURCES in a host program where it also deallocates buffer space used for cursors: EXEC SQL OPEN CURSOR1; ... EXEC SQL FETCH CURSOR1; ... EXEC SQL DELETE FROM... WHERE CURRENT OF CURSOR1; ...
FUP Command MIN Function Returns a minimum value for a column or set of values PAGE_NUMBER Function Returns the page number of the current page in a report SETSCALE Function Specifies the scale of a host variable SUM Function Computes the sum of a set of numbers UPSHIFT Function Upshifts single-byte characters For more information, see the entry for a specific function. FUP Command FUP is an SQLCI command that executes File Utility Program (FUP) commands.
FUP Commands and SQL Objects Table F-3.
Considerations—FUP You can also use ALLOW, CTRL-Y, EXIT, FC, HELP, RESET, SET, SHOW, SYSTEM, and VOLUME to perform operations on files. When you return to SQLCI, the default system and volume are those that were in effect when you invoked FUP. If a table or view is encountered during the processing of a FUP command that does not support SQL objects, the table or view is skipped and a warning message is issued.
Examples—FUP PATH: /lt2/br/src/csrc/lex.
G Generalized Owner A generalized owner of an SQL object or Guardian file is any user ID that has ownership privileges for the file. On the node where the file is located, the generalized owner includes the user ID that owns the file, the group manager of the group that includes that user ID, and the super ID.
Examples—GET CATALOG OF SYSTEM Examples—GET CATALOG OF SYSTEM This SQLCI example shows how GET CATALOG OF SYSTEM returns a catalog name in an SQLCI session: >>GET CATALOG OF SYSTEM \SYSA; CATALOG: \SYSA.$SYSTEM.SQL --- SQL operation complete.
Consideration—GET VERSION For type CATALOG, name must be a catalog name or a DEFINE of class CATALOG. For type SYSTEM, name is optional (the default is the local node) but must be a node name with a leading “\” if specified. For all other types name must be a Guardian name (or an equivalent DEFINE). INTO :var (static SQL programs only) specifies a host variable in which to return the version. Consideration—GET VERSION In host language programs, GET VERSION returns an integer value.
GET VERSION OF PROGRAM Statement This COBOL example uses dynamic SQL to retrieve the version of catalog \sysa.$vol1.mycat and store it in an SQLDA: MOVE "GET VERSION OF CATALOG \sysa.$vol1.mycat" TO string1. EXEC SQL PREPARE s1 FROM :string1 END-EXEC. EXEC SQL EXECUTE s1 RETURNING USING DESCRIPTOR :mysqlda END-EXEC.
Examples—GET VERSION OF PROGRAM GET VERSION OF PROGRAM also sets SQLCODE to report status and fills in the SQLCA. GET VERSION OF PROGRAM has no EXPLAIN output. Examples—GET VERSION OF PROGRAM This SQLCI example retrieves the PCV, PFV, and HOSV of program MYPROG: >>GET CATALOG VERSION OF PROGRAM myprog; VERSION: 315 --- SQL operation complete. >>GET FORMAT VERSION OF PROGRAM myprog; VERSION: 315 --- SQL operation complete.
GOAWAY Command GOAWAY Command GOAWAY is a TACL utility program that allows a user with super ID authority to delete Guardian SQL files or shadow labels that cannot be removed with other commands or utilities. You execute GOAWAY from TACL with this command: GOAWAY [ /IN cmdfile/ ] [ filename[:S] ] ; /IN cmdfile/ specifies an EDIT file that lists SQL objects, programs, or shadow labels to delete. List one item per line, using syntax described for the filename [:S] option.
Group Manager Group Manager A group manager is a Guardian user ID that has user number 255. By convention, a group manager also has the user name MANAGER, but this is not required. 32,255 Group manager user ID number DP.MANAGER Typical group manager user ID name A group manager can act as the owner of any object or file on the local node owned by a member of the Guardian security group to which the group manager belongs (group number 32 or group name DP in the examples just listed).
Considerations—Guardian Names letters or digits. This portion of the name is sometimes called the “simple file name.” Considerations—Guardian Names If you do not fully qualify a Guardian name, SQL uses the current default node, volume, and subvolume names to expand the name as needed at name-resolution time. You can change the current defaults in effect for a program by changing the defaults in the process that executes the program or by setting the =_DEFAULTS DEFINE in the program.
H HEADING Clause HEADING is a clause in the ALTER TABLE, ALTER VIEW, CREATE TABLE, and CREATE VIEW statements that specifies a default heading for a column. HEADING string | NO HEADING HEADING string specifies a default heading for a column, expressed as a string of single-byte or multi-byte characters enclosed in single or double quotation marks. string can be 0 to 132 bytes long. string cannot include the character string specifier normally allowed on a string literal.
HEADINGS Option HEADINGS Option HEADINGS is an option of the report writer SET STYLE command. HEADINGS activates or suppresses the printing of headings in the current report and in subsequent reports until you reset the HEADINGS option or end the SQLCI session. Setting HEADINGS OFF is the same as specifying NOHEAD for every print item in your report. For information about the way report writer determines headings, see the Considerations subsection in DETAIL Command on page D-47.
Considerations—HELP The default is SYNTAX. Considerations—HELP HELP topics correspond to the main words of each major entry in the SQL/MP Reference Manual. For example, HELP is available for the topic CREATE TABLE (not CREATE or CREATE TABLE statement). In some cases, HELP contains additional “pointer entries” that direct you to available topics.
HELP TEXT Statement HELP TEXT Statement HELP TEXT is a DDL statement that specifies help text for a column of a table or view. HELP TEXT FOR COLUMN column ON { name } IS text-line [, text-line ] ... column is the name of a column with which to associate help text. name is the name of a table or view that includes the column. text-line is a line of help text in the form of a string of single-byte or multibyte characters enclosed in single or double quotation marks.
HISTORY Command HISTORY Command HISTORY is an SQLCI command that displays the commands or statements most recently entered during the SQLCI session. HISTORY identifies each command by a number that you can use with the FC command to reexecute or edit the command. (HISTORY is similar to the TACL command HISTORY.) HISTORY [ number ] ; The default is 10. number is the number of commands to display. The history buffer contains at the most 25 commands.
Host Programs Host Programs A host program or host language program is a program that contains both hostlanguage statements and embedded SQL statements. You can write SQL/MP host programs in C, COBOL, Pascal, or TAL. For more information, see one of these manuals: SQL/MP Programming Manual for C SQL/MP Programming Manual for COBOL SQL Programming Manual for Pascal SQL Programming Manual for TAL C programs can run in the Guardian or OSS environments; other programs run only in the Guardian environment.
Host Variables TYPE AS indicates that values in the host variable have a date-time or INTERVAL data type. (SQL interprets such values as character values unless you specify TYPE AS.) For more information about declaring and using host variables, see the SQL/MP programming manual for your host language.
Host Variables HP NonStop SQL/MP Reference Manual—523352-013 H-8
I ICOMPRESS File Attribute ICOMPRESS is a file attribute that controls key compression in index blocks. ICOMPRESS applies only to key-sequenced tables and indexes. { ICOMPRESS | NO ICOMPRESS } The table default is NO ICOMPRESS. The index default is the table value at index creation. Considerations—ICOMPRESS Occasionally, the use of ICOMPRESS can reduce the number of index levels. Reducing the number of index levels improves performance.
Considerations—IF/THEN/ELSE conditional expression cannot use the AVG, COUNT, MAX, MIN, or SUM functions, but can include column identifiers and report writer functions. (For more information, see Search Conditions on page S-5 or Expressions on page E-21.) THEN ( print-list ) specifies what to print if the condition is true. ELSE ( print-list ) specifies what to print if the condition is false. If you omit the ELSE clause, report writer prints blanks when the condition is false.
IN Predicate IN Predicate IN is a predicate that determines if a value is equal to any of the values in a list or collection of values. expression1 [ NOT ] IN { ( subquery ) } { ( expression-list ) } expression-list is: expression [ , expression ] ... subquery is a subquery that has a result table of one column. For more information, see Subqueries on page S-82. expression1 or expression is an expression. For more information, see Expressions on page E-21.
Examples—IN Examples—IN This example finds those items whose number is 39, 337, or 452: EMPNUM IN (39, 337, 452) The example finds those items whose part number is not in the PARTLOC table: PARTNUM NOT IN (SELECT PARTNUM FROM INVENT.PARTLOC) INCLUDE SQLCA Directive INCLUDE SQLCA is a host program directive that declares the SQL communication area (SQLCA) in a host program. The SQLCA is a status-checking area for host programs.
INCLUDE SQLDA Directive For more information about the contents and usage of SQLDA, see the SQL/MP programming manual for your host language. INCLUDE SQLDA ( sqlda-name [, sqlvar-count ] [ , names-buffer, name-length ] [ , { RELEASE1 | RELEASE2 } ] [ , CPRULES collation-buffer, collation-size ] ) sqlda-name is a host identifier that is the name for the SQLDA.
Consideration—INCLUDE SQLDA Consideration—INCLUDE SQLDA By default, INCLUDE SQLDA declares a version 2 SQLDA. To request a different version, use INCLUDE STRUCTURES before INCLUDE SQLDA. The CPRULES clause can be used with SQL/MP versions 300 or later.
INCLUDE STRUCTURES Directive INCLUDE STRUCTURES Directive INCLUDE STRUCTURES is a host program or dynamic SQL directive that specifies the version of the structures generated by the INCLUDE SQLCA, INCLUDE SQLDA, and INCLUDE SQLSA directives. { [ ALL ] INCLUDE STRUCTURES { { {| SQLCA { {| SQLDA { {| SQLSA {version | VERSION version } } VERSION version |} } VERSION version |} } VERSION VERSION CURRENT} } version is an integer that identifies a version of SQL/MP (1, 2, 300, 310, 315, 320, 325, or 330).
Considerations—INCLUDE STRUCTURES Considerations—INCLUDE STRUCTURES INCLUDE STRUCTURES can appear anywhere in the host language compilation unit where declarations are allowed, but it must precede any INCLUDE SQLCA, INCLUDE SQLDA, or INCLUDE SQLSA directive. You should specify INCLUDE STRUCTURES once in every SQL module that contains an INCLUDE SQLCA, INCLUDE SQLDA, or INCLUDE SQLSA directive.
Index Keys Index Keys An index is stored in a key-sequenced file. Each row in an index contains: A two-byte column called the “keytag” column The columns specified in the CREATE INDEX statement The primary key of the underlying table (the user-defined primary key, the SYSKEY, or combination of the clustering key and the SYSKEY) For a unique index, the primary key of the index is composed of the first two of these items.
INDEXES Table INDEXES Table The INDEXES table is a catalog table that describes primary keys and indexes. Table I-1 describes the contents of the INDEXES table. Table I-1.
Indicator Variables and Indicator Parameters Guardian names in the INDEXES table are fully qualified and use uppercase characters. The Guardian security vector (column 13) is stored as uppercase characters. Indicator Variables and Indicator Parameters In a host program, a variable called an indicator variable is associated with each SQL data item that can contain a null value.
INFO DEFINE Command INFO DEFINE Command INFO DEFINE is an SQLCI command that displays the attributes and values associated with one or more existing DEFINEs. (INFO DEFINE is similar to the TACL command INFO DEFINE and the OSS command info_define.) { define } INFO DEFINE { ( define [, define ] ... ) } [, DETAIL ]; define is the name of an existing DEFINE or DEFINEs for which you want information. You can specify define as a DEFINE template.
INITIALIZE SQL Command INITIALIZE SQL Command INITIALIZE SQL is an SQLCI command that allows a user with the local super ID to ensure that SQL is using compatible components and to prepare a node to run NonStop SQL/MP. The INITIALIZE SQL command is required whenever you install a new NonStop SQL/MP PVU or software product revision (SPR).
INSERT Statement INSERT Statement INSERT is a DML statement that inserts a row into a table or protection view. INSERT INTO { name } [ ( column-list ) ] { VALUES ( val [, val ] ...) [ insert-opt ] ... } { ( select-stmt ) [ insert-opt ] ... } { select-stmt } column-list is: [ * [ , syskey ] ] [ syskey , * ] [ col [ , col ] ...
INSERT Statement the columns, and specify values of appropriate type and size for the corresponding columns. val is a host variable, a literal, an expression, a parameter name, or the keyword NULL (representing a null value) that specifies a value. val cannot include a column reference. select-stmt specifies a select operation that selects values from other tables or views to insert in name.
Considerations—INSERT APPEND | ANYWHERE specifies whether to add rows at the end of the table (APPEND) or anywhere in the table (ANYWHERE). (Applies only to tables with relative file organization or to protection views defined on such tables. Cannot be used if the SYSKEY column is one of the columns for the INSERT.) With APPEND, if you specify an ORDER clause in select-stmt, rows are added in that order. With ANYWHERE, an ORDER clause in select-stmt has no effect.
Considerations—INSERT Date and time values A date-time data type is compatible only with another date-time data type with the same range of DATETIME fields. When you use a range of fields to specify only some of the DATETIME fields for a DATETIME column, SQL uses the current date and time for any missing fields to the left of the fields for which values are specified.
Examples—INSERT For information on buffering INSERT operations, see CONTROL TABLE Directive on page C-77.
INTERVAL Data Type In this example, CUSTLIST is a protection view of all columns of the CUSTOMER table except the credit rating. Suppose that one of your suppliers has become a customer. If you can use the same number for both the customer and supplier numbers, you can select the supplier information from the SUPPLIER table and insert it in the CUSTOMER table through the CUSTLIST view.
Considerations—INTERVAL Data Type start-ym [ (digits) ] [ TO end-ym ] specifies the range of fields for a year-month set of INTERVAL values, or a subset of the year-month INTERVAL values, and the number of digits allowed for the starting field in the set. start-dt [ (digits) ] [ TO end-dt ] specifies the range of fields for a day-time set of INTERVAL values, or a subset of the day-time INTERVAL values, and the number of digits allowed for the starting field in the set.
Example—INTERVAL Data Type MINUTE 1 to 59 SECOND 1 to 59 FRACTION 1 to 999999 (less with small precision) An INTERVAL can be negative, but individual fields within the interval are expressed as positive values. The negative sign (-), if present, applies to the entire value, and is not counted in the number of digits for any field. To compute the size of an INTERVAL column: Add 1 byte for the sign. Add 2 bytes for each nonstarting field other than FRACTION.
INTERVAL Literals INTERVAL Literals An INTERVAL literal is a constant of data type INTERVAL that represents a positive or negative duration of time as a year-month or day-time interval. An INTERVAL literal can contain a maximum of 18 digits, plus characters such as hyphens (-) or colons (:) that separate the values of INTERVAL fields. The value can be enclosed in either double quotation marks (shown in this diagram) or in single quotation marks.
INTERVAL Literals days is an unsigned integer that specifies a number of days. It can have up to 18 digits, minus the number of digits in the other fields of the INTERVAL literal. Negative values are allowed, with the minus sign inside the quotes. hours is an unsigned integer that specifies a number of hours. Used as a starting field, it can have up to 18 digits, minus the number of digits in the other fields of the INTERVAL literal; as a nonstarting field, it must be in the range 0 through 23.
Example—Interval Literals Example—Interval Literals These are all INTERVAL literals: INTERVAL “1” MONTH An interval of 1 month INTERVAL “7” DAY An interval of 7 days INTERVAL “2-7” YEAR TO MONTH An interval of 2 years, 7 months INTERVAL “5:2:15:36.8” DAY TO FRACTION(1) An interval of 5 days, 2 hours, 15 minutes, and 36.
INVOKE Directive and Command INVOKE Directive and Command INVOKE is a directive or SQLCI utility command that produces a record description that corresponds to a row in a specified table or view. The record description includes a data item for each column in the table or view except the SYSKEY column and (except for SQL-format) an indicator variable for each column that allows null values.
INVOKE Directive and Command If you omit the AS clause, the record name depends on the FORMAT option. For COBOL85, the record name is the unqualified name of the table or view. (For example, if the table name is \SYS1.$VOL1.PERSNL.JOB, the record name is JOB.) For C or PASCAL, the record name is the unqualified name of the table or view with the suffix “_type” appended. (For example, JOB_TYPE.
INVOKE Directive and Command If you do not specify a prefix, indicator variable names have no prefix. If you specify a prefix but do not specify a suffix, indicator names have no suffix. If you do not specify either a prefix or a suffix, the suffix depends on the language: C _i Pascal _i COBOL -I TAL ^i A prefix or suffix must consist of legal identifier values for the host language in which it is used.
Considerations—INVOKE Considerations—INVOKE To use the INVOKE statement on a table or view, you must have authority to read the table or view at compile time. Multibyte characters can be displayed only on output devices that support them. If a record definition contains a DEFAULT clause with a multibyte character, the output might not be displayed on your output device. Examples—INVOKE This SQLCI command generates an SQL-format description of a table named EMPLOYEE: >> INVOKE $VOL1.PERSNL.
ISLACK File Attribute ISLACK File Attribute ISLACK is a file attribute that specifies the minimum percentage of space to leave for future insertions when loading index blocks. ISLACK applies only to key-sequenced tables and to indexes. ISLACK percent The default is the value of the SLACK file attribute. The default for SLACK is 15 percent. percent is an integer from 0 to 99 that specifies the percent of empty space to leave in each index block during loading.
Considerations—ISLACK HP NonStop SQL/MP Reference Manual—523352-013 I-30
J Joins A join is an operation that combines two tables or views to form a new table. A join query is a query that requests columns from more than one table or view. A join query should contain predicates that compare a column from one table with a column from another table. The join concatenates (joins together) rows (from each of the joined tables) that satisfy the predicates. Without predicates, SQL creates a Cartesian product with all rows of each table combined with each other.
Examples - Joins DEPT TABLE DEPT_NUM DEPT_NAME DEPT_LOC 6400 Marketing - Far East 900 6410 Marketing - Korea 910 6420 Marketing - Hong Kong 920 6440 Marketing - Singapore 940 6470 Marketing - Taiwan 970 6480 Marketing - Australia 980 7600 Marketing - USA 100 7620 Marketing - USA West 120 Examples of inner joins In these examples, rows that do not have the same department number are not returned in the result.
Examples - Joins DEPT table contain a question mark (?) to denote missing information. Each question mark represents a null value. SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPT_NUM, D.DEPT_NUM, D.DEPT_NAME FROM EMPLOYEE E LEFT JOIN DEPT D ON E.DEPT_NUM = D.
JULIANTIMESTAMP Function JULIANTIMESTAMP Function JULIANTIMESTAMP is a function that converts a date-time value into a 64-bit Julian timestamp that represents the number of microseconds that have elapsed between 4713 B.C., January 1, 00:00 and the specified date-time value. JULIANTIMESTAMP returns a value of type LARGEINT. You can use JULIANTIMESTAMP anywhere SQL allows a numeric expression.
K Keys See one of these specific entries: Clustering Keys Index Keys Partitions (first keys) Primary Keys Syskeys (system-defined primary keys) User-Defined Keys (user-defined primary keys) KEYS Table The KEYS table is a catalog table that describes the columns of primary keys and indexes. Table K-1 describes the contents of the KEYS table. Table K-1.
KEYS Table HP NonStop SQL/MP Reference Manual—523352-013 K-2
L LEFT_MARGIN Option LEFT_MARGIN is an option of the SQLCI report writer SET LAYOUT command that sets the left margin for the current report and for subsequent reports until you reset it. LEFT_MARGIN number number specifies the number of spaces to precede the leftmost printed character in the report. number must be an integer in the range 0 through 255 and cannot exceed the value of the RIGHT_MARGIN layout option. The default is zero.
LIKE Predicate LIKE Predicate LIKE is a predicate that searches for character strings matching a pattern. char-exp [NOT] LIKE pattern [ ESCAPE char ] [ TERMINATE { char } ] char-exp is a character expression that specifies the set of strings to search for matches to pattern. pattern is a character expression that does not contain a column name and that specifies the pattern string for the search.
Considerations—LIKE If the values you compare are both empty strings (that is, strings of zero length), the LIKE predicate is true. A blank is compared in the same way as any other character. If the value of col-name is null or if host-variable or param-name contains INDICATOR clauses that specify a null value, the LIKE predicate evaluates to null. You can use only parameters in a prepared dynamic SQL statement or in a statement you enter through SQLCI.
Considerations—LIKE To include the escape character itself in the comparison string, enter two escape characters. For example, to locate A_B\C%, enter: NAME LIKE "A\_B\\C\%" ESCAPE "\" The escape character must precede only the percent sign, underscore, or escape character itself. For example, if the escape character is \, RA\BS is not valid. These guidelines apply to pattern matching using columns of data type CHARACTER: Columns of data type CHARACTER are fixed length.
Examples—LIKE Suppose that you want to select all names that end with the letter y. If you define the pattern-matching string as “%y@,” this statement finds all names ending in y (provided that no trailing blanks are entered): >> SELECT NAME FROM EMPLOYEE +> WHERE NAME LIKE "%y@" +> TERMINATE "@"; NAME ---Jay Holly The character sets associated with the column, the LIKE pattern, the ESCAPE character, and the TERMINATE character must be the same.
Limits Limits NonStop SQL/MP has limits on the size and number of objects, on the size of columns, on file attributes, and on other items you use with NonStop SQL/MP. SQL uses information stored in file labels and special records in the disk volume directory that contain information about a table, view, partition, or catalog. As a result, some limits depend on the size of file labels, which have a block size of 4096.
Limits default values. File label restrictions can reduce the maximum number of columns allowed to as few as 128. Columns per view The maximum number of columns allowed for a view is between 200 and 400, depending on the size of the column definitions. The size of the column definitions depends on the data types of the columns and the complexity and number of operators that define the selection of rows. The descriptions of the columns of the view must fit in a file label.
Limits A numeric literal cannot exceed 18 digits. Default value for a character column The maximum length for the default value is 8 characters. First key length A specification for the first key for partitions cannot exceed the length of the user-defined primary key or clustering key for tables or the length of the index key for indexes. See the Index length item later in this subsection. FROM clause tables The maximum number of tables that can be specified in a FROM clause is 16.
Limits SQL defines a lock limit for the number of row locks that a program can own. The limit is internal to SQL/MP software. If a program exceeds the limit and the locks cannot be upgraded to table locks, the program receives an error message. MAXEXTENTS The maximum number of extents allowed is 944 for a Format 1 partition. This number is decreased if the extent size (primary or secondary) is very large, such as a secondary extent size of 2,000 pages.
Limits When you create or alter a table or index with a large number of partitions, the PARTNS catalog table and associated IXPART01 index might become full. To correct the situation, distribute object and partition definitions across multiple catalogs. Actual limits depend upon the definition of the SQL tables and indexes, but the PARTNS and IXPART01 catalog tables can contain approximately 500,000 rows. Each table or index with N partitions stores N**2 rows of information in the PARTNS catalog table.
Limits Partitions per index or key-sequenced table, format 2 enabled partition array Typically, if the primary key of the index or key-sequenced table is between 10 and 50 bytes, the maximum number of partitions allowed can be from approximately 800 (for the smaller key size) down to 390 (for the larger key size). If the key of an index is between 10 and 50 bytes, the maximum number of index partitions is typically between approximately 620 (for keys of 10 bytes) and 238 (for keys of 50 bytes).
Limits Partitions per relative or entry-sequenced table This type of limit on partitions for a table is affected by the same restrictions that apply to key-sequenced tables with standard partition arrays. Additional restrictions also apply because of dependencies on the number and size of extents and on primary key values.
Limits A header is 32 bytes for key-sequenced tables and 22 bytes for relative and entrysequenced tables. In addition, there are two bytes overhead for each row in a block. For Format 2 key-sequenced tables, a file header is of 56 bytes. Statement length The maximum length of an SQL/MP statement is 32,767 characters, including blanks. Subquery nesting Queries can be nested a maximum of 16 levels, including the outermost query.
LINE_NUMBER Function The CREATE VIEW statement, including any name expansion from the use of asterisks in column, view, and table specifications, can have a maximum of 3,000 bytes. Views per table Approximately 180 protection views can be defined for a table. The limit is determined by the requirement that a small amount of information about each protection view defined on a table must fit in a file label. There is no maximum number of shorthand views for a table.
Considerations—LINE_NUMBER Considerations—LINE_NUMBER The default format for line numbers is I11. If you specify LINE_NUMBER OVER break-column, you must enter a BREAK ON command that defines the referenced break column (and that also defines break columns referenced in other LINE_NUMBER function calls in the current set). Examples—LINE_NUMBER This example produces a line that contains a line number, part number, and part name. The line numbers start over at 1 on the first line of each page.
LINE_SPACING Option LINE_SPACING Option LINE_SPACING is an option of the SQLCI report writer SET LAYOUT command that specifies how many lines to advance between report lines. LINE_SPACING also defines the increment of the SKIP clause. For example, if you set the LINE_SPACING option to 2 and specify SKIP 3 as a print item in a print list, the report writer skips six (2*3) lines before printing the next report line. LINE_SPACING number The default is 1.
Considerations—LIST If you omit number, SQL uses the current value of the LIST_COUNT session option. Using LIST NEXT without number is equivalent to pressing the return key at the S> prompt. A[LL] displays all rows of the result table and returns you to the standard SQLCI prompt (>>). Considerations—LIST Rows are listed on the OUT_REPORT file, or (if you did not specify one) on the OUT file.
Literals Literals Literals are numeric, string, date-time, or INTERVAL constants that can be used in expressions, in statements, or as parameter values.
LOAD Command control-option is: { { { { { { { { { { { { { { { { { { { { { { { ALLOWERRORS [ ON | OFF | num ] } } COUNT num-records } } EMPTYOK } } FIRST { ordinal-record-num } } { KEY record-spec } } { KEY (key-value[, key-value] ... ) } } { key-specifier ALTKEY (key-value } } { [, key-value] ...
LOAD Command move-option is: { { { { { { { { { { { { { SOURCEDICT SOURCEREC TARGETDICT TARGETREC dictionary-name record-name dictionary-name record-name MOVE { source-name TO target-name } { ( source-name TO target-name } { [, source-name TO target-name]...) } MOVEBYNAME [ ON | OFF ] MOVEBYORDER [ ON | OFF ] TRUNC[ATION] [ ON | OFF ] REDEFINE ( redefine-spec [, redefine-spec]...
LOAD Command Nonconvertible records include records that contain one or more of: A nonnumeric value in a numeric field A duplicate key value in the primary key field of the output file A null value when the target field cannot represent a null value Parity errors An ordering that does not match the sort criteria for the output file (only if you specified SORTED) Inconsistencies with constraints defined for the output table For more information about the rules for data conversion, see CONVERT
LOAD Command For entry-sequenced files or tables, record-spec is the Enscribe record address. For descriptions about the record addresses in entry-sequenced files and tables, see the Enscribe Programmer's Guide. KEY ( key-value> [, key-value ] ... ) indicates the approximate position of the starting record for key-sequenced files. Subsequent rows are obtained in primary key order. key-value is either a string in quotation marks or an integer in the range 0 to 255.
LOAD Command raw data on disc. You do not need to request the UNSTRUCTURED option to examine an unstructured file. UPSHIFT (for loading a non-SQL object only) converts all bytes of the input that contain lowercase ASCII characters to uppercase ASCII characters before loading the data to the target record.
LOAD Command BLOCKIN in-block-length specifies the length of an input block in bytes. in-block-length is a value from 1 through 32,767 that indicates the actual number of bytes requested in a single physical read operation. BLOCKIN does not apply to a table unless you specify the UNSTRUCTURED option. If the input block length exceeds the input record length specified in the RECIN in-record-length option, input records are deblocked.
LOAD Command ASCII EBCDIC Left square bracket Cent sign Right square bracket Exclamation point Circumflex Logical NOT sign The conversion is done without regard to the data types of fields or columns of the input, so undesired changes to the data can occur if you use EBCDICIN with input that is not composed of simple character data. RECIN in-record-length specifies the maximum length of an input record in bytes.
LOAD Command REELS num-reels (for loading from an unlabeled magnetic tape only) sets the number of reels that make up the input file. Specify num-reels as an integer from 1 through 255. The tape is read until two consecutive EOF marks are reached for num-reels. At each end of reel before the last reel, the tape is rewound and unloaded, and you are prompted for the next reel. If you omit REELS, in-file data transfer terminates when a single EOF mark is encountered.
LOAD Command { UNLOADIN | NO UNLOADIN } (for loading from a magnetic tape only) specifies whether the tape is unloaded when rewinding occurs. The default is UNLOADIN (the tape is unloaded when rewound).
LOAD Command MAX num-records (for loading files or tables—but not indexes—with key-sequenced file organization only) specifies the number of input records as an integer from 0 through 2,147,483,647. LOAD uses num-records to determine the size of the scratch file to be used by the SORT process. If you specify the SORTED option, you do not have to specify the MAX option. If you underestimate the number of records, the sort can be significantly slower.
LOAD Command ISLACK percent (for loading only files or tables with key-sequenced file organization) specifies the minimum percentage of space to be left in index blocks for future insertions. Specify percent as a numeric literal from 0 through 99. If space is not available when an insertion is made, a block split occurs. If you omit this option, LOAD uses the SLACK percent value.
LOAD Command TARGETDICT dictionary-name specifies the subvolume containing the DDL dictionary that contains the DDL record or DEF definition for an Enscribe output file. dictionary-name is a Guardian subvolume name. If you omit the TARGETDICT option, LOAD assumes that the dictionary resides on the current default subvolume. TARGETREC record-name (for loading non-SQL objects only) specifies the DDL record name of the record or DEF definition for outfile.
LOAD Command You can specify both MOVE and MOVEBYNAME. For fields where the two options conflict, MOVE overrides MOVEBYNAME. If MOVEBYNAME is ON, a DDL group representing a variable-length character string can be loaded. For more information, see Conversion of DDL Elementary Items on page C-103 under CONVERT. MOVEBYNAME OFF is the default. MOVEBYORDER [ ON | OFF ] specifies whether fields in the source are loaded to fields in the target on the basis of position.
LOAD Command CUSTOMER.ADDRESS.STREET-ADDRESS is the qualified name of the STREET-ADDRESS field in the ADDRESS group. The ADDRESS group is in the CUSTOMER group. redefined-qualified-name identifies a redefined field or group that corresponds to the original field or group. The name must be qualified by the group names at all preceding levels. For example, CUSTOMER.ADDRESS.STREET-DETAIL is the qualified name of the STREET-DETAIL field that redefines the STREET-ADDRESS field.
Considerations—LOAD If you specify more than one configuration file in the PARALLEL EXECUTION clause, you must specify the FOR index-name clause for each configuration file. If at least one index is specified in the CONFIG clause, the partitions of any indexes not specified are loaded in parallel using default configuration values. Considerations—LOAD LOAD requires authority to read the source file and to write to the target file.
Considerations—LOAD You should perform these operations when you need to load data into an audited table: 1. Use ALTER TABLE to set the AUDIT attribute of the table OFF. 2. Execute the LOAD utility to load data into the nonaudited table. 3. Use ALTER TABLE to set the AUDIT attribute of the table ON. 4. Perform online dumps of all table partitions and indexes. For more information about performing online dumps, see the TMF Operations and Recovery Guide.
Considerations—LOAD To load a single partition (primary or secondary), specify the name of the partition as out-file, and specify the name of the volume that contains the partition in the PARTOF option. If you attempt to load a secondary partition when you have not specified the PARTOF option, you receive an error message. To load all partitions, specify the name of the primary partition as out-file and omit the PARTOF option. Be careful when you use PAD and TRIM options.
Considerations—LOAD Field formats If a non-SQL object is the source or target, LOAD loads only those fields whose DDL descriptions conform these rules: The field must be elementary, unless it is the special DDL group that represents a variable-length character string, in which case the field is handled as one field during the LOAD operation. This DDL group has this structure and is converted to a column with data type VARCHAR: 02 A-VARCHAR 03 LEN PIC S9(4) COMP. 03 VAL PIC X(len).
Considerations—LOAD An interval is expressed as a multiple of the smallest unit in the INTERVAL type. For example, if the column in the table is defined as HOURS TO SECONDS, the value of the interval "1:2:3" is 3723, which is the number of seconds in 1 hour, 2 minutes, and 3 seconds. SQL does not support the Enscribe data types COMPLEX and LOGICAL, but an Enscribe field of either one of these data types is compatible with an SQL column of data type CHARACTER with the same length as the field.
Considerations—LOAD SQL Data Type Enscribe Data Type SMALLINT BINARY 16. SMALLINT UNSIGNED BINARY 16 UNSIGNED. INT BINARY 32. INT UNSIGNED BINARY 32 UNSIGNED. LARGEINT BINARY 64. PIC 9(d)V9(s) PIC 9(d)V9(s). PIC S9(d)V9(s) PIC S9(d)V9(s).1 PIC 9(d)V9(s) COMP same as NUMERIC(d+s,s) UNSIGNED PIC S9(d)V9(s) COMP same as NUMERIC(d+s,s) FLOAT FLOAT 64. FLOAT(n) FLOAT 32. FLOAT 64.
Considerations—LOAD then the field normally contains ASCII character data. However, if the field contains binary 0 in each of the 10 bytes, LOAD would consider the field to have the null value. If the Enscribe DDL description of a file contains a field defined as: 02 F1 PIC X(10) NULL "*". the field normally contains ASCII character data, but if the field contains "*" in each of the 10 bytes (that is, if the field contains the value "**********"), LOAD would consider the field to have the null value.
Considerations—LOAD some of the ASCII printable characters, in which case using one of those characters as the NULL attribute works correctly. For DECIMAL fields, the same thing is true, because each byte of a DECIMAL field normally contains the ASCII code for one of the characters "0" through "9", so it is easy to choose a value for the NULL attribute that will not yield a null value that is the same as any expected value for the field.
Considerations—LOAD These rules govern the transfer of data across character sets. A LOAD that violates these rules terminates with an error. Source and Target File Types Source Field Character Set Target Field Character Set SQL to SQL UNKNOWN Any character set ISO88591 ISO88591 ... ... ISO88599 ISO88599 KANJI KANJI KSC5601 KSC5601 UNKNOWN PIC X or PIC N ISO88591 PIC X ... ...
Considerations—LOAD You can use the FIRST KEY value option for every type of file or table except key-sequenced files or tables. You should use the FIRST KEY (value, value ...) option only for key-sequenced files or tables. You can specify string literals or numeric literals. A string literal occupies as many bytes as there are characters in the literal. Each numeric literal occupies one byte. To specify a value for a CHAR column, enclose the value in quotes.
Considerations—LOAD To specify 1,000,000 as a four-byte INTEGER value, start with 0, followed by 15, 66, and 64: FIRST KEY (0,15,66,64) This example specifies a key value for a table with a four-column key of varying types: CHAR(2), SMALLINT, CHAR(1), and SMALLINT, starting at the values “ab”, 20, “x” and 10: FIRST KEY ("ab", 0, 20, "x", 0, 10) The byte string in this example is 7 bytes long—one byte for each ASCII character and one byte for each numeric value.
Considerations—LOAD The value 7,201 for the year 1993 is obtained by dividing by 256 and using remainders, as described in the preceding binary column discussion. To specify a value for an INTERVAL column, first determine the number of bytes required. Find the entry for the column in the COLUMNS catalog table. Do not use INVOKE output; this does not describe the internal representation of the column.
Example—LOAD The SQLCI LOAD command ignores the PARTONLYIN if it is combined with the SHARE option and reads all the source partitions. HP encourages the use of BLOCKIN and RECIN attributes when the input file is an unstructured file. In this case, the RECIN must be set to the actual row length and BLOCKIN must be set to the largest multiple of the RECIN value between 1 through 32,767. Example—LOAD Suppose that the ODETAIL table on subvolume $VOL1.
Considerations—LOCK TABLE SHARE or EXCLUSIVE specifies the locking mode: SHARE Others can read, but not delete, insert, or update the table or view. EXCLUSIVE Others can read with BROWSE access, but cannot read with STABLE or REPEATABLE access and cannot delete, insert, or update. If you request a SHARE lock on a table locked with an EXCLUSIVE lock by another user, your request waits until the EXCLUSIVE lock is released.
Examples—LOCK TABLE Examples—LOCK TABLE This example locks an audited table with an EXCLUSIVE lock (presumably at a time when few users need access to the database) to perform an update. The CONTROL TABLE statement ensures the most efficient operation. COMMIT WORK automatically unlocks the table when it ends the transaction. >> VOLUME $VOL1.
Locking Locking To protect the integrity of the database, SQL provides locks on data. For example, SQL locks a row when an executing process (either SQLCI or a host program) accesses a row to modify it. The lock ensures that no other process simultaneously modifies the same row. Default locking normally protects data but reduces concurrency.
Lock Duration Table L-1. Lock Release Summary Audited Tables/Views Nonaudited Tables/Views Operation STABLE REPEATABLE STABLE REPEATABLE SELECT INTO RA KA RA KA FETCH cursor updateable 1 RS,KE2 KA RA KA FETCH cursor not updateable RS3 KA RA KA Set UPDATE or DELETE RS,KE KA RA KA INSERT KA KA RA KA CLOSE cursor RS,KE KA RA KA FREE RESOURCES RS,KE KA RA RA FREE RESOURCES AUDIT ONLY RS,KE KA N.A. N.A. UNLOCK TABLE N.A. N.A.
Lock Mode partition of a partitioned table, SQL might escalate the row locks to a partition lock. A partition lock applies only to the specific partition and not to the entire table. For a nonpartitioned table, a partition lock is a table lock. If you do not want lock escalation, use the TABLELOCK OFF option on the CONTROL TABLE statement. Lock Mode Lock mode controls access to locked data. You can control lock mode only for rows that are read.
LOCKLENGTH File Attribute FREE RESOURCES ROLLBACK WORK If you specify AUDITONLY, the process releases locks on audited objects only. You can use this option if you want to hold locks on nonaudited objects throughout a series of transactions. Stopping or abnormal termination of a process frees any locks the process holds on nonaudited tables. CLOSE and FREE RESOURCES release locks on nonaudited tables.
LOG Command ) LOCKLENGTH 6; —Lock length 6 bytes LOG Command LOG is an SQLCI command that starts or stops logging to a file. SQLCI logs the commands you enter and the information the commands display. You can also use an option to log only the commands. This option allows you to create an SQLCI OBEY command file from the log file directly. SQLCI does not log the FUP, EDIT, PERUSE, and TEDIT commands. LOG [ log-file [COMMAND[S] ] [ CLEAR ] ] ; log-file identifies the current log file and starts logging.
LOGICAL_FOLDING Option LOGICAL_FOLDING Option LOGICAL_FOLDING is an option of the SQLCI report writer SET LAYOUT command that specifies where to break a default detail line (one for which there is no DETAIL command) that does not fit within the report width. LOGICAL_FOLDING { ON } { OFF } ON breaks the line before the first print item that does not fit. ON is the default. OFF breaks the line exactly at the right margin, even if the break is in the middle of a print item.
Example—LOGICAL_FOLDING SUPPNUM SUPPNAME STREET ------- ------------------ ---------------------TY STATE POSTCODE ------------ ------------ ---------- CI -- 1 NEW COMPUTERS INC N FRANCISCO CALIFORNIA 2 DATA TERMINAL INC S VEGAS NEVADA SA 1800 KING ST.
M MAX Function MAX is a function that determines the maximum value within a set of values. The type of the result depends on the type of the argument. MAX { ( [ ALL ] expression ) } { ( DISTINCT column ) } [ ALL ] expression specifies an expression that indicates the set of values from which to determine a maximum value.
Example—MAX MAX is evaluated after eliminating all null values from the aggregate set. If the result set is empty, MAX returns a null value. A host variable that receives the result of the MAX function must have an indicator variable to handle a possible null value. (For more information about using indicator variables, see the SQL/MP programming manual for your host language.) Example—MAX To display the maximum value in the SALARY column, type: >>SELECT MAX (SALARY) FROM PERSNL.
Message File values; for tables in descending order, the last partition is the one with the lowest range of FIRST KEY values.) It is generally not efficient to have partitions with hundreds of extents, so you should keep MAXEXTENTS well below the allowed maximum value. If necessary, increase the number of partitions. In addition, the maximum value for MAXEXTENTS might be lower in future releases.
Considerations—MIN ALL is an optional keyword that does not change the meaning of the clause. SQL uses all rows (whether or not you specify ALL) unless you use the DISTINCT clause, described next. DISTINCT column specifies a set of distinct column values from each row of the result table to determine a minimum value. The column cannot be a column from a view that corresponds to an expression in the view definition.
MODIFY CATALOG with REPLACE NODENAME MODIFY CATALOG with REPLACE NODENAME The MODIFY CATALOG with REPLACE NODENAME command modifies node names in SQL/MP catalogs on the local node. A catalog can be a user-defined catalog or the system catalog. This command is intended for use when physically moving a disk from one node in your network to another node, or when system loading a node with a new node name or number. When this happens, changes are not automatically reflected in the catalogs and file labels.
MODIFY CATALOG with REPLACE NODENAME To specify a single catalog, enter the name of the catalog (the name of the subvolume that contains the catalog). To specify multiple catalogs in a catalogset, use wild-card characters. You can use these wild-card characters: * Matches 0 to 8 characters in the position where it appears. Specifying only an asterisk indicates any name is acceptable. To specify all catalogs, use either $*.* or *.* ? Matches any single character For example, $DATA.
MODIFY CATALOG with REPLACE NODENAME The maximum number of volumesets that can be specified in this clause is 10 for each volumeset list. For example, this clause is invalid because it has 11 volumes in the first volumeset list: ($A*,$B*,$C*,$D*,$E*,$F*,$G*,$H*,$I*,$J*,$K* EXCLUDE $Z*) If this clause is not specified, only the node name is considered. WITH node-name specifies the node name to replace occurrences of the node name specified in replace-spec.
Considerations—MODIFY CATALOG with REPLACE NODENAME LISTALL is the default. DETAIL [ MATCH | ALL ] REPORT [ TO EMS-Collector ] [ ON ] [ OFF ] specifies that detail about the MODIFY CATALOG with REPLACE NODENAME operation is to be sent in event messages to a valid EMS collector. If MATCH is specified, detailed is reported about SQL catalog tables specified in target-spec that contain node names that match the criteria specified in the REPLACE clause.
Considerations—MODIFY CATALOG with REPLACE NODENAME volume name in file label), and MODIFY REGISTER Command (to register userdefined catalogs in the local system catalog). Multiple MODIFY commands (including LABEL, CATALOG, and REGISTER commands) can be run concurrently on the same node as long as each command is processing a different set of files or catalogs.
Considerations—MODIFY CATALOG with REPLACE NODENAME be run on, before they are used. Prepare scripts that run the necessary MODIFY commands. When you add a new dependent object to the database, update the scripts. While the node is in a consistent state, you can use the DISPLAY USE OF command to locate dependent objects. After MODIFY commands are run, use the VERIFY utility to verify that the database is in a consistent state. Partitioned objects.
Examples—MODIFY CATALOG with REPLACE NODENAME Examples—MODIFY CATALOG with REPLACE NODENAME These examples illustrate the use of the REPLACE clause.
MODIFY CATALOG with REPLACE VOLUME MODIFY LABEL with REPLACE VOLUME operation on all catalog tables in the catalog first. For more information about MODIFY CATALOG with REPLACE VOLUME, see Considerations-MODIFY CATALOG with REPLACE VOLUME for more details. MODIFY [DICTIONARY] CATALOG target-spec replace-spec WITH volume-name [ [,] option ] ...
MODIFY CATALOG with REPLACE VOLUME TABLES (file code must be 581). The optional EXCLUDE catalog-list-2 clause specifies catalogs to be excluded from catalog-list-1. REPLACE VOLUME volume-name [(node-name1 [,node-name2] [EXCLUDE node-name3 [,node-name4] ] ) ] specifies the volume name to be replaced in the catalog tables. SQL catalog tables contain file names, and those file names contain volume names. Volume names are changed based on the node-name list.
MODIFY CATALOG with REPLACE VOLUME volume-name must be preceded by a dollar '$' character and consist of at least one to six alphanumeric characters. The first character must be alphabetic. Either uppercase or lowercase alphabetic characters can be specified; alphabetic characters are upshifted before the comparison and substitution process. ALLOWERRORS [ OFF | ON | number-of-errors ] determines handling of nonfatal errors. MODIFY reports two classes of errors: fatal errors and nonfatal errors.
Considerations-MODIFY CATALOG with REPLACE VOLUME REPLACE clause. If ALL is specified, details are reported about all SQL catalog tables specified in target-spec irrespective of a match being identified with regards to volume name. MATCH is the default option. For information on report options, see REPORT Option on page R-3. CHECKONLY specifies that the catalog tables specified in target-spec should be checked to see if they contain volume names that match the criteria specified in the REPLACE clause.
Considerations-MODIFY CATALOG with REPLACE VOLUME However, if the volume name where the catalog exists has not been modified at the time of executing MODIFY DICTIONARY command, then consider executing MODIFY CATALOG with REPLACE VOLUME command first followed by MODIFY LABEL with REPLACE VOLUME. For example, if a CATALOG exists on $DATA00.CATVOL and the volume name has been changed to $NEW00, the file labels of the catalog tables and indexes retain the CATALOG name field as $DATA00.
Considerations-MODIFY CATALOG with REPLACE VOLUME Partitioned objects. For a partitioned SQL object, each volume that contains a partition of the object must be specified separately. MODIFY CATALOG does not automatically modify information about all partitions of a partitioned object. You must be aware of how the database is partitioned. While the node is in a consistent state, SELECT from the PARTNS partitions table to locate other partitions. Prepare scripts that run the necessary MODIFY commands.
Examples-MODIFY CATALOG with REPLACE VOLUME Examples-MODIFY CATALOG with REPLACE VOLUME These examples illustrate the use of the REPLACE clause.
MODIFY LABEL MODIFY LABEL The MODIFY LABEL command has two options that can be specified as the replace-spec: MODIFY LABEL with REPLACE NODENUMBER MODIFY LABEL with REPLACE VOLUME MODIFY LABEL with REPLACE NODENUMBER The MODIFY LABEL with REPLACE NODENUMBER command modifies node numbers stored in file labels of SQL objects and SQL object programs on the local node.
MODIFY LABEL with REPLACE NODENUMBER MODIFY [DICTIONARY] LABEL target-spec replace-spec WITH node-spec [ [,] option ] ...
MODIFY LABEL with REPLACE NODENUMBER The wild-card characters you can use are: * Matches 0 to 8 characters in the position where it appears. Specifying only an asterisk indicates that any name is acceptable. To specify all files on all volumes, use either $*.*.* or *.*.* ? Matches any single character For example, *VOL* matches NEWVOL, OLDVOL1, and VOL45. $VOL1.SUBV1.* specifies all files on subvolume SUBV1 of volume $VOL1, and \SYS1.*.SUBV1.
MODIFY LABEL with REPLACE NODENUMBER For example, $DAT* specifies all volumes that begin with $DAT. *VOL* matches NEWVOL, OLDVOL1, and VOL45. VOL? matches VOL1 and VOLX but not VOL or VOL48. The maximum number of volumesets that can be specified in this clause is 10 for each volumeset list.
MODIFY LABEL with REPLACE NODENUMBER ALLOWERRORS [ OFF | ON | number-of-errors ] determines handling of nonfatal errors. MODIFY LABEL with REPLACE NODENUMBER reports two classes of errors: fatal errors and nonfatal errors. The MODIFY LABEL with REPLACE NODENUMBER command terminates immediately after reporting a fatal error.
Considerations—MODIFY LABEL with REPLACE NODENUMBER specified in the REPLACE clause. If ALL is specified, detail is reported about all SQL objects and object programs specified in target-spec. MATCH is the default. For information on report options, see REPORT Option on page R-3. CHECKONLY specifies that file labels specified by target-spec should be checked to see if they contain node numbers that match the criteria specified in the REPLACE clause. No file labels are modified.
Considerations—MODIFY LABEL with REPLACE NODENUMBER begin with SQL, specifying $VOL1.SQL*.* is more efficient than specifying $VOL1.*.*. The MODIFY LABEL with REPLACE NODENUMBER command would not have to search for SQL objects on other subvolumes. Multiple MODIFY commands (including LABEL, CATALOG, and REGISTER commands) can be run concurrently on the same node as long as each command is processing a different set of files or catalogs.
Considerations—MODIFY LABEL with REPLACE NODENUMBER moved from \SYS2 to \SYS1. The database is left in an inconsistent state if only these commands are run: >> MODIFY LABEL $DBS.*.* +> REPLACE NODENUMBER \SYS2 ($DBS) WITH \SYS1; >> MODIFY CATALOG $DBS.CAT +> REPLACE NODENAME \SYS2 ($DBS) WITH \SYS1; One example of an inconsistency that exists is that the \SYS1.$DB1.CAT.USAGES table will indicate that the USINGOBJNAME of the index is \SYS2.$DBS.OBJECTS.I1, although $DBS is now on \SYS1.
Examples—MODIFY LABEL with REPLACE NODENUMBER The MODIFY DICTIONARY utility does not modify the node names in the DEFINE set stored in the object program file. After the DEFINEs are changed by the user, if automatic recompilation is enabled the programs are automatically recompiled using the new DEFINEs. A catalog and the objects registered in it must be on the same node. However, it is possible for an object to be on a different disk than its catalog.
Examples—MODIFY LABEL with REPLACE NODENUMBER In this REPLACE clause, the node number associated with the node name \SQLNLS is replaced with the node number associated with the node name \SQL: REPLACE NODENUMBER \SQLNLS WITH \SQL In this REPLACE clause, node number 50 is replaced with the node number 100 if 50 is the node number associated with the node name \SQLNLS and 100 is the node number associated with the node name \SQL: REPLACE NODENUMBER (\SQLNLS,50) WITH (\SQL,100) This example lists t
Examples—MODIFY LABEL with REPLACE NODENUMBER This SELECT statements illustrate this point: >> SELECT FILENAME,PARTITIONNAME,CATALOGNAME +> FROM \A.$DA1.CATSUBV.PARTNS; FILENAME PARTITIONNAME CATALOGNAME -------------- -------------- --------------\A.$DA1.SQL.T1 \A.$DA1.SQL.T1 \A.$DA1.CATSUBV \A.$DA1.SQL.T1 \A.$DB1.SQL.T1 \A.$DB1.CATSUBV \A.$DA1.SQL.T1 \C.$DC1.SQL.T1 \C.$DC1.CATSUBV >> SELECT FILENAME,PARTITIONNAME,CATALOGNAME +> FROM \A.$DB1.CATSUBV.
MODIFY LABEL with REPLACE VOLUME The ($DB1) part is necessary to change all references from \A.$DB1.SQL.T1 to \B.$DB1.SQL.T1 while leaving intact all references to the \A.$DA1.SQL.T1 partition that remains on \A. After the MODIFY commands are run, the information in the PARTNS catalog tables looks like this: >> SELECT FILENAME,PARTITIONNAME,CATALOGNAME +> FROM \A.$DA1.CATSUBV.PARTNS; FILENAME PARTITIONNAME CATALOGNAME -------------- -------------- --------------\A.$DA1.SQL.T1 \A.$DA1.SQL.T1 \A.$DA1.
MODIFY LABEL with REPLACE VOLUME MODIFY [DICTIONARY] LABEL target-spec replace-spec WITH volume-name [ [,] option ] ...
MODIFY LABEL with REPLACE VOLUME You can also specify a logical DEFINE name as a fileset. File labels for dependent objects and partitions are not considered for modification unless the dependent object or partition is specified in simple-fileset-list-1. For example, if $A.B.T1 is a table that has a dependent index, specifying $A.B.T1 results in only the file label of the table being considered; the file label of the index is not considered.
MODIFY LABEL with REPLACE VOLUME WITH volume-name specifies the volume name to replace occurrences of the volume name specified in replace-spec. volume-name must be preceded by a dollar '$' character and consist of at least one to six alphanumeric characters. The first character must be alphabetic. Either uppercase or lowercase alphabetic characters can be specified; alphabetic characters are upshifted before the comparison and substitution process.
Considerations-MODIFY LABEL with REPLACE VOLUME DETAIL [ MATCH | ALL ] REPORT [ TO EMS-Collector ] [ON ] [OFF ] specifies the details about the MODIFY LABEL with REPLACE VOLUME operation that is to be sent in event messages to a valid EMS collector. If MATCH is specified, details are reported about SQL file labels specified in targetspec that contain volume names that match the criteria specified in the REPLACE clause.
Considerations-MODIFY LABEL with REPLACE VOLUME CATALOG with REPLACE VOLUME command cannot be run first because it will try to identify the CATALOG name from the file label to update the records in catalog indexes. However, if the volume name where the catalog exists has not been modified at the time of running the MODIFY DICTIONARY command, consider executing MODIFY CATALOG with REPLACE VOLUME command first followed by MODIFY LABEL with REPLACE VOLUME command. For example, if a catalog exists on $DATA00.
Considerations-MODIFY LABEL with REPLACE VOLUME Partitioned objects. Volume names in the file labels of partitions of tables and indexes are not modified unless those partitions are specified in the MODIFY LABEL with REPLACE VOLUME command. For a partitioned SQL object, each volume that contains a partition of the object must be specified separately. The MODIFY LABEL with REPLACE VOLUME command does not automatically modify information about all partitions of a partitioned object.
Examples-MODIFY LABEL with REPLACE VOLUME Examples-MODIFY LABEL with REPLACE VOLUME These examples illustrate the use of the REPLACE clause.
MODIFY LABEL with REPLACE VOLUME and Partitioned Objects This example on summary information is included whether you specify LISTALL or NO LISTALL: Summary Information: nnn label(s) modified. nnn label(s) not modified. MODIFY LABEL with REPLACE VOLUME and Partitioned Objects This example shows the usage of the MODIFY LABEL with REPLACE VOLUME command when you move partitioned objects. Suppose that you have table T1 with one partition at \A.$DA1.SQL.T1, a second partition at \A.$DB1.SQL.
MODIFY LABEL with REPLACE VOLUME and Partitioned Objects FILENAME PARTITIONNAME CATALOGNAME -------------- -------------- --------------\C.$DC1.SQL.T1 \A.$DA1.SQL.T1 \A.$DA1.CATSUBV \C.$DC1.SQL.T1 \A.$DB1.SQL.T1 \A.$DB1.CATSUBV \C.$DC1.SQL.T1 \C.$DC1.SQL.T1 \C.$DC1.CATSUBV Now suppose that the $DB1 volume on \A node is renamed to $XYZ.
MODIFY REGISTER Command MODIFY REGISTER Command The MODIFY REGISTER command registers a user-defined catalog in the local system catalog. Each node that uses NonStop SQL/MP has a catalog called the system catalog that contains information about all the catalogs on the node. If a disk containing an SQL database is moved from one node to another, the catalogs that reside on the relocated disk are not automatically registered in the system catalog on the new node.
MODIFY REGISTER Command For example, $DATA.* specifies all catalogs on the volume $DATA, while *.* specifies all catalogs on the node. *VOL* matches NEWVOL, OLDVOL1, and VOL45. VOL? matches VOL1 and VOLX but not VOL or VOL48. The MODIFY REGISTER command functions requires that a subvolume contains a valid catalog if the subvolume contains the catalog table TABLES (file code must be 581). ALLOWERRORS [ OFF | ON | number-of-errors ] determines handling of nonfatal errors.
Considerations—MODIFY REGISTER Considerations—MODIFY REGISTER You must be logged on as the super ID to run a MODIFY DICTIONARY command, unless you specify the CHECKONLY option. NonStop SQL/MP uses the TMF subsystem to protect the integrity of the database during the MODIFY REGISTER operation. MODIFY commands are not allowed inside a user-defined transaction. The MODIFY REGISTER command is one of a set of commands that uses the MODIFY DICTIONARY utility.
Multibyte Character Sets This summary information is displayed if either LISTALL or NO LISTALL is specified: Summary Information: nnn catalog(s) registered. nnn catalog(s) not registered. For a comprehensive example, see the MODIFY LABEL on page M-20. Multibyte Character Sets SQL supports two multibyte character sets: Kanji KSC5601 Multibyte character sets are described under the entry Character Sets and can be associated with columns, literals, host variables, and parameters.
System Default National Character Set HP NonStop SQL/MP Reference Manual—523352-013 M-44
N NAME Command NAME is an SQLCI report writer command that assigns an alias to a column in the select list of the SELECT command. You can then use the alias to refer to the column in any other part of your report definition. NAME is convenient for defining abbreviations for long column names or for assigning informative names to columns that consist of expressions. NAME column alias ; column identifies a column in the select list of the SELECT command.
NAME Option NAME Option The NAME option specifies an operation name for an operation started by a statement that includes the NAME option. Use the operation name in subsequent CONTINUE statements or to identify EMS messages sent by the operation. For more information, see REPORT Option on page R-3. NAME operation-name operation-name is an SQL identifier to be the name for the operation.
Names By default, SQL resolves names in a static SQL statement at program startup, resolves names in a prepared statement at the time the PREPARE or EXECUTE IMMEDIATE executes, and resolves names in a non-prepared SQLCI statement at the time you enter the statement.
NEWLINE_CHAR Option NEWLINE_CHAR Option NEWLINE_CHAR is an option of the SQLCI report writer SET STYLE command that specifies the character that indicates a new line in a column heading. NEWLINE_CHAR "character" character is a single-byte character to mark the end of a line in a heading string. The default is “/”. Consideration—NEWLINE_CHAR For information about how to create headings, see DETAIL Command on page D-47.
NOPURGEUNTIL File Attribute NOPURGEUNTIL File Attribute NOPURGEUNTIL is a Guardian file attribute that specifies an expiration date and time after which a table or index can be purged or dropped. NOPURGEUNTIL applies to key-sequenced, relative, and entry-sequenced tables and to indexes. SQL stores the date and time in local civil time (LCT).
NULL Predicate NULL Predicate NULL is a predicate that determines whether a column contains a null value. row-value-specification IS [ NOT ] NULL row-value-specification is: { expression [, expression ] ... } { ( expression [, expression ] ... ) } Considerations—NULL If any expression in the NULL predicate evaluates to a value other than null, the IS NOT NULL predicate evaluates to TRUE; otherwise, IS NOT NULL evaluates to FALSE. This chart summarizes expression evaluation for null predicates.
Null Values This example evaluates to true if the expression (PRICE + TAX) evaluates to null: (PRICE + TAX) IS NULL This example evaluates to true if the value in :JOBCODE is not null: :JOBCODE IS NOT NULL This example finds all rows where both FIRST_NAME and SALARY have a null value: FIRST_NAME, SALARY IS NULL Null Values A null value is a special symbol, independent of data type, that represents an unknown or inapplicable value. A null value indicates that an item has no value.
Defining Columns That Allow or Prohibit Nulls In deciding whether to allow nulls or use defaults, also note these points: Null values are not the same as blanks. Two blanks can be compared and found equal, while the equivalence of two null values is indeterminate. Null values are not the same as zeros. Zeros can participate in arithmetic operations, while null values are excluded from arithmetic.
Specifying Null Values in Host Programs example uses the LIKE predicate to avoid entering the whole, exact table name for the OD2 table. The column of interest is the DELIV_DATE column in table OD2. >> SELECT NULLALLOWED FROM COLUMNS +> WHERE TABLENAME LIKE "%OD2%" AND +> COLNAME = "DELIV_DATE"; NULLALLOWED ----------Y --- 1 row(s) selected.
Null Values and Expression Evaluation Null Values and Expression Evaluation This chart summarizes the results of expression evaluation with null values.
Numeric Data Types >> SELECT DEPT FROM PRJ; DEPT -----Z Z Z Numeric Data Types Table N-1 lists the numeric data types available in NonStop SQL/MP. A numeric data type is compatible with any other numeric data type, but not with character, date-time, or interval data types. Table N-1.
Considerations—Numeric Data Types Table N-2. Numeric Data Types in SQL—Floating Point Types SQL Designation Description Size or Range (1) FLOAT [(pre)] Approximate floating point number; pre-designates from 1 through 54 bits of precision +/-8.62 times 10**-78 through +/-1.
Numeric Literals Numeric Literals A numeric literal represents a numeric value. Each numeric literal has the data type NUMERIC and the minimum precision required to represent the value it specifies. A simple numeric literal (one without an exponent) can include up to 18 digits (0 through 9), a plus sign (+) or a minus sign (–), and a period (.) that indicates a decimal point. Leading zeros do not count toward the 18-digit limit; trailing zeros do.
Example—Numeric Literals HP NonStop SQL/MP Reference Manual—523352-013 N-14
O OBEY Command OBEY is an SQLCI command that executes SQL statements and SQLCI commands from a file. OBEY executes the statements and commands exactly as if you had entered them from the terminal. After execution, SQLCI closes the file but does not return any setting changed by the commands (such as a session attribute) to a previous state. OBEY is often used to set DEFINEs or define reports, but is useful in any situation in which you repeat a sequence of statements or commands.
Considerations—OBEY Specify sections within a command file by including a section header starting in column 1 at the beginning of each section: ?SECTION section-name The section-name is an SQL identifier that is the name of the section. Each section name within a file should be unique, because SQLCI executes only the first section it finds that has the name you specify in an OBEY command. Most command files are simply EDIT files that contain SQLCI commands (and, optionally, section headers).
Examples—OBEY If BREAK_KEY is OFF, pressing the Break key interrupts execution and passes control to the previous Break key owner, usually TACL. From TACL, enter PAUSE to resume or STOP to terminate SQLCI. Examples—OBEY This example shows the contents of a simple command file that sets DEFINE values: SET ADD ADD ADD DEFMODE ON; DEFINE =REP, FILE \SYS1.$VOL2.SALES.SALESREP; DEFINE =CUST, FILE \SYS1.$VOL2.SALES.CUSTOMER; DEFINE =ORD, FILE \SYS1.$VOL2.SALES.
OCTET_LENGTH Function OCTET_LENGTH Function The OCTET_LENGTH function returns the length of a character string in bytes. OCTET_LENGTH (character-string) where character-string is: {string-literal } {column-name } {param-name } {host-var-name } {UPSHIFT function } {character-expression} character-string specifies the string for which the length is to be returned. Considerations—OCTET LENGTH Function SQL returns the result as a two-byte signed integer with a scale of zero.
OPEN Statement OPEN Statement OPEN is a DML statement that opens a cursor in a host program. OPEN executes the SELECT associated with the cursor, positions the cursor before the first row selected, and returns statistics to the SQLSA. In dynamic SQL, OPEN also specifies parameters for the SELECT. OPEN { cursor } [ USING :var [, :var ]... ] { :cursor-var } [ USING DESCRIPTOR :in-sqlda ] cursor is the name of a cursor defined by DECLARE CURSOR.
Example—OPEN To use a cursor, you must first declare it with DECLARE CURSOR and then open it with OPEN. After a successful OPEN, you use FETCH to retrieve data. You cannot open a cursor that is already open in the program. (Use CLOSE or FREE RESOURCES to close a cursor before program termination.
OUT Command create, compile, execute, and maintain the program. NonStop SQL/MP uses the ZYQ name to identify the file in an SQL/MP catalog. To determine a pathname from a ZYQ name, use the DETAIL option on the FILEINFO or FUP INFO command. Both these commands return one of the pathnames of an OSS file as part of the DETAIL display. OUT Command OUT is an SQLCI command that directs SQLCI output to a specific file or closes the current OUT file and redirects output to the initial OUT file.
Example—OUT Example—OUT In this example, SQLCI output is directed to a printer. After the SELECT command is executed, the output is redirected to the initial OUT file. >> OUT $S.#FASTPRT; >> SELECT * FROM EMPLOYEE; >> OUT; OUT_REPORT COMMAND OUT_REPORT is an SQLCI report writer command that directs the formatted output of a SELECT command to a specified report file, instead of to the OUT file. OUT_REPORT [ file ] [ CLEAR [ SPOOL3 spool-option [, spool-option] ...
Considerations—OUT_REPORT The level 3 spooling options (and the defaults SQLCI uses when you open a spooler file) are: LOC #name Spooler location. Default is #DEFAULT. FORM name Name of form. Default is blanks (no form). REPORT name Name of report. Default is your user ID. COPIES num Number of copies. Default is 1. PAGESIZE num Lines per page in PERUSE. (Make this the same as the PAGE_LENGTH option.) Default is 60. For more information about level 3 spooling, see the Spooler Programmer's Guide.
OVERFLOW_ CHAR OPTION OVERFLOW_ CHAR OPTION OVERFLOW_CHAR is an option of the SQLCI report writer SET STYLE command that specifies the default filler character to print when the value of a numeric report item is too large for its display format. OVERFLOW_CHAR “character” character is a printable, single-byte character to use as an overflow character. The default is *.
P PAGE_COUNT Option PAGE COUNT is an option of the SQLCI report writer SET LAYOUT command that specifies the maximum number of pages for a printed report. PAGE_COUNT { number } { ALL } number is an integer in the range 1 through 32,767 that specifies the number of pages to print. ALL specifies printing the entire report. The default is ALL. Consideration—PAGE_COUNT After the report writer prints the maximum number of pages, SQLCI terminates the SELECT command that retrieved the information for the report.
Considerations—PAGE FOOTING CENTER centers each line of the page footing between the left and right margins. If you omit CENTER, the page footing starts at the left margin. Considerations—PAGE FOOTING On each page of a report, a blank line separates the page footing from the body of the page. On the last page, the page footing prints below the report footing. Only one PAGE FOOTING command is in effect at a time. When you enter a PAGE FOOTING command, it replaces the previous one.
Considerations—PAGE_LENGTH number is an integer in the range 1 through 32,767 that specifies the number of lines per report page. number must be large enough to print at least one detail line (or a total or subtotal line, if specified) plus any page title and page footing. Considerations—PAGE_LENGTH Each report page begins with the page title (if defined) and ends with a page footing (if defined).
PAGE TITLE Command The output looks like this: Monthly Report - 6 PAGE TITLE Command PAGE TITLE is an SQLCI report writer command that specifies text for the top of each report page. [ PAGE ] TITLE print-item [,print-item]...[ CENTER ] ; print-item specifies an item to print in the page title. The form for print-item is the same as in the DETAIL command, except that it cannot include the HEADING, NOHEAD, or NAME clause. For more information, see DETAIL Command on page D-47.
Parallel Index Loading Parallel Index Loading If you create an index on a base table that already contains data, SQL automatically loads the index file with data from the base table. If the index is partitioned, you can specify the PARALLEL EXECUTION option on the CREATE INDEX statement to direct SQL to load partitions of the index in parallel. A similar clause on the LOAD command serves the same function there.
Specifying Configuration for Parallel Index Loading 3 * Number of Records / Number of Partitions SQL estimates the number of records in the base table by dividing the file size by the record length. The estimate of the number of records is used by the sort process to calculate the scratch file size. For more information on the scratch file size, see the FastSort Manual. Swap file—The swap file for a sort process defaults to the same volume as the scratch volume if the scratch volume is local.
Specifying Configuration for Parallel Index Loading Set of volumes to exclude from overflow storage (NOSCRATCHON) Default pool of volumes to use for swap files for the record generators, and another pool for swap files for the sort processes (SWAP) In addition, you can specify any of these attributes for a specific partition. Thus, CREATEINDEX specifies both default and explicit configuration values for record generators and sort processes. The values you specify override any SQL defaults.
Specifying Configuration for Parallel Index Loading preserve software behavior available in NonStop SQL/MP versions prior to version 315. If you do not specify LOCALONLY, each SORTPROG or RECGEN process runs on the node where the associated partition resides. If you specify LOCALONLY, it must be the first CREATEINDEX statement in the configuration file. BASETABLE indicates attributes apply to processes that read the base table partition.
Specifying Configuration for Parallel Index Loading NUMRECS ( number ) is valid only if INDEX is specified and specifies the approximate number of records to be loaded into the index partition. This number is used to calculate the scratch file size, as described in the FastSort Manual. If this number is too small, the sort process might fail with sort error 30 and file-system error 45 (File is full). Use this attribute if the index is not partitioned evenly across all volumes.
Consideration—Parallel Index Loading You can specify up to 32 volumes, limited by the line length (a maximum of 132 characters). The scratchvol specification can include wild-card characters (* and ?). You cannot specify both NOSCRATCHON and SCRATCHON. If you do not specify either SCRATCHON or NOSCRATCHON, the sort processes consider using any available volume except $SYSTEM and TMF audit trail volumes. If SMF is installed on your node, you can specify only physical volumes for SCRATCHON.
Sample Configuration File Sample Configuration File == == == == == == == Sample configuration file for loading index partitions in parallel. Creates index AGEINDEX on table CUST, which is partitioned as follows: $DATA1.SALES.CUST $DATA2.SALES.CUST $DATA3.SALES.CUST \NEWYORK.$DATA1.SALES.CUST == == == == == AGEINDEX is partitioned as follows: $DATA4.SALES.AGEINDEX $DATA5.SALES.AGEINDEX \NEWYORK.$DATA2.SALES.AGEINDEX \NEWYORK.$DATA3.SALES.
Parameters This diagram shows how to specify a parameter in a DML statement or SQLCI command file. For information about assigning values to parameters, see EXECUTE Statement on page E-7 or SET PARAM Command on page S-35.
Considerations—Parameters TYPE AS { { { { { { { DATETIME [start-dt TO] end-dt DATE TIME TIMESTAMP INTERVAL start-dt [ (start-field-precision) ] [ TO end-dt ] } } } } } } } tells SQL to expect the value entered for the parameter to be a value of the specified date-time or INTERVAL data type. (You cannot use TYPE AS to specify a character or numeric data type for a parameter. Use the CAST function instead.
Considerations—Parameters Assigning a value to the first occurrence of a parameter in the statement automatically assigns a value to the other occurrences also. For example, assume a statement uses five parameters—two named A, two unnamed, and one named B—ordered: ?A, ?, ?B, ?, ?A Executing the statement requires only four values, for example: EXECUTE USING 10, 20, 30, 40; because SQL assigns the first value (10) to each of the parameters named A.
Example—Parameters The data type is DATETIME if the parameter name is followed by a range of fields and start-field-precision is not specified or if the expression has any of these forms: parameter-name { + | - } interval-term interval-expression + parameter-name date-time-expression - parameter-name The data type is NUMERIC if the expression takes either of this forms: parameter-name { + | - } scalar-value { + | - } parameter-name Example—Parameters In this example, you can substitute different va
PARTITION Clause PARTITION Clause PARTITION is a clause on the ALTER INDEX, ALTER TABLE, CREATE INDEX, and CREATE TABLE statements that defines secondary partitions for a table or index. PARTITION ( partition [ , partition ] ... ) partition is: [\node.][$volume.][subvol.]object [| [| [| [| [| CATALOG catalog |] PHYSVOL volume-name |] EXTENT { size | ( pri-size[,sec-size] ) } |] MAXEXTENTS integer |] { FORMAT 1 | FORMAT 2 } |] [ FIRST KEY { value } ] { ( value [ , value ] ... ) } [\node.][$volume.
PARTITION Clause MAXEXTENTS integer specifies the MAXEXTENTS file attribute for the partition. For more information, see MAXEXTENTS File Attribute on page M-2. { FORMAT 1 | FORMAT 2 } indicates the format of the partition. The default partition format type for tables and indexes is based on the partition array value of the underlying table. For STANDARD and EXTENDED, the default format is 1. For FORMAT2ENABLED, the default format is 2.
Consideration—PARTITION Consideration—PARTITION Each partition you specify must follow the rules for partitions described in the entry PARTITIONS. Example—PARTITION This example shows a CREATE TABLE statement that uses the PARTITION clause: CREATE TABLE \SYS1.$VOL1.SALES.
Partitions Partitions A partition is the portion of a table or index that resides on a single disk volume. Each table or index consists of at least one partition. An “unpartitioned” table or index is a table or index that consists of exactly one partition. A “partitioned” table or index is a table or index that consists of more than one partition. A “primary partition” is the first partition in a partitioned table or index. Other partitions are called “secondary partitions.
PARTNS Table New partitions must comply with the limits on the number and size of partitions. For more information, see Limits on page L-6. You can partition tables of any file organization but you cannot partition a key-sequenced table that has a system-defined primary key (as opposed to a user-defined primary key) unless it also has a clustering key.
PERUSE Command of the table (the PARTITIONNAME column of the PARTNS table). You can register all partitions of a table on a node in a single catalog if you want, or put them in separate catalogs. Values in FIRSTKEY are in ASCII format, separated by commas. For example, a key composed of a character and an integer column might have a FIRSTKEY value such as: “A”,1234. Guardian names in the PARTNS table are fully qualified and use uppercase characters.
Plans Plans A plan (also called an execution plan or a query execution plan) is an execution method for a single compiled SQL statement. A plan captures both the semantics and execution characteristics of the statement. Compiled programs typically include many plans. Each plan might be operable or inoperable, optimal or not optimal, and valid or invalid. An operable plan is a plan that will give correct results for a given set of database tables.
POSITION Function POSITION Function The POSITION function searches for a given substring in a character string. If the substring is found, SQL returns the character position of the substring within the string.
Examples—POSITION Function If the length of substring is greater than the length of the character string, SQL returns 0. If character-string, substring, or occurrence is a null value, SQL returns a null value. The collating sequences of substring and character-string must be the same or comparable, or SQL returns an error. The character sets of substring and character-string must also be identical. To ignore case in the search, use the UPSHIFT function or a collation.
PREPARE Statement For more information about a specific predicate, see the entry for that predicate. PREPARE Statement PREPARE is a dynamic SQL statement and an SQLCI command that compiles an SQL statement for later execution with EXECUTE. In host programs, PREPARE also returns information to the SQLSA that you can use to declare an SQLDA for DESCRIBE and EXECUTE statements. (For information, see INCLUDE SQLDA Directive on page I-4 or the SQL/MP programming manual for your host language.
Considerations—PREPARE Cannot refer to host variables Cannot use an INTO clause if it is a SELECT Cannot be CLOSE, DECLARE CURSOR, DESCRIBE, DESCRIBE INPUT, EXECUTE, EXECUTE IMMEDIATE, FETCH, OPEN, PREPARE, or RELEASE Considerations—PREPARE If a PREPARE statement fails, any subsequent attempt to execute the named statement fails. Only the process that executes the PREPARE can execute the associated prepared statement.
Primary Keys +> & "FROM PERSNL.EMPLOYEE WHERE DEPTNUM = 1500)"; --- SQL command prepared. >> DISPLAY STATISTICS; This SQLCI example prepares an INSERT statement with parameters, then supplies parameter values with the EXECUTE: >> PREPARE EMPIN FROM "INSERT INTO PERSNL.EMPLOYEE" +> &" VALUES (?, ?, ?, ?, ?, ?); ---SQL command prepared.
Print Item Index Keys Syskeys (system-defined primary keys) User-Defined Keys (user-defined primary keys) Print Item A print item identifies an item to print in an SQLCI report writer report, optionally accompanied by instructions for formatting the item. A print item can generally be a column identifier for a column in the current SELECT list (a column name, column position number, alias, or detail alias), a literal, an arithmetic expression, or a report clause (for example, SKIP, SPACE, or TAB).
Operations That Invalidate a Program An invalid program requires either explicit or automatic recompilation to execute. An invalid program requires explicit recompilation to revalidate it. Operations That Invalidate a Program Copying the program file. If you copy a program file by using the FUP DUPLICATE command, the original file is unaffected, but the new file is invalid. For more information, see the SQL/MP Programming and Installation Guide. Binding the program file.
Preventing Program Invalidation Caused by DDL Operations ALTER TABLE...ADD PARTITION statement (including an underlying table of a protection or shorthand view used by the program) ALTER TABLE...ADD COLUMN statement (for more information, including restrictions, see the SQL/MP Installation and Management Guide.) ALTER TABLE statement to move or split partitions (including a simple move, one-way split, or two-way split) or change the type of partition array ALTER TABLE...
PROGRAMS Table the PROGRAMS catalog table. However, the operations listed in the subsections, Operations That Invalidate a Program on page P-29, and Preventing Program Invalidation Caused by DDL Operations on page P-29, invalidate specific execution plans within an unregistered program. For more information about program invalidation, see the SQL/MP Installation and Management Guide. PROGRAMS Table The PROGRAMS table is a catalog table that describes object programs that have been SQL-compiled.
Protection View Table P-2.
PURGE Command PURGE Command PURGE is an SQLCI utility that deletes SQL objects (except catalog tables and their indexes), SQL programs in Guardian files, and Enscribe files. For an SQL object, PURGE deletes the file that contains the object, the catalog entries for the object, and objects (but not programs) that depend upon the object. The local super ID (but not other users) can also use PURGE to delete shadow labels. You cannot PURGE constraints or catalogs. Use DROP instead.
Considerations—PURGE If SMF is installed on your node, qualified-fileset-list cannot specify any objects or files on a $*.ZYS*. subvolume.
Considerations—PURGE indexes that depend on the table, and all views that depend on the table except dependent shorthand views for which you lack purge authority. SQL invalidates the latter. To purge a protection view, all partitions of the view, all views and SQL program files that depend on the view, all partitions of the table that the view depends on, and all partitions of all indexes on that table must be available.
Examples—PURGE Examples—PURGE This example deletes all Enscribe files, SQL programs, and SQL objects (except for catalog table and indexes) on subvolume $VOL1.PERSNL. The exclamation point suppresses the PURGE confirmation prompt. >> PURGE $VOL1.PERSNL.* ! LISTALL; Depending on the contents of the subvolume, the response might look like this: TABLE $VOL1.PERSNL.DEPT PURGED TABLE $VOL1.PERSNL.EMPLOYEE PURGED TABLE $VOL1.PERSNL.
PURGEDATA Command PURGEDATA works on audited and unaudited files but—unlike most other operations on audited files—PURGEDATA cannot be used within a user-defined TMF transaction and cannot be rolled back. PURGEDATA qualified-fileset-list [| [,] ALLOWERRORS [ ON | OFF | num ] |] [| [,] [ NO ] LISTALL |] ; [| [,] PARTONLY |] qualified-fileset-list specifies the tables, partitions, and files to clear. When clearing an entire SQL table, PURGEDATA also clears data from all indexes defined on the table.
Considerations—PURGEDATA LISTALL is the default. If you specify NO LISTALL, PURGEDATA suppresses the display. PARTONLY specifies that data should be cleared from individual partitions included in qualified-fileset-list. An individual partition to be cleared cannot be part of an SQL table with dependent indexes. In addition, if the partition belongs to an SQL table with a relative or entry-sequenced file organization, it must be the last partition in the file.
Examples—PURGEDATA error 40 (The operation timed out) when the operation attempts to update file labels and catalog entries. If the PURGEDATA operation fails, PURGEDATA leaves the object or file marked corrupt. To clear the corrupt flag, correct whatever problem caused the operation to fail and repeat the PURGEDATA command. PURGEDATA does not apply to audited tables that reside on nodes running versions of SQL/MP software earlier than version 300.
Examples—PURGEDATA HP NonStop SQL/MP Reference Manual—523352-013 P-40
Q Qualified Fileset List A qualified fileset list specifies a set of objects and files for an SQLCI utility operation and optionally includes clauses that restrict the objects and files operated on based on attributes of the objects and files. { fileset-list [ restrictions ] { { ( fileset-list [ restrictions ] { [, fileset-list [ restrictions ] ] ... ) } } } } fileset-list is: { fileset } { ( fileset [ , fileset ] ...
Qualified Fileset List fileset is a set of objects and files specified as a Guardian name that optionally includes these wild-card characters in the volume, subvolume, or file ID portions of the name. ? Matches any single character. For example, TBL? matches TBL1 or TBLX but not TBL48. * Matches any 0 to 8 characters. For example, * matches any 0 to 8 character name; *VOL* matches NEWVOL, OLDVOL1, VOL45, and so forth.
Qualified Fileset List OWNER = user-id restricts operations to files in fileset owned by a Guardian user ID specified: { { { { group-name.user-name group-name.* group-number,user-number group-number,* } } } } For more information about user IDs, see Security on page S-11.
Qualified Fileset List file-attribute restricts operations to files in fileset that have one of these characteristics or file attributes: AUDITED INDEX ROLLFORWARDNEEDED BROKEN KEYSEQUENCED SAFEGUARD COLLATION LICENSED SECONDARY PARTITION CORRUPT OPEN SHORTHAND VIEW CRASHOPEN PARTITION SQLPROGRAM ENSCRIBE PRIMARY PARTITION SQL ENTRYSEQUENCED PROGID TABLE FORMAT1 PROTECTION VIEW UNSTRUCTURED FORMAT2 RELATIVE VIEW BROKEN and CRASHOPEN are states described under FILEINFO Command on
Examples—Qualified Fileset List name. (To determine the processing order to select the appropriate startfile, issue a FILENAMES command with the same qualified fileset list used for the utility.) startfile is a Guardian file name that can optionally include the wild-card character * as the subvolume or file id portion of the name. The file or set of files that startfile specifies must be a subset of the fileset to which the clause applies.
Quantified Predicate Quantified Predicate A quantified predicate compares the value of an expression to all, some, or any of the values in the result of a subquery. [ ANY ] expression comparison-operator [ ALL ] subquery [ SOME ] comparison-operator is one of the following: = <> < > <= >= Equal Not equal Less than Greater than Less than or equal to Greater than or equal to Considerations—Quantified Predicate QUANTIFIED is a comparison predicate.
Examples—Quantified Predicate This example finds all part numbers that are equal to any part number with more than five units in stock: PARTNUM = ANY (SELECT PARTNUM FROM ODETAIL WHERE QTY_ORDERED > 5) HP NonStop SQL/MP Reference Manual—523352-013 Q-7
Examples—Quantified Predicate HP NonStop SQL/MP Reference Manual—523352-013 Q-8
R RECLENGTH File Attribute RECLENGTH is a file attribute that specifies the number of physical bytes of space reserved for each row of a table. RECLENGTH applies only to relative tables. RECLENGTH length length is an integer that specifies the number of bytes to reserve for each record. RECLENGTH must be at least as great as the total length of all columns in the column list when the table is created and cannot exceed BLOCKSIZE minus 24.
REPORT FOOTING Command For more information, see Host Variables on page H-6 and the programming manual for your host language. REPORT FOOTING Command REPORT FOOTING is an SQLCI report writer command that specifies text for the end of a report. REPORT FOOTING print-item [,print-item]...[ CENTER ] ; print-item specifies an item to print in the report footing. The form for print-item is the same as in the DETAIL command, except that it cannot include the HEADING, NOHEAD, or NAME clause.
REPORT Option REPORT Option The REPORT option controls generation of EMS messages for an SQL operation started by a statement that includes the option. [ON ] REPORT [OFF ] [TO collector ] If you specify REPORT without an option, the default is ON. If you omit the REPORT clause entirely, the default is OFF. ON directs event messages for the operation to $0, the default EMS collector. OFF suppresses event messages for the operation. TO collector directs event messages for the operation to collector.
Examples—REPORT Option option does not specify an EMS collector, messages go to $0, the default EMS collector. CREATE INDEX $DK.REG1.IREL2 ON $DK.APPL.RECORDS(COL1, COL6) WITH SHARED ACCESS NAME CREATE_INDEX_IREL2 REPORT ON COMMIT WHEN READY ONCOMMITERROR COMMIT BY REQUEST; The sample report in Example R-1 is produced from EMS messages sent by the operation started with the CREATE INDEX statement in the previous example.
Examples—REPORT Option Example R-1. Report Example (page 2 of 2) \SA.$X314 TANDEM.SQLAUDIT.D30 000002 ** Audit Fixup Status ** Time since last status: 300 secs Distance to audit EOF : ? kbytes Records read : 3781 recs Records redone : 97 recs Read rate : 2370 bytes/sec Redo rate : 28 bytes/sec Progress rate : ? bytes/sec \SA.$X314 TANDEM.SQLAUDIT.
REPORT TITLE Command REPORT TITLE Command REPORT TITLE is an SQLCI report writer command that specifies text to print at the beginning of the report as the main title for the report. REPORT TITLE print-item [,print-item]...[ CENTER ] ; print-item specifies an item to print in the report title. The form for print-item is the same as for the DETAIL command, except that it cannot include the HEADING, NOHEAD, or NAME clause. For more information, see DETAIL Command on page D-47.
Report Writer S> REPORT TITLE "Summary of Orders:", SALESREP, +> CONCAT (FIRST_NAME STRIP, " ", LAST_NAME); S> DETAIL COL 1, COL 2, COL 3, COL 5; S> LIST ALL; Summary of Orders: 226 HEIDI WEIGL ORDERNUM ---------- ORDER_DATE ---------- DELIV_DATE ----------- CUSTNUM ------- 200490 300380 600480 880319 880319 880512 881101 880820 881010 123 123 3333 Report Writer The report writer is a component of SQLCI that enables you to produce formatted reports from rows returned by SELECT statements.
Report Writer Clauses AS Clause IF/THEN/ELSE Clause SKIP* AUDIT File Attribute NEED* SPACE Option* CONCAT Clause PAGE* TAB* Functions COMPUTE_TIMESTAMP Function LINE_NUMBER Function CURRENT_TIMESTAMP Function PAGE_NUMBER Function For more information, see the entries for specific commands, functions, clauses, or options. Clauses marked with an asterisk (*) are described in DETAIL Command on page D-47. For more information about using the report writer, see the SQL/MP Report Writer Guide.
Report Writer Table R-1. SQLCI Commands Used to Write Reports (page 2 of 2) OUT_REPORT Directs the output of a SELECT to a report file or closes the current report file PREPARE Compiles a command.
Report Writer Table R-2. Style and Layout Options for Reports Option What the Option Defines Default CENTER_REPORT Is report centered? OFF DATE_FORMAT Format for dates M2/D2/Y2 DECIMAL_POINT Symbol for decimal point Period (.
Reserved Words Table R-3. Report Writer Clauses (page 2 of 2) Clause What the Clause Specifies NEED Number of lines to keep together on page PAGE Advance to the next page and optionally start a new page-number sequence SKIP Number of lines before next item SPACE Number of blanks between items TAB Position of the next item on a line Table R-4.
RESET DEFINE Command Table R-5. Reserved Words (page 2 of 2) CHECK FROM LIKE CLOSE COMMIT SET GROUP COUNT CURRENT SELECT MAX SMALLINT MIN SOME HAVING SUM These words are reserved for the report writer.
Example—RESET DEFINE When you reset the CLASS attribute, you set the DEFINE class to MAP and establish a working attribute set consisting of the FILE attribute (set to its initial value). If you reset an attribute that does not belong to the current class, an error message appears. Example—RESET DEFINE This example shows the way RESET DEFINE changes the working attribute set. Initially, the working attribute set contains attributes for CLASS CATALOG. RESET DEFINE resets the SUBVOL attribute value.
Example—RESET LAYOUT Example—RESET LAYOUT This example resets three report writer layout options to their default values: >> RESET LAYOUT RIGHT_MARGIN, LINE_SPACING, PAGE_LENGTH; RESET PARAM Command RESET PARAM is an SQLCI command that clears the values of one or more parameters. RESET { [ PARAM ] param-name [ ,param-name] ... } ; { PARAM * } param-name specifies a parameter to clear. * clears the values of all parameters. For a description of parameters and their use, see SET PARAM Command on page S-35.
Examples—RESET PARAM parameter has no value after the EXECUTE command terminates because the value is assigned only temporarily. 4> PARAM SAL 140000 5> PARAM DEVICE^TYPE .2. SAL .140000. 6> SQLCI SQL Conversational Interface - T9191D20 - (01JUN93) COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987-1994 >> SHOW PARAM *; ?DEVICE^TYPE 2 ?SAL 140000 >> SET PARAM ?ENUM 557; ... >> SET PARAM ?STATE "CALIFORNIA", ?SAL 45000; >> SHOW PARAM *; ?ENUM 557 ?STATE CALIFORNIA ?SAL 45000 >> EXECUTE FINDSUP USING ?ENUM = 45; ..
RESET PREPARED Command RESET PREPARED Command RESET PREPARED is an SQLCI command that resets prepared commands. Resetting a prepared command is equivalent to deleting it. If you have prepared the maximum of 20 commands, you can reset a command you no longer need to use to prepare another command. RESET PREPARED { command-name [ ,command-name] ... } ; { * } command-name is the name you specified when you prepared the command. * resets all prepared commands.
RESET REPORT Command report-cmd is the name of a report-formatting command to reset to its default setting, or (if you specify column or alias with the command name) with which to delete the specified column name or alias. You can specify the report-cmd option only at the select-in-progress prompt, S>.
Consideration—RESET REPORT Consideration—RESET REPORT You cannot reset a command that defines an alias or detail alias (such as NAME or DETAIL) if other current report commands use the alias or detail alias, so the order in which you reset report commands is significant. For example, if a SUBTOTAL command refers to a detail alias, you must reset the SUBTOTAL command before you reset the DETAIL command. (Note that aliases are reset by RESET REPORT NAME, but detail aliases are reset by RESET REPORT DETAIL.
RESET SESSION Command RESET SESSION Command RESET SESSION is an SQLCI session command that resets SQLCI session options to default settings. RESET [ SESSION ] { option [, option ] ... } ; { * } The session options and their default settings are: AUTOWORK ON BREAK_KEY ON DISPLAY_ERROR ALL ERROR_ABORT OFF ERROR_TEXT DETAIL LIST_COUNT ALL MANDATORY_REPORT OFF STATISTICS OFF WARNINGS ON WRAP ON * resets all options to their default settings.
Example—RESET STYLE NEWLINE_CHAR / NULL_DISPLAY ? OVERFLOW_CHAR * ROWCOUNT ON SUBTOTAL_LABEL * TIME_FORMAT HP2:M2:S2 UNDERLINE_CHAR - VARCHAR_WIDTH 80 * resets all style options to their default settings. For more information, see the entries for individual options.
Consideration—RIGHT_MARGIN Consideration—RIGHT_MARGIN Report lines that extend beyond the margin continue on the next line. For default detail lines, the point at which the lines break depends on the LOGICAL_FOLDING option. For other lines, the point at which the lines break depends on the DETAIL command. For other lines, see LOGICAL_FOLDING Option on page L-53 or DETAIL Command on page D-47.
Considerations—ROLLBACK WORK Considerations—ROLLBACK WORK TMF transactions begin with BEGIN WORK and end with COMMIT WORK or ROLLBACK WORK. For more information, see TMF Transactions on page T-6 or BEGIN WORK Statement on page B-3. ROLLBACK WORK returns status information to the SQLCA, so you can use WHENEVER for processing related errors.
ROWCOUNT Option ROWCOUNT Option ROWCOUNT is an option of the SQLCI SET STYLE report writer command that causes SQLCI to generate or suppress a line that reports the number of rows returned as the result of a SELECT. ROWCOUNT { ON } { OFF } ON generates the row-count line. OFF suppresses the row-count line. The default is ON.
Example—ROWCOUNT HP NonStop SQL/MP Reference Manual—523352-013 R-24
S Sample Database To help users of NonStop SQL/MP become familiar with the product's features, HP includes a sample database and a sample application on the product site update tape (SUT). The sample database demonstrates the use of NonStop SQL/MP in a Pathway transaction processing environment. It includes several host language programs that use embedded SQL statements to access the sample database. Users can also access the sample database with SQLCI commands.
SAVE Command SAVE Command SAVE is an SQLCI command that saves in a file the values of one or more session attributes in command format. The file can be executed later with an OBEY command. SAVE {ALL } {ENV } {DEFINES } { } { { [ PARAM ] param-name [, param-name ] ... } } { { PARAM * } } { } { { [ LAYOUT ] layout-opt [,layout-opt ] ... } } { { LAYOUT * } } { } { [ SESSION ] { session-opt [, session-opt ]...}} { {* }} { } { { [ STYLE ] style-opt [, style-opt ] ...
SAVE Command save specific parameters or specify an asterisk (*) to save the names and values of all current parameters. { [ LAYOUT ] layout-opt [, layout-opt ] ... } { LAYOUT * } saves the layout options you specify; layout-opt is a single layout option. An asterisk (*) specifies all layout options. For more information about layout options, see SET LAYOUT Command on page S-34. [ SESSION ] { session-opt [, session-opt ] ...
Example—SAVE -number is a negative integer that indicates the position of a command in the history buffer relative to the current command. For more information, see HISTORY Command on page H-5. TO file specifies a disk file, process file, or terminal name. If the file does not exist, SQLCI creates an EDIT file. section-name is the simple name of a section header of the form ?SECTION section-name. SQLCI writes the section header in the line preceding the attribute values you are saving.
Search Conditions Search Conditions A search condition is a set of predicates (or other search conditions) combined with logical operators (AND, OR, or NOT) that specifies criteria for choosing rows from tables or views.
Considerations—Search Conditions Considerations—Search Conditions SQL evaluates search conditions in this order: first to last; predicates within parentheses; NOT, AND, and OR. Within a search condition, a reference to a column refers to the value of that column in the row evaluated by the search condition. If a search condition contains a predicate of the form expression comparison-operator subquery and the subquery returns no values, the predicate evaluates to null.
SECURE Command This example searches for values where supplier number in the SUPPLIER table equals supplier number in the PARTSUPP table, and part number is less than 3000 or equal to 7102: SUPPLIER.SUPPNUM = PARTSUPP.SUPPNUM AND (PARTNUM < 3000 OR PARTNUM = 7102) SECURE Command SECURE is an SQLCI utility that changes security, ownership, and some file attributes for tables, views, collations, SQL programs in Guardian files, and Enscribe files.
SECURE Command If you do not specify a security string, the security of files and objects remains unchanged. ALLOWERRORS [ ON | OFF | num ] specifies action when errors occur: ON Resecure all elements of the fileset list regardless of how many errors are encountered. OFF Stop the operation when an error occurs. num Resecure files and objects until the number of errors is greater than num. If you omit the ALLOWERRORS clause completely, the default is ALLOWERRORS OFF.
Considerations—SECURE Command PROGID [ ON | OFF ] determines the process accessor ID of a program file when the program executes. ON Set the process accessor ID to the Guardian user ID of the owner of the program. OFF Set the process accessor ID to the Guardian user ID of the user who runs the process. If you omit the PROGID option, the file attribute is not changed. PROGID ON is the default if you specify PROGID but omit ON and OFF.
Examples—SECURE Command You can press the Break key to interrupt the SECURE utility. SECURE reports the last object resecured. If a user-defined TMF transaction is not in progress, the changes made to the database before you pressed the Break key are committed and the change in progress at the time you press the key is also committed, although SECURE does not issue a message confirming the last change. If a user-defined transaction is in progress, the transaction is rolled back and all changes are undone.
SECURE File Attribute SECURE File Attribute SECURE is a Guardian file attribute that corresponds to the security string that controls Guardian file security for table, index, collation, and protection views. SECURE “rwep” "rwep" is a four-character string that specifies the Guardian read, write, execute, and purge security for an object or file. For more information, see Security on page S-11.
User IDs subsystem can secure SQL/MP objects at the volume or subvolume level and can secure all other Guardian files at the volume, subvolume, or file level. SQL programs in OSS files and other OSS files use OSS security, which differs from Guardian security.
Process Access IDs 255,255 Super ID number SUPER.SUPER Typical super ID name The super ID can act as the owner of any object or file on the node. Certain operations can be performed only by a user logged on with the super ID. Process Access IDs Each executing process on a system has a process access ID (PAID) that determines the SQL/MP objects and Guardian files the process can access. The process access ID is always a Guardian user ID.
File Ownership subsystem to set the PROGID attribute. NonStop SQL/MP stores the PROGID attribute of an SQL program in the PROGRAMS table of the catalog in which the program is registered and in the file label of the program itself. File Ownership Each SQL/MP object or Guardian file is owned by a single Guardian user ID. When an object or file is created, the owner is the Guardian user ID that corresponds to the process access ID of the process that created the file.
Authorization Requirements for SQL Statements In contrast, the security string “NGNU” specifies that any user on the network can read or execute the file (the “r” and “e” characters of the security string) but the generalized owner or a local user with a user ID that has the same Guardian security group as that of the owner can only write to the file (the “w” character of the security string). Only the generalized owner can purge the file (the “p” character of the security string).
Authorization Requirements for SQL Statements Table S-1.
Authorization Requirements for SQL Statements Table S-1. Authorization Requirements for SQL Statements (page 3 of 3) Statement Authority Required SELECT Read authority to the tables, protection views, and underlying tables of shorthand views referred to in the statement INVOKE Read authority to the catalogs that contain the object descriptions * All DDL statements require authority to read and write to any catalogs affected by the change in addition to any other requirements listed previously.
SELECT Statement SELECT Statement SELECT is a DML statement that retrieves values from tables and views. SELECT [ ALL | DISTINCT ] select-list [ INTO :host-variable [ , :host-variable ] ... ] FROM [| [| [| [| [| [| [| [| [| [| [| [| [| table-ref [ , table-ref ] ...
SELECT Statement [ ALL | DISTINCT ] specifies whether to retrieve all rows of the intermediate table described by the FROM clause or only rows that are not duplicates (DISTINCT rows). NULL values are considered equal for removing duplicates. The default is ALL. select-list specifies the columns to select from the intermediate table described by the FROM clause: List Item Columns of Intermediate Table Retrieved * All columns, including SYSKEYs for view corr.
SELECT Statement FROM table-ref [ , table-ref ] ... is a list of up to 16 tables, views, and join-tables (or equivalent DEFINEs), each optionally qualified by a correlation name, that specifies the contents of an intermediate table from which SQL retrieves the columns you specified in select-list. If you specify only one table-ref, the intermediate table consists of rows from that table, view, or join-table.
SELECT Statement HAVING search-cond specifies a search condition to apply to each row of the result table of the previous clause. The search condition is applied to each group, or (if there is no GROUP BY clause) to all the rows. In the search condition, you can specify any column as the argument of a function (for example, AVG (SALARY)). A column that is not in a function must be, however, a column in a GROUP BY clause or a column in a table or view specified in an outer query.
SELECT Statement The IN clause is not allowed for BROWSE ACCESS. In SHARE MODE it is ignored for cursor SELECT operations. If you omit the IN clause, SQL uses SHARE until an attempt is made to modify the data, escalates the lock to EXCLUSIVE. For more information, see Locking on page L-48. GROUP BY { colname | colnum } specifies columns of the result table from the preceding FROM or WHERE clause that define a set of groups in which each group consists of rows with identical values in the specified columns.
SELECT Statement COLLATE { collation | CHARACTER SET } specifies an alternate collating sequence that determines the ordering of rows in a column specified on a GROUP BY or ORDER BY clause, temporarily overriding the effect of any collation associated with the column as part of its table definition. collation is the name of an existing collation that specifies a collating sequence and uses the same character set as the associated column.
Considerations—SELECT The result of UNION is a table that contains rows belonging to either of the two tables. If you specify UNION ALL, the table contains all the rows retrieved by each SELECT statement; otherwise, duplicate rows are removed. The number of columns in the table is the same as the number of columns in each select-list. The column names in the table are the same as the corresponding names in the select-list of the leftmost SELECT statement.
Considerations for UNION A grouped view is a view defined with a CREATE VIEW AS clause that contains a GROUP BY or HAVING clause that is not in a subquery, contains an aggregate function in the select list, or refers to a grouped view in the FROM clause. A shorthand view whose definition is based on a union of SELECT statements or that contains a LEFT JOIN operator, cannot participate in another join operation.
ORDER BY clause and UNION operator If both columns are of exact numeric data types, RESULT contains an exact numeric value whose precision and scale are equal to the greater of the two contributing columns. If both columns are of approximate (floating point) numeric data types, RESULT contains an approximate numeric value whose precision is equal to the greater of the two contributing columns.
GROUP BY Clause, HAVING Clause, and the UNION Operator This example on SELECT is also incorrect: SELECT A FROM T1 UNION (SELECT B FROM T2 ORDER BY A) Because the subquery (SELECT B FROM T2...) is processed first, the ORDER BY clause does not follow the final SELECT.
Examples—SELECT This SQLCI example displays selected rows grouped by job code in ascending order. The select-list contains only grouping columns and functions because each group results in one row. >> SELECT JOBCODE, AVG (SALARY) +> FROM PERSNL.EMPLOYEE +> WHERE JOBCODE > 500 AND DEPTNUM <= 3000 +> GROUP BY JOBCODE +> ORDER BY JOBCODE; JOBCODE EXPR ------- --------------------600 29000.00 900 27125.00 --- 2 row(s) selected.
Examples—SELECT 568 300 39500 900 MANAGER 2. Drop rows with unequal job codes. EMPLOYEE Table EMPNUM ... JOBCODE 1 100 . 207 420 . 568 300 ... JOB Table SALARY JOBCODE 175500 100 . . 33000 420 . . 39500 300 JOBDESC MANAGER ENGINEER SALESREP 3. Drop rows with job codes not 900, 300 or 420. EMPLOYEE Table EMPNUM ... JOBCODE 75 300 . 178 900 . 207 420 . 568 300 ... JOB Table SALARY JOBCODE 32000 300 . . 28000 900 . . 33000 420 . . 39500 300 JOBDESC SALESREP SECRETARY ENGINEER SALESREP 4.
Examples—SELECT +> WHERE O.PARTNUM = P.PARTNUM AND ORDERNUM IN +> (SELECT ORDERNUM FROM ORDERS O, CUSTOMER C +> WHERE O.CUSTNUM = C.CUSTNUM AND STATE = "CALIFORNIA") +> GROUP BY ORDERNUM; >> VOLUME $VOL1.SALES; >> SELECT ORDERNUM, SUM (QTY_ORDERED * PRICE) +> FROM PARTS P, ODETAIL O +> WHERE O.PARTNUM = P.
Examples—SELECT N3 33.3 --- 3 row(s) selected. The query joins table T with itself; X and Y are correlation names so that a query can compare one row of the table with every other row. The GROUP BY Y.C clause produces sets of n*v values for each distinct C value in which these are true: n is the number of rows in the table. v is the number of occurrences of a particular C value. COUNT(DISTINCT X.I) is n for each group.
SERIALWRITES File Attribute SERIALWRITES File Attribute SERIALWRITES is a Guardian file attribute that specifies whether to write data serially or in parallel to the two disk devices that make up a mirrored volume. SERIALWRITES applies to key-sequenced, relative, and entry-sequenced tables and to indexes. { SERIALWRITES | NO SERIALWRITES } SERIALWRITES Selects serial mirror writes NO SERIALWRITES Selects parallel mirror writes The table default is NO SERIALWRITES.
Considerations—SET DEFINE attr value is the name and value of a DEFINE attribute to add to the working attribute set. For information about DEFINE attributes, see DEFINEs on page D-27. Considerations—SET DEFINE The working attribute set consists of values for the attributes of the current class. Only one class of attributes can be in the working set at one time.
SET DEFMODE Command SET DEFMODE Command SET DEFMODE is an SQLCI command that enables or disables the use of DEFINEs in the current SQLCI session. (SET DEFMODE is similar to the TACL command SET DEFMODE.) SET DEFMODE { ON } ; { OFF } ON enables the use of DEFINEs. If DEFMODE is ON, you can execute commands that contain DEFINE names and you can create, modify, or delete DEFINEs, display information about DEFINES, and propagate existing DEFINEs to any processes you start from the SQLCI session.
Example—SET LAYOUT You cannot specify the same option more than once in a single SET LAYOUT command. For more information about a specific option, see the entry for that option. Example—SET LAYOUT This example sets the left margin for reports at byte position eight and also sets double spacing: >> SET LAYOUT LEFT_MARGIN 8, LINE_SPACING 2; SET PARAM Command SET PARAM is an SQLCI command that sets values for parameters in your SQLCI session.
Considerations—SET PARAM COMPUTE_TIMESTAMP (date) is the Julian timestamp for the date and time you specify in date in this form: {mm/dd/yyyy } {mm/dd/yyyy hh:nn:ss:mss:uss} yyyy Year, from 1 through 9999, 1 to 4 digits mm Month, from 1 through 12, 1 to 2 digits dd Day, from 1 through 31, 1 to 2 digits hh Hour, from 0 through 23, 1 to 2 digits nn Minute, from 0 through 59, 1 to 2 digits ss Second, from 0 through 59, 1 to 2 digits mss Millisecond, from 0 through 999, 1 to 3 digits uss Micros
Examples—SET PARAM Examples—SET PARAM In this example, the SELECT statement in the FINDSUP2 file finds suppliers of a specified part. The suppliers are located in a specified state. VOLUME $VOL1.INVENT; SELECT S.SUPPNUM, SUPPNAME FROM SUPPLIER S, PARTSUPP WHERE S.SUPPNUM = PARTSUPP.
Examples—SET PARAM Before executing the INSORD OBEY command file, you change the value of the order number parameter and set values for the other parameters of the INSERT command: >> SET PARAM ?ONUM 600480, ?TODAY CURRENT_TIMESTAMP, +> ?DDATE COMPUTE_TIMESTAMP (7/5/1988), ?REP 221; >> OBEY INSORD; These values are inserted in ORDERS: ORDERNUM 600480 ORDER_DATE (timestamp for current date and time) DELIV_DATE (timestamp for 7/5/88) SALESREP 221 CUSTNUM 7654 After setting a different order number a
SET SESSION Command SET SESSION Command SET SESSION is an SQLCI command that sets session options for your SQLCI session. SET [ SESSION ] option [ , option ] ...
SET SESSION Command BREAK_KEY { OFF | ON } specifies the SQLCI action when you press the Break key while executing an SQL command or an OBEY command. OFF Return control to the previous Break key owner (type PAUSE to resume SQLCI later) ON Retain control (the IN file must be a terminal) Use OFF if you want to prevent someone from interrupting commands at your terminal. The default when you start SQLCI is BREAK_KEY ON.
SET SESSION Command ERROR_TEXT { DETAIL | BRIEF } specifies the default information SQLCI displays when you enter an ERROR command. You can override the ERROR_TEXT option with the ERROR command. DETAIL Display error text, cause, effect, and suggestions for recovery BRIEF Display only the error text The default when you start SQLCI is ERROR_TEXT DETAIL. LIST_COUNT { number | ALL } specifies how many rows of data from a SELECT command to display before a pause.
Considerations—SET SESSION WARNINGS { OFF | ON } specifies whether to display warning messages. (SQLCI always displays error messages.) OFF Do not display warning messages ON Display warning messages The default when you start SQLCI is WARNINGS ON. WRAP { OFF | ON } specifies whether SQLCI output lines that exceed the output device width are truncated or continued on the next line.
Considerations—SET SESSION SQLCI responds to the Break key based on the setting of the BREAK_KEY session option: BREAK_KEY ON BREAK_KEY OFF SQL commands Command rolls back; SQLCI prompts. Control returns to previous owner. OBEY command Command terminates; SQLCI prompts. Control returns to previous owner. Executing Command BREAK_KEY ON or OFF FC command Command terminates and SQLCI prompts; BREAK_KEY has no effect.
Examples—SET SESSION where file is an interactive device; typically a terminal (device type 6) or a process (device type 0) SQLCI /INLINE/ SQLCI /INV variable, OUTV variable/ The use of TACL INLINE and INV/OUTV are special cases of a process device type classification. Non-interactive processing includes: SQLCI /IN file,...
SET STYLE Command To direct SQLCI to display ten rows at a time, enter: >> SET LIST_COUNT 10; >> To enable the display of statistics and disable continuation of text to the next line, enter: >> SET STATISTICS ON, WRAP OFF; SET STYLE Command SET STYLE is an SQLCI report writer command that sets style options. Style options affect the appearance of specific report items, such as underlines, headings, and date and time formats. SET [ STYLE ] option [ , option ] ...
Consideration—SET STYLE For information about a specific option, see the entry for that option. Consideration—SET STYLE You cannot specify the same option more than once in a single SET STYLE command. Example—SET STYLE This example activates report headings and specifies an asterisk as the character to display for null values: >> SET STYLE HEADINGS ON, NULL_DISPLAY "*"; SETSCALE Function SETSCALE is a function that specifies the scale of a host variable to SQL.
Examples—SETSCALE For example, if the program is representing a price of $123.45, the program should assign 12345 to host-var and use SETSCALE to specify a scale of two. If the value is being retrieved from the database through a SELECT operation, SQL assigns a value that allows for the scale to host-var. For example, if SQL is storing 123.45, the value 12345 is returned to host-var when the program specifies SETSCALE with a scale of two in the SELECT statement.
Shorthand View Shorthand View A shorthand view is a view derived from one or more tables or views by joining tables or views, by projecting columns, by restricting rows, or by a combination of these actions. Shorthand views can be read, but cannot be updated or secured. SHOW CONTROL Command SHOW CONTROL is an SQLCI command that displays the current values of options set by the CONTROL EXECUTOR, CONTROL QUERY, and CONTROL TABLE statements.
Consideration—SHOW DEFINE Consideration—SHOW DEFINE If you do not specify an attribute or an asterisk, SHOW DEFINE displays all attributes with a current value and warns you to specify values for required attributes without current values. Attributes whose values violate consistency rules are flagged with an asterisk (*).
SHOW LAYOUT Command SHOW LAYOUT Command SHOW LAYOUT is an SQLCI report writer command that displays the values of the current layout options. Layout options affect the way a report appears on a terminal screen or printed page. SHOW { [ LAYOUT ] option [, option] ... } ; { LAYOUT * } option is: { { { { { { { { CENTER_REPORT LEFT_MARGIN LOGICAL_FOLDING PAGE_COUNT PAGE_LENGTH RIGHT_MARGIN SPACE WINDOW } } } } } } } } * displays all layout options.
Examples—SHOW PARAM Examples—SHOW PARAM This example displays two parameter values: >> SHOW PARAM ?PNUM, ?ST; ?PNUM 4103 ?ST TEXAS This example displays the value of every currently defined parameter: >> SHOW PARAM *; SHOW PREPARED Command SHOW PREPARED is an SQLCI command that displays prepared commands. SHOW PREPARED { command-name [, command-name ] ... } ; { * } command-name is the name you specified for the command when you prepared it. * displays all the currently prepared commands.
SHOW REPORT Command SHOW REPORT Command SHOW REPORT is an SQLCI report writer command that displays the current SELECT statement and the current report formatting commands. SHOW REPORT { { command } [, command ] ... } ; { * } command is: { { { { { { { { { { { { BREAK BREAK FOOTING BREAK TITLE DETAIL NAME PAGE FOOTING PAGE TITLE REPORT FOOTING REPORT TITLE SELECT SUBTOTAL TOTAL } } } } } } } } } } } } * displays the SELECT and all report formatting commands.
SHOW SESSION Command SHOW SESSION Command SHOW SESSION is an SQLCI command that displays the values of the current session options. SHOW [ SESSION ] { option [, option ] ... } ; { * } option is: { { { { { { { { { { AUTOWORK BREAK_KEY DISPLAY_ERROR ERROR_ABORT ERROR_TEXT LIST_COUNT MANDATORY_REPORT STATISTICS WARNINGS WRAP } } } } } } } } } } * displays all session options. For an explanation of each session option, see SET SESSION Command on page S-39.
SHOW STYLE Command SHOW STYLE Command SHOW STYLE is an SQLCI report writer command that displays the current style options. Style options affect the appearance of specific report items, such as underlines, headings, and date and time formats. SHOW { [ STYLE ] option [, option ] ...
General Rules for Similarity Similarity checks work by comparing information stored in a program file at explicit compilation time with information current at recompilation time. During an explicit recompilation, the SQL compiler uses similarity checks to differentiate between invalid and inoperable plans if you specify COMPILE INOPERABLE PLANS.
Similarity Between Tables Data within the tables Column headings and help text Catalog where the table is registered Comments on columns, constraints, indexes, or tables Number of partitions and partitioning key ranges Key tags (or values) for indexes Creation and redefinition timestamps These file attributes can differ: ALLOCATE LOCKLENGTH SECURE AUDITCOMPRESS MAXEXTENTS SERIALWRITES BUFFERED NOPURGEUNTIL TABLECODE CLEARONPURGE OWNER VERIFIEDWRITES EXTENT (primary and second
Similarity Between Collations Tables referenced in the position of tablea in this statement can be similar, but tables referenced in the position of tableb in this statements can never be similar: SELECT columnx FROM tablea WHERE EXISTS SELECT [DISTINCT] * FROM tableb; SELECT tableb.*, tablea.x FROM tableb, tablea; Similarity Between Collations Collations that are explicitly referenced in an SQL query are similar only if they are equal.
SPACE Option SPACE Option SPACE is an option of the SQLCI report writer SET LAYOUT command that specifies the default number of spaces between columns of report print items. Each space occupies a single byte position, regardless of the character set in use. SPACE number number is an integer in the range 0 through 255 that specifies the default number of spaces between print items. The default is 2.
SQL Directive This example uses the SPACE layout option to change the default number of spaces between columns. The content of the report (not the spacing) is the same as in the previous example. >> SET LAYOUT SPACE 5; >> SET LIST_COUNT 0; >> SELECT * FROM PERSNL.JOB; S> LIST NEXT 4; JOBCODE JOBDESC -----------------------100 MANAGER 200 PRODUCTION SUPV 250 ASSEMBLER 300 SALESREP SQL Directive The SQL directive indicates to a host language compiler that a program contains SQL statements.
SQLCI SQLCI SQLCI, the SQL/MP conversational interface, executes commands and SQL statements entered interactively at a terminal or through a command file. SQLCI is useful for ad hoc queries and reports, for testing SQL statements before adding them to programs, for comparing the relative efficiency of different versions of a query, and for database administration tasks such as defining and modifying the structure of the database.
The SQLCI Command Within an SQLCI session, the tasks you perform are affected by these session attributes: Environment The files to use for SQLCI output, logging, and reports; the default node, volume, subvolume, and catalog DEFINEs DEFINEs that associate an actual file or object with a logical name used in a command or statement Session Options Options set by the SET SESSION command that control the response to the Break key, automatic creation of TMF transactions, the amount of SELECT information di
Considerations—SQLCI OUT list-file specifies the file to which SQLCI writes or displays prompts, messages, listings of data, and so forth; list-file is a valid Guardian name for a local or remote file. You can specify a disk file, terminal, process, magnetic tape, line printer, or spooler collector. If you do not specify an OUT file, SQLCI uses the same OUT file as the command interpreter—usually the home terminal. If an existing file contains data, SQLCI appends its output to the existing information.
Example—SQLCI You can set up your SQLCI session environment by including the sqlci-command option when you start SQLCI. For example, you might create an OBEY command file named PROFILE that contains these commands: VOLUME $VOL1.SALES; CATALOG SALES; SET SESSION AUTOWORK OFF, BREAK_KEY OFF; LOG SQLCILOG; Then you can prepare your environment quickly by entering the: SQLCI OBEY PROFILE; This command starts an SQLCI session, sets the current default volume and subvolume to $VOL1.
SQLCI Commands WRITER. SQL statements—most of which you can use in SQLCI—are summarized separately in the entry STATEMENTS.) Table S-2.
SQLCI Commands Table S-2.
SQLCI Commands Table S-2.
SQLCODE Table S-2.
Exceptions to Conformance With Entry Level SQL 1992 NonStop SQL/MP does not have the concept of a catalog or schema equivalent to those of ANSI/ISO SQL. However, the SQL/MP concept of a catalog is similar to that of an ANSI/ISO SQL schema in many respects. SQL/MP table, view, and collation names differ from table, view, and collation names in ANSI/ISO SQL. ANSI/ISO SQL table, view, and collation names are of the form: catalog.schema.
Exceptions to Conformance With Entry Level SQL 1992 ANSI/ISO SQL provides both statement atomicity (all changes made during a multirecord operation are canceled if an error occurs during the operation) and implicit transaction beginnings. In NonStop SQL/MP, audited tables have equivalent protection for a TMF transaction but not for a single statement. You must explicitly begin each transaction within a program, and you must explicitly begin any multistatement transactions entered through SQLCI.
SQL/MP Features From Intermediate Level SQL 1992 SQL/MP Features From Intermediate Level SQL 1992 NonStop SQL/MP includes these features from Intermediate Level ANSI/ISO SQL, although the specific implementation of the features does not always conform exactly to the implementation described in the ANSI/ISO standard: DATETIME, NATIONAL CHARACTER, and VARCHAR data types The ability to add and drop constraints (ANSI/ISO SQL provides ALTER TABLE ADD CONSTRAINT and ALTER TABLE DROP CONSTRAINT statements.
SQL/MP Features From Full Level SQL 1992 NonStop SQL/MP has access options that are similar to ANSI/ISO Isolation Levels. You can set the access option for an individual DML statement (but not for an entire transaction) to BROWSE, STABLE, or REPEATABLE. The default, STABLE, is equivalent to the ANSI/ISO Isolation Level READ COMMITTED. Note that what SQL/MP calls access options are not related to the ANSI/ISO Access Mode for a transaction. NonStop SQL/MP has no equivalent for Access Mode.
SQL/MP Extensions to SQL 1992 CREATE INDEX ALTER {TABLE | INDEX | COLLATION | PROGRAM | VIEW} DROP {INDEX | PROGRAM} COMMENT ON HELP TEXT UPDATE STATISTICS Additional DCL statements and data control directives: LOCK TABLE UNLOCK TABLE FREE RESOURCES CONTROL TABLE CONTROL EXECUTOR CONTROL QUERY Additional functions: CONVERTTIMESTAMP DATEFORMAT DAYOFWEEK EXTEND JULIANTIMESTAMP SETSCALE UPSHIFT Predicates can have multiple values (for example, a, b
Statements Statements SQL/MP statements define SQL objects and catalogs, manipulate data within those objects and catalogs, and control various aspects of the processes that perform the data definition and manipulation you specify in the statements. A single SQL/MP statement can contain up to 32,767 single-byte characters, including spaces. A tab is considered the same as a space in SQL/MP statements. You can enter and execute most SQL/MP statements either from SQLCI or from an embedded SQL program.
Statements Table S-3. Summary of SQL Statements Command Action ALTER PROGRAM Statement Renames or alters security of an SQL program in a Guardian file ALTER TABLE Statement Renames, alters security or file attributes, or enables or disables similarity checks for a table. Also adds columns to a table and adds, drops, or moves partitions of a table. ALTER VIEW Statement Renames, alters security, or enables or disables similarity checks for a view. Also adds columns to a view.
Statements Table S-3.
Statements Table S-3.
Static SQL Static SQL Table S-4. Summary of SQL Statements Command Action * Can be used only in program Static SQL is a form of embedded SQL in which SQL statements are coded directly into a host language source program. Static SQL statements begin with EXEC SQL and end with one of these statement terminators: ; Occurs in C, Pascal, and TAL programs END-EXEC.
Statistics Statistics SQL has an UPDATE STATISTICS statement you can use to collect and save statistics on columns and tables. The SQL compiler uses these statistics to determine the selectivity of predicates, indexes, and tables. Because selectivity directly influences the cost of access plans and regular collection of statistics, it increases the likelihood that SQL will choose efficient access plans.
Considerations—SMF Considerations—SMF Most SQL CREATE and ALTER statements have either syntax changes or considerations for SMF. Syntax changes consist of a new option, PHYSVOL, which allows you to place an SQL object on a particular physical volume associated with the specified virtual volume. Some SQL/MP utility commands are also affected, as are temporary table and file placement.
String Functions String Functions You can use string functions in expressions that involve columns defined with character data types. You can use a string function anywhere an arithmetic expression is allowed. NonStop SQL/MP provides these string functions: SUBSTRING Extracts a substring from a given string. POSITION Searches for a given character pattern in a character string. If the pattern is found, SQL returns the position of the pattern.
Considerations—String Literals _UNKNOWN specifies that data in the string literal belongs to an unknown character set. Specifying UNKNOWN is equivalent to omitting the character set specification. NonStop SQL/MP handles the data as if it were 8-bit data. N associates the system default multibyte character set with the string literal. (Kanji is the standard system default multibyte character set, but the default can be different at your site.
Examples—String Literals Examples—String Literals This string literals do not have an associated character set: "This is an ordinary literal." "This literal contains "" a quotation mark." "This SQLCI literal is" & " in three parts, " & "specified over three lines" '1234.56' _UNKNOWN"abc^&*" This string literals have associated character sets. The first is associated with a single-byte character set, and the second and third are associated with doublebyte character sets.
Considerations—Subqueries Considerations—Subqueries A SELECT statement that contains a subquery is called an outer query. The subquery within the SELECT is called an inner query. The differences between a SELECT statement and a subquery are: A subquery is always enclosed in parentheses. The INTO clause of a SELECT statement cannot be associated with a subquery in a cursor declaration. Therefore, the subquery cannot be used to retrieve values for host variables.
SUBSTRING Function Each time an outer query selects and evaluates a row, the outer reference is visible as a new value to the correlated subquery. The correlated subquery operates on the new value to test its own search condition, so the correlated subquery executes whenever the outer query selects a new row, which leads to reduced performance.
Considerations—SUBSTRING Function substring-length specifies the number of characters to extract from character-string. substring-length must be a value of exact numeric data type with a scale of zero; otherwise, SQL returns an error. substring-length must be greater than or equal to zero. Considerations—SUBSTRING Function The data types of substring-length and start-position must be numeric; otherwise, SQL returns an error.
SUBTOTAL Command This example returns “”: SUBSTRING ("Robert John Smith" FROM -5 FOR 2) This example returns “Ro”: SUBSTRING ("Robert John Smith" FROM -2 FOR 5) This example produces an empty string, “”, which is different than a null value for the result: SUBSTRING("Robert John Smith" FROM 8 FOR 0) SUBTOTAL Command SUBTOTAL is an SQLCI report writer command that specifies columns to subtotal and when to print the subtotals. SUBTOTAL returns you to the first SELECT output row.
Examples—SUBTOTAL enough room for the subtotal. If the subtotal is too large for its display format, the field is filled instead with overflow characters.) Subtotals print on three lines: the first line contains underline characters (see UNDERLINE_CHAR Option on page U-1), the second line contains the subtotal value, and the third line is blank. To identify the break group for the subtotal, the report writer prints a subtotal label (see SUBTOTAL_LABEL Option on page S-88) under the break column.
SUBTOTAL_LABEL Option The report looks like this: DEPTNUM ------1000 JOBCODE ------100 * 500 * * 1500 100 * 600 * 900 * * SALARY --------------137000.10 --------------137000.10 25000.75 29000.00 50000.00 --------------104000.75 --------------260000.85 90000.00 --------------90000.00 26000.00 32000.00 --------------58000.00 17000.00 --------------17000.00 --------------165000.00 BONUS -----------3425.00 -----------3425.00 625.02 725.00 1250.00 -----------2600.02 -----------6500.02 2250.
Example—SUBTOTAL_LABEL Example—SUBTOTAL_LABEL This command defines a subtotal label: >> SET STYLE SUBTOTAL_LABEL "@"; SUM Function SUM is a function that computes the sum of a set of numbers. The type of the result depends on the type of the argument. If the argument is an exact numeric type, the result is LARGEINT. If the argument type is FLOAT, REAL, or DOUBLE PRECISION, the result is DOUBLE PRECISION. The scale of the result is the same as the scale of the argument.
Example—SUM A host variable that receives the result of the SUM function must have an indicator variable to handle a possible null value. (For more information about using indicator variables, see the SQL/MP programming manual for your host language.) Example—SUM To compute the total value of parts in the current inventory (the sum of each value in the PRICE column multiplied by the corresponding value in the QTY_AVAILABLE column), type this: >> SELECT SUM (PRICE * QTY_AVAILABLE) FROM SALES.
Syskeys When you insert a record in a table stored in an entry-sequenced file or in a key-sequenced file with a SYSKEY column, the file system automatically generates a value for the SYSKEY column. You cannot supply the value. When you insert a record in a table stored in a relative file, you can specify a record number for the SYSKEY value. If you do not specify a value, the file system supplies a record number. The SYSKEY column cannot contain a null value.
System Catalog System Catalog Each node on a network has one special catalog called the system catalog. The system catalog contains the same tables as other catalogs on the node, but includes one additional table, the CATALOGS table, that lists all catalogs on the node. For more information, see CATALOGS Table on page C-11. The system catalog is established during the installation of NonStop SQL/MP.
SYSTEM Command SYSTEM Command SYSTEM is an SQLCI session command that selects a node to be the current default node for the SQLCI session. SYSTEM [ \node ] ; node is the name of a node to be the current default.If you omit node, SQL uses the node on which SQLCI is executing. Considerations—SYSTEM The default stays in effect only for the SQLCI session. When you return to TACL, the TACL default is used.
System DEFINEs System DEFINEs A system DEFINE is a DEFINE used in HP NonStop system software to identify system defaults or configuration information. Each system DEFINE name begins with an equal sign and an underscore (=_). This special prefix is reserved for system DEFINE names. (Do not create DEFINE names that begin with an equal sign and an underscore unless specifically directed to do so in HP documentation.
T TABLECODE File Attribute TABLECODE is a file attribute that assigns a numeric code to a table, index, or collation. TABLECODE applies to key-sequenced, relative, and entry-sequenced files and to indexes. A table code is a numeric code chosen by the user to categorize files. The table code appears in the TABLES and INDEXES catalog tables. In output from the FILEINFO command and in contexts other than SQL, a table code is referred to as a file code.
TABLES Table By default, SQL automatically opens partitions of tables and indexes as they are needed. You can, however, use the CONTROL TABLE statement with the OPEN ALL option to open all indexes and base partitions of a table the first time a partition is accessed. Note. Use the CONTROL TABLE OPEN ALL statement with the OPEN ALL option only if all these are true: All open activities must occur when the program starts (add a dummy call to the cursor during initialization).
TEDIT Command Table T-1. The TABLES Table (page 2 of 2) Column Name Data Type Description 10 SECURITYMODE CHAR(1) S if Safeguard security G if Guardian security 11 OBJECTVERSION SMALLINT UNSIGNED Version of table, view, or collation 12 SIMILARITYCHECK VARCHAR(3 0) ENABLED if similarity check is allowed, DISABLED if not * Indicates primary key The columns TABLENAME through SECURITYVECTOR (1 through 9) were created in version 1.
Temporary Tables Temporary Tables SQL creates temporary tables as needed according to these rules: If a query involves a join operation, SQL creates a temporary table on the volume on which the outermost table in the join resides. If the table is partitioned, SQL creates a temporary table on the volume on which the specified partition resides. If a query involves a hash join or a hash grouping, SQL creates temporary files on the program swap volume.
TIME Data Type An example of this format is: 3:45:20:16:033 TIME Data Type Data of type TIME represents a time of day according to a 24-hour clock. Values of data type TIME are equivalent to values of data type DATETIME declared: DATETIME HOUR TO SECOND For more information, see DATETIME Data Type on page D-15. Example—TIME Data Type These literals are of data type TIME in (respectively) default, USA, and European format: TIME "13:40:05" TIME "1:40:05 PM" TIME "13.40.
TMF Transactions TMF Transactions The TMF subsystem simplifies the task of maintaining data consistency for a distributed database being updated by concurrent transactions. A TMF transaction (a set of database changes that must be completed as a group) is the basic recoverable unit if a failure or transaction interruption occurs. TMF transactions can be defined during an SQLCI session or in a host program. The typical order of events is: 1. Transaction is started. 2. Database changes are made. 3.
Rules for DDL and DML Statements To ensure that several statements either execute successfully or not at all, you can define one transaction consisting of several statements by using the BEGIN WORK and COMMIT WORK statements. You can abort a transaction with the ROLLBACK WORK statement. COMMIT WORK and ROLLBACK WORK perform the FREE RESOURCES operation along with transaction control.
Rules for SQLCI Rules for SQLCI In SQLCI, you do not have to define your own TMF transactions or directly secure locks on data. By setting the AUTOWORK session option, you can specify system-defined transactions or user-defined transactions. AUTOWORK ON If you set AUTOWORK to ON, all data in SQL operations is automatically accessed within a TMF transaction. By default, SQL obtains locks to guarantee a specific level of data consistency.
TOTAL Command DDL operation terminates with an error and the operation was partially performed, the system automatically aborts the user-defined TMF transaction. DDL statements that operate on nonaudited tables cannot execute when a user-defined TMF transaction is in progress. A DML statement access option should provide transaction consistency and concurrency appropriate for your application and the environment in which it runs.
Examples—TOTAL Totals require three report lines: two for underline characters (see UNDERLINE_CHAR Option on page U-1) and one for the totals. In calculating totals, the report writer uses the maximum format for the item's data type and the same scale as the item to be totaled. In unusual cases (such as when an expression contains an item multiplied by an extremely small fractional value), this strategy can cause numeric overflow. Specifying small numeric values in exponential notation (for example, .
TRANSIDS Table TRANSIDS Table The TRANSIDS table is a catalog table used to prevent multiple DDL operations from being executed on the same catalog at the same time under the same TMF transaction. Table T-2 describes the contents of the TRANSIDS table. The TRANSIDS table was created in version 1. Table T-2.
Consideration—TRIM Function trim-char specifies the character to be trimmed from the string. The data type of trim-char must be CHARACTER with a maximum length of 1. If you omit trim-char, SQL trims blanks (“ “) from the string. character-string specifies the string from which to trim characters. Consideration—TRIM Function The result is always of type VARCHAR, with the same collating attributes as the source character-string.
U UNDERLINE_CHAR Option UNDERLINE_CHAR is an option of the SQLCI report writer SET STYLE command that specifies the character to use for underlining. Underline characters print below headings and above subtotals and totals. UNDERLINE_CHAR "character" character is a printable, single-byte character to use for underlining. The default is (hyphen).
Examples—UNLOCK TABLE When you specify the default value, TABLELOCK ENABLE, the executor can determine at run time whether a table lock is necessary. This strategy increases concurrency for statements that do not require a table lock, such as those statements that need only a few row locks. A host program cannot execute an UNLOCK TABLE statement if the program has a cursor open on the table or view with STABLE or REPEATABLE access.
UPDATE Statement UPDATE Statement UPDATE is a DML statement that updates rows of a table or protection view. UPDATE { name } SET col = exp [, col = exp ] ... [ [ [ [ [ [ [| WHERE search-cond |] [| |] [| [FOR] {STABLE } ACCESS |] [| {REPEATABLE} |] WHERE CURRENT OF cursor ] ] ] ] ] ] name is the name (or an equivalent DEFINE) of the table or protection view to update. name cannot be the name of a catalog table. col is the name of the column to update.
Considerations—UPDATE The default is REPEATABLE. For a detailed discussion of both STABLE and REPEATABLE, see Access Options on page A-1. WHERE CURRENT OF cursor (for use in programs only) specifies a host-program cursor to identify the row to be updated. The cursor must be positioned at a single row to be updated and not between rows. For static SQL programs, each column to be updated must appear in the FOR UPDATE clause of the cursor declaration.
Considerations—UPDATE The optimizer chooses the alternate index as the access path. The index columns in the equal-predicate are not changed by the update. Another index column of the same index is updated to a higher value (if the column is stored in ASCENDING order, or a lower value if the column is stored in DESCENDING order). For a fixed-length character column, an update value shorter than the column length is padded with single-byte ASCII blanks (HEX20) to fill the column.
Examples—UPDATE Examples—UPDATE This example updates a single row of the ORDERS table that contains information about order number 200038 and changes the delivery date: >> UPDATE SALES.ORDERS SET DELIV_DATE = 880522 +> WHERE ORDERNUM = 200038; --- 1 row(s) updated. This example updates several rows of the CUSTOMER table: >> UPDATE SALES.CUSTOMER SET CREDIT = "A1" +> WHERE CUSTNUM IN (21, 3333, 324); --- 3 row(s) updated.
UPDATE STATISTICS Statement UPDATE STATISTICS Statement UPDATE STATISTICS is a DDL statement that updates the statistics stored in the catalog for the specified table. SQL does not automatically update statistics; you must execute this statement to have current statistics in your catalog. UPDATE [ ALL ] STATISTICS FOR TABLE table [ RECOMPILE ] [ SIMPLE ] [ EXACT ] [ NO RECOMPILE ] [ PROBABILISTIC ] [ SAMPLE n BLOCKS ] ALL requests updated statistics for all columns in the table.
Considerations—UPDATE STATISTICS The PROBABILISTIC algorithm is designed to give more accurate results than the previous (SIMPLE) algorithm. Moreover, when you specify the PROBABILISTIC option, SQL computes statistics in parallel on partitioned tables; SQL operates in parallel on each partition in a table. If you specify the PROBABILISTIC option, SQL ignores the EXACT and SAMPLE n BLOCKS options. With the PROBABILISTIC algorithm, SQL always reads every row in the table.
Considerations—UPDATE STATISTICS Byte address of the end-of-file of the table (The number of bytes indicates the space used by the table.) Percent of blocks that contain rows (nonempty blocks) Number of index levels for the indexes on the table (Each level represents a disk access operation required to retrieve data.) Number of unique entries in each column (All null entries count as one unique entry, just as with other values.
Examples—UPDATE STATISTICS SQLCI LOAD or FUP RELOAD to reorganize the table before updating the statistics. UPDATE STATISTICS momentarily locks the definition of the table in the catalog during the operation, but not the table itself; the statement uses BROWSE ACCESS. All partitions of a table must be available for SQL to generate accurate statistics, not just the partition specified in the UPDATE STATISTICS statement.
UPGRADE CATALOG Command This example updates statistics for columns in the primary key or in indexes for a table named STUDENTS, directing SQL to calculate the statistics based on the contents of the first 300 blocks in each partition: UPDATE STATISTICS FOR TABLE STUDENTS SAMPLE 300 BLOCKS; UPGRADE CATALOG Command UPGRADE CATALOG is an SQLCI utility command that converts catalogs to a newer version so the catalogs can register objects associated with a newer version of the SQL/MP software.
Considerations—UPGRADE CATALOG specify must be newer than the current version of the catalogs you specify with catalogs. In addition, version must not specify a version newer than the version of the SQL/MP software executing UPGRADE CATALOG or newer than the version of the SQL/MP software running on the node of the catalog being upgraded.
UPGRADE SYSTEM CATALOG Command This example converts all catalogs (except the system catalog) on the current default node to the same version as the current SQL/MP software: >> UPGRADE CATALOGS $*.*; UPGRADE SYSTEM CATALOG Command UPGRADE SYSTEM CATALOG is an SQLCI utility command that allows a user with super ID authority to convert the system catalog on the local node to support a newer version of NonStop SQL/MP.
UPSHIFT Function UPSHIFT Function UPSHIFT is a function that upshifts single-byte characters. UPSHIFT can appear in a select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE predicate, an expression, or qualifying a new value in an UPDATE or INSERT statement. UPSHIFT returns a string of either character or varying-length character data, depending on the data type of the input string. For more information, see Character Expressions on page C-14.
USAGES Table USAGES Table The USAGES table is a catalog table that keeps records of dependencies between objects and between programs and objects. Table U-1 describes the contents of the USAGES table. In the column descriptions, the terms initial object and used object refer to an object on which another object depends. The dependent object is called a using object. Table U-1.
User-Defined Keys Only direct relations are stored. Indirect relations can be determined by following a path from one relation to another. The lock length for the USAGES table is set to zero to indicate that the entire primary key length should be used for locking. Guardian names in USAGES are fully qualified and use uppercase characters. Names of SQL programs in OSS files are stored as the corresponding ZYQ Guardian names, not OSS pathnames.
Utilities DUP PURGE EDIT PURGEDATA EXPLAIN SECURE FILEINFO TEDIT FILENAMES UPGRADE CATALOG FILES UPGRADE SYSTEM CATALOG FUP VERIFY The SQLCI commands EDIT, FUP, PERUSE, and TEDIT access Guardian utilities from SQLCI that you can also access from TACL; the other utilities are part of NonStop SQL/MP. For more information about these utilities, see entries for specific commands.
Utilities HP NonStop SQL/MP Reference Manual—523352-013 U-18
V VARCHAR_WIDTH Option VARCHAR_WIDTH is an option of the SQLCI report writer SET STYLE command that specifies the maximum number of single-byte characters the report writer can display in a print item of a varying-length character data type. VARCHAR_WIDTH number number is an integer in the range 1 through 255 that specifies the maximum number of single-byte characters that can appear in the print item. The default is 80.
Consideration—VERIFIEDWRITES Consideration—VERIFIEDWRITES On a verified write, the disk controller hardware makes a byte-by-byte comparison between the newly written data and the corresponding data in the controller's memory. Verified writes help ensure the accuracy of write operations, but they increase response time and disk utilization. VERIFY Command VERIFY is an SQLCI utility command that reports whether SQL objects and programs are consistently described in the file labels and the catalog.
Considerations—VERIFY You can use edit-file with the TACL OBEY command to recompile the invalid SQL programs in the Guardian files. (SQL does not generate commands to recompile SQL programs in OSS files.) CLEAR clears edit-file before writing SQLCOMP commands. If you omit CLEAR, SQL appends commands to the existing text. If SMF is installed on your node, edit-file must be either a logical or direct file.
Considerations—VERIFY view columns and the SELECT clause of a protection view are the same in the catalog and in the file label. For a shorthand view, VERIFY checks descriptions of the view. However, VERIFY does not check whether data types of view columns are the same in the catalog and in the file label. For an index, VERIFY checks descriptions of the index and its base table, views of the base table, and partitions.
Examples-VERIFY Repairing Views Both the protection view and short hand view, after re-creation, contain a new creation timestamp in the physical objects (file label) and catalog. This invalidates the programs using these views, which might call for recompilation of the programs. Usage of File Sets With Wild-Card Characters in Repair File sets used in the VERIFY command, such as $VOL.SUBVOL.T* cannot be repaired because the filenames are extracted from a disk.
Versions This example shows how to use the REPAIR option: >> VERIFY $VOL1.PERSNL.EMPLOYEE REPAIR; --- Verifying $VOL1.PERSNL.EMPLOYEE --- $VOL1.PERSNL.EMPLOYEE Verified. --- SQL operation complete. Versions A version is associated with each component of SQL/MP software; with each SQL/MP catalog, object, or program; and with each host language compiler that supports NonStop SQL/MP.
SQL/MP Component Versions SQL/MP Component Versions Each component of the SQL/MP software has a version. Except for the message file, all SQL/MP software components on any one node in a network must have the same product version. SQL/MP software on different nodes in the same network can have different product versions. The message file used by SQLCI also has a version.
Program Versions the table that has the NCHAR data type, however, the version of the table becomes version 300 (because the NCHAR data type is first supported in version 300). Because the version of an object affects the version of any object that depends upon that object, changing the version of an object can automatically change the version of other objects that depend on that object. For example, adding an index that has a newer version than the associated table changes the version of the table.
VERSIONS Table VERSIONS Table The VERSIONS table is a catalog table that stores version information about the catalog. The version information is also replicated in the SQL.CATALOGS table. Table V-1 describes the contents of the VERSIONS table. Table V-1. The VERSIONS Table Column Name Data Type Description 1 SUBSYSTEMNAME * CHAR(30) Name of subsystem where catalog resides 2 VERSION CHAR(4) Version of catalog: A010=1, A011=2, ...
VIEWS Table Protection views are always valid. A shorthand view becomes invalid if a user who lacks purge authority for the view itself purges any of the tables or views underlying the view. To make an invalid shorthand view valid, the owner of the invalid view must purge and re-create the view. VIEWS Table The VIEWS table is a catalog table that describes the views available for the base tables. Table V-2 describes the contents of the VIEWS table. Table V-2.
VOLUME Command VOLUME Command VOLUME is an SQLCI command that changes the current node, volume, or subvolume defaults for the SQLCI session. VOLUME also sets the VOLUME attribute of the =_DEFAULTS DEFINE. Defaults you set with VOLUME remain in effect until you exit SQLCI or until you enter a SYSTEM, VOLUME, or ALTER DEFINE that changes the defaults. [ \node] ] VOLUME [ [\node.][$volume] ] ; [ [\node.][$volume.]subvol ] \node specifies the node to be the current default node.
Examples—VOLUME This example resets the default node, volume, and subvolume to their values at the start of the SQLCI session: >> VOLUME; HP NonStop SQL/MP Reference Manual—523352-013 V-12
W WHENEVER DIRECTIVE WHENEVER is a host program directive that specifies an action to take when an error, warning, or no-rows-found condition occurs.
Consideration—WHENEVER Directive If you do not specify an action, SQL discontinues checking for the specified condition. Consideration—WHENEVER Directive The WHENEVER directive applies to source lines of a program that are sequentially compiled (including text brought in by SOURCE, COPY, and INLINE directives) The directive stays in effect until SQL detects another WHENEVER directive for the same condition.
Consideration—WINDOW specifies the position of the column in the select list). column cannot be a detail alias. You can use this clause only at the select-in-progress prompt (S>). If you specify a column that is not in the detail print list, the report writer ignores the WINDOW command. If the column appears in the detail print list more than once, its first occurrence is the one that prints at the left edge of the output device.
WITH SHARED ACCESS OPTION WITH SHARED ACCESS OPTION WITH SHARED ACCESS is an option available on some DDL statements that specifies that the DDL operation is to allow concurrent read-write DML access and read-only utility access to the objects on which it operates during all but the final phase of the operation.
Considerations—WITH SHARED ACCESS COMMIT WHEN READY TIMEOUT DEFAULT ONCOMMITERROR ROLLBACK WORK For more information, see COMMIT Option on page C-52. Considerations—WITH SHARED ACCESS Restrictions You cannot use WITH SHARED ACCESS on a statement that executes within a user-defined TMF transaction.
Considerations—WITH SHARED ACCESS 1. Initialization and load phase SQL reads catalog entries for existing objects involved in the operation (the “source objects”) and creates any new objects for the operation with auditing disabled. For CREATE INDEX operations, SQL also sets the NOAUDITCOMPRESS file attribute for the table being indexed unless it is already set. All this activity occurs in one transaction. Note. Some operations involve one source object and one target object, while others involve many.
Considerations—WITH SHARED ACCESS warnings 1618 and 1619 to notify the user that the operation is ready to commit and waits for the user to respond with a CONTINUE statement. If the operation cannot move into the commit phase immediately (because it must wait for a time window or a CONTINUE statement) the audit fix-up processes continue reading audit trails and updating target objects to maintain the ready-to-commit state. 3.
Example—WITH SHARED ACCESS cause far less application downtime than equivalent operations without WITH SHARED ACCESS. The duration of a WITH SHARED ACCESS operation increases with the number and length of transactions on the node that contains the source partition, particularly with the number and length of transactions that involve the source partition and the amount of activity on the audit trail used for the source partition.
Z ! Command ! (exclamation point) is an SQLCI command that re-executes a statement or command without modifying a previous statement or command in the history buffer or a current report formatting command. For more information, see HISTORY Command on page H-5. ! [ text ] [ ; ] [ [-]number ] text specifies the most recent version of a command in the history buffer or a stored report formatting command.
=_AUDSERV_XSWAP_node DEFINE =_AUDSERV_XSWAP_node DEFINE =_AUDSERV_XSWAP_node is a system DEFINE that specifies a swap volume for the audit fix-up process on the specified node. ADD DEFINE =_AUDSERV_XSWAP_node, CLASS MAP, FILE volume node is the name of the node (without the usual leading “\”) for which the swap volume of the audit fix-up process is to be set. volume is the name of the swap volume for the audit fix-up process on the node.
=_DEFAULTS DEFINE =_DEFAULTS DEFINE =_DEFAULTS is a system DEFINE that specifies the current default node, volume, subvolume, and catalog. =_DEFAULTS determines how to expand partially qualified Guardian names. =_DEFAULT always has a VOLUME attribute that specifies the current default volume and subvolume. If =_DEFAULT has a CATALOG attribute, that attribute specifies the current default SQL catalog; if not, the VOLUME attribute specifies the subvolume that is the current default SQL catalog.
Examples—=_DEFAULTS Examples—=_DEFAULTS These commands are all legal commands that alter the =_DEFAULTS DEFINE: ALTER DEFINE =_DEFAULTS, ALTER DEFINE =_DEFAULTS, ALTER DEFINE =_DEFAULTS, VOLUME $VOL1.ORDENTRY; SYSTEM \SYS2; ALTER DEFINE =_DEFAULTS, VOLUME \SYS1.$VOL1.PERSNL; CATALOG $VOL1.CAT1; VOLUME $VOL1.
Considerations—=_SORT_DEFAULTS param value specifies a FastSort parameter and a value for that parameter. FastSort parameters commonly used with NonStop SQL/MP are: SCRATCH file specifies the Guardian name of a scratch file or volume to store runs of records sorted by each SORTPROG process. If you specify only a volume name, FastSort automatically creates the scratch file; this method is recommended for parallel sort operations.
Example—=_SORT_DEFAULTS An error occurs if you add =_SORT_DEFAULTS without specifying CLASS SORT either explicitly or through the working attribute set. For more information about =_SORT_DEFAULTS, see the TACL Reference Manual or the FastSort Manual.
=_SQL_CMP_CPUS_node DEFINE =_SQL_CMP_CPUS_node DEFINE The =_SQL_CMP_CPUS_node DEFINE is a system DEFINE that directs SQL to limit processors used in a parallel query to a specified node. ADD DEFINE =_SQL_CMP_CPUS_node, CLASS MAP, FILE Xhhhh _node is the node associated with the processors to use for the query. _node is optional; the default value is the current node. Xhhhh is the letter X (uppercase) followed by up to four hex characters.
=_SQL_CMP_DOUBLE_SBB_OFF DEFINE The processors in this example are shown in groups of four, because each hex character represents four processors: Hex Binary Hex Binary 0 0000 8 1000 1 0001 9 1001 2 0010 A 1010 3 0011 B 1011 4 0100 C 1100 5 0101 D 1101 6 0110 E 1110 7 0111 F 1111 Using this binary-to-hex conversion chart, the first group of four processors-0, 1, 2, and 3-with the binary code “1111” map to the single hex character “F.
Consideration—=_SQL_CMP_DOUBLE_SBB_OFF Consideration—=_SQL_CMP_DOUBLE_SBB_OFF =_SQL_CMP_DOUBLE_SBB_OFF disables file system double buffering for queries that are SQL compiled while this DEFINE is in effect. For information about how to manage double buffering, see the SQL/MP Installation and Management Guide. =_SQL_CMP_DOUBLE_SBB_ON DEFINE =_SQL_CMP_DOUBLE_SBB_ON is a system DEFINE that directs SQL to use file system double buffering to scan the inner table in a nested join or key-sequenced merge join.
=_SQL_CMP_EQ_LIMIT DEFINE =_SQL_CMP_EQ_LIMIT DEFINE =_SQL_CMP_EQ_LIMIT is a system DEFINE that specifies the number of expressions in an equivalence class for which the query rewrite feature adds equality predicates. ADD DEFINE =_SQL_CMP_EQ_LIMIT, CLASS MAP, FILE xnn xnn is a string containing a single arbitrary alphabetic character followed by one or two digits that represent a decimal value between 0 and 99. If you do not use this DEFINE, SQL uses a default value of 5.
=_SQL_CMP_EVENT DEFINE =_SQL_CMP_EVENT DEFINE =_SQL_CMP_EVENT is a system DEFINE that directs the SQL compiler to log compiler event messages to a file or to the home terminal. ADD DEFINE =_SQL_CMP_EVENT, CLASS MAP, FILE filename filename must be a legal Guardian file name. If the named file is an existing, entry-sequenced file, the compiler sends compiler event messages to the file.
Considerations—=_SQL_CMP_EVENT Considerations—=_SQL_CMP_EVENT If you use =_SQL_CMP_EVENT to direct compiler event messages regarding automatic recompilations (PROG and STMT type events) to a home terminal, the home terminal used is the home terminal for the process that invoked the SQL compiler.
Default Event Messages Default Event Messages If the =_SQL_CMP_EVENT_NO0 DEFINE does not exist, the SQL compiler issues a message to $0 whenever an automatic recompilation occurs.
=_SQL_CMP_NO_KS_MJOIN DEFINE =_SQL_CMP_NO_KS_MJOIN DEFINE =_SQL_CMP_NO_KS_MJOIN is a system DEFINE that inhibits SQL from using the key-sequenced merge join operation. ADD DEFINE =_SQL_CMP_NO_KS_MJOIN, CLASS MAP, FILE name name must be a legal Guardian file name but has no purpose except to make the ADD DEFINE command syntactically correct. If you do not set this DEFINE, a key-sequenced merge join is considered.
Consideration—=_SQL_cmp_node name is the name of the object file to use in place of the standard $SYSTEM.SYSTEM object file for the component. Consideration—=_SQL_cmp_node You can use a =_SQL_cmp_node DEFINE only if you are logged on as a user in group 255. SQL ignores the DEFINE for other users. If the DEFINE =_SQL_CI2_node is in effect during INITIALIZE SQL, the alternate file specified is the file compiled and registered in the system catalog by the INITIALIZE SQL operation.
=_SQL_EXE_ESPS_CK_CMON DEFINE For information about how to manage double buffering, see the SQL/MP Installation and Management Guide. =_SQL_EXE_ESPS_CK_CMON DEFINE =_SQL_EXE_ESPS_CK_CMON is a system DEFINE that directs SQL to communicate with the $CMON process whenever it must create a new ESP. ADD DEFINE =_SQL_EXE_ESPS_CK_CMON, CLASS MAP, FILE name name must be a legal Guardian file name but has no purpose except to make the ADD DEFINE command syntactically correct.
=_SQL_EXE_USE_SWAPVOL DEFINE =_SQL_EXE_USE_SWAPVOL DEFINE =_SQL_EXE_USE_SWAPVOL is a system DEFINE that directs SQL to allocate temporary tables for serial plans on the swap volume for the process. ADD DEFINE =_SQL_EXE_USE_SWAPVOL, CLASS MAP, FILE name name must be a legal Guardian file name but has no purpose except to make the ADD DEFINE command syntactically correct.
=_SQL_MSG_node DEFINE =_SQL_MSG_node DEFINE =_SQL_MSG_node is a system DEFINE that directs SQL to use an alternate message file. It allows you to specify message files that provide SQL messages in languages other than English. ADD DEFINE =_SQL_MSG_node, CLASS MAP, FILE msg-file node is the name of the node running the SQL software that is to use the alternate message file. Specify the node name without the leading backslash (\). msg-file is the name of the alternate message file.
Examples—=_SQL_MSG_node To use an alternate message file from a program, specify an =_SQL_MSG_node DEFINE in TACL before you execute the program, or specify the DEFINE at the beginning of the program. SQL uses the file you specify for all SQL messages from the program.
=_SQL_RECGEN_node DEFINE =_SQL_RECGEN_node DEFINE =_SQL_RECGEN_node is a system DEFINE that allows a user with super ID authority to specify an alternate location for the FastSort record generator program. ADD DEFINE =_SQL_RECGEN_node, CLASS MAP, FILE prog-file node is the name of the node (without the usual leading backslash “\”) running the SQL software that is to use the alternate FastSort record generator program. Example—=_SQL_RECGEN_node This SQLCI command specifies $DP1.TEST.
Considerations—=_SQL_TM_node_vol syncdepth is the keyword SYNC1 (if you want the temporary files to use SYNCDEPTH 1), or a simple file name (if you want the temporary files to use SYNCDEPTH 0, as usual). The file name must be a legal Guardian file name, but the file is not used and does not need to exist.
Examples—=_SQL_TM_node_vol This TACL command directs NonStop SQL/MP to create all temporary tables that would normally go to volume \NY.$HDQ on volume \NY.$SCR instead and to use SYNCDEPTH 1 for the tables rather than the usual SYNCDEPTH 0: 32> ADD DEFINE =_SQL_TM_NY_HDQ, FILE \NY.$SCR.TMP.SYNC1 Both DEFINEs shown in the previous examples can exist simultaneously, directing different sets of temporary tables to different locations.
Index Numbers 255, user number G-7, S-12 A A (alphanumeric) descriptor A-61 Abort transaction R-22 Access options BROWSE A-1 concurrency summary A-3 DDL statements W-4 description of A-1 DML statements A-1 INSERT statement I-15 REPEATABLE A-1 STABLE A-1 UPDATE statement U-3 Access path alternate E-16 controlling C-79, C-85, C-86 primary E-16 ACCESS PATH option CONTROL TABLE directive C-79 controlling C-79, C-85 in EXPLAIN report E-16 Access type, in EXPLAIN report E-16 Ada language S-69 ADD COLUMN clause S
Index A ALTER INDEX statement ADD PARTITION clause A-18 ALLOCATE clause A-18 description of A-13 DROP PARTITION clause A-15 examples of A-26 file security attributes A-15 FIRST KEY clause A-18 Format 2 enabling A-25 MAXEXTENTS clause A-18 MOVE clause A-16 MOVE options A-16 RENAME clause A-15 RESETBROKEN clause A-18 SECURE file attribute A-15 WITH SHARED ACCESS clause A-17 ALTER PROGRAM statement description of A-27 OWNER file attribute A-27 RENAME clause A-27 SECURE file attribute A-27 ALTER statements an
Index B AS clause (continued) report writer option R-10 AS DATE/TIME clause description of A-67 report writer option R-10 ASCII character set A-70, C-18 Associativity and UNION ALL S-27 Asterisk, in subtotal label S-88 Atomicity, statement S-68 Attributes See File Attributes AUDIT in FILES table F-27 in VIEWS table V-10 AUDIT file attribute and BUFFERED attribute A-75 description of A-74 set by DUP D-78 turning off for loading data L-18, L-34 views and C A-74, C-170 Audit fix-up phase W-6 Audit trails A-7
Index C BREAK FOOTING command AS clause A-60 COMPUTE_TIMESTAMP function C-62 CONCAT clause C-63 CURRENT_TIMESTAMP function C-174 description of B-6 IF/THEN/ELSE clause I-1 Break key and CLEANUP command C-22 and command files O-2 and CONVERT operation C-100 and COPY command C-124 and DUP command D-78 and LOAD command L-33 and PURGE command P-35 and PURGEDATA command P-38 and SECURE command S-9, S-10 and SET SESSION command S-43 effect on command files O-2 effect on duplicating D-78 SQLCI response to S-40,
Index C Catalog tables (continued) COMMENTS C-51 CONSTRNT C-70 CPRLSRCE C-131 CPRULES C-131 FILES F-27 INDEXES I-10 KEYS K-1 operations on C-9 PROGRAMS P-29, P-31 retrieving statistics from U-10 See PARTNS catalog table See USAGES catalog table selecting from S-30 TABLES T-2 TRANSIDS T-11 VERSIONS V-9 VIEWS V-10 CATALOGCLASS in CATALOGS table C-11 in VERSIONS table V-9 CATALOGFORMAT in VERSIONS table V-9 CATALOGNAME in CATALOGS table C-11 in PARTNS table P-20 Catalogs ALTER CATALOG statement A-8 component
Index C Character strings (continued) literals S-80 matching pattern L-2 Character values, display format of A-61 CHARACTERISTICS in CPRULES table C-131 CHARACTERSET in COLUMNS table C-47 in CPRULES table C-131 CHAR_LENGTH function C-20 CHECK clause CHECKMODE, in PROGRAMS table P-32 CREATE CONSTRAINT statement C-139 CREATE VIEW statement C-168 CHECKMODE, in PROGRAMS table P-32 Clauses AS A-60 COLLATE C-29 CONCAT C-63 DEFAULT D-26 DISTINCT D-57 IF/THEN/ELSE I-1 PARTITION P-16 CLEANUP command and dependent
Index C Collations (continued) similarity rules for S-57 versions V-7 Collations buffer DESCRIBE statement D-45 INCLUDE SQLDA directive I-5 Collectors R-3 COLNAME in COLUMNS table C-46 COLNUMBER in COLUMNS table C-46 COLSIZE in COLUMNS table C-46 Column identifier C-44 Columns ADD COLUMN option A-33 assigning alias to N-1 catalog description of C-46 changing heading A-33 default values for D-26 definition of tables C-46 definition syntax C-155 description of C-45 HEADING clause A-50 heading length limit L
Index C Commands (continued) LIST L-16 LOAD L-18 LOG L-52 MODIFY CATALOG M-4 MODIFY LABEL M-19 MODIFY REGISTER M-40 NAME N-1 OUT O-7 OUT_REPORT O-8 PAGE FOOTING P-1 PAGE TITLE P-4 PERUSE P-21 PURGE P-33 PURGEDATA P-36 reexecuting Z-1 REPORT FOOTING R-2 REPORT TITLE R-6 RESET DEFINE R-12 RESET LAYOUT R-13 RESET PARAM R-14 RESET PREPARED R-16 RESET REPORT R-16 RESET SESSION R-19 RESET STYLE R-19 SAVE S-2 saving in a file S-2 SECURE S-7 SET DEFINE S-32 SET DEFMODE S-34 SET LAYOUT S-34 SET PARAM S-35 SET SESS
Index C Compatible data types and INSERT I-16 Compilation authority for S-15 automatic P-29 explicit P-29 Guardian command for S-67 OSS command for C-176 time D-52 Compiler event messages Z-11 Compilers and statistics U-9 host language, versions V-8 NonStop SQL/MP, versions V-6 Components, versions of V-6 COMPUTE_TIMESTAMP function and report writer R-11 description of C-62 SET PARAM command S-36 CONCAT clause description of C-63 report writer option R-10 Concurrency affect of access options A-3 and buffe
Index C CONTROL TABLE directive (continued) STOP AT option C-83 SYNCDEPTH option C-84 TABLELOCK option C-84 TIMEOUT option C-84 UNAVAILABLE PARTITION option C-83 with LOCK TABLE statement L-46 Controlling the SORTPROG process S-25 Conversational interface See SQLCI CONVERT command alternate key specification C-100 authorization requirement C-99 behavior C-99 CATALOG option C-96 CHARACTER option C-98 COMMENTS option C-97 conversion of DDL items with C-103 DDL groups C-97, C-107, C-108 description of C-94 D
Index C Copying data from Enscribe file to SQL table C-113 data from SQL table to Enscribe file C-113 Enscribe files Correlated subqueries S-83 Correlation names C-128 Cost of executing a statement D-52 of hash operation E-17 of sort operation E-19 of SQL operation, in EXPLAIN report E-18 of total SQL statement E-20 COUNT function C-130 COUNT option COPY command C-116 LOAD command L-21 CPARRAYENTRY in COLUMNS table C-47 CPRLSRCE catalogs table C-131 CPROBJSIZE in CPRULES table C-131 CPRULES catalog table
Index D CREATE VIEW statement AS clause C-167 CATALOG clause C-168 CHECK C-168 description of C-166 FOR PROTECTION clause C-168 HEADING clause C-167, H-1 length of C-170 SECURE file attribute C-168 SELECT DISTINCT clause S-68 SIMILARITY CHECK clause C-168 WITH CHECK OPTION clause C-169 WITH HEADINGS clause C-169 WITH HELP TEXT clause C-169 CREATEINDEX keyword, configuration file P-6 CREATETIME in INDEXES table I-10 in PROGRAMS table P-31 in TABLES table T-2 Creating catalog C-132 CATALOGS table C-152 coll
Index D Data declaration BEGIN DECLARE SECTION directive B-2 END DECLARE SECTION directive E-3 tables and views I-25 Data Definition Language (DDL) alternate keys C-100 and database concurrency C-65, D-21 clauses, CONVERT statement C-101 groups, converting C-107 performance considerations C-134 primary key C-100 record definitions, Enscribe file C-100 security requirements S-15 Data Definition Language (DDL) statements ALTER CATALOG A-8 ALTER COLLATION A-10 ALTER INDEX A-13 ALTER PROGRAM A-27 ALTER TABLE
Index D Data types (continued) for parameters C-4, P-14, S-36 FORTRAN C-105 INTERVAL I-19 Julian timestamp C-62 numeric N-11 TIME T-5 TIMESTAMP T-5 view columns C-170 Database authorization for access S-11 integrity C-69, C-139 sample S-1 updating statistics U-7 DATATYPE in COLUMNS table C-46 Date computing for report C-62, C-173 of expiration A-32 print item display format A-67 specifying default format D-8 DATE data type D-8 DATE literal D-11 DATE values in host variables H-6 LOAD command L-38 DATEFORMA
Index D Decorations default A-64 description of A-64 display A-60 using A-64 Dedicated-operation-in-progress prompt S-60 Default catalog C-8 column values D-26 CURRENT value D-26 decimal character D-22 decorations A-64 detail line D-50 displaying current E-3 layout options R-13 line length L-7 names in prepared commands P-25 node, displaying E-3 page length for reports P-2 print item headings D-50 session options R-19 space between print items S-58 style options R-19 subvolume V-11 system D-27, E-3 time d
Index D Defines (continued) =_SQL_TM_node_vol Z-20 =_SQL_UTL_node Z-14 DEFINES option, SAVE command S-2 DEFMODE attribute D-28 DELETE DEFINE command D-38 DELETE statement description of D-39 lock release summary L-48 table privileges S-68 WHERE clause D-39 WHERE CURRENT OF clause D-39 Deleting damaged objects C-21 help text H-4 rows D-39 system catalog D-66 Delimited identifiers S-69 Dependent object DISPLAY USE OF command D-54 information in USAGES table U-15 type D-55 DESCRIBE INPUT statement D-41 DESCR
Index D Disk file device width used R-20 labels in SQL D-1 Disk space conserving D-18 manipulating A-7 Display formats date print item A-67 default, for date D-8 default, for time T-4 print item A-60 STATISTICS of FILEINFO F-22 subtotal S-86 time print item A-67 total T-9 Display format, COPY command C-122 Display modifiers A-60 DISPLAY STATISTICS command D-52 DISPLAY USE OF command AT option D-54 authorization requirements D-54 brief format D-54 description of D-54 USAGES table D-55 Displaying contents o
Index E DROP PARTITION clause, ALTER INDEX statement A-15 DROP PARTITION clause, ALTER TABLE statement A-34 DROP statement D-63 DROP SYSTEM CATALOG command D-66 DSAP utility U-17 DSL See Data Status Language (DSL) DSLACK file attribute D-68 DSLACK option, LOAD command L-28 DSM (Distributed Systems Management) R-3 DUP command authorization requirements D-74 CATALOG option D-71 description of D-69 INDEXES option D-74 LISTALL option D-72 MAP NAME option D-70 rules D-74 SAVEALL option D-73 SAVEID option D-73
Index E Erasing data from disk C-26 ERROR command description of E-4 DETAIL option E-5 Error messages description of E-6 FETCH statement F-4 Errors 10088 S-44 1057 C-54, D-21 1125 A-24, A-45 12 C-54, D-21 1203 D-21 122 C-88, Z-17, Z-21 1222 D-21 1411 A-25 1615 - 1618 C-54 1621 - 1622 C-54 199 (Disk file is Safeguard protected) A-28 200 - 255 (A path or network error occurs) C-83 3001 - 3999 C-54 35 (Lock limit has been reached) C-84 40 C-54, D-21, D-22, P-35, P-39 40 (Operation timed out) C-85 45 A-17, A-
Index F Examples, database used in S-1 Exclamation point command Z-1 EXCLUSIVE clause, LOCK TABLE statement L-45 Exclusive locks description of L-50 EXCLUSIVE clause L-45 on SELECT statement S-21 Execute access S-14 EXECUTE command COMPUTE_TIMESTAMP function C-62 CURRENT_TIMESTAMP function C-174 EXECUTE IMMEDIATE statement E-11 EXECUTE statement CURRENT_TIMESTAMP function E-8 description of E-7 RETURNING clause E-9 USING clause E-7 USING DESCRIPTOR clause E-7 Execution plans P-22 EXISTS predicate E-12 EXI
Index F File attributes (continued) DSLACK D-68 EXTENT E-30 ICOMPRESS I-1 ISLACK I-29 LOCKLENGTH L-51 MAXEXTENTS M-2 NOPURGEUNTIL N-5 OWNER O-10 PROGID P-28 RECLENGTH R-1 RESETBROKEN R-20 SECURE S-11 SERIALWRITES S-32 SLACK S-57 TABLECODE T-1 VERIFIEDWRITES F-30, V-1 FILE IS option, CONVERT command C-97 File labels, used by SQL D-1 File names Guardian G-7 simple G-7 File organizations F-8 File Utility Program (FUP) F-33/F-36 FILEFORMAT, in FILES table F-28 FILEINFO command BRIEF option F-10, F-11, F-21 de
Index F FILETYPE, in FILES table F-27 File, formats, Enscribe Q-4 File-system errors D-21 Filler characters, overflow O-10 FIRST KEY clause ALTER INDEX statement A-18 ALTER TABLE statement A-37 defaults for unspecified columns P-17 First key, size limit L-8 FIRST option COPY command C-116 LOAD command L-21 FIRSTKEY, in PARTNS table P-20 Fixing command F-1 FLOAT data syntax D-5 FOLD option, COPY command C-119 Folding a line L-53 Footing See PAGE FOOTING command Footings break B-6 page P-1 report R-2 FOR PR
Index G Functions (continued) COUNT C-130 CURRENT C-173 CURRENT_TIMESTAMP C-174 DATEFORMAT D-14 date-time D-10 DAYOFWEEK D-17 description of F-32 EXTEND E-28 JULIANTIMESTAMP J-4 LINE NUMBER L-14 MAX M-1 MIN M-3 PAGE_NUMBER P-3 POSITION P-23 scalar F-33, S-46 SETSCALE S-46 string S-80 SUBSTRING S-84 SUM S-89 TRIM T-11 UPSHIFT U-14 FUP command F-33/F-36 FUP LICENSE command F-35 G Generalized owner G-1, S-9, S-14 Generic lock L-51 GET CATALOG OF SYSTEM statement G-1 GET VERSION OF PROGRAM statement G-4 GET
Index I HEADINGTEXT in COLUMNS table C-47 HELP command H-2 HELP TEXT statement H-4 Help text, deleting H-4 Hidden text C-51 HISTORY command H-5 Host and SQL communication H-6 Host identifiers H-5 Host object SQL version (HOSV) V-8 Host programming language compiler versions V-8 description of E-2, H-5 Host programs H-6 Host variables declaration B-2 description of H-6 substituting values in a query H-6 TYPE AS clause H-6 HOSV (host object SQL version) V-8 I ICOMPRESS file attribute description of I-1 dis
Index I INDEXES catalog table I-10 INDEXES option, DUP command D-74 INDEXLEVELS, in INDEXES table I-10 INDEXNAME in INDEXES table I-10 in KEYS table K-1 INDICATOR clause host variable H-6 parameter name P-12 INDICATOR parameter I-11 Indicator variable H-6, I-11, P-12 INFO DEFINE command I-12 Initial object DISPLAY USE OF command D-56 type D-56 INITIALIZE SQL command I-13 Initializing columns A-33, D-7 Inner joins J-1, J-2 Inner query S-83, S-84 Inoperable plan P-22 Input host variables D-41 INSERT operati
Index J IXPART01 catalog table, limits L-10, L-11 file C-9 IXPROG01 file C-9 IXTABL01 file C-9 IXUSAG01 file C-9 I/O (input/output) serial or parallel writes S-32 verify disk writes V-1 J JIS X0208 (Japanese) character set C-18 JOIN METHOD option, CONTROL TABLE directive C-80 Join queries J-1 JOIN SEQUENCE option, CONTROL TABLE directive C-81 Joins controlling C-86 description of J-1 example of S-30 inner J-1, J-2 left outer J-1 SELECT specification S-20 Julian timestamp and COMPUTE_TIMESTAMP C-62 format
Index L LAYOUT option, SAVE command S-3 LC_COLLATE section C-32 LC_CTYPE section C-35 LC_TDMCODESET section C-37 Left justification A-61, A-63, A-64, A-65 LEFT OUTER JOIN operator S-70 Left outer joins J-1 LEFT_MARGIN layout option L-1, R-10 Length of page P-2 Levels of index, displaying F-19 Licensing F-35 LIKE clause, CREATE TABLE statement C-155 LIKE predicate L-2 Limits on concurrency C-68, L-6 Lines folding L-53 new line character N-4 number on page P-2 resetting number of L-14 skipping in report D-4
Index M LOAD command options (continued) TARGETREC L-30 TRIM L-26 TRUNC L-31 UNLOADIN L-27 UPSHIFT L-23 VARIN L-27 LOAD option, CONVERT command C-97 Loading data, rules for tape use L-34 Local autonomy C-87 Local user S-14 LOCK TABLE statement and CONTROL TABLE directive L-46 description of L-45 EXCLUSIVE clause L-45 SHARE clause L-45 Lock waits, CONTROL TABLE directive C-82 Locking L-47 LOCKLENGTH file attribute description of L-51 displaying F-17 in FILES table F-27 Locks access by cursors F-4 and FETCH
Index N MDAM option CONTROL QUERY directive C-75 CONTROL TABLE directive C-81 Memory limits L-10, L-11 Message file M-3, Z-17 alternate Z-18 versions of V-6 MESSAGEFILE, displaying name of E-3 Messages compiler event Z-11 displaying warning S-42 error and warning E-6 from SQLCOMP compiler Z-13 non-English Z-18 MIN function M-3 Mixed views, AUDIT file attribute A-74 MODE, parameter for FastSort Z-4 Modifiers, display A-60 MODIFY CATALOG command REPLACE NODENAME M-4 REPLACE VOLUME M-11 MODIFY LABEL command
Index O NO PROGID file attribute, description of P-28 NOAUDITCOMPRESS attribute, turned off for WITH SHARED ACCESS W-6 Nonaudited objects consistency considerations for T-9 control opens C-77 FREE RESOURCES considerations F-30 lock holder L-50 lock release summary L-48 Nonaudited tables BUFFERED file attribute B-11 description of N-4 unlocking U-1 virtual sequential block buffering(VSBB) C-89 NONEMPTYBLOCKCOUNT, in FILES table F-28 NonStop SMF S-78 NonStop SQL/MP dictionary D-1 statements S-73 statements,
Index O Objects deleting damaged C-21 dependencies U-15, V-7 determining catalog in which defined F-16 dropping P-33 names of G-7 programs as P-31 versions V-7 Objects displaying ownership of D-56 security of D-56 use of D-54 OBJECTVERSION in INDEXES table I-10 in TABLES table T-3 OBJNAME, in COMMENTS table C-51 OBJSUBNAME, in COMMENTS table C-51 OBJTYPE, in COMMENTS table C-51 OC modifier O-10 OCTET_LENGTH function O-4 OFFSET in COLUMNS table C-46 One-way partition split, ALTER TABLE statement A-40 Onlin
Index P ORDER BY clause (continued) relation to break groups B-8 SELECT statement S-23 ORDERING, in KEYS table K-1 ORGANIZATION clause, CREATE TABLE statement C-158 Organization of files F-8 Orphans, preventing in report D-49 OSS names O-6 OSS processes E-2 OSSFILE, in PROGRAMS table P-32 OUT command CLEAR option O-7 description of O-7 OUT file description of O-7 displaying name of E-3 Outer joins J-1 Outer query S-83 Outer reference S-83 Output device width R-20, S-42 directing to a file O-7 host variabl
Index P PAGE TITLE command (continued) IF/THEN/ELSE clause I-1 PAGE_COUNT layout option P-1, R-10 PAGE_LENGTH layout option P-2, R-10 PAGE_NUMBER function P-3, R-11 PAID (Process Access ID) S-13 Parallel execution in EXPLAIN report E-18 loading indexes C-145 query C-73, C-74 similarity, effect on S-54 PARALLEL EXECUTION clause, CREATE INDEX statement C-145 PARALLEL EXECUTION option, LOAD command L-32 Parallel index loading description of P-5 partitions C-145 Parallel writes compared to serial S-32 descrip
Index P Partitions adding index A-20 table A-35 Partitions altering file attributes index A-15 table A-32 Partitions dropping index A-25 table A-34, A-47 Partitions Format 2 description F-30 EXTENT sizes E-30 MAXEXTENTS M-2 migration A-46 PARTITION clause P-16 PARTNS catalog table description of P-20 limits L-10, L-11 PARTOF option, LOAD command L-27 PARTONLY option, LOAD command L-27 PARTONLYIN option, LOAD command L-27 Pascal language and embedded SQL E-2 record definition, invoking I-26 Pathnames O-6 P
Index P PREPARE dynamic SQL P-5 PREPARE statement P-25 Prepared command displaying S-51 resetting R-16 Primary access path E-16 PRIMARY KEY clause, CREATE TABLE statement C-157 Primary keys catalog description of K-1 description of P-27 for indexes I-9 length limit L-13 system-defined, data types S-90 updateable S-69 user-defined U-16 Primary partition P-19 PRIMARYEXT2, in FILES table F-28 PRIMARYEXT, in FILES table F-27 PRIMARYPARTITION, in PARTNS table P-20 Print item headings, default D-50 Print items
Index Q Program format version (PFV) V-8 PROGRAM INVALIDATION P-28 PROGRAMCATALOGVERSION, in PROGRAMS table P-32 PROGRAMFORMATVERSION, in PROGRAMS table P-32 Programmatic SQL E-2 Programming language, description of E-2 PROGRAMNAME, in PROGRAMS table P-31 Programs ALTER PROGRAM statement A-27 altering security attributes A-27 catalog description of object in P-31 compiling from Guardian S-67 compiling from OSS C-176 dependent, effect of CLEANUP C-23 DROP statement D-65 dropping D-65, P-33 duplicating D-69
Index R Query processing getting partial results C-87 table reference limit L-13 Quotation marks within string literals S-81 R Random access block size B-5 Range of values, selecting B-4 Read access S-14 READ COMMITTED S-70 READ operations, buffered C-82 READ UNCOMMITTED S-70 Reading data S-17 row F-3 REAL data syntax D-5 RECIN option, LOAD command L-25 RECLENGTH file attribute R-1 RECOMPILEMODE, in PROGRAMS table P-32 RECOMPILETIME, in PROGRAMS table P-31 Record definition, invoking I-26 descriptions, c
Index R Report cancelling C-2 centering contents of C-12 copies O-9 default format D-50 default right margin L-53 file, closing O-8 file, specifying O-8 file, specifying or closing O-8 form feed for P-2 form name O-9 headings D-50 line length D-50 logical lines D-50 naming O-9 number of copies, specifying O-9 number of rows before pause S-41 numbering lines L-14 page size for PERUSE O-9 preventing orphans D-49 preventing widows D-49 summaries D-50 width L-53 REPORT FOOTING command AS clause A-60 COMPUTE_T
Index S Report writer functions COMPUTE_TIMESTAMP C-62 CURRENT_TIMESTAMP C-174 LINE_NUMBER L-14 Reports form feeds P-3 summary D-50 suppressing headings H-1 Reserved words R-11 RESET DEFINE command R-12 RESET LAYOUT command R-13 RESET PARAM command R-14 RESET PREPARED command R-16 RESET REPORT command R-16 RESET SESSION command R-19 RESET STYLE command R-19 RESETBROKEN clause, ALTER INDEX statement A-18 RESETBROKEN file attribute R-20 RESTORE utility U-17 Return control of locks C-82 RETURN IF LOCKED opti
Index S SAVEALL option, DUP command D-73 Saved commands, executing O-1 SAVEID option, DUP command D-73 Saving commands in a file S-2 SBB, in EXPLAIN report E-19 Scale of expression results E-24 of numeric results E-24 SETSCALE function S-46 SCALE in COLUMNS table C-46 Scientific notation N-13 SCRATCH option, LOAD command L-28 SCRATCHON, parameter for FastSort Z-4 SCRATCH, parameter for FastSort Z-4 Search conditions description of S-5 table selectivity E-19 WHERE clauses W-2 SEARCH DEFINEs D-32 Secondary
Index S SEGMENT, parameter for FastSort Z-4 SELECT command, directing output of O-8 SELECT DISTINCT clause, and CREATE VIEW statement S-68 SELECT statement and scale S-47 BROWSE access S-21 cancelling, in SQLCI C-2 description of S-18 DISTINCT clause D-57, S-19 dynamic SQL S-24 FOR UPDATE OF clause S-24 FROM clause S-20 GROUP BY clause S-22 HAVING clause S-21 INTO clause S-19 lock mode option S-21 lock realease summary L-48 lock release summary L-48 ORDER BY clause S-23 restrictions for view definitions C
Index S SET PARAM command (continued) description of S-35 SET SESSION command AUTOWORK option S-39 BREAK_KEY option S-40 description of S-39 DISPLAY_ERROR option S-40 ERROR_ABORT option S-40 ERROR_TEXT option S-41 LIST_COUNT option S-41 MANDATORY_REPORT option S-41 STATISTICS option S-41 WARNINGS option S-42 WRAP option S-42 SET STYLE command S-45 SETMODE 91,3 option C-83 SETSCALE function S-46 shadow label and CLEANUP C-22 and FILEINFO F-10 description of F-12 Shadow labels and CLEANUP command C-22 and F
Index S SORT DEFINEs D-32 SORTED option, LOAD command L-27 Sorting ORDER BY clause on SELECT S-23 parameters for Z-4 performance Z-4 SORTPROG process, controlling S-25 SOURCE option, CONVERT command C-97 SOURCEDATE option, DUP command D-73 SOURCEDICT option, LOAD command L-29 SOURCEREC option, LOAD command L-29 Space available in file F-19 SPACE clause, DETAIL command D-49 SPACE clause, report writer option R-11 SPACE layout option R-10, S-58 Spaces, suppressing in printing C-63 Splitting indexes A-19 par
Index S SQL.
Index S Statistics (continued) enabling display of S-41 file F-22 for partition U-10 from PREPARE P-25 retrieving from catalog tables U-10 selecting current compiler with U-9 updating U-7 using for better access plan S-78 STATISTICS display, FILEINFO command F-22 STATISTICS option FILEINFO command F-10 SET SESSION command S-41 STATISTICSTIME, in BASETABS table B-1 Status, checking with SQLCA I-4 STOP AT option, CONTROL TABLE directive C-83 Stopping a SELECT command C-2 Storage Management Foundation (SMF)
Index T System catalog (continued) creating C-152 description of S-92 dropping D-66 upgrading U-13 versions V-7 SYSTEM command S-93 System default multibyte character set M-43 SYSTEM default value D-27 System defines summary S-94 =_AUDSERV_XSWAP_node Z-2 =_DEFAULTS Z-3 =_SORT_DEFAULTS Z-4 =_SQL_AUD_node Z-14 =_SQL_CAT_HEAP_LIMIT Z-6 =_SQL_CAT_node Z-14 =_SQL_CI2_node Z-14 =_SQL_CMP_CPUS_node Z-7 =_SQL_CMP_DOUBLE_SBB_OFF Z-8 =_SQL_CMP_DOUBLE_SBB_ON Z-9 =_SQL_CMP_EQ_LIMIT Z-10 =_SQL_CMP_EVENT Z-11 =_SQL_CMP
Index T Tables address of EOF F-19 altering attributes A-32 attribute specification C-154 available space F-19 base T-1 catalog C-8 catalog description of T-2 clearing data from P-36 column limit L-6 COLUMNS C-46 COMMENTS C-51 CONSTRNT C-70 copying to an Enscribe file C-113 CREATE TABLE statement C-154 date-caused program recompilation F-19 description of T-1 displaying C-123 DROP statement D-65 dropping P-33 duplicating D-69, D-74 expiration date, setting N-5 file statistics F-22 fragmented, updating sta
Index T Terminal, device width R-20 Terminating a SELECT command C-2 Testing for errors W-1 Text editor T-3 Text, display format A-60 TEXT, in CPRLSRCE table C-131 The M-3 Time compilation and execution of D-52 computing for report C-62, C-173 of expiration A-32 print item display format A-67 TIME data type T-5 TIME literal D-11 TIME values in host variables H-6 LOAD command L-38 TIMEOUT option, CONTROL TABLE directive C-84 TIMESTAMP data type T-5 literal D-11 TIMESTAMP values in host variables H-6 LOAD c
Index U U UNAVAILABLE PARTITION option, CONTROL TABLE directive C-83 UNDERLINE_CHAR layout option R-10, U-1 UNION columns S-25 UNION operator and GROUP BY clause S-27 and HAVING clause S-27 and ORDER BY clause S-26 and shorthand views C-170 SELECT statement S-23 UNION ALL and associativity S-27 using S-25 UNIQUE clause, CREATE INDEX statement C-143 UNIQUE index and duplicate values C-143 and null values C-143 description of C-143 keys for I-9 UNIQUEENTRYCOUNT in COLUMNS table C-46 UNIQUEVALUE, in INDEXES
Index V USERID in PROGRAMS table P-31 in TABLES table T-2 User-defined primary key description of C-158, U-16 length limit L-13 specifying C-158 USESQLNULLS option COPY command C-117 LOAD command L-23 USING clause CURRENT_TIMESTAMP function E-8 EXECUTE statement E-7 USING DESCRIPTOR clause EXECUTE statement E-7 FETCH statement F-3 OPEN statement O-5 USING host variables clause O-5 USINGCATALOGNAME, in USAGES table U-15 USINGOBJNAME, in USAGES table U-15 USINGOBJTYPE, in USAGES table U-15 Utilities APPEND
Index V VERSION in CATALOGS table C-11 in VERSIONS table V-9 Version description of V-6 displaying E-3 information from FILEINFO F-16 information in catalogs V-9 numbers V-6 Version management Data Status Language D-67 DOWNGRADE CATALOG D-58 DOWNGRADE SYSTEM CATALOG D-61 GET CATALOG OF SYSTEM G-1 GET VERSION G-2 GET VERSION OF PROGRAM G-4 UPGRADE CATALOG U-11 UPGRADE SYSTEM CATALOG U-13 VERSION option, FILEINFO command F-16 VERSIONS catalog table V-9 VERSIONUPGRADETIME in CATALOGS table C-11 in VERSIONS t
Index W W WAIT IF LOCKED option, CONTROL TABLE directive C-82 Wait time for lock requests C-84 Warnings 1618 C-53, C-56, C-72, W-7 1619 C-53, C-56, C-70, C-71, C-72, W-7 8239 C-83 displaying S-42 WARNINGS option, SET SESSION command S-42 WHENEVER directive W-1 WHERE clause DELETE statement D-39 description of W-2 UPDATE statement U-3 WHERE CURRENT OF clause DELETE statement D-39 UPDATE statement U-4 Widows, preventing in report D-49 Width of report L-53 Wild-card characters in LIKE predicate L-3 in qualif
Index Special Characters >> prompt S-60 ? unnamed parameter P-13 HP NonStop SQL/MP Reference Manual—523352-013 Index-53
Index Special Characters HP NonStop SQL/MP Reference Manual—523352-013 Index-54
