NonStop SQL/MP Reference Manual

ManualsBrandsHP ManualsServerHP NonStop G-Series

Table Of Contents

What’s New in This Manual
About This Manual
A
- Access Options
  - Access Options on DML Statements
  - Access Options on DDL Statements
- ADD DEFINE Command
  - Considerations—ADD DEFINE
  - Examples—ADD DEFINE
- AGGREGATE Functions
- Alias
- ALLOCATE File Attribute
  - Considerations—ALLOCATE
- ALTER CATALOG Statement
  - Considerations—ALTER CATALOG
  - Examples—ALTER CATALOG
- ALTER COLLATION Statement
  - Considerations—ALTER COLLATION
  - Examples—ALTER COLLATION
- ALTER DEFINE Command
  - Considerations—ALTER DEFINE
  - Examples—ALTER DEFINE
- ALTER INDEX Statement
  - Considerations—ALTER INDEX
  - Examples—ALTER INDEX
- ALTER PROGRAM Statement
  - Considerations—ALTER PROGRAM
  - Examples—ALTER PROGRAM
- ALTER TABLE Statement
  - Considerations—ALTER TABLE
  - Examples—ALTER TABLE
- ALTER VIEW Statement
  - Considerations—ALTER VIEW
  - Examples—ALTER VIEW
- APPEND Command
  - Considerations—APPEND
- APPENDCANCEL Command
  - Considerations—APPENDCANCEL
- APPENDRESTART Command
  - Considerations—APPENDRESTART
- AS Clause
  - Display Descriptors for Character Items
  - Display Descriptors for Numeric Items
  - Scale-Sign Descriptors
  - Modifiers
  - Decorations
  - Examples—AS
- AS DATE/TIME Clause
  - Examples—AS DATE/TIME
- ASCII Character Set
- AUDIT File Attribute
  - Considerations—AUDIT
- AUDITCOMPRESS File Attribute
  - Considerations—AUDITCOMPRESS
- Audited Tables
- AVG Function
  - Considerations—AVG
  - Examples—AVG
B
- BACKUP Utility
- BASETABS Table
- BEGIN DECLARE SECTION Directive
  - Examples—BEGIN DECLARE SECTION
- BEGIN WORK Statement
  - Examples—BEGIN WORK
- BETWEEN Predicate
  - Considerations—BETWEEN
  - Examples—BETWEEN
- BLOCKSIZE File Attribute
  - Considerations—BLOCKSIZE
- BREAK FOOTING Command
  - Considerations—BREAK FOOTING
  - Examples—BREAK FOOTING
- BREAK ON Command
  - Considerations—BREAK ON
  - Examples—BREAK ON
- BREAK TITLE Command
  - Considerations—BREAK TITLE
  - Examples—BREAK TITLE
- BUFFERED File Attribute
  - Considerations—BUFFERED
C
- CANCEL Command
  - Consideration—CANCEL
  - Examples—CANCEL
- CASE Expression
  - Considerations—CASE Expression
  - Examples—CASE Expression
- CAST Function
  - Considerations—CAST
  - Examples—CAST
- CATALOG Command
  - Considerations—CATALOG
  - Examples—CATALOG
- Catalogs
  - Operations on Catalog Tables
- CATALOGS Table
- CENTER_REPORT Option
  - Considerations—CENTER_REPORT
  - Examples—CENTER REPORT
- Character Data Types
- Character Expressions
  - Considerations—Character Expressions
  - Examples—Character Expressions
- Character Sets
  - ISO 8859 Character Sets
  - Kanji Character Set
  - KS C5601 Character Set
- CHAR_LENGTH Function
  - Considerations—CHAR_LENGTH Function
  - Examples—CHAR_LENGTH Function
- CLEANUP Command
  - Considerations—CLEANUP
  - CLEANUP Exception Cases
  - Examples—CLEANUP
- CLEARONPURGE File Attribute
  - Considerations—CLEARONPURGE
- CLOSE Statement
  - Considerations—CLOSE
  - Examples—CLOSE
- Clustering Keys
  - Examples—CLUSTERING KEYS
- COLLATE Clause
- Collation Definitions
  - Comment and Escape Characters in Collation Definitions
  - The LC_COLLATE Section of a Collation Definition
  - The LC_CTYPE Section of a Collation Definition
  - The LC_TDMCODESET Section of a Collation Definition
  - Considerations—Collation Definitions
  - Examples—Collation Definitions
- Column Identifier
  - Examples—Column Identifiers
- Columns
- COLUMNS Table
- COMMENT Statement
  - Considerations—COMMENT
  - Examples—COMMENT
- Comments
  - Examples—Comments
- COMMENTS Table
- COMMIT Option
  - Considerations—COMMIT Option
  - Examples—COMMIT Option
- COMMIT WORK Statement
  - Considerations—COMMIT WORK
  - Examples—COMMIT WORK
- Comparison Predicate
  - Considerations—Comparison Predicate
  - Examples—Comparison Predicate
- COMPUTE_TIMESTAMP Function
  - Considerations—COMPUTE_TIMESTAMP
  - Examples—COMPUTE_TIMESTAMP
- CONCAT Clause
  - Considerations—CONCAT
  - Examples—CONCAT
- Concurrency
  - Summary of Concurrent DDL and DML Operations
  - ALTER Operation Effects on Timestamps
  - Limits on Concurrent Utility and DML Operations
  - Effect of VSBB on Concurrency
- Constraints
- CONSTRNT Table
- CONTINUE Statement
  - Considerations—CONTINUE
  - Examples—CONTINUE
- CONTROL EXECUTOR Directive
  - Considerations—CONTROL EXECUTOR
  - Examples—CONTROL EXECUTOR
- CONTROL QUERY Directive
  - Considerations—CONTROL QUERY
  - Examples—CONTROL QUERY
- CONTROL TABLE Directive
  - Considerations—CONTROL TABLE
  - Examples—CONTROL TABLE
- CONVERT Command
  - CONVERT Behavior
  - Enscribe Files and DDL Record Definitions
  - DDL Primary Keys and Alternate Keys
  - DDL Clause Mapping
  - Conversion of DDL Elementary Items
  - DDL Groups
  - Physical File Attributes of Tables and Indexes
  - Partition Attributes of Tables and Indexes
  - Examples—CONVERT
- CONVERTTIMESTAMP Function
  - Examples—CONVERTTIMESTAMP
- COPY Command
  - Considerations—COPY
  - Enscribe Field Formats
  - Field Conversions
  - Examples—COPY
- Correlation Names
- COUNT Function
  - Considerations—COUNT
  - Examples—COUNT
- CPRLSRCE Table
- CPRULES Table
- CREATE CATALOG Statement
  - Considerations—CREATE CATALOG
  - Examples—CREATE CATALOG
- CREATE COLLATION Statement
  - Considerations—CREATE COLLATION
  - Examples—CREATE COLLATION
- CREATE CONSTRAINT Statement
  - Considerations—CREATE CONSTRAINT
  - Examples—CREATE CONSTRAINT
- CREATE INDEX Statement
  - Considerations—CREATE INDEX
  - Examples—CREATE INDEX
- CREATE SYSTEM CATALOG Command
  - Considerations—CREATE SYSTEM CATALOG
  - Examples—CREATE SYSTEM CATALOG
- CREATE TABLE Statement
  - Considerations—CREATE TABLE
  - Examples—CREATE TABLE
- CREATE VIEW Statement
  - Considerations—CREATE VIEW
  - Examples—CREATE VIEW
- CURRENT Function
  - Examples—CURRENT
- CURRENT_TIMESTAMP Function
  - Considerations—CURRENT_TIMESTAMP
  - Examples—CURRENT_TIMESTAMP
- Cursors
  - Cursor Position
  - Cursor Stability
- C89
D
- Data Dictionary
- Data Types
- DATE Data Type
  - Examples—DATE Data Type
- DATE_FORMAT Option
  - Examples—DATE_FORMAT
- DATE-TIME Data Types
- DATE-TIME Functions
- DATE-TIME Literals
  - Examples—Date-Time Literals
- DATEFORMAT Function
  - Examples—DATEFORMAT
- DATETIME Data Type
  - Considerations—DATETIME DATA TYPE
  - Examples—DATETIME
- DAYOFWEEK Function
  - Examples—DAYOFWEEK
- DCL (Data Control Language) Statements
- DCOMPRESS File Attribute
  - Considerations—DCOMPRESS
- DDL (Data Definition Language) Statements
  - DDL Statements
- Deadlocks
- DECIMAL_POINT Option
  - Considerations—DECIMAL_POINT
  - Examples—DECIMAL_POINT
- DECLARE CURSOR Statement
  - Considerations—DECLARE CURSOR
  - Examples—DECLARE CURSOR
- DEFAULT Clause
  - Examples—DEFAULT
- DEFINEs
  - Using DEFINEs
  - Using DEFINEs From SQLCI
  - Using DEFINEs With SQL Programs
  - DEFINE Attributes
  - DEFINEs of Class CATALOG
  - DEFINEs of Class DEFAULT
  - DEFINEs of Class MAP
  - Summary of DEFINE Attributes
  - Examples—DEFINEs Used With SQL Programs
- DELETE DEFINE Command
  - Examples—DELETE DEFINE
- DELETE Statement
  - Considerations—DELETE
  - Examples—DELETE
- DESCRIBE INPUT Statement
  - Examples—DESCRIBE INPUT
- DESCRIBE Statement
  - Examples—DESCRIBE
- Detail Alias
- DETAIL Command
  - Considerations—DETAIL
  - Examples—DETAIL
- DISPLAY STATISTICS Command
  - Considerations—DISPLAY STATISTICS
  - Examples—DISPLAY STATISTICS
- DISPLAY USE OF Command
  - Considerations—DISPLAY USE OF
  - Examples—DISPLAY USE OF
- DISTINCT Clause
- DML (Data Manipulation Language) Statements
  - Summary of DML Statements
- DOWNGRADE CATALOG Command
  - Considerations—DOWNGRADE CATALOG
  - Examples—DOWNGRADE CATALOG
- DOWNGRADE SYSTEM CATALOG Command
  - Considerations—DOWNGRADE SYSTEM CATALOG
  - Examples—DOWNGRADE SYSTEM CATALOG
- DROP Statement
  - Considerations—DROP
  - Examples—DROP
- DROP SYSTEM CATALOG Command
  - Considerations—DROP SYSTEM CATALOG
  - Examples—DROP SYSTEM CATALOG
- DSL (Data Status Language) Statements
  - Summary of DSL Statements
- DSLACK File Attribute
  - Considerations—DSLACK
- DUP Command
  - Considerations—DUP
  - Examples—DUP
- Dynamic SQL
  - Summary of Dynamic SQL Statements
  - Determining When to Use Dynamic SQL
  - Features of Dynamic SQL
E
- EDIT Command
  - Examples—EDIT
- Embedded SQL
- END DECLARE SECTION Directive
  - Examples—END DECLARE SECTION
- ENV Command
  - Considerations—ENV
  - Examples—ENV
- ERROR Command
  - Examples—ERROR
- Error Messages
- EXECUTE Statement
  - Considerations—EXECUTE
  - Examples—EXECUTE
- EXECUTE IMMEDIATE Statement
  - Considerations—EXECUTE IMMEDIATE
  - Examples—EXECUTE IMMEDIATE
- EXISTS Predicate
  - Examples—EXISTS
- EXIT Command
  - Examples—EXIT
- EXPLAIN Directive
  - Considerations—EXPLAIN
  - Examples—EXPLAIN
- Expressions
- EXTEND Function
  - Considerations—EXTEND
  - Examples—EXTEND
- EXTENT File Attribute
  - Considerations—EXTENT
F
- FC Command
  - Considerations—FC
  - Examples—FC
- FETCH Statement
  - Considerations—FETCH
  - Examples—FETCH
- File Attributes
  - File Attributes of SQL Objects
- File Organizations
- FILEINFO Command
- FILENAMES Command
  - Examples—FILENAMES
- FILES Command
  - Examples—FILES
- FILES Table
- Filesets
  - Examples—Filesets
- FREE RESOURCES Statement
  - Considerations—FREE RESOURCES
  - Examples—FREE RESOURCES
- Functions
- FUP Command
G
- Generalized Owner
- GET CATALOG OF SYSTEM Statement
  - Considerations—GET CATALOG OF SYSTEM
  - Examples—GET CATALOG OF SYSTEM
- GET VERSION Statement
  - Considerations—GET VERSION
  - Examples—GET VERSION
- GET VERSION OF PROGRAM Statement
  - Considerations—GET VERSION OF PROGRAM
  - Examples—GET VERSION OF PROGRAM
- GOAWAY Command
  - Considerations—GOAWAY
  - Examples—GOAWAY
- Group Manager
- Guardian Names
  - Considerations—Guardian Names
  - Examples—Guardian Names
H
- HEADING Clause
  - Considerations—HEADING
  - Examples—HEADING
- HEADINGS Option
  - Examples—HEADINGS
- HELP Command
  - Considerations—HELP
  - Examples—HELP
- HELP TEXT Statement
  - Considerations—HELP TEXT
  - Examples—HELP TEXT
- HISTORY Command
  - Examples—HISTORY
- Host Identifiers
- Host Programs
- Host Variables
I
- ICOMPRESS File Attribute
  - Considerations—ICOMPRESS
- IF/THEN/ELSE Clause
  - Considerations—IF/THEN/ELSE
  - Examples—IF/THEN/ELSE
- IN Predicate
  - Considerations—IN
  - Examples—IN
- INCLUDE SQLCA Directive
  - Considerations—INCLUDE SQLCA
  - Examples—INCLUDE SQLCA
- INCLUDE SQLDA Directive
  - Considerations—INCLUDE SQLDA
  - Examples—INCLUDE SQLDA
- INCLUDE SQLSA Directive
  - Considerations—INCLUDE SQLSA
  - Examples—INCLUDE SQLSA
- INCLUDE STRUCTURES Directive
  - Considerations—INCLUDE STRUCTURES
  - Examples—INCLUDE STRUCTURES
- Index Keys
- INDEXES Table
- Indicator Variables and Indicator Parameters
- INFO DEFINE Command
  - Considerations—INFO DEFINE
  - Examples—INFO DEFINE
- INITIALIZE SQL Command
  - Considerations—INITIALIZE SQL
  - Examples—INITIALIZE SQL
- INSERT Statement
  - Considerations—INSERT
  - Examples—INSERT
- INTERVAL Data Type
  - Considerations—INTERVAL Data Type
  - Examples—INTERVAL Data Type
- INTERVAL Literals
  - Examples—Interval Literals
- INVOKE Directive and Command
  - Considerations—INVOKE
  - Examples—INVOKE
- ISLACK File Attribute
  - Considerations—ISLACK
J
- Joins
  - Examples - Joins
- JULIANTIMESTAMP Function
  - Examples—JULIANTIMESTAMP
K
- Keys
- KEYS Table
L
- LEFT_MARGIN Option
  - Examples—LEFT_MARGIN
- LIKE Predicate
  - Considerations—LIKE
  - Examples—LIKE
- Limits
- LINE_NUMBER Function
  - Considerations—LINE_NUMBER
  - Examples—LINE_NUMBER
- LINE_SPACING Option
  - Examples—LINE_SPACING
- LIST Command
  - Considerations—LIST
  - Examples—LIST
- Literals
- LOAD Command
  - Considerations—LOAD
  - Examples—LOAD
- LOCK TABLE Statement
  - Considerations—LOCK TABLE
  - Examples—LOCK TABLE
- Locking
- LOCKLENGTH File Attribute
  - Considerations—LOCKLENGTH
  - Examples—LOCKLENGTH
- LOG Command
  - Examples—LOG
- LOGICAL_FOLDING Option
  - Considerations—LOGICAL_FOLDING
  - Examples—LOGICAL_FOLDING
M
- MAX Function
  - Considerations—MAX
  - Examples—MAX
- MAXEXTENTS File Attribute
  - Considerations—MAXEXTENTS
- Message File
- MIN Function
  - Considerations—MIN
  - Examples—MIN
- MODIFY CATALOG Command
  - Considerations—MODIFY CATALOG
  - Examples—MODIFY CATALOG
- MODIFY LABEL Command
  - Considerations—MODIFY LABEL
  - Examples—MODIFY LABEL
- MODIFY REGISTER Command
  - Considerations—MODIFY REGISTER
  - Examples—MODIFY REGISTER
- Multibyte Character Sets
  - System Default National Character Set
N
- NAME Command
  - Considerations—NAME Command
  - Examples—NAME Command
- NAME Option
  - Considerations—NAME Option
  - Examples—NAME Option
- Name Resolution
- Names
- NEWLINE_CHAR Option
  - Considerations—NEWLINE_CHAR
  - Examples—NEWLINE_CHAR
- Nonaudited Tables
- NOPURGEUNTIL File Attribute
  - Defaults
  - Examples—NOPURGEUNTIL
- NULL Predicate
  - Considerations—NULL
  - Examples—NULL
- Null Values
- NULL_DISPLAY Option
  - Examples—NULL_DISPLAY
- Numeric Data Types
- Numeric Literals
  - Examples—Numeric Literals
O
- OBEY COMMAND
  - Considerations—OBEY
  - Examples—OBEY
- OCTET_LENGTH Function
  - Considerations—OCTET LENGTH Function
  - Examples—OCTET LENGTH Function
- OPEN Statement
  - Considerations—OPEN
  - Examples—OPEN
- OSS NAMES
- OUT COMMAND
  - Considerations—OUT
  - Examples—OUT
- OUT_REPORT COMMAND
  - Considerations—OUT_REPORT
  - Examples—OUT_REPORT
- OVERFLOW_ CHAR OPTION
  - Considerations—OVERFLOW_CHAR
  - Examples—OVERFLOW_CHAR
- OWNER FILE ATTRIBUTE
P
- PAGE_COUNT Option
  - Considerations—PAGE_COUNT
  - Examples—PAGE_COUNT
- PAGE FOOTING Command
  - Considerations—PAGE FOOTING
  - Examples—PAGE FOOTING
- PAGE_LENGTH Option
  - Considerations—PAGE_LENGTH
  - Examples—PAGE_LENGTH
- PAGE_NUMBER Function
  - Considerations—PAGE_NUMBER
  - Examples—PAGE_NUMBER
- PAGE TITLE Command
  - Considerations—PAGE TITLE
  - Examples—PAGE TITLE
- Parallel Index Loading
  - Default Configuration for Parallel Index Loading
  - Specifying Configuration for Parallel Index Loading
  - Considerations—Parallel Index Loading
  - Sample Configuration File
- Parameters
  - Considerations—Parameters
  - Examples—Parameters
- PARTITION Clause
  - Considerations—PARTITION
  - Examples—PARTITION
- Partitions
- PARTNS Table
- PERUSE Command
  - Examples—PERUSE
- Plans
- POSITION Function
  - Considerations—POSITION Function
  - Examples—POSITION Function
- Predicates
- PREPARE Statement
  - Considerations—PREPARE
  - Examples—PREPARE
- Primary Keys
- Print Item
- PROGID File Attribute
- Program Invalidation
- PROGRAMS Table
- Protection View
- PURGE Command
  - Considerations—PURGE
  - Examples—PURGE
- PURGEDATA Command
  - Considerations—PURGEDATA
  - Examples—PURGEDATA
Q
- Qualified Fileset List
  - Examples—Qualified Fileset List
- Quantified Predicate
  - Considerations—Quantified Predicate
  - Examples—Quantified Predicate
R
- RECLENGTH File Attribute
  - Considerations—RECLENGTH
- RELEASE Statement
- REPORT FOOTING Command
  - Considerations—REPORT FOOTING
  - Examples—REPORT FOOTING
- REPORT Option
  - Considerations—REPORT Option
  - Examples—REPORT Option
- REPORT TITLE Command
  - Considerations—REPORT TITLE
  - Examples—REPORT TITLE
- Report Writer
- Reserved Words
- RESET DEFINE Command
  - Considerations—RESET DEFINE
  - Examples—RESET DEFINE
- RESET LAYOUT Command
  - Examples—RESET LAYOUT
- RESET PARAM Command
  - Considerations—RESET PARAM
  - Examples—RESET PARAM
- RESET PREPARED Command
  - Examples—RESET PREPARED
- RESET REPORT Command
  - Considerations—RESET REPORT
  - Examples—RESET REPORT
- RESET SESSION Command
  - Examples—RESET SESSION
- RESET STYLE Command
  - Examples—RESET STYLE
- RESETBROKEN File Attribute
- RIGHT_MARGIN Option
  - Considerations—RIGHT_MARGIN
  - Examples—RIGHT_MARGIN
- ROLLBACK WORK Statement
  - Considerations—ROLLBACK WORK
  - Examples—ROLLBACK WORK
- ROWCOUNT Option
  - Examples—ROWCOUNT
S
- Sample Database
- SAVE Command
  - Examples—SAVE
- Search Conditions
  - Considerations—Search Conditions
  - Examples—Search Conditions
- SECURE Command
  - Considerations—SECURE Command
  - Examples—SECURE Command
- SECURE File Attribute
  - Considerations—SECURE File Attribute
  - Examples—SECURE File Attribute
- Security
  - User IDs
  - Group Manager and Super ID
  - Process Access IDs
  - File Ownership
  - Security Strings
  - Authorization Requirements for SQL Statements
  - Authorization Requirements for SQL Statements
- SELECT Statement
  - Considerations—SELECT
  - Considerations for UNION
  - Characteristics of UNION Columns
  - ORDER BY clause and UNION operator
  - GROUP BY Clause, HAVING Clause, and the UNION Operator
  - UNION ALL and Associativity
  - Examples—SELECT
- SERIALWRITES File Attribute
  - Considerations—SERIALWRITES
- SET DEFINE Command
  - Considerations—SET DEFINE
  - Examples—SET DEFINE
- SET DEFMODE Command
  - Examples—SET DEFMODE
- SET LAYOUT Command
  - Examples—SET LAYOUT
- SET PARAM Command
  - Considerations—SET PARAM
  - Examples—SET PARAM
- SET SESSION Command
  - Considerations—SET SESSION
  - Examples—SET SESSION
- SET STYLE Command
  - Considerations—SET STYLE
  - Examples—SET STYLE
- SETSCALE Function
  - Considerations—SETSCALE
  - Examples—SETSCALE
- Shorthand View
- SHOW CONTROL Command
  - Examples—SHOW CONTROL
- SHOW DEFINE Command
  - Considerations—SHOW DEFINE
  - Examples—SHOW DEFINE
- SHOW DEFMODE Command
  - Examples—SHOW DEFMODE
- SHOW LAYOUT Command
  - Examples—SHOW LAYOUT
- SHOW PARAM Command
  - Examples—SHOW PARAM
- SHOW PREPARED Command
  - Examples—SHOW PREPARED
- SHOW REPORT Command
  - Examples—SHOW REPORT
- SHOW SESSION Command
  - Examples—SHOW SESSION
- SHOW STYLE Command
  - Examples—SHOW STYLE
- Similarity Checks
  - General Rules for Similarity
  - Similarity Between Protection Views
  - Similarity Between Tables
  - Similarity Between Collations
- SLACK File Attribute
  - Purpose of SLACK
- SPACE Option
  - Considerations—SPACE
  - Examples—SPACE
- SQL Directive
- SQL Identifiers
- SQLCI
  - An SQLCI Session
  - The SQLCI Command
  - Considerations—SQLCI
  - Examples—SQLCI
- SQLCI Commands
  - Summary of SQLCI Commands
- SQLCODE
- SQLCOMP Command
- Standards Conformance
  - Exceptions to Conformance With Entry Level SQL 1992
  - NonStop SQL/MP Features From Intermediate Level SQL 1992
  - NonStop SQL/MP Features From Full Level SQL 1992
  - NonStop SQL/MP Extensions to SQL 1992
- Statements
  - Summary of SQL Statements
- Static SQL
- Statistics
- Storage Management Foundation (SMF)
  - Considerations—ServerWare SMF
- String Functions
- String Literals
  - Considerations—String Literals
  - Examples—String Literals
- Subqueries
  - Considerations—Subqueries
- SUBSTRING Function
  - Considerations—SUBSTRING Function
  - Examples—SUBSTRING Function
- SUBTOTAL Command
  - Considerations—SUBTOTAL
  - Examples—SUBTOTAL
- SUBTOTAL_LABEL Option
  - Considerations—SUBTOTAL_LABEL
  - Examples—SUBTOTAL_LABEL
- SUM Function
  - Considerations—SUM
  - Examples—SUM
- Super ID
- Syskeys
- System Catalog
- SYSTEM Command
  - Considerations—SYSTEM
  - Examples—SYSTEM
- System DEFINEs
T
- TABLECODE File Attribute
- Tables
- TABLES Table
- TEDIT Command
  - Examples—TEDIT
- Temporary Tables
- TIME_FORMAT Option
  - Examples—TIME_FORMAT
- TIME Data Type
  - Examples—TIME Data Type
- TIMESTAMP Data Type
  - Examples—TIMESTAMP Data Type
- TMF Transactions
- TOTAL Command
  - Considerations—TOTAL
  - Examples—TOTAL
- TRANSIDS Table
- TRIM Function
  - Considerations—TRIM Function
  - Examples—TRIM Function
U
- UNDERLINE_CHAR Option
  - Examples—UNDERLINE_CHAR
- UNLOCK TABLE Statement
  - Considerations—UNLOCK TABLE
  - Examples—UNLOCK TABLE
- UPDATE Statement
  - Considerations—UPDATE
  - Examples—UPDATE
- UPDATE STATISTICS Statement
  - Considerations—UPDATE STATISTICS
  - Examples—UPDATE STATISTICS
- UPGRADE CATALOG Command
  - Considerations—UPGRADE CATALOG
  - Examples—UPGRADE CATALOG
- UPGRADE SYSTEM CATALOG Command
  - Considerations—UPGRADE SYSTEM CATALOG
  - Examples—UPGRADE SYSTEM CATALOG
- UPSHIFT Function
  - Considerations—UPSHIFT
  - Examples—UPSHIFT
- USAGES Table
- User-Defined Keys
- Utilities
V
- VARCHAR_WIDTH Option
  - Considerations—VARCHAR_WIDTH
  - Examples—VARCHAR_WIDTH
- VERIFIEDWRITES File Attribute
  - Considerations—VERIFIEDWRITES
- VERIFY Command
  - Considerations—VERIFY
  - Examples—VERIFY
- Versions
- VERSIONS Table
- Views
- VIEWS Table
- VOLUME Command
  - Considerations—VOLUME
  - Examples—VOLUME
W
- WHENEVER DIRECTIVE
  - Considerations—WHENEVER Directive
- WHERE CLAUSE
- WINDOW OPTION
  - Considerations—WINDOW
  - Examples—WINDOW
- WITH SHARED ACCESS OPTION
  - Considerations—WITH SHARED ACCESS
  - Examples—WITH SHARED ACCESS
Z
- ! COMMAND
  - Examples—!
- =_AUDSERV_XSWAP_node DEFINE
  - Considerations—=_AUDSERV_XSWAP_node
  - Examples—=_AUDSERV_XSWAP_node
- =_DEFAULTS DEFINE
  - Considerations—=_DEFAULTS
  - Examples—=_DEFAULTS
- =_SORT_DEFAULTS DEFINE
  - Considerations—=_SORT_DEFAULTS
  - Examples—=_SORT_DEFAULTS
- =_SQL_CAT_HEAP_LIMIT DEFINE
- =_SQL_CMP_DOUBLE_SBB_OFF DEFINE
  - Considerations—=_SQL_CMP_DOUBLE_SBB_OFF
- =_SQL_CMP_DOUBLE_SBB_ON DEFINE
  - Considerations—=_SQL_CMP_DOUBLE_SBB_ON
- =_SQL_CMP_EQ_LIMIT DEFINE
  - Considerations—=_SQL_CMP_EQ_LIMIT
  - Examples—=_SQL_CMP_EQ_LIMIT
- =_SQL_CMP_EVENT DEFINE
- =_SQL_CMP_EVENT_NO0 DEFINE
- =_SQL_CMP_NO_KS_MJOIN DEFINE
  - Examples—=_SQL_CMP_NO_KS_MJOIN
- =_SQL_cmp_node DEFINE
  - Considerations—=_SQL_cmp_node
  - Examples—=_SQL_cmp_node
- =_SQL_EXE_DOUBLE_SHUTOFF DEFINE
  - Considerations—=_SQL_EXE_DOUBLE_SHUTOFF
- =_SQL_EXE_ESPS_CK_CMON DEFINE
  - Considerations—=_SQL_EXE_ESPS_CK_CMON
  - Examples—=_SQL_EXE_ESPS_CK_CMON
- =_SQL_EXE_USE_SWAPVOL DEFINE
  - Considerations—=_SQL_EXE_USE_SWAPVOL
  - Examples—=_SQL_EXE_USE_SWAPVOL
- =_SQL_MSG_node DEFINE
  - Considerations—=_SQL_MSG_node
  - Examples—=_SQL_MSG_node
- =_SQL_RECGEN_node DEFINE
  - Examples—=_SQL_RECGEN_node
- =_SQL_TM_node_vol DEFINE
  - Considerations—=_SQL_TM_node_vol
  - Examples—=_SQL_TM_node_vol
Index

NonStop SQL/MP Reference Manual—142115

A-1

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. The possible

settings for access options on DML statements are:

BROWSE access is not available on DML statements that modify the database.

STABLE access and REPEATABLE access are available on DML statements that

modify the database. STABLE access and REPEATABLE access are discussed in more

detail in the following subsections.

STABLE Access Option on DML Statements

STABLE locks all data accessed through the DML statement but releases locks on

unmodified data as soon as possible. STABLE access locks modified data in audited

tables until the end of the transaction.

In host programs that use cursors, STABLE locks an unmodified row only when the row

is in the current position and releases the lock at the next FETCH on the cursor. CLOSE

cursor releases the lock from the last FETCH.

For modified rows in audited tables, STABLE access uses exclusive locks held by the

TMF transaction that are released only when the entire transaction ends.

For nonaudited tables, all locks are held by the process (not the transaction), and

STABLE access releases locks when the statement finishes.

STABLE access provides sufficient consistency for any process that does not require a

repeatable read capability.

BROWSE Does not lock data. Reads locked data that might be inconsistent. Used for

browse access or when a table lock is in effect. Minimum consistency,

maximum concurrency.

STABLE Locks all data accessed but releases locks on unmodified rows without

waiting for the end of the transaction. The default for read operations.

REPEATABLE Locks all data accessed until the end of the transaction. Maximum

consistency, minimum concurrency. The default for update operations.