About This Document
Who Should Read This Manual
For More Information
SQL Overview
Working with SQL in Zen
Data Definition Statements
Creating, Modifying, and Deleting Tables
Creating and Deleting Views
Creating and Deleting Indexes
Creating and Deleting Triggers
Creating and Deleting Stored Procedures
Creating and Deleting User-Defined Functions (UDF)
Data Manipulation Statements
Retrieving Data
Modifying Data
Creating and Deleting Views
Executing Stored Procedures
Executing System Stored Procedures
Executing Triggers
Data Control Statements
Enabling and Disabling Security
Creating and Deleting Users and Groups
Granting and Revoking Rights
Zen Metadata
Comparison of Metadata Versions
Relational Engine Limits
Fully Qualified Object Names
Delimited Identifiers in SQL Statements
Examples
SQL Syntax Reference
Literal Values
String Values
Examples
Number Values
Date Values
Examples
Time Values
Examples
Time Stamp Values
Examples
SQL Grammar in Zen
ADD
Remarks
See Also
ALL
Remarks
Examples
See Also
ALTER (rename)
Syntax
Remarks
Examples
See Also
ALTER GROUP
Syntax
Remarks
Examples
See Also
ALTER TABLE
Syntax
Remarks
IN DICTIONARY
USING
WITH REPLACE
MODIFY COLUMN and ALTER COLUMN
PSQL_MOVE
RENAME COLUMN
ON DELETE CASCADE
Examples
See Also
ALTER USER
Syntax
Remarks
Examples
See Also
ANY
Remarks
Examples
See Also
AS
Remarks
Examples
See Also
BEGIN [ATOMIC]
Remarks
Example
See Also
CALL
Remarks
Examples
See Also
CASCADE
Remarks
See Also
CASE (expression)
Syntax
Arguments
Remarks
Examples
See Also
CASE (string)
Remarks
Examples
See Also
CLOSE
Syntax
Remarks
Examples
See Also
COALESCE
Syntax
Returned Value Types
Restrictions
COALESCE Supported Combination Types and Result Data Types
Examples
COMMIT
Syntax
Examples
See Also
CREATE DATABASE
Syntax
Remarks
Database Name and IF NOT EXISTS Clause
Dictionary Path
Data Path
Referential Integrity
BOUND
Dictionary Files
Security
Metadata Version
Encoding
Valid Code Page Names and Numbers
Examples
See Also
CREATE FUNCTION
Syntax
Remarks
Restrictions
Limits
Supported Scalar Input Parameters and Returned Data Types
Examples
Invoking a Scalar User-Defined Function
Limits
Examples of User-Defined Functions
See Also
CREATE GROUP
Syntax
Remarks
Examples
See Also
CREATE INDEX
Syntax
Remarks
Index Segments
UNIQUE
PARTIAL
Limitations of PARTIAL
Examples
NOT MODIFIABLE
USING
IN DICTIONARY
Examples
See Also
CREATE PROCEDURE
Syntax
Remarks
Trusted and Non-Trusted Stored Procedures
Memory Caching
Caching Exclusions
Data Type Restrictions
Limits
Examples
Using Stored Procedures
General Stored Procedure Engine Limitations
Limits to SQL Variables and Parameters
Limits to Cursors
Limits when using Long Data
See Also
CREATE TABLE
Syntax
Remarks
Limitations on Record Size
Example of Limitation on Record Size
Delete Rule
Update Rule
IN DICTIONARY
USING
WITH REPLACE
DCOMPRESS
PCOMPRESS
PAGESIZE
LINKDUP
Examples
See Also
CREATE (temporary) TABLE
Syntax
Remarks
Compatibility with Previous Releases
TEMPDB Database
Table Names of Local Temporary Tables
Transactions
SELECT INTO
Restrictions on SELECT INTO
Caching of Stored Procedures
Examples of Temporary Tables
See Also
CREATE TRIGGER
Syntax
Remarks
Examples
See Also
CREATE USER
Syntax
Remarks
Examples
See Also
CREATE VIEW
Syntax
Remarks
ORDER BY
Trusted and Non-Trusted Views
Examples of Trusted and Non-Trusted Views
See Also
DECLARE
Remarks
Examples
See Also
DECLARE CURSOR
Syntax
Remarks
Examples
See Also
DEFAULT
Syntax
Remarks
Restrictions on Identity Data Types
Scalar Functions and Simple Expressions as Default Column Values
Using DEFAULT with ALTER TABLE
Examples
See Also
DELETE (positioned)
Syntax
Remarks
Examples
See Also
DELETE
Syntax
Remarks
FROM Clause
Examples
DISTINCT
Examples
See Also
DROP DATABASE
Syntax
Remarks
Secured Databases
DELETE FILES
Examples
See Also
DROP FUNCTION
Syntax
Remarks
Examples
See Also
DROP GROUP
Syntax
Remarks
Examples
See Also
DROP INDEX
Syntax
Remarks
Partial Indexes
Examples
See Also
DROP PROCEDURE
Syntax
Remarks
Examples
See Also
DROP TABLE
Syntax
Remarks
Examples
See Also
DROP TRIGGER
Syntax
Remarks
Examples
See Also
DROP USER
Syntax
Remarks
Examples
See Also
DROP VIEW
Syntax
Remarks
Examples
See Also
END
Remarks
EXECUTE
Syntax
Remarks
Examples
See Also
EXISTS
Syntax
Remarks
Examples
See Also
FETCH
Syntax
Remarks
Examples
See Also
FOREIGN KEY
Remarks
Examples
See Also
GRANT
Syntax
Remarks
GRANT LOGIN TO
Constraints on Permissions
By Object Type
ALL Keyword
GRANT and Data Security
Granting Privileges to Users and Groups
Granting Access Using Owner Names
Permissions on Views and Stored Procedures
Trusted and Non-Trusted Objects
Examples
See Also
GROUP BY
See Also
HAVING
Examples
See Also
IF
Syntax
Remarks
Examples
See Also
IN
Remarks
Examples
See Also
INSERT
Syntax
Remarks
INSERT ON DUPLICATE KEY UPDATE
Inserting Data Longer Than the Maximum Literal String
Examples
Examples for INSERT
Examples for INSERT ON DUPLICATE KEY UPDATE
Errors When Using DEFAULT
See Also
JOIN
Syntax
LEFT OUTER
Vendor Strings
Examples
Emp Table
Dept Table
Addr Table
Loc Table
Algorithm
See Also
LAG
Syntax
Examples
LEAVE
Remarks
Examples
See Also
LIKE and ILIKE
Syntax
Remarks
Examples of LIKE
Examples of ILIKE
See Also
LOOP
Remarks
Examples
See Also
NOT
Remarks
Examples
See Also
OPEN
Syntax
Remarks
Examples
See Also
PARTIAL
Remarks
See Also
PRIMARY KEY
Remarks
Examples
See Also
PRINT
Remarks
Examples
See Also
PUBLIC
Remarks
Examples
See Also
RELEASE SAVEPOINT
Syntax
Remarks
Examples
See Also
RESTRICT
Remarks
See Also
REVOKE
Syntax
Examples
See Also
ROLLBACK
Syntax
Remarks
Examples
See Also
SAVEPOINT
Syntax
Remarks
Examples
See Also
SELECT
Syntax
Remarks
FOR UPDATE
Constraints
GROUP BY
SQL Windowing Functions
Limitations
Considerations
Dynamic Parameters
Aliases
SUM and DECIMAL Precision
Subqueries
Subquery Optimization
UNION in Subquery
Table Subqueries
Using Table Hints
Table Hint Restrictions
Accessing System Data v2
Examples
FOR UPDATE
FOR UPDATE
Approximate Numeric Literal
Between Predicate
Correlation Name
Exact Numeric Literal
In Predicate
Set Functions
Date Literal
Time Literal
Time Stamp Literal
String Literal
Date Arithmetic
IF
Multidatabase Join
Left Outer Join
Right Outer Join
Cartesian Join
Queries with Sys$create and Sys$update
DISTINCT in Aggregate Functions
TOP or LIMIT
Cursor Types and TOP or LIMIT
TOP or LIMIT Examples
Table Hint Examples
See Also
SELECT (with INTO)
Syntax
Remarks
Examples
See Also
SET
Syntax
Remarks
Examples
See Also
SET ANSI_PADDING
Syntax
Remarks
Restrictions
Examples
See Also
SET CACHED_PROCEDURES
Syntax
Remarks
Registry Setting
Caching Exclusions
Examples
See Also
SET DECIMALSEPARATORCOMMA
Syntax
Remarks
Examples
See Also
SET DEFAULTCOLLATE
Syntax
Remarks
Using ACS Files
Using ISR Table Names
ACS, ISR, and ICU Examples
See Also
SET LEGACYTYPESALLOWED
Syntax
Remarks
Example
SET OWNER
Syntax
Remarks
Examples
See Also
SET PASSWORD
Syntax
Remarks
Password Characteristics
Examples
See Also
SET PROCEDURES_CACHE
Syntax
Remarks
Registry Setting
Caching Exclusions
Examples
See Also
SET ROWCOUNT
Syntax
Remarks
Examples
See Also
SET SECURITY
Syntax
Remarks
User Permissions
Examples
See Also
SET TIME ZONE
Syntax
Remarks
A Note about Time Stamp Data Types
Examples
See Also
SET TRUEBITCREATE
Syntax
Remarks
Example
SET TRUENULLCREATE
Syntax
Remarks
Examples
SIGNAL
Remarks
Syntax
Examples
See Also
SQLSTATE
Remarks
See Also
START TRANSACTION
Syntax
Remarks
Examples
See Also
UNION
Remarks
Examples
See Also
UNIQUE
Remarks
See Also
UPDATE
Syntax
Remarks
Updating Data Longer Than the Maximum Literal String
FROM Clause
Examples
See Also
UPDATE (positioned)
Syntax
Remarks
Examples
See Also
USER
Remarks
Example
See Also
WHILE
Syntax
Remarks
Examples
See Also
Grammar Element Definitions
SQL Statement List
Predicate
Expression
Global Variables
@@IDENTITY and @@BIGIDENTITY
Examples
@@ROWCOUNT
Examples
@@SESSIONID
Example
@@SPID
Example
@@VERSION
Example
Other Characteristics
Temporary Files
When Are Temporary Files Created?
In-Memory Temporary File
On-Disk Temporary File
Btrieve Temporary File
Working with NULL Values
Working with Binary Data
Creating Indexes
Comma as Decimal Separator
Client-Server Considerations
Changing the Locale Setting
Examples
Example A – Server locale uses a comma for decimal separator
Example B – Server locale uses the period for decimal separator
Scalar Functions
Bitwise Operators
Truth Table
Arithmetic Operators
Date Arithmetic
Example
String Functions
Examples
Numeric Functions
Examples
Time and Date Functions
Time and Date Function Examples
System Functions
System Function Examples
Logical Functions
Logical Function Examples
Conversion Functions
Conversion Function Examples
System Stored Procedures
Zen System Stored Procedures
psp_columns
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Result Set
Error Conditions
psp_column_attributes
Syntax
Arguments
Returned Result Set
Examples
Result Set
Result Set
Result Set
Error Conditions
psp_column_rights
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Result Set
Result Set
Error Conditions
psp_fkeys
Syntax
Arguments
Returned Result Set
Example
Result Set
Error Conditions
psp_groups
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_help_sp
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_help_trigger
Syntax
Arguments
Returned Result Set
Example
Result Set
Error Conditions
psp_help_udf
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_help_view
Syntax
Arguments
Returned Result Set
Example
Result Set
Error Conditions
psp_indexes
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_pkeys
Syntax
Arguments
Returned Result Set
Example
Result Set
Error Conditions
psp_procedure_rights
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_rename
Syntax
Arguments
Example
Error Conditions
psp_stored_procedures
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_tables
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Result Set
Error Conditions
psp_table_rights
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_triggers
Syntax
Arguments
Returned Result Set
Example
Result Set
Error Conditions
psp_udfs
Syntax
Arguments
Returned Result Set
Example
Result Set
Error Conditions
psp_users
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
psp_view_rights
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Result Set
Error Conditions
psp_views
Syntax
Arguments
Returned Result Set
Example
Result Set
Result Set
Error Conditions
Performance Reference
Restriction Analysis
Modified CNF Conversion
Restrictions that Cannot be Converted
Conditions Under Which Conversion is Avoided
Restriction Optimization
Single Predicate Optimization
Closed Range Optimization
Modified Disjunct Optimization
Conjunct Optimization
Disjunctive Normal Form Optimization
Modified Conjunctive Normal Form Optimization
Closing Open-Ended Ranges through Modified CNF Optimization
Single Join Condition Optimization
Conjunct with Join Conditions Optimization
Modified Conjunctive Normal Form with Join Conditions Optimization
Closing Join Condition Open-Ended Ranges through Modified CNF Optimization
Multi-Index Modified Disjunct Optimization
Push-Down Filters
Efficient Use of Indexes
DISTINCT in Aggregate Functions
DISTINCT Preceding Selection List
Relaxed Index Segment Order Sensitivity
Relaxed Segment Ascending Attribute Sensitivity
Search Update Optimization
Temporary Table Performance
Row Prefetch
Terminology
Aggregate Function
Closed Range
Conjunct
Conjunctive Normal Form (CNF)
Disjunct
Disjunctive Normal Form (DNF)
Expression
Index
Join Condition
Leading Segments
Modified Conjunctive Normal Form (Modified CNF)
Modified Disjunct
Open-Ended Range
Predicate
Restriction
System Catalog Functions
Zen System Catalog Functions
Return Status
Summary
dbo.fSQLColumns
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLForeignKeys
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLPrimaryKeys
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLProcedures
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLProcedureColumns
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLSpecialColumns
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLStatistics
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLTables
Syntax
Arguments
Returned Result Set
Example
dbo.fSQLDBTableStat
Syntax
Argument
Returned Result Set
Example
String Search Patterns
Examples
A. Data Types
Zen Supported Data Types
Data Type Ranges
Operator Precedence
Parentheses
Data Type Precedence
Numeric Data Types
Character Data Types
Data Types with No Precedence
Precision and Scale of Decimal Data Types
Scale of Time Stamp Data Types and Returned Function Values
Truncation
Notes on Data Types
CHAR, NCHAR, VARCHAR, NVARCHAR, LONGVARCHAR, and NLONGVARCHAR
BINARY and LONGVARBINARY
Limitations on LONGVARCHAR, NLONGVARCHAR and LONGVARBINARY
DATETIME
Format of DATETIME
Compatibility of Date and Time Data Types
UNIQUEIDENTIFIER
Declaring Variables
Converting UNIQUEIDENTIFIER to Another Data Type
Representation of Infinity
Legacy Data Types
Btrieve Key Data Types
AUTOINCREMENT
Restrictions
AUTOTIMESTAMP
Inserts and Updates Using AUTOTIMESTAMP
Restrictions
Usage in Function Executor and Maintenance Tools
BFLOAT
STRING
CURRENCY
DATE
DECIMAL
Windows
Linux and macOS
FLOAT
GUID
GUID Keys
INTEGER
LOGICAL
LSTRING
MONEY
NUMERIC
Enabling the Shifted Format
Windows 32-Bit
Linux and macOS
Consistent Sign Values for Positive NUMERIC Data
NUMERICSA
NUMERICSLB
NUMERICSLS
NUMERICSTB
NUMERICSTS
TIME
TIMESTAMP
Usage in Function Executor and Maintenance Tools
TIMESTAMP2
Usage in Function Executor and Maintenance Tools
UNSIGNED BINARY
WSTRING
WZSTRING
ZSTRING
Non-Key Data Types
BLOB
CLOB
B. SQL Reserved Words
Reserved Words
Symbols
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
Words to Avoid
C. System Tables
Overview
System Tables Structure
V1 Metadata System Tables
X$Attrib
X$Depend
X$Field
X$File
X$Index
X$Proc
X$Relate
X$Rights
X$Trigger
X$User
X$View
V2 Metadata System Tables
X$Attrib
X$Depend
X$Field
X$File
X$Index
X$Proc
X$Relate
X$Rights
X$Trigger
X$User
X$View
D. SQL Access for COBOL Applications
Overview of Zen Support for COBOL
Restrictions
SQL Statements
Components
Using SQL Access
Step 1: Modify the Sample XML Templates
Step 2: Copy the Data File Specified in the XML Template
Step 3: Run the Schema Executor Utility
Schema Executor Command Format
Example Usage
Creating a New Database with Schema Executor
Log Messages
Step 4: Optionally, Deploy the System Tables
Example of How to Execute a Sample XML File
Additional Notes
SELECT Statements
Table Filters
Examples of Valid TABLEFILTER Usage
E. Query Plan Viewer
Query Plan Settings
Graphical User Interface
Query Viewer
Plan Viewer
Nodes
Node Details
Query Plan Viewer Tasks
Examining Query Plans and Evaluating Query Performance
Creating Example Query Plans for Comparison
Viewing the Example Query Plans
About This Document
E. Query Plan Viewer