SQL Language Guide
About This Guide
In This Guide
Audience
System-specific Text in This Guide
Terminology Used in This Guide
Syntax Conventions Used in This Guide
Introducing SQL
ANSI Compliance
Interactive SQL
Line-Based Terminal Monitor
Object Naming Rules
User Names
Regular and Delimited Identifiers
Case Sensitivity of Identifiers
Restrictions on Identifiers
Comment Delimiters
Statement Terminators
Correlation Names
Correlation Name Rules
Security Levels
Data Types
Data Types
Char Data Type
Varchar Data Type
Nchar Data Type
Nvarchar Data Type
Integer Data Types
Decimal Data Type
Floating Point Data Types
Float Point Limitations
Money Data Type
Ansidate Data Type
Time Data Types
Timestamp Data Types
Interval Data Types
Boolean Data Type
IP Network Address Data Types
Universal Unique Identifier (UUID)
UUID Format
Storage Formats of Data Types
Literals
String Literals
Hexadecimal Representation
Quotes within Strings
Unicode Literals
Numeric Literals
Integer Literals
Decimal Literals
Floating Point Literals
Date and Time Literals
Date Literals
Time Literals
Timestamp Literals
Interval Literals
Boolean Literals
Constants
Nulls
Nulls and Comparisons
Nulls and Aggregate Functions
Elements of SQL Statements
SQL Operators
Arithmetic Operators
Comparison Operators
Logical Operators
SQL Operations
String Concatenation Operations
Assignment Operations
Character String Assignments
String Truncation
Numeric Assignments
Date and Time Assignments
Null Value Assignments
Arithmetic Operations
Default Type Conversion
Arithmetic Operations on Decimal Data Types
Date and Time Arithmetic
ANSI Date and Time Comparisons
Operator Coercion Rules
SQL Functions
Aggregate Functions
Basic Aggregate Functions
Regression and Correlation Analysis Aggregate Functions
String Aggregate Functions
Ordering Aggregate Functions
Aggregate Functions and Decimal Data
GROUP BY Clause with Aggregate Functions
Restrictions on the Use of Aggregate Functions
Window Functions
Window Function Syntax
Windowing Aggregate Functions
Analytical Functions
Numeric Functions
String Functions
String Functions and the UTF8 Character Set
Date and Time Functions
Conversion Functions
Length of Results for Data Type Conversions
Bitwise Functions
Hashing Functions
Random Number Functions
Null Handling Functions
IP Network Address Functions
UUID ([expr]) Function
Encryption Functions
MASK_COLUMN Function
DBMSINFO Function
Dbmsinfo Examples
Request Names for DBMSINFO Function
Scalar User-defined Functions (UDFs)
How to Set up User-defined Functions
Expressions in SQL
CASE Expressions
DECODE Function
IF, NULLIF, and COALESCE Functions
GREATEST, GREATER, LEAST, LESSER Functions
CAST Expressions
Predicates in SQL
Comparison Predicate
Pattern-matching Predicates
LIKE Predicate
BEGINNING, CONTAINING, and ENDING Predicates
BETWEEN Predicate
IN Predicate
Any-or-All Predicate
EXISTS Predicate
IS NULL Predicate
IS INTEGER Predicate
IS DECIMAL Predicate
IS FLOAT Predicate
IS TRUE, IS FALSE, IS UNKNOWN Predicates
Search Conditions in SQL Statements
Subqueries
Subqueries in the WHERE Clause
Subqueries in the FROM Clause (Derived Tables)
Derived Table Syntax
Subqueries in the Target List, SET and VALUE Clauses (Scalar Subqueries)
Scalar Subqueries
Common Table Expressions (CTEs)
Common Table Expression Format
Components of a CTE
How CTEs Are Used
Examples of Using CTEs: The Person and Relative Tables
Referring to a Defined WITH Element
Using Nested WITH Element References
WITH Elements in CREATE VIEW
Pivot Tables
PIVOT Format
Pivot Table Examples
Sales Summary
Range Pivoting
Pivot Table Usage Notes
Embedded SQL
Embedded SQL Statements
How Embedded SQL Statements Are Processed
General Syntax and Rules of an Embedded SQL Statement
Syntax Conventions Used in this Chapter
Structure of an Embedded SQL Program
Host Language Variables in Embedded SQL
Variable Declaration
INCLUDE Statement
Variable Usage
Variable Structures
Dclgen Utility--Generate Structure
Indicator Variables
Indicator Variable Declaration
Null Indicators and Data Retrieval
Using Null Indicators to Assign Nulls
Indicator Variables and Character Data Retrieval
Null Indicator Arrays and Host Structures
Data Manipulation with Cursors
Example: Cursor Processing
Cursor Declaration
Open a Cursor
Readonly Cursors
Open Cursors and Transaction Processing
Fetch Data From Cursor
Fetch Rows Inserted by Other Queries
Closing Cursors
Summary of Cursor Positioning
Dynamically Specifying Cursor Names
Cursors Versus Select Loops
Dynamic Programming
SQLDA
Structure of the SQLDA
Including the SQLDA in a Program
DESCRIBE Statement and SQLDA
Data Type Codes
USING Clause
Dynamic SQL Statements
EXECUTE IMMEDIATE Statement
PREPARE and EXECUTE Statements
DESCRIBE Statement
Execute a Dynamic Non-select Statement
Using EXECUTE IMMEDIATE to Execute a Non-select Statement
Preparing and Executing a Non-select Statement
Execute a Dynamic SELECT Statement
Unknown Result Column Data Types
How Unknown Result Column Data Types are Handled
Prepare and Describe SELECT Statements
Sqlvar Elements
SELECT Statement with EXECUTE IMMEDIATE
Retrieve Results Using Cursors
Working with Transactions and Handling Errors
Transactions
How Transactions Work
How Consistency is Maintained During Transactions
SET AUTOCOMMIT ON--Commit Individual Statement
How to Determine if You Are in a Transaction
Statements Used to Control Transactions
How Effects of a Transaction Are Controlled
How the Transaction Processing System Handles Interrupts
Abort Policy for Transactions and Statements
Conflicts Detected at Commit Time
How to Direct the DBMS to Roll Back an Entire Transaction or Statement
Ways to Obtain Status Information
SESSION_PRIV Function--Determine If Session Has a Privilege
DBMSINFO Function--Return Information About the Current Session
Dbmsinfo Examples
INQUIRE_SQL Function
SQL Communications Area (SQLCA)
Variables that Compose SQLCA
SQLCODE and SQLSTATE
SQLCODE Variable
SQLSTATE Variable
Error Handling
Types of Error Codes
Error Message Format
Display of Error Messages
Error Handling in Embedded Applications
Error Information from SQLCA
SQLSTATE
Error Trapping Using WHENEVER Statement
How to Define an Error-Handling Function
Event Handlers and Message Handlers
Error Checking Using Inquire Statements
Set_Sql(Programquit)--Specify Whether to Abort on Error
Database Procedures, Sessions, and Events
How Database Procedures Are Created, Invoked, and Executed
Benefits of Database Procedures
Contents of Database Procedures
Permissions on Database Procedures
Methods of Executing Procedures
How Parameters Are Passed in Database Procedures
Row-Producing Procedures
Format of a Row-Producing Procedure
How a Row-Producing Procedure Is Called
Row-Producing Procedure Example
Effects of Errors in Database Procedures
iierrornumber and iirowcount Variables
RAISE ERROR Statement
Messages from Database Procedures
Message Handling Using the WHENEVER Statement
Message Handling Using User-Defined Handler Routines
Multiple Session Connections
Multiple Sessions
Session Identification
Session Switching
Disconnection of Sessions
Status Information in Multiple Sessions
What You Should Know When Creating Multiple Sessions
Example: Two Open Sessions
Examples: Session Switching
Database Events
Database Event Statements
Create a Database Event
Raise a Database Event
Register Applications to Receive a Database Event
How a Database Event Is Received
Get a Database Event
How to Process Database Events
Get Dbevent Statement Example
Whenever Dbevent Statement
User-Defined Database Event Handlers
Remove a Database Event Registration
Drop a Database Event
Privileges and Database Events
Trace Database Events
Working with JSON Data
Data Types
JSON Scalars
JSON Arrays
JSON Objects
JSON Concepts
Lax and Strict Modes
JSON Sequences
JSON Member Accessors
JSON API Common Syntax
JSON Predicates
EXISTS
Comparison Operators
SIMILAR TO
STARTS WITH
IS UNKNOWN
JSON Constructors
JSON_OBJECT
JSON_OBJECTAGG
JSON_ARRAY
JSON_ARRAYAGG
How JSON Dates Are Handled
JSON Query Functions
Use of Operators with JSON Data
Arithmetic Expressions
Booleans and Boolean Operators
Comparison Operators
JSON Item Methods
SQL Statements
Statement Contexts
ALTER GROUP
ALTER GROUP Examples
ALTER LOCATION
ALTER LOCATION Examples
ALTER PROFILE
ALTER PROFILE Examples
ALTER ROLE
ALTER ROLE Examples
ALTER SECURITY_AUDIT
ALTER SECURITY_AUDIT Examples
ALTER SEQUENCE
ALTER SEQUENCE Examples
ALTER TABLE
ALTER TABLE...ALTER COLUMN Restrictions
Rules and Restrictions on Renaming Tables
Rules and Restrictions on Renaming Columns
Constraint Specifications
Named Constraints
ALTER TABLE Examples
ALTER USER
ALTER USER Examples
CALL X100
CLEANUP_UNUSED_FILES Command
CONDENSE_LOG Command
FORCE_TRANSACTION_DISALLOW_COMMIT Command
SETCONF Command
TERMINATE Command
TERMINATE_IF_IDLE Command
TOTAL_DB_SIZE Command
VWLOG_RELOAD Command
VWLOG_ROTATE Command
CLOSE
CLOSE Example
COMMENT ON
COMMENT ON Examples
COMMIT
CONNECT
CONTINUE
Permissions
COPY
Unformatted Copying
Formatted Copying
Column Formats for COPY
Character (Text) Formats
Counted Character Formats
Unicode Formats
Dummy Format
Binary Formats
COPY Format Details
Delimiters in the Data File
WITH NULL Clause for COPY
Filename Specification for COPY
Windows File Types for COPY
With Clause Options for COPY
Filename Specification for COPY
COPY Examples
COPY VWLOAD
COPY VWLOAD Examples
vwload Escape Sequences
vwload Date Format Settings
vwload Supported Character Sets
CREATE DBEVENT
CREATE EXTERNAL TABLE
CREATE EXTERNAL TABLE Examples
CREATE FUNCTION
CREATE FUNCTION Examples
CREATE GROUP
CREATE GROUP Examples
CREATE INDEX
CREATE INDEX Examples
CREATE LOCATION
CREATE LOCATION Example
CREATE PROCEDURE
Description
Parameter Modes
Nullability and Default Values for Parameters
SET OF Parameters
Embedded Usage
Permissions
Related Statements
CREATE PROCEDURE Examples
CREATE PROFILE
CREATE PROFILE Examples
CREATE ROLE
CREATE ROLE Examples
CREATE SECURITY_ALARM
CREATE SECURITY_ALARM Examples
CREATE SEQUENCE
CREATE SEQUENCE Examples
CREATE STATISTICS
CREATE STATISTICS Example
CREATE SYNONYM
CREATE TABLE
Column Specification
DEFAULT Clause
Restrictions on the Default Value for a Column
Null Clause and Default Clause Combinations
Masked Column Default Values
IDENTITY Columns
Constraints
Table-level and Column-level Constraints
WITH Clause
MINMAX_SAMPLES Option
Partitioned Tables
Partitioning Schemes
Partitioning Syntax
Guidelines for Partitioned Tables
CREATE TABLE Examples
CREATE USER
CREATE USER Examples
CREATE VIEW
CREATE VIEW Example
DECLARE
DECLARE Example
DECLARE CURSOR
DECLARE CURSOR Examples
DECLARE GLOBAL TEMPORARY TABLE
DECLARE GLOBAL TEMPORARY TABLE Examples
DECLARE TABLE
DELETE
DELETE Examples
DESCRIBE
DESCRIBE Examples
DESCRIBE INPUT
DISABLE PASSPHRASE
DISABLE PASSPHRASE Examples
DISABLE SECURITY_AUDIT
DISABLE SECURITY_AUDIT Example
DISCONNECT
DISCONNECT Examples
DROP
DROP Examples
DROP DBEVENT
DROP DBEVENT Example
DROP FUNCTION
DROP FUNCTION Example
DROP GROUP
DROP GROUP Examples
DROP LOCATION
DROP LOCATION Example
DROP PROCEDURE
DROP PROCEDURE Example
DROP PROFILE
DROP PROFILE Example
DROP ROLE
DROP ROLE Example
DROP SECURITY_ALARM
DROP SECURITY_ALARM Examples
DROP SEQUENCE
DROP SEQUENCE Example
DROP STATISTICS
DROP STATISTICS Examples
DROP SYNONYM
DROP USER
ENABLE PASSPHRASE
ENABLE PASSPHRASE Examples
ENABLE SECURITY_AUDIT
ENABLE SECURITY_AUDIT Example
ENDLOOP
ENDSELECT
ENDSELECT Example
EXECUTE
EXECUTE Examples
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE Examples
EXECUTE PROCEDURE
Passing Parameters - Non-Dynamic Version
Passing Parameters - Dynamic Version
Positional Parameters Sample Syntax
Temporary Table Parameter
Limitations of Temporary Table Parameter
Execute Procedure Loops
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Performance
Related Statements
EXECUTE PROCEDURE Examples
FETCH
FETCH Examples
FOR - ENDFOR
FOR - ENDFOR Example
GET DBEVENT
GOTO
GOTO label Example
GRANT (privilege)
Object Privileges
TABLE Privileges
TABLE Privileges for Views
DATABASE Privileges
PROCEDURE Privileges
DBEVENT Privileges
SEQUENCE Privileges
Privilege Defaults
GRANT ALL PRIVILEGES Option
Installation and Database Privileges
Other Privileges
Granting All Privileges on Views
GRANT OPTION Clause
GRANT (PRIVILEGE) Examples
GRANT (role)
GRANT (role) Example
HELP
HELP Examples
IF - THEN - ELSE
IF Statement
IF Statement
IF...THEN Statement
IF...THEN...ELSEIF Statement
Nesting IF Statements
IF-THEN-ELSE Example
INCLUDE
INCLUDE Examples
INQUIRE_SQL
INQUIRE_SQL Example
INSERT
INSERT Examples
INSERT INTO EXTERNAL CSV
INSERT INTO EXTERNAL CSV Examples
MERGE
MERGE Examples
MESSAGE
MESSAGE Examples
MODIFY
MODIFY...TO COMBINE Statement
MODIFY...TO COMBINE Examples
MODIFY...TO RECONSTRUCT Statement
MODIFY...TO RECONSTRUCT Examples
MODIFY Examples
OPEN
OPEN Examples
PREPARE
PREPARE Example
RAISE DBEVENT
Description
RAISE ERROR
RAISE ERROR Example
REGISTER DBEVENT
REGISTER TABLE
Security Log File Format
REGISTER TABLE Example
REMOVE DBEVENT
REMOVE TABLE
REMOVE TABLE Example
RENAME TABLE
RENAME TABLE Example
REPEAT - ENDREPEAT
REPEAT - ENDREPEAT Example
RETURN
RETURN Example
RETURN ROW
RETURN ROW Example
REVOKE
Revoking Grant Option
Restrict versus Cascade
REVOKE Examples
ROLLBACK
SELECT (Interactive)
SELECT Statement Clauses
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY Clause
LIMIT Clause
OFFSET Clause and FETCH_FIRST Clause
UNION Clause
INTERSECT Clause
EXCEPT Clause
WITH Clause for SELECT
Query Evaluation
Syntax for Specifying Tables and Views
Joins
Join Relationships
ANSI/ISO Join Syntax
SELECT Examples
SELECT (Embedded)
Non-Cursor Select
Select Loops
INTO Clause—Retrieve Values into Host Language Variables
REPEATED Queries
Cursor Select
Error Handling for Embedded SELECT
Embedded Usage
Related Statements
SELECT (embedded) Examples
SET
SET Options
SET_SQL
Permissions
Related Statements
UPDATE
FROM Clause
WHERE Clause
UPDATE Examples
WHENEVER
Embedded Usage
Permissions
Locking
Related Statements
WHENEVER Examples
WHILE - ENDWHILE
While - Endwhile Permissions
WHILE - ENDWHILE Example
WITH (common_table_expression)
Guidelines for Using CTEs
WITH (common_table_expression) Embedded Usage
WITH (common_table_expression) Examples
A. Terminal Monitor
Terminal Monitor
Start the Terminal Monitor
Terminal Monitor Query Buffering
Terminal Monitor Commands
Terminal Monitor Character Input and Output
The HELP Statement
Terminal Monitor Macros
Macro Concepts
System Macros
Macro Evaluation
Macro Parameters
Parameter Prescan
Using Tab or Newline
Use of Quotes
Use of Backslashes
Special {define} Processing
Terminal Monitor Macro Examples
B. System Catalogs
Standard Catalog Interface
Example of HELP VIEW and HELP Statements
Standard Catalogs for All Databases
iiaccess Catalog
iialt_columns Catalog
iiaudittables Catalog
iicolumns Catalog
iiconstraint_indexes Catalog
iiconstraints Catalog
iidb_comments Catalog
iidb_subcomments Catalog
iidbcapabilities Catalog
iidbconstants Catalog
iidistcols Catalog
iidistschemes Catalog
iievents Catalog
iifile_info Catalog
iihistograms Catalog
iiindex_columns Catalog
iiindexes Catalog
iiingres_tables Catalog
iiintegrities Catalog
iikeys Catalog
iikey_columns Catalog
iilog_help Catalog
iilpartitions Catalog
iimulti_locations Catalog
iipermits Catalog
iiphysical_tables Catalog
iiprocedures Catalog
iiproc_access Catalog
iiproc_params Catalog
iiproc_rescols Catalog
iirange Catalog
iiref_constraints Catalog
iiregistrations Catalog
iirules Catalog
iisecurity_alarms Catalog
iisession_privileges Catalog
iisequences Catalog
iistats Catalog
iisynonyms Catalog
iitables Catalog
iiviews Catalog
Standard Catalogs for iidbdb
iiaudit Catalog
iidatabase_info Catalog
iidbprivileges Catalog
iiextend_info Catalog
iilocation_info Catalog
iiprofiles Catalog
iirolegrants Catalog
iiroles Catalog
iisecurity_state Catalog
iiusers Catalog
Mandatory and Vector-only Standard Catalogs
Mandatory Catalogs With Entries Required
Mandatory Catalogs Without Entries Required
Vector-only Catalogs
X100 Query Profiling System Catalogs
iivwprof_query Catalog
iivwprof_op Catalog
iivwprof_io Catalog
iivwprof_stage Catalog
iivwprof_expr Catalog
iivwprof_parse_tree Catalog
Geospatial System Catalogs
DBMS System Catalogs
System Catalogs for All Databases
System Catalogs for iidbdb
SQL Language Guide
SystemCatalogs.12.20.htm