SET SESSION TRANSACTION ISOLATION LEVEL Syntax Elements - Analytics Database - Teradata Vantage
SQL Data Definition Language Syntax and Examples
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2025-03-30
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™
Introduction to SQL Data Definition Language Syntax and Examples
Changes and Additions
SQL DDL Statements
How the Statements are Organized
List of SQL Statements and Purposes
Table Statements
CREATE TABLE and CREATE TABLE AS
CREATE TABLE Syntax
CREATE TABLE AS Syntax
Syntax Elements
table_kind
table_specification
table_option
MAP
Usage Notes
Examples
FALLBACK
WITH JOURNAL TABLE
LOG
BEFORE JOURNAL
AFTER JOURNAL
JOURNAL
CHECKSUM
FREESPACE
mergeblockratio
Example: Specifying Various MERGEBLOCKRATIO Settings
data_block_size
Example: Specifying DATABLOCKSIZE, CHECKSUM, and FREESPACE
blockcompression
isolated_loading
column_partition_definition
column_name
data type
column_data_type_attribute
UPPERCASE
CASESPECIFIC
FORMAT quotestring
TITLE quotestring
NAMED name
DEFAULT
WITH DEFAULT
CHARACTER SET server_character_set
NULL
AUTO COLUMN
Examples: Creating a Table with a JSON Auto Column
compression_attribute
COMPRESS constant
COMPRESS USING
DECOMPRESS USING
column_constraint_attribute
CONSTRAINT
UNIQUE
PRIMARY KEY
CHECK Constraint Column Attribute
Example: Specifying Column-Level Named CHECK Constraints
REFERENCES Constraint Column Attribute
identity_column
Identity Column Parameters
COLUMN
ROW
(column_name)
AUTO COMPRESS
PERIOD FOR
Example: Creating a Table with a Derived Period Column
normalize_option
table_constraint
PRIMARY KEY (column_name)
CHECK (boolean_condition)
FOREIGN KEY
WITH NO CHECK OPTION
row_level_security_constraint_column_name CONSTRAINT
index
PRIMARY INDEX
NO PRIMARY
PRIMARY AMP
PARTITION BY
partitioning level
UNIQUE INDEX
ORDER BY
WITH LOAD IDENTITY
table_preservation
AS_clause
source_table
subquery_clause
WITH DATA
WITH NO DATA
AND [NO] STATISTICS
Example: Copying Statistics
Example: Copying Statistics for All Columns
Example: Copying Zeroed Statistics
Example: Copying Zeroed Multicolumn and Composite Index Statistics
Example: Copying PARTITION Statistics
INDEX Definitions
Example: CREATE TABLE AS, CT AS, and Table Kind
Example: Copy Table with Data and Default Column Names
Example: Copy Table without Data and Default Column Names
Example: Changing Column Grouping for Column-Partitioned Table Using CREATE TABLE AS Syntax
Examples
Example: CHARACTER Partitioning Using a CASE_N Function
Example: CHARACTER Partitioning Defined Using a RANGE_N Function
Example: Character Partitioning Defined Using RANGE_N Functions
Example: Character Partitioning, Session Mode, and Case Specificity
Example: Character Partitioning and System-Derived PARTITION[#Ln] Columns
Example: RANGE_N Partitioning Expression Using the END Bound Function
Example: CASE_N Partitioning Expression Using the END Bound Function
Example: 15 Levels of Multilevel Partitioning
Example: Volatile Table With Single-Level Partitioning
Example: Global Temporary Table With Single-Level Partitioning
Example: Specifying a Column-Level Primary Key
Example: Default Primary Index When Neither Primary Index Nor Primary Key Is Specified
Example: Local Journaling
Example: CREATE TABLE AS … WITH NO DATA with Named Expressions
Example: CREATE TABLE AS ... WITH NO DATA with Nonunique Secondary Index
Example: CREATE TABLE AS … WITH DATA with Nonunique Secondary Index
Example: Statistics Not Copied
Example: Statistics Are Not Copied for Conditions in The Subquery
Example: Statistics Are Not Copied When Set Or Aggregation Operators Are Specified In The Query
Example: Statistics Are Not Copied If An OLAP Operation Is Specified in The Subquery
Example: Statistics Are Not Copied If a Join Operation Is Specified in the Subquery
Example: Statistics Are Not Copied If Functions Or Expressions Are Specified in the Subquery
Example: CREATE TABLE AS Requests That Create Table with Primary Index
Example: CREATE Request with Presence or Absence of PRIMARY KEY or UNIQUE Constraint and Setting of PrimaryIndexDefault
Example: NoPI Table CREATE Request That Converts PRIMARY KEY or UNIQUE Constraints to USIs
Example: CREATE Request That Produces a NoPI Table
Example: CREATE TABLE … AS Requests That Create NoPI Table
Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression
Example: CURRENT_DATE Built-In Function in a CASE_N Expression
Example: Quarterly Partitioning Using CURRENT_DATE in a RANGE_N Expression
Example: Historical and Current Partitioning Using CURRENT_DATE in a CASE_N Expression
Example: Partitioning Expression With TIMESTAMP Data Type
Example: Partitioning Expression That Specifies AT LOCAL Date
Example: Grouping Columns in the Column List of a Column-Partitioned Table Rather Than in the Partitioning Expression
Example: Column-Partitioned Table With Default Autocompression
Example: Columns Defined Using Different Types of NUMBER Types
Example: UDT Primary and Secondary Index Examples
Example: Specifying the Same Column in Different Ways
Example: Create Tables with ST_GEOMETRY Data Type Columns
Example: Create Tables with XML Data Type Columns
Example: Create Tables with JSON Data Type Columns
Example: Creating a Table With Row-Level Security Constraints
Related Information
CREATE TABLE (Queue Table Form)
CREATE TABLE Syntax (Queue Table Form)
CREATE TABLE Syntax Elements (Queue Table Form)
table_kind
table_specification
table_option
MAP
Usage Notes
Example
FALLBACK
FREESPACE
data_block_size
blockcompression
CHECKSUM
QITS_specification
QITS_column_name
column_name
compression_attributes
column_attribute
column_constraint_attributes
CONSTRAINT name
UNIQUE
PRIMARY KEY
column_name
CHECK (boolean condition)
column_identity_attributes
identity_attribute
START WITH
INCREMENT BY
MINVALUE
MAXVALUE
CYCLE
index_specification
PRIMARY INDEX
INDEX, secondary index definition
INDEX, secondary index definition using an ORDER BY clause
ORDER BY, Index Definition
CREATE TABLE (Queue Table Form) Examples
Example: Creating a Simple Queue Table
Example: Creating a Multiset Queue Table
Related Information
CREATE GLOBAL TEMPORARY TRACE TABLE
CREATE GLOBAL TEMPORARY TRACE TABLE Syntax
CREATE GLOBAL TEMPORARY TRACE TABLE Syntax Elements
CREATE GLOBAL TEMPORARY TRACE TABLE Examples
Related Information
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE Syntax
CREATE FOREIGN TABLE Syntax Elements
MULTISET
table_specification
table_option
external_security_clause
location_column
payload_column
data_column_definition
USING Clause
LOCATION
SCANPCT
PATHPATTERN
MANIFEST
TABLE_FORMAT ('DELTALAKE')
ROWFORMAT
STOREDAS
HEADER
STRIP_EXTERIOR_SPACES
STRIP_ENCLOSING_CHAR
NO PRIMARY INDEX
PARTITION BY
Usage Notes
External File Types
External File Object Rules
JSON External Files
CSV External Files
Parquet External Files
Foreign Table Locking
Queries on External Data are Nondeterministic
LOCATION Key Prefix Best Practices
Indexes and Foreign Tables
Table Types and Foreign Tables
Copying Foreign Tables
Load Isolation and Foreign Tables
Column Level Compression and Foreign Tables
Disable HASH BY RANDOM
Native Object Store Limitations
CREATE FOREIGN TABLE Examples
Examples: Creating Foreign Tables
JSON File
CSV File
Parquet File
Example: Creating Delta Lake Table
Example: Creating Foreign Table with PATHPATTERN
Example: Creating Foreign Table with Payload Column with Latin Characters
Example: Creating and Using an Authorization for a Foreign Table
Setting Up an Object Store for River Flow Data
Example: Copying Foreign Table without Data
Example: Copying Data from Foreign Table into Permanent Table
Example: Creating Foreign Table to Access Parquet Data
Example: Importing External Data from Foreign Table into Permanent Table
Create Foreign Table
Create View of Foreign Table
Create Permanent Table
Insert Foreign Table Rows into Permanent Table
Display Permanent Table
Example: SHOW TABLE for Foreign Table
CREATE FOREIGN TABLE Statement
Default Format
XML Format
Example: NOS Detects Columns
Example: CREATE FOREIGN TABLE Statement Specifies Columns
Example: NOS and Nondefault Field Delimiter, Header
Example: STRIP_EXTERIOR_SPACES and STRIP_ENCLOSING_CHAR
Example: Object Paths with Variable Names
Example: Object Paths without Variable Names
CREATE ERROR TABLE
CREATE ERROR TABLE Syntax
CREATE ERROR TABLE Syntax Elements
error_table_name_specification
data_table_name_specification
NO RLS
CREATE ERROR TABLE Examples
Related Information
ALTER TABLE
ALTER TABLE Syntax (Basic)
ALTER TABLE Syntax (Join Index)
ALTER TABLE Syntax (Revalidation)
ALTER TABLE Syntax (Release Rows)
ALTER TABLE Syntax Elements
ALTER TABLE Syntax Elements (Basic)
ALTER TABLE Basic Options
NORMALIZE
MODIFY PRIMARY
MODIFY NO PRIMARY
MODIFY partitioning
FROM TIME ZONE
DOWN TABLE Option
DOWN
ALTER TABLE Syntax Elements (Join Index)
ALTER TABLE Join Index Options
ADD COLUMN (column_name)
ADD ROW (column_name)
ADD SYSTEM (column_name )
ADD (column_name ) AUTO COMPRESS
ALTER TABLE Revalidation Options
REVALIDATE
WITH DELETE
WITH INSERT
ALTER TABLE Release Rows Options
Alter Options
ADD column_name
ADD COLUMN (column_name)
ADD ROW (column_name)
ADD SYSTEM (column_name )
ADD (column_name ) AUTO COMPRESS
ADD PERIOD FOR
ADD CONSTRAINT name
ADD row_level_security_constraint_name CONSTRAINT
ADD FOREIGN KEY REFERENCES
ADD CHECK (boolean_condition)
ADD UNIQUE (column_name)
ADD PRIMARY KEY (column_name)
MODIFY CHECK (boolean_condition )
RENAME
DROP PERIOD FOR
DROP column_name
DROP CONSTRAINT name
DROP row_level_security_constraint_name CONSTRAINT
DROP FOREIGN KEY REFERENCES
DROP CHECK
DROP INCONSISTENT REFERENCES
Table Options
FALLBACK
WITH JOURNAL TABLE
JOURNAL
LOG
AFTER JOURNAL
JOURNAL
ON COMMIT
CHECKSUM
DEFAULT FREESPACE
FREESPACE
MERGEBLOCKRATIO
DATABLOCKSIZE
BLOCKCOMPRESSION
WITH ISOLATED LOADING
USING FAST MODE
Column Attributes
UPPERCASE
CASESPECIFIC
FORMAT quotestring
TITLE quotestring
NAMED name
DEFAULT
WITH DEFAULT
CHARACTER SET server_character_set
NULL
AUTO COLUMN
NO COMPRESS
COMPRESS
COMPRESS USING
DECOMPRESS USING
UNIQUE
PRIMARY KEY
CHECK (boolean_ condition)
REFERENCES
Alter Partitioning
PARTITION BY
PARTITION BY COLUMN
NOT PARTITIONED
DROP RANGE
ADD RANGE
WITH DELETE
WITH INSERT
Examples
ALTER TABLE NORMALIZE Examples
ALTER TABLE MODIFY PRIMARY Examples
ALTER TABLE MODIFY NO PRIMARY Examples
ALTER TABLE MODIFY partitioning Examples
Example: Migrating Data to a New Time Zone
Example: Using SET DOWN
Example: Using RESET DOWN
ALTER TABLE Join Index Examples
ALTER TABLE RELEASE ROWS Examples
ALTER TABLE ADD column_name Examples
ALTER TABLE ADD COLUMN (column_name) Examples
ALTER TABLE ADD ROW (column_name) Examples
Example: Adding a Single-Column Partition to a Table
Example: Adding a Single-Column Partition
Example: Adding a Column to a Single-Column Partition
Example: Attempting to Add and Drop Constraints in the Same Statement
Example: Attempting to Add a Constraint with a Duplicate Name
Example: Adding a Row-Level Security Constraint
Example: Adding a FOREIGN KEY Constraint
Example: Adding or Dropping a Batch Referential Constraint
Example: Adding a Table-Level Referential Constraint
Example: Adding a NORMALIZE Constraint to a Table
Example: Adding a Foreign Key to a Column-Partitioned Table
Example: Adding a Named CHECK Constraint to a Table
Example: Adding an Unnamed CHECK Constraint
Example: Adding a Column With an Unnamed CHECK Constraint
Example: Adding a Named UNIQUE Constraint to a Table
Example: Adding a Multicolumn Unnamed UNIQUE Constraint
Example: Adding a PRIMARY KEY Constraint to a Table
Example: Modifying a Named CHECK Constraint
Example: Attempting to Modify a Nonexistent Constraint
Example: Adding a Derived Period Column to a Table
Example: Dropping the IDENTITY Attribute from a Column without Dropping the Column
Example: Dropping a Row-Level Security Constraint
Example: Dropping a Column from a Column Partition and Modifying it to Become a Two-Column Partition
Example: Dropping a Column from a Column Partition and Modifying it to become a Single-Column Partition
Example: Dropping a Column from a Column Partition Which Also Drops the Partition
Example: Dropping 2 Columns from a Column Partition Which Also Drops the Partition
Example: Dropping All But 1 Column from a Column Partition
Example: Dropping a Named Constraint From a Table
Example: Dropping a Named CHECK Constraint From a Table
Example: Dropping a UNIQUE Constraint From a Table
Example: Dropping a Named Constraint From a Table
Example: Dropping an Unnamed Column-Level CHECK Constraint
Example: Adding an Unnamed CHECK Constraint
Example: Attempting to Drop a Nonexistent Constraint
Example: Adding a Named FOREIGN KEY Constraint to a Table
Example: Dropping a FOREIGN KEY Constraint
Example: Dropping All Unnamed Table-Level CHECK Constraints
Example: Adding Fallback to a Table
Example: Adding a Single Before-Image Journal and Dual After-Image Journal to a Table
Example: Changing a Single Before-Image Journal to a Dual Before-Image Journal and Dropping Two Columns
Example: Changing CHECKSUM to the Default
Example: Changing FREESPACE
Example: Modifying MERGEBLOCKRATIO
Example: Changing DATABLOCKSIZE
Example: Adding a TITLE Phrase to a Column
Example: Adding the Null Attribute to Columns
Example: Making Composite Changes to a Column: NULL to NOT NULL
Example: Making Composite Changes to a Column: NOT NULL to NULL
Examples: Adding and Removing the JSON Auto Column Option
Example: Changing NO COMPRESS for an Existing Column
Example: Changing the Value Compression for a Compressed Column
Example: Adding a New Column With Multivalue Compression
Example: Adding and Changing Algorithmic Compression
Table Definitions for Examples
Example: Modifying Partition Ranges
Example: Non-Valid MODIFY PRIMARY INDEX Statements
Example: Changing a CASE_N-Based Row Partitioning Expression
Example: Adding a Name to a Previously Unnamed Primary Index
Example: Modifying Partitioning for Multilevel Partitioned Tables
Example: Modifying Row Partitioning for Multilevel Column-Partitioned Tables
Example: Modifying Character Partitioning for a Row-Partitioned Table
Example: Modifying Character Partitioning for a Column-Partitioned Table
Example: Dropping and Adding Partition Ranges
Example: Drop and Add Partition Ranges and Delete Rows Outside the Defined Ranges
Example: Dropping Row Range Partition When Partitioning is Defined with RANGE_N Function and NO RANGE Partition
Example: Dropping Ranges Without an EACH Clause
Example: Using MODIFY to Repartition a Table and Saving Resulting Nonvalid Rows in a Save Table
Example: Revalidating the Partitioning for a Table
Examples: USING FAST MODE
Related Information
ALTER TABLE (Map and Colocation Form)
ALTER TABLE Syntax (Map and Colocation Form)
ALTER TABLE Syntax Elements (Map and Colocation Form)
Usage Notes
Examples
ALTER FOREIGN TABLE
ALTER FOREIGN TABLE Syntax
ALTER FOREIGN TABLE Syntax Elements
Usage Notes
ALTER FOREIGN TABLE Examples
Tables for Examples
JSON
CSV
Parquet
Column-Level Alterations
Table-Level Alterations
Example: Altering LOCATION Alters PARTITION BY
Example: Altering PATHPATTERN Alters PARTITION BY
ALTER TABLE TO CURRENT
ALTER TABLE TO CURRENT Syntax
ALTER TABLE TO CURRENT Syntax Elements
Usage Notes
ALTER TABLE TO CURRENT Processing
ALTER TABLE TO CURRENT Examples
Related Information
RENAME TABLE
RENAME TABLE Syntax
RENAME TABLE Syntax Elements
new_table_name
RENAME TABLE Example
Related Information
DROP TABLE
DROP TABLE Syntax
DROP TABLE Syntax Elements
DROP TABLE Examples
Related Information
DROP ERROR TABLE
DROP ERROR TABLE Syntax
DROP ERROR TABLE Syntax Elements
Example: Dropping an Error Table
Related Information
HELP COLUMN
HELP COLUMN Syntax
HELP COLUMN Syntax Elements
Examples
Example: HELP COLUMN Sample Output
Example: HELP COLUMN column_name
Example: HELP COLUMN for All Columns
Example: HELP COLUMN for Fully Qualified Columns
Example: HELP COLUMN for a Table
Example: HELP COLUMN On Non-Column Expressions Only
Example: HELP COLUMN On Multiple table_name.* Expressions
Example: HELP COLUMN On a Mixture of Non-Column Expressions and table_name .* Expressions
Example: Partitioned Primary Index Table
Example: HELP COLUMN for a Distinct UDT Column
Example: HELP COLUMN with a Derived Period Column
Example: HELP COLUMN for Temporal Columns
Example: HELP COLUMN for a JSON Column with the Auto Column Option
Related Information
HELP CONSTRAINT
HELP CONSTRAINT Syntax
HELP CONSTRAINT Syntax Elements
HELP CONSTRAINT Examples
Related Information
HELP ERROR TABLE
HELP ERROR TABLE Syntax
HELP ERROR TABLE Syntax Elements
Usage Notes
Related Information
HELP TABLE
HELP TABLE Syntax
HELP TABLE Syntax Elements
Usage Notes
Table Columns
Examples
Example: HELP TABLE
Example: HELP TABLE with a UDT Column
Example: HELP TABLE with Temporal Columns
Example: HELP TABLE with a JSON Auto Column
Example: HELP TABLE with Foreign Table
HELP VOLATILE TABLE
HELP VOLATILE TABLE Syntax
HELP VOLATILE TABLE Syntax Elements
Usage Notes
Volatile Table Attributes
Example: HELP VOLATILE TABLE
Related Information
SHOW TABLE
SHOW TABLE Syntax
SHOW TABLE Syntax Elements
Usage Notes
Exceptions to the SHOW TABLE Table Definition
SHOW TABLE Examples
Example: SHOW TABLE
Example: SHOW TABLE with Column-Level Named Constraints
Example: SHOW TABLE with REFERENCES Constraint
Example: SHOW TABLE for a Table Created without Specifying a MERGEBLOCKRATIO Option
Example: SHOW TABLE for a User with a Default Character Type of Latin
Example: SHOW TABLE for Algorithmic Compression
Example: SHOW TABLE for a Column-Partitioned Table or Join Index
Example: SHOW TABLE in XML Format
Example: SHOW TABLE with a Derived Period Column
Example: SHOW TABLE with an Auto Column
Examples: SHOW TABLE for Foreign Table
Example: SHOW TABLE for a Complex Structure Table
View Statements
CREATE VIEW and REPLACE VIEW
CREATE VIEW and REPLACE VIEW Syntax
CREATE VIEW and REPLACE VIEW Syntax Elements
locking_clause
as_of_clause
nonrecursive_with_modifier
recursive_with_modifier
seed
union_specification
recursive
implicit_join
explicit_join
selection
source
search_condition
group_specification
having_condition
qualify_condition
WITH CHECK OPTION
order_by_specification
CREATE VIEW and REPLACE VIEW Examples
Related Information
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW Syntax
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW Syntax Elements
locking_specification
item_to_lock
date_specification
seed_statement
FROM seed_statement_source
WHERE search_condition
GROUP BY grouping_specification
HAVING having_condition
QUALIFY qualify_condition
Examples
Example: A Simple Recursive View
Example: Simple REPLACE RECURSIVE VIEW
Example: Controlling Infinite Recursion
Example: Aggregate and Ordered Analytic Function Usage
Example: Left Outer Join Usage
Example: Right Outer Join Usage
Example: Full Outer Join Usage
Example: Mutual Recursion
Example: RECURSIVE Specified for a Non-Recursive View
Example: GROUP BY Clause Usage
Related Information
RENAME VIEW
RENAME VIEW Syntax
RENAME VIEW Syntax Elements
RENAME VIEW Example
DROP VIEW
DROP VIEW Syntax
DROP VIEW Syntax Elements
Example: Dropping a View
Related Information
HELP VIEW
HELP VIEW Syntax
HELP VIEW Syntax Elements
Example: HELP VIEW for a UDT View
Index Statements
CREATE INDEX
CREATE INDEX Syntax
CREATE INDEX Syntax Elements
index_specification
UNIQUE
index_name
ALL
index_column_name
ordering_clause
loading_clause
table_specification
join_index_specification
Examples
Example: Creating Named Indexes
Example: Creating a Named Unique Secondary Index
Example: Creating a Named Nonunique Secondary Index
Example: Creating an Unnamed Unique Secondary Index
Example: Creating an Unnamed Nonunique Secondary Index
Example: Creating a Secondary Index on a Join Index
Example: Defining Secondary Indexes on UDT Columns
Examples: Creating Indexes on Load Isolated Tables
CREATE JOIN INDEX
CREATE JOIN INDEX Syntax
CREATE JOIN INDEX Syntax Elements
table_option
MAP
FALLBACK
CHECKSUM
BLOCKCOMPRESSION
select_clause
selection
aggregation_clause
SUM(numeric_expression )
COUNT(value_expression)
MIN (value_expression)
MAX(value_expression)
EXTRACT FROM
AS expression_alias
Row Compression
Repeating Column Set for Join Index Row Compression
Fixed Column Set for Join Index Row Compression
Grouping Column-Partitioned Join Index Column Data
FROM source
joined_table
WHERE search_condition
GROUP BY grouping_or_ordering_specification
ORDER BY grouping_or_ordering_specification
index
PRIMARY INDEX
PRIMARY AMP
NO PRIMARY
PARTITION BY
INDEX
Usage Notes
Examples
Example: Creating and Using a Simple Join Index
Example: Creating and Using a Simple Row-Compressed Join Index
Example: Defining and Using a Simple Join Index With an n -way Join Result
Example: Using the EXTRACT Function With a Join Index Definition
Example: Creating and Using an Aggregate Join Index
Example: Creating an Aggregate Join Index Using the MIN and MAX Functions
Example: Creating a Join Index With a Single-Level Partitioned Primary Index
Example: SLPPI Join Index and Performance
Example: Join Index Coverage
Example: Exceptions to Join Index Coverage
Example: Creating a Join Index With a Partitioned PPI Using a CURRENT_DATE Function
Example: Creating a Join Index With a Period Column in its Select List
Example: Creating a Join Index With a BEGIN Bound Function in its Select List
Example: Creating a Join Index with a Multicolumn CASE Expression in its Select List
Example: Creating a Join Index With a P_INTERSECT Expression in the Select List
Example: Creating an Aggregate Join Index With a Nested Expression in its Select List
Example: Creating a Unique Join Index
Example: Creating a Join Index Using an Updatable CURRENT_DATE Function
Example: Altering the Partitioning of a Join Index Using ALTER TABLE TO CURRENT
Example: Creating a Join Index Defined On a Period Column
Example: Creating a Join Index With a BEGIN Expression in its Select List
Example: Creating a Join Index With a Character Partitioning Expression
Example: Partitioning Expression that Specifies an AT LOCAL Date
Example: Creating Join Indexes With a UDT Column as a Primary or Secondary Index
Example: Creating a Join Index for a Table with JSON Columns
Example: Creating Column-Partitioned Join Indexes
Example: Creating Column-Partitioned Sparse Join Index With Autocompression Based on the Default
Example: Creating a Join Index with a Sparse Map
Related Information
CREATE HASH INDEX
CREATE HASH INDEX Syntax
CREATE HASH INDEX Syntax Elements
index
Usage Notes
Examples
Examples for Base Table With Unique Primary Index
Example: Hash Index With Checksum
Example: Creating a Hash Index According to Order Date
Example: Creating a Hash Index According to Customer ID
Example: Creating a Hash Index According to Order Date by Order ID
Example: Creating a Hash Index According to Order Date
Example: Creating a Hash Index on Orders by Order Date
Example: Creating a Hash Index According to Order Date
Examples for Base Table With Nonunique Primary Index
Example: Creating a Hash Index According to Shipping Date
Example: Creating a Hash Index According to Part Number
Example: Creating a Hash Index According to Ship Date
Example: Creating a Hash Index According to Ship Date by Order ID
Example: Creating a Hash Index According to Ship Date
Example: Creating a Hash Index According to Ship Date by Order Number
Example: Hash Indexes With a UDT Column in their Column Lists
Related Information
ALTER JOIN INDEX
ALTER JOIN INDEX Syntax
Usage Notes
ALTER HASH INDEX
ALTER HASH INDEX Syntax
Usage Notes
DROP INDEX
DROP INDEX Syntax
DROP INDEX Syntax Elements
index_specification
table_specification
join_index_specification
index_definition
table_specification
join_index_specification
order_clause
Examples
Example: Dropping a Named Secondary Index
Example: Dropping a Simple Unnamed Secondary Index
Example: Dropping a Composite Unnamed Secondary Index
Related Information
DROP JOIN INDEX
DROP JOIN INDEX Syntax
DROP JOIN INDEX Syntax Elements
Example: Dropping a Join Index
Related Information
DROP HASH INDEX
DROP HASH INDEX Syntax
DROP HASH INDEX Syntax Elements
Example: Dropping a Hash Index
Related Information
HELP INDEX
HELP INDEX Syntax
HELP INDEX Syntax Elements
HELP INDEX Examples
Related Information
HELP JOIN INDEX
HELP JOIN INDEX Syntax
HELP JOIN INDEX Syntax Elements
Example: HELP JOIN INDEX on a Join Index
Related Information
HELP HASH INDEX
HELP HASH INDEX Syntax
HELP HASH INDEX Syntax Elements
Example: HELP HASH INDEX
Related Information
Authorization Statements for External Routines
CREATE AUTHORIZATION and REPLACE AUTHORIZATION
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Syntax
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Syntax Elements
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Usage Notes
Assume Role Authorization
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Examples
Example: Creating a DEFINER Authorization
Example: Creating a DEFINER Authorization for an External Security Clause
Example: Creating an INVOKER Authorization by Default
Example: Creating an INVOKER Authorization Explicitly
Example: Creating a Google Cloud Authorization
Example: Creating an AUTHORIZATION OBJECT for Use with NOS
Example: Using AWS Assume Role Authorization with READ_NOS
Example: Using AWS Assume Role Authorization with WRITE_NOS
Example: Using AWS Assume Role Authorization with CREATE FOREIGN TABLE
CREATE AUTHORIZATION and REPLACE AUTHORIZATION Related Information
DROP AUTHORIZATION
DROP AUTHORIZATION Syntax
DROP AUTHORIZATION Syntax Elements
Example: Dropping an Authorization Object
Related Information
Global and Persistent (GLOP) Data Statements
CREATE GLOP SET
CREATE GLOP SET Syntax
CREATE GLOP SET Syntax Elements
Examples
Example: Creating a GLOP Set
Related Information
DROP GLOP SET
DROP GLOP SET Syntax
DROP GLOP SET Syntax Elements
Example: Dropping a GLOP Set
Related Information
Procedure Statements
CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax (External Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax Elements (External Form)
parameter_specification
language_clause
SQL_data_access
external_data_access
parameter_style_specification
GLOP_set_name
privilege_option
code_specification
JAR_ID_specification
Examples
Example: Creating a Procedure Using PARAMETER STYLE TD_GENERAL
Example: Creating a Procedure Using PARAMETER STYLE SQL
Example: Creating an External Procedure that Can Modify SQL Data Using CLIv2
Example: Creating a Java External Stored Procedure Using a Distinct UDT
Example: Creating a Java External Stored Procedure Using a Structured UDT
Example: Creating a Java External Stored Procedure Using a Period Data Type
Example: Creating a Java External Stored Procedure Using a Data Type of ST_Geometry
Example: Creating a Java External Stored Procedure Using an ARRAY or VARRAY Data Type
Example: Creating a Java External Stored Procedure Using a DATASET Data Type
Example: Creating a Procedure Using PARAMETER STYLE SQL
Example: Creating a Procedure Using PARAMETER STYLE TD_GENERAL
Example: Creating Java External SQL Procedures
Related Information
CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax (SQL Form)
CREATE PROCEDURE and REPLACE PROCEDURE Syntax Elements (SQL Form)
parameter_specification
statement
SQL_statement
SQL_multistatement_request
compound statement
open_statement
fetch_statement
assignment_statement
condition_statement
iteration_statement
diagnostic_statement
ITERATE label_name
LEAVE label_name
SQL_data_access
DYNAMIC RESULT SETS
privilege_option
local_declaration
condition_name CONDITION
cursor_declaration
PREPARE
cursor_specification
condition_handler
Usage Notes
Rules for SQL Procedure Privileges
Privilege Violations during Procedure Compilation
Privilege Violations during Procedure Execution
Examples
Example: Creating a Procedure with Parameters of Different Data Types
Example: Specifying a SQL SECURITY—CREATOR
Example: Specifying a SQL SECURITY—DEFINER
Example: Specifying SQL SECURITY—INVOKER
Example: Specifying a SQL SECURITY—OWNER
Example: Passing the Value of for a TOP n Operator to an SQL Procedure
Example: Creating a Procedure with Local Variable, Cursor, and Conditional Handler Declarations
Example: Creating SQL Procedures with Single Statement
Example: Using Nested Compound Statements in a Procedure
Example: Splitting a CLOB Value in Half and Inserting the Pieces into Two Different Tables
Example: Converting a CLOB Containing XML Data into Rows for Insertion into a Table
Example: Creating an SQL Procedure with a Multistatement Request
Example: Creating a Procedure with Multistatement Request Using a Dynamic SQL Call Statement
Example: Setting the Transaction Query Band Using a Parameter
Example: Creating a Procedure with Dynamic SQL Using the SQL PREPARE Statement
Example: Creating an SQL Procedure Using Condition and Iteration Statements
Example: Creating SQL Procedures Using UDTs
Example: Creating an SQL Procedure that Specifies Recursion
Example: Invoking an SQL UDF From an SQL Procedure
Example: Converting a CLOB Containing XML Data into Rows for Insertion into a Table
Example: Creating a Procedure with Parameters and Local Variables
Example: Creating a Procedure that Performs a Consume Mode SELECT
Example: Creating a Procedure that Consumes a Queue Table
Related Information
ALTER PROCEDURE (SQL Form)
ALTER PROCEDURE Syntax (SQL Form)
ALTER PROCEDURE Syntax Elements (SQL Form)
Examples: ALTER PROCEDURE (SQL Form)
ALTER PROCEDURE (External Form)
ALTER PROCEDURE Syntax (External Form)
ALTER PROCEDURE Syntax Elements (External Form)
procedure_name
LANGUAGE
COMPILE
AT TIME ZONE
EXECUTE PROTECTED
Usage Notes
Invocation Restrictions
ALTER PROCEDURE Examples (External Form)
RENAME PROCEDURE
RENAME PROCEDURE Syntax
RENAME PROCEDURE Syntax Elements
old_procedure_name
new_procedure_name
Example: Renaming a Procedure
Related Information
DROP PROCEDURE
DROP PROCEDURE Syntax
DROP PROCEDURE Syntax Elements
Example: Dropping a Procedure
Related Information
HELP PROCEDURE
HELP PROCEDURE Syntax
HELP PROCEDURE Syntax Elements
Examples
Example: Reporting UDT Parameters
Example: Reporting ARRAY Parameters for a Procedure
Example: HELP PROCEDURE Attributes Report
Example: HELP PROCEDURE with Unicode Pass Through Set
Related Information
Macro Statements
CREATE MACRO and REPLACE MACRO
CREATE MACRO and REPLACE MACRO Syntax
CREATE MACRO and REPLACE MACRO Syntax Elements
CREATE MACRO and REPLACE MACRO Examples
Related Information
RENAME MACRO
RENAME MACRO Syntax
RENAME MACRO Syntax Elements
RENAME MACRO Example
Related Information
DROP MACRO
DROP MACRO Syntax
DROP MACRO Syntax Elements
Related Information
HELP MACRO
HELP MACRO Syntax
HELP MACRO Syntax Elements
Example: HELP MACRO With UDTs
Example: HELP MACRO
Related Information
User-Defined Function Statements
CREATE FUNCTION and REPLACE FUNCTION (SQL Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax (SQL Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (SQL Form)
RETURN Statement
RETURN Statement Syntax
RETURN Statement Syntax Elements
Usage Notes
RETURN Statement Examples
Examples
Example: Defining an SQL UDF With a LOB Parameter
Example: Defining an SQL UDF With a Function in its RETURN Statement
Example: Defining an SQL UDF With a DateTime Expression in its RETURN Statement
Example: Defining an SQL UDF With a Period Function in its RETURN Statement
Example: Defining an SQL UDF With a Method in its RETURN Statement
Example: Defining an SQL UDF With an External UDF in its RETURN Statement
Example: Defining an SQL UDF With a Geospatial Data Type in Its RETURNS Clause and RETURN Statement
Example: Creating a Function with Geospatial Input Arguments
Example: Invoking an SQL UDF as an Argument to an External UDF
Related Information
CREATE FUNCTION and REPLACE FUNCTION (Table Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax (Table Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (Table Form)
Usage Notes
CREATE FUNCTION Examples
Example: Creating a Function from Input Arguments
Example: Creating and Using an XML Function
Example: Constant Reference Table Function
Example: Variable Reference Table Function
Example: Variable Reference Table Function Called from a Derived Table
Example: Table Function With a UDT Parameter that Returns a Column With a UDT Data Type
Example: Different LOB Specifications for Parameter and RETURNS TABLE Clauses
Example: Dynamic Result Row Specification
Example: Java Table UDF
Example: Table Operator Function Written in SAS
Example: Ordering Input Parameters to a Table Function
Example: Using a Table Function to Pass Data Between Two Vantage Systems
Related Information
CREATE FUNCTION and REPLACE FUNCTION (External Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax (External Form)
CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (External Form)
parameter_specification
return_data_type
language_clause
SQL_data_access
function_attribute
code_specification
path_specification
JAR_ID_specification
Examples
Example: Creating a UDF C Function
Example: Parsing an XML Document Using a Scalar UDF
Example: Using a UDF to Write a Message to an External Queue
Example: Retrieving Rows from the Data Dictionary and Writing Them to an External Queue on a Client System
Example: Creating and Using an EXTERNAL SECURITY Clause in a UDF
Example: Creating and Using a UDT Input Parameter Data Type in Scalar UDF Definition
Example: Creating Functions that Algorithmically Compress LOB-Related Data
Example: Creating Functions that Decompress Algorithmically Compressed LOB-Related Data
Example: Creating and Using a VARIANT_TYPE Input Parameter UDT Data Type in a UDF Definition
Example: Using the TD_ANYTYPE Parameter and RETURNS Clause Data Type in a UDF Definition
Example: Creating a Java Scalar Function
Example: Creating a Java Aggregate Function
Example: Using a One-Dimensional ARRAY in a Parameter Definition
Example: Using a Multidimensional ARRAY in a Parameter Definition
Example: Creating a Row-Level Security Function
Example: Creating a Simple Constraint UDF to Enforce No Read Up Row-Level Security
Related Information
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING Syntax
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING Syntax Elements
table_list
CREATE FUNCTION MAPPING and REPLACE FUNCTION MAPPING Examples
Example: Defining a Function Mapping
Example: Replacing a Function Mapping Definition
Example: Function Mapping Definition for a Function Within the Database
Example: Function Mapping Definition with IN TABLE, OUT TABLE, and Parameters Options
Example: Function Mapping Definition with IN TABLE and Parameter Options
Example: Function Mapping Definition that Includes a Variable with a Default Value
Example: Function Mapping Definition with Multiple Variable Substitutions
Example: Function Mapping with the ANY IN TABLE Option
Example: Function Mapping with Multiple ANY IN TABLE Clauses
Example: Function Mapping with (ANY) IN TABLE Clause Substitution
Example: Function Mapping Definition with IN TABLE Options
Example: Function Mapping Definition with IN TABLE Clause Substitution
Example: Function Mapping Definition with a Substitution in the OUT TABLE Clause
Example: Function Mapping Definition with Scalar Subquery (SSQ) Substitution
Example: Function Mapping with Scalar Subquery (SSQ) Substitution that Includes a System Variable
Example: Function Mapping with Concatenated Variable Substitution
Example: Function Mapping for WRITE_NOS
ALTER FUNCTION
ALTER FUNCTION Syntax
ALTER FUNCTION Syntax Elements
Usage Notes
Using ALTER FUNCTION or REPLACE FUNCTION with Row-Level Security UDFs
ALTER FUNCTION Examples
DROP FUNCTION
DROP FUNCTION Syntax
DROP FUNCTION Syntax Elements
Usage Notes
DROP FUNCTION Examples
Related Information
DROP FUNCTION MAPPING
DROP FUNCTION MAPPING Syntax
DROP FUNCTION MAPPING Syntax Elements
DROP FUNCTION MAPPING Example
Example: Dropping a Function Mapping
HELP FUNCTION
HELP FUNCTION Syntax
HELP FUNCTION Syntax Elements
Examples
Example: HELP FUNCTION Report
Example: HELP FUNCTION Report for a UDF
Example: Displaying UDT Parameters
Example: Displaying Multidimensional ARRAY Parameters
Example: VARIANT_TYPE Input Parameter Data Type
Example: TD_ANYTYPE Input and Output Parameter Data Type
Example: Displaying a Java UDF with Array and Period Data Types
Related Information
RENAME FUNCTION (SQL Form)
RENAME FUNCTION Syntax (SQL Form)
RENAME FUNCTION Syntax Elements (SQL Form)
RENAME FUNCTION Examples (SQL Form)
Related Information
RENAME FUNCTION (External Form)
RENAME FUNCTION Syntax (External Form)
RENAME FUNCTION Syntax Elements (External Form)
Example 1: Renaming the Specific Name for a Function
Example 2: Renaming the Overloaded Calling Name for a Function
Related Information
SHOW FUNCTION MAPPING
SHOW FUNCTION MAPPING Syntax
SHOW FUNCTION MAPPING Syntax Elements
SHOW FUNCTION MAPPING Examples
Example: Showing a Function Mapping Definition
Example: Showing the Definition of a Function Mapping in XML Format
User-Defined Method Statements
CREATE METHOD
CREATE METHOD Syntax
CREATE METHOD Syntax Elements
locator_specification
item_list
CREATE METHOD Examples
Example: Creating a Constructor Method for a UDT
Example: Creating a Method Named in_state for a UDT Named address
Example: Creating a Method Named timezone for a UDT Named address
Example: Creating an Instance Method Using a One-Dimensional ARRAY Type and an SQL Parameter Type
Example: Creating an Instance Method Using a One-Dimensional ARRAY Type and a TD_GENERAL Parameter Type
Example: Creating an Instance Method Using a Multidimensional ARRAY Type and an SQL Parameter Type
Example: Creating an Instance Method Using a Multidimensional ARRAY Type and a TD_GENERAL Parameter Type
Example: Methods That Return a TD_ANYTYPE or INTEGER Data Type
Example: Creating an Instance Method for a UDT Stored on the Client
Related Information
ALTER METHOD
ALTER METHOD Syntax
ALTER METHOD Syntax Elements
specific_method_clause
method_clause
ALTER METHOD Examples
REPLACE METHOD
REPLACE METHOD Syntax
REPLACE METHOD Syntax Elements
specific_method_clause
method_clause
item_list
S_or_C_item
Usage Notes
How REPLACE METHOD And CREATE METHOD Differ
Example: Replacing a Method Definition Using Its Specific Method Name
Related Information
HELP METHOD
HELP METHOD Syntax
HELP METHOD Syntax Elements
HELP METHOD Examples
Example: HELP METHOD with Sample Output
Example: TD_ANYTYPE Input Parameter Data Type
Example: HELP METHOD
Related Information
User-Defined Type Statements
CREATE TYPE (Structured Form)
CREATE TYPE Syntax (Structured Form)
CREATE TYPE Syntax Elements (Structured Form)
attribute_specification
method_specification
parameter_specification
returns_parameter_specification
type_attribute
language_clause
SQL_data_access
Examples
Example: Creating a Structured UDT with 2 Attributes
Example: Creating a Structured UDT from Predefined Data Types
Related Information
CREATE TYPE (Distinct Form)
CREATE TYPE Syntax (Distinct Form)
CREATE TYPE Syntax Elements (Distinct Form)
method_specification
parameter_specification
returns_parameter_specification
type_attribute
language_clause
SQL_data_access
CREATE TYPE Examples (Distinct Form)
Related Information
CREATE TYPE (ARRAY/VARRAY Form)
CREATE TYPE Syntax (ARRAY/VARRAY Form)
CREATE TYPE Syntax Elements (ARRAY/VARRAY Form)
One-Dimensional ARRAY - Teradata Form
One-Dimensional VARRAY - Oracle-Compatible Form
Multidimensional Array - Teradata Form
Multidimensional Array - Oracle-Compatible Form
Examples
Example: One-Dimensional ARRAY Type
Example: UDT and a One-Dimensional ARRAY Type
Example: Three-Dimensional ARRAY Type
Example: Three-Dimensional ARRAY Type Using Different Syntax
Example: Three-Dimensional VARRAY Type
Example: Three-Dimensional ARRAY and VARRAY Types With a Negative Lower Bound on a Dimension
Related Information
CREATE storage_format SCHEMA
CREATE storage_format SCHEMA Syntax
CREATE storage_format SCHEMA Syntax Elements
CREATE storage_format SCHEMA Examples
Example: Schema for DATASET Type with Avro Storage Format
Example: Schema for DATASET Type with CSV Storage Format
Usage Notes
Schemas are Converted to UTF-8
CREATE CAST and REPLACE CAST
CREATE CAST and REPLACE CAST Syntax
CREATE CAST and REPLACE CAST Syntax Elements
source_predefined_data_type
target_predefined_data_type
specific_method
METHOD
specific_function
FUNCTION
AS ASSIGNMENT
CREATE CAST and REPLACE CAST Examples
Related Information
CREATE ORDERING and REPLACE ORDERING
CREATE ORDERING and REPLACE ORDERING Syntax
CREATE ORDERING and REPLACE ORDERING Syntax Elements
UDT_name
method_specification
function_specification
CREATE ORDERING and REPLACE ORDERING Examples
Example: Order Mapping for a UDT using a UDF
Example: Order Mapping for a UDT using a Method
Example: Order Mapping for a UDT using a Method
Related Information
CREATE TRANSFORM and REPLACE TRANSFORM
CREATE TRANSFORM and REPLACE TRANSFORM Syntax
CREATE TRANSFORM and REPLACE TRANSFORM Syntax Elements
transform_specification
to_method_or_function
from_method_or_function
TO SQL WITH SPECIFIC METHOD specific_method_name
TO SQL WITH METHOD method_name
TO SQL WITH SPECIFIC FUNCTION specific_function_name
TO SQL WITH FUNCTION function_name
FROM SQL WITH SPECIFIC specific_method_name
FROM SQL WITH METHOD method_name
FROM SQL WITH SPECIFIC FUNCTION specific_function_name
FROM SQL WITH FUNCTION function_name
CREATE TRANSFORM and REPLACE TRANSFORM Examples
Related Information
SET TRANSFORM GROUP FOR TYPE
SET TRANSFORM GROUP FOR TYPE Syntax
SET TRANSFORM GROUP FOR TYPE Syntax Elements
SET TRANSFORM GROUP FOR TYPE Examples
Examples: Setting Transform Groups for UDTs
Usage Notes
JSON and XML in MultipartRecord Mode
ALTER TYPE
ALTER TYPE Syntax
ALTER TYPE Syntax Elements
UDT_name
ADD ATTRIBUTE attribute_name
DROP ATTRIBUTE attribute_name
ADD METHOD
ADD SPECIFIC METHOD
DROP INSTANCE METHOD
DROP CONSTRUCTOR METHOD
DROP SPECIFIC METHOD SYSUDTLIB.specific_method_name
Examples
Example: Using the ALTER TYPE Statement with the COMPILE Option
Example: Using the ALTER TYPE Statement with the COMPILE ONLY Option
Example: Adding an Attribute to a Structured UDT
Example: Adding Multiple Attributes to a Structured UDT
Example: Adding Multiple Instance Methods to a Structured UDT
Example: Adding an Instance Method to a Structured UDT
Example: Dropping an Attribute From a Structured UDT
Example: Dropping a Method From a Structured UDT Using Its Specific Method Name
Related Information
DROP TYPE
DROP TYPE Syntax
DROP TYPE Syntax Elements
DROP TYPE Examples
Related Information
DROP storage_format SCHEMA
DROP storage_format SCHEMA Syntax
DROP storage_format SCHEMA Syntax Elements
DROP storage_format SCHEMA Example
DROP CAST
DROP CAST Syntax
DROP CAST Syntax Elements
DROP CAST Examples
Related Information
DROP ORDERING
DROP ORDERING Syntax
DROP ORDERING Syntax Elements
DROP ORDERING Examples
Related Information
DROP TRANSFORM
DROP TRANSFORM Syntax
DROP TRANSFORM Syntax Elements
DROP TRANSFORM Examples
Related Information
HELP TYPE
HELP TYPE Syntax
HELP TYPE Syntax Elements
Usage Notes
HELP COLUMN Report on a Unicode View Differs From a Compatibility View
HELP TYPE Examples
Example: HELP TYPE with No Options Specified
Example: HELP TYPE for a Distinct UDT Type
Example: HELP TYPE for a UDT with the ATTRIBUTE Option
Example: HELP TYPE with METHOD Option
Example: HELP TYPE for a One-Dimensional ARRAY
Example: HELP TYPE for an ARRAY with the ATTRIBUTE Option
Example: HELP TYPE with No Options for a One-Dimensional Array
Related Information
HELP storage_format SCHEMA
HELP storage_format SCHEMA Syntax
HELP storage_format SCHEMA Syntax Elements
HELP storage_format SCHEMA Example
HELP CAST
HELP CAST Syntax
HELP CAST Syntax Elements
HELP CAST Examples
Related Information
HELP TRANSFORM
HELP TRANSFORM Syntax
HELP TRANSFORM Syntax Elements
Examples
Example: HELP TRANSFORM for a UDT
Related Information
Database Statements
CREATE DATABASE
CREATE DATABASE Syntax
CREATE DATABASE Syntax Elements
name
FROM database_name
PERMANENT
SPOOL
TEMPORARY
ACCOUNT = ‘account_string’
DEFAULT MAP
FALLBACK PROTECTION
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
Usage Notes
Journal Tables and the Default Map
CREATE DATABASE Examples
Example: Creating a New Database
Example: Using a Constant Expression to Specify the PERM, SPOOL, and TEMPORARY Space for a Database
Example: Creating a Database with a Permanent Space Skew Limit
Example: Creating a Database with a Spool Space Skew Limit
Example: Creating a Database with a Temporary Space Skew Limit
Example: Creating a Database with Fallback and Journaling
DELETE DATABASE
DELETE DATABASE Syntax
DELETE DATABASE Syntax Elements
Example: Deleting a Database
Related Information
MODIFY DATABASE
MODIFY DATABASE Syntax
MODIFY DATABASE Syntax Elements
database_name
AS
PERMANENT
TEMPORARY
SPOOL
ACCOUNT
DEFAULT MAP
FALLBACK
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
Usage Notes
Journal Tables and the Default Map
MODIFY DATABASE Examples
Example: Change Permanent Space Allocation
Example: Use Constant Expression to Modify PERM, SPOOL, and TEMPORARY Database Spaces
Example: Add Permanent Space Skew Limit to Database
Example: Add Temporary Space Skew Limit to Database
Example: Add Spool Space Skew Limit to Database
Example: Change Fallback Protection and Space Allocation
Example: Change Default Journal Table
Example: Drop Default Journal Table
Example: Define New Default Journal Table
Related Information
DATABASE
DATABASE Syntax
DATABASE Syntax Elements
Example: Defining Default Database for Current Session
Related Information
DROP DATABASE
DROP DATABASE Syntax
DROP DATABASE Syntax Elements
Usage Notes
Dropping a Populated Database
Example: Dropping Empty Database
Related Information
HELP DATABASE
HELP DATABASE Syntax
HELP DATABASE Syntax Elements
HELP DATABASE Examples
Example: HELP DATABASE
Example: HELP DATABASE Output Showing a UDT and a Method
Example: Output with a UIF
Related Information
LOGGING INCREMENTAL ARCHIVE ON FOR object_list
LOGGING INCREMENTAL ARCHIVE ON FOR object_list Syntax
LOGGING INCREMENTAL ARCHIVE ON FOR object_list Syntax Elements
LOGGING INCREMENTAL ARCHIVE ON FOR object_list Examples
Examples: Enable Incremental Restore
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list Syntax
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list Syntax Elements
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list Examples
Examples: Disabling Incremental Restore
INCREMENTAL RESTORE ALLOW WRITE FOR object_list
INCREMENTAL RESTORE ALLOW WRITE FOR object_list Syntax
INCREMENTAL RESTORE ALLOW WRITE FOR object_list Syntax Elements
INCREMENTAL RESTORE ALLOW WRITE FOR object_list Examples
Examples: Enable Read and Write Access After an Incremental Restore
User, Profile, and Role Statements
CREATE PROFILE
CREATE PROFILE Syntax
CREATE PROFILE Syntax Elements
profile_name
ACCOUNT
DEFAULT MAP
DEFAULT DATABASE = database_name
SPOOL
TEMPORARY
PASSWORD
QUERY_BAND
IGNORE QUERY_BAND VALUES
TRANSFORM
COST PROFILE = cost_profile_name
CONSTRAINT
Usage Notes
Effects of Profile-Based Password Controls
Working with the SPECCHAR Password Control
Specifying the Single-Letter Code to Define SpecChar Rules
Default Value
Effects of Object Naming on SPECCHAR Controls
CREATE PROFILE Examples
Example: Creating Profile that Defines Default Database
Example: Using Constant Expression to Specify SPOOL Space for Profile
Example: Using Constant Expression to Specify TEMPORARY Space for Profile
Example: Creating Profile that Defines User-Level Password Security Attribute
Example: Set Profile Query Band Pairs as Default
Example: Profile Query Band Pairs Are Not Default
Example: Creating Profile to Set Default Query Band Value and Query Band Value to Ignore
Example: Creating Profile with System Default Query Band Value and Query Band Value to Ignore
Example: Creating Profile that Ignores pair-name with Any pair-value
Example: Adding UDT Transform Groups to Profile
Example: Assigning Row-Level Security Constraint Classifications to Profile
Related Information
MODIFY PROFILE
MODIFY PROFILE Syntax
MODIFY PROFILE Syntax Elements
profile_name
ACCOUNT
DEFAULT MAP
DEFAULT DATABASE
SPOOL
TEMPORARY
PASSWORD
QUERY_BAND
IGNORE QUERY_BAND VALUES
TRANSFORM
COST PROFILE
CONSTRAINT
MODIFY PROFILE Examples
Example: Modify profile spool space
Example: Using a Constant Expression to Specify the SPOOL Space for a Profile
Example: Using Constant Expression to Specify TEMPORARY Space for Profile
Examples: QUERY_BAND
Example: Modifying Profile to Set Default Query Band Value and Query Band Value to Ignore
Example: Adding UDT Transform Groups to Profile
Example: Removing UDT Transform Groups from Profile
Related Information
CREATE ROLE
CREATE ROLE Syntax
CREATE ROLE Syntax Elements
Example: Creating an External Role
Related Information
CREATE USER
CREATE USER Syntax
CREATE USER Syntax Elements
user_name
AS
PERMANENT
PASSWORD
STARTUP
TEMPORARY
SPOOL
DEFAULT DATABASE
COLLATION
ACCOUNT
DEFAULT MAP
FALLBACK
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
TIME ZONE
DATEFORM
DEFAULT CHARACTER SET
DEFAULT ROLE
PROFILE
TRANSFORM
DBA
CONSTRAINT
EXPORTWIDTH
Usage Notes
Privileges Received by the Creator
Privileges Granted Automatically to a Created User
Privileges Not Granted Automatically
Collation Option Usage
MULTINATIONAL Collation
Valid Export Width Specifications
Compatibility Mode
Expected Mode
Maximum Mode
DEFAULT Mode
Effects of Shift Out and Shift In Characters on Export Widths
Journal Tables and the Default Map
CREATE USER Examples
Example: Creating an Administrative User
Example: Using Constant Expression to Specify PERM, SPOOL, and TEMPORARY Space for User
Example: Creating User with Permanent Space Skew Limit
Example: Creating User with Temporary Space Skew Limit
Example: Creating User with Spool Space Skew Limit
Example: Creating User Using TIME ZONE=’time_zone_string’
Example: Create User that Includes UDT Transforms
Example: Creating Users With Row-Level Security Constraints
Example: Creating New User With EXPECTED Export Width Table Name
Related Information
MODIFY USER
MODIFY USER Syntax
MODIFY USER Syntax Elements
user_name
PERMANENT
STARTUP
PASSWORD
RELEASE PASSWORD LOCK
TEMPORARY
SPOOL
ACCOUNT
DEFAULT MAP
DEFAULT DATABASE
COLLATION
FALLBACK
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
DROP DEFAULT JOURNAL TABLE
TIME ZONE
DATEFORM
DEFAULT CHARACTER SET
DEFAULT ROLE
PROFILE
TRANSFORM
DBA
EXPORTWIDTH
CONSTRAINT
Usage Notes
Valid Export Width Specifications
Compatibility Mode
Expected Mode
Maximum Mode
DEFAULT Mode
Effects of Shift Out and Shift In Characters on Export Widths
Journal Tables and the Default Map
MODIFY USER Examples
Example: Modifying Permanent Space Allocation for User
Example: Modifying PERM, SPOOL, and TEMPORARY Space Using Constant Expression
Example: Modifying User to Add Permanent Space Skew Limit
Example: Modifying Password, Spool Allocation, Startup String, and Default Database
Example: Modifying User to Add Temporary Space Skew Limit
Example: Modifying User to Add Spool Space Skew Limit
Example: Modifying Default Database and Password for User
Example: Modifying Default Journal Table for User
Example: Changing Default Journal Table
Example: Modifying Time Zone Displacement with TIME ZONE LOCAL Option
Example: Modifying Time Zone Displacement with TIME ZONE time_zone_string Option
Example: Assigning Profile to User with PROFILE Option
Example: Adding UDT Transform Groups to User
Example: Removing UDT Transform Groups from User
Example: Adding or Dropping Security Constraint Assignments in MODIFY USER Statement
Related Information
SET ROLE
SET ROLE Syntax
SET ROLE Syntax Elements
SET ROLE Examples
Related Information
DELETE USER
DELETE USER Syntax
DELETE USER Syntax Elements
Example: Deleting Database Objects from User
Related Information
DROP PROFILE
DROP PROFILE Syntax
Usage Notes
Effects of Dropping a Profile
Example: Dropping Profile
Related Information
DROP ROLE
DROP ROLE Syntax
DROP ROLE Syntax Elements
DROP ROLE Examples
Related Information
DROP USER
DROP USER Syntax
DROP USER Syntax Elements
Usage Notes
Effects of Dropping a User on Database Privileges
Effects of Dropping a User on User-Created Roles and Profiles
DROP USER Examples
Related Information
HELP USER
HELP USER Syntax
HELP USER Syntax Elements
HELP USER Example
Related Information
Map Statements
CREATE MAP
CREATE MAP Syntax
CREATE MAP Syntax Elements
Usage Notes
CREATE MAP Example
DROP MAP
DROP MAP Syntax
DROP MAP Syntax Elements
DROP MAP Example
SHOW MAP
SHOW MAP Syntax
SHOW MAP Syntax Elements
SHOW MAP Examples
Load Isolation Statements
BEGIN ISOLATED LOADING
BEGIN ISOLATED LOADING Syntax
BEGIN ISOLATED LOADING Syntax Elements
Usage Notes
BEGIN ISOLATED LOADING Examples
LDILoadGroup Query Band
LDILoadGroup Query Band Examples
CHECKPOINT ISOLATED LOADING
CHECKPOINT ISOLATED LOADING Syntax
CHECKPOINT ISOLATED LOADING Syntax Elements
Usage Notes
CHECKPOINT ISOLATED LOADING Example
END ISOLATED LOADING
END ISOLATED LOADING Syntax
END ISOLATED LOADING Syntax Elements
Usage Notes
END ISOLATED LOADING Example
Secure Zones Statements
CREATE ZONE
CREATE ZONE Syntax
CREATE ZONE Syntax Elements
CREATE ZONE Examples
ALTER ZONE
ALTER ZONE Syntax
ALTER ZONE Syntax Elements
ALTER ZONE Examples
DROP ZONE
DROP ZONE Syntax
DROP ZONE Syntax Elements
DROP ZONE Example
Session Statements
SET SESSION
SET SESSION Syntax
Usage Notes
Using SET SESSION Statements with Connection Pooling
SET SESSION ACCOUNT
SET SESSION ACCOUNT Syntax
SET SESSION ACCOUNT Syntax Elements
Example: Setting the Account to Change Resource Charges for a Session
Example: Setting the Account to Change the Priority for a Request
Related Information
SET SESSION CALENDAR
SET SESSION CALENDAR Syntax
SET SESSION CALENDAR Syntax Elements
Example: Changing the Default Session Calendar Using SET SESSION CALENDAR
Example: Changing the Default Session Calendar Using CREATE USER
Related Information
SET SESSION CHARACTER SET UNICODE PASS THROUGH
SET SESSION CHARACTER SET UNICODE PASS THROUGH Syntax
SET SESSION CHARACTER SET UNICODE PASS THROUGH Syntax Elements
Example: Enabling Unicode Pass Through for the Session
SET SESSION COLLATION
SET SESSION COLLATION Syntax
SET SESSION COLLATION Syntax Elements
ASCII
CHARSET_COLL
EBCDIC
HOST
JIS_COLL
MULTINATIONAL Collation
Usage Notes
SET SESSION COLLATION Examples
Example: Setting a Session to Use the ASCII Collation Sequence
Example: Effect of the ASCII Collation Sequence on SQL Report Output
Example: Setting a Session to Use the EBCDIC Collation Sequence
Example: Effect of the EBCDIC Collation Sequence on SQL Report Output
Related Information
SET SESSION CONSTRAINT
SET SESSION CONSTRAINT Syntax
SET SESSION CONSTRAINT Syntax Elements
Usage Notes
Rules for Determining Whether Profile Defaults or User Defaults Are Assigned as Session Row-Level Security Defaults
Using SET SESSION CONSTRAINT To Modify the Constraints Assigned to You for the Current Session
Using the NULL Option for SET SESSION CONSTRAINT
Determining the Session Constraint Values
Session Constraint Values for Permanent Database Users
Session Constraint Values for Directory Users
Session Constraint Values for Application Pooled Users
Session Constraint Values for Trusted User Applications and Proxy Users
Session Constraint Values in OVERRIDE Sessions
Specifying Non-Hierarchical Constraint Values when Loading Tables
Using SET SESSION to Change the Session Security Constraint Value
Using HELP SESSION to Investigate Session Constraint Values
SET SESSION CONSTRAINT Examples
Constraint Definitions for the Examples
Example: Changing the Security Level and Category for a Session
Example: Changing the Row-Level Security Level for a Session
Example: Changing the Row-Level Security Category for a Session
Example: Loading Tables without User OVERRIDE Privileges
Example: Loading Tables with User OVERRIDE Privileges
Related Information
SET SESSION DATABASE
SET SESSION DATABASE Syntax
SET SESSION DATABASE Syntax Elements
Usage Notes
Session Database and Called Procedures
Example
SET SESSION DATEFORM
SET SESSION DATEFORM Syntax
SET SESSION DATEFORM Syntax Elements
Usage Notes
Session Dateform and Called Procedures
SET SESSION DATEFORM Examples
SET SESSION DEBUG FUNCTION
SET SESSION DEBUG FUNCTION Syntax
SET SESSION DEBUG FUNCTION Syntax Elements
Usage Notes
SET SESSION DEBUG FUNCTION Examples
SET SESSION DOT NOTATION
SET SESSION DOT NOTATION Syntax
SET SESSION DOT NOTATION Syntax Elements
Example: Default Response for 15.0 and 15.10 Dot Notation
SET SESSION FOR ISOLATED LOADING
SET SESSION FOR ISOLATED LOADING Syntax
SET SESSION FOR ISOLATED LOADING Syntax Elements
SET SESSION FOR ISOLATED LOADING Examples
SET SESSION FUNCTION TRACE
SET SESSION FUNCTION TRACE Syntax
SET SESSION FUNCTION TRACE Syntax Elements
SET SESSION FUNCTION TRACE Example
Related Information
SET SESSION JSON IGNORE ERRORS
SET SESSION JSON IGNORE ERRORS Syntax
SET SESSION JSON IGNORE ERRORS Syntax Elements
Example: Disabling JSON Data Validation
Related Information
SET SESSION SEARCHUIFDBPATH
SET SESSION SEARCHUIFDBPATH Syntax
SET SESSION SEARCHUIFDBPATH Syntax Elements
Example: Setting the Database UIF Search Path
SET SESSION TRANSACTION ISOLATION LEVEL
SET SESSION TRANSACTION ISOLATION LEVEL Syntax
SET SESSION TRANSACTION ISOLATION LEVEL Syntax Elements
SET SESSION TRANSACTION ISOLATION LEVEL Examples
Related Information
SET QUERY_BAND
SET QUERY_BAND Syntax
SET QUERY_BAND Syntax Elements
pair_name
pair_value
NONE
UPDATE
FOR SESSION
FOR TRANSACTION
Usage Notes
Query Banding and Middle Tier Applications
Query Banding for Pooled Sessions
Query Banding for Trusted Sessions
Preventing Unauthorized Use of Query Banding by Proxy Users
Creator Status for Objects Created in a Trusted Session
Query Bands and Row-Level Security Constraints
SET QUERY_BAND FOR SESSION, Proxy Users, and the Deletion of Volatile and Global Temporary Tables
SET QUERY_BAND Examples
Example: Setting a Query Band For a Session
Example: Making a Proxy User Connection
Example: Setting the Role for a Trusted Session Using a PROXYROLE name:value Pair
Example: Using the BLOCKCOMPRESSION Reserved Query Band
Example: Setting TVSTEMPERATURE Query Bands to VERYHOT
Example: Setting TVSTEMPERATURE Query Bands
Example: Setting BLOCKCOMPRESSION and TVSTEMPERATURE Query Bands
Example: Setting a Query Band That Specifies the EQUALS SIGN Character as Part of the Value
Example: Removing a Query Band From a Session
Example: Query Band UPDATE Examples
Example: Setting a Query Band Using FOR SESSION VOLATILE
Example: Using UPDATE With a FOR SESSION VOLATILE Query Band
Example: Setting a Query Band for the Current Transaction
Example: Removing a Query Band From the Current Transaction
Example: SET QUERY_BAND FOR TRANSACTION As Parameterized JDBC Request
Example: Preventing an Injected SET QUERY_BAND from Changing the Proxy User
Example: Initiating a Trusted Session Using a Transaction Query Band
Example: Setting a Query Band for the Current Transaction for a Multistatement Request in a Trusted Session
Related Information
SET TIME ZONE
SET TIME ZONE Syntax
SET TIME ZONE Syntax Elements
SET TIME ZONE Examples
SET SESSION UDFSEARCHPATH
SET SESSION UDFSEARCHPATH Syntax
SET SESSION UDFSEARCHPATH Syntax Elements
Example: SET SESSION UDFSEARCHPATH
HELP SESSION
HELP SESSION Syntax
HELP SESSION Syntax Elements
Usage Notes
Examples
Example: HELP SESSION for the Current User
Example: HELP SESSION for a KanjiEBCDIC User
Example: HELP SESSION for Session Attributes
Example: Row-Level Security Constraints Only
Example: Additional Object Name and Title Fields Returned with HELP SESSION
Example: Show SEARCHUIFDBPATH
Example: HELP SESSION with Unicode Pass Through Set
Related Information
Logging Statements
BEGIN LOGGING
BEGIN LOGGING Syntax
BEGIN LOGGING Syntax Elements
DENIALS
WITH TEXT
logging_frequency
FOR CONSTRAINT constraint_name
ALL
operation
GRANT
BY user_name
ON item_list
Usage Notes
Logging Session Entries
Logging of External Users
Determining Object Level for BEGIN LOGGING Request
Requirements for Logging Certain Operations and Objects in Combination
Logging MODIFY Statements
Logging Self-Referent MODIFY USER Statements
Examples
Example: Using BEGIN LOGGING
Example: Logging UDTs
Example: Logging UDTs with SQL Text
Example: Logging on First UDT Method with SQL Text
Example: Logging UDTs with Insufficient Privileges in SYSUDTLIB
Example: Logging UDTs with Insufficient Privileges on a UDT
Example: Column-Level Discretionary Access Control and Row-Level Security Audit Logging
Example: Logging Denials for a Row-Level Security Constraint
Example: Logging the Denied First Insert Into a Row-Level Security-Secured Table
Example: Logging the Denial of an Attempt to Archive a Row-Level Security-Secured Table by a User Not Having Archive Access to the Table
Related Information
BEGIN QUERY CAPTURE
BEGIN QUERY CAPTURE Syntax
BEGIN QUERY CAPTURE Syntax Elements
Example: Using the Default TDQCD Database to Capture and Store a Query Plan in XML Format
Example: Capturing a Mix of DML and DDL Requests in XML Format Using BEGIN QUERY CAPTURE
Example: Using the Default TDQCD Database to Capture and Store a Query Plan in XML Format
Example: Capturing a Mix of DML and DDL Requests in XML Format Using BEGIN QUERY CAPTURE
Related Information
BEGIN QUERY LOGGING
BEGIN QUERY LOGGING Syntax
BEGIN QUERY LOGGING Syntax Elements
with_item
m
limit_item
on_items
Examples
Example: Logging All Users for All Accounts
Example: Logging Up To 1,000 SQL Text Characters in DBQLogTbl
Example: Logging Specific Users
Example: Handling Short Queries
Example: Logging All Users Who Are Logged On Under Specific Accounts
Example: Logging Requests by Summary Completion Time in Full Second Intervals
Example: Logging Requests by Summary Completion Time in Subsecond Intervals
Example: Short Requests Based on CPU Time
Example: Short Requests Based on I/O Counts
Example: Logging Requests by Threshold Normalized CPU Times
Example: Logging Requests on All Users for the Default Table
Example: Disabling Query Logging for MultiLoad Jobs
Example: Logging Requests Except for a Single User and Account
Example: Invoking and Disabling Query Logging for a Rule Set
Example: Fine Tuning Query Logging Rules
Example: Logging Plans as XML Text
Example: Logging Plans as XML Text for Detailed Statistics
Example: Logging Verbose EXPLAIN as XML Text
Example: Logging Statistics Usage for All Requests by a User
Example: Logging an XML Plan with VERBOSE EXPLAIN and DETAILED STATSUSAGE Collections
Example: Logging an XML Plan with VERBOSE EXPLAIN and DETAILED STATSUSAGE Collections in the Same Request
Example: Logging Object Use Counts on a Specified Database
Example: Logging Object Use Counts on a Mix of Databases and Users
Related Information
FLUSH QUERY LOGGING
FLUSH QUERY LOGGING Syntax
FLUSH QUERY LOGGING Syntax Elements
FLUSH QUERY LOGGING Examples
Related Information
REPLACE QUERY LOGGING
REPLACE QUERY LOGGING Syntax
REPLACE QUERY LOGGING Syntax Elements
on_items
Related Information
END LOGGING
END LOGGING Syntax
END LOGGING Syntax Elements
DENIALS
WITH TEXT
operation_specification
constraint_name
user_name
logged_item
END LOGGING Examples
Related Information
END QUERY CAPTURE
END QUERY CAPTURE Syntax
Usage Notes
Rules and Restrictions for END QUERY CAPTURE
Related Information
END QUERY LOGGING
END QUERY LOGGING Syntax
END QUERY LOGGING Syntax Elements
END QUERY LOGGING Examples
Example: Removing the ALL rule from the rule cache and DBC.DBQLRulesTbl
Example: Remove all DBQL rules in effect from DBC.DBQLRuleTbl
Example: Specify an account explicitly to discontinue request logging
Example: Removing the rule for user_1 from the rule cache and DBC.DBQLRulesTbl
Example: Removing the rule for user_1 and user_2 from the rule cache and DBC.DBQLRulesTbl
Related Information
SHOW QUERY LOGGING
SHOW QUERY LOGGING Syntax
SHOW QUERY LOGGING Syntax Elements
Usage Notes
Alternative Method for Listing Query Logging Rules
Rules Hierarchy for SHOW QUERY LOGGING
Examples
Example: SHOW QUERY LOGGING for Simple Conditions
Example: Report a DBQL Rule That Logs All Users for Any Account String
Example: Report a DBQL Rule That Logs All Users for a Specific Account
Example: Report a DBQL Rule That Logs a Specific User for a Specific Account
Example: Report a DBQL Rule That Logs a Specific User for a Specific Account
Example: Report a DBQL Rule That Logs a Specific User for All Accounts
Example: Report the DBQL Logging Rule for the MultiLoad Utility
Example: Searching Multiple Levels of the DBQL Rules Hierarchy
Example: Report a DBQL Rule That Logs All Users for All Accounts Using Default Logging
Example: Report a DBQL Rule That Logs a Specific User for All Accounts
Example: Report a DBQL Rule That Logs a Specific User for a Specific Account
Example: Report a DBQL Rule That Logs All Users for a Specific Account
Example: Report a DBQL Rule That Logs a Specific User for a Specific Account
Example: Report a DBQL Rule That Logs a Specific User for a Specific Account
Example: Report a DBQL Rule That Logs a Specific User for Any Account
Example: Report a DBQL Rule That Logs All Queries for the MultiLoad Utility
Example: Report a DBQL Rule That Shows SQL, StepInfo, and FeatureInfo Options Enabled
Examples: Report Detailed Logging Data
Example: Report Options for a User when System-Level Logging is Enabled and User-Level Rule is in Place
Statistics Statements
COLLECT STATISTICS (Optimizer Form)
COLLECT STATISTICS Syntax (Optimizer Form)
COLLECT STATISTICS Syntax Elements (Optimizer Form)
SUMMARY
using_option
index_specification
column_specification
collection_source
from_option
Examples
Example: Collecting Full Statistics
Example: Collecting Sampled Statistics
Example: Collecting Sampled Statistics
Example: Collecting SUMMARY Statistics
Example: Recollecting Statistics Without Specifying Thresholds
Example: A Simple Recollect Statistics Request Using the THRESHOLD Option
Example: Using the THRESHOLD Option to Recollect Statistics
Example: Collecting Statistics on Multiple Columns
Example: Collecting Single-Column PARTITION Statistics
Example: Collecting Multicolumn PARTITION Statistics
Example: Collecting Statistics on a Single-Column with an Expression
Example: Collecting Statistics on Multiple Columns with an Expression
Example: Collecting Statistics on a Constant Expression
Example: Collecting Statistics on Expressions Using Statistics Names
Example: Collecting Statistics on Built-In Function Expressions
Example: Collecting Statistics on a Geospatial Column
Example: Collecting Statistics on a Geospatial Index
Example: Collecting Statistics on Named Indexes
Example: Collecting Statistics on an Unnamed Indexes
Example: Copying Statistics From a Source Table to an Identical Target Table
Example: Collecting Sampled Statistics with USING Options
Example: Recollecting Statistics When Statistics Were First Collected Specifying a USING Clause
Example: Overriding USING Options With the FOR CURRENT Option
Example: Collecting Statistics on a Foreign Table Column
Related Information
SHOW STATISTICS
SHOW STATISTICS Syntax
SHOW STATISTICS Syntax Elements
Usage Notes
Response Sequences for Detailed Statistics (Single Record and Indicator Modes)
Response Sequences for Detailed Statistics (Multiple Records and Indicator Modes)
Rules and Guidelines for SHOW STATISTICS Requests
Examples
Example: SHOW STATISTICS for COLLECT STATISTICS SQL Text Only
Example: SHOW STATISTICS SEQUENCED Option
Example: SHOW STATISTICS with a VALUES Clause for a Table With Statistics Collected on a Geospatial Column
Example: SHOW SUMMARY STATISTICS with VALUES Clause
Example: Reporting Detailed QCD Statistics
Example: SHOW STATISTICS with a VALUES Clause for a Table With Statistics Collected on a Geospatial Column
Related Information
DROP STATISTICS (Optimizer Form)
DROP STATISTICS Syntax (Optimizer Form)
DROP STATISTICS Syntax Elements (Optimizer Form)
column_index
DROP STATISTICS Examples (Optimizer Form)
Related Information
HELP STATISTICS (Optimizer Form)
HELP STATISTICS Syntax (Optimizer Form)
HELP STATISTICS Syntax Elements (Optimizer Form)
Usage Notes
How To Report Summary Statistics and Detailed Statistics
Examples
Example: Summary Statistics on a Database
Example: HELP STATISTICS on Multiple Individual Columns
Example: Summary Multicolumn Statistics
Example: HELP STATISTICS for a Geospatial Column
Related Information
HELP STATISTICS (QCD Form)
HELP STATISTICS Syntax (QCD Form)
HELP STATISTICS Syntax Elements (QCD Form)
Usage Notes
How To Report Summary Statistics and Detailed Statistics
Example: Summary Statistics
Related Information
Row-Level Security Constraint Statements
CREATE CONSTRAINT
CREATE CONSTRAINT Syntax
CREATE CONSTRAINT Syntax Elements
Examples
Example: Creating a Constraint Object for a Hierarchical Classification
Example: Creating a Constraint Object with a Non-Hierarchical Classification
Related Information
ALTER CONSTRAINT
ALTER CONSTRAINT Syntax
ALTER CONSTRAINT Syntax Elements
ALTER CONSTRAINT Examples
Related Information
DROP CONSTRAINT
DROP CONSTRAINT Syntax
DROP CONSTRAINT Syntax Elements
Usage Notes
Restrictions on Dropping a Row-Level Security Constraint
Example: Dropping a Row-Level Security Constraint
Related Information
Triggers Statements
ALTER TRIGGER
ALTER TRIGGER Syntax
ALTER TRIGGER Syntax Elements
CREATE TRIGGER and REPLACE TRIGGER
CREATE TRIGGER and REPLACE TRIGGER Syntax
CREATE TRIGGER and REPLACE TRIGGER Syntax Elements
Examples
Example: Ensuring That Parent Table Updates Propagate to its Child
Example: Audit Log for Large Pay Raises
Example: Using a SET Clause
Example: Cascaded Triggers
Example: Valid WHEN Clause
Example: Non-Valid WHEN Clause
Example: Valid Use of Subquery in WHEN Clause
Example: Non-Valid Use of Subquery in WHEN Clause
Example: OLD_NEW_TABLE AFTER UPDATE Trigger
Example: Calling an SQL Procedure From Within a Trigger
Example: Calling a UDF From a Trigger
Example: Adding to a Mailing List Using UDTs
Example: Setting a Default Address to Replace an Inserted Null
Example: Dynamic UDT Input From a Trigger
Example: Using a Trigger to Update a NoPI Table
Example: Invoking an SQL UDF Within a Trigger Definition
Related Information
DROP TRIGGER
DROP TRIGGER Syntax
DROP TRIGGER Syntax Elements
Example: Dropping a Trigger
Related Information
HELP TRIGGER
HELP TRIGGER Syntax
HELP TRIGGER Syntax Elements
HELP TRIGGER Examples
Related Information
RENAME TRIGGER
RENAME TRIGGER Syntax
RENAME TRIGGER Syntax Elements
Example: Rename a Trigger
Related Information
Comment, Help, and Show Statements
COMMENT (Comment Placing Form)
COMMENT Syntax (Comment-Placing Form)
COMMENT Syntax Elements (Comment-Placing Form)
Usage Notes
Maps and Comments
Japanese Characters in Comments
COMMENT Examples (Comment Placing Form)
Example: Placing a Comment
Example: Commenting on a UDF
Example: Commenting on an SQL Function
Examples: Commenting on a Map
Example: Commenting on a Hash Index
Commenting on UDT and Method Definitions
Related Information
HELP ONLINE
HELP ONLINE Syntax
HELP ONLINE Syntax Elements
Examples: HELP ONLINE
SHOW object
SHOW object Syntax
SHOW object Syntax Elements
SHOW object Examples
Example: SHOW HASH INDEX
Example: SHOW HASH INDEX in XML Format
Example: SHOW JOIN INDEX
Example: SHOW JOIN INDEX in XML Format
Example: SHOW MACRO
Example: SHOW VIEW
Example: SHOW VIEW in XML Format
Example: SHOW PROCEDURE
Example: Showing a Java External Stored Procedure with an XML Data Type
Example: SHOW FUNCTION (External Form)
Example: SHOW FUNCTION (SQL Form)
Example: SHOW METHOD
Example: Show User-Installed File (UIF)
Example: SHOW CAST
Example: SHOW TYPE
Example: SHOW TYPE for ARRAY Types
Example: SHOW Avro SCHEMA
Example: SHOW CONSTRAINT
SHOW request
SHOW request Syntax
SHOW request Syntax Elements
Examples
Example: SHOW For a SELECT Request
Example: SHOW For a SELECT Request That Accesses Multiple Views
Example: SHOW For a SELECT Request Using the QUALIFIED Option
Example: SHOW For a SELECT Request on a Table With Multiple Referential Integrity Constraints
Example: SHOW for an Updatable DATE PPI Table
Example: SHOW for an SQL Function
Example: SHOW in XML Format
Example: SHOW for a SELECT Request with a Function Mapping
Notation Conventions
How to Read Syntax
Character Shorthand Notation Used in This Document
Object Data Types
Data Types Syntax
Additional Information
Teradata Links
isolation_level Default transaction isolation level in force for the current session.
The valid transaction isolation level options are the following.
READ UNCOMMITTED
RU
Sets the read-only locking severity for the current session to ACCESS for SELECT operations embedded within DELETE, INSERT, MERGE, and UPDATE requests if the DBS Control parameter AccessLockForUncomRead is set to TRUE; otherwise, the read-only locking severity for such requests remains READ.
For outer SELECT requests and SELECT subqueries that are not embedded within DELETE, INSERT, or UPDATE requests, the default read-only locking severity is always READ.
The read-only locking severity for SELECT operations embedded within DELETE, INSERT, MERGE, or UPDATE requests remains READ when the transaction isolation level is set to READ UNCOMMITTED only if the setting of the DBS Control parameter AccessLockForUncomRead is FALSE.
If AccessLockForUncomRead is set to TRUE, the default read-locking severity lock for these requests is ACCESS.
RU and READ UNCOMMITTED are synonyms.
SERIALIZABLE
SR
Sets the read-only locking severity for all SELECT requests made against nontemporal tables, whether they are outer SELECT requests or subqueries, in the current session to READ regardless of the setting for the DBS Control parameter AccessLockForUncomRead.
Serializability violations can occur with DML operations that use Current semantics or that specify the CURRENT VALIDTIME qualifier whether the transaction isolation level is set to SERIALIZABLE or not. See Teradata Vantage™ - Temporal Table Support , B035-1182
SR and SERIALIZABLE are synonyms.
Note that transaction isolation levels affect locking severities, not locking levels (see Teradata Vantage™ - SQL Request and Transaction Processing , B035-1142