NonStop SQL/MP Reference Manual Abstract This manual describes NonStop SQL/MP, the Tandem 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 D47 Supported Releases This manual supports D47.00 and all subsequent releases until otherwise indicated in a new edition.
Document History Part Number Product Version Published 100149 NonStop SQL/MP D30 December 1994 122053 NonStop SQL/MP D31 February 1996 131926 NonStop SQL/MP D44 June 1997 136367 NonStop SQL/MP D45 April 1998 142115 NonStop SQL/MP D47 June 1998 Ordering Information For manual ordering information: domestic U.S. customers, call 1-800-243-6886; international customers, contact your local sales representative.
NonStop SQL/MP Reference Manual Index What’s New in This Manual xxxi Manual Information xxxi New and Changed Information xxxi About This Manual xxxiii Related Manuals xxxiii Your Comments Invited xxxiv Notation Conventions xxxiv A.
A.
B. Contents Audited Tables A-70 AVG Function A-71 A-71 Considerations—AVG Examples—AVG A-72 B.
C.
C.
C.
C.
D. Contents Examples—CREATE VIEW C-160 CURRENT Function C-162 C-163 Examples—CURRENT CURRENT_TIMESTAMP Function C-163 Considerations—CURRENT_TIMESTAMP C-163 Examples—CURRENT_TIMESTAMP C-164 Cursors C-164 Cursor Position C-165 Cursor Stability C-165 C89 C-165 D.
D.
E.
E.
F. Contents F.
G. Contents Examples—FUP F-35 G.
I. Contents Examples—HISTORY H-5 Host Identifiers H-5 Host Programs H-5 Host Variables H-5 I.
J. Contents Examples—INITIALIZE SQL I-13 INSERT Statement I-14 Considerations—INSERT I-16 Examples—INSERT I-18 INTERVAL Data Type I-19 Considerations—INTERVAL Data Type Examples—INTERVAL Data Type INTERVAL Literals I-21 I-22 I-22 I-24 Examples—Interval Literals INVOKE Directive and Command I-24 Considerations—INVOKE I-28 Examples—INVOKE I-28 ISLACK File Attribute I-29 Considerations—ISLACK I-29 J. Joins J-1 Examples - Joins J-1 JULIANTIMESTAMP Function J-4 Examples—JULIANTIMESTAMP J-4 K.
M.
N. Contents MIN Function M-3 Considerations—MIN M-4 M-4 Examples—MIN MODIFY CATALOG Command M-4 Considerations—MODIFY CATALOG M-8 Examples—MODIFY CATALOG M-10 MODIFY LABEL Command M-11 Considerations—MODIFY LABEL M-16 Examples—MODIFY LABEL M-18 MODIFY REGISTER Command M-21 Considerations—MODIFY REGISTER M-24 Examples—MODIFY REGISTER Multibyte Character Sets M-23 M-24 System Default National Character Set M-25 N.
O.
P. Contents OVERFLOW_ CHAR OPTION O-9 Considerations—OVERFLOW_CHAR O-10 Examples—OVERFLOW_CHAR O-10 OWNER FILE ATTRIBUTE O-10 P.
Q.
R.
S. Contents RIGHT_MARGIN Option R-22 Considerations—RIGHT_MARGIN R-23 Examples—RIGHT_MARGIN ROLLBACK WORK Statement R-23 R-23 Considerations—ROLLBACK WORK R-24 R-24 Examples—ROLLBACK WORK ROWCOUNT Option R-25 Examples—ROWCOUNT R-25 S.
S.
S.
S.
T. Contents Considerations—SYSTEM Examples—SYSTEM S-91 S-92 System DEFINEs S-92 T.
V.
W. Contents Object Versions V-7 Program Versions V-7 Host language compiler versions VERSIONS Table Views V-8 V-8 V-9 VIEWS Table V-9 VOLUME Command V-10 Considerations—VOLUME Examples—VOLUME V-11 V-11 W. WHENEVER DIRECTIVE W-1 Considerations—WHENEVER Directive W-2 WHERE CLAUSE W-2 WINDOW OPTION W-2 Considerations—WINDOW Examples—WINDOW W-3 W-3 WITH SHARED ACCESS OPTION W-4 Considerations—WITH SHARED ACCESS W-5 Examples—WITH SHARED ACCESS W-8 Z.
Z.
Index Contents Examples—=_SQL_MSG_node =_SQL_RECGEN_node DEFINE Z-18 Z-18 Examples—=_SQL_RECGEN_node =_SQL_TM_node_vol DEFINE Z-18 Z-19 Considerations—=_SQL_TM_node_vol Z-19 Examples—=_SQL_TM_node_vol Z-20 Index NonStop SQL/MP Reference Manual—142115 xxx
What’s New in This Manual Manual Information NonStop SQL/MP Reference Manual Abstract This manual describes NonStop SQL/MP, the Tandem 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 D47 Supported Releases This manual supports D47.00 and all subsequent releases until otherwise indicated in a new edition.
New and Changed Information What’s New in This Manual NonStop SQL/MP Reference Manual—142115 xxxii
About This Manual This manual is the main reference text for NonStop SQL/MP, the Tandem relational database management system based on SQL. The manual includes reference information about the programmatic and conversational interfaces to NonStop SQL/MP, as well as information about utilities used to install and maintain a NonStop SQL/MP database. This manual is structured as an encyclopedia-style reference text.
Your Comments Invited About This Manual • The NonStop SQL/MP Programming Manual for C and NonStop SQL/MP Programming Manual for COBOL85 describe the NonStop SQL/MP programmatic interfaces for C and COBOL85, respectively. NonStop SQL Programming Manual for Pascal and NonStop SQL Programming Manual for TAL are the equivalent manuals for Pascal and TAL. Your Comments Invited After using this manual, please take a moment to send us your comments.
General Syntax Notation About This Manual each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: LIGHTS [ ON ] [ OFF ] [ SMOOTH [ num ] ] K [ X | D ] address-1 { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list may be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
Notation for Messages About This Manual Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must enter as shown. For example: "[" repetition-constant-list "]" Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted.
Notation for Messages About This Manual Bold Text. Bold text in an example indicates user input entered at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. Nonitalic text. Nonitalic letters, numbers, and punctuation indicate text that is displayed or returned exactly as shown. For example: Backup Up. lowercase italic letters. Lowercase italic letters indicate variable items whose values are displayed or returned.
Notation for Management Programming Interfaces About This Manual | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: Transfer status: { OK | Failed } % Percent Sign. A percent sign precedes a number that is not in decimal notation. The %þnotation precedes an octal number. The %Bþnotation precedes a binary number. The %Hþnotation precedes a hexadecimal number.
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 all include access options that control lock duration and that have row-level granularity.
Access Options on DDL Statements 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. For audited tables, REPEATABLE access uses shared locks for unmodified rows and exclusive locks for modified rows—but all locks are held by the TMF transaction and not released until the transaction ends.
Access Options on DDL Statements Summary: Effect of Access Options on 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 already being accessed is given access or placed in a wait queue) depends on the purpose of the access (to read data or to update data), on the access option, and on whether SQL uses VSBB for the access.
ADD DEFINE Command 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.) ADD DEFINE { define } { ( define [ , define ] ... ) } [ , LIKE other-define ] [ , attr value ] ... ; define is a name for the new DEFINE; the name cannot be the same as the name of an existing DEFINE.
Considerations—ADD DEFINE Considerations—ADD DEFINE • • • • You cannot use ADD DEFINE unless the DEFMODE setting is ON. The OSS command add_define automatically sets DEFMODE ON, but the TACL form of ADD DEFINE does not. Before issuing a TACL ADD DEFINE command, set DEFMODE ON. A DEFINE stays in effect until you change it, delete it, or exit the SQLCI session in which you created it. Attributes you specify in an ADD DEFINE command do not become part of the working attribute set.
AGGREGATE Functions AGGREGATE Functions NonStop SQL/MP provides the following 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. SUM Function Computes the sum of a set of numbers. For more information, see the entry for a specific function.
Considerations—ALLOCATE For ALTER TABLE or ALTER INDEX, ALLOCATE allocates new extents until the total of new and existing extents equals the specified number. DEALLOCATE frees all unused allocated extents (that is, all allocated extents beyond the extent that contains the end-of-file). DEALLOCATE is valid only for ALTER TABLE or ALTER INDEX.
Considerations—ALTER CATALOG The clauses set the following security-related file attributes for the catalog: CLEARONPURGE Controls disk erasure when files are dropped NOPURGEUNTIL Sets date after which drop is allowed OWNER Specifies owner SECURE Sets Guardian security string For more information, see the System Catalog on page S-91 entry or an entry for a specific attribute.
Examples—ALTER CATALOG independently from the other tables in the system catalog. You can use ALTER TABLE to set the security for SQL.CATALOGS. • Relationship between ownership and security Changing the OWNER attribute of a catalog affects the interpretation of the SECURE file attribute, because authorization is determined at run time using the current group and owner. If another process is using a catalog when the owner changes, the process might not be able to reaccess the catalog after the change.
Examples—ALTER COLLATION ALTER COLLATION requires read and write authority for the collation and for the catalog in which the collation is registered. 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 COLLATION 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.
ALTER DEFINE Command ALTER DEFINE Command ALTER DEFINE is an SQLCI command that changes the attributes of DEFINEs in the current SQLCI session. (ALTER DEFINE is similar to the TACL command ALTER DEFINE.) ALTER DEFINE define-list { , attr value } ... ; { , RESET reset-list} define-list is: { { { { define } ( define [ , define ] ... ) } ** } =* } reset-list is: { attr } { ( attr [ , attr ]... ) } define is the name of an existing DEFINE to alter. ** or =* specifies all DEFINEs.
Examples—ALTER DEFINE • • • ALTER DEFINE affects only existing DEFINEs, not the working attribute set. (SET DEFINE modifies the working attribute set.) Attributes are altered in the order in which they are specified. If the value of an attribute is a Guardian name or subvolume name, the name is expanded immediately using the current default node, volume, and subvolume. • You cannot alter an attribute unless it is valid for the class of the DEFINE.
ALTER INDEX Statement • Moving part of an index partition into another existing partition ALTER INDEX name { { { { { { { { { { { { { { { { { { { { { { { { { { { { { RENAME new-name {| security-spec |} {| attribute-spec |} DROP PARTITION part-name {[FROM KEY val [UP TO LAST KEY]] { TO dest-part [ move-spec ] { [WITH SHARED ACCESS [wsa-spec]] { {TO dest-part [ move-spec ] { WITH SHARED ACCESS [wsa-spec] [PARTONLY] MOVE{ {([FROM FIRST KEY] UP TO KEY val { TO dest-part [ move-spec ], { [WITH SHARED ACCESS[ws
ALTER INDEX Statement move-spec is: {| {| {| {| {| {| {| CATALOG catalog-name PHYSVOL volume-name EXTENT { size1 | ( size1 [, size2 ] ) } MAXEXTENTS int DSLACK percent ISLACK percent SLACK percent |} |} |} |} |} |} |} wsa-spec is: {| {| {| {| {| {| NAME operation-name |} |} REPORT [ TO collector | ON | OFF ] |} |} { COMMIT [ WORK ] [commit-options] } |} { ROLLBACK [ WORK ] } |} add-spec is: {| {| {| {| {| {| FIRST KEY { val | ( val [,val ] ...
ALTER INDEX Statement security-spec sets the following security-related file attributes for index name: CLEARONPURGE Controls disk erasure when index is dropped NOPURGEUNTIL Sets date after which drop is allowed SECURE Sets Guardian security string For more detail, see the entry for a specific attribute.
ALTER INDEX Statement {[FROM KEY val [UP TO LAST KEY]] } { TO dest-part [ move-spec ] } { [WITH SHARED ACCESS [wsa-spec]] } { } {TO dest-part [ move-spec ] } { WITH SHARED ACCESS [wsa-spec] } [PARTONLY] MOVE { } {([FROM FIRST KEY] UP TO KEY val } { TO dest-part [ move-spec ] , } { [WITH SHARED ACCESS [wsa-spec]])} { } {([FROM FIRST KEY] UP TO KEY val } { TO dest-part [ move-spec ] , } { FROM KEY val [UP TO LAST KEY] } { TO dest-part [ move-spec ]) } } } } } } } } } } } } } } } } moves a specified portion
ALTER INDEX Statement Check that the values you specify result in an index or partition large enough to hold data being moved from an existing index or partition. Error 45 (File is full) occurs if the new partition does not have enough space to store the rows transferred. The default is the value of the corresponding attribute for the index or partition being moved or split. For more information, see the entry for a specific attribute.
Considerations—ALTER INDEX For more detail about ALLOCATE, MAXEXTENTS, or RESETBROKEN, see ALLOCATE File Attribute on page A-6, MAXEXTENTS File Attribute on page M-2, or RESETBROKEN File Attribute on page R-22. For more information about INCOMPLETE SQLDDL OPERATION, see Completing ALTER INDEX Operations in Considerations—ALTER INDEX on page A-18. ADD PARTITION new-part add-spec adds a partition named new-part to index name using the options specified in add-spec.
Considerations—ALTER INDEX 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. (See DDL (Data Definition Language) Statements on page D-19 for more information.) Additional authorization and access requirements that exist for some ALTER INDEX operations are described in the following subsections.
Considerations—ALTER INDEX name specifies the partition being moved. You can specify a simple move with or without the WITH SHARED ACCESS option. ° A merge operation moves the partition into another existing partition, deleting the original partition: MOVE TO dest-part WITH SHARED ACCESS name specifies the actual partition being moved. The WITH SHARED ACCESS option is required.
Considerations—ALTER INDEX 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 combination of node name and volume name, specified as dest-part, must be unique for each partition of the same object. The two-way split does not support the WITH SHARED ACCESS option. ° A one-way move operation moves the first or last part of a partition to its logically adjacent partition, leaving the other part in the existing partition.
Considerations—ALTER INDEX (See the OPEN ACCESSED PARTITIONS clause under CONTROL TABLE Directive on page C-72 for information about specifying on-demand opens.) Without WITH SHARED ACCESS, you might want to stop activity on a table when you intend to move or split one of the partitions of an index to the table. If SQL statements refer to the source partition and the partition is moved, you might need to change your program or DEFINEs to reference the new location.
Considerations—ALTER INDEX Lengthy operations that use WITH SHARED ACCESS might require an operator to mount tapes of TMF audit dumps. (Requests to mount TMF audit dump tapes for WITH SHARED ACCESS operations are not distinguishable from other requests to mount TMF audit dump tapes. Such requests are generally sent to an operator's console. SQL does not return information about such requests to the terminal or process that started the operation.
Examples—ALTER INDEX version 1 of NonStop SQL/MP software, error 1125 (Incompatible remote system) occurs. You cannot use the WITH SHARED ACCESS option with a split, merge, or move request unless each source object and each target object resides on a node running a version of NonStop SQL/MP software (315 or later) that supports the specific type of split, merge, or move operation.
ALTER PROGRAM Statement processes. The CONTINUE statement starts the commit phase, directing SQL to return control to the user if a retryable error occurs during the phase.
Considerations—ALTER PROGRAM RENAME new-name renames the program and changes all references to the old name in the affected catalog to the new name; new-name is a Guardian name or DEFINE. The fully 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 ServerWare 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.
Examples—ALTER PROGRAM The following dependencies apply when you alter program security attributes: OWNER and PROGID Specifying OWNER turns off the PROGID attribute. OWNER and SECURE A change in the ownership of a program affects the interpretation of the security string. The security string is interpreted at run time against the new owner and, if applicable, a new group.
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 } DROP PARTITION part-name {[FROM KEY val [UP TO LAST KEY]]} { TO dest-part [ move-spec ] } { [WITH SHARED ACCESS [wsa-
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 DSLACK percent ISLACK percent
ALTER TABLE Statement 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. name and new-name must have the same node and volume name when expanded.
ALTER TABLE Statement Prohibiting similarity checks (SIMILARITY CHECK DISABLE) on a table that has version 310 decreases the version of the table (and the version of objects that depend on the table). ADD COLUMN col-name data-type [ DEFAULT def [ NOT NULL ] ] [ HEADING string | NO HEADING ] adds a column named col-name to table name, including all its partitions, if any. The data-type and the DEFAULT clause specify the data type and default value for the new column.
ALTER TABLE Statement to drop a partition that contains data, use the PARTONLY option of PURGEDATA. See PURGEDATA Command on page P-36 for a discussion of index issues.
ALTER TABLE Statement Make sure that the values you specify result in a table or partition large enough to hold data being moved from an existing table or partition. Error 45 (File is full) occurs if the new partition does not have enough space to store the rows transferred. The default is the value of the corresponding attribute for the table or partition being moved or split. For more information, see the entry for a specific attribute.
ALTER TABLE Statement For more detail about ALLOCATE, MAXEXTENTS, or RESETBROKEN, see the ALLOCATE File Attribute on page A-6, MAXEXTENTS File Attribute on page M-2, or RESETBROKEN File Attribute on page R-22 entries. For more information about INCOMPLETE PARTITION CHANGE, see the discussion about completing operations in Considerations—ALTER TABLE on page A-35. ADD PARTITION new-part add-spec adds a partition named new-part to table name using the options specified in add-spec.
Considerations—ALTER TABLE reuse-spec specifies FIRST KEY for a partition reused with the ADD PARTITION clause. The FIRST KEY clause specifies the primary or clustering key values for the first key allowed in a reused partition of a table with key-sequenced file organization. It is required for key-sequenced files. val is a list of comma-separated literals (one for each column in key) that specifies the beginning key value for the partition to be reused.
Considerations—ALTER TABLE ° Changing the OWNER for a table automatically changes the OWNER of indexes and protection views defined on the table. ° Changing the SECURE attribute for a table automatically changes the SECURE attribute of indexes defined on the table.
Considerations—ALTER TABLE ° ALTER TABLE ADD COLUMN automatically requests an exclusive table lock on the table. If data in the table is already locked, ALTER TABLE waits until the request is granted or a timeout occurs. ° A new column appears as the last column of the table. In existing rows of the table, the new column takes on its default value unless it has a date-time data type with the default set to CURRENT or SYSTEM.
Considerations—ALTER TABLE ° A one-way split moves the first or last part of a partition to a new partition, leaving the remaining part in the existing partition: MOVE FROM KEY val TO dest-part [WITH SHARED ACCESS] MOVE UP TO KEY val TO dest-part WITH SHARED ACCESS In a one-way split, name specifies the table for the operation. The partition that is split is the one whose data range includes the key val, even if the specified partition does not actually contain a row with that key.
Considerations—ALTER TABLE A one-way move is similar to a one-way split, but moves data to an existing partition instead of a new partition. In a one-way move, name specifies a valid partition of the table. NonStop SQL/MP determines the actual source partition during execution. The WITH SHARED ACCESS option is required. After a successful one-way move operation, run FUP RELOAD to reclaim unused disk space. For more information about FUP RELOAD, see the File Utility Program (FUP) Reference Manual.
Considerations—ALTER TABLE successfully acquire the locks while other transactions are active and cause those transactions to time out. If SQL statements refer to the source partition and the partition is moved with a simple move operation, you might need to change your program or DEFINEs to reference the new location.
Considerations—ALTER TABLE 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.
Considerations—ALTER TABLE If ALTER TABLE fails during a merge or 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 issue an ALTER TABLE name PARTONLY RECOVER INCOMPLETE SQLDDL OPERATION request, followed by FUP RELOAD. ° The F flag, UNRECLAIMED FREE SPACE, indicates that you need to request a FUP RELOAD operation to reclaim space on disk.
Considerations—ALTER TABLE ° Tables and indexes using extended arrays require a version 320 or later catalog. SQL DML and DDL statements on tables and indexes with extended arrays can only be performed from nodes running version 320 or later. Otherwise, SQL returns an error. When you modify the partition array of a table, all programs that reference the table are invalidated. Recompile the programs with NonStop SQL/MP version 320 or later.
Examples—ALTER TABLE Examples—ALTER TABLE • The following example alters the security and owner for a table: ALTER TABLE \SYS1.$VOL2.PERSNL.EMPLOYEE SECURE "nunu" OWNER 12,101; • The following example alters the date that a table can be purged: ALTER TABLE SALES.ORDERS NOPURGEUNTIL JAN 01 1995; • The following example turns on TMF auditing (perhaps after temporarily setting the NO AUDIT attribute to perform a LOAD operation) for a table: ALTER TABLE SALES.
ALTER VIEW Statement • The following statement reuses a partition named $DISK5.SALES.ORDERS, in which the new primary first key is 5000 after the partition is reused: ALTER TABLE $DISK1.SALES.ORDERS REUSE PARTITION $DISK5.SALES.ORDERS FIRST KEY “5000” 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 Changing a column heading in a view does not change the corresponding heading in any view previously created on that view. A view inherits headings from underlying tables and views when you create it, but the headings then exist independently. SIMILARITY CHECK { ENABLE | DISABLE } authorizes or prohibits similarity checks on a protection view. (You cannot specify this clause for a shorthand view.
Examples—ALTER VIEW The following dependencies apply when you alter view security attributes: OWNER and SECURE A change in the ownership of a view affects the interpretation of the security string. The security string is interpreted at run time against the new owner and, if applicable, a new group. If another process is using a view when the owner or security string is changed, the process might not be able to access the view after the view is closed.
Considerations—APPEND out-file is the name (or equivalent DEFINE) of an existing entry-sequenced or keysequenced SQL table to which to append data. Neither type of file can have indexes. recovery-file is the name (or equivalent DEFINE) of a disk file APPEND creates to hold data for recovery in case of interruption or failure in restoring out-file to its pre failure state. RECOVERYFILE is a required parameter. append-option is one or more options that configure the APPEND operation.
Considerations—APPEND If it is not partitioned, a key-sequenced target table is unavailable to applications during the APPEND operation. If the table is partitioned and you do not specify PARTONLY, the partition into which the first new row was added and all subsequent partitions are unavailable. The whole table is unavailable for a short time at the start of APPEND, so this is not really an online operation.
Considerations—APPEND ° A CPU or process failure interrupts the APPEND operation so that it cannot terminate gracefully. If the APPEND operation is interrupted and cannot restore the target table to its original state, it does not purge recovery-file. In this case, the target table or partition remains unavailable to applications until some recovery is performed. To recover from an interrupted APPEND operation and complete the operation, use the APPENDRESTART command.
APPENDCANCEL Command unless you specify the SORTED option. All considerations listed for LOAD that do not require an Enscribe out-file are true for APPEND. Following are the major differences between APPEND and LOAD: ° APPEND is typically used to add data to a file that already contains data. LOAD is typically used to enter initial data into an empty file. ° APPEND does not erase or overwrite existing records. LOAD erases or overwrites existing records.
Considerations—APPENDCANCEL PARTONLY You must use the PARTONLY option if PARTONLY was specified in the original APPEND command from which this APPENDCANCEL command is recovering. Considerations—APPENDCANCEL • Authorization requirements APPENDCANCEL has the same authorization requirements as the APPEND command. See APPEND Command on page A-47 for more information about authorization requirements for APPENDCANCEL.
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 then proceeds with the APPEND operation, adding data to the end of the target table.
AS Clause APPENDRESTART has the same authorization requirements as the APPEND command. See APPEND Command on page A-47 for more information about authorization requirements for APPENDRESTART. • APPENDRESTART operations APPENDRESTART verifies that the information in recovery-file accurately describes both the source file and the target table (or partition). Using the information in recovery-file, APPENDRESTART restores the target table to the state it was in before the APPEND operation began.
AS Clause 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 ] ... ] } [ modifier [, modifier ] ... ] "]" } ( scale-sign-descriptor } display-descriptor )" } display-descriptor specifies a format.
Display Descriptors for Character Items 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| |.
Display Descriptors for Numeric Items Display Descriptors for Numeric Items Form and Usage Example Value Printed F8.4 F8.4 F8.4.2 “[FL'*']F8.2” 123.4567 0.000123 4.56789 123.4567 |123.4567| | 0.0001| |-04.5679| |**123.46| 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.00| |$ 92000| Fw.d [.
Modifiers Modifiers Form and Usage Example Value Printed “[BZ] F8.2” “[BZ] F8.2” .00 123.00 | | |123.00| “[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.00 |++| |>>>>>| “[LJ]A8” “[LJ]A3” SQL OREGON |SQL |ORE| BZ Prints a blank field if the value is zero. F Specifies whether data in C format is split at a blank if possible.
Decorations Form and Usage Example Value Printed “[RJ]A8” “[RJ]A3” SQL OREGON | SQL| |GON| “[SS'.:']F6.2” “[SS'.,']F8.2” “[SS'9X'] M” 12.45 12345.67 103191 |12:45| |12345,67| |10/31/1991 | RJ Right justify in display format width. Applies only to A descriptors. Note that data with trailing blanks won't look justified. SS'ss' Changes a descriptor symbol. First character in pair is old symbol, second is new. Use to change mask descriptor symbols (9,Z,V,.
Examples—AS Rules for Using Decorations • • • • • • Specify decorations immediately before the descriptors they modify. Enclose the decorations and the modified descriptors in quotation marks (“”). Separate multiple decorations by commas and enclose decorations and modifiers in brackets. With the overflow condition (O), use only the form location An. The default overflow character is the current OVERFLOW_CHAR option. Decoration OC overrides the default but does not change it.
Examples—AS Sample negative display: | • <240.
AS DATE/TIME Clause MPF specifies that if the value is negative or positive (MP), the print location is set immediately to the left of the value (F). 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.
AS DATE/TIME Clause Format Values M 1, 2, ... 12 MB2 1, 2, ... 12 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, ... "time-string" is a string that specifies how to format a time value.
Examples—AS DATE/TIME Format Values S2 00, 01, ... 59 SB2 0, 1, ... 59 C2 00, 01, ... 99 CB2 0, 1, ... 99 T3 000, 001, ... 999 TB3 0, 1, ... 999 IN LCT specifies local civil time, the default.
ASCII Character Set Octal Char Left Right Hex Dec Meaning Ordinal HT 004400 000011 9 9 Horizontal tabulation 10 LF 005000 000012 A 10 Line feed 11 VT 005400 000013 B 11 Vertical tabulation 12 FF 006000 000014 C 12 Form feed 13 CR 006400 000015 D 13 Carriage return 14 SO 007000 000016 E 14 Shift out 15 SI 007400 000017 F 15 Shift in 16 DLE 010000 000020 10 16 Data link escape 17 DC1 010400 000021 11 17 Device control 1 18 DC2 011000 000
ASCII Character Set Octal Char Left Right Hex Dec Meaning Ordinal ) 024400 000051 29 41 Closing parenthesis 42 * 025000 000052 2A 42 Asterisk 43 + 025400 000053 2B 43 Plus 44 , 026000 000054 2C 44 Comma 45 - 026400 000055 2D 45 Hyphen (minus) 46 .
ASCII Character Set Octal Char Left Right Hex Dec Meaning Ordinal I 044400 000111 49 73 Uppercase I 74 J 045000 000112 4A 74 Uppercase J 75 K 045400 000113 4B 75 Uppercase K 76 L 046000 000114 4C 76 Uppercase L 77 M 046400 000115 4D 77 Uppercase M 78 N 047000 000116 4E 78 Uppercase N 79 O 047400 000117 4F 79 Uppercase O 80 P 050000 000120 50 80 Uppercase P 81 Q 050400 000121 51 81 Uppercase Q 82 R 051000 000122 52 82 Uppercase R 83
AUDIT File Attribute Octal Char Left Right Hex Dec Meaning Ordinal i 064400 000151 69 105 Lowercase i 106 j 065000 000152 6A 106 Lowercase j 107 k 065400 000153 6B 107 Lowercase k 108 l 066000 000154 6C 108 Lowercase l 109 m 066400 000155 6D 109 Lowercase m 110 n 067000 000156 6E 110 Lowercase n 111 o 067400 000157 6F 111 Lowercase o 112 p 070000 000160 70 112 Lowercase p 113 q 070400 000161 71 113 Lowercase q 114 r 071000 000162 72
Considerations—AUDIT See TMF Transactions on page T-5 for more information. { AUDIT | NO AUDIT } The table default is AUDIT. Considerations—AUDIT • Indexes An index always has the same AUDIT attribute as its underlying table. If you change the AUDIT attribute for a table, SQL automatically changes the AUDIT attribute for its indexes. • Views A protection view has the same AUDIT value as the underlying table.
Considerations—AUDITCOMPRESS Compressed audit records omit unchanged columns from the before and after images of updated rows. Uncompressed audit records allow you to read complete rows in the audit trail but require more space. { AUDITCOMPRESS | NO AUDITCOMPRESS } The table default is AUDITCOMPRESS. The index default is the table value at index creation.
AVG Function See TMF Transactions on page T-5 for more information. AVG Function AVG is a function that computes the average of a set of numbers. The data type of the result depends on the data type of the argument. If the argument is an exact numeric type, the result is LARGEINT. If the argument is FLOAT, REAL, or DOUBLE PRECISION type, the result is DOUBLE PRECISION. The scale of the result is the same as the scale of the argument. If the argument has no scale, the result is truncated.
Examples—AVG Examples—AVG • The following SELECT statement returns the average salary from the SALARY column of the PERSNL.EMPLOYEE table: >> SELECT AVG (SALARY) FROM PERSNL.EMPLOYEE; (EXPR) --------------------48784.65 --- 1 row(s) selected. • The following SELECT statement returns the average unique salary from the SALARY column of the PERSNL.EMPLOYEE table: >> SELECT AVG (DISTINCT SALARY) FROM PERSNL.EMPLOYEE; (EXPR) --------------------52664.21 --- 1 row(s) selected.
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 magnetic tape to disk.) See the Guardian Disk and Tape Utilities Reference Manual for more information about BACKUP. 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 NonStop SQL/MP programming manual for your host language.
Examples—BEGIN WORK Examples—BEGIN WORK • The following example uses BEGIN WORK in SQLCI to group three separate statements that update the database into a single TMF transaction: >> VOLUME SALES; >> BEGIN WORK; >> INSERT INTO ORDERS VALUES (124, 860323, 860330, 75, 7654); --- 1 ROW(S) INSERTED. >> INSERT INTO ODETAIL VALUES (124, 4103, 25000, 2); --- 1 ROW(S) INSERTED. >> UPDATE INVENT.PARTLOC SET QTY_ON_HAND = QTY_ON_HAND -2 +> WHERE PARTNUM = 4103 AND LOC_CODE = "K43"; --- 1 ROW(S) UPDATED.
Examples—BETWEEN • • The data type of the result of an expression in one row-value-spec must be compatible with the data types of the results of the two expressions in the same ordinal position in the others.
Considerations—BLOCKSIZE block size greater than 4096 for a CREATE TABLE or CREATE INDEX statement, SQL issues an error. Considerations—BLOCKSIZE • Recommendations for block size For sequential processing, use the largest block size. For key-sequenced tables, avoid small block sizes (less than 4096 bytes) if the size of the table causes the number of index levels to increase over the number required for 4096-byte blocks. Additional index levels can reduce online performance.
Considerations—BREAK FOOTING Considerations—BREAK FOOTING • BREAK ON required If you define break footings, you must enter a BREAK ON command that includes the break column identifier in each BREAK FOOTING command before you list any output. • One footing per break column Only one BREAK FOOTING command is in effect for each break column. If you enter another BREAK FOOTING command for the same break column, the most recent command replaces the previous BREAK FOOTING command for that column.
Examples—BREAK FOOTING Examples—BREAK FOOTING • The following 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 To sort break columns, include an ORDER BY clause in the associated SELECT statement. Examples—BREAK ON • The following 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 • The following example groups detail lines by monthly salary and (within salary groups) by job code and department number. When finished, enter CANCEL at the select-in progress prompt (S>): >> SET LIST_COUNT 0; >> SELECT DEPTNUM, JOBCODE, SALARY/12 +> FROM PERSNL.EMPLOYEE +> WHERE SALARY > 20000 +> ORDER BY 3, 2, 1; S> BREAK ON COL 3, JOBCODE, DEPTNUM; S> LIST NEXT 5; DEPTNUM JOBCODE (EXPR) ------- ------- -------------------- 3200 300 1833.
Considerations—BREAK TITLE print-item specifies the contents and format of items to print as the break title. print-item is the same as described under DETAIL, except that it cannot include the HEADING, NOHEAD, or NAME clauses. If print-item is a column identifier, the column value used is the one from the first row in the new group. CENTER centers each line of the break title between the left and right margins. If you omit CENTER, the break title is positioned at the left margin.
BUFFERED File Attribute Before the first detail line is printed, and each time the value of DEPTNUM changes, the break title defined in the following command appears in the report: S> BREAK TITLE D.
Considerations—BUFFERED Buffering can improve transaction times by reducing the number of writes required and by deferring writes so that the disk process can write a string of blocks in a 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.
Considerations—BUFFERED NonStop SQL/MP Reference Manual—142115 B -14
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 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 . . . WHEN search-condition-n THEN [ { ELSE result-x | NULL } ] END { result-1 | NULL } { result-2 | NULL } { result-3 | NULL } { result-n | NULL } or CASE target-value WHEN value-1 THEN WHEN value-2 THEN WHEN value-3 THEN . . .
Considerations—CASE Expression value-1 through value-n specifies a value associated with result-n. If the value matches targetvalue, the CASE expression returns the associated result. Considerations—CASE Expression • • • • • The data type of the CASE expression depends on the data types of the result expressions. If the results all have the same data type, the CASE expression adopts that data type. If all results are untyped, the CASE expression has a CHAR (256) data type.
Examples—CASE Expression Examples—CASE Expression • The following 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; • The following example returns last_name, first_name, and a value based on salary that depends on the value of employee.dept_num: SELECT last_name, first_name, CASE WHEN dept_num = 9000 THEN salary * 1.
Considerations—CAST cannot be a date-time or INTERVAL data type. (To associate a date-time or INTERVAL data type with a parameter, use the TYPE AS clause described under PARAMETERS.) The value you specify for the parameter must be compatible with the data type you specify with the CAST function. Considerations—CAST • Null values If the parameter value is NULL, SQL sets the target value to NULL, regardless of the data type.
Considerations—CATALOG Considerations—CATALOG • • Whenever the default catalog is empty, SQL uses the default subvolume for the default catalog. Effect on prepared statements Unless a CONTROL QUERY BIND NAMES AT EXECUTION directive is in effect when a PREPARE executes, a prepared statement uses the node, volume, and catalog defaults in effect at the time of the PREPARE and is not affected by a change in the default catalog.
Catalogs You can create your own tables, indexes, views, and files on a subvolume that includes an SQL catalog, but it is better to create such objects in other subvolumes, if a later release of NonStop SQL/MP could add new tables or indexes to the catalog. (The UPGRADE CATALOG command would be unable to upgrade your catalog if a new catalog table had the same name as a table or file you created on the catalog subvolume.) Each catalog has a version and a format version associated with it.
Operations on Catalog Tables Operations on Catalog Tables You create a catalog with CREATE CATALOG. You drop a catalog (after dropping all user-defined collations, indexes, programs, tables, and views that the catalog describes) with DROP CATALOG. You can alter the security of an entire catalog with ALTER CATALOG, or alter the security of the CATALOGS, PROGRAMS, TRANSIDS, or USAGES catalog tables with ALTER TABLE.
CATALOGS Table Caution. Normally, DELETE, INSERT, and UPDATE statements do not work on catalog tables, but a licensed SQLCI2 process (or a licensed SQL program file) can use any DML operation on catalog tables, as described in the NonStop SQL/MP Installation and Management Guide. Only the most extreme situations should require the use of a licensed SQLCI2 process, because such operations can be extremely dangerous to the consistency of the database and the data dictionary.
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 } Considerations—CENTER_REPORT • Centering a report If you specify ON, the report writer centers the whole report as a block of text.
Character Expressions The maximum length of a character column depends on whether the data type is fixedlength or variable-length, whether the associated character set is single-byte or doublebyte, and on the file organization of the file that contains the column: Data Type Key-Sequenced Relative or Entry-Sequenced Single-byte unvarying 4061 4072 Single-byte VARYING 4059 4070 Double-byte unvarying 2030 2036 Double-byte VARYING 2029 2035 Each variable-length character data item requires two b
Character Expressions A character expression consists of one or more operands connected by string operators, as shown in the following diagram. string-operand [ [ string-operator string-operand ] ...
Considerations—Character Expressions Considerations—Character Expressions • Guidelines for using character expressions in SQL statements Character expressions can appear at any place where a string literal, parameter, column, or host variable can appear.
Considerations—Character Expressions that was defined with a COLLATE FRENCH clause, the collation FRENCH is implicitly associated with the expression even though no COLLATE clause appears in the expression. A character expression is explicitly associated with a collation if the expression itself includes the COLLATE clause. An explicit association with a collation overrides an implicit association with a collation in the same expression.
Examples—Character Expressions sorts as string of u and e \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; numerics; hexdigits; specials alphas ;...;;\ ;...;;\ \d192;...;\d214;\d216;...;\d246;\d248;...;\d255 numerics <0>;<1>;\d050;\x33;\ <4>;...;<9> hexdigits <0>;...;<9>;\ ;...;;\ ;...
Character Sets Character Sets NonStop SQL/MP allows you to associate one of the following character sets with a column, literal, host variable, or parameter: ISO 8859/1 through ISO 8859/9 Kanji KS C5601 You can also define a collation that uses any of the nine ISO 8859 character sets and associate the collation with a column, literal, host variable, or parameter of the same character set. (You cannot define a collation that uses the Kanji or KS C5601 characters sets.
Kanji Character Set Though the nine ISO 8859 character sets are not documented in full in the NonStop SQL/MP documentation, the entry ASCII Character Set on page A-64 lists the ASCII characters common to all nine ISO 8859 sets. Kanji Character Set The Kanji character set (also known as the Shift JIS character set and—within Tandem—as the Tandem Kanji character set) is a double-byte character set originally developed for CP/M microcomputers in Japan and later adopted for use on MS-DOS systems.
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 • The following two examples use a table created as follows: CREATE TABLE EMPLOYEE (EMPNAME CHAR(20), ADDRESS VARCHAR(100)); INSERT INTO EMPLOYEE VALUES ("Robert Smith", "19333 Vallco Parkway "); • The following example returns 20: CHAR_LENGTH ( EMPNAME ) • The following 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 progr
Considerations—CLEANUP ! (either before or after qualified-fileset-list) directs CLEANUP to delete all objects in the specified filesets without prompting for confirmation. If you omit the exclamation point when you use CLEANUP interactively, the following prompts normally appear: DO YOU WISH TO CLEANUP THE ENTIRE FILESET ... (name of fileset) ...
Considerations—CLEANUP After deleting each object, CLEANUP displays a message indicating the object and catalog entry that was deleted. • Transactions, breaks, and failures You cannot use CLEANUP within a user-defined TMF transaction. SQL automatically starts a transaction for each catalog description and file label purged with CLEANUP, so only the deletion of the last SQL object (or partition) is undone if CLEANUP fails before the deletion is committed.
CLEANUP Exception Cases object that depends on a collation but not the collation, CLEANUP purges the dependent object and deletes the relationship to the collation. • CLEANUP and remote objects 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 the following command to purge SQL objects and catalogs: SQLCI CLEANUP $*.*.
Examples—CLEANUP • • Purge a catalog's name entry in the system catalog's CATALOGS table when a file system 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) will 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 the following: >> 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 as follows: >> CLEANUP $VOL1.PERSNL.
CLOSE Statement CLOSE Statement CLOSE is a DML and dynamic SQL statement that closes a cursor in a host program. After the CLOSE executes, the result table for the cursor (the output that results from the execution of the SELECT for the cursor) no longer exists. CLOSE { cursor } { :cursor-variable } 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.
Examples—CLOSE Examples—CLOSE • The following program fragment declares and opens a cursor, uses FETCH to retrieve data, then closes the cursor: EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT COL1, COL2, COL3 FROM =PARTS WHERE COL1 >= :HOSTVAR1 ORDER BY COL1 BROWSE ACCESS; EXEC SQL OPEN CURSOR1; EXEC SQL FETCH CURSOR1 INTO :HOSTVAR1, :HOSTVAR2, :HOSTVAR3; EXEC SQL CLOSE CURSOR1; Clustering Keys A clustering key is the user-defined portion of a primary key that is determined partly by the user and partly
Examples—CLUSTERING KEYS Examples—CLUSTERING KEYS • The following statement declares a table with a clustering key: CREATE TABLE CK (SYS_ID SMALLINT, CPU SMALLINT, PIN SMALLINT, PROG_NAME VARCHAR(34)) CLUSTERING KEY (SYS_ID, CPU, PIN); 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.
Comment and Escape Characters in Collation Definitions section, and the LC_TDMCODESET section), and ending with examples and special considerations. Remember that keywords shown in uppercase must be entered in uppercase, and keywords shown in lowercase must be entered in lowercase.
The LC_COLLATE Section of a Collation Definition To specify one of these characters as a character in a collation (rather than as a syntactic element in the collation definition language), precede the character with the escape character. If you redefine the comment or escape character as one of the punctuation characters just listed, an error occurs if you later use portions of the collation definition language that include these characters as syntactic elements.
The LC_COLLATE Section of a Collation Definition You can specify multiple collating-element clauses (subject to the general limits described in the Considerations sub-section of this entry), but each one must appear on a separate line and all such clauses must precede the order_start clause. is a stream of 2 to 30 characters, enclosed in a set of angle brackets, that represents the new element.
The LC_CTYPE Section of a Collation Definition If element is an ellipsis, it specifies the ordered series of characters in the character set between the preceding element specified in the collation definition and the following element in the collation definition. Neither the preceding element nor the following 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).
The LC_CTYPE Section of a Collation Definition 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 ] ... class-name [class-element[; class-element] ... ] ] [ toupper (lower,upper) [; (lower,upper) ] ... ] END LC_CTYPE LC_CTYPE starts an LC_CTYPE section. charclass class-name [ ; class-name ] ... specifies names for up to 100 user-defined character classes.
The LC_TDMCODESET Section of a Collation Definition char is defined in the discussion of the LC_COLLATE section. lower specifies a lowercase char; upper specifies an uppercase char. If neither char in a pair has appeared previously in the toupper clause, the first character in the pair is treated as a lowercase character in the collation and the second character in the pair is treated as the corresponding uppercase character.
Considerations—Collation Definitions 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. LC_TDMCODESET { { { { { { { { { { ISO88591 ISO88592 ISO88593 ISO88594 ISO88595 ISO88596 ISO88597 ISO88598 ISO88599 UNKNOWN } } } } } } } } } } END LC_TDMCODESET LC_TDMCODESET starts an LC_TDMCODESET section. ISO88591 ...
Examples—Collation Definitions This requirement exists because Pathmaker generates SQL queries in the following format: SELECT x FROM t WHERE col1 >= :h1 AND col1 <= :h2 where :h1 is a value entered from a screen, and :h2 is the same value padded with binary 1s. Such a query depends on binary 1 having the maximum character value (that is, having the integer value 255 and being positioned last in the order list).
Examples—Collation Definitions The first ellipsis specifies that all of the characters between the letters a and z have unique weights equal to the relative order of the characters. The second ellipsis specifies that all of the characters between the letters A and E have the same weight as the letter a. The third ellipsis specifies that all of the characters between the letters E and N have unique weights equal to the relative order of the characters. LC_COLLATE order_start forward ... ...
Examples—Collation Definitions \d200 ... \d203 \d232 ... \d235 \d204 ... \d207 \d236 ... \d239 \d209 \d241 \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 ...
Examples—Collation Definitions \d228 "" \d214 "" \d246 "" \d220v "" \d252 "" \d223 "" \d163 \d163 \d215 \d215 \d159 \d159 \d170 \d170 \d186 \d186 UNDEFINED IGNORE order_end END LC_COLLATE LC_CTYPE charclass alphas numerics hexdigits # # # # # # # # a-umlaut sorts as string of a and e O-umlaut sorts as string of o and e o-umlaut sorts as string of o and e U-umlaut sorts as string of u and e u-umlaut sorts as string of u and e sharp-s sorts as string of s and s upper-hal
Column Identifier NonStop SQL/MP supports collations for single-byte character sets, but not for doublebyte character sets. (Double-byte character values always collate in binary order and cannot be upshifted.) When you create or index a column that has a character data type and a single-byte character set, you can specify the name of a collation to associate with the column. The collation defines the default sort order for values in the column within the table or index.
Examples—Column Identifiers COL number specifies the column in position number of the select list. The first item in the select list is COL 1. You can use this form to refer to literals and expressions. alias is defined in a NAME command. You can use this form to refer to a name you assign to a literal or expression. See Alias on page A-6 for more information. detail-alias is a detail alias name defined in the NAME clause of a DETAIL command.
COLUMNS Table name of the table or view that contains the column as a qualifier, but you must omit the equals sign (=) that normally precedes the DEFINE name. You must also refer to a column by a qualified column name if you join a table with itself within a query in order to compare one row of the table with other rows in the same table. COLUMNS Table The COLUMNS table is a catalog table that describes the columns of the tables in the TABLES catalog table.
COLUMNS Table Column Name Data Type Description 13 SECONDLOWVALUE VARCHAR(20) First 20 bytes of second-lowest value in column (ignoring nulls); stored as for SECONDHIGHVALUE; set by UPDATE STATISTICS 14 NULLALLOWED CHAR(1) Y if null values allowed N if not 15 DEFAULTCLASS CHAR(1) S if column has system-defined default U if user-defined default N if no default D if null default 16 DEFAULTVALUE VARCHAR(36) Default for column; stored in ASCII if numeric; blank if system-defined primary key; NULL
COMMENT Statement Column Name Data Type Description 24 CPRULESNAME CHAR(34) Name of collation associated with column; "" if none 25 CPARRAYENTRY SMALLINT SIGNED Reserved for internal use by Tandem software 26 CHARACTERSET CHAR(30) Name of the character set associated with column * Indicates primary key The columns TABLENAME through PICTURETEXT (1 through 17) were created in version 1.
Considerations—COMMENT Considerations—COMMENT • Authorization requirements To use COMMENT on a collation, column, table, or view you must be a generalized owner of the table or view. To use COMMENT on an index, you must be a generalized owner of the underlying table. COMMENT also requires authority to write to the affected catalogs. Only one DDL statement can operate on a given SQL object (or partition of an SQL object) at a time.
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 (--), as follows: -- 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. The following table describes the contents of the COMMENTS table.
COMMIT Option timeout period, and error handling for the final phase can minimize the unavailability of applications that use the objects.
COMMIT Option See the examples that follow for the text of the warnings. See CONTINUE Statement on page C-65 for more information about user responses. {| AFTER time |} {| BEFORE time |} specifies that the operation start the commit phase only during the specified time period in local civil time. If the operation is ready to commit before AFTER time, the operation remains ready to commit until AFTER time, performing audit fix-up work as needed to remain ready to commit.
Considerations—COMMIT Option { value [ SECOND[S] ] } TIMEOUT { DEFAULT [ SECOND[S] ] } { NEVER } sets the time allowed for lock requests to complete during the commit phase, as follows: value Wait the specified number of seconds (a number in the range 0.01 to 21474836.47); wait indefinitely if value is -1. DEFAULT Wait 60 seconds. NEVER Wait indefinitely. Your operation acquires the lock after all concurrent locking transactions complete. The default is TIMEOUT DEFAULT.
Examples—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 the following options: ... WITH SHARED ACCESS NAME OP1 COMMIT BY REQUEST ONCOMMITERROR COMMIT BY REQUEST; and later continue the operation with the following statement: CONTINUE OP1 COMMIT; the ONCOMMITERROR option for the final phase of the operation is the default, ONCOMMITERROR ROLLBACK WORK. Note.
COMMIT WORK Statement beginning. (The output in the example indicates that the operation terminates without error.) >>ALTER TABLE $HR.PERSONEL.EMP MOVE TO $HDQ.PERSONEL.EMP +> WITH SHARED ACCESS COMMIT BY REQUEST; *** 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. .
Considerations—COMMIT WORK AUDITONLY directs SQL to retain existing locks on nonaudited objects. If your program holds locks on nonaudited objects and specifies AUDITONLY, the program must explicitly use CLOSE, UNLOCK TABLE, or FREE RESOURCES to close cursors and release locks. Considerations—COMMIT WORK • • • BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction. See TMF Transactions on page T-5 for more information. 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 For comparisons between character strings of different lengths, the shorter string is padded on the right with spaces (HEX 20) until it is the length of the longer string. 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.
Examples—Comparison Predicate For comparisons of INTERVAL values, SQL first converts the intervals to a common base unit. If no common unit exists, SQL reports an error. • 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.
Examples—Comparison Predicate • The following comparison predicate compares two DATETIME values: EXTEND (TIME1, DAY TO SECOND) > EXTEND (TIME2, DAY TO SECOND) TIME1, defined as DATETIME HOUR TO SECOND, is 09:06:24. TIME2, defined as DATETIME DAY TO MINUTE, is 15:09:21. 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 department 1500 and whose salary is less than the minimum salary determined by the first outer subquery. SELECT FIRST_NAME, LAST_NAME FROM PERSNL.EMPLOYEE WHERE DEPTNUM <> 1500 AND SALARY < (SELECT MIN (SALARY) FROM PERSNL.EMPLOYEE WHERE DEPTNUM <> 1500 AND SALARY > (SELECT AVG (SALARY) FROM PERSNL.EMPLOYEE WHERE DEPTNUM = 1500) ) COMPUTE_TIMESTAMP Function COMPUTE_TIMESTAMP is an SQLCI function that returns a Julian timestamp for a specified date and time.
Examples—COMPUTE_TIMESTAMP For the third form, any of the arguments can be either a numeric literal or a column identifier (but not a numeric expression). The range of valid dates is from 01/02/0001 00:00:00:000:000 through 12/31/3999 00:00:00:000:000. • Not compatible with date-time data types Note that COMPUTE_TIMESTAMP returns a value of data type NUMERIC(18) or LARGEINT, not data type TIMESTAMP or DATETIME YEAR TO FRACTION.
Considerations—CONCAT Except for the AS, STRIP, and SPACE clauses, which are described in this entry, the descriptions of elements of print-item shown in the syntax box are the same as the descriptions for elements of print-item in the DETAIL command. See DETAIL Command on page D-43 for more information. AS format is an Aw or a Cn display descriptor that specifies the width of the result of the concatenated print list.
Examples—CONCAT Examples—CONCAT • The following clause concatenates the CITY and STATE values and restricts the formatted result to 25 single-byte characters: CONCAT ( CITY STRIP, ", ", STATE) AS A25 Assume that a column of the report contains the following. Note that the entry for North Carolina is truncated to 25 single-byte characters.
Summary of Concurrent DDL and DML Operations • • • Summary of Concurrent DDL and DML Operations shows DDL operations that limit (or are limited by) DML operations. For DDL operations that occur in two phases, the table shows both scan and update (change timestamp) phases. ALTER Operation Effects on Timestamps shows the forms of the ALTER statement that change the timestamp of an object. The concurrency of an ALTER operation depends on whether ALTER changes a timestamp.
DML Operations You Can Start DDL Operations in Progress SELECT BROWSE SELECT SHARED SELECT EXCLUSIVE DELETE/INSERT UPDATE A2 A2 A2 A2 Timestamp change A1 Wait Wait Wait No timestamp chg A A A A Scan phase A A Wait Wait Change timestamp A1 Wait Wait Wait A2 A2 A2 A2 Scan phase A A Wait Wait Change timestamp A1 Wait Wait Wait ALTER WITH SHARED ACCESS CREATE CONSTRAINT CREATE INDEX WITH SHARED ACCESS A Allowed Wait Started operation waits for the operation in progr
Limits on Concurrent Utility and DML Operations Timestamp Updated ALTER Operation Timestamp Unaffected COLUMN HEADING X RENAME X Security attributes X File attributes and whether you can alter them for tables (T) or indexes (I) are as follows: ALLOCATE T, I AUDIT T AUDITCOMPRESS T, I BUFFERED T, I LOCKLENGTH T, I MAXEXTENTS T, I RESETBROKEN T, I SERIALWRITES T, I TABLECODE T, I VERIFIEDWRITES T, I Limits on Concurrent Utility and DML Operations DML Operations SELECT BROWSE SEL
Constraints experiencing concurrency problems, use CONTROL TABLE to disable VSBB. You can use EXPLAIN to find out if SQL is choosing VSBB for your application. • Sequential read operations For sequential read operations that use VSBB, the disk process locks all rows within the block (rather than a row at a time). Consequently, SQL operations that use VSBB, even with STABLE access, can acquire locks that remain in place longer than operations that do not use VSBB.
CONSTRNT Table also keeps such values from being added to the table because SQL rejects any such values after the constraint is in place. A constraint name is an SQL identifier. See CREATE CONSTRAINT Statement on page C-131 for more information. CONSTRNT Table The CONSTRNT table is a catalog table that describes the constraints placed on tables. The following table describes the contents of the CONSTRNT table.
Considerations—CONTINUE operation-name is the name of the operation to continue, as specified by the NAME option in the DDL statement that started the operation. If you did not specify the NAME option, operation_name is based on the type of statement that started the operation (ALTER_TABLE, CREATE_INDEX, and so forth).
Considerations—CONTINUE process or directly or indirectly modify DEFINEs) between error 1619 and a CONTINUE statement: ADD DEFINE ALTER DEFINE CATALOG DELETE DEFINE EXIT SET DEFMODE SYSTEM VOLUME The SQLCI process also acts as if you specified SET BREAK_KEY OFF, returning control to the previous Break key owner (usually TACL) if you hit the Break key.
Examples—CONTINUE Examples—CONTINUE • The following SQLCI example cancels a DDL operation about to begin its final phase: >> ALTER TABLE $D1.MDB.EMP PARTONLY MOVE $D2.MDB.EMP WITH SHARED ACCESS NAME MOVE_EMP_TABLE COMMIT BY REQUEST; *** WARNING from SQL [1618]: The MOVE_EMP_TABLE statement is ready to commit. *** WARNING from SQL [1619]: To continue processing, please enter a commit or rollback with a CONTINUE statement.
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.
Examples—CONTROL EXECUTOR Examples—CONTROL EXECUTOR • The following example shows an equijoin operation: CONTROL EXECUTOR PARALLEL EXECUTION ON; SELECT CUSTOMER.CUSTNUM, CUSTOMER.CUSTNAME FROM =CUSTOMER, =ORDERS WHERE CUSTOMER.CUSTNUM = ORDERS.CUSTNUM STABLE ACCESS; CONTROL QUERY Directive CONTROL QUERY is an SQL compiler directive that controls plans for queries.
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 Considerations—CONTROL QUERY • Scope of CONTROL QUERY An option you set with CONTROL QUERY stays in effect for the compilation of all statements and commands (including prepared ones) until another CONTROL QUERY directive resets that option or until the SQLCI or host language process stops. In host language programs, other scoping rules might also apply to the use of CONTROL QUERY. For more information, see the NonStop SQL/MP programming manual for your host language.
CONTROL TABLE Directive to checkpoint unaudited writes, and what to do when encountering locked data or unavailable partitions.
CONTROL TABLE Directive AS cor specifies that the control options apply only to instances of the table or view with the correlation name cor. The correlation name can be either implicit or explicit. view is the name of a view to which the control options apply (or an equivalent DEFINE), exactly as the view name (or DEFINE name) appears in subsequent references that are to be affected by the control options.
CONTROL TABLE Directive To specify different access paths for different occurrences of the same table within a single query, use correlation names to distinguish occurrences of the table and use the AS cor clause in CONTROL TABLE directives that specify access paths. If you specify the INDEX option and index does not exist, SQL issues an error (for SQLCI or dynamic SQL) or warning (for static SQL) when it compiles the CONTROL TABLE statement.
CONTROL TABLE Directive If you specify both USE and ACCESS, the USE option must precede the ACCESS option. Otherwise, SQL returns an error. If you specify MDAM ON along with CONTROL QUERY MDAM OFF, SQL returns an error. When you specify the ACCESS PATH...MDAM ON option, SQL resets any unspecified options to their default values. If, for example, you change the value of the USE option and want to preserve a previous ACCESS setting, include the appropriate ACCESS clause in your new CONTROL TABLE statement.
CONTROL TABLE Directive sequence-number must be greater than zero and no greater than the number of tables (or tables with different correlation names) participating in the SELECT. Two tables (or two occurrences of the same table) cannot have the same join sequence in a SELECT. MDAM { OFF | ENABLE } specifies whether to enable MDAM or turn it off. OFF turns MDAM off for the specified table ENABLE enables the use of MDAM for the specified table.
CONTROL TABLE Directive SEQUENTIAL [ INSERT | READ | UPDATE ] { ON | OFF | ENABLE } specifies whether the file system should buffer INSERT, READ, or UPDATE operations. Buffering reduces the number of messages between the file system and disk process by a factor equal to the blocking factor. ON buffer operations when possible OFF do not buffer operations ENABLE let SQL decide when to buffer For audited tables, the default is ENABLE.
CONTROL TABLE Directive This option applies to both partitioned tables and partitioned indexes, but affects only the main query in a SELECT statement without an INTO clause. (SQL always stops processing and returns an error when a required partition is unavailable for a subquery, a SELECT statement in the search condition of an UPDATE or DELETE statement, a SELECT with an INTO clause, or any other DML or DDL statement.
Considerations—CONTROL TABLE TABLELOCK ENABLE is the default. If you want to increase access performance and are not concerned with concurrency, use TABLELOCK ON. If TABLELOCK OFF is in effect and SQLCI or a host language program attempts to acquire more row locks than allowed, the file system issues error 35 (Lock limit has been reached). Note that for nonaudited tables, locking protocol enforced by DP2 is not available.
Considerations—CONTROL TABLE that table or view. It also does not affect the values of options set with different specific references to that table or view. For example, the following three directives can coexist if entered in the order shown: CONTROL TABLE A ACCESS PATH INDEX1; CONTROL TABLE A AS B ACCESS PATH INDEX2; CONTROL TABLE A AS C ACCESS PATH INDEX3; Accesses to table A from references that use correlation names B or C use INDEX2 or INDEX3, respectively, but other accesses to table A use INDEX1.
Considerations—CONTROL TABLE Make sure you are familiar with details of query operations as described in the NonStop SQL/MP Query Guide, and be sure to restore default values for these options immediately afterwards. For example: CONTROL TABLE * ACCESS PATH SYSTEM; CONTROL TABLE * JOIN METHOD SYSTEM JOIN SEQUENCE SYSTEM; Also, be aware of these special considerations: ° If you specify an access path, a query does not run unless that access path is available.
Considerations—CONTROL TABLE If you recognize such a situation you can force the use of the sequential blocksplit algorithm by including code such as the following in the server: CONTROL TABLE SALES.CUSTOMER SEQUENTIAL BLOCKSPLIT ON; ... INSERT INTO SALES.CUSTOMER ... ; <--- Series of inserts ... CONTROL TABLE SALES.CUSTOMER SEQUENTIAL BLOCKSPLIT ENABLE; Reset the SEQUENTIAL BLOCKSPLIT option to ENABLE (as in the final directive) immediately after the sequential inserts.
Considerations—CONTROL TABLE performance of SQL statements by reducing the number of messages exchanged and the amount of data transferred between the file system and the disk process. The following guidelines apply to sequential block buffering operations enabled by the SEQUENTIAL option: ° Sequential INSERT buffering applies to INSERT operations performed in sequential primary key, clustering key, or SYSKEY order.
Considerations—CONTROL TABLE To disable VSBB for INSERT operations, use this directive: CONTROL TABLE * SEQUENTIAL INSERT OFF • Buffered INSERT or UPDATE operations on nonaudited tables SQL buffers INSERT or UPDATE operations on nonaudited tables only if the SEQUENTIAL option is ON and SYNCDEPTH is 0. (You can specify both these options with CONTROL TABLE.
Considerations—CONTROL TABLE The buffer is flushed to the disk process, which puts the buffer in cache and writes the data to disk when the table is closed or, for audited tables, when the transaction commits. For nonaudited tables, the buffer is written to disk when the FREE RESOURCES statement executes. • Conditions that prevent buffering for INSERTs and UPDATEs SQL does not buffer operations if any of the conditions described in the following table occur.
Examples—CONTROL TABLE The optimizer often chooses virtual sequential block buffering (VSBB) when compiling a cursor definition. (You can determine whether SQL used VSBB in a specific case by looking at the EXPLAIN output for the cursor.) During a cursor session, conflicting DML operations can invalidate the cursor's buffering for the table. Each invalidation forces the next FETCH operation to send a message to the disk process to retrieve a new buffer; this can degrade performance substantially.
Examples—CONTROL TABLE • The following example forces SQL to choose a specific access plan for a query that accesses a view created as follows: CREATE VIEW EMPDEPT AS SELECT EMP_NAME, EMP_NO, E.DEPT_NO, DEPT_NAME, DEPT_LOCN FROM EMPLOYEE E, DEPT D WHERE E.DEPT_NO = D.DEPT_NO; The CONTROL TABLE directives force a join sequence and access path for table EMPLOYEE and SALARY, leaving SQL to choose the join sequence for table DEPT.
CONVERT Command path, join sequence, and join method for future SELECT operations to SQL. The example uses table EMPLOYEE with a primary key of EMP_NO and an alternate index, IEMP, on column DEPT_NO. CONTROL TABLE EMPLOYEE AS E1 ACCESS PATH INDEX IEMP JOIN SEQUENCE 1; CONTROL TABLE EMPLOYEE AS E2 ACCESS PATH PRIMARY JOIN SEQUENCE 2 JOIN METHOD NESTED; SELECT E1.EMP_NAME, E1.DEPT_NAME, E2.EMP_NAME, E2.DEPT_NAME FROM EMPLOYEE E1, EMPLOYEE E2 WHERE E1.DEPT_NO = "9999" AND E1.MGR_ENO = E2.
CONVERT Command If ServerWare Storage Management Foundation (SMF) is installed on your node, files you specify in CONVERT syntax cannot be on any $*.ZYS*. subvolumes. CONVERT RECORD ddl-record-name TO TABLE table-name [ [,] convert-option ] ... ; convert-option is: { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { { MAP NAME[S] { map } { ( map [ , map ] ... ) } } } } CATALOG[S] { catalog-spec } { ( catalog-spec [, catalog-spec ]...
CONVERT Command { NATIONAL { { { { { { { { { { { { map is: { { { { { { { { { { { { { ISO88591 ISO88592 ISO88593 ISO88594 ISO88595 ISO88596 ISO88597 ISO88598 ISO88599 KANJI KSC5601 UNKNOWN DEFAULT } } } } } } } } } } } } } } } } } } } } } } } } } } simple-fileset-list TO files catalog-spec is: catalog-name [ FOR simple-fileset-list ] redef-spec is: original-qualified-name AS redefined-qualified-name ddl-record-name is a DDL data name that identifies the DDL record definition for the file to convert.
CONVERT Command 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. See Filesets on page F-29 for information about simple fileset lists. TO files specifies the names and locations for the new partitions and indexes.
CONVERT Command 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. The default is the file name in the FILE IS clause of the DDL record definition name that follows the keyword RECORD.
CONVERT Command 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. redef-spec is: original-qualified-name AS redefined-qualified-name original-qualified-name identifies an original field or group in a DDL record.
CONVERT Behavior 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 KS C5601. (See Character Sets on page C-16 for more information.) UNKNOWN specifies that the character set is unknown, and specifying this option is equivalent to omitting the CHARACTER clause. SQL uses the data as 8-bit data. DEFAULT specifies the system default multibyte character set.
Enscribe Files and DDL Record Definitions The CONVERT utility does not check any version information; therefore, the versions of the table and any indexes you are creating cannot be greater than the version of the catalog in which they will be registered. CONVERT supports the HEADING, UPSHIFT, and HELP TEXT attributes. Enscribe Files and DDL Record Definitions CONVERT operates on Enscribe files only.
DDL Clause Mapping The key specifier for each alternate key in the DDL record definition is used in the KEYTAG clause of the CREATE INDEX command to associate a key specifier with an index. If a field name defined with a DDL REDEFINES clause is used as a primary or alternate key, CONVERT determines which column is to be the key based on the REDEFINE option of the CONVERT command. If you do not include the REDEFINE option, CONVERT uses the original field as the key column.
Conversion of DDL Elementary Items DEFAULT "default-string" DEFAULT "default-character-repeated" default-string specifies a default value for the column. If the defaultstring value specified in the DDL VALUE IS clause is longer than eight bytes, the value is truncated to eight bytes and a warning is displayed.
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) } Conversion of Decimal Data 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) P
Conversion of DDL Elementary Items The following 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 the following 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 NATIONAL set CHAR(y) SQL Data Type PIC X, PIC A or CHARACTER Field 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 character sets and equals j/2 for doublebyte character sets y The number of characters; y eq
Examples—CONVERT LOW KEY value must be a valid FIRST KEY value in a CREATE TABLE command. • Partitioned Enscribe alternate key files are converted to partitioned SQL indexes. Examples—CONVERT • Suppose that you have a key-sequenced Enscribe file named ORDERTAB on subvolume $VOL3.DDL. The contents of the Enscribe file are described by the record definition ORDER. The record ORDER includes two DDL groups named ORDERDATE and DELDATE.
Examples—CONVERT 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. The following 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.SALES; CNVSRC contains the following commands: ?SECTION CREATE_ORDERS CREATE TABLE \NODE.$VOL1.SALES.
Examples—CONVERT NO CLEARONPURGE NO DCOMPRESS NO ICOMPRESS NO SERIALWRITES NO VERIFIEDWRITES NO BUFFERED; CREATE INDEX \NODE.$VOL1.SALES.ORDERS0 ON \NODE.$VOL1.SALES.ORDERS (CUSTNUM) CATALOG \SYSTEM.$VOL1.SALES KEYTAG "oc" EXTENT (4,32) MAXEXTENTS 100; ?SECTION LOAD_ORDERS LOAD \NODE.$VOL3.DDL.ORDERTAB, \SYSTEM.$VOL1.SALES.ORDERS, SOURCEDICT \NODE.$VOL3.
Examples—CONVERT • The following example is the DDL record definition for the key-sequenced Enscribe file called TSTKANJI and uses the Kanji data type: RECORD kanji. FILE IS "$vol1.subvol1.tstkanji" KEY-SEQUENCED. 02 A PIC N. 02 B PIC N VALUE IS N"aa". 02 C PIC N(4). 02 D PIC N(3) VALUE IS N"abcdef". 02 E PIC N(5) VALUE IS N"abcdefghij". 02 F PIC N(4) REDEFINES E. 02 G. 03 LEN PIC S9(4) COMP. 03 VAL PIC N(4). KEY IS C DUPLICATES NOT ALLOWED.
Examples—CONVERT E PIC X(10) -- WARNING - PIC N DEFAULT "abcdefgh" NOT NULL , --WARNING Default literal originally national language 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. • The following example illustrates more features of the CONVERT command. The DDL record definition follows: RECORD SCHEDULE.
Examples—CONVERT The EMPNUM field is converted to two columns named DEP_KEY and EMP_KEY based on the elementary fields of the EMPNUM-KEY redefinition. The column definitions in the CREATE TABLE command are: DEP_KEY PIC X(2), EMP_KEY PIC X(3), The LOAD command in the SCHDCONV file contains the REDEFINE (EMPSCHEDULE.EMPNUM AS EMP-SCHEDULE.EMPNUM-KEY) option. The CREATE TABLE command follows: CREATE TABLE $VOL1.PERSNL.
CONVERTTIMESTAMP Function END_HOUR_5_2 PIC 9(2) NOT NULL, PRIMARY KEY ( DEP_KEY , EMP_KEY ) ) ORGANIZATION KEY SEQUENCED ... NO BUFFERED ; Each elementary item of the OCCURS group is converted to a column. The two fields of the EMPNUM-KEY group, which redefined EMPNUM, are used as the primary key of the table. The following command performs the conversion: >> OBEY SCHDCONV; CONVERTTIMESTAMP Function CONVERTTIMESTAMP is a function that converts a Julian timestamp to a DATETIME value.
COPY Command COPY resembles the FUP COPY command but, unlike FUP COPY, COPY works with SQL objects. If ServerWare Storage Management Foundation (SMF) is installed on your node, files you specify in COPY syntax cannot be on any $*.ZYS*. subvolumes. However, remote files on a non-SMF node can reside on any subvolume. COPY in-file [ [, out-file [ [,] option ] ... ] ] ; [ ,, option [ [,] option ] ...
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 } 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 tar
COPY Command in-file is the name (or an equivalent DEFINE) of the table or file from which to copy data. in-file can be a table, a disk file, a labeled or unlabeled tape, a terminal, or a process. 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.
COPY Command REPLACE SPACES WITH { ZERO[ES] | DEFAULT[S] } specifies how to copy an Enscribe ASCII numeric decimal field containing all blanks to an SQL numeric column. (Does not apply to Enscribe numeric binary fields) ZEROES sets the target column to 0 DEFAULTS sets the target column to its default value 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.
COPY Command out-option specifies characteristics of the output file. BLOCKOUT out-block-length (for copying to non-SQL files and processes only) specifies the number of bytes in a block of the output file (the maximum number of bytes written in a single physical operation). out-block-length is an integer in the range 1 to 32767 that specifies a blocksize supported for out-file. (Not all file types support the full range of blocksizes.
COPY Command 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. • FOLD (for copying to Enscribe files only) divides input records that are longer than RECOUT out-record-length into as many out-record-length records as needed to copy the entire input record.
COPY Command • If you do not specify out-block-length and if out-file is a structured disk file or a nondisk device, out-record-length is the record length specified or assigned when the file is created (or when the system is generated). If out-file is a labeled tape, you can specify the output record length with either the RECOUT clause of the COPY command (as described here) or with the RECLEN attribute of the CLASS TAPE DEFINE for the tape.
COPY Command VAROUT terminates a block by writing a 1-word block terminator of -1 (%177777) to indicate that there are no more valid records in the block, then padding the remainder of the physical block. VAROUT cannot write the terminator when the previous record ends on a block boundary or when out-block-length is odd and only one byte remains in the block. Empty or zero-length records are supported. The PAD and FOLD options are not allowed with VAROUT.
Considerations—COPY If you do not specify BYTE, COPY treats each word as a single value and converts it accordingly. If you specify BYTE but not OCTAL, DECIMAL, or HEX, the display appears in byte-octal format. If you specify more than one of OCTAL, DECIMAL, and HEX, each line is displayed in each specified format in the following order: octal, decimal, and hexadecimal. The ASCII option has no meaning when combined with OCTAL, DECIMAL, HEX, or BYTE.
Considerations—COPY The following rules govern the transfer of data across character sets. A COPY that violates these rules terminates with an error.
Considerations—COPY If you use COPY to write to a nonaudited file or table within a user-defined transaction, COPY issues a warning but performs the COPY anyway. You can press the Break key to interrupt COPY. If the target file or table is audited, the COPY is rolled back and all the work is undone. If the target is nonaudited, all the work done by COPY up to the point of the break is committed. If COPY fails and the target table is audited, TMF performs the recovery operation.
Considerations—COPY the table. The TRUNC option determines whether values are truncated. See INSERT Statement on page I-14 for more information. Any column except a system-defined primary key can be a source or target item. • Using COPY with tapes Rules for using CLASS TAPE DEFINEs or labelled tapes are described in the discussion of the FUP COPY command in the File Utility Program (FUP) Reference Manual.
Enscribe Field Formats ° 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. For example, a DECIMAL column would not accept blanks; a CHAR column would.
Examples—COPY If the default subvolume is $VOL1.PERSNL, the following command performs the same function: >> COPY EMPLOYEE, $VOL2.TEST.EMPLOYEE; The tables have identical descriptions. You need not specify any move options; MOVEBYORDER is used by default. • The following command displays the first 100 rows of table $VOL1.SALES.PARTS at your terminal and specifies the octal display format: >> COPY $VOL1.SALES.PARTS,,OCTAL COUNT 100; • The following example demonstrates the COUNT and MOVEBYNAME options.
Correlation Names • The following example demonstrates the MOVE option. The command copies data from the table $VOL1.SALES.PARTS to the Enscribe file $TESTVOL.SALES.PARTS, copying only the PARTNUM column to the PARTNUMR field and the PARTDESC column to the PARTD field: >> COPY $VOL1.SALES.PARTS, $TESTVOL.SALES.PARTS, +> TARGETDICT $TESTVOL.
COUNT Function 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. A table or view reference that has no explicit correlation name has an implicit correlation name.
Considerations—COUNT Considerations—COUNT • Null values COUNT is evaluated after eliminating all null values from the aggregate set, unless you specify an asterisk (*). If the set is empty, COUNT returns zero. Examples—COUNT • The following statement counts the number of distinct departments: >> SELECT COUNT (DISTINCT DEPTNUM) FROM PERSNL.EMPLOYEE; (EXPR) -------------------11 --- 1 row(s) selected.
CPRULES Table CPRULES Table The CPRULES table is a catalog table that contains one row for each collation. The following table describes the contents of the CPRULES table. Column Name Data Type Description 1 CPRULESNAME * CHAR(34) Collation name 2 CHARACTERISTICS CHAR(1) Properties of the collation: O if only identical strings sort equal N if some nonidentical strings sort equal 3 CPRULESCLASS CHAR(1) Always U.
Considerations—CREATE CATALOG SECURE "rwep" specifies security for the new catalog. If you omit the SECURE clause, SQL uses the default security of the user who creates the catalog. (See Security on page S-11 for more information.) PHYSVOL volume-name If ServerWare SMF is installed on your node, specifies a physical volume on which to place the set of catalog tables. This option overrides ServerWare SMF features. volume-name can be either the name of a physical volume or equivalent DEFINE.
Examples—CREATE CATALOG especially important for tables with many partitions. The performance of DDL statements such as CREATE TABLE, ALTER TABLE ADD PARTITION, and DROP TABLE can be greatly enhanced with an effective cache setting. For example, a table with 200 partitions, all described in a single catalog, has 40,000 rows in the PARTNS catalog table and in the IXPART01 index on the PARTNS catalog table. Creating such a table causes more than 80,000 writes to the catalog.
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 ServerWare SMF is installed on your node, the volume portion of name can be a virtual or direct volume.
Considerations—CREATE COLLATION Considerations—CREATE COLLATION • Authorization requirements 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.
Considerations—CREATE CONSTRAINT • • • The combined search conditions of all constraints associated with a table must have fewer than 31,000 bytes. The search condition cannot include a function other than UPSHIFT, a subquery, a host variable, or a system-created SYSKEY column. For any row of table, the search condition must be resolved by looking only at that row.
Examples—CREATE CONSTRAINT • Program invalidation CREATE CONSTRAINT invalidates SQL programs that use the underlying table. Examples—CREATE CONSTRAINT • The following statement 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; • The following statement creates a constraint that enforces a relationship between two items in a row.
CREATE INDEX Statement collate-spec is: COLLATE { collation | CHARACTER SET } wsa-spec is: {| {| {| {| NAME operation-name REPORT [ TO collector | ON | OFF ] { COMMIT [ WORK ] commit-options } { ROLLBACK [ WORK ] } |} |} |} |} attribute-spec is: {| {| {| {| {| {| {| {| {| {| {| {| {| {| {| ALLOCATE integer { AUDITCOMPRESS | NO AUDITCOMPRESS } BLOCKSIZE integer { BUFFERED | NO BUFFERED } { CLEARONPURGE | NO CLEARONPURGE } { DCOMPRESS | NO DCOMPRESS } DSLACK percent EXTENT { size | (pri-size [,sec-size])
CREATE INDEX Statement index can reside on any node or volume, independent of the location of the underlying table, but the volume on which the index is created must be audited by the TMF subsystem, even if the index itself is nonaudited. (An index is nonaudited if its underlying table is nonaudited.) table is the name of the table for which to create the index (or an equivalent DEFINE).
CREATE INDEX Statement volume volume-name. For volume-name, specify either a physical volume or equivalent DEFINE. You can specify a physical volume for each secondary partition in the PARTITION clause. This option is available only if you specify a virtual volume for index. volumename must belong to the virtual volume you specify.
CREATE INDEX Statement the =_SORT_DEFAULTS DEFINE. See the FastSort Manual for further information on configuring subsorts. See Examples—CREATE INDEX on page C-140. PARTITION ( partition [ , partition ] ... ) defines secondary partitions for a partitioned index. partition is the definition of a single secondary partition and includes the location of the partition, the first key value for the partition, and (optionally) the catalog, physical volume, and EXTENT and MAXEXTENT values for the partition.
Considerations—CREATE INDEX CLEARONPURGE* Controls disk erasure when file is dropped. DCOMPRESS* Controls key compression in data blocks. DSLACK Sets percent of slack in data blocks. Default is value of the SLACK attribute. EXTENT Sets extent sizes. Default is 16 pages for the first extent, 64 for others. ICOMPRESS* Controls key compression in index blocks. ISLACK Sets percent of slack in index blocks. Default is value of the SLACK attribute.
Considerations—CREATE INDEX While the index is being created, other processes can execute SELECT statements and read-only utility operations on the table, except during the final phase of the operation, when no access by other processes is allowed. 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.
Examples—CREATE INDEX An operation that uses WITH SHARED ACCESS cannot complete successfully unless the TMF audit trail generated during the operation is available for reading later in the operation. If a required audit trail has been overwritten, a WITH SHARED ACCESS operation cancels changes made to the database and terminates.
Examples—CREATE INDEX • The following example creates an index on a single column of the EMPLOYEE table and specifies a maximum number of extents for the index. CREATE INDEX EMPLOYE2 ON EMPLOYEE (JOBCODE) CATALOG PERSNL MAXEXTENTS 200 WITH SHARED ACCESS NAME CR_IND_EMP2 COMMIT BY REQUEST; ...
CREATE SYSTEM CATALOG Command CREATE INDEX statement starts a sort process that uses the specified subsort processes: >> ADD DEFINE =_SORT_DEFAULTS, CLASS SORT, SUBSORTS (=SS1, +> =SS2, =SS3, =SS4); >> ADD DEFINE, =SS1, CLASS SUBSORT, SCRATCH $VOL1; >> ADD DEFINE, =SS2, CLASS SUBSORT, SCRATCH $VOL2; >> ADD DEFINE, =SS3, CLASS SUBSORT, SCRATCH $VOL3; >> ADD DEFINE, =SS4, CLASS SUBSORT, SCRATCH $VOL4; >> CREATE INDEX AGEINDEX ON CUSTABLE (COL2); CREATE SYSTEM CATALOG Command CREATE SYSTEM CATALOG is an SQLC
Considerations—CREATE SYSTEM CATALOG Considerations—CREATE SYSTEM CATALOG • • • • • • Only the local super ID can create a system catalog. The TMF subsystem must be operating when you execute CREATE SYSTEM CATALOG. SQL creates the CATALOGS table on a subvolume named SQL on the same volume as the rest of the system catalog. The CATALOGS table is described in the system catalog, and the system catalog is registered in the CATALOGS table.
CREATE TABLE Statement 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 table { like-spec } { definition-spec } [| CATALOG catalog |] [| PHYSVOL volume-name |] [| CLUSTERING KEY key-column-list |] [| { ORGANIZATION } { K[EY SEQUENCED] } |] [| { ORGANISATION } { E[NTRY SEQUENCED] } |] [| { R[ELATIVE] } |] [| PARTITION ( partition [ , partition ] ...
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 } { ICOMPRESS | NO ICOMPRESS } LOCKLENGTH integer MAXEXTENTS integer NOPURGEUNTIL date RECLENGTH integer { SERIALWRITES | NO SERIALWRITES } TABLECODE integer { VERIFIEDW
CREATE TABLE Statement SQL does not apply partitions, views, indexes, or owner information from the source table to the created table. (The SQLCI DUP command applies partitions and owner information to a duplicate copy of a table and optionally duplicates views and indexes of the table.) source-table is the name of an existing table (or an equivalent DEFINE). col-def defines a column in the table by specifying the name, data type, and (optionally) other information about the column.
CREATE TABLE Statement If you omit this clause, the default heading is the column name. See HEADING Clause on page H-1 for more information. [ PRIMARY ] KEY key-column-list specifies the set of columns that make up the primary key for a key-sequenced table. Each column in the set must be a column previously defined for the table. The columns do not need to be contiguous, but their combined length cannot exceed 255 bytes.
CREATE TABLE Statement CLUSTERING KEY clause for a key-sequenced table, SQL adds a SYSKEY column to the table to use as the primary key. References to keys in other tables, or any references that require a unique key, should always use a primary key rather than a SYSKEY or clustering key. Columns in the clustering key definition cannot be updated and cannot contain null values, even if you omit the NOT NULL clause in the column definition.
CREATE TABLE Statement performed only from nodes running version 320 or later of NonStop SQL/MP. If these conditions are not met, SQL returns an error. SECURE "rwep" specifies the security for the table. See Security on page S-11 for more information. The default is the security of the user who executes the CREATE TABLE. SIMILARITY CHECK { ENABLE | DISABLE } authorizes or prohibits similarity checks on the table. The default is SIMILARITY CHECK DISABLE.
Considerations—CREATE TABLE SERIALWRITES Specifies serial or parallel writes. Default is serialwrites. TABLECODE Sets tablecode. Default is 0. VERIFIEDWRITE S Controls verification of writes to disk. Default is no verification. For more information, see the entry for a specific attribute. Considerations—CREATE TABLE • Authorization and access requirements 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 Examples—CREATE TABLE • The following example creates a table named ORDERS on subvolume $VOL1.SALES. The catalog also resides on subvolume $VOL1.SALES and the primary key is the ORDERNUM column. CREATE TABLE $VOL1.SALES.
Examples—CREATE TABLE One row of EMP, with column headings, might appear as follows. The SALARY column has the default value, and the EMPNAME column is upshifted. Employee Number Employee Name Monthly Salary -------- -------------------------------- -------------- 62389 • ANNA JONES 3882.50 The following example creates a table with a clustering key. CREATE TABLE PROC.
Examples—CREATE TABLE PARTNUM, 4; UNIT_PRICE, 6; and QTY_ORDERED, 4. The PARTITION specification could describe 24 partitions: CREATE TABLE \SYS1.$VOL1.SALES.ODETAIL ( ORDERNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL, PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL, UNIT_PRICE NUMERIC (8,2) NO DEFAULT NOT NULL QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL, PRIMARY KEY ( ORDERNUM , PARTNUM ) ) CATALOG \SYS1.$VOL1.SALES ORGANIZATION KEY SEQUENCED PARTITION ( \SYS1.$VOL2.SALES.
Examples—CREATE TABLE NO AUDIT; Some of the attributes specified apply to the entire table and some only to the primary partition. NOPURGEUNTIL, NO AUDIT, and LOCKLENGTH apply to all partitions. The example specifies NO AUDIT for an initial load operation, after which the attribute can be changed to AUDIT. The EXTENT and MAXEXTENTS attributes apply to the primary partition. • The following example creates a partitioned, key-sequenced table with a clustering key: CREATE TABLE HD.
Examples—CREATE TABLE • The following example creates a table with the primary key of the TIMESTAMP data type and partitions the table on a TIMESTAMP value. Another column has the TIME data type. CREATE TABLE $VOL1.SUBV1.PARTTIME ( A TIMESTAMP DEFAULT CURRENT NOT NULL , B TIME DEFAULT TIME "11:00:00" NOT NULL , C VARCHAR(300) NO DEFAULT NOT NULL , PRIMARY KEY A ) PARTITION ( $VOL2.SUBV1.PARTTIME FIRST KEY TIMESTAMP "1989-12-1:12:00:00.
CREATE VIEW Statement CREATE VIEW Statement CREATE VIEW is a DDL statement that creates a view. CREATE VIEW view [ ( new-name [ , new-name ] ... ) ] AS select-statement [| [| [| [| [| [| [| FOR PROTECTION |] SIMILARITY CHECK { ENABLE | DISABLE } |] CATALOG catalog-name |] SECURE "rwep" |] WITH CHECK OPTION |] WITH HEADINGS |] WITH HELP TEXT |] new-name is: new-column-name [ HEADING string | NO HEADING ] view specifies a Guardian name for the view (or an equivalent DEFINE).
CREATE VIEW Statement HEADING string | NO HEADING specifies a default heading for the column. (See HEADING Clause on page H-1 for more information.) AS select-statement specifies the columns for the view and sets the selection criteria that determines the rows that make up the view. select-statement cannot include a host variable, an INTO or ORDER BY clause, or (except in a subquery) the BROWSE, STABLE, or REPEATABLE access option.
CREATE VIEW Statement FOR PROTECTION specifies a protection view. If you omit this clause, the view is a shorthand view. SIMILARITY CHECK { ENABLE | DISABLE } authorizes or prohibits similarity checks on a protection view. (You cannot specify this clause unless you also specify the FOR PROTECTION clause.) The default is SIMILARITY CHECK DISABLE. Views that authorize similarity checks (SIMILARITY CHECK ENABLE) have version 310 or later.
Considerations—CREATE VIEW HEADING or NO HEADING clause in new-column-name, no heading is inherited. A view column that is a function or an expression cannot inherit a heading. WITH HELP TEXT specifies that help text for a view column is inherited from an underlying base table or view. A view column that is a function or an expression cannot inherit help text.
Examples—CREATE VIEW protection view is partitioned, too, and each partition of the protection view is registered in the same catalog as the corresponding partition of the table. You cannot insert data in a protection view unless the view includes all the columns of the underlying table that are defined with the NO DEFAULT option.
Examples—CREATE VIEW • The following example creates a protection view on the table EMPLOYEE that contains rows with employee numbers greater than 1000. The view is secured so that anyone on the network can read the view, but only a local user with super ID authority can write to it. Any member of the owner's user group can purge the view. CREATE VIEW \SYS1.$VOL1.PERSNL.EMPVIEW AS SELECT * FROM \SYS1.$VOL1.PERSNL.EMPLOYEE WHERE EMPNUM > 1000 FOR PROTECTION CATALOG \SYS1.$VOL1.
CURRENT Function The following 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.
Examples—CURRENT precision is an unsigned integer in the range 1 through 6 that specifies the number of significant digits with which the fraction of a second is expressed. The default is 6.
Examples—CURRENT_TIMESTAMP In contrast, the following statements also execute the same statement S twice, but use the same Julian timestamp (the time SET PARAM executed) for each execution: SET PARAM ?T CURRENT_TIMESTAMP; EXECUTE S; EXECUTE S: Examples—CURRENT_TIMESTAMP • To print the current date in a detail line, include the following items in the print list: "Date: ", CURRENT_TIMESTAMP AS DATE "D2/M2/Y2", SPACE 5, "Time: ", CURRENT_TIMESTAMP AS TIME "HP2:M2" For example, on March 15, 1994 at 1:30 p.
Cursor Position have write authority for the referenced table, protection view, and underlying table of a view. SQL checks authority to use a cursor when you execute an OPEN statement. If you use a cursor to locate rows to delete without specifying FOR UPDATE in the declaration, SQL checks only the read authority when the OPEN executes, even though the delete requires write authority. SQL checks for write authority when the DELETE executes.
C89 compiling, binding, accelerating, and SQL-compiling (compiling C programs that contain embedded SQL statements). For details about c89, see the C/C++ Programmer's Guide or the NonStop SQL/MP Programming Manual for C.
D Data Dictionary The NonStop 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 NonStop SQL/MP objects. (See Catalogs on page C-6 if you need more information.) 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 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 Key-Sequenced Relative or Entry-Sequenced 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 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. PIC[TURE] X [(len)] [DISPLAY] specifies a column with fixed-length character data. You can specify the number of characters in a PIC X column with either the len option described earlier or by specifying multiple Xs, with each X representing one character position.
Data Types { SMALLINT | INT[EGER] | LARGEINT } [SIGNED | UNSIGNED] defines a binary integer column, as follows: SMALLINT Two bytes, SIGNED or UNSIGNED; stores integers in the range unsigned 0 to 65535 or signed -32768 to +32767. INTEGER Four bytes, SIGNED or UNSIGNED; stores integers in the range unsigned 0 to 4294967295 or signed -2147483648 to 2147483647. LARGEINT Eight bytes, must be SIGNED; stores integers in the range -2**63 to 2**63 -1 (approximately 9.223 times 10 to the eighteenth power).
Data Types 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. If you omit COMP, DISPLAY SIGN IS LEADING is the default and the data type is equivalent to the data type DECIMAL. The value of the number stored in the data item cannot exceed the number of 9s in the PICTURE specification. The S specifies a signed column.
DATE Data Type 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. start-field and end-field specify the starting or ending field in one of these ranges of interval fields: YEAR, MONTH DAY, HOUR, MINUTE, SECOND, FRACTION(fraction-prec) If the ending field is FRACTION, you can specify a precision of from 1 to 18 digits, within parentheses; the default is FRACTION(6).
DATE_FORMAT Option DATE_FORMAT Option DATE_FORMAT is an option of the SQLCI report writer SET STYLE command that specifies a default format for dates. DATE_FORMAT "date-format" date-format is a string that defines a new default format for print items specified with AS DATE *. It must contain a valid numeric format as described in AS DATE/TIME. The default format is M2/D2/Y2.
DATE-TIME Functions Each of the other date-time data types is equivalent to a specific range of DATETIME fields: DATE DATETIME YEAR TO DAY (4 bytes) TIME DATETIME HOUR TO SECOND (3 bytes) TIMESTAMP DATETIME YEAR TO FRACTION(6) (11 bytes) A specific date-time data type is compatible only with another date-time data type that has the same range of fields. See DATETIME Data Type on page D-14 for more detailed information about the contents of DATETIME fields.
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 • The following are TIME literals in default, USA, and European format, respectively: TIME "13:40:05" TIME "1:40:05 PM" TIME "13.40.05" 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.
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.
Examples—DATETIME The following three data types are NOT compatible, even though they are all DATETIME types: DATETIME YEAR TO DAY DATETIME YEAR TO FRACTION(6) DATETIME HOUR • 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 99999
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.
DCOMPRESS File Attribute DCL Statements and Directives FREE RESOURCES Statement Closes cursors and releases locks held by a process LOCK TABLE Statement Locks a table (or the underlying tables of a view) and associated indexes UNLOCK TABLE Statement Releases locks held on nonaudited tables or views For more information, see the specific statement or directive. DCOMPRESS File Attribute DCOMPRESS is a Guardian file attribute that controls key compression in data blocks.
Considerations—DCOMPRESS ° Unsigned exact numeric: NUMERIC (1 to 9) or PIC 9V9 COMP (not NUMERIC (10) or larger or PIC S9) ° Unsigned decimal: includes PIC 9V9 DISPLAY For compression method 2, the new method, the following data types are supported: ° Fixed-length character: CHARACTER or PIC X ° Unsigned integer: INTEGER or SMALLINT (not LARGEINT) ° Unsigned exact numeric: NUMERIC (1 to 9) or PIC 9V9 COMP (not NUMERIC (10) or larger or PIC S9) ° Unsigned decimal: DECIMAL UNSIGNED, includes PIC 9V9 DISPL
DDL (Data Definition Language) Statements • Relative and entry-sequenced files Relative and entry-sequenced files always have the NO DCOMPRESS attribute, although key compression has no effect on them. DDL (Data Definition Language) Statements DDL (Data Definition Language) is the set of SQL statements that define, delete, or modify the SQL definition of an object or catalog. They can also change the authorization to use an object or catalog.
Deadlocks Following are the most common errors that occur when you attempt to execute a DDL statement on an SQL object while another DDL operation is in progress on the same object: File System Errors 12 File in use 40 The operation timed out 73 The table is locked 1057 Unable to access table that is being altered NonStop SQL/MP Errors 1203 Data could not be retrieved from catalog table name 1222 The label of name could not be altered Deadlocks Deadlock is a block to data flow caused by process
Considerations—DECIMAL_POINT Considerations—DECIMAL_POINT • DECIMAL_POINT character must be a single byte The DECIMAL_POINT character must be a single-byte period or comma, regardless of the character set used. • DECIMAL_POINT does not change numeric literal or mask cescriptor character The DECIMAL_POINT option does not change the decimal point character used in numeric literals or in mask display descriptors. That character (which is input to SQLCI) is always a period.
DECLARE CURSOR Statement DECLARE CURSOR Statement DECLARE CURSOR is a DML statement used in host programs to define a cursor and associate the cursor with a SELECT statement. The program uses the cursor to fetch rows retrieved by the SELECT statement one-by-one. { cursor CURSOR FOR select-stmt { [ FOR UPDATE OF col [ , col ] ... ] DECLARE { { :cursor-var CURSOR FOR :select-stmt-var } } } } cursor CURSOR FOR select-stmt specifies the cursor and the SELECT statement to associate.
Considerations—DECLARE CURSOR Considerations—DECLARE CURSOR • Order of cursor operations In static SQL, a cursor declaration must compile before other statements that reference the cursor. In dynamic SQL, a cursor declaration must execute before other statements that reference the cursor. • SELECT statements for DECLARE CURSOR A SELECT statement in a DECLARE CURSOR statement cannot include an INTO clause.
Examples—DECLARE CURSOR Examples—DECLARE CURSOR • The following static SQL statement defines a cursor for a read: EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT COL1, COL2, COL3, COL4 FROM =PARTS WHERE • COL2 >= :HOSTVAR2 ORDER BY COL2 BROWSE ACCESS; The following static SQL statement defines a cursor for an update. The FOR UPDATE clause lists the columns to be updated.
DEFAULT Clause literal is a literal of a data type compatible with the data type defined for the associated column. For a character column, literal must be a string literal of no more than eight bytes or the length of the column, whichever is less. If the character column is associated with a double-byte character set, literal must contain an even number of bytes. SQL pads literal with spaces (HEX 20) when inserting the value into longer character fields.
Examples—DEFAULT You cannot specify NULL if you also specify the NOT NULL clause in the command that creates the column.
Using DEFINEs • • • • • • • • • • • =_SORT_DEFAULTS =_SQL_CAT_HEAP_LIMIT =_SQL_CMP_EQ_LIMIT =_SQL_CMP_EVENT =_SQL_CMP_EVENT_NO0 =_SQL_CMP_NO_KS_MJOIN =_SQL_cmp_node =_SQL_EXE_USE_SQAPVOL =_SQL_MSG_node =_SQL_RECGEN_node =_SQL_TM_node_vol Each of the preceding DEFINES has an entry describing it. If using SQLCI HELP to access the text, be sure to include the “=_” characters in the DEFINE name. Use DEFINEs carefully. DEFINEs that identify the wrong objects can cause unexpected results.
Using DEFINEs Use the following commands to work with DEFINEs from SQLCI. Each command is described in more detail in a separate entry.
Using DEFINEs From SQLCI 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 and completeness Using DEFINEs From SQLCI The following rules apply to the use of DEFINEs in SQLCI: • Make sure DEFMODE is set.
Using DEFINEs With SQL Programs You normally supply DEFINEs at explicit SQL compilation for any DEFINE names you use in static SQL statements. SQL attempts to resolve such DEFINE names during explicit compilation, if possible (even though the names might be re-resolved at load or execution time according to the rules that follow). If corresponding DEFINEs do not exist, the SQL compiler issues a warning and produces a program that, although valid, requires recompilation with appropriate DEFINE values.
DEFINE Attributes executes), using the DEFINEs in effect at that time. Changing DEFINE values during program execution has no effect on such a statement. DEFINE names in a static SQL statement that was compiled with a CONTROL QUERY BIND NAMES AT EXECUTION directive in effect are resolved just before each execution of the statement. Changing DEFINE values during program execution affects such a statement.
DEFINEs of Class CATALOG DEFINEs of Class CATALOG DEFINEs of class CATALOG specify a logical name for a particular SQL catalog. For example, this SQLCI statement assigns the logical name =PCAT to the catalog that resides on subvolume \D.$E.F: ADD DEFINE =PCAT, CLASS CATALOG, SUBVOL \D.$E.F; The following statement, executed while the preceding DEFINE is in effect, creates a table on \D.$E.F: CREATE TABLE T ... CATALOG =PCAT; A DEFINE of class CATALOG does not change the current default catalog.
Summary of DEFINE Attributes MAP is the default class unless the working attribute set specifies a different class, so the previous command is normally equivalent to this one: ADD DEFINE =ORDERS, FILE \SYS1.$VOL2.SALES.ORDERS; (The working attribute set is a set of default attribute values used when you create a new DEFINE and do not explicitly specify its attributes.
Summary of DEFINE Attributes Attributes of DEFINEs (by Class) Required/ Optional Class Attributes CATALOG SUBVOL subvol specifies an actual subvolume name that identifies a catalog; subvol is a Guardian subvolume name. Required DEFAULTS CATALOG[\node.][$vol.]subvol specifies the current default catalog. If you omit \node or $vol, the current value of the corresponding element in the =_DEFAULTS DEFINE VOLUME attribute is used. Optional VOLUME[\node.]$vol.
Summary of DEFINE Attributes Class SPOOL SUBSORT Attributes Required/ Optional VLM {ON | OFF} Optional BATCHID job-id Optional BATCHNAME batch-name Optional COPIES number Optional FORM form-name Optional HOLD {ON | OFF} Optional HOLDAFTER {ON | OFF} Optional LOC destination Required MAXPRINTLINES number Optional MAXPRINTPAGES number Optional OWNER owner-id Optional PAGESIZE number Optional REPORT report-name Optional SELPRI priority-number Optional BLOCK size Optional CPU
Examples—DEFINEs Used With SQL Programs Class Attributes Required/ Optional GEN generation-number Optional LABELS Optional {ANSI | IBM | OMITTED | BYPASS | BACKUP} OWNER owner-id Optional MOUNTMSG “text” Optional RECFORM {F | U} Optional RECLEN record-length Optional REELS num-of-volumes Optional RETENTION days Optional SYSTEM \system-name Optional TAPEMODE {STARTSTOP | STREAM} Optional USE {IN | OUT | EXTEND | OPENFLAG} Optional VERSION number Optional VOLUME {volume-id | SCRATC
DELETE DEFINE Command compiled program. The DEFINE for =SCAT must be in effect when you issue the SQLCOMP command. SQLCOMP /IN object-file, OUT list-file>/ CATALOG =SCAT 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.
Considerations—DELETE 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. The file organization of the table or underlying table must be key-sequenced or relative.
Examples—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. • Locking 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; • The following DELETE removes all suppliers not in Texas from the table PARTSUPP: DELETE FROM INVENT.PARTSUPP WHERE SUPPNUM IN (SELECT SUPPNUM FROM INVENT.
Examples—DESCRIBE INPUT 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.
DESCRIBE Statement stmt-name is the SQL identifier of the prepared SQL statement. :stmt-variable is the name of a host variable that stores the SQL identifier of the prepared SQL statement.
Examples—DESCRIBE COLLATIONS INTO :collations-buffer specifies the name of a collations buffer declared in an INCLUDE SQLDA statement or elsewhere in your program into which DESCRIBE returns collations associated with the SELECT columns. If you specify the COLLATIONS INTO clause, DESCRIBE sets the CPRL_PTR item for each entry in the SQLDA to the address of the corresponding entry in the collations buffer.
DETAIL Command command, even though it may 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 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). See Expressions on page E-23 if you need details about the form of numeric expressions. AS format specifies a format for printing the item using the syntax of the AS clause or the AS DATE/TIME clause. (See AS Clause on page A-54 or AUDIT File Attribute on page A-68 for details.
DETAIL Command 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. (When you compute the number of lines you need, include lines for the page footing.
Considerations—DETAIL Considerations—DETAIL • Default detail line The detail line for the default report is: DETAIL COL 1, COL 2, COL 3, ... ; A DETAIL command with no print list (DETAIL;) produces a report that has no detail lines. This can be useful for summary reports. • Each DETAIL command replaces the previous DETAIL command Only one DETAIL command is in effect at a time.
Examples—DETAIL Examples—DETAIL • The following commands specify a report in the format shown: >> SET LIST_COUNT 0; >> SELECT * FROM SALES.
DISPLAY STATISTICS Command • The following example demonstrates how to use SKIP to print a report vertically instead of horizontally: >> SET LIST_COUNT 0; >> SET STYLE HEADINGS OFF, >> SET LAYOUT PAGE_LENGTH ALL; >> SELECT CUSTNAME, STREET, CITY, STATE, POSTCODE +> FROM SALES.
Examples—DISPLAY STATISTICS Estimated cost is a relative measure of cost derived using the same cost functions that the SQL compiler uses to choose a query execution plan. The estimate includes CPU, disk I/O, and message costs. Higher numbers tend to indicate that SQL expects to process larger amounts of data. Large numbers are not typically associated with fast on-line response times.
DISPLAY USE OF Command Although you did not display the statistics when you executed the command, you can display them using the DISPLAY STATISTICS command as follows: >> DISPLAY STATISTICS; Estimated Cost 39 Start Time 94/06/01 09:07:16.678185 End Time 94/06/01 09:07:56.533061 Elapsed Time 00:00:39.854876 Master Executor Execution Time 00:00:00.359826 Records Table Name \S.$V.I.S \S.$V.#2549 \S.$V.I.P \S.$V.
Considerations—DISPLAY USE OF AT { \node } { ( \node [, \node ] ... ) } specifies nodes to search for dependent objects and programs. The default is all nodes in the network. { BRIEF | STANDARD } specifies the information to display and the format for the display. BRIEF Display name, object type, and status of dependent objects and programs. STANDARD Display name, object type, status, owner, security, number of partitions, and catalog name for the object and for dependent objects and programs.
Considerations—DISPLAY USE OF protection view, however, the USAGES table describes only the relations between the primary partitions of the dependent object and the protection view. If object is the name of a secondary partition, DISPLAY USE OF substitutes the name of the primary partition and reports the primary partitions of all the objects that depend on that primary partition. • Display formats The columns of the DISPLAY USE OF output contain the following information: ° Object Name.
Examples—DISPLAY USE OF ° Secure. The security string for the object. ° Catalog Name. The name of the catalog in which the object is described. The “Number of unique dependencies” field shows the number of objects that depend directly or indirectly on the initial object. The number does not include duplicate occurrences of objects. The “Number of direct dependencies” field shows the number of objects that depend directly on the object being traced.
DISTINCT Clause subvolume \SYS1.$VOL1.HR. (The display layout has been modified slightly to fit the page width.) >> DISPLAY USE OF \SYS.$VOL.PERSNL.EMPLOYEE; Object Name Type S P Owner Name Secure ------------------------ ---- - --- ----------- ------ Catalog Name ------------0 \SYS1.$VOL1.HR.EMP TA GROUP.NAME CUCU PV GROUP.NAME CUCU IN GROUP.NAME CUCU $VOL1.HR 1 \SYS1.$VOL1.HR.EMPL $VOL1.HR 1 \SYS1.$VOL1.HR.XEMP $VOL1.HR 1 \SYS1.$DATA.ZYQ39483.Z000002H PG PATH/usr/empinfo/reports/app.
Summary of DML Statements Summary of DML Statements CLOSE Closes a cursor DECLARE CURSOR Statement Defines a cursor DELETE Statement Deletes rows from a table or view FETCH Statement Retrieves a row from a cursor INSERT Statement Inserts a row into a table or view OPEN Statement Opens a cursor SELECT Retrieves data from tables and views UPDATE Statement Updates values in columns of a table or view For more information, see the specific statement.
Considerations—DOWNGRADE CATALOG If ServerWare SMF is installed on your node, catalogs cannot specify any catalog or system catalog on a $*.ZYS*. subvolume. The default is the current default catalog. TO version specifies the catalog format version for the downgraded catalog.
Examples—DOWNGRADE CATALOG DOWNGRADE CATALOG creates a new temporary catalog on the same volume as each catalog being downgraded. Such volumes must have enough disk space available to store files twice as large as the original catalog. • Not allowed in user-defined transactions You cannot use DOWNGRADE CATALOG in a user-defined transaction.
Considerations—DOWNGRADE SYSTEM CATALOG You can express version as either an integer (2, 300, 310, 315, 320, 325, or 330) or a character string (A011, A300, A310, A315, A320, A325, or A330), but the version you specify must be older than the current version of the system catalog. You cannot downgrade a catalog to version 1, but version 2 catalogs are compatible with version 1 software.
Examples—DOWNGRADE SYSTEM CATALOG • Temporary disk space requirements DOWNGRADE SYSTEM CATALOG creates a new temporary catalog on the same volume as the catalog being downgraded. Such volumes must have enough disk space available to store files twice as large as the original system catalog. • Not allowed in user-defined transactions You cannot use DOWNGRADE SYSTEM CATALOG in a user-defined transaction.
Considerations—DROP INDEX index specifies the name (or an equivalent DEFINE) of an index to delete. index cannot be a catalog index. PROGRAM file specifies the name (or an equivalent DEFINE) of a Guardian file that contains an SQL program. TABLE table specifies the name (or an equivalent DEFINE) of a table to delete. table cannot be a catalog table. VIEW view specifies the name (or an equivalent DEFINE) of a view to delete.
Considerations—DROP You cannot drop a collation that has dependent objects. (See DISPLAY USE OF Command on page D-51 to learn how to determine dependencies.) • Dropping a constraint Dropping a constraint invalidates all SQL object program files that use the underlying table and can change the version of the associated table and any views defined on that table. To drop a constraint, you must be the local or remote owner of the underlying table, with purge authority, or the local super ID.
Examples—DROP Invalidated program files might be usable, but you should ensure that they are explicitly SQL-compiled to avoid automatic recompilation each time the program runs. WARNING. It is legal to DROP an open table, 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).
DROP SYSTEM CATALOG Command DROP SYSTEM CATALOG Command DROP SYSTEM CATALOG is an SQLCI command that allows the local super ID to delete the system catalog, including the CATALOGS table and information about the SQLCI2 program. DROP SYSTEM CATALOG catalog-name ; catalog-name identifies the system catalog to be dropped; catalog-name is the name of the volume and subvolume that contain the system catalog (or an equivalent DEFINE).
Examples—DROP SYSTEM CATALOG Examples—DROP SYSTEM CATALOG • The following command drops a system catalog that resides on the default location $SYSTEM.SQL: 16> SQLCI DROP SYSTEM CATALOG $SYSTEM.SQL; • The next command drops a system catalog that resides on the volume $VOL1 and subvolume SVOL1: 18> SQLCI DROP SYSTEM CATALOG $VOL1.SVOL1; DSL (Data Status Language) Statements A DSL statement retrieves status information about the version of the database. The following table summarizes the DSL statements.
DUP Command • • Specifying a larger-than-normal DSLACK value when a file is initially loaded and many more inserts are expected can improve performance by reducing the number of block splits required when inserts occur. For a file expected to have little write activity, you can save disk space by specifying a smaller-than-normal DSLACK value.
DUP Command DUP resembles the FUP DUP command in function and syntax, but you cannot use FUP DUP on SQL objects. DUP source-fileset-list, { target-fileset } { } { MAP NAME[S] { map-spec } } { { (map-spec [, map-spec] ... ) } } [ [,] dup-option ] ... ; dup-option is: { { { { { { { { { { { { { { { { { { { { CATALOG[S] { catalog-spec } } { ( catalog-spec [, catalog-spec ]...) } } } COLLATION[S] ( collation-spec[,collation-spec]...
DUP Command source-fileset-list is a qualified fileset list that specifies the objects or files to duplicate. See Filesets on page F-29 for details. If source-fileset-list includes a primary partition, DUP duplicates all partitions of the table or file, deriving names for new secondary partitions from the values you specify for target-fileset or the MAP NAME option. If source-file-list explicitly specifies a secondary partition, DUP reports an error.
DUP Command specifications *.*.* and \LOCAL.*.*.* match all files on all nodes, both local and remote. fileset is a fileset that specifies names and locations for the new objects and files. 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.*.
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 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 then specify either SAVEID or SOURCEDATE separately, the settings must match. The default is SAVEALL OFF.
Considerations—DUP 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 indexes being duplicated as part of duplicating a table. (For an example, see the discussion of Target Specification later in this entry.) • If constraints or statistics exist for a table that is duplicated, DUP applies the constraints and statistics to the new table and records them in the appropriate catalog tables of the catalog for the new table.
Considerations—DUP 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. To duplicate an alternate-key file, you must specify it explicitly or implicitly (through wild-card characters) in source-fileset-list. DUP does not duplicate the DDL record definition of an Enscribe file.
Considerations—DUP For example, suppose 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. To copy the base table and all dependent objects, you must use a MAP NAMES option that includes all of the dependent objects. The following MAP NAMES clause includes all three objects: MAP NAMES ($A.A.* TO $D.A.*, $A.XX.* TO $D.XX.*, $A.ZZ.* TO $D.ZZ.
Considerations—DUP duplicated to the same volumes and subvolumes as the original table, but with different names. DUP $VOL1.TESTSUBV.PARTS, MAP NAMES ( \REMOTE.*.*.* TO \REMOTE.*.*.OLDPARTS, *.*.* TO *.*.OLDPARTS ) CATALOGS ( \REMOTE.$VOL1.CAT FOR \REMOTE.*.*.*, $VOL1.CAT FOR *.*.* ); The following command does not work as expected because DUP changes the \LOCAL.*.*.* part of the MAP NAMES specification to *.*.*.
Examples—DUP If a DUP operation fails after correctly duplicating some objects and files, restarting the operation from the beginning causes errors unless the original operation used the TARGET PURGE option. Determine the objects and files that were duplicated and consider the appropriate setting for the TARGET option before you restart a partially successful DUP operation.
Dynamic SQL CATALOG clause specifies a catalog on \NEWSYS in which to describe the new objects. >> DUP $VOL1.INVENT.PARTLOC, • +> MAP NAME ( $VOL1.*.* TO \NEWSYS.$NVOL1.*.*, +> $WHS2.*.* TO \NEWSYS.$NVOL2.*.*, +> $WHS3.*.* TO \NEWSYS.$NVOL3.*.* ) +> CATALOG \NEWSYS.$NVOL.DB FOR \NEWSYS.*.*.* NO LISTALL; The following command duplicates all tables, collations, and files, but no indexes or views, that reside on subvolume $VOL1.PERSNL to subvolume $VOL1.NWPERS: >> DUP $VOL1.PERSNL.*, *.NWPERS.
Summary of Dynamic SQL Statements Summary of Dynamic SQL Statements DESCRIBE Returns information about output variables of prepared statements DESCRIBE INPUT Returns information about input parameters of prepared statements EXECUTE Executes a compiled statement EXECUTE IMMEDIATE Executes an SQL statement contained in a host variable PREPARE Compiles a DDL, DML, or DCL statement for later execution by EXECUTE RELEASE Deallocates memory for a dynamic SQL statement referred to through a host variab
Features of Dynamic SQL character host variable, and then compiles and executes it. With dynamic SQL statements, you must perform some additional operations (such as building descriptors for host variables) that are performed for you when you use static SQL statements. After compilation, SQL executes statements in the same way, whether they are dynamic or static. SQL places the results of dynamic SQL statements into output variables; you can use DESCRIBE to obtain information about those variables.
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.
END DECLARE SECTION Directive NonStop SQL/MP programming manual exists for each of the four host languages supported by NonStop SQL/MP. From each language you can also use the basic SQL statements (but not the SQLCI commands) documented in thiis manual You can write embedded SQL programs that run as OSS processes or Guardian processes in the host language C. Embedded SQL programs written in COBOL85, Pascal, or TAL must run as Guardian processes.
Examples—END DECLARE SECTION Examples—END DECLARE SECTION • The following statements from a C, Pascal, or TAL program declare host variables that correspond to the columns of the table PARTS: EXEC SQL BEGIN DECLARE SECTION; EXEC SQL INVOKE PARTS AS PARTSREC; EXEC SQL END DECLARE SECTION; ENV Command ENV is an SQLCI command that displays attributes of the current SQLCI session. You can use ENV at either the SQLCI prompt (>>) or the select-in-progress prompt (S>).
Examples—ENV DEFINE after you start SQLCI, however, does not change the message file. (You cannot change the message file version and language directly because they depend upon the message file in use.) Examples—ENV • The following example shows the output of an ENV command. In the example, the command is entered at a select-in-progress prompt. S> ENV; -------------------------------------------------Current Environment -------------------------------------------------CATALOG \SYS1.$VOL1.
ERROR Command ERROR Command ERROR is an SQLCI command that displays the error text associated with an error number and, optionally, the cause and effect of the error and suggestions for recovery.
Examples—ERROR The default is DETAIL unless you have previously set the ERROR_TEXT session option to BRIEF. (See SET SESSION Command on page S-39 for more information.) Examples—ERROR • The following command displays the error text of file system error number 1066: >> ERROR FS 1066, BRIEF; Internal error: Occurred in OPEN. >> • The following command displays the text, cause, and effect of SQL error number 1249: >> ERROR SQL 1249, DETAIL; A column cannot be added to an entry-sequenced table.
EXECUTE Statement but are intended to be passed directly to users or stored in tables. There are cases in which an SQL statement can return a different error than it did in a previous version of SQL. See the NonStop SQL/MP Messages Manual and the NonStop SQL/MP programming manual for your host language for more information about handling errors and warnings within programs.
EXECUTE Statement [ USING [ ?param=] value [, [?param =] value ] ... ] [ USING :variable [, :variable ] [ USING DESCRIPTOR :in-sqlda ... ] ] specifies values for parameters in the compiled statement. Use the first form in SQLCI, the second form in a program that has information about the parameters, and the third form in a program that uses DESCRIBE INPUT to dynamically retrieve information about the parameters.
Considerations—EXECUTE • COMPUTE_TIMESTAMP (date)—an SQLCI function that returns a Julian timestamp for the date and time you specify in date as a value of data type NUMERIC 18 or LARGEINT. See CURRENT_TIMESTAMP Function on page C-163 or COMPUTE_TIMESTAMP Function on page C-57 for more detail about the functions. For examples of their use with EXECUTE, see the examples later in this entry. :variable (used in programs) is a host variable that contains a value for a parameter in the statement.
Examples—EXECUTE You can specify parameter values for named formal parameters with the USING clause, with the SQLCI SET PARAM command, or with the TACL PARAM command. (A TACL PARAM named “A” is the same as a parameter named “?A” in SQLCI.) You can specify parameter values for unnamed parameters only with the USING clause.
EXECUTE IMMEDIATE Statement parameter ?T directly for the type LARGEINT field but converts it for the type TIMESTAMP field.
Considerations—EXECUTE IMMEDIATE :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. If the SQL statement is an INSERT statement, the statement cannot contain the RETURNING clause. The SQL statement must not contain parameters or refer to host variables. Considerations—EXECUTE IMMEDIATE • • You can use EXECUTE IMMEDIATE for any DDL, DML, or DCL, or DSL statement, except OPEN, CLOSE, and SELECT.
Examples—EXISTS Examples—EXISTS • The following query searches for departments that have no engineers (job code 420): SELECT DEPTNAME FROM PERSNL.DEPT D WHERE NOT EXISTS (SELECT JOBCODE FROM PERSNL.EMPLOYEE E WHERE E.DEPTNUM = D.DEPTNUM AND JOBCODE = 420); • The following query searches for parts with less than 20 units in the inventory: SELECT PARTNUM, SUPPNUM FROM INVENT.PARTSUPP PS WHERE EXISTS (SELECT PARTNUM FROM INVENT.PARTLOC PL WHERE PS.PARTNUM = PL.
EXIT Command EXIT Command EXIT is an SQLCI command that ends an SQLCI session. Pressing control-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 You can use the information in an EXPLAIN report to tune queries and to help determine whether to add or drop indexes for a database. See the NonStop SQL/MP Query Guide for listings of sample EXPLAIN reports and for a detailed explanation of what to look for when you analyze EXPLAIN output. Note that an EXPLAIN report is based on information available at the time you generate the report.
Considerations—EXPLAIN <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Query plan [ : Will utilize parallel execution ] SQL request : { Delete | Update | Select | Insert | Insert-Select | Union of Selects } <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ----------------------------------------------------------Plan step n [: Perform an [ [Inner|Left] Join|Union ] ] [ Join strategy : [Nested | [Hybrid] Hash | ] [ [Key Sequenced] Merge] Join ] [ (The following section appears on
Considerations—EXPLAIN Base table pred. : [ Type of [ Update | Delete ] : ] [ SBB for [ Insert | Update ] : ] [ Seq Blocksplit : ] Executor pred. : [ Executor aggr. : ] [ Pred. selectivity : ] [ DP2 aggr.
Considerations—EXPLAIN Access path Identifies the path used for retrieving rows from the base table. The path can be a primary path (the base table) or an alternate path (an index defined on the base table). The phrase path forced indicates that the path was specified by the user with a CONTROL TABLE directive. Access path also states whether the table is partitioned and whether access is sequential and pages are kept in cache as long as possible (called sequential cache).
Considerations—EXPLAIN Expected rowcount For an operation other than a sort, shows the number of rows the optimizer expects to be returned. For a join query, the rowcount is cumulative for each join operation. Appears for sort operations only if the purpose of the sort is to group rows for a GROUP BY clause. In this case, shows the number of rows expected after the GROUP BY operation finishes. Hash cost Indicates the relative cost of the hash operation.
Considerations—EXPLAIN Parallel Execution Plan Describes the parallel execution plan for the operation, including: • • • • • The number of executor server processes (ESPs) that perform each operation in parallel The name of the system and the CPUs in which the ESPs are started The names of the volumes on which partitions of the base table reside The names of the volumes on which rows are redistributed to promote parallel access The number and purpose of each sort started by each ESP in parallel.
Considerations—EXPLAIN Sort cost Indicates the relative cost of the sort operation. Lower numbers indicate more efficient and less costly execution. Sort key columns Identifies columns used in the sort by column names qualified by correlation or table names or (if the sort column is an expression) by position in the select list. Also lists collations associated with the sort columns.
Examples—EXPLAIN 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. Type of [DELETE] Indicates the type of DELETE or UPDATE operations: cursor, [UPDATE] subset, or unique.
Expressions • The following examples show 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. MAX(U3) >>PREPARE SAVQ FROM SELECT U1,U2, MIN(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, datetime, or INTERVAL operands connected by arithmetic operators, as shown in the following 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-14. 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, 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, then the second operand must be greater than 0, and the result is 0. If the second operand is 0, then the first operand cannot be 0, and the result is 1. If the first operand is negative, then the second operand must be an integer. Exponentiation is subject to rounding error. Results should be considered to be approximate.
Considerations—Expressions Assume the operands are defined as follows: COL1 LARGEINT precision=18, scale=0, magnitude=18 COL2 LARGEINT precision=18, scale=0, magnitude=18 100.00 constant precision=5, scale=2, magnitude=3 SQL evaluates the expression as follows: ° 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.
Considerations—Expressions a. In a date-time or INTERVAL expression, you can specify fields for the result with a range of fields following the expression. For example, the following expression gives the result 09-17: ( DATE "1988-09-22" - INTERVAL "5" DAY ) MONTH TO DAY b. If you subtract a date-time value from another date-time value, both values must have the same range of date-time field. c.
Examples—Expressions Examples—Expressions • • The following are examples of arithmetic expressions: QTY_ON_HAND * AVG (PARTCOST) Column value multiplied by function applied to column values QTY_ORDERED * (PRICE - PARTCOST) Column values combined by operators PRICE * :INCREASE Column value multiplied by value in host variable In this example and in all the following examples in this entry, date-time and INTERVAL values are from the following table: Table Definition: CREATE TABLE PROJECTS ( PROJEC
Examples—Expressions • The following example adds an INTERVAL value qualified by DAY to a DATETIME value. SQL handles 1988 as a leap year. The result is 1988-0312:20:30. >> SELECT +> • START_DATE + WAIT_TIME FROM PROJECTS WHERE PROJECT_NAME = "920"; The following example subtracts an INTERVAL value from a DATETIME value and adjusts the adjacent field. The result is 1940-02-20:21:00.
EXTEND Function • The following SQLCI example uses the UNITS clause to convert a date-time field to a numeric field: >> SELECT START_DATE UNITS MONTH FROM PROJECTS; (EXPR) -----2 1 2 10 10 --- 5 row(s) selected. • The following programmatic example adds 10 years to the start date and stores the result in a DATETIME variable. The statement uses the UNITS clause to convert a numeric 10 to a value of type INTERVAL YEAR.
Examples—EXTEND • • 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. If the result is not a valid DATETIME value, an SQL error is generated.
Considerations—EXTENT partition, or you can specify one size for the primary (first) extent and another size for the secondary extents. EXTENT { ext-size } { ( pri-ext-size [, sec-ext-size ] ) } ext-size, pri-ext-size, or sec-ext-size is: integer [ [ [ [ PAGE[S] BYTE[S] REC[S] MEGABYTE[S] ] ] ] ] The default is 16 pages for the primary extent and 64 pages for each secondary extent. The default unit type is PAGE.
Considerations—EXTENT To ensure adequate space for your file, choose extent sizes and a MAXEXTENTS value large enough to accommodate the amount of data you expect to store in the file. Using large extents can improve performance by reducing the number of seeks. The disadvantage of large extents is that an entire extent is allocated simultaneously, leaving allocated but unused space on the disk while the extent contains only a small amount of data.
F FC Command FC is an SQLCI command with which you can retrieve, edit, and reexecute a command in the history buffer. See HISTORY Command on page H-4 for more information. 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 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. To stop editing, press the Return key without entering any editing commands. After you modify the last line and accept it by pressing the Return key, the revised command executes.
FETCH Statement To execute the query again but save typing effort, you can use the FC command, specifying the relative position of the SELECT command in relation to the current command, FC: >> FC -2 >> SELECT SUPPNAME, CITY, STATE FROM INVENT.SUPPLIER . +> WHERE SUPPNUM = 4; . DDDDDDDDDDDDDDDDD +>; The first five rows of the table SUPPLIER are listed. • Suppose you have executed several commands since you entered the SELECT command.
Considerations—FETCH Use this option in dynamic SQL if you know the number and data types of the returned columns. USING DESCRIPTOR :sqlda-desc (dynamic SQL only) is a host variable containing an SQLDA descriptor that describes a list of memory locations (not always declared host variables) into which corresponding SELECT columns are copied. Use this option in dynamic SQL if you have no previous knowledge of the returned columns and use DESCRIBE to retrieve their descriptions.
Examples—FETCH Avoid using SQLCODE 100 as an end-of-file indicator. SQL resets SQLCODE to 0 when you close the associated cursor. Instead, define your own end-of-file flag. Examples—FETCH • In the following example, assume you have a cursor that returns information from the PARTS table. The host variables are declared in a Declare Section, and the cursor declaration lists the columns to be retrieved. The FETCH statement lists a corresponding host variable to receive the values returned for each column.
File Attributes EXEC SQL CLOSE CURSOR1 END-EXEC. • The following steps demonstrate a dynamic SQL FETCH. The code uses FETCH with USING DESCRIPTOR to return information on SELECT columns of which there is no previous knowledge. (The example uses the SQL statement terminator for C, Pascal, and TAL programs.) 1. Declare an SQLDA to hold input parameters and name the area SDAI; NAMESINPUT is the names buffer. The values 5 and 39 are arbitrary values chosen for the size of the SQLDA and the names buffer.
File Attributes SQL uses default values. Many attributes can be changed later (with statements such as ALTER TABLE or ALTER INDEX), some attributes remain in effect for the life of the object, and a few can change as a side effect of a command or a change to some other attribute. The following table summarizes the file attributes important for SQL objects. Because SQL objects reside in Guardian files, all the attributes listed are attributes of Guardian files.
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.
FILEINFO Command must be familiar with the three physical file organizations available for SQL tables: keysequenced, entry-sequenced, and relative. • Key-sequenced file organization In key-sequenced files, records are stored in sequence by primary key or clustering key. The key can be supplied by the user, generated by the system, or built from values supplied by the user and a value generated by the system. You cannot update columns in a primary or clustering key.
FILEINFO Command FILEINFO is similar to FUP INFO, although its displays are slightly different. FILEINFO qualified-fileset-list [[,]fileinfo-option] ... ; fileinfo-option is:} { { { { { { { { USER [ group.member [ group-ID,member-ID [ "user-name" [ user-ID [ BRIEF | DETAIL ] EXTENTS STAT[ISTICS] [ , PARTONLY ] SHADOWS ] ] ] ] } } } } } } } } qualified-fileset-list is a qualified fileset list that specifies files for which to display information. See Qualified Fileset List on page Q-1 for details.
Considerations—FILEINFO that you own. If you omit the USER option, FILEINFO displays information about all files in qualified-fileset-list. BRIEF | DETAIL specifies whether to display brief or detailed information about each file. BRIEF, the default, displays only a single line of information. EXTENTS displays information on the allocation of extents for each file. Extent information appears for tables, indexes, collations, and Enscribe files, but not for views or OSS files.
BRIEF Display for SQL Objects and Guardian Files Use a later version of FILEINFO (that is, a later version of NonStop SQL/MP) to display information about such an object. FILEINFO returns error -9133 if it encounters an SQL object (but not an SQL program or catalog) that has a version greater than the version of the NonStop SQL/MP software on the node on which the object resides. No version of FILEINFO can access such an object, which might exist because of a fallback situation.
BRIEF Display for SQL Objects and Guardian Files F An ALTER TABLE or ALTER INDEX operation using the WITH SHARED ACCESS option left unreclaimed free space on disk, run FUP RELOAD. For more information about FUP RELOAD, see the File Utility Program (FUP) Reference Manual. S File is a shadow label, a temporary file label for an object that has been dropped which exists until the transaction is committed, aborted, or rolled back, or until a TMF file recovery is performed.
DETAIL Display for Objects (Except Views) and Guardian Files REC BLOCK Codes that appear -> X P E A In K P Pg R S Ta Vi Character Code Meaning Position 1 X Secondary Partition 2 P Partitioned Table, protection view or file Entry-sequenced file structure 3 E Key-sequenced file structure K Relative file structure R View or unstructured file Blank Enscribe file with alternate key 4 A Protection view P Shorthand view S Tables, indexes, collations, and Blank files without alternate keys Index 5-6 In SQL objec
DETAIL Display for Objects (Except Views) and Guardian Files The name of the object or file and the date when the listing is produced appear in the first line. The numbers in the leftmost column do not appear in the listing. These numbers relate sections of the listing to the notes and explanations that follow. file-name date-and-time 1. object-type 2. CATALOG catalog-name 3. VERSION version-number 4. PROGRAM CATALOG VERSION version-number 5. PROGRAM FORMAT VERSION version-number 6.
DETAIL Display for Objects (Except Views) and Guardian Files 14. ODDUNSTR REFRESH AUDIT BUFFERSIZE BUFFERED AUDITCOMPRESS VERIFIEDWRITES SERIALWRITES INCOMPLETE SQLDDL OPERATION UNRECLAIMED FREESPACE 15. OWNER group-id, owner-id SECURITY security-info: rwep, PROGID, CLEARONPURGE, LICENSE NOPURGEUNTIL: expire-time 16. SECONDARY PARTITION 17. MODIF: modif, open-state CREATION DATE: create-time REDEFINITION DATE: redefinition-time LAST OPEN: last-open-time 18. EOF eof (percent-used% USED) 19.
DETAIL Display for Objects (Except Views) and Guardian Files 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. PROGRAM CATALOG VERSION (PCV) indicates the oldest version catalog in which the SQL program can be registered.
DETAIL Display for Objects (Except Views) and Guardian Files • KEY key-descriptor is one or more sets of the following 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. If the row contains a system-defined primary key, the primary key is column 0; otherwise, the first column defined for the table is column 0.
DETAIL Display for Objects (Except Views) and Guardian Files 14. This section describes file attributes and flags as follows: 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. BUFFERED Writes to file are buffered. AUDITCOMPRESS Compressed audit-checkpoint messages are generated for files. VERIFIEDWRITES Writes to file are verified.
DETAIL Display for Objects (Except Views) and Guardian Files LABEL QUESTIONABLE File is in a crash-label state. OPEN File is open. QUESTIONABLE File is in a crash-open state. RECOVERY NEEDED File cannot be opened; volume recovery is needed (TMF2 files only). REDO NEEDED File cannot be opened; TMF redo file recovery is needed. UNDO NEEDED File cannot be opened; TMF undo file recovery is needed. CREATION DATE Indicates the date the table or index was created.
DETAIL Display for Views DETAIL Display for Views The following figure shows all the information that can appear if you specify the DETAIL option when you request FILEINFO for a view. file-name date-and-time object-type CATALOG catalog-name VERSION version-number BASE TABLE base-table-name PART ( [\system.] $volume ) ...
EXTENTS Display EXTENTS Display The following figure shows the EXTENTS display. file-name EXTENT extent-num ... # OF PAGES num-pages STARTING PAGE start-page date-and-time [ PART ] [ name ] The first line in the EXTENTS display is the same as the first line in the DETAIL display for tables, indexes, and Guardian files. The other fields are as follows: extent-num The ordinal extent number of the entry. The first extent is 0. NONE indicates no extents are allocated.
Examples—FILEINFO PART For partitioned files, the volume name of the partition associated with the entry. FREE TOTAL BLOCKS For key-sequenced files, the total number of unused blocks from the beginning to the EOF. FREE TOTAL RECS For relative files, the total number of empty records from the beginning to the EOF. BITMAP TOTAL BLOCKS For relative and key-sequenced files only, the number of bitmap blocks. Examples—FILEINFO For the following examples, assume that the subvolume $VOL1.
Examples—FILEINFO • The following command displays DETAIL information for the file PARTLOC: >> FILEINFO $VOL1.INVENT.PARTLOC, DETAIL; $VOL1.INVENT.PARTLOC 13 Jun 1995, 12:32 SQL BASE TABLE CATALOG $VOL1.
Examples—FILEINFO If you request the STATISTICS display, the following additional information appears below EXTENTS ALLOCATED instead of the INDEX LEVELS: TOTAL LEVEL • TOTAL AVG # RECS RECS BLOCKS AVG AVG % SLACK SLACK 1 1 1 1.0 4065 99 DATA 1 15 15.0 3899 95 FREE 0 BITMAP 1 1 1 1 1.0 4065 99 DATA 1 31 31.0 3723 89 FREE 0 BITMAP 1 PART $VOL1 $WHS2 The following command displays EXTENTS information about PARTLOC: >> FILEINFO $VOL1.INVENT.PARTLOC, EXTENTS; $VOL1.
FILENAMES Command • The following FILEINFO command displays DETAIL information for a collation: >> FILEINFO \A.$A.A.COL1, DETAIL; \A.$A.A.COL1 1 Jun 1995, 9:51 SQL CHARACTER PROCESSING RULES OBJECT CATALOG $A.
Examples—FILENAMES If ServerWare SMF is installed on your node, qualified-fileset-list can include files on $*.ZYS*. subvolumes. If you specify FROM CATALOG(S), all specified catalogs must be either logical or direct files. FILENAMES displays file names on the OUT file, which is typically your terminal. For OSS files, FILENAMES displays ZYQ names.
Examples—FILES Examples—FILES • The following example lists all files in volumes with names that end with the letter M and subvolumes on those volumes that have names that are six-characters long beginning with Z, with 00 in the fifth and sixth character positions: >> FILES $*M.Z???00; $SYSTEM.ZLOG00 ZZEV0011 ZZEV0012 ZZEV0013 ZZEV0014 ZZEVCONF >> FILES Table The FILES table is a catalog table that describes the attributes of files that contain tables and indexes.
Filesets Column Name Data Type Description 13 SERIALWRITES CHAR(1) Y if serial mirror writes N if parallel 14 VERIFIEDWRITES CHAR(1) Y if disk read follows each disk write N if not 15 BUFFERED CHAR(1) Y if writes to disk are buffered N if not 16 NOPURGEUNTIL LARGEINT SIGNED Julian timestamp of earliest time file can be purged; 0 if file can be purged any time 17 EOF INTEGER SIGNED Relative byte address of first unused byte of last block in partition or file; updated by UPDATE STATISTICS 1
Examples—Filesets Notice that a single Guardian name that includes wild-card characters can represent a fileset that includes many files. You cannot use a wild-card in the node portion of a Guardian name that specifies a fileset. You can use a DEFINE to specify a fileset, but you cannot use wild-card characters in the DEFINE name or in the file name you specify on the DEFINE. As a result, a fileset you specify with a DEFINE always consists of a single object or file.
Considerations—FREE RESOURCES CLOSE TABLES directs SQL to close tables. Otherwise, other users or programs may open them even though they are not privileged to do so. Considerations—FREE RESOURCES • Effect on buffers 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.
Examples—FREE RESOURCES to an audited table only if the FREE RESOURCES statement executes in the same transaction as the most recent OPEN of the cursor. Examples—FREE RESOURCES • The following example shows FREE RESOURCES used to release locks acquired by SQLCI on a nonaudited file. Suppose JOB is a nonaudited table and AUTOWORK AUDITONLY is ON. Because the INSERT uses REPEATABLE access, locks are not released when the SQLCI-defined transaction ends.
FUP Command COUNT Function Counts the rows returned from a query or the distinct values in a column CURRENT Function Returns the current date and time CURRENT_TIMESTAMP Function Returns a Julian timestamp for the current date and time DATEFORMAT Function Formats a date-time value DAYOFWEEK Function Returns an integer that represents a day of the week EXTEND Function Adjusts the range of fields for a data-time value JULIANTIMESTAMP Function Converts a date-time value to a Julian timestamp LINE
FUP Commands and SQL Objects If you specify fup-command-line, FUP returns you to SQLCI after the command finishes. If you omit fup-command-line, FUP prompts you for each command and returns you to SQLCI when you enter the EXIT command. FUP Commands and SQL Objects FUP commands work on SQL-compiled object program files but generally do not work on other SQL objects. Some SQLCI commands perform the same or similar operations on SQL objects.
Considerations—FUP FUP Command Works on SQL Objects Equivalent SQL Command or NonStop Utility FUP PURGE Yes (programs only) DROP, PURGE FUP PURGEDATA No PURGEDATA FUP RELOAD Yes FUP RENAME No FUP REVOKE No FUP SECURE No ALTER CATALOG, ALTER INDEX, ALTER PROGRAM, ALTER TABLE, ALTER VIEW, SECURE You can also use ALLOW, CTRL-Y, EXIT, FC, HELP, RESET, SET, SHOW, SYSTEM, and VOLUME to perform operations on files.
Examples—FUP • The following command lists processes that currently have files and objects open on subvolume ZYQ00001: >> FUP LISTOPENS ZYQ00001.*; $OSS000.ZYQ00001.Z00000T6 PID MODE SD MYTERM PROGRAM FILE NAME 00 $ZTN.#PTY03 $VOL1.S.PROG USERID SD MYTERM PROGRAM FILE NAME 175,02,0111 R -S 104,11 $COBOL.COBOL.COBOL 01 $ST.#PTY04 000,00,0000 W USERID -S 104,2 $OSS000.ZYQ00001.Z00005NM PID MODE >> The following example lists detailed information about an OSS file: >> FUP INFO $OSS001.
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 • The following 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.
Considerations—GET VERSION CATALOG, COLLATION, INDEX, TABLE, VIEW, or SYSTEM specifies the type of item for which to return the version. name is the name of the item for which to return the 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).
GET VERSION OF PROGRAM Statement • The following static SQL statement from a C, Pascal, or TAL program retrieves the version of table mytable and stores it in hostvar1: EXEC SQL GET VERSION OF TABLE mytable INTO :hostvar1; • The following static SQL statement from a COBOL85 program retrieves the version of index IDX2 and stores it in hostvar2: EXEC SQL GET VERSION OF INDEX idx2 INTO :hostvar2; • The following COBOL85 example uses dynamic SQL to retrieve the version of catalog \sysa.$vol1.
Examples—GET VERSION OF PROGRAM In programs, GET VERSION OF PROGRAM returns an integer value. The variable to receive the value must be compatible with the SQL data type UNSIGNED SMALLINT. For static SQL, specify the variable in the INTO clause of GET VERSION OF PROGRAM. For dynamic SQL, specify the variable in the RETURNING clause of the EXECUTE or specify an output SQLDA in the RETURNING USING DESCRIPTOR clause of the EXECUTE. (You cannot use GET VERSION OF PROGRAM with EXECUTE IMMEDIATE.
GOAWAY Command • The following C example uses dynamic SQL to retrieve the HOSV of the program MYPROG and store it in an output SQLDA: string2 = "GET HOST OBJECT VERSION OF PROGRAM MYPROG "; EXEC SQL PREPARE s2 FROM :string2; EXEC SQL EXECUTE s2 RETURNING USING DESCRIPTOR :mysqlda; 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.
Group Manager • The following example deletes the shadow label indexb: 43> GOAWAY indexb:S 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 DP.
Considerations—Guardian Names file-id is the name of a Guardian disk file or the name of a NonStop SQL/MP table, view, index, partition, collation, or program. The name consists of a letter followed by 1 to 7 letters or digits. This portion of the name is sometimes called the “simple file name.” Considerations—Guardian Names • Name expansion 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.
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.
Examples—HEADINGS For details of the way report writer determines headings, see the Considerations subsection in DETAIL Command on page D-43. HEADINGS { ON } { OFF } The default is ON. Examples—HEADINGS • To omit headings from reports, enter: >> SET STYLE HEADINGS OFF; HELP Command HELP is an SQLCI command that displays information about SQL statements, SQLCI commands, and other SQL-related topics.
Examples—HELP In some cases, HELP contains additional “pointer entries” that direct you to available topics. For example, if you enter HELP CREATE, SQLCI responds by listing available topics that begin with the word “create”: Enter: HELP create catalog create constraint create collation create index create system catalog create table create view • If the entire help text for a topic fits on the screen, SQLCI displays the text and returns you to the standard SQLCI prompt.
Considerations—HELP TEXT 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. The string can be 0 to 132 bytes long, but SQL issues a warning if it contains more than 77 bytes. The set of strings you specify replaces any existing help text for the column. To delete help text, specify a null string (“”).
Examples—HISTORY Examples—HISTORY • The following command displays the last five commands or statements entered during the SQLCI session: >> HISTORY 5; 4> SHOW PREPARED *; 5> VOLUME PERSNL; 6> ENV; 7> LOG; 8> HISTORY 5; Host Identifiers Host identifiers are names used in host language programs to identify data items, structures, functions, or labels declared in the programs.
Host Variables A host variable can be any valid host language variable that has a corresponding SQL data type. You can include host variables in many SQL statements and in SQL expressions.
I ICOMPRESS File Attribute ICOMPRESS is a file attribute that controls key compression in index blocks. ICOMPRESS applies only to key-sequenced tables and to indexes. { ICOMPRESS | NO ICOMPRESS } The table default is NO ICOMPRESS. The index default is the table value at index creation. Considerations—ICOMPRESS • Purpose of 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 The conditional expression has the same form as an SQL search condition, except that it cannot include subqueries. It can include any form of column name (for example, COL 3 > 5 or EMPNUM = 228). Numeric expressions within the conditional expression cannot use the AVG, COUNT, MAX, MIN, or SUM functions but can include column identifiers and report writer functions. (See Search Conditions on page S-5 or Expressions on page E-23 for further details.
IN Predicate • Use IF/THEN/ELSE to print a default value as blanks: S> DETAIL ORDERNUM, +> IF DELIV_DATE <> 0 THEN ( DELIV_DATE AS DATE * ); • Use IF/THEN/ELSE to convert values to text: S> DETAIL CUSTNUM, CUSTNAME, +> IF CREDIT = "A1" THEN ("EXCELLENT") ELSE +> ( IF CREDIT = "B1" THEN ("GOOD") ELSE +> ( IF CREDIT = "C1" THEN ("FAIR"))) +> HEADING "CREDIT RATING"; 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.
Examples—IN ° The first expression is equal to any expression in the list or to a value selected by the subquery. ° The subquery returns no values. The NOT operator reverses the value obtained from evaluating a search condition. For example, if IN is true, NOT IN is false, and so on. • The IN predicate evaluates to null if either of the following is true: ° expression1 evaluates to null.
Examples—INCLUDE SQLCA Examples—INCLUDE SQLCA • The following directives declare a version 315 SQLCA in a program: EXEC SQL INCLUDE STRUCTURES ALL VERSION 315; EXEC SQL INCLUDE SQLCA; INCLUDE SQLDA Directive INCLUDE SQLDA is a dynamic SQL directive that declares an SQL descriptor area (SQLDA) and optional names and collation buffers in a host program that uses dynamic SQL.
Considerations—INCLUDE SQLDA name-length is the maximum number of bytes in the longest column or parameter name that will be returned to the buffer. { RELEASE1 | RELEASE2 } is an obsolete clause for specifying that the version of the SQLDA should be 1 (RELEASE1) or 2 (RELEASE2). You cannot use this clause if you also use INCLUDE STRUCTURES. NonStop SQL/MP will not support this clause in the future, so you should use INCLUDE STRUCTURES to specify the version instead.
Considerations—INCLUDE SQLSA Considerations—INCLUDE SQLSA • Version management considerations By default, INCLUDE SQLSA declares a version 2 SQLSA. To request a different version, use INCLUDE STRUCTURES prior to INCLUDE SQLSA.
Considerations—INCLUDE STRUCTURES When you specify the VERSION CURRENT option, you must also include the following line at the beginning of the C program source module: #include This line includes the prototype of the SQLGETSYSTEMVERSION procedure in your C program. See the C/C++ Programmer's Guide for more info and examples of programs that use this option.
Index Keys • The following directive specifies version 310 for all SQLCA structures declared later in the compilation unit, but version 315 for all SQLDA and SQLSA structures declared later in the compilation unit: EXEC SQL INCLUDE STRUCTURES SQLCA VERSION 310 SQLDA VERSION 315 SQLSA VERSION 315; Index Keys An index is stored in a key-sequenced file.
INDEXES Table INDEXES Table The INDEXES table is a catalog table that describes primary keys and indexes. The following table describes the contents of the INDEXES table.
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.
Considerations—INFO DEFINE You can specify define as a DEFINE template. A DEFINE template allows you to use these special characters as part of a name: ** Matches all DEFINE names =* Matches all DEFINE names In TACL or OSS, but not in SQLCI, you can also use the following special characters: * ? Matches 0 or more characters at the same position Matches one character in the same position DETAIL requests the value of each attribute for the DEFINE that has a value.
Considerations—INITIALIZE SQL SQL/MP. The INITIALIZE SQL command is required whenever you install a new release or interim modification to NonStop SQL/MP. INITIALIZE SQL ; Considerations—INITIALIZE SQL • If NonStop SQL/MP has not been installed on your node previously, you must create the system catalog prior to executing INITIALIZE SQL. (See CREATE SYSTEM CATALOG Command on page C-142 for more information.
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 { VALUES ( val [, val ] ...) [ insert-opt ] ... } { ( select-stmt ) [ insert-opt ] ... } { select-stmt } specifies the values to insert. Include a value for each column specified previously on the INSERT statement, specify the values in the same order as 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.
Considerations—INSERT For static SQL programs, :host-var specifies a host variable to receive the SYSKEY. For dynamic SQL programs, LASTSYSKEY specifies a place holder to store the SYSKEY. ?param is the name of a parameter that has the same function as LASTSYSKEY and is included to maintain compatibility with existing programs. Use LASTSYSKEY instead. APPEND | ANYWHERE specifies whether to add rows at the end of the table (APPEND) or anywhere in the table (ANYWHERE).
Considerations—INSERT If you insert a value into a numeric column that is not large enough, an overflow error occurs. If a value has more digits to the right of the decimal point than specified by the scale for the column definition, the value is truncated. ° INTERVAL values An INTERVAL data type is compatible only with another INTERVAL data type with the same range of INTERVAL fields.
Examples—INSERT • Buffering insert operations To allow VSBB for insert operations for a nonaudited file, use the CONTROL TABLE directive with SEQUENTIAL INSERT ON, SYNCDEPTH 0, and TABLE LOCK ON options. Additionally, specify FOR REPEATABLE ACCESS in your INSERT statement and specify IN EXCLUSIVE MODE in the LOCK TABLE statement. See CONTROL TABLE Directive on page C-72 for information on buffering INSERT operations.
INTERVAL Data Type • The following example inserts a DATETIME value into the TIME_SHIPPED column: INSERT INTO SHIPMENTS (TIME_SHIPPED) VALUES ( DATETIME "1988-10-22:08:15" YEAR TO MINUTE ); • The following example inserts a DATE value into the BIRTHDATE column of the PERSONNEL table: INSERT INTO PERSONNEL (BIRTHDATE) VALUES ( DATE "1940-10-09" ); • The following example inserts DATETIME and INTERVAL values: INSERT INTO PROJECTS VALUES ("945", DATETIME "1989-10-20" YEAR TO DAY, DATETIME "1990-10-21" YE
INTERVAL Data Type No INTERVAL unit exists to bridge a year-month interval or a day-time interval because the varying number of days in a month makes conversion on a duration basis inexact. INTERVAL { start-ym } [ (digits) ] [ TO end-ym ] { start-dt } [ TO end-dt ] start-ym and end-ym are: { YEAR } { MONTH } but the start-ym you specify must precede the end-ym you specify in the list.
Considerations—INTERVAL Data Type Considerations—INTERVAL Data Type • Compatibility with other data types A specific INTERVAL data type is compatible only with another INTERVAL data type that has the same range of INTERVAL fields. • Range of INTERVAL values An INTERVAL value can have a maximum of 18 digits, including the digits in all fields.
Examples—INTERVAL Data Type You can also determine the storage size for a column by querying the COLSIZE column of the COLUMNS catalog table. For example, the following query from SQLCI returns a column's length in bytes: >>SELECT colsize FROM columns +> WHERE tablename LIKE "%table-name%" +> AND colname = "column-name"; * Version Management Consideration The INTERVAL data type is supported on NonStop SQL/MP versions 2 and later.
INTERVAL Literals y-m is: { years[-months] } { months } d-t is: { { { { { days:hours[:minutes[:seconds[.fraction]]] hours[:minutes[:seconds[.fraction]]] minutes[:seconds[.fraction]] seconds[.fraction] fraction } } } } } start-field and end-field are: { { { { { { { YEAR MONTH DAY HOUR MINUTE SECOND FRACTION } } } } } } [ (precision) ] } The start-field you specify must precede the end-field you specify in the list of field name, and only end-field can use the precision option on FRACTION.
Examples—Interval Literals minutes is an unsigned integer that specifies a number of minutes. 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 59. Negative values are allowed, with the minus sign inside the quotes. seconds is an unsigned integer that specifies a number of seconds.
INVOKE Directive and Command 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. The record description includes the SYSKEY column of a view only if the view explicitly listed the column in its definition. Because INVOKE declares host variables that are compatible with the SQL columns, no data conversion is required at run time.
INVOKE Directive and Command record name is the unqualified name of the table or view with the suffix “^type” appended and an asterisk indicating a structure template. (For example, JOB^TYPE(*).) FORMAT { C | [ANSI|TANDEM] COBOL85 | PASCAL | SQL | TAL } specifies the language format for the record definition. The default depends on the environment. In a C program, the default is C; in a TAL program, the default is TAL; and so forth. In SQLCI, the default is SQL. The COBOL85 format can be ANSI or TANDEM.
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, as follows: C _i Pascal _i COBOL85 -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 • Authorization requirements To use the INVOKE statement on a table or view, you must have authority to read the table or view at compile time. • Multibyte character sets 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 displayable on your output device.
ISLACK File Attribute Compare the record description in the example to the SQL-format record description in the last example and notice the indicator variables for JOBCODE-I and SALARY-I that are included in the COBOL85 format but not in the SQL format. >> INVOKE $VOL1.PERSNL.EMPLOYEE AS EMP FORMAT COBOL85 +> TO COBLIB (EMPSEC); ?SECTION EMPSEC * Definition of table \SYS1.$VOL1.PERSNL.EMPLOYEE * Definition current at 10:50:32 - 03/01/94 01 EMP. 02 EMPNUM PIC 9(4) COMP.
Considerations—ISLACK • For a file expected to have little activity, you can save disk space by specifying a smaller-than-normal ISLACK value.
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 EMPLOYEE TABLE EMP_ID LAST_NAME FIRST_NAME DEPT_NUM MGR_ID SALARY 4096 5361 9069 Murakami Smythe Smith Kazuo Roger John 6410 7690 7690 3598 9069 2705 36000.00 42650.00 38760.
Examples - Joins Both queries return the following result: Murakami Nakagawa Nakamura Simpson Smith • Kazuo Etsuro Eichiro Travis James 6410 6400 6480 7600 7620 6410 6400 6480 7600 7620 Marketing - Korea Marketing - Far East Marketing - Australia Marketing - USA Marketing - USA West Examples of left outer joins The following query retrieves all rows from the EMPLOYEE table. Employees with employee numbers 5361 and 9069 are in department 7690.
JULIANTIMESTAMP Function The query returns the following data: Smythe Smith Roger John 7690 7690 ? ? ? ? The IS NULL predicate is applied to the DEPT_NUM column of the DEPT table because it appears in the join predicate and belongs to the table that is not preserved. A null value marker (?) in the column indicates that for a given department number in the EMPLOYEE table, there is no matching department number in the DEPT table.
Examples—JULIANTIMESTAMP Examples—JULIANTIMESTAMP • The following example converts a date-time value into a Julian-timestamp representation of the value. (If START_DATE is 1988-02-21:20:30, the resulting Julian timestamp would be 211439233800000000.
Examples—JULIANTIMESTAMP NonStop SQL/MP Reference Manual—142115 J -6
K Keys See one of the following more 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. The following table describes the contents of the KEYS table.
KEYS Table NonStop SQL/MP Reference Manual—142115 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.
Considerations—LIKE 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. ESCAPE char specifies a literal, host variable (preceded by a colon), or parameter that contains a single character to use as an escape character to turn off the special meaning of percent and underscore.
Considerations—LIKE • If you specify NOT, the predicate is true if the value you are comparing does not match any string to which you compare it, or is not the same length as any string to which you compare it. For example, NAME NOT LIKE “_Z” is true if the string is not two characters long or the last character is not Z. In a search-condition, the predicate NAME NOT LIKE “ABC” is equivalent to NOT (NAME LIKE “ABC”).
Considerations—LIKE The following guidelines apply to pattern matching using columns of data type CHARACTER: ° Columns of data type CHARACTER are fixed length. When a value is entered, SQL pads the value in the column with blanks if necessary. The value “JOE” inserted in a CHAR(6) column becomes “JOE “ (3 characters plus 3 blanks). In a comparison value, the condition is met only if the column value and the comparison value are the same length. The value “JOE “ will not match “JOE” but will match “JOE%”.
Examples—LIKE Suppose you want to select all names that end with the letter y. If you define the pattern-matching string as “%y@,” the following 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 • Base tables per view A view definition can include a maximum of 16 base tables in a FROM clause. • Block size The largest allowed block size is 4096 bytes. • Clustering key length The sum of the column lengths for the columns of the key cannot exceed 247 bytes. SQL appends an 8-byte SYSKEY column to the columns specified for the clustering key, making the maximum actual physical key length 255. • Column heading The SQLCI report writer imposes a maximum of 50 lines on a column heading.
Limits A comment line in a catalog table cannot exceed 132 characters. The maximum number of lines allowed is 10,000. • Constraint definition The search condition that defines a constraint must be less than 3,000 bytes. The code generated for the combined search conditions of all constraints associated with a table must be less than 31,000 bytes. • Cursors One object file can have up to a billion cursors. Assuming 300 bytes for each SQL statement object, 4 megabytes would hold 12,000 cursors.
Limits The maximum number of tables that can be specified in a FROM clause is 16. This maximum includes the underlying base tables of views. • IN predicate expressions The maximum number of expressions in the expression-list of an IN predicate is 500. • Index length The maximum length depends on the index type: Unique The sum of the length of the columns in the index cannot exceed 253 bytes.
Limits The formula for applying the limit to a partition is: PRIM + ( SEC * (MAXEXT -1) ) <= 2.1465 gigabytes PRIM Primary extent size SEC Secondary extent size MAXEXT Maximum number of extents A partition must fit on a single disk, however, so the hardware limits of a particular disk might also limit the size of a partition on that disk.
Limits If you see one of these errors, you can increase PFS size using the BINDER SET PFS integer command, as follows: 1>RENAME SQLCAT,ZZSQLCAT 2>BIND @ADD * FROM ZZSQLCAT @SET PFS 256 @SELECT LIST (* OFF) @BUILD SQLCAT ! @EXIT 3>FUP LICENSE SQLCAT Alternately, for programs executed using TACL, you can specify PFS size in the TACL RUN command. Increase PFS size selectively, only as the need arises. Keep the original copy of any program that requires a larger PFS setting.
Limits ° SQL assigns rows to partitions until it either runs out of primary key values or runs out of space declared for the table. You cannot specify partitions that would require primary key values greater than 4,294,963,199. ° The bigger your partitions are, the fewer you can specify. ° When you define a partition for a relative or entry-sequenced table, you do not specify the range of rows to be stored in the partition. SQL determines where rows are stored.
LINE_NUMBER Function The maximum possible number of tables in a database, if enough memory is available, is 32,767. Only 16 of these tables can be referred to in any given query. • Temporary file size The size of a temporary file is limited to the space available on the disk on which the file resides. Temporary files provide space for sort operations required for some queries, for creating indexes, for splitting partitions, and for loading tables.
Considerations—LINE_NUMBER You can use LINE_NUMBER in any report writer command with a print list, but SQL calculates the function value for detail lines only (not title or footing lines, for example), so it is generally useful only in DETAIL commands. [ OVER REPORT ] LINE_NUMBER [ OVER PAGE ] [ OVER break-column ] The default is OVER REPORT. OVER REPORT determines the line number by setting a count of 0 at the beginning of the report and incrementing the number by 1 at the start of each detail line.
Examples—LINE_NUMBER Examples—LINE_NUMBER • The following command 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. S> DETAIL LINE_NUMBER OVER PAGE, PARTNUM, PARTDESC; (EXPR) PARTNUM PARTDESC ----------- ------- ------------------ 1 212 SYSTEM 192KB CORE 2 244 SYSTEM 192KB SEMI 3 1403 PROC 96KB SEMI ...
Examples—LINE_SPACING LINE_SPACING also defines the increment of the SKIP clause. For example, if you set the LINE_SPACING option to 2 and then 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. (Single spacing) number is an integer in the range 1 through 32,767 that specifies how many lines to advance before printing the next report line.
Considerations—LIST A[LL] displays all rows of the result table and returns you to the standard SQLCI prompt (>>). Considerations—LIST • OUT and OUT_REPORT files Rows are listed on the OUT_REPORT file, or (if you did not specify one) on the OUT file. The default for OUT and OUT_REPORT file is the home terminal of your SQLCI process, which is typically your terminal. • Formatting reports If you are experimenting with a report format, set the LIST_COUNT option to a small number.
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 (key-value[, key-value] ... ) } } { key-specifier ALTKEY key-value } } { key-specifier ALTKEY (key-value } } { [, key-value] ...
LOAD Command key-sequence-option is: { { { { { { { { { { { { { { { PARTONLY } } { PARTOF volume-name } } } SORTED } } MAX num-records } } SCRATCH scratch-file } } DSLACK percent } } ISLACK percent } } SLACK percent } move-option is: { { { { { { { { { { { { { { { { { { { { SOURCEDICT dictionary-name SOURCEREC record-name TARGETDICT dictionary-name TARGETREC record-name MOVE { source-name TO target-name { { ( source-name TO target-name { [, source-name TO target-name]...
LOAD Command is the name (or an equivalent DEFINE) of the table or file from which to load data. in-file can be a Guardian process, device (such as a terminal or tape), unstructured disk file, or Enscribe file. out-file is the name (or an equivalent DEFINE) of an existing SQL table or Enscribe file to load. ALLOWERRORS [ ON | OFF | num ] specifies what happens when errors occur. ON skips nonconvertible records but process subsequent records.
LOAD Command FIRST { { { { { { { ordinal-record-num KEY record-spec KEY key-value KEY (key-value[, key-value] ... ) key-specifier ALTKEY key-value key-specifier ALTKEY ( key-value [ , key-value ] ... ) } } } } } } } specifies the starting record of the input file from which to begin loading. If you omit FIRST, the load operation starts with the first record. ordinal-record-num is the number of records (from the beginning of the file) to skip.
LOAD Command Specify key-value for key-sequenced files according to the description of keyvalue in the preceding KEY key-value option and under Considerations— LOAD on page L-31. PAD pad-character (for loading Enscribe files only) pads output records containing less than inrecord-length bytes with the pad-character up to the record length specified in the file label of the output file.
LOAD Command If you omit USESQLNULLS, LOAD provides no special treatment for Enscribe null characters. in-option specifies characteristics of the input file. 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.
LOAD Command In a conversion between ASCII and EBCDIC, the symbols representing each character are the same in ASCII and EBCDIC except for the following: 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 d
LOAD Command attribute of the CLASS TAPE DEFINE for the tape. If you specify values for both the RECIN clause and the RECLEN attribute, the values must match. 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.
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 The default is MAX 50000 unless an =_SORT_DEFAULTS DEFINE with VLM ON is in effect. With VLM ON, the default is MAX 1000000. (See =_SORT_DEFAULTS DEFINE on page Z-3 for more information about VLM.) For loading indexes, LOAD estimates the maximum number of input records based on the size of the base table, ignoring any value you specify for MAX.
LOAD Command If you omit this option, LOAD leaves 15 percent slack space in both the data and index blocks. If you specify DSLACK, ISLACK, and SLACK, the LOAD utility uses the DSLACK value for data blocks and the ISLACK value for index blocks. The SLACK value is ignored. move-option specifies names of elements related to the table or file and how to map source names to different target names.
LOAD Command MOVE { source-name TO target-name } { (source-name TO target-name } { [,source-name TO target-name ] ... ) } associates a field of the source with a field in the target so that data from the source field is loaded into the specified target field. Any column except a system-defined primary key can be a source or target item. source-name is the name of a DDL elementary data field item if you are loading data from an Enscribe file, or a column name if you are loading data from a table.
LOAD Command MOVEBYORDER ON is the default. TRUNC[ATION] [ ON | OFF ] specifies whether to truncate data from a source field that is longer than its target field. If TRUNC OFF is in effect, LOAD reports an error if a source field is longer than its target field. TRUNC OFF is the default. REDEFINE ( redefine-spec [ , redefine-spec ] ...
Considerations—LOAD PARALLEL EXECUTION { ON [ config-op ] | OFF } config-op is: CONFIG { config-file [ FOR index-name ] { { ( config-file FOR index-name { [, config-file FOR index-name]... ) } } } } specifies whether to load partitions of a partitioned index in parallel. The default (PARALLEL EXECUTION OFF) is to load partitions sequentially. PARALLEL EXECUTION ON applies only to one partitioned index at a time.
Considerations—LOAD For full-table loads, LOAD sets the corrupt flag on the base table and indexes before the load starts. If the operation finishes successfully, LOAD resets the flags. If the operation fails, the corrupt flag indicates that the file is unusable. If the target is a table, LOAD automatically loads any indexes on the table after it loads the table. When you load a very large key-sequenced file and the data must be sorted, you might want to use a partitioned scratch file.
Considerations—LOAD Source and Target File Types Enscribe to SQL Source Field Character Set Target Field Character Set KSC5601 PIC N PIC X Any character set PIC N Any character set For example, if the source field character set is UNKNOWN, you can copy it to a target field associated with any character set. If the source field character set is one of the nine supported ISO character sets, you can copy it only to a target field associated with that same character set.
Considerations—LOAD Rules for using CLASS TAPE DEFINEs or labeled tapes are described in the discussion of the FUP COPY command in the File Utility Program (FUP) Reference Manual. • Loading data into Enscribe files An Enscribe file into which you load data must be an existing file. LOAD does not load data into any alternate-key file associated with an Enscribe file. The target file must have default values defined for fields that do not have source fields mapped to them.
Considerations—LOAD blanks must be acceptable in that column of the source record. For example, a decimal field would not accept blanks; a character field would. • • • If the record contains an array defined by an OCCURS DEPENDING ON clause and at least one element of the array is present, then the field that contains the count must be present and the number of elements in the record must be equal to the value of the field that contains the count.
Considerations—LOAD ° The field must not be a filler field. ° LOAD ignores Level-88 CONDITION-NAME clauses and Level-66 RENAMES clauses. ° Unless you specify a REDEFINE clause in the REDEFINE option of the LOAD command, LOAD ignores the clause and uses the original field definition.
Considerations—LOAD SQL Data Type Enscribe Data Type DATETIME, DATE, TIME, or TIMESTAMP BINARY 64 A non-SQL timestamp value is represented as a Julian timestamp. INTERVAL BINARY 16, 32, or 64,based on INTERVAL precision: If < or = 4, BINARY 16 If > or = 5 and < or = 9, BINARY 32 If > or = 10, BINARY 64 (The COLUMNS table indicates the size in bytes.) A non-SQL value is represented in the smallest units specified. For example, DAYS TO SECONDS would produce a value in number of seconds.
Considerations—LOAD Compile-time errors (such as references to nonexistent fields or mapping between incompatible source and target fields) always terminate the LOAD operation. Dataloading errors (such as violations of integrity constraints and source data that exceeds the length of the target field) can be permitted or restricted with the ALLOWERRORS option. • Using the FIRST KEY option The FIRST KEY option specifies the starting point in the input table or file for a LOAD operation.
Considerations—LOAD For example, to represent a value of 1,000,000 for a four-byte INTEGER column: 1,000,000 divided by 256 is 3906, with a remainder of 64 3,906 divided by 256 is 15, with a remainder of 66 15 divided by 256 is 0, with a remainder of 15 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) The following example specifies a key value for a table with a four-column key of varying types: CHAR(2), SMALLINT, CHAR(1), and SMALLIN
Considerations—LOAD stored in four bytes. SQL stores only the portion of the DATETIME field that is declared for the column. The following example specifies a value of 1993-03-01 for a DATETIME YEAR TO DAY column: FIRST KEY (7,201,3,1) 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.
Examples—LOAD Examples—LOAD • Suppose the ODETAIL table on subvolume $VOL1.SALES is empty, and you want to load it with data from an Enscribe file named OBASE that resides on subvolume $VOL2.SALES. You also want to specify the following requirements for the load operation: ° The source file is unsorted; a temporary scratch file named $SPOOL.SCR.TEMP is to be used during the sorting phase of the operation. ° The source file is described by a DDL RECORD definition named OFORMAT.
Considerations—LOCK TABLE If you request an EXCLUSIVE lock on a table and any part of the table is locked by another user, your request waits until the lock is released, or until your lock request times out and SQL returns an error message. Considerations—LOCK TABLE • Authorization requirements LOCK TABLE requires authority to read the table. Locking a view requires authority to read the view and its underlying tables.
Examples—LOCK TABLE update. The CONTROL TABLE statement ensures the most efficient operation. COMMIT WORK automatically unlocks the table when it ends the transaction. >> VOLUME $VOL1.PERSNL; >> BEGIN WORK; >> CONTROL TABLE EMPLOYEE TABLELOCK ON; >> LOCK TABLE EMPLOYEE IN EXCLUSIVE MODE; --- SQL operation complete >> UPDATE EMPLOYEE SET SALARY = SALARY * 1.05 +> WHERE JOBCODE <> 100; --- 45 row(s) updated.
Locking • The following example locks a nonaudited table (SALES.PARTS) and then explicitly unlocks it after processing: EXEC SQL LOCK TABLE SALES.PARTS IN EXCLUSIVE MODE; EXEC SQL CONTROL TABLE SALES.PARTS TABLELOCK ON; ... EXEC SQL UNLOCK TABLE SALES.PARTS; EXEC SQL CONTROL TABLE SALES.PARTS TABLELOCK ENABLE; Locking To protect the integrity of the database, SQL provides locks on data.
Lock Duration The following table lists SQL operations that release locks and shows the effects of STABLE and REPEATABLE access mode on lock duration. The table assumes default locking (shared locks for reads and exclusive locks for updates) and also assumes the locking strategy uses the minimum number of locks. SQL can use additional locks (either row locks or table locks) if it determines that additional locks are necessary to protect data integrity or to provide faster data access.
Lock Release Summary 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 escalate the row locks to a table lock. If a process holds many row locks on the same 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.
LOCKLENGTH File Attribute COMMIT WORK 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 SQL issues a single lock against all rows for an order. (The default LOCKLENGTH would cause a separate lock for each row in the order.) CREATE TABLE SALES.ODETAIL ( ORDERNUM NUMERIC (6) UNSIGNED, —Column length PARTNUM NUMERIC (4) UNSIGNED, 6 bytes QTY_ORDERED NUMERIC (5), PRIMARY KEY ( ORDERNUM , PARTNUM ) —Key declaration ) LOCKLENGTH 6; —Lock length 6 bytes LOG Command LOG is an SQLCI command that starts or stops logging to a file.
Examples—LOG The final version of a command is written to the log file without the extra characters you enter while making changes with FC. Examples—LOG • The following command starts logging SQLCI output to the file SUBV2.MAYLOG: >> LOG SUBV2.MAYLOG; 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.
Examples—LOGICAL_FOLDING Examples—LOGICAL_FOLDING • The following report is printed with LOGICAL_FOLDING ON (by default), then with LOGICAL_FOLDING OFF (after SET LAYOUT): >> SET LAYOUT RIGHT_MARGIN 60; >> SET LIST_COUNT 2; >> SELECT * FROM INVENT.SUPPLIER; SUPPNUM SUPPNAME STREET ------- ------------------ ---------------------- CITY STATE POSTCODE -------------- ------------ ---------- 1 NEW COMPUTERS INC SAN FRANCISCO 2 CALIFORNIA DATA TERMINAL INC LAS VEGAS NEVADA 1800 KING ST.
Examples—LOGICAL_FOLDING NonStop SQL/MP Reference Manual—142115 L- 52
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.
Examples—MAX MAX is evaluated after eliminating all null values from the aggregate set. If the result set is empty, MAX returns a null value. • Indicator required for host variables 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 NonStop SQL/MP programming manual for your host language.
Message File In addition, the maximum value for MAXEXTENTS might be lower in future releases. If that occurs, existing files with higher MAXEXTENTS values will still be valid, but those files will not be able to add additional extents beyond the thencurrent maximum value of MAXEXTENTS.
Considerations—MIN 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. If you specify DISTINCT in more than one MIN function in the same statement, the functions must reference the same column. Duplicate rows are eliminated only if you specify DISTINCT; otherwise, all rows are included, whether or not you specify ALL.
MODIFY CATALOG Command labels. The internal consistency of the database is lost; catalogs (and objects they describe) cannot be accessed. The MODIFY commands (LABEL, CATALOG, and REGISTER) let you change the database to reflect the new information. Before requesting a MODIFY CATALOG operation for a catalog, check that the file labels of the catalog are accessible. Do a MODIFY LABEL operation on all catalog tables in the catalog first, if necessary.
MODIFY CATALOG Command The MODIFY CATALOG command assumes that a subvolume contains a valid catalog if the subvolume contains the catalog table TABLES (file code must be 581). The optional EXCLUDE catalog-list-2 clause specifies catalogs to be excluded from catalog-list-1. REPLACE NODENAME node-name [ ( volumeset [,volumeset] [EXCLUDE volumeset [,volumeset] ] ) ] specifies the node name to replace in the catalog tables. SQL catalog tables contain file names, and those file names contain node names.
MODIFY CATALOG Command uppercase or lowercase alphabetic characters can be specified; alphabetic characters are upshifted prior to 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. The MODIFY CATALOG command terminates immediately after reporting a fatal error.
Considerations—MODIFY CATALOG CHECKONLY specifies that the catalog tables specified in target-spec should be checked to see if they contain node names that match the criteria specified in the REPLACE clause. No catalog tables are changed. The CHECKONLY option lets you estimate the effect of running the MODIFY CATALOG command before actually modifying the catalog tables.
Considerations—MODIFY CATALOG ° Remote nodes. If you specify a remote file name or catalog name, the MODIFY command reports a nonfatal error. Therefore, each node with dependent objects or partitioned objects must have a version of NonStop SQL/MP that supports MODIFY CATALOG commands. ° User-defined SQL object files. For example, MODIFY CATALOG does not modify a node name stored in a column of a user-defined table. ° Names stored in SQL object program files.
Examples—MODIFY CATALOG the program is SQL compiled is saved in the object program file. If objects or object programs specified by the DEFINEs are moved between the time that the program is SQL compiled and the time that the program is executed, the DEFINEs must be changed to reflect the new location of the objects and object programs. This guideline is true regardless of how the database was moved and regardless of whether the MODIFY DICTIONARY utility was used to modify the node names and numbers.
MODIFY LABEL Command The following summary information is included whether you specify LISTALL or NO LISTALL: Summary Information: nnn catalog(s) require modification. nnn catalog(s) do not require modification. For a comprehensive example, see MODIFY LABEL Command on page M-11. MODIFY LABEL Command The MODIFY LABEL command modifies node numbers stored in file labels of SQL objects and SQL object programs on the local node.
MODIFY LABEL Command SQL objects include SQL catalog tables, user-defined tables, table partitions, indexes, index partitions, views, and collations. MODIFY [DICTIONARY] LABEL target-spec replace-spec WITH node-spec [ [,] option ] ...
MODIFY LABEL Command fileset is a Guardian name in which wild-card characters can be used to specify volumes, subvolumes, files, and objects. You cannot use wild-card characters in node names. 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 *.*.
MODIFY LABEL Command 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. For example, the following clause is invalid because it has 11 volumesets in the first volumeset list: ($A*,$B*,$C*,$D*,$E*,$F*,$G*,$H*,$I*,$J*,$K* EXCLUDE $AA*) If this clause is not specified, only the node number is considered.
MODIFY LABEL Command immediately after reporting a fatal error.
Considerations—MODIFY LABEL 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. The CHECKONLY option lets you estimate the effect of running the MODIFY LABEL command before actually modifying the file labels. The amount of information written to the current OUT file depends on whether LISTALL or NO LISTALL is specified.
Considerations—MODIFY LABEL • • MODIFY LABEL locks one file label at a time. The file itself is not locked. There is nothing to prevent the user from accessing a partially modified data dictionary. The user should refrain from using the database, including partitions and dependent objects on remote nodes, until the node numbers have been modified. The MODIFY DICTIONARY utility does not handle the following: ° Remote nodes.
Examples—MODIFY LABEL It is the responsibility of the user to know how the database is distributed and partitioned. Document the MODIFY commands that need to be executed—and what nodes they need to be executed on—before they are needed. Prepare scripts that execute the necessary MODIFY commands. When you add a new dependent object to the database, update the scripts. While the system is in a consistent state, you can use the DISPLAY USE OF command to locate dependent objects.
Examples—MODIFY LABEL • In the following REPLACE clause, node number 24 is replaced with node number 75 if the first three characters of the volume name are $DA: REPLACE NODENUMBER 24 ($DA*) WITH 75 • In the following REPLACE clause, node number 100 is replaced with node number 175 if the volume name is either $SAM or $CAT or if the first three characters of the volume name are $DA: REPLACE NODENUMBER 100 ($SAM,$CAT,$DA*) WITH 175 • In the following REPLACE clause, node number 100 is replaced with nod
Examples—MODIFY LABEL • The MODIFY LABEL command produces the following display when requested with the LISTALL option: Checking \SYS.$VOL.SUBVOL.T1 label. --- \SYS.$VOL.SUBVOL.T1 label was modified. Checking \SYS.$VOL.SUBVOL.T2 label. --- \SYS.$VOL.SUBVOL.T2 label was not modified. • The following summary information is included whether you specify LISTALL or NO LISTALL: Summary Information: nnn label(s) modified. nnn label(s) not modified.
Examples—MODIFY LABEL On node \B, to modify the volume moved from \A to \B: >> MODIFY LABEL $DB1.*.* +> REPLACE NODENUMBER 101 ($DB1) WITH 102; >> MODIFY CATALOG $DB1.CATSUBV +> REPLACE NODENAME \A ($DB1) WITH \B; >> MODIFY REGISTER CATALOG $DB1.CATSUBV; On node \A, to modify the references to the partition moved from \A to \B: >> MODIFY LABEL $DA1.*.* +> REPLACE NODENUMBER 101 ($DB1) WITH 102; >> MODIFY CATALOG $DA1.
MODIFY REGISTER Command \C.$DC1.SQL.T1 \B.$DB1.SQL.T1 \B.$DB1.CATSUBV \C.$DC1.SQL.T1 \C.$DC1.SQL.T1 \C.$DC1.CATSUBV 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.
MODIFY REGISTER Command To specify multiple volumes, use wild-card characters. You can use the following 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.* specifies all catalogs on the volume $DATA, while *.* specifies all catalogs on the node. *VOL* matches NEWVOL, OLDVOL1, and VOL45.
Considerations—MODIFY REGISTER Considerations—MODIFY REGISTER • • • • • • • • You must be logged on as the super ID to execute 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 • For a comprehensive example, see the MODIFY LABEL Command on page M-11. Multibyte Character Sets SQL supports two multibyte character sets: • • Kanji KS C5601 Multibyte character sets are described under the entry Character Sets and can be associated with columns, literals, host variables, and parameters. (You cannot use multibyte character sets in collations. SQL always collates multibyte character values according to the binary representation of the characters.
System Default National Character Set NonStop SQL/MP Reference Manual—142115 M-26
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 (see REPORT Option on page R-3). NAME operation-name operation-name is an SQL identifier to be the name for the operation.
Names directive is in effect at a statement's compilation, preparation, or entry (for static, prepared, or SQLCI statements, respectively), then SQL resolves names in the statement at the time the statement executes instead. See CONTROL QUERY Directive on page C-70 for more information about changing the time at which names are resolved. See DEFINEs on page D-26 for details about the resolution of DEFINE names.
Considerations—NEWLINE_CHAR character is a single-byte character to mark the end of a line in a heading string. The default is “/”. Considerations—NEWLINE_CHAR • See DETAIL Command on page D-43 for information about how to create headings. Examples—NEWLINE_CHAR • The following example sets the new-line character to an exclamation point, then uses it in a DETAIL command to create a two-line heading: >> SET STYLE NEWLINE_CHAR "!"; S> DETAIL EMPNUM HEADING "Employee!Number" CENTER, ...
Defaults b is required space mmm is a 3-character month value (JAN, FEB, MAR,APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, or DEC) dd is a 2-digit day value (01, 02, ... , 31) 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) Defaults The default is NOPURGEUNTIL 0, which specifies that the object can be purged at any time. If you specify a date but omit time, the time 00:00 is used.
Examples—NULL The following chart summarizes expression evaluation for null predicates. rvs stands for row value specification. Degree is the number of expressions in row value specification.
Using Null Values Versus Default Values You cannot store a null value in a column, either with INSERT or UPDATE, unless the column was declared to allow null values when it was created. Any row of a column that allows null values can be empty. In SQL, a column that allows null values has two extra bytes associated with it in each row. A -1 stored in those two bytes indicates that the column has a null value for that row; a 0 indicates a null value.
Determining Whether a Column Allows Nulls CC CD INTEGER INTEGER CF INTEGER NO DEFAULT DEFAULT SYSTEM NOT NULL DEFAULT NULL Allows nulls Prohibits nulls Allows nulls, default null The NonStop SQL/MP Installation and Management Guide discusses defining columns with the NULL and DEFAULT clauses in detail.
Specifying Null Values in Host Programs • The following example invokes a table description in SQL format (the default format through SQLCI). The display shows NOT NULL for columns whose definition prohibits null values. >> INVOKE OD2; -- Definition of table \SYS1.$VOL1.SALES.
Null Values and Expression Evaluation Null Values and Expression Evaluation The following chart summarizes the results of expression evaluation with null values.
Examples—NULL_DISPLAY Examples—NULL_DISPLAY • The following example adds a column with null values and prints the column twice, using a different value for the NULL_DISPLAY option each time: >> ALTER TABLE PRJ ADD COLUMN DEPT PIC X(6) DEFAULT NULL; --- SQL operation complete. >> SELECT DEPT FROM PRJ; DEPT -----? ? ? --- 3 row(s) selected.
Numeric Data Types in SQL—Binary Types Numeric Data Types in SQL—Binary Types SQL Designation Description Size or Range (1) NUMERIC(1,s) to NUMERIC(18,2) Exact binary number with optional scale; signed or unsigned for 1 to 9 digits; signed required for 10 or more digits 1 to 18 digits stored as follows: 1 to 4 digits in 2 bytes 5 to 9 digits in 4 bytes 10 to 18 digits in 8 bytes PIC S9V9 COMP to PIC S9(18) COMP Binary number; same as NUMERIC 1 to 18 digits; stored as NUMERIC SMALLINT Binary integ
Considerations—Numeric Data Types Considerations—Numeric Data Types • • • All of the preceding data types are exact data types except for the floating point types, which are approximate data types. Exact data types have greater precision. Approximate data types are subject to rounding error and should not be used for equality comparisons or other operations that require exact results.
Examples—Numeric Literals NonStop SQL/MP Reference Manual—142115 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.
Examples—OBEY OBEY. SQLCI closes the command file and prompts you for a new command. If a transaction is in progress, it is rolled back. 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 • The following example shows the contents of a simple command file that sets DEFINE values: SET DEFMODE ON; ADD DEFINE =REP, 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.
Considerations—OPEN 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. :cursor-var (dynamic SQL only) is the name of a host variable of SQL type CHAR or VARCHAR that contains the name of a cursor defined by DECLARE CURSOR. USING :var [, :var ]...
Examples—OPEN Examples—OPEN • The following program fragment declares and opens a cursor, uses FETCH to retrieve data, then closes the cursor: EXEC SQL DECLARE CURSOR1 CURSOR FOR SELECT COL1, COL2, COL3 FROM =PARTS WHERE COL1 >= :HOSTVAR1 ORDER BY COL1 BROWSE ACCESS; EXEC SQL OPEN CURSOR1; EXEC SQL FETCH CURSOR1 INTO :HOSTVAR1, :HOSTVAR2, :HOSTVAR3; EXEC SQL CLOSE CURSOR1; OSS NAMES OSS names are names used for files that belong to the Open System Services environment on a Tandem NonStop System, rath
OUT COMMAND 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. OUT [ list-file [ CLEAR ] ] ; list-file is the name of the file to which you want the output written.
Examples—OUT Examples—OUT • In the following 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 FORM name REPORT name COPIES num PAGESIZE num Spooler location. Default is #DEFAULT. Name of form. Default is blanks (no form). Name of report. Default is your user ID. Number of copies. Default is 1. Lines per page in PERUSE. (Make this the same as the PAGE_LENGTH option.) Default is 60. For information about level 3 spooling, see the Spooler Programmer's Guide.
Considerations—OVERFLOW_CHAR character is a printable, single-byte character to use as an overflow character. The default is *. Considerations—OVERFLOW_CHAR • Overriding the default overflow character You can override the default filler character for a specific print item by specifying the OC modifier in the display format for the item. See AS Clause on page A-54 for more information.
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.
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 • Placement of footing line 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. • Each PAGE FOOTING replaces the previous PAGE FOOTING Only one PAGE FOOTING command is in effect at a time.
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 • Titles and footings Each report page begins with the page title (if defined) and ends with a page footing (if defined).
Examples—PAGE_NUMBER Examples—PAGE_NUMBER • The following example prints a page number on the title line of a report: S> PAGE TITLE TAB 40, "Monthly Report -", PAGE_NUMBER AS I2; 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.
Examples—PAGE TITLE Examples—PAGE TITLE • The following command defines a title for each page of a report: S> TITLE "----- Accounts of: ", SALESREP, " -----" CENTER; The title looks like this: ----- Accounts of: 220 ----- 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.
Specifying Configuration for Parallel Index Loading • • Scratch file—By default, the sort process determines a volume for the scratch file. Record generators do not have scratch files.
Specifying Configuration for Parallel Index Loading • • • • • Default pool of CPUs in which to run the record generators and another pool in which to run the sort processes (CPU) Default pool of volumes to use for the initial set of sort scratch files for the sort processes (SCRATCH) Default pool of volumes to use for overflow storage for the sort processes, if needed (SCRATCHON) Set of volumes to exclude from overflow storage (NOSCRATCHON) Default pool of volumes to use for swap files for the record gene
Specifying Configuration for Parallel Index Loading attr is: [ [ [ [ [ [ [ [ CPU ( num ) NOSCRATCHON (scratchvol[, scratchvol]...) NUMRECS ( number ) PRI ( priority ) PROGRAM ( file-name ) SCRATCH ( scratchvol [, scratchvol ]...) SCRATCHON (scratchvol [, scratchvol]...) SWAP ( swapvol ) ] ] ] ] ] ] ] ] LOCALONLY directs SQL to run the parallel load operation and all associated sort processes on the node where the operation was initiated.
Specifying Configuration for Parallel Index Loading CPU ( num [, num ] ... ) is valid only if INDEX or BASETABLE is specified. CPU specifies one or more local or remote CPUs for the record generator or sort process. You can specify multiple CPUs only for DEFAULT CPUs. NOSCRATCHON ( scratchvol [, scratchvol ] ...) is valid only if INDEX is specified. NOSCRATCHON specifies one or more volumes to be excluded as overflow scratch volumes for the sort process.
Considerations—Parallel Index Loading SCRATCHON option. To request an overflow pool but exclude specific volumes, use the NOSCRATCHON option. If ServerWare SMF is installed on the node you specify, scratchvol can be a virtual or physical volume. If you specify a virtual volume, FastSort ignores any volumes specified in SCRATCHON or NOSCRATCHON and uses only the virtual volume for both initial and overflow scratch files. SCRATCHON ( scratchvol [, scratchvol ] ...) is valid only if INDEX is specified.
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 == Request that the $data3 sort process use $temp7 for == scratch space. CREATEINDEX INDEX \NEWYORK.$data3 SCRATCH ($TEMP7) == End of Configuration File Parameters Parameters let you provide literals for prepared DML statements or command files when you execute the statements or commands (using EXECUTE or OBEY) rather than when you PREPARE or create them. You can use parameters for literals in DML statements compiled with PREPARE or for literals in SQLCI command files.
Considerations—Parameters ?[param-name] specifies a parameter and, optionally, a name for the parameter. The name must be an SQL identifier. [ INDICATOR ] ?[indicator-param] specifies an indicator parameter to use for inserting null values into the database through the parameter, or for handling null values that might be returned to the parameter in host programs. (An indicator parameter with a value less then 0 indicates a null value; an indicator parameter with a value of 0 indicates a non null value.
Considerations—Parameters with unnamed parameters, the position of the parameters indicates which values to use for which parameters. For example, the following statements average the salaries of employees in departments with numbers between 1000 and 2000, and between 5000 and 6000: PREPARE AVGSAL FROM "SELECT AVG (SALARY) FROM PERSNL.
Examples—Parameters • • • If the target column has a numeric data type, SQL treats the parameter as DECIMAL(n), where n is the number of digits in the parameter value. If the target column has a character data type and the target column has the UNKNOWN character set associated with it, SQL treats the parameter as CHAR(n), where n is the number of bytes in the parameter value.
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 |] [ FIRST KEY { value } ] { ( value [ , value ] ... ) } [\node.][$volume.][subvol.
Considerations—PARTITION FIRST KEY { value | ( value [ , value ] ... ) } specifies the first primary key or clustering key value that can be stored in the associated partition. FIRST KEY specifies the lowest value for the partition if the column for the value has an ascending collating sequence; it specifies the highest value for the partition if the column has a descending collating sequence.
Examples—PARTITION Examples—PARTITION • The following example shows a CREATE TABLE statement that uses the PARTITION clause: CREATE TABLE \SYS1.$VOL1.SALES.ODETAIL ( ORDERNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL, PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL, UNIT_PRICE NUMERIC (8,2) NO DEFAULT NOT NULL, QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL, PRIMARY KEY ( ORDERNUM , PARTNUM ) ) CATALOG \SYS1.$VOL1.SALES ORGANIZATION KEY SEQUENCED PARTITION ( \SYS1.$VOL2.SALES.
Partitions NOPURGEUNTIL OCT 31 1997, 23:59 NO AUDIT; Some of the attributes specified apply to the entire table and some only to the primary partition. NOPURGEUNTIL, NO AUDIT, and LOCKLENGTH apply to all partitions. The example specifies NO AUDIT because the table will be loaded, after which the attribute can be changed to AUDIT. The EXTENT and MAXEXTENTS attributes apply only to the primary partition, for which \SYS1.$VOL1 and a first key of 000000 are assumed.
PARTNS Table • • You can partition tables of any file organization but you cannot partition a keysequenced table that has a system-defined primary key (as opposed to a user-defined primary key) unless it also has a clustering key. For relative and entry-sequenced tables, SQL determines the set of rows in a partition, depending on the size of the partitions and the size of the rows.
Examples—PERUSE jobs while they are in the spooler system. For a complete description of PERUSE, see the Guardian Utilities Reference Manual. PERUSE [ /run-options/ ] [supervisor] ; run-options are one or more standard run-options, separated by commas (as described in the TACL Reference Manual). supervisor is the name of the spooler supervisor that PERUSE communicates with. If supervisor is omitted, then PERUSE assumes $SPLS is the supervisor.
POSITION Function An invalid plan is a plan considered invalid by SQL because changes made after the plan was compiled might have made the plan inoperable or not optimal. A plan is invalid, for example, if an object referenced in the plan was redefined after the plan was last compiled. A plan can also be invalid for a specific program startup—but not generally invalid—if the startup-time value of a DEFINE referenced in the plan is different from the value of that DEFINE at the time the plan was compiled.
Considerations—POSITION Function occurrence specifies which occurrence of the substring to look for. occurrence must have an unsigned numeric data type with a scale of zero. The value of occurrence must be greater than zero; otherwise, SQL returns an error. If you omit occurrence, SQL searches for the first occurrence of the substring. Considerations—POSITION Function • • • • • • • • The result is returned as a two-byte signed integer with a scale of zero.
Predicates Predicates A predicate is a statement involving a comparison that evaluates to a value of true, false, or unknown (null). Use predicates within search conditions to specify criteria for choosing rows from tables or views.
Considerations—PREPARE FROM { "stmt" | 'stmt' } { :stmt-var } specifies the statement to prepare. { "stmt" | 'stmt' } (for SQLCI only) is a DCL, DDL, DML, or DSL statement enclosed in single or double quotation marks. :stmt-var (for programs only) is a host variable of a character data type that contains an SQL statement.
Examples—PREPARE You can have up to 20 prepared statements in a SQLCI session. (Programs can have more prepared statements.) Examples—PREPARE • The following program fragment uses PREPARE to compile an SQL statement stored in :INTEXT, a varying length character variable. The program constructs the SQL statement (not shown), compiles it (naming it OPERATION1), and then executes it. ... EXEC SQL PREPARE OPERATION1 FROM :INTEXT; ... EXEC SQL EXECUTE OPERATION1; ...
Primary Keys • The following SQLCI example uses a string literal within a string literal because the prepared statement includes a string literal itself. (Double quotes delimit the outer string, and two quotation marks represent one quotation mark within the string.) >> PREPARE ADDSUP FROM "INSERT INTO INVENT.SUPPLIER" +> &" VALUES (?, ?, ?, ""BEND"", ""OREGON"", ""97709"" )"; ---SQL command prepared. >> EXECUTE ADDSUP USING 572, "ULTRA-TECH", +> &"240 INDUSTRIAL WAY"; --- 1 row(s) inserted.
PROGID File Attribute 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). A print list is a set of one or more print items in a report writer command, separated by commas, as follows: print-item [ , print-item ] ... See DETAIL Command on page D-43 for the complete syntax of a print item.
Program Invalidation CLEANUP table, DROP TABLE, or PURGE table CLEANUP view, DROP VIEW or PURGE view DROP CONSTRAINT Performing any of the following operations on an object used by a program invalidates a registered program unless the program was compiled with the CHECK INOPERABLE PLANS option and the object being operated on has similarity checks enabled (explicitly for tables and views, implicitly for other objects): ALTER TABLE ADD COLUMN ALTER TABLE ADD PARTITION ALTER TABLE DROP PARTITION ALTER TABLE
PROGRAMS Table PROGRAMS Table The PROGRAMS table is a catalog table that describes object programs that have been SQL-compiled. The following table describes the contents of the PROGRAMS table.
Protection View Column Name Data Type Description 15 SIMILARITYINFO CHAR(1) Y if similarity info stored N if not 16 RECOMPILEMODE VARCHAR( 30) ALL -RECOMPILEALL ON_DEMAND -RECOMPILEONDEMAND UNKNOWN -pre version 310 17 CHECKMODE VARCHAR( 30) Value of CHECK clause: INVALID_PROGRAMS (default) INVALID_PLANS INOPERABLE_PLANS 18 REGISTERONLY CHAR(1) Y if REGISTERONLY specified N if not 19 OSSFILE CHAR(1) Y if OSS file N if not * Indicates primary key The columns PROGRAMNAME through CLEARONPURGE
PURGE Command If PURGE cannot delete objects because of missing objects or bad labels, use CLEANUP instead. PURGE [!] qualified-fileset-list[!] [ [,]option]... ; option is: { ALLOWERRORS [ ON | OFF | num ] } { [ NO ] LISTALL } { SHADOWSONLY } [!]qualified-fileset-list[!] is a qualified fileset list that specifies the items to purge. (See Qualified Fileset List on page Q-1.
Considerations—PURGE ALLOWERRORS [ ON | OFF | num ] specifies the action if errors occur: ON Attempt to purge all specified files and objects regardless of how many errors are encountered OFF Stop the purge operation after the first error is encountered num Purge all specified files and objects until the number of errors encountered exceeds the value of num If you omit the ALLOWERRORS clause completely, the default is ALLOWERRORS OFF.
Considerations—PURGE 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. When you purge a view, SQL also purges all views that depend on the purged view, except for dependent shorthand views for which you do not have purge authority. SQL invalidates the latter.
Examples—PURGE Examples—PURGE • The following command 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 the following: TABLE $VOL1.PERSNL.DEPT PURGED TABLE $VOL1.PERSNL.EMPLOYEE PURGED TABLE $VOL1.PERSNL.
PURGEDATA Command PURGEDATA Command PURGEDATA is an SQLCI utility that clears data from SQL tables and their indexes, from specified partitions of SQL tables without indexes, or from Enscribe files or specified partitions of Enscribe files. PURGEDATA works on audited files as well as on 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.
Considerations—PURGEDATA [ NO ] LISTALL specifies whether to list the name of each cleared object. 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.
Examples—PURGEDATA After pressing the Break key, you can restart a PURGEDATA operation by reentering the same command. The following sequence is permissible: >> PURGEDATA *.*.* FROM CATALOG $VOL1.SUBV1; >> (press the Break key) >> PURGEDATA *.*.* FROM CATALOG $VOL1.SUBV1; You could also use FC to reenter the PURGEDATA command.
Examples—PURGEDATA NonStop SQL/MP Reference Manual—142115 P -39
Examples—PURGEDATA NonStop SQL/MP Reference Manual—142115 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 the following 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 as follows: { group-name.user-name } { group-name.* } { group-number,user-number } { group-number,* } See Security on page S-11 for more information about user IDs.
Qualified Fileset List file-attribute restricts operations to files in fileset that have one of the following characteristics or file attributes: AUDITED BROKEN INDEX KEYSEQUENCED COLLATION CORRUPT LICENSED OPEN CRASHOPEN PARTITION ENSCRIBE PRIMARY PARTITION ENTRYSEQUENCED PROGID FORMAT1 PROTECTION VIEW FORMAT2 RELATIVE ROLLFORWARDNEEDED SAFEGUARD SECONDARY PARTITION SHORTHAND VIEW SQLPROGRAM SQL TABLE UNSTRUCTURED VIEW BROKEN and CRASHOPEN are states described under FILEINFO Command on page F-9.
Examples—Qualified Fileset List 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. Examples—Qualified Fileset List • The following command displays information for all objects on subvolume $VOL1.PERSNL that are described in the PERSNL catalog: >> FILEINFO $VOL1.PERSNL.* FROM CATALOG $VOL1.
Considerations—Quantified Predicate Considerations—Quantified Predicate • • • • QUANTIFIED is a comparison predicate. See Comparison Predicate on page C-53 for a discussion of general rules for comparisons and specific information about comparing character data (including character data associated with collations), numeric data, date-time data, and interval data. The subquery result must be a table of one column.
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 :host-identifier is a host variable of SQL type CHAR or VARCHAR that contains the statement name as declared in the host program. host-identifier must conform to the naming conventions of the host language. For more information, see Host Variables on page H-5 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]...
Examples—REPORT FOOTING Examples—REPORT FOOTING • The following example specifies a report footing that includes both a literal and a column value: S> REPORT FOOTING "End of Summary for Sales Representative", +> SALESREP CENTER; The footing looks like this: End of Summary for Sales Representative 212 REPORT Option The REPORT option controls generation of EMS messages for an SQL operation started by a statement that includes the option.
Examples—REPORT Option See the EMS Manual for more information about EMS in general. See the NonStop SQL/MP Messages Manual for information about EMS messages issued by NonStop SQL/MP software. • Default EMS reports for SQL operations The EDIT file RPTSQL on the subvolume on which NonStop SQL/MP is installed (normally $SYSTEM.SYSTEM) contains a TACL script that generates EMS reports for SQL operations.
Examples—REPORT Option \SA.3,49 Records redone : 0 recs Read rate : 0 bytes/sec Redo rate : 0 bytes/sec Progress rate : ? bytes/sec TANDEM.SQLAUDIT.D30 000001 Audit Fixup Initialized Time to perform initialization: 6 secs \SA.3,49 TANDEM.SQL.D30 000004 Data Copy Started \SA.3,49 TANDEM.SQL.D30 000006 Data Copy Completed Time since last status: 6 secs \SA.3,49 TANDEM.SQL.D30 000007 Online dump allowed for \SA.DK.REG1.IREL2 \SA.3,49 TANDEM.SQLAUDIT.D30 000003 Audit Fixup 1 started \SA.
Examples—REPORT Option Status ** Time since last status: 300 secs Distance to audit EOF : ? kbytes \SA.$X314 Records read : 3781 recs Records redone : 97 recs Read rate : 2370 bytes/sec Redo rate : 28 bytes/sec Progress rate : ? bytes/sec TANDEM.SQLAUDIT.D30 000002 ** Audit Fixup Status ** Time since last status: 300 secs Distance to audit EOF : ? kbytes \SA.
REPORT TITLE Command started \SA.3,49 TANDEM.SQLAUDIT.D30 000004 Audit Fixup 3 completed Duration: \SA.3,49 0 secs TANDEM.SQL.D30 000012 All partition labels have been updated, Time since last event 2 secs \SA.$X314 TANDEM.SQLAUDIT.D30 000002 ** Audit Fixup Status ** Time since last status: 105 secs Distance to audit EOF : ? kbytes \SA.3,49 Records read : 4939 recs Records redone : 277 recs Read rate : 26 bytes/sec Redo rate : 9 bytes/sec Progress rate : ? bytes/sec TANDEM.SQLAUDIT.
Considerations—REPORT TITLE CENTER centers each line of the report title between the left and right margins. If you omit CENTER, the report title starts immediately after the left margin. Considerations—REPORT TITLE • A blank line separates the report title from the body of the report. The report title appears below the page title on the first page. • Each REPORT TITLE command replaces the previous one. Only one REPORT TITLE command is in effect at a time.
Report Writer Report Writer The report writer is a component of SQLCI that allows you to produce formatted reports from rows returned by SELECT statements.
SQLCI Commands Used to Write Reports For more detailed information, see the entries for specific commands, functions, clauses, or options. (Clauses marked with an asterisk (*) are described in DETAIL Command on page D-43.) For additional information about using the report writer, see the NonStop SQL/MP Report Writer Guide. The uses of these commands, functions, clauses, and options are summarized in the tables that follow.
Style and Layout Options for Reports Command RESET REPORT Description Deletes commands from the current report definition, or deletes columns or alias from stored report formatting commands in the current report definition Resets session options to default settings Resets style options to default settings Retrieves data from tables and views Sets layout options to new values Sets session options to new values Sets style options to new values Displays the values of layout options Displays report formatting
Report Writer Clauses Option What the Option Defines Default RIGHT_MARGIN SPACE ROWCOUNT SUBTOTAL_LABEL Right margin Spaces between columns Is the row-count line generated? Label to print in a break column with subtotals Format for time Character for underlining Maximum characters in a varyinglength print item Column position displayed at the left edge of output device Device width 2 ON Asterisk (*) TIME_FORMAT UNDERLINE_CHAR VARCHAR_WIDTH WINDOW HP2:M2:S2 Hyphen (-) 80 TAB 1 Report Writer Clauses
Reserved Words Reserved Words The following words are reserved for NonStop SQL/MP. You cannot use these words as names of constraints, columns (including correlation names), cursors, or statements, but you can use these words in catalog names; in Guardian names that identify tables, indexes, views, partitions, and collations; and in host variable names.
RESET DEFINE Command RESET DEFINE Command RESET DEFINE is an SQLCI command that restores one or more DEFINE attributes in the working attribute set to their initial settings. (RESET DEFINE is similar to the TACL command ADD DEFINE.) RESET DEFINE { attr [ , attr ] ... } ; { * } attr is a DEFINE attribute whose value is to be reset to its initial value. (See DEFINEs on page D-26 for a list of DEFINE attributes.
RESET LAYOUT Command SUBVOL attribute is required (there is no default value), the attribute is set to no value. >> SHOW DEFINE *; CLASS CATALOG SUBVOL \SYS1.$VOL1.PERSNL >> RESET DEFINE SUBVOL; >> SHOW DEFINE *; CLASS CATALOG SUBVOL ?? Current attribute set is incomplete RESET LAYOUT Command RESET LAYOUT is an SQLCI report writer command that resets layout options to their default settings. Layout options affect the way a report appears on screen or on the printed page.
Examples—RESET LAYOUT Examples—RESET LAYOUT • The following command 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.
Examples—RESET PARAM 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; ...
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 in order 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>.
Considerations—RESET REPORT You can specify the SELECT option only at the standard SQLCI prompt, >>. Considerations—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.
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 BREAK_KEY DISPLAY_ERROR ERROR_ABORT ERROR_TEXT LIST_COUNT MANDATORY_REPOR T STATISTICS WARNINGS WRAP ON ON ALL OFF DETAIL ALL OFF OFF ON ON * resets all options to their default settings. See SET SESSION Command on page S-39 for more information.
Examples—RESET STYLE The style options and their default settings are: DATE_FORMAT DECIMAL_POINT HEADINGS NEWLINE_CHAR NULL_DISPLAY OVERFLOW_CHAR ROWCOUNT SUBTOTAL_LABEL TIME_FORMAT UNDERLINE_CHAR VARCHAR_WIDTH M2/D2/Y2 . ON / ? * ON * HP2:M2:S2 80 * resets all style options to their default settings. For more information, see the entries for individual options.
Considerations—RIGHT_MARGIN number is an integer in the range 1 through 255 that indicates the rightmost byte position in the report line. number must be greater than the LEFT_MARGIN layout option. The default is the width of the output device. Considerations—RIGHT_MARGIN • Report lines that extend beyond the margin continue on the next line.
Considerations—ROLLBACK WORK the nonaudited objects by using CLOSE cursor and UNLOCK TABLE or FREE RESOURCES. Considerations—ROLLBACK WORK • • • TMF transactions begin with BEGIN WORK and end with COMMIT WORK or ROLLBACK WORK. See TMF Transactions on page T-5 or BEGIN WORK Statement on page B-2 for more information. 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.
Examples—ROWCOUNT NonStop SQL/MP Reference Manual—142115 R -26
S Sample Database To help users of NonStop SQL/MP become familiar with the product's features, Tandem includes a sample database and 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 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. See SET LAYOUT Command on page S-35 for more information about layout options. [ SESSION ] { session-opt [, session-opt ] ... } { * } saves the session options you specify; session-opt is a single session option.
Examples—SAVE number is a positive integer that refers to the ordinal number of a command in the history buffer. -number is a negative integer that indicates the position of a command in the history buffer relative to the current command. See HISTORY Command on page H-4 for more information. 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.
Search Conditions You want to set up this session environment and add these DEFINEs each time you execute a particular query.To save the commands, enter the following commands: >> SAVE ENV TO SETUP1 CLEAR; >> SAVE DEFINES TO SETUP1; The first command saves the VOLUME, LOG, and SYSTEM commands in the file SETUP1.The next command appends all of the ADD DEFINE commands to SETUP1.
Considerations—Search Conditions OR specifies that the search condition is true if either of the surrounding predicates or search conditions are true. AND specifies that the search condition is true only if both the surrounding predicates or search conditions are true. Considerations—Search Conditions • Order of evaluation SQL evaluates search conditions in the following order, first to last: predicates within parentheses, NOT, AND, OR.
Examples—Search Conditions For example, the following 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—Search Conditions • The following example searches for values in rows where the quantity is less than 9, the delivery date is prior to November 2, 1991, and the order number in the ORDERS table equals the order number in the ODETAIL table: QTY_ORDERED
SECURE Command SECURE changes the security you specify for a table or protection view before it changes the ownership. SECURE changes the security of objects, programs, and files in the order in which you specify them. To change the security of a partitioned Enscribe file, you must specify the primary partition in qualified-fileset-list. SECURE then changes the security for all partitions. If ServerWare SMF is installed on your node, qualified-fileset-list cannot specify any file or object on a $*.ZYS*.
Considerations—SECURE Command OWNER { group-name.user-name } { group-number, user-number } specifies the Guardian user ID of the user who will be given ownership of the object or file. (See Security on page S-11 if you need an explanation of the contents of a Guardian user ID.) If you do not specify the OWNER option, the ownership of the object remains unchanged. PROGID [ ON | OFF ] determines the process accessor ID of a program file when the program executes.
Examples—SECURE Command If you use SECURE within a user-defined TMF transaction, all resecuring of audited objects is reversed if SECURE fails during execution. If you use SECURE outside of a user-defined TMF transaction, SQL automatically begins a system-defined transaction for each SQL object you resecure. In this case, only the resecuring of a single object is undone if SECURE fails. The SECURE operation is not protected by the TMF subsystem for an Enscribe file.
SECURE File Attribute SECURE File Attribute SECURE is a Guardian file attribute that corresponds to the security string that controls Guardian security for a table, index, collation, protection view, or Guardian file. SECURE “rwep” "rwep" is a four-character string that specifies the Guardian read, write, execute, and purge security for an object or file. (See Security on page S-11 for more information.
User IDs NonStop SQL/MP objects, SQL programs in Guardian files, and other Guardian files can optionally use the Safeguard security management facility, a product that provides security features beyond those of standard Guardian security. The Safeguard subsystem can secure NonStop SQL/MP objects at the volume or subvolume level and can secure all other Guardian files at the volume, subvolume, or file level.
Process Access IDs group 255 and user number 255. Normally (by convention), both group 255 and user 255 in group 255 are named SUPER; for example: 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.
File Ownership File Ownership Each NonStop 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. You can change the owner of an SQL object with an appropriate ALTER command or with the SECURE command. A generalized owner of an object or file is any user ID that has ownership privileges for the file.
Authorization Requirements for SQL Statements but the generalized owner includes the owner user ID, group manager, and super ID on other nodes in the network. Authorization Requirements for SQL Statements To access an object in a NonStop SQL/MP database, an executing process (an SQLCI session or a host program) must have a processor access ID with the appropriate authority based on the security string associated with the object. Different SQL statements have different authorization requirements.
Authorization Requirements for SQL Statements Authorization Requirements for SQL Statements Statement Authority Required DCL LOCK TABLE UNLOCK TABLE Read authority to the table or view and to underlying tables of the view DDL* ALTER* Generalized ownership of the object (or for an index, of the underlying table), program file, or catalog being altered; for a program, you must also have read and write authority to the catalogs that describe the program and the objects referenced in the program COMMENT*
Authorization Requirements for SQL Statements Statement Authority Required DML DELETE INSERT UPDATE Read and write authority to the table or protection view being deleted or modified; read authority to the tables, protection views, and underlying tables of shorthand views in subqueries of the statement OPEN FETCH Read authority to the tables, protection views, and underlying tables of shorthand views referred to in the SELECT statement that defines the cursor; write authority, too, if the cursor is FO
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 the purpose of removing duplicates. The default is ALL. select-list specifies the columns to select from the intermediate table described by the FROM clause, as follows: 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 See Locking on page L-44 for more information about locking. 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. For example, if you specify AGE, the result table contains one group of rows with AGE equal to 40 and one group with AGE equal to 50.
SELECT Statement colname and colnum are as previously described for the GROUP BY clause, with the following additional restrictions: • • • If you specify DISTINCT, colname must be in select-list. If you specify a GROUP BY or HAVING clause, the ordering column must also be a grouping column. If an ORDER BY clause applies to a union of SELECT statements, the ordering column must be explicitly referenced, outside a function or an expression, in select-list of the leftmost SELECT statement.
Considerations—SELECT FOR UPDATE OF column [ , column ] ... (only for dynamic SQL statements that are not subqueries) associates a list of updateable columns with the statement so that a cursor can be declared for the statement. Considerations—SELECT • Authorization requirements SELECT requires authority to read all views and tables referred to in the statement, including the underlying tables of all shorthand views referred to in the statement.
Considerations for UNION If you include a GROUP BY clause, the columns you refer to in expressions in the select list must be either grouping columns or arguments of a function. There will be no more than one row in the result table for each group. For example, if AGE is not a grouping column, you can refer to AGE only in a function invocation, such as AVG (AGE). If you do not include a GROUP BY clause, but you specify a function in the select list, all rows of the result table form a group.
ORDER BY clause and UNION operator • • • • If both columns are of approximate (floating point) numeric data types, then RESULT contains an approximate numeric value whose precision is equal to the greater of the two contributing columns. If both columns are of date-time data types, then RESULT contains a DATETIME value whose precision is the most significant start field to the least significant end field from the ranges of DATETIME fields in the contributing columns.
GROUP BY Clause, HAVING Clause, and the UNION Operator GROUP BY Clause, HAVING Clause, and the UNION Operator In a query containing a UNION operator, the GROUP BY or HAVING clause is associated with the SELECT statement that it is a part of (unlike the ORDER BY clause, which is associated with the result of a union operation). The groups are visible in the result table of the particular SELECT statement. The GROUP BY and HAVING clauses cannot be used to form groups in the result of a union operation.
Examples—SELECT • The following 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 1. Join the tables. EMPLOYEE Table EMPNUM ... JOBCODE JOB Table ... SALARY JOBCODE JOBDESC 1 100 175500 100 MANAGER 1 100 175500 200 PROD SUPV . . 1 100 175500 900 SECRETARY 568 300 39500 100 MANAGER 568 300 39500 200 PROD SUPV 568 300 39500 900 MANAGER 2. Drop rows with unequal job codes. EMPLOYEE Table EMPNUM ... JOBCODE 1 JOB Table ... 100 . 207 JOBCODE JOBDESC 175500 100 MANAGER . 420 . 33000 . 568 SALARY 420 . ENGINEER 300 .
Examples—SELECT 4. Process select list, leaving only four columns. EMPLOYEE Table JOB Table JOBDESC FIRST_NAME LAST_NAME SALARY SALESREP TIM WALKER 32000 . . SECRETARY JOHN . . FOLEY . SALESREP 28000 . MARK . . CHOU . ENGINEER • . . 33000 DESIREE . EVANS 39500 The following SQLCI example selects from three tables and groups the rows by job code and (within job code) by department number. Only job codes 300, 420, and 900 are selected.
Examples—SELECT >> VOLUME $VOL1.SALES; >> SELECT ORDERNUM, SUM (QTY_ORDERED * PRICE) • +> FROM PARTS P, ODETAIL O +> WHERE O.PARTNUM = P.PARTNUM AND ORDERNUM IN +> (SELECT ORDERNUM FROM ORDERS WHERE CUSTNUM IN +> (SELECT CUSTNUM FROM CUSTOMER +> WHERE STATE = "CALIFORNIA" ) ) +> GROUP BY ORDERNUM; The following SQLCI example selects the value in the AUDIT column of the FILES catalog table in the PERSNL catalog.
SERIALWRITES File Attribute The SQLCI query and the result: >> --Print the percents >> SELECT Y.C, (100.0*COUNT(*))/(COUNT(DISTINCT X.I) * +> COUNT(DISTINCT X.I)) +> +> FROM T X, T Y GROUP BY Y.C; C (EXPR) -- --------------------- N1 50.0 N2 16.6 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.
Considerations—SERIALWRITES The index default is its table's value at index creation. Considerations—SERIALWRITES • Selecting a SERIALWRITES value For audited tables and indexes, use NO SERIALWRITES. For nonaudited tables or indexes, weigh the value of performance versus data reliability. SERIALWRITES can degrade response time, but it can also improve the reliability of data not protected by TMF auditing.
Examples—SET DEFINE For example, if you use SET DEFINE to set attribute values for class TAPE and enter the following command, the working set provides values for all attributes except FILEID: ADD DEFINE =T, CLASS TAPE, FILEID BACKUP • Attributes are set in the order they are specified. Because the CLASS attribute erases the working set, you should set the CLASS attribute first, then set values for the other attributes.
Examples—SET DEFMODE ON is the default. OFF disables the use of DEFINEs. With DEFMODE OFF, you cannot execute commands that contain DEFINE names and you cannot add DEFINEs or propagate existing DEFINEs to another process. You can modify, delete, and display information about existing DEFINEs. Examples—SET DEFMODE • The following example enables the use of DEFINEs: >> SET DEFMODE ON; SET LAYOUT Command SET LAYOUT is an SQLCI report writer command that sets layout options.
SET PARAM Command SET PARAM Command SET PARAM is an SQLCI command that sets values for parameters in your SQLCI session. SET PARAM overrides parameter values you set prior to entering SQLCI, but only for the duration of the SQLCI session. SET [ PARAM ] ?param value [ , ?param value ]... ; value is: { literal } { CURRENT_TIMESTAMP } { COMPUTE_TIMESTAMP (date) } ?param is the name of the parameter to receive a value. An SQL parameter name is an SQL identifier preceded by a question mark.
Considerations—SET PARAM 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 Microsecond, from 0 through 999, 1 to 3 digits date cannot be an expression. Considerations—SET PARAM • Data types for parameters SQL determines the data type for a parameter based on how you use the parameter in an SQL statement.
Examples—SET PARAM Before you can execute the SELECT statement, you must specify the state and part number with a SET PARAM command, as shown: >> SET PARAM ?ST TEXAS, ?PN 4103; >> OBEY FINDSUP2; You do not have to enclose TEXAS in quotation marks because SQLCI determines from the STATE column definition that the column has a character data type.
SET SESSION Command The following values are inserted in ORDERS: ORDERNUM 600480 ORDER_DAT E (timestamp for current date and time) DELIV_DATE (timestamp for 7/5/88) SALESREP 221 CUSTNUM 7654 After setting a different order number and customer number, you execute the OBEY file again: >> SET PARAM ?ONUM 600481, ?CUSTNUM 123; >> OBEY INSORD; The following values are inserted into ORDERS: ORDERNUM 600481 ORDER_DAT E (timestamp for current date and time) DELIV_DATE (timestamp for 7/5/88) SALESR
SET SESSION Command AUTOWORK [ ON [ AUDITONLY ] | OFF ] specifies whether SQLCI should automatically initiate a TMF transaction when you enter a DML command and how locking works in relation to nonaudited objects locked during the transaction.
SET SESSION Command ERROR_ABORT { OFF | ON } specifies the SQLCI action when an error occurs in a noninteractive SQLCI session. (Has no effect on an interactive SQLCI session.) OFF Continue after error ON Terminate immediately when an error is encountered The default when you start SQLCI is ERROR_ABORT OFF.
SET SESSION Command MANDATORY_REPORT { OFF | ON } specifies whether SQLCI should print a report even if the associated query returns zero rows. This option has no effect when the associated query returns one or more rows. OFF Do not print a report when the query does not return any rows. (This is the default.) ON Print a report even if the query does not return any rows. The report format uses current report settings. In addition, the report contains the message “--- No rows selected.
Considerations—SET SESSION Output device widths are as follows: Terminal 80 bytes Disk File Unstructured or EDIT 80 bytes Structured Record length of file Process (for example, Spooler or background) 132 bytes; 255 if RIGHT_MARGIN > 132) Printer 132 bytes; 255 if RIGHT_MARGIN > 132 Considerations—SET SESSION • You can set up your SQLCI session environment by including the sqlcicommand option when you start SQLCI.
Examples—SET SESSION Executing Command OBEY Command BREAK_KEY ON BREAK_KEY OFF Command terminates; SQLCI prompts. Control returns to previous owner. BREAK_KEY ON or OFF FC command Command terminates and SQLCI prompts; BREAK_KEY has no effect. Other SQLCI commands (whether or not within user-defined transaction) Command terminates; control returns to previous owner; BREAK_KEY has no effect. The previous owner is usually the process from which you started the SQLCI session.
Examples—SET SESSION Break key is pressed, SQLCI returns control to the command interpreter until you type PAUSE to resume your operation. >> SET BREAK_KEY OFF; >> OBEY ORDUPDT; (Break key is pressed.) 5> PAUSE • Suppose the BREAK_KEY option is ON. During a TMF transaction that updates the price of each part in the PARTS table by 5 percent, you press the Break key. SQLCI rolls back the transaction. >> SET BREAK_KEY ON; >> BEGIN WORK; >> UPDATE INVENT.PARTS SET PRICE = PRICE * 1.O5; (Break key is pressed.
SET STYLE Command 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 ] ... ; option is: { { { { { { { { { { { DATE_FORMAT DECIMAL_POINT HEADINGS NEWLINE_CHAR NULL_DISPLAY OVERFLOW_CHAR ROWCOUNT SUBTOTAL_LABEL TIME_FORMAT UNDERLINE_CHAR VARCHAR_WIDTH date-format } { ".
Examples—SET STYLE Examples—SET STYLE • The following command 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. You can use SETSCALE in SQL statements in C, Pascal, or TAL programs.
Examples—SETSCALE To use SETSCALE in an expression, you must apply SETSCALE to each operand individually rather than to the result of the expression. For example, the following expression adds two prices with a scale of two decimal places: SETSCALE (:PRICE1, 2) + SETSCALE (:PRICE2, 2) Examples—SETSCALE • The following C program fragment uses SETSCALE with an INSERT to create a new row with the value 98.34 in the PARTS.PRICE column after storing the value in host variable :HV1.
SHOW CONTROL Command 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. SHOW CONTROL ; Examples—SHOW CONTROL >> SHOW CONTROL; --------------------------------------------Current Environment --------------------------------------------CONTROL TABLE ORDERS TABLELOCK OFF CONTROL TABLE ORDERS TIMEOUT 10.
Considerations—SHOW DEFINE Considerations—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 • The following command displays two parameter values: >> SHOW PARAM ?PNUM, ?ST; • ?PNUM 4103 ?ST TEXAS The following command 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.
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. See SET SESSION Command on page S-39 for an explanation of each session option.
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 • ° ° ° ° ° ° 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 The following file attributes can differ: ALLOCATE LOCKLENGTH SECURE AUDITCOMPRESS MAXEXTENTS SERIALWRITES BUFFERED NOPURGEUNTIL TABLECODE CLEARONPURGE OWNER VERIFIEDWRITES EXTENT (primary and secondary) The AUDIT f
Similarity Between Collations Tables referenced in the position of tablea in the following statements can be similar, but tables referenced in the position of tableb in the following 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.
Considerations—SPACE number is an integer in the range 0 through 255 that specifies the default number of spaces between print items. The default is 2. Considerations—SPACE • • If you specify the SPACE clause on the DETAIL command, the value you specify overrides the value of the SPACE layout option for that detail line. SQL does not print spaces before or after a string literal in a report unless you specify a heading for the report column that includes the string literal.
SQL Directive • The following 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.
SQLCI SQLCI SQLCI, the NonStop 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 Layout Options Options set by the SET LAYOUT command that define the layout of information displayed after a SELECT Style Options Options set by the SET STYLE command that determine the style of report elements such as date and time formats and the underline character Parameters Parameter values set by the SET PARAM command to substitute for parameter names when a command or statement executes Prepared Commands Commands compiled during an SQLCI session for execution later in the se
Considerations—SQLCI With NOWAIT, you should include SET BREAK_KEY OFF as an sqlcicommand option. This strategy prevents SQLCI from taking control from another process when you press the Break key. run-option is an option of the RUN command, which is described in the TACL Reference Manual. sqlci-command is any SQLCI command. You must terminate each SQLCI command with a semicolon (;), as shown in the command description.
Examples—SQLCI AUTOWORK and BREAK_KEY session options, and begins logging session input and output to the file SQLCILOG. Examples—SQLCI • The following is an example of a simple SQLCI session started from a TACL prompt to set up tables for testing. Commands entered by the user are shown in lowercase. 157>SQLCI SQL Conversional Interface - T9191D30 COPYRIGHT TANDEM COMPUTERS INCORPORATED 1987-1994 >>volume mycat; >>create table test1 (a int, b char (3)); --- SQL operation complete.
Summary of SQLCI Commands Summary of SQLCI Commands CANCEL Cancels the current SELECT CATALOG Selects a different default catalog CLEANUP Purges damaged SQL objects CONVERT Generates commands to convert Enscribe files to SQL tables COPY Copies data into an Enscribe file or SQL table, adding to existing data CREATE SYSTEM CATALOG Creates the system catalog when NonStop SQL/MP is first installed DISPLAY STATISTICS Displays statistics for a recently compiled DML statement DISPLAY USE OF Display
Summary of SQLCI Commands LOG Starts or ends session logging MODIFY CATALOG Modifies node names in SQL catalogs on the local node MODIFY LABEL Modifies node numbers in file labels on the local node MODIFY REGISTER Registers a user-defined catalog in the local system catalog OBEY Executes commands from a file OUT Specifies or closes the output file PERUSE Invokes the PERUSE utility program PURGE Purges objects or files PURGEDATA Erases data from files or tables RESET PARAM Clears paramete
SQLCODE VERIFY Checks the consistency and validity of object definitions in catalogs and file labels; lists invalid programs VOLUME Changes the default volume or subvolume ! Reexecutes a previous command For more information about a specific SQLCI command, see the entry for that command. SQLCODE SQLCODE is a variable in a host program to which SQL returns status information. For more information about SQLCODE, see the NonStop SQL/MP programming manual for your host language.
Exceptions to Conformance With Entry Level SQL 1992 NonStop SQL/MP table names are Guardian names, which allow a node and disk specification where ANSI/ISO SQL allows catalog. Guardian names also allow what NonStop SQL/MP calls a catalog where ANSI/ISO SQL allows schema. • Security The ANSI/ISO SQL security model differs overall from the Guardian security model (including the optional Safeguard security product) used by NonStop SQL/MP.
NonStop SQL/MP Features From Intermediate Level SQL 1992 SQL/MP also allows you to create nonaudited tables that are not protected even within transactions. • Updates ANSI/ISO SQL allows a positioned UPDATE on any updateable cursor. NonStop SQL/MP requires a FOR UPDATE clause on the UPDATE WHERE CURRENT statement for a positioned UPDATE.
NonStop SQL/MP Features From Full Level SQL 1992 • INNER JOIN and LEFT OUTER JOIN operators (NonStop SQL/MP uses the keywords LEFT JOIN instead of LEFT OUTER JOIN for this operator.
NonStop SQL/MP Extensions to SQL 1992 • • • • • Additional data types: ° LARGEINT (64-bit integers) and PIC data types ° UNSIGNED/SIGNED option for numeric data types Additional table features: ° ° ° ° ° ° ° ° ° Ascending or descending order for user-defined primary keys System-generated primary keys Clustering keys Distributed tables System default values for columns Key-sequenced, entry-sequenced, and relative file types Partitioned tables Nonaudited tables Other Guardian file attributes for table
Statements • • • • ° ° ° ° ° DAYOFWEEK EXTEND JULIANTIMESTAMP SETSCALE UPSHIFT Predicates can have multiple values (for example, a, b < 10, 20) SHARE and EXCLUSIVE lock modes let you share or restrict access to locked data. Objects referred to in SQL statements can be reassigned to different objects at run time by using DEFINEs. The WHENEVER directive includes an SQLWARNING option and allows a CALL statement.
Summary of SQL Statements Summary of SQL Statements ALTER CATALOG Statement Alters security for a catalog ALTER COLLATION Statement Renames or alters security for a collation ALTER INDEX Statement Renames, adds, or drops partitions, or alters security or other attributes of an index ALTER PROGRAM Statement Renames or alters security of a SQL program in a Guardian file ALTER TABLE Statement Renames, alters security or file attributes, or enables or disables similarity checks for a table.
Summary of SQL Statements CREATE TABLE Statement Creates a base table CREATE VIEW Statement Creates a view DECLARE CURSOR Statement * Defines a cursor DELETE Statement Deletes rows from a table or view DESCRIBE Statement * Returns information about output variables of prepared statements DESCRIBE INPUT Statement * Returns information about input variables of prepared statements DROP Statement Drops a catalog, collation, constraint, index, table, view, or SQL program in a Guardian file END DEC
Static SQL LOCK TABLE Statement Locks a table (or the underlying tables of a view) and associated indexes OPEN Statement * Opens a cursor PREPARE Statement Compiles a DDL, DML, DCL, or DSL statement for later execution by EXECUTE RELEASE Statement * Deallocates memory for a dynamic SQL statement referred to through a host variable ROLLBACK WORK Statement Undoes all database modifications made to audited objects during the current TMF transaction and releases all locks held by the transaction SELE
Statistics The following is an example of a static SQL statement from a COBOL85 program: EXEC SQL SELECT LAST_NAME, EMPNUM INTO :LNAME, :EMPNUM FROM EMPLOYEE WHERE DEPTNUM = 200 END-EXEC. The following is an example of a static SQL statement from a C, Pascal, or TAL program: EXEC SQL SELECT LAST_NAME, EMPNUM INTO :LNAME, :EMPNUM FROM EMPLOYEE WHERE DEPTNUM = 200; Statistics SQL has an UPDATE STATISTICS statement you can use to collect and save statistics on columns and tables.
Considerations—ServerWare SMF management with logical files, virtual volumes, and storage pools. On any ServerWare SMF node, logical files, virtual volumes, and storage pools can coexist with conventional Guardian files and volumes. In the context of ServerWare SMF, conventional Guardian files and volumes are called direct files and direct volumes. With ServerWare SMF, NonStop SQL objects can be either direct or logical files.
String Functions • On physical volumes, subvolumes named ZYS* are reserved for ServerWare SMF. Do not attempt to create objects or files on subvolumes named in this format. • If you specify a logical file name in a FILEINFO, DETAIL command, the corresponding physical file name is displayed. Similarly, if you execute FILEINFO, DETAIL for a physical file, its corresponding logical name is displayed.
Considerations—String Literals The character set is one of the single-byte character sets ISO 8859/1 through ISO 8859/9 or one of the double-byte character sets Kanji or KS C5601. (See Character Sets on page C-16 for more information about character sets.) _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.
Examples—String Literals You should not mix single-byte and double-byte characters in the same string, but NonStop SQL/MP does not prevent you from doing so. • Data validation NonStop SQL/MP does not perform data validation to ensure that characters in a string literal belong to the character set associated with the string literal.
Subqueries Subqueries A subquery is a special form of the SELECT statement that selects only for the purpose of comparison. You can specify a subquery in a comparison, EXISTS, IN, or qualified predicate of a search condition. ( SELECT [ ALL | DISTINCT ] { { { { { expression * correlation-name.* table-name.* view-name.* } } } } } FROM table-ref [, table-ref] ...
Considerations—Subqueries an EXISTS predicate or if the FROM clause refers to a single table (or view) consisting of a single column. ° A subquery cannot contain an ORDER BY clause. ° If a subquery is not part of an EXISTS, IN, or quantified predicate, and the subquery evaluates to more than one row, a run-time error occurs. ° If a subquery contains references to an outer query, the subquery might be evaluated repeatedly.
SUBSTRING Function SUBSTRING Function The SUBSTRING function extracts a substring out of a given string.
Examples—SUBSTRING Function • • • • • • If the sum of start-position and substring-length is less than zero, SQL returns an empty string (“”). If start-position is greater than the length of the character string, SQL returns an empty string (“”). If you do not specify substring-length, SQL returns all characters starting at start-position and continuing until the end of character-string.
SUBTOTAL Command 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. SUBTOTAL column [, column ] ... [ OVER break-col ] ; column identifies a column to subtotal. column must be a column with a numeric data type from the current detail line that is not an IF/THEN/ELSE column.
Examples—SUBTOTAL To identify the break group for the subtotal, the report writer prints a subtotal label (see SUBTOTAL_LABEL Option on page S-87) under the break column. If the label does not fit in the break column, the label is truncated. An asterisk is the default subtotal label. If the subtotal column is the same as the break column, both the subtotal label and the subtotal value must print under that column.
SUBTOTAL_LABEL Option The report looks like this: DEPTNUM JOBCODE SALARY BONUS ------- ------- --------------- ------------ 1000 100 137000.10 3425.00 --------------- ------------ 137000.10 3425.00 25000.75 625.02 29000.00 725.00 50000.00 1250.00 --------------- ------------ 104000.75 2600.02 --------------- ------------ 260000.85 6500.02 90000.00 2250.00 --------------- ------------ 90000.00 2250.00 26000.00 650.00 32000.00 800.
Considerations—SUBTOTAL_LABEL label is a character string to use as a label. label consists of 0 to 255 single-byte characters or 0 to 127 double-byte characters. The default is * (asterisk). Considerations—SUBTOTAL_LABEL • Use of subtotal labels The report writer uses the same subtotal label for all groups. The label prints in the break column associated with the subtotal and is truncated if it does not fit.
Considerations—SUM 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 to average from each row of the result table.The column cannot be a column from a view that corresponds to an expression in the view definition.
Syskeys another node has group manager privileges (generalized owner privileges) for objects and files secured so that their owner can purge them remotely. Syskeys A SYSKEY, or system-defined primary key, is a primary key defined by SQL rather than by the user. Tables stored in relative and entry-sequenced files or in key-sequenced files without a user-defined primary key have a primary key defined by SQL and stored in a column named SYSKEY.
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. (See CATALOGS Table on page C-9 for more information about the CATALOGS table.) The system catalog is established during the installation of NonStop SQL/MP.
Examples—SYSTEM • The following commands are not equivalent: >> SYSTEM \local-node; >> SYSTEM ; The first command shown causes the network restrictions on file-name lengths to take effect; the second command does not. See the Expand Network Management Guide for information on network file-name lengths. • SYSTEM sets the node name qualifier in the =_DEFAULTS DEFINE.
System DEFINEs =_SQL_MSG_node Sets file for SQL message text =_SQL_RECGEN_node Specifies an alternate location for the FastSort record generator =_SQL_TM_node_vol Directs SQL to use another disk for temporary tables normally allocated on the specified volume; optionally opens the tables with SYNCDEPTH 1 instead of 0 For more information about a specific system DEFINE, see the entry for that DEFINE.
System DEFINEs NonStop SQL/MP Reference Manual—142115 S -94
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 TABLES Table The TABLES table is a catalog table that contains information about tables, views, and collations. It contains a row for each table, view, and collation in the catalog, including itself and other catalog tables. The following table describes the contents of the TABLES table.
TEDIT Command TEDIT Command TEDIT is an SQLCI command that invokes the PS Text Edit editor. TEDIT [tedit-command-line] ; tedit-command-line is one or more TEDIT commands, as described in the PS Text Edit Reference Manual.
TIME_FORMAT Option TIME_FORMAT Option TIME_FORMAT is an option of the SQLCI report writer SET STYLE command that defines a default print format for items specified with AS TIME *. TIME_FORMAT “time-format” time-format is a format to use as the default format for print items specified with AS TIME *. (See AUDIT File Attribute on page A-68 for information about how to specify the formats.) The default format is HP2:M2:S2.
TIMESTAMP Data Type TIMESTAMP Data Type Data of type TIMESTAMP represents a date according to the Gregorian calendar and a time of day according to a 24-hour clock. Values of data type TIMESTAMP are equivalent to values of data type DATETIME declared as: DATETIME YEAR TO FRACTION(6) See DATETIME Data Type on page D-14 if you need additional information.
Transaction Control Statements Transaction Control Statements The statements in the following table control TMF transactions.
Rules for DDL and DML Statements Rules for DDL and DML Statements The following rules apply to DDL statements in transactions: • • A DDL statement always executes within a TMF transaction or as a series of system-defined TMF transactions. Some DDL statements on audited tables can execute within a user-defined transaction. Other DDL statements on audited tables, and all DDL statements on nonaudited tables, cannot execute within a user-defined transaction.
Rules for Host Programs If you set AUTOWORK to OFF, you can define your own TMF transaction for statements that can execute within a user-defined transaction. You must define a transaction for DML operations on audited tables. SQLCI automatically defines a transaction or series of transactions for a DDL statement unless a user-defined transaction is in effect.
TOTAL Command TOTAL Command TOTAL is an SQLCI report writer command that specifies columns in a report for which to calculate and print totals. TOTAL returns you to the first SELECT output row. TOTAL column [, column ] ... ; column identifies a column to total. The column must be a column with a numeric data type from the current detail line and cannot be an IF/THEN/ELSE column.
Examples—TOTAL Examples—TOTAL • The following example selects data, specifies a detail line, and specifies a column to total: >> SELECT * FROM PERSNL.EMPLOYEE WHERE DEPTNUM = 3000; S> DETAIL EMPNUM, SALARY; S> TOTAL SALARY; The output might look like the following: EMPNUM SALARY ------ ----------- 1 175500.00 ... --------------------489075.
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. The following table describes the contents of the TRANSIDS table. The TRANSIDS table was created in version 1.
Considerations—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. Considerations—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 Always follow an UNLOCK TABLE statement with a CONTROL TABLE statement with the TABLELOCK ENABLE option, as shown in the examples. The CONTROL TABLE statement provides information at compile time, unlike the UNLOCK TABLE statement, which is in effect only at execution time. When you specify the default value, TABLELOCK ENABLE, the executor can determine at run time whether a table lock is necessary.
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 STABLE. See Access Options on page A-1 for a detailed discussion of both STABLE and REPEATABLE. 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 higher than the actual number of rows changed. The data in the table is correct and the message correctly reports the number of times rows were updated. This behavior (a variation of the “Halloween problem” described in computer science literature) occurs when all of the following are true: ° The optimizer chooses the alternate index as the access path. ° The index columns in the equal-predicate are not changed by the update.
Examples—UPDATE 100 >0 <0 No rows satisfied the search condition A warning was issued An error occurred; the UPDATE did not complete The SQLCA records the number of rows updated. Examples—UPDATE • The following 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.
UPDATE STATISTICS Statement You must also update the DEPT table to change the MANAGER column to the employee's new number. To ensure that all of your changes take place (or that none of them do), you should perform the operation as a TMF transaction, as shown: >> VOLUME $VOL.PERSNL; >> BEGIN WORK; >> DELETE FROM EMPLOYEE WHERE EMPNUM = 23; --- 1 row(s) deleted. >> INSERT INTO EMPLOYEE +> VALUES (50, "JERRY", "HOWARD", 1000, 100, 137000.10); --- 1 row(s) inserted.
UPDATE STATISTICS Statement RECOMPILE | NO RECOMPILE specifies whether to invalidate program files that use the table affected by the UPDATE STATISTICS operation: RECOMPILE NO RECOMPILE invalidates program files, but if table has SIMILARITY CHECK ENABLED, does not invalidate files compiled with CHECK INOPERABLE PLANS. does not invalidate the program files. RECOMPILE is the default behavior. SQL uses the RECOMPILE option if you do not specify either option.
Considerations—UPDATE STATISTICS table (SAMPLE n BLOCKS) and extrapolating from that sample. The value n must be greater than zero. If you do not specify either option, SQL computes statistics based on reading all rows in partitions smaller than 1000 blocks and reading approximately 500 blocks from partitions of 1000 blocks or more. (In the latter case, SQL reads a larger sample if less than 3 percent of the total values have been sampled and 97 percent or more of the sampled values are distinct.
Considerations—UPDATE STATISTICS Except for row count, statistics are for the entire table (not for individual partitions as in earlier releases of NonStop SQL/MP). Note that the second-highest and second-lowest values reflect the column values of the entire table, rather than of a single partition. The unique entry count is the unique entry count for the entire table divided by the number of partitions in the table.
Examples—UPDATE STATISTICS prevent it from occurring at all by executing UPDATE STATISTICS at least once for any partitioned table. • Transactions When your table has many partitions—for example, 100—you might want to avoid putting the UPDATE STATISTICS statement in a user-defined TMF transaction. With many partitions, the UPDATE STATISTICS operation might take so long that TMF might have too little log file space to perform all the logging required by other TMF transactions at the time.
Considerations—UPGRADE CATALOG catalogs specifies the catalogs to upgrade. It can be a single catalog name or a name that specifies multiple catalogs by including the following wild-card characters: ? matches any single character * matches 0 to 8 characters For example, these names specify multiple catalogs: MYCAT? matches MYCAT1, MYCAT2, and MYCATX (and possibly others), but not MYCAT48 $DATA.* matches all catalogs on volume $DATA $*.
Examples—UPGRADE CATALOG another process has one of the tables in the catalogs open when you execute UPGRADE CATALOG. In addition, all indexes, views, and programs registered in the catalogs must be available for read access. For performance reasons, SQLCI sometimes keeps catalog files open for five minutes after the SQLCI command or statement that uses them finishes. This strategy can interfere with a subsequent UPGRADE CATALOG operation in the SQLCI session.
Considerations—UPGRADE SYSTEM CATALOG You can express version as an integer (2, 300, 310, 315, 320, 325, or 330) or as a string (A011, A300, A310, A315, A320, A325, or A330), but the version you specify must be greater than the current version of the catalogs you specify with catalogs. In addition, version must not be a version later than the NonStop SQL/MP software installed on the node. The default is the version of the NonStop SQL/MP software installed on the node.
Considerations—UPSHIFT Considerations—UPSHIFT • Effect of collations If the character expression you specify as an argument to UPSHIFT is associated with a collation, SQL upshifts the string based on the rules specified in that collation. If the argument is not associated with a collation, SQL converts lowercase characters to uppercase characters according to the usual rules of English.
USAGES Table refer to an object on which another object depends. The dependent object is called a using object.
User-Defined Keys 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. User-Defined Keys A user-defined primary key is made up of the columns specified in the PRIMARY KEY clause of the CREATE TABLE statement when the table is created. Values for a userdefined primary key must be unique within the table.
Utilities FILENAMES FILES FUP UPGRADE CATALOG UPGRADE SYSTEM CATALOG 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. You can also use the Guardian utilities, BACKUP, DCOM, DSAP, PUP, and RESTORE on NonStop SQL/MP objects, but you must run these utilities from TACL, not from SQLCI.
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.
Considerations—VERIFIEDWRITES Considerations—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 ServerWare SMF is installed on your node, edit-file must be either a logical or direct file.
Considerations—VERIFY For an index, VERIFY checks descriptions of the index and its base table, views of the base table, and partitions. VERIFY also checks the consistency of base table and partition entries in related file labels. For an SQL program, VERIFY checks whether the program is valid or invalid and also checks the consistency of the PCV and PFV values in related file labels. For an SQL program in a Guardian file, VERIFY also checks descriptions of the program.
Examples—VERIFY Examples—VERIFY • The following example verifies objects and locates invalid programs in the SALES catalog: >> VERIFY *.*.* FROM CATALOG SALES; --- Verifying $VOL1.SALES.ASSERTS --- $VOL1.SALES.ASSERTS Verified. --- Verifying $VOL1.SALES.BASETABS --- $VOL1.SALES.BASETABS Verified. ... (any invalid programs are listed) ... --- SQL operation complete. • The following example shows the verification of the EMPLOYEE table: >> VERIFY $VOL1.PERSNL.EMPLOYEE; --- Verifying $VOL1.PERSNL.
NonStop SQL/MP Component Versions that supports NonStop SQL/MP. The following are the NonStop SQL/MP versions and the NonStop Kernel releases with which they correspond. NonStop SQL/MP Version 1 2 300 310 315 320 325 330 Corresponding NonStop Kernel Release C10 C30 through D20 (Controlled availability release) (Controlled availability release) D30 D30 N. A. N. A. Versions 1, 2, and 315 are sometimes called NonStop SQL release 1, NonStop SQL release 2, and NonStop SQL release 1.0, respectively.
Catalog Versions Catalog Versions Each NonStop SQL/MP catalog has a version that indicates the newest-version object you can register in the catalog. When you create a catalog using NonStop SQL/MP software of version 300 or later, NonStop SQL/MP assigns the new catalog the version of the NonStop SQL software running on the node on which the catalog resides.
Host language compiler versions than the PCV of the program, but a program cannot be registered in a catalog that has a version older than the PCV of the program. • The program format version (PFV) is the version of the SQL compiler that compiled the program. It indicates the oldest version of NonStop SQL/MP software that can execute the program and the newest version of objects that the program can access.
Views The columns SUBSYSTEMNAME through CATALOGCLASS (1 through 4) were created in version 1. The columns CATALOGVERSION and CATALOGFORMAT (5 through 6) were added in version 300. Views A view is a logical table created with the CREATE VIEW statement and derived by projecting a subset of columns, restricting a subset of rows, or both, from one or more base tables or other views. A view has a file label but has no actual data separate from the data in the tables on which it is defined.
VOLUME Command Column Name Data Type Description 5 WITHCHECKOPTION CHAR (1) Y if defined with WITH CHECK OPTION N if not 6 INSERTABLE CHAR (1) Y if row inserts allowed N if not 7 VIEWTEXT VARCHAR (3000) Text of statement used to define view * Indicates primary key The VIEWS table was created in version 1 and there have been no subsequent modifications. Guardian names in the VIEWS table (including names of tables and views in the statements stored in the VIEWTEXT column) are fully qualified.
Considerations—VOLUME Considerations—VOLUME • Defaults for omitted parameters If you enter VOLUME with no parameters, SQLCI resets the current default node, volume, and subvolume to their values at the start of the SQLCI session. However, if you specify at least one parameter but omit one or more, SQLCI changes only what you specify and leaves other values unchanged.
Examples—VOLUME NonStop SQL/MP Reference Manual—142115 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.
Considerations—WHENEVER Directive CONTINUE continue with next statement GO TO :host-id pass control to location host-id CALL :host-id execute host-id (Not COBOL85) PERFORM :host-id execute host-id (COBOL85 only) host-id is an identifier that specifies a location in the host language program. For more information, see the NonStop SQL/MP programming manual for your host language. If you do not specify an action, SQL discontinues checking for the specified condition.
Considerations—WINDOW TAB number specifies an integer in the range 1 through 255 that is the print position of the output line to display at the left edge of the output device. (The first position in the output line is 1, and each print position occupies one byte.) The default is TAB 1. You can use this clause at the select-in-progress prompt (S>) or at the standard SQLCI prompt (>>). column identifies a print item in the detail print list to begin printing at the left edge of the output device.
WITH SHARED ACCESS OPTION these must be preceded by an appropriate SELECT command and followed by a LIST command to actually print a report.
Considerations—WITH SHARED ACCESS { COMMIT [WORK] commit-options } { ROLLBACK [WORK] } specifies the start time, the timeout period for lock requests, and the handling of retryable errors for the commit phase of the operation. The default is the following: COMMIT WHEN READY TIMEOUT DEFAULT ONCOMMITERROR ROLLBACK WORK See COMMIT Option on page C-46 for more information.
Considerations—WITH SHARED ACCESS 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 processes continue reading audit trails and updating target objects to maintain the ready-to-commit state. 3. Commit phase and command completion SQL begins a transaction and acquires an exclusive table lock on each source object. This stops DML transaction activity against the source objects so that the audit fix-up processes can complete their work.
Examples—WITH SHARED ACCESS Examples—WITH SHARED ACCESS • The following CREATE INDEX statement uses the WITH SHARED ACCESS option: CREATE INDEX EMPLOYE2 ON EMPLOYEE (JOBCODE) CATALOG PERSNL WITH SHARED ACCESS NAME CR_IND_EMP2 COMMIT WHEN READY TIMEOUT NEVER; NonStop SQL/MP Reference Manual—142115 W -8
Z ! COMMAND ! (exclamation point) is an SQLCI command that reexecutes a statement or command without modifying a previous statement or command in the history buffer or a current report formatting command. See HISTORY Command on page H-4 for more information. ! [ text ] [ ; ] [ [-]number ] text specifies the most recent version of a command in the history buffer or a stored report formatting command.
Considerations—=_AUDSERV_XSWAP_node volume is the name of the swap volume for the audit fix-up process on the node. Considerations—=_AUDSERV_XSWAP_node • An operation that uses the WITH SHARED ACCESS option starts an audit fix-up process on each node in the network that contains at least one source object used in the operation. (There might be more than one node for CREATE INDEX operations on network-partitioned tables.
Considerations—=_DEFAULTS VOLUME [\node.][$volume.]subvolume sets the current default node, volume, and subvolume. The file system uses the current defaults to expand a partially specified Guardian name to a fully qualified name. Considerations—=_DEFAULTS • • Processes started during a TACL session automatically inherit the =_DEFAULTS DEFINE from the TACL session, regardless of the DEFMODE setting. You can alter =_DEFAULTS explicitly using the ALTER DEFINE command.
Considerations—=_SORT_DEFAULTS =_SORT_DEFAULTS affects sorts performed as part of an SQL statement executed in parallel if you do not specify scratch and swap files in a configuration file or by some other mechanism. In this case, SQL uses the scratch and swap files specified in the =_SORT_DEFAULTS DEFINE. ADD DEFINE =_SORT_DEFAULTS, CLASS SORT [ , param value ] ...
Examples—=_SORT_DEFAULTS volume. Using =_SORT_DEFAULTS can also prevent errors caused when DML operations encounter a full disk. • • • If the swap and scratch files you specify do not exist at the time of the FastSort operation, FastSort creates them and uses MAXEXTENTS 160. If you create the files yourself, you must choose an appropriate file size.
Considerations—=_SQL_CAT_HEAP_LIMIT heap-space-size is the amount of heap space for the SQLCAT process, in megabytes. This value can range from 8 to 2047. SQL does not use the node, volume, and subvolume portions of the file name if supplied. Considerations—=_SQL_CAT_HEAP_LIMIT • • If there is an error in heap-space-size, SQL returns a warning, and SQLCAT runs with the default heap space size. The =_SQL_CAT_HEAP_LIMIT DEFINE can be used from TACL, SQLCI, or a user process (for embedded SQL).
Considerations—=_SQL_CMP_CPUS_node DEFINE is in effect. To specify a different set of available CPUs for a new parallel query, reset the DEFINE. =_SQL_CMP_CPUS_node affects only the location of Executor Server Processes (ESPs) for parallel plans. It does not affect the location of the master executor.
=_SQL_CMP_DOUBLE_SBB_OFF DEFINE • Availability of CPUs at compile time When you compile a parallel query, the compiler assigns ESPs to the CPUs specified as available in =_SQL_CMP_CPUS_node. If a CPU is unavailable when you compile the query then no ESP is assigned to that CPU, even if you specified the CPU as available in this DEFINE. In this case, the executor chooses a substitute CPU for the stranded ESP. The substitute CPU does not have to be specified as available in =_SQL_CMP_CPUS_node.
=_SQL_CMP_EQ_LIMIT DEFINE is present, the optimizer also considers using double buffering for these two types of join operations. • • If both =_SQL_CMP_DOUBLE_SBB_ON and =_SQL_CMP_DOUBLE_SBB_OFF are present, file-system double buffering will not be used. Double buffering can increase performance, but can also increase the likelihood of PFS memory overflow. See the NonStop SQL/MP Installation and Management Guide for a full discussion of how to manage double buffering.
=_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.
Examples—=_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. This selection is normally the home terminal for the executing TACL for SQLCI session, but you can specify a different home terminal for the process by specifying the TERM option in the RUN command that executes the process.
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.
Examples—=_SQL_CMP_NO_KS_MJOIN 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 keysequenced merge join is considered.
Considerations—=_SQL_cmp_node Considerations—=_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. Specifying an appropriate alternate CI2 file at initialization time is the way you install a licensed SQLCI2.
=_SQL_EXE_ESPS_CK_CMON DEFINE =_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 needs to 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. Considerations—=_SQL_EXE_ESPS_CK_CMON • In previous releases, SQL would send a message to the $CMON process before creating an ESP.
Considerations—=_SQL_EXE_USE_SWAPVOL Considerations—=_SQL_EXE_USE_SWAPVOL • You can use the =_SQL_EXE_USE_SWAPVOL DEFINE to change the location of temporary tables from serial plans that might require more space than available on the volumes where the temporary tables would normally be located, causing filesystem error 122. NonStop SQL/MP normally places temporary tables on the same volume as the outermost table in a join.
Considerations—=_SQL_MSG_node msg-file is the name of the alternate message file. Considerations—=_SQL_MSG_node • The SQL message file The SQL message file is a key-sequenced file that contains the text of most of the messages displayed by SQL. SQL retrieves messages from the file as needed. The standard SQL message file (the one Tandem releases with NonStop SQL/MP) is placed in $SYSTEM.SYSTEM.SQLMSG when NonStop SQL/MP is installed on a node. All the messages in the file are in English.
Examples—=_SQL_MSG_node ° ° ° ° ° SQLCAFSCODE SQLCATOBUFFER SQLSADISPLAY SQLSA_DISPLAY2 SQLCA_TOBUFFER2 Whether SQL closes the message file when it returns from the procedure depends on the parameters you supply in the procedure call. If the message file is left open, SQL uses the same message file the next time you call one of the procedures. If the file is closed, SQL opens the message file (possibly a different one) again the next time you call one of the procedures listed previously.
Examples—=_SQL_RECGEN_node Examples—=_SQL_RECGEN_node • The following SQLCI command specifies $DP1.TEST.RGP as the program file for the FastSort record generator on node \REG1: ADD DEFINE =_SQL_RECGEN_REGs, CLASS MAP, FILE $DP1.TEST.RGP; =_SQL_TM_node_vol DEFINE =_SQL_TM_node_vol is a system DEFINE that directs NonStop SQL/MP to create temporary tables that would normally go to the specified volume on another specified volume instead.
Examples—=_SQL_TM_node_vol NonStop SQL/MP normally places temporary tables on the same volume as the outermost table in a join. See Temporary Tables on page T-3 for more information about temporary table placement. • The disk process normally uses SYNCDEPTH 0 to access temporary NonStop SQL/MP tables. This approach saves the overhead of checkpointing operations on the tables to the backup DP2 process but can result in error 122 if a DP2 takeover occurs during execution of a statement.
Index Numbers 255, user number G-7, S-12 A A (alphanumeric) descriptor A-56 Abort transaction R-24 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-17 controlling C-74, C-80, C-81 primary E-17 ACCESS PATH option CONTROL TABLE directive C-74 controlling C-74, C-80 in EXPLAIN report E-17 Access requirements, HELP TEXT H-4 Access type, in EXPLAIN report E-17 Ad
A Index OWNER file attribute A-9 RENAME clause A-9 SECURE file attribute A-9 ALTER DEFINE command A-11 ALTER INDEX statement ADD PARTITION clause A-18 description of A-12 examples of A-24 file attributes A-15 FIRST KEY clause A-18 SECURE file attribute A-15 ALTER PROGRAM statement description of A-25 OWNER file attribute A-25 RENAME clause A-26 SECURE file attribute A-25 ALTER statements and GRANT S-68 catalog A-7 collation A-9 concurrent DML operations C-61 index A-12 program A-25 table A-27 view A-45 AL
B Index report writer option R-12 ASCII character set A-64, C-16 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-28 in VIEWS table V-9 AUDIT file attribute and BUFFERED attribute A-69 description of A-68 set by DUP D-76 turning off for loading data L-17, L-33 views A-68 views and C-160 Audit fix-up phase W-6 Audit trails A-70, W-5, W-6 AUDITCOMPRESS file attribute ALTER INDEX statement A-15 ALTER TABLE statem
C Index BLOCK, parameter for FastSort Z-4 Boolean operators S-5 Break column, subtotaling S-85 BREAK FOOTING command AS clause A-54 COMPUTE_TIMESTAMP function C-57 CONCAT clause C-58 CURRENT_TIMESTAMP function C-163 description of B-5 IF/THEN/ELSE clause I-1 Break key and CLEANUP command C-21 and command files O-2 and CONVERT operation C-95 and COPY command C-119 and DUP command D-76 and LOAD command L-33 and PURGE command P-34 and PURGEDATA command P-37 and SECURE command S-9, S-10 and SET SESSION comman
C Index CLEANUP command C-20 CONVERT command C-92 DUP command D-69 Catalog tables BASETABS B-1 CATALOGS C-9 COLUMNS C-41 COMMENTS C-46 CONSTRNT C-65 CPRLSRCE C-126 FILES F-28 INDEXES I-10 KEYS K-1 operations on C-8 PARTNS P-20 PROGRAMS P-30 retrieving statistics from U-11 selecting from S-31 summary C-7 TABLES T-2 TRANSIDS T-11 USAGES U-15 VERSIONS V-8 VIEWS V-9 CATALOGCLASS in CATALOGS table C-9 in VERSIONS table V-8 CATALOGFORMAT in VERSIONS table V-8 CATALOGNAME in CATALOGS table C-9 in PARTNS table P-
C Index Multibyte M-24 shift JIS C-17 specifying for columns D-1 summary of C-16 Tandem Kanji C-17 Tandem Korean C-17 Tandem KSC5601 C-17 Character strings comparing C-53 effect of collation on C-54 literals S-78 matching pattern L-2 Character values, display format of A-56 CHARACTERISTICS in CPRULES table C-127 CHARACTERSET in COLUMNS table C-43 in CPRULES table C-127 CHAR_LENGTH function C-18 CHECK clause CHECKMODE, in PROGRAMS table P-31 CREATE CONSTRAINT statement C-131 CREATE VIEW statement C-158 CHE
C Index associating with a column D-3 catalog description of T-2 CREATE COLLATION statement C-130 definitions C-27 description of C-38 DROP statement D-61 duplicating D-73 in expressions C-12 renaming A-9 similarity rules for S-58 versions V-7 Collations buffer DESCRIBE statement D-43 INCLUDE SQLDA directive I-6 Collector alternate R-3 EMS R-3 COLNAME in COLUMNS table C-41 COLNUMBER in COLUMNS table C-41 COLSIZE in COLUMNS table C-41 Column identifier C-39 Columns ADD COLUMN option A-31 assigning alias to
C Index ENV E-3 ERROR E-4 executing multiple O-1 EXIT E-13 FC F-1 FILEINFO F-9 FILENAMES F-26 FILES F-27 FUP F-33 GOAWAY G-6 HISTORY H-4 INFO DEFINE I-11 INITIALIZE SQL I-12 INVOKE I-24 LIST L-15 LOAD L-17 LOG L-49 MODIFY CATALOG M-4 MODIFY LABEL M-11 MODIFY REGISTER M-21 NAME N-1 OUT O-7 OUT_REPORT O-8 PAGE FOOTING P-1 PAGE TITLE P-4 PERUSE P-20 PURGE P-31 PURGEDATA P-36 reexecuting Z-1 REPORT FOOTING R-2 REPORT TITLE R-7 RESET DEFINE R-14 RESET LAYOUT R-15 RESET PARAM R-16 RESET PREPARED R-18 RESET REPO
C Index in collation definitions C-27, C-28, C-29 in programs and SQLCI C-46 line length limit L-7 number allowed per object C-44 COMMENTS catalog table C-46 COMMENTS option, CONVERT command C-92 COMMENTTEXT, in COMMENTS table C-46 COMMIT option C-46 Commit phase W-7 COMMIT WORK statement C-51 Communication, SQL and host H-5 COMPACT option, LOAD command L-23 Comparing date-time values C-54 Comparing numeric data C-54 Comparing values to subquery results Q-6 Comparison predicate description of C-53 QUANTIF
C Index BIND NAMES option C-70 description of C-69 HASH JOIN option C-70 INTERACTIVE ACCESS option C-71 MDAM option C-71 CONTROL TABLE directive ACCESS PATH option C-74 and BIND NAMES option C-70 description of C-72 displaying current values S-49 in EXPLAIN report E-17 JOIN METHOD option C-75 JOIN SEQUENCE option C-76 lock waits C-77 MDAM option C-76 OPEN option C-76 RETURN IF LOCKED option C-77 SEQUENTIAL BLOCKSPLIT option C-77 SEQUENTIAL option C-77 SKIP option C-78 STOP AT option C-78 SYNCDEPTH option
C Index description of C-109 display format C-117 EBCDICOUT option C-114 FIRST option C-112 FOLD option C-115 operations C-118 PAD option C-115 RECOUT option C-115 REPLACE SPACES WITH option C-113 REWINDOUT option C-116 SKIPOUT C-116 TMF transaction C-119 UNLOADOUT option C-116 UNSTRUCTURED option C-113 UPSHIFT option C-113 USESQLNULLS option C-113 VAROUT option C-116 Copying data from Enscribe file to SQL table C-109 data from SQL table to Enscribe file C-109 Enscribe files C-121 Correlated subqueries S-
C Index CREATE statements and concurrent DML operations C-61 CREATE SYSTEM CATALOG command C-142 CREATE TABLE statement AUDITCOMPRESS file attribute C-149 CATALOG option C-147 CLUSTERING KEY clause C-147 DEFAULT clause C-146, D-24 description of C-143 file attributes C-149 HEADING clause C-146, H-1 LIKE clause C-145 NOT NULL clause C-146 ORGANIZATION clause C-148 PARTITION ARRAY clause C-148 PARTITION clause C-148 PHYSVOL clause C-147 PRIMARY KEY clause C-147 SECURE file attribute C-149 SIMILARITY CHECK c
D Index D Damaged objects, deleting C-19 Data clearing from file or table P-36 compression of index blocks I-1 copying C-109 deleting D-37 fetching F-3 inserting I-14 loading into database L-17 modifying U-3 removing C-24 retrieval cursor declaration D-22 SELECT statement S-17 selecting S-17 size limits L-7 updating U-3 Data Control Language (DCL) authorization S-16 description of D-16 Data declaration BEGIN DECLARE SECTION directive B-2 END DECLARE SECTION directive E-2 tables and views I-24 Data Definit
D Index Date computing for report C-57, C-163 of expiration A-30 print item display format A-62 specifying default format D-8 DATE data type D-7 DATE literal D-10 DATE values in host variables H-6 LOAD command L-37 DATEFORMAT function D-13 DATETIME data syntax D-6 data type D-14 literals D-10 values, LOAD command L-37 Datetime values inserting, example of I-19 DATETIMEENDFIELD in COLUMNS table C-42 DATETIMEQUALIFIER in COLUMNS table C-42 DATETIMESTARTFIELD in COLUMNS table C-42 Date-time arithmetic E-26 d
D Index CREATE INDEX C-133 CREATE TABLE C-143 CREATE VIEW C-156 DROP D-60 HELP TEXT H-3 processing rules T-7 UPDATE STATISTICS U-7 DDL (Data Definition Language) statements D-19 DEADLOCKS D-20 DEALLOCATE file attribute A-7 DECIMAL columns, explicit lengths S-69 DECIMAL data syntax D-5 DECIMAL data types, converting to SQL C-100 DECIMAL_POINT layout option D-20, R-11 DECLARE CURSOR statement and locking D-23 description of D-22 FOR UPDATE OF clause D-22 Declare section declaration B-2 terminating E-2 Decor
D Index using with SQL programs D-27 working attribute set D-31, D-32 Defines =_AUDSERV_XSWAP_node Z-1 =_DEFAULTS Z-2 =_SORT_DEFAULTS Z-3 =_SQL_AUD_node Z-13 =_SQL_CAT_HEAP_LIMIT Z-5 =_SQL_CAT_node Z-13 =_SQL_CI2_node Z-13 =_SQL_CMP_CPUS_node Z-6 =_SQL_CMP_DOUBLE_SBB_OFF Z8 =_SQL_CMP_DOUBLE_SBB_ON Z-8 =_SQL_CMP_EQ_LIMIT Z-9 =_SQL_CMP_EVENT Z-10 =_SQL_CMP_EVENT_NO0 Z-11 =_SQL_CMP_node Z-13 =_SQL_cmp_node Z-13 =_SQL_CMP_NO_KS_MJOIN Z-12 =_SQL_EXE_DOUBLE_SHUTOFF Z14 =_SQL_EXE_ESPS_CK_CMON Z-14 =_SQL_EXE_USE_
D Index vertical printing of D-49 DETAIL option ERROR command E-5 FILEINFO command F-11, F-12, F-14, F-21 Device, output R-22 DICTIONARY option CONVERT command C-93 Dictionary, NonStop SQL/MP D-1 Directing output to a file O-7 Directives BEGIN DECLARE B-2 CONTROL EXECUTOR C-68 CONTROL QUERY C-69 CONTROL TABLE C-72 defined S-72 END DECLARE SECTION E-2 EXPLAIN E-13 INCLUDE SQLCA I-4 INCLUDE SQLDA I-5 INCLUDE SQLSA I-6 INCLUDE STRUCTURES I-7 INVOKE I-24 SQL S-60 summary S-73 Disk erasing C-24 space conservin
E Index PARTITION ARRAY attribute F-20 physical characteristics F-9 record length F-17 security F-13 security of object D-53 software version E-3 statistics S-42 statistics for command D-49 type of object F-16 use of object D-51 warning messages S-42 DISPLAY_ERROR option, SET SESSION command S-40 DISTINCT clause and null values N-9 description of D-55 SELECT statement S-19 Distinct rows, selecting S-19 Distributed Systems Management (DSM) R-3 DML statements and database concurrency C-60 authorization requ
E Index Edit files F-9 Editing a command F-1 Editor EDIT E-1 TEDIT T-3 vi F-9 Efficiency of multivalue predicates C-56 Elapsed compilation time D-49 ELSE clause I-1 Embedded SQL E-1 EMPTYOK option, LOAD command L-20 EMS See Event Management Service EMS default reports R-4 EMS (Event Management Service) R-3 END DECLARE SECTION directive E-2 Ending TMF transaction C-51 ENDTRANSACTION procedure call C-52 Enscribe applications, converted C-77 Enscribe files clearing data from P-36 converting to an SQL table C
E Index 8204 C-138 9179 A-52 9182 A-52 A path or network error occurs (200 255) C-78 checking with SQLCA I-4 Disk file is Safeguard protected (199) A-26 displaying S-40 File system D-20 File/Record locked (73) C-79 Lock limit has been reached (35) C-79 No more opens are permitted on the volume (61) C-78 NonStop SQL/MP D-20 Operation timed out (40) C-79 sort error 30 P-9 SQL/MP D-20 testing for with WHENEVER W-1 The file is bad (59) C-78 The volume is not available (66) C-78 -3036 M-25 -6021 C-75 -8300 C-7
F Index date-time, evaluation E-26 description of E-22 INTERVAL E-23 numeric E-23 with date-time items E-26 EXTEND function E-30 EXTENT file attribute E-31 EXTENTS ALLOCATED display, FILEINFO command F-20 EXTENTS display, FILEINFO command F-22 EXTENTS option, FILEINFO command F-11 Extents, MAXEXTENTS file attribute M-2 F FastSort Z-3 FC command description of F-1 prompt S-61 FETCH statement and cursors F-4 and SQLDA F-4 description of F-3 INTO clause F-3 lock release summary L-46 USING DESCRIPTOR clause
F Index DETAIL display F-16 DETAIL display for OSS files F-21 DETAIL display for views F-21 DETAIL option F-11, F-12 EXTENTS display F-20, F-22 EXTENTS option F-11 STATISTICS display F-22 STATISTICS option F-11 VERSION option F-17 FILEINFO command, DETAIL option for objects F-14 FILENAME in BASETABS table B-1 in FILES table F-28 in INDEXES table I-10 in PARTNS table P-20 FILENAMES command F-26 Files attributes of F-27 available space F-20 CNVSRC C-93 directing output to O-7 displaying contents of C-109 di
G Index FORCE, in PROGRAMS table P-30 FOREIGN KEY table constraint S-68 Form feeds for reports P-3 Form name for reports O-9 Format dates and times C-163 print item A-54 Formats Enscribe files Q-4 Formatting commands See Report writer Formatting Julian timestamps A-55 FORTRAN S-69 FORTRAN data types, converting to SQL C-100 FREE RESOURCES statement and COMMIT WORK statement C-52 and ROLLBACK WORK statement R-24 description of F-30 lock release summary L-46 FROM clause limit on tables L-7 SELECT statement
H Index GROUPID in PROGRAMS table P-30 in TABLES table T-2 Groups and GROUP BY clause S-22 and SECURE file attribute S-11 and SELECT statement S-27 Guardian names G-7 Guardian processes E-2 Guardian user IDs S-12 H HASH JOIN option, CONTROL QUERY directive C-70 Hash joins restrictions on C-75 with CONTROL QUERY directive C-70 HAVING clause and UNION operator S-27 SELECT statement S-21 HEADING clause H-1 ALTER VIEW statement A-45 CREATE TABLE statement C-146 CREATE VIEW statement C-157 Heading for column,
I Index altering attributes A-15 alternate access path E-17 available space F-20 block length F-17 catalog description of I-10 column limit L-6 configuration file for parallel loading P-5 CREATE INDEX statement C-133 date-caused program recompilation F-20 dependencies on base table C-139 displaying physical characteristics F-9 DROP statement D-62 dropping P-31 expiration date, setting N-4 file statistics F-22 for catalog tables C-7 keytags I-9 levels and ICOMPRESS I-1 limit per table L-8 loading data into
J Index Inserting null values I-17 Inserting rows I-14 Inserts, sequential C-78, C-82 INTEGER data syntax D-5 Integrity constraint, creating C-131 INTERACTIVE ACCESS option, CONTROL QUERY directive C-71 Interactive interface See SQLCI INTERVAL data syntax D-7 data type I-19 literals I-22 Interval expressions E-23 INTERVAL values in host variables H-6 LOAD command L-37 INTO clause FETCH statement F-3 INSERT statement I-15 SELECT statement S-19 Invalid object V-3 Invalid plan P-22 Invalid program P-22, P-28
L Index clustering C-26 column length F-18 description of K-1 displaying F-17 first, specifying value of A-18, A-34 for indexes I-9 INDEX I-9 maximum values L-11 nonunique primary C-26 physical primary P-27 primary P-27, S-90 specifier description of C-136 displaying F-18 system-defined primary S-90 user-defined primary U-17 KEYS catalog table K-1 KEYSEQNUMBER, in KEYS table K-1 KEYTAG clause, CREATE INDEX statement C-136 Keytags I-9 KEYTAG, in INDEXES table I-10 Key-sequenced block size B-5 Key-sequenced
L Index PURGE command P-33 PURGEDATA command P-37 SECURE command S-9 LIST_COUNT option RESET SESSION command R-21 SET SESSION command S-41 Literals DATE D-10 DATETIME D-10 date-time D-9 description of L-17 INTERVAL I-22 numeric N-13 string S-78 TIME D-10 TIMESTAMP D-10 LOAD command and AUDIT attribute L-17, L-33 authorization requirements L-31 compared to APPEND A-50 compared to COPY L-32 conversion and loading errors L-37 description of L-17 examples of L-41 field conversion L-36 field formats L-35 move
M Index escalation considerations D-23 exclusive mode on SELECT S-21 granularity L-46 holder L-47 in EXPLAIN report E-18 limits L-8 LOCK TABLE statement L-41 mechanism description L-44 mode L-47 release summary L-46 return control C-77 share mode on SELECT S-21 LOG command CLEAR option L-49 COMMAND option L-49 description of L-49 LOG file, displaying name of E-3 LOGFILE Z-11 Logging to a file L-49 Logical lines, in reports D-47 Logical names D-26 Logical operators S-5 Logical table C-156 LOGICAL_FOLDING l
N Index partitions A-32 tables A-32 Multibyte character sets M-24, S-25 Multiple line heading N-4 Multi-value predicates C-56 N NAME clause, DETAIL command D-46 NAME command N-1 NAME option N-2 Name resolution N-2 Name substitution with DEFINEs D-26 Names alias A-6 catalog C-6 column C-40 correlation C-124 cursor C-164 DEFINE D-26 description of N-3 detail alias D-43 Guardian G-7 OSS O-6 partition P-19 pathnames O-6 subvolume C-6 syntax and usage rules for N-3 views V-9 ZYQ O-6 Names buffer DESCRIBE stat
O Index NOT operator S-5 NOTCPUS, parameter for FastSort Z-4 NULL predicate N-5 NULL STRUCTURE clause, INVOKE directive I-27 Null values and aggregate functions A-71 and AVG A-71 and COUNT C-126 and index sorting C-140 and INSERT I-17 and MAX M-1 and MIN M-4 and SUM S-89 and unique index C-134 copying from Enscribe C-113 defining columns N-7 description of N-6 expression evaluation N-9, N-10 inserting I-14 loading from Enscribe L-22 specifying for UPDATE U-3 use in partitioned indexes P-17 NULLALLOWED in
O Index Operable plan P-21 Operation cost, in EXPLAIN report E-18 Operations on catalog tables C-8 Operators AND S-5 arithmetic and unary E-22 Boolean or logical S-5 comparison C-55 NOT S-5 OR S-5 Optimal plan P-21 Options CENTER_REPORT C-10 COMMIT C-46 DATE FORMAT D-8 DECIMAL POINT D-20 HEADINGS H-1 LEFT_MARGIN L-1 LINE_SPACING L-14 LOGICAL_FOLDING L-50 NAME N-2 NEWLINE_CHAR N-3 NULL_DISPLAY N-10 OVERFLOW_CHAR O-9 PAGE_COUNT P-1 PAGE_LENGTH P-2 REPORT R-3 resetting R-22 RIGHT MARGIN R-22 ROLLBACK C-49 RO
P Index ALTER TABLE statement A-30 ALTER VIEW statement A-45 description of O-10 SECURE command S-9 Ownership changing methods for S-7 program A-25 table A-30 displaying D-53, F-13 of a file, defined S-14 Owner, generalized S-9, S-14 P Packed record length F-17 PAD option COPY command C-115 LOAD command L-22 Page advancing to next D-46 conditional break D-45 length P-2 maximum in report P-1 size for PERUSE O-9 text at bottom of P-1 PAGE clause DETAIL command D-46 report writer option R-12 PAGE FOOTING co
P Index unnamed P-13 using P-13 PART option, CONVERT command C-93 Partial query results C-82 PARTITION ARRAY attribute, displaying F-20 PARTITION ARRAY clause ALTER TABLE statement A-31 CREATE TABLE statement C-148 PARTITION clause CREATE INDEX statement C-137 CREATE TABLE statement C-148 description of P-16 PARTITIONARRAY, in FILES table F-29 PARTITIONED, in FILES table F-28 Partitioning tables L-10 PARTITIONNAME, in PARTNS table P-20 Partitions adding index A-20 table A-32 allocating disk space for A-7
P Index increasing L-10 memory requirements L-9 PFV (program format version) V-8 Physical characteristics of object F-9 Physical primary keys description of P-27 for indexes I-9 PHYSVOL clause CREATE INDEX statement C-135 CREATE TABLE statement C-147 PICTURE 9 data syntax D-6 PICTURE X data syntax D-4 PICTURETEXT in COLUMNS table C-42 Plans altered P-22 description of P-21 inoperable P-21 invalid P-22 operable P-21 optimal P-21 valid P-21 PL/1 S-69 POSITION function P-22 Precision of expression results E-
P Index Print lists P-28 Printer, device width R-22 PRI, parameter for FastSort Z-4 Process alter owner A-25, A-27 device width used R-22 Process Access ID (PAID) S-13 Process File Segment (PFS) and DEFINEs D-26 limits L-9 Processes E-2 Processing concepts for TMF transactions T-5 control locking C-72 control parallel execution C-68, C-69 control query processing C-69 control table opens C-72 Processing rules audited objects T-7 DDL and DML statements T-7 NO AUDIT attribute T-7 SQLCI T-7 PROCESSNAME, in T
Q Index PROGRAMS catalog table and program invalidation P-28, P-29 description of P-30 PROGRAM, parameter for FastSort Z-4 Prompts S-61 Protection views AUDIT file attribute A-69 description of P-31, V-9 limit per table L-12 similarity S-56 PROTECTION, in VIEWS table V-9 PS Text Edit T-3 PUP utility U-18 PUP (Peripheral Utility Program) C-128, C-129 Purge date and time, changing A-30 objects D-60 Purge access S-14 PURGE command and TMF P-33, P-34 description of P-31 LISTALL option P-33 SHADOWSONLY option
R Index displaying F-17 RECLENGTH file attribute R-1 RECORDSIZE, in FILES table F-29 Records, nonconvertible C-112, L-20 RECOUT option, COPY command C-115 REDEFINE option CONVERT command C-94 LOAD command L-30 Redefinition time F-20 REDEFTIME, in TABLES table T-2 REELS option, LOAD command L-25 REFERENCES column constraint S-68 column privileges S-68 REGISTERONLY, in PROGRAMS table P-31 RELATIONSHIPTYPE, in USAGES U-16 Relative files F-9 Relative tables and SYSKEYS S-90 file attribute summary F-8 organiza
R Index COMPUTE_TIMESTAMP function C-57 CONCAT clause C-58 CURRENT_TIMESTAMP function C-163 IF/THEN/ELSE clause I-1 Report layout options CENTER_REPORT C-10, R-11 DATE_FORMAT D-8, R-11 DECIMAL_POINT D-20, R-11 HEADINGS H-1, R-11 LEFT_MARGIN L-1, R-11 LINE_SPACING L-14, R-11 LOGICAL_FOLDING L-50, R-11 NEWLINE_CHAR N-3, R-11 NULL_DISPLAY N-10, R-11 OVERFLOW_CHAR O-9, R-11 PAGE_COUNT P-1, R-11 PAGE_LENGTH P-2, R-11 RIGHT_MARGIN R-12, R-22 ROWCOUNT R-12, R-25 SPACE R-12, S-58 SUBTOTAL_LABEL R-12, S-87 TIME_FO
S Index REVOKE statement, ANSI/ISO SQL S-68 REWINDIN option, LOAD command L-25 REWINDOUT, COPY command C-116 Right justification A-57, A-59, A-60 RIGHT_MARGIN layout option R-12, R-22 ROLLBACK option C-49 ROLLBACK WORK statement R-23 Row count display, suppressing R-25 ROWCOUNT layout option R-12, R-25 ROWCOUNT, in BASETABS table B-1 Rows deleting D-37 displaying definition of I-24 displaying or printing L-15 displaying subset of W-3 fetching F-3 inserting I-14 length limit L-11 pausing while displaying S
S Index CREATE CATALOG statement C-128 CREATE TABLE statement C-149 CREATE VIEW statement C-158 SECURE command ALLOWERRORS option S-8 and REVOKE S-68 CLEARONPURGE option S-8 description of S-7 LISTALL option S-9 OWNER option S-9 PROGID option S-9 TMF and S-10 SECURE file attribute ALTER CATALOG statement A-8 ALTER COLLATION statement A-9 ALTER INDEX statement A-15 ALTER PROGRAM statement A-25 ALTER TABLE statement A-30 ALTER VIEW statement A-45 description of S-11 Security altering S-7 catalogs and A-8 ch
S Index value comparisons C-55 values to insert I-14 values within a range B-3 Selecting from catalog tables S-31 Selecting rows for DELETE operation D-38 for INSERT operation I-14 for UPDATE operation U-3 Selecting values from a list B-3 Select-in-progress prompt S-61 Select-list dependencies on GROUP BY S-25 for distinct rows S-19 Self join S-31 SEQNUMBER in COMMENTS table C-46 in CONSTRNT table C-65 in CPRLSRCE table C-126 Sequential access block size B-5 Sequential block buffering See Virtual sequenti
S Index PURGE command P-33 SHARE clause, LOCK TABLE statement L-41 SHARE option, LOAD command L-25 SHARED ACCESS on DDL statements W-4 Shared lock description of L-47 on SELECT statement S-21 SHARE clause L-41 Shift JIS character set C-17 Shorthand views and UNION operator C-160, S-26 AUDIT file attribute A-69 based on joins C-160 description of S-48, V-9 SHOW CONTROL command S-49 SHOW DEFINE command S-49 SHOW DEFMODE command S-50 SHOW LAYOUT command S-51 SHOW PARAM command S-51 SHOW PREPARED command S-52
S Index device width R-22 use of O-8 SQL and host communication H-5 compilation, prepare statements P-24 compiler event messages Z-10 identifiers S-60 message file Z-16 programmatic E-1 statement execution time D-49 statements S-72 static S-75 tables converting an Enscribe file to C-89 loading data into L-17 rules for copying C-120 SQL compiler event messages Z-10 SQL directive S-60 SQL SENSITIVE flag F-35 SQL VALID flag F-35 SQLCA description of I-4 effect of DECLARE CURSOR D-22 SQLCI commands S-64 descr
S Index CONTINUE C-65 cost of executing D-49 CREATE ASSERTION (ANSI/ISO SQL) S-70 CREATE CATALOG C-127 CREATE COLLATION C-130 CREATE CONSTRAINT C-131 CREATE INDEX C-133 CREATE TABLE C-143 CREATE VIEW C-156 DCL D-16 DDL D-19 DECLARE CURSOR D-22 DELETE D-37 DESCRIBE D-41 DESCRIBE INPUT D-40 description of S-72 DROP D-60 DROP ASSERTION S-70 DSL D-65 EXECUTE E-7 EXECUTE IMMEDIATE E-11 FETCH F-3 FREE RESOURCES F-30 GET CATALOG OF SYSTEM G-1 GET VERSION G-2 GET VERSION OF PROGRAM G-4 HELP TEXT H-3 INSERT I-14 L
S Index setting S-46 UNDERLINE_CHAR U-1 STYLE option, SAVE command S-3 Subqueries comparing expression to results Q-6 correlated S-82 description of S-81 in EXISTS predicate E-12 nested S-82 nesting limit of L-11 SUBSORT DEFINEs D-31 Subsorts for loading indexes C-136 SUBSORTS, parameter for FastSort Z-4 Substituting values with parameters P-12 SUBSTRING function S-83 SUBSYSTEMNAME in CATALOGS table C-9 in VERSIONS table V-8 SUBTOTAL command and BREAK ON command S-85 description of S-85 SUBTOTAL_LABEL lay
T Index =_SQL_EXE_USE_SWAPVOL Z-15 =_SQL_MSG_node Z-16 =_SQL_RECGEN_node Z-18 =_SQL_TM_node_vol Z-19 =_SQL_UTL_node Z-13 SYSTEM, default S-91 System, default E-3 System-defined primary key description of S-90 maximum values L-11 returned value on INSERT I-17 S> prompt S-61 T TAB clause DETAIL command D-46 report writer option R-12 use with report window W-3 Table BASETABS catalog table B-1 CATALOGS catalog table C-9 CPRLSRCE catalog table C-126 CPRULES catalog table C-127 FILES catalog table F-28 INDEXES
T Index organizations F-8 ownership change A-30, S-7 partitioned P-19 partitions adding A-32 dropping A-31, A-32 physical file attributes C-102 purging D-61 renaming A-30 row definition I-24 security change S-7 selecting from S-17 selectivity E-20 setting organization for C-148 similarity rules for S-56 specifying file attributes of C-149 temporary T-3, Z-15 transaction limit L-12 unlocking nonaudited U-1 unpartitioned P-19 updating rows of U-3 updating statistics for U-7 using ALTER TABLE statement A-27
U Index aborting R-24 and COMMIT WORK C-51 and SECURE S-10 audit fix-up phase W-6 AUTOWORK T-7 BEGIN WORK statement B-2 beginning B-2 commit phase W-7 committing C-51 concurrency summary of A-3 CONTINUE statement not allowed C-66 control statements T-5 copying data from C-119 description of T-5 displaying status E-3 ending C-51 file recovery protection A-41 IDs T-11 IDs in catalogs T-11 initialization and load phase W-5 limit per table L-12 processing concepts T-5 utility AUTOWORK T-7 LOAD L-33 PURGE P-31
U Index Unpartitioned table P-19 Unstructured files F-9 UNSTRUCTURED option COPY command C-113 LOAD command L-22 Unsupported data types L-36 UPDATE operations buffered C-77 file organization dependent F-8 UPDATE statement S-47, U-3 and scale S-47 column privileges S-68 lock release summary L-46 table privileges S-68 UPDATE STATISTICS statement concurrent DML operations C-61 description of U-7 Updateable primary keys S-69 UPGRADE CATALOG command U-11 UPGRADE SYSTEM CATALOG command U-13 UPSHIFT clause, and
V Index PURGE P-31 PURGEDATA P-36 SECURE S-7 VERIFY V-2 Utility operations and concurrency C-63 Utility operations, and concurrency C-63 V Valid plan P-21 Validation, program file P-28 VALIDDATA in BASETABS table B-1 in INDEXES table I-10 VALIDDEF in BASETABS table B-1 in INDEXES table I-10 in VIEWS table V-9 VALID, in PROGRAMS table P-30 Value substitution P-12 Values compatible I-16 matching L-2 search conditions for S-5 searching for existing E-12 selecting a range of B-3 selecting from a list I-1 VAR
W Index CREATE VIEW statement C-156 definition text limit L-12 description of V-9 displaying physical characteristics F-9 DROP statement D-62 dropping P-33, P-34 duplicating D-66, D-73 grouped S-19, S-24 inserting with check option I-17 limit per table L-12 locking L-41 names G-7, V-9 ownership changes A-45, S-7 protection L-12, P-31 purging D-61 renaming A-45 row definition I-24 security changes A-45, S-7 selecting data from S-19, S-24 shorthand C-160, S-26, S-48 similarity S-56 specifying security attri
Z Index WITH SHARED ACCESS clause, CREATE INDEX statement C-137 WITH SHARED ACCESS option W-4 WITHCHECKOPTION, in VIEWS table V-10 Working attribute set D-32 WRAP option, SET SESSION command S-42 Wrapping display line S-42 Write access S-11, S-14 =_SQL_EXE_USE_SWAPVOL DEFINE Z-15 =_SQL_MSG_node DEFINE Z-16 =_SQL_RECGEN _node DEFINE Z-18 =_SQL_TM_node_vol DEFINE Z-19 =_SQL_UTL_node define Z-13 >> prompt S-61 ? unnamed parameter P-13 Z ZYQ names O-6 Special Characters ! (exclamation point) command Z-1 +>
Special Characters Index NonStop SQL/MP Reference Manual—142115 Index -54
