Preface
Purpose
Audience
Supported Software Releases and Operating Systems
Prerequisites
Changes to this Book
Additional Information
Product Safety Information
Teradata Database Optional Features
Introduction
How the Statements are Organized
List of SQL Statements and Purposes
Table Statements
CREATE TABLE
Syntax Elements
Table Kind
Table Name
FALLBACK
WITH JOURNAL TABLE
LOG
BEFORE JOURNAL
AFTER JOURNAL
JOURNAL
CHECKSUM
FREESPACE
MERGEBLOCKRATIO
Example: Specifying Different MERGEBLOCKRATIOs
DATABLOCKSIZE
Example: Specifying DATABLOCKSIZE, CHECKSUM, and FREESPACE
BLOCKCOMPRESSION
WITH ISOLATED LOADING
Column Definitions
AUTO COMPRESS
data type
Column Attributes
PERIOD FOR
Example: Creating a Table with a Derived Period Column
Compression Attributes
COMPRESS constant
COMPRESS USING
DECOMPRESS USING
Identity Column
Identity Column Parameters
CONSTRAINT
UNIQUE
PRIMARY KEY
CHECK Constraint Column Attribute
Example: Specifying Column-Level Named CHECK Constraints
REFERENCES Constraint Column Attribute
Table Attribute Syntax
PRIMARY KEY (column_name)
CHECK (boolean_condition)
FOREIGN KEY
WITH NO CHECK OPTION
row_level_security_constraint_column_name CONSTRAINT
NORMALIZE
PRIMARY INDEX
PRIMARY AMP
NO PRIMARY
PARTITION BY
partitioning level
UNIQUE INDEX
INDEX
ORDER BY
WITH LOAD IDENTITY
Temporary/Volatile Table Preservation
ON COMMIT
AS Clause (Copy Table Syntax)
Example: Copy Table with Data and Default Column Names
Example: Copy Table Without Data and Default Column Names
Example: Changing Column Grouping for a Column-Partitioned Table Using CREATE TABLE … AS Syntax
Source Table
Subquery Clause
WITH DATA
AND 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
ANSI Compliance
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: AS … WITH NO DATA With Named Expressions
Example: AS … WITH NO DATA With a Nonunique Secondary Index
Example: AS … WITH DATA With a 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 … AS Requests That Produce a Table With a 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 … AS Requests That Produce a NoPI Table
Example: Current and Historical Partitioning Using CURRENT_DATE in a CASE_N Expression
Example: Quarterly Current and a Single Historical Partition Defined Using the 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: Creating a Table With Row-Level Security Constraints
Related Topics
CREATE TABLE (Queue Table Form)
Syntax Elements
Table Kind Clause
Example: Creating a Multiset Queue Table
Table Name Clause
table_name
Example: Creating a Simple Queue Table
Table Options Clause
QUEUE
FALLBACK
FREESPACE integer
DATABLOCKSIZE
BLOCKCOMPRESSION
CHECKSUM
Column Definition Clause
QITS_column_name
column_name
Compression Attributes Clause
COMPRESS
Identity Column Specification
Identity Column Parameters
START WITH
INCREMENT BY
MINVALUE
MAXVALUE
CYCLE
Column Constraint Attributes Clause
CONSTRAINT name
UNIQUE
PRIMARY KEY
CHECK (boolean condition)
UNIQUE Definition Clause
CONSTRAINT name
UNIQUE
PRIMARY KEY
column_name
CHECK Definition Clause
CONSTRAINT name
CHECK (boolean_condition)
INDEX Definition Clause
PRIMARY INDEX
INDEX, secondary index definition
INDEX, secondary index definition using an ORDER BY clause
ORDER BY, Index Definition
Related Topics
ANSI Compliance
CREATE GLOBAL TEMPORARY TRACE TABLE
Syntax Elements
table_name
proc_ID BYTE(2)
sequence INTEGER
column_name
ON COMMIT
ANSI Compliance
Related Topics
CREATE ERROR TABLE
Syntax Elements
error_table_name
data_table_name
NO RLS
ANSI Compliance
Related Topics
ALTER TABLE
Syntax Elements
ALTER TABLE Basic Options
table_name
NORMALIZE
Example: Adding a NORMALIZE Constraint to a Table
Example: Dropping a NORMALIZE Constraint From a Table
MODIFY PRIMARY
MODIFY NO PRIMARY
MODIFY partitioning
FROM TIME ZONE
Example: Migrating Data to a New Time Zone
DOWN TABLE Option
DOWN
Example: Using SET DOWN
Example: Using RESET DOWN
ALTER TABLE Join Index Options
join_index
ADD COLUMN (column_name)
ADD ROW (column_name)
ADD SYSTEM (column_name )
ADD (column_name ) AUTO COMPRESS
REVALIDATE Option
REVALIDATE
PRIMARY INDEX
WITH DELETE
WITH INSERT
RELEASE ROWS
Alter Options
ADD column_name
ADD COLUMN (column_name)
ADD ROW (column_name )
ADD SYSTEM (column_name )
Example: Adding a Single-Column Partition to a Table
ADD (column_name ) AUTO COMPRESS
Example: Adding a Single-Column Partition
Example: Adding a Column to a Single-Column Partition
ADD PERIOD FOR
ADD CONSTRAINT name
Example: Attempting to Add and Drop Constraints in the Same Statement
Example: Attempting to Add a Constraint with a Duplicate Name
ADD row_level_security_constraint_name CONSTRAINT
Example: Adding a Row-Level Security Constraint
ADD FOREIGN KEY REFERENCES
Example: Adding a FOREIGN KEY Constraint
Example: Adding or Dropping a Batch Referential Constraint
Example: Adding a Table-Level Referential Constraint
Example: Adding a Foreign Key to a Column-Partitioned Table
ADD CHECK (boolean_condition)
Example: Adding a Named CHECK Constraint to a Table
Example: Adding an Unnamed CHECK Constraint
Example: Adding a Column With an Unnamed CHECK Constraint
ADD UNIQUE (column_name)
Example: Adding a Named UNIQUE Constraint to a Table
Example: Adding a Multicolumn Unnamed UNIQUE Constraint
ADD PRIMARY KEY (column_name)
Example: Adding a PRIMARY KEY Constraint to a Table
MODIFY CHECK (boolean_condition )
Example: Modifying a Named CHECK Constraint
Example: Attempting to Modify a Nonexistent Constraint
RENAME
DROP PERIOD FOR
Example: Adding a Derived Period Column to a Table
DROP column_name
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
DROP CONSTRAINT name
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
DROP row_level_security_ constraint_name CONSTRAINT
DROP FOREIGN KEY REFERENCES
Example: Adding a Named FOREIGN KEY Constraint to a Table
Example: Dropping a FOREIGN KEY Constraint
DROP CHECK
Example: Dropping All Unnamed Table-Level CHECK Constraints
DROP INCONSISTENT REFERENCES
Table Options
FALLBACK
Example: Adding Fallback to a Table
WITH JOURNAL 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
JOURNAL
LOG
AFTER JOURNAL
JOURNAL
ON COMMIT
CHECKSUM
DEFAULT FREESPACE
FREESPACE
MERGEBLOCKRATIO
DATABLOCKSIZE
BLOCKCOMPRESSION
WITH ISOLATED LOADING
Column Attributes
UPPERCASE
CASESPECIFIC
FORMAT quotestring
TITLE quotestring
NAMED name
DEFAULT
USER
DATE
TIME
NULL
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
WITH DEFAULT
NO COMPRESS
Example: Changing NO COMPRESS for an Existing Column
COMPRESS
Example: Changing the Value Compression for a Compressed Column
Example: Adding a New Column With Multivalue Compression
COMPRESS USING
Example: Adding and Changing Algorithmic Compression
DECOMPRESS USING
UNIQUE
PRIMARY KEY
CHECK (boolean_ condition)
REFERENCES
Alter Partitioning
PARTITION BY
Table Definitions for Examples
Example: Non-Valid MODIFY PRIMARY INDEX Statements
Example: Modifying Partition Ranges
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
PARTITION BY COLUMN
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
NOT PARTITIONED
DROP RANGE
Example: Dropping and Adding Partition Ranges
Example: Drop and Add Partition Ranges and Delete Rows Outside the Defined Ranges
Example: Dropping a Row Range Partition When Partitioning is Defined With a RANGE_N Function and a NO RANGE Partition
Example: Dropping Ranges Without an EACH Clause
ADD RANGE
Example: Using MODIFY to Repartition a Table and Saving Resulting Nonvalid Rows in a Save Table
WITH DELETE
Example: Revalidating the Partitioning for a Table
WITH INSERT
Related Topics
ANSI Compliance
ALTER TABLE TO CURRENT
Syntax Elements
table_name
join_index_name
WITH INSERT INTO save_table
WITH DELETE
ANSI Compliance
Usage Considerations
ALTER TABLE TO CURRENT Processing
Related Topics
RENAME TABLE
Syntax Elements
old_table_name
TO
new_table_name
ANSI Compliance
Related Topics
DROP TABLE
Syntax Elements
table_name
Example: Dropping a Table
TEMPORARY
ALL
ANSI Compliance
Related Topics
DROP ERROR TABLE
Syntax Elements
FOR data_table
Example: Dropping an Error Table
error_table_name
ANSI Compliance
Related Topics
HELP COLUMN
Syntax Elements
Column Name
ANSI Compliance
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
Related Topics
HELP CONSTRAINT
Syntax Elements
Constraint name
ANSI Compliance
Related Topics
HELP ERROR TABLE
Syntax Elements
Data Table or Error Table Name
ANSI Compliance
Usage Notes
Related Topics
HELP TABLE
Syntax Elements
Table or Error Table Name
ANSI Compliance
Usage Considerations
Table Columns
Examples
Example: HELP TABLE
Example: HELP TABLE with a UDT Column
Example: HELP TABLE with Temporal Columns
HELP VOLATILE TABLE
Syntax Elements
Volatile Table Name
Example: HELP VOLATILE TABLE
ANSI Compliance
Usage Considerations
Volatile Table Attributes
Related Topics
View Statements
CREATE VIEW and REPLACE VIEW
Syntax Elements
view_name
column_name
AS
LOCKING Clause
AS OF
temporal_modifier
SELECT Clause
SELECT
TOP
WITH TIES
expression
*
FROM Clause
FROM
table_name
view_name
joined_table
JOIN
CROSS JOIN
Derived Tables
subquery
WHERE Clause
WHERE search_condition
Example: Creating a View that Defines a Self-Join with a Table
Example: Invoking an SQL UDF Within a View Definition
GROUP BY
HAVING Clause
HAVING
Example: Creating a View with Aggregates
QUALIFY Clause
QUALIFY
WITH CHECK OPTION
ORDER BY Clause
ORDER BY
ANSI Compliance
Related Topics
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW
Syntax Elements
view_name
WITH Request Modifier Clause (Non-Recursive Form)
WITH query_name
LOCKING Clause
AS OF Clause
temporal_modifier
AS OF
embedded_services_calendar_function
DATE expression
TIMESTAMP expression
system_calendar_name
SELECT Clause
SELECT
DISTINCT
ALL
expression
*
table_name
FROM Clause
FROM
table_name .*
AS correlation_name
FROM Joined Table
joined_table
JOIN
CROSS JOIN
Derived Tables
subquery
AS
derived_table_name
column_name
WHERE Clause
WHERE
GROUP BY Clause
GROUP BY
HAVING Clause
HAVING
QUALIFY Clause
QUALIFY
ANSI Compliance
Examples
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
Related Topics
RENAME VIEW
Syntax Elements
old_view_name
TO
new_view_name
ANSI Compliance
DROP VIEW
Syntax Elements
view_name
Example: Dropping a View
ANSI Compliance
Related Topics
HELP VIEW
Syntax Elements
View Name
ANSI Compliance
Index Statements
CREATE INDEX
Syntax Elements
UNIQUE
index_name
ALL
index_column_name
ORDER BY
Loading
ON Table
ON Join Index
ANSI Compliance
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
Syntax Elements
Table Options
join_index_name
FALLBACK
CHECKSUM
BLOCKCOMPRESSION
SELECT
Join Index Select List
Aggregation Clause
SUM(numeric_expression )
COUNT(value_expression)
MIN(value_expression)
MAX(value_expression)
EXTRACT FROM
AS expression_alias
Row Compression
Fixed Column Set for Join Index Row Compression
Repeating Column Set for Join Index Row Compression
Grouping Column-Partitioned Join Index Column Data
FROM Clause
Joined Table Clause
WHERE Clause
GROUP BY
ORDER BY
Indexes Clause
PRIMARY INDEX
PRIMARY AMP
NO PRIMARY
PARTITION BY
INDEX
ANSI Compliance
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: An 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 Column-Partitioned Join Indexes
Example: Creating Column-Partitioned Sparse Join Index With Autocompression Based on the Default
Related Topics
CREATE HASH INDEX
Syntax Elements
Hash Index Name
FALLBACK
BLOCKCOMPRESSION
CHECKSUM
column_name_1
Table Name
BY column_name_2
ORDER BY
VALUES
HASH
column_name_3
ANSI Compliance
Examples
Examples for Base Table With Unique Primary Index
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 Topics
DROP INDEX
Syntax Elements
Index Name
ON Table
ON Join Index
Index Definition
ORDER BY
table_name
join_index_name
ANSI Compliance
Examples
Example: Dropping a Named Secondary Index
Example: Dropping a Simple Unnamed Secondary Index
Example: Dropping a Composite Unnamed Secondary Index
Related Topics
DROP JOIN INDEX
Syntax Elements
join_index_name
ANSI Compliance
Related Topics
DROP HASH INDEX
Syntax Elements
hash_index_name
Example: Dropping a Hash Index
ANSI Compliance
Related Topics
HELP INDEX
Syntax Elements
Table Name
Join Index, Hash Index, or View Name
ANSI Compliance
Related Topics
HELP JOIN INDEX
Syntax Elements
Join Index Name
ANSI Compliance
Related Topics
HELP HASH INDEX
Syntax Elements
Hash Index Name
ANSI Compliance
Related Topics
Authorization Statements for External Routines
CREATE AUTHORIZATION and REPLACE AUTHORIZATION
Syntax Elements
authorization_name
DEFINER
INVOKER
USER ‘user_name’
PASSWORD ‘password’
ANSI Compliance
Related Topics
DROP AUTHORIZATION
Syntax Elements
authorization_name
ANSI Compliance
Related Topics
Global and Persistent (GLOP) Data Statements
CREATE GLOP SET
Syntax Elements
GLOP_set_name
ANSI Compliance
Examples
Example: Creating a GLOP Set
Related Topics
DROP GLOP SET
Syntax Elements
GLOP_set_name
Example: Dropping a GLOP Set
ANSI Compliance
Related Topics
Procedure Statements
CREATE PROCEDURE and REPLACE PROCEDURE (External Form)
Syntax Elements
procedure_name
Example: Creating a Procedure Using PARAMETER STYLE TD_GENERAL
parameter_name data type
Example: Creating a Procedure Using PARAMETER STYLE SQL
Example: Creating an External Procedure With SQL CLIv2 Callability
DYNAMIC RESULT SETS number_of_sets
language_clause
SQL_data_access
external_data_access
PARAMETER STYLE
Example: Creating Java External SQL Procedures
SQL SECURITY privilege_option
USING GLOP SET GLOP_set_name
EXTERNAL
EXTERNAL NAME external_procedure_name
EXTERNAL NAME external_string_literal
EXTERNAL NAME external_Java_reference_string
EXTERNAL SECURITY
ANSI Compliance
Related Topics
CREATE PROCEDURE (SQL Form) and REPLACE PROCEDURE (SQL Form)
Syntax Elements
procedure_name
parameter_name
SQL_data_access
DYNAMIC RESULT SETS
SQL SECURITY
Statement Options
SQL_statement
BEGIN REQUEST
SQL_multistatement_request
END REQUEST
compound statement
assignment statement
condition statement
iteration statement
diagnostic statement
ITERATE
LEAVE
Compound Statement
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
label_name
BEGIN
statement_list
END
Local Declaration
DECLARE
variable_name data_type
condition_name CONDITION
Cursor Declaration
cursor_name
WITHOUT RETURN
WITH RETURN
FOR cursor_specification Clause
PREPARE
Cursor Specification
SELECT
FROM table_name
WHERE clause
other SELECT clauses
Condition Handler
CONTINUE
HANDLER
FOR
SQLSTATE
SQLEXCEPTION
handler_action_statement
Open Statement
OPEN cursor_name
USING
Fetch Statement
cursor_name
parameter_reference
Assignment Statement
SET
assignment_target
assignment_source
Condition Statement
CASE
operand_1
THEN
statement
conditional_expression
END CASE
IF
THEN
END IF
Iteration Statement
WHILE
LOOP
FOR
REPEAT
Diagnostic Statements
SIGNAL
RESIGNAL
SQLSTATE SQLSTATE_code
SET condition_information
GET DIAGNOSTICS
Usage Considerations
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 a 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: Setting the Transaction Query Band Using a Parameter
Example: Creating a Procedure with Multistatement Request Using a Dynamic SQL Call Statement
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
ANSI Compliance
Related Topics
ALTER PROCEDURE (SQL Form)
Syntax Elements
procedure_name
Alter Procedure Options
SPL
WARNING
AT TIME ZONE
Examples: ALTER PROCEDURE (SQL form)
ANSI Compliance
ALTER PROCEDURE (External Form)
Syntax Elements
procedure_name
LANGUAGE
COMPILE
AT TIME ZONE
EXECUTE PROTECTED
Usage Considerations
Invocation Restrictions
ANSI Compliance
RENAME PROCEDURE
Syntax Elements
TO
old_procedure_name
Example
new_procedure_name
ANSI Compliance
Related Topics
DROP PROCEDURE
Syntax Elements
procedure_name
Example: Dropping a Procedure
ANSI Compliance
Related Topics
HELP PROCEDURE
Syntax Elements
Procedure Name
ANSI Compliance
Examples
Example: Reporting UDT Parameters
Example: Reporting ARRAY Parameters for a Procedure
Example: HELP PROCEDURE Attributes Report
Related Topics
Macro Statements
CREATE MACRO and REPLACE MACRO
Syntax Elements
macro_name
parameter_name
USING request_ modifier
LOCKING request_ modifier
SQL_statement
ANSI Compliance
Related Topics
RENAME MACRO
Syntax Elements
old_macro_name
TO
new_macro_name
ANSI Compliance
Related Topics
DROP MACRO
Syntax Elements
macro_name
ANSI Compliance
Related Topics
HELP MACRO
Syntax Elements
Macro Name
Example: HELP MACRO
Example: HELP MACRO With UDTs
ANSI Compliance
Related Topics
User-Defined Function Statements
CREATE FUNCTION (SQL Form) and REPLACE FUNCTION (SQL Form)
Syntax Elements
function_name
parameter_name
RETURNS data type
language_clause
SQL_data_access
SPECIFIC specific_function_name
DETERMINISTIC
RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN expression
ANSI Compliance
RETURN Statement
Syntax Elements
value_expression
ANSI Compliance
Rules
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 Topics
CREATE FUNCTION (Table Form)
Syntax Elements
function_name
data type
RETURNS TABLE
VARYING COLUMNS (maximum_output_columns)
VARYING USING FUNCTION function_name
language_clause
SQL_data_access
external_data_access
SPECIFIC specific_function_name
PARAMETER STYLE
DETERMINISTIC
CALLED ON NULL INPUT
USING GLOP_set_name
EXTERNAL
EXTERNAL NAME external_function_name
EXTERNAL NAME external_string_literal
EXTERNAL SECURITY
ANSI Compliance
Related Topics
CREATE FUNCTION (External Form) and REPLACE FUNCTION (External Form)
Syntax Elements
function_name
parameter name data type
RETURNS data type
CAST FROM data type
language_clause
SQL_data_access
external_data_access
SPECIFIC specific_function_name
CLASS
PARAMETER STYLE
FOR COMPRESS
FOR DECOMPRESS
DETERMINISTIC
RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
USING GLOP SET GLOP_set_name
EXTERNAL
EXTERNAL NAME external_function_name
EXTERNAL NAME external_string_literal
EXTERNAL SECURITY
ANSI Compliance
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 Topics
ALTER FUNCTION
Syntax Elements
SPECIFIC FUNCTION
FUNCTION
EXECUTE PROTECTED
COMPILE
Usage Considerations
Using ALTER FUNCTION or REPLACE FUNCTION with Row-Level Security UDFs
ANSI Compliance
DROP FUNCTION
Syntax Elements
SPECIFIC FUNCTION
Differences Between the External and SQL Forms of DROP FUNCTION
Example: Dropping an SQL Function
Example: Dropping an External Function by Function Name or Specific Function Name
FUNCTION
Example: Dropping an External Function and Unique Function Names
ANSI Compliance
Related Topics
HELP FUNCTION
Syntax Elements
SPECIFIC FUNCTION
FUNCTION
ANSI Compliance
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
Related Topics
RENAME FUNCTION (SQL Form)
Syntax Elements
SPECIFIC FUNCTION
FUNCTION
ANSI Compliance
Related Topics
RENAME FUNCTION (External Form)
Syntax Elements
SPECIFIC FUNCTION
Example 1: Renaming the Specific Name for a Function
FUNCTION
Example 2: Renaming the Overloaded Calling Name for a Function
ANSI Compliance
Related Topics
User-Defined Method Statements
CREATE METHOD
Syntax Elements
method_name
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
data type
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
RETURNS data type
Example: Methods That Return a TD_ANYTYPE or INTEGER Data Type
CAST FROM data type
FOR UDT_name
USING GLOP SET GLOP_set_name
EXTERNAL
Example: Creating an Instance Method for a UDT Stored on the Client
EXTERNAL SECURITY
ANSI Compliance
Related Topics
ALTER METHOD
Syntax Elements
SPECIFIC METHOD
METHOD
FOR UDT.name
ANSI Compliance
REPLACE METHOD
Syntax Elements
SPECIFIC METHOD
METHOD
EXTERNAL
EXTERNAL SECURITY
ANSI Compliance
Usage Considerations
How REPLACE METHOD And CREATE METHOD Differ
Related Topics
HELP METHOD
Syntax Elements
METHOD
Example: HELP METHOD with Sample Output
Example: TD_ANYTYPE Input Parameter Data Type
SPECIFIC METHOD
Example: HELP METHOD
ANSI Compliance
Related Topics
User-Defined Type Statements
CREATE TYPE (Structured Form)
Syntax Elements
UDT_name
AS attribute_name
INSTANTIABLE
NOT FINAL
METHOD
parameter_name
AS LOCATOR
RETURNS
AS LOCATOR
CAST FROM
SPECIFIC
SELF AS RESULT
PARAMETER STYLE
DETERMINISTIC
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
language_clause
SQL_data_access
ANSI Compliance
Examples
Example: Creating a Structured UDT with 2 Attributes
Example: Creating a Structured UDT from Predefined Data Types
Related Topics
CREATE TYPE (Distinct Form)
Syntax Elements
UDT_name
data type
CHARACTER SET server_character_set
FINAL
METHOD
parameter_name
RETURNS
CAST FROM
SPECIFIC specific_method_name
SELF AS RESULT
PARAMETER STYLE
DETERMINISTIC
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
language_clause
SQL_data_access
ANSI Compliance
Related Topics
CREATE TYPE (ARRAY/VARRAY Form)
Syntax Elements
One-Dimensional ARRAY Options
One-Dimensional VARRAY Options
Multidimensional Array Options
Multidimensional Varying Array Options
ANSI Compliance
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 Topics
CREATE CAST and REPLACE CAST
Syntax Elements
source_data_type
target_data_type
SPECIFIC FUNCTION
FUNCTION
SPECIFIC METHOD specific_method_name
METHOD
AS ASSIGNMENT
ANSI Compliance
Related Topics
CREATE ORDERING and REPLACE ORDERING
Syntax Elements
FOR UDT_name
SPECIFIC FUNCTION specific_function_name
FUNCTION function_name
Example: Order Mapping for a UDT using a UDF
SPECIFIC METHOD specific_method_name
METHOD method_name
Example: Order Mapping for a UDT using a Method
INSTANCE METHOD method_name
FOR UDT_name
ANSI Compliance
Related Topics
CREATE TRANSFORM and REPLACE TRANSFORM
Syntax Elements
FOR UDT_name
transform_group_name
TO SQL Clause
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 Clause
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
ANSI Compliance
Related Topics
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
ANSI Compliance
Examples
Example: COMPILE Option, ALTER TYPE Statement
Example: COMPILE ONLY Option, ALTER TYPE Statement
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 Topics
DROP TYPE
Syntax Elements
user_defined_type_name
ANSI Compliance
Related Topics
DROP CAST
Syntax Elements
source_data_type
target_data_type
ANSI Compliance
Related Topics
DROP ORDERING
Syntax Elements
user_defined_type_name
ANSI Compliance
Related Topics
DROP TRANSFORM
Syntax Elements
transform_group_name
user_defined_type_name
ANSI Compliance
Related Topics
HELP TYPE
Syntax Elements
UDT Name
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
ANSI Compliance
Usage Considerations
HELP COLUMN Report on a Unicode View Differs From a Compatibility View
Related Topics
HELP CAST
Syntax Elements
UDT_name
ANSI Compliance
Related Topics
HELP TRANSFORM
Syntax Elements
UDT Name
ANSI Compliance
Examples
Example: HELP TRANSFORM for a UDT
Related Topics
Database Statements
CREATE DATABASE
Syntax Elements
name
FROM database_name
PERMANENT
SPOOL
TEMPORARY=n BYTES
ACCOUNT = ‘account_string’
FALLBACK PROTECTION
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
ANSI Compliance
DELETE DATABASE
Syntax Elements
Database Name
Example: Deleting a Database
ANSI Compliance
Related Topics
MODIFY DATABASE
Syntax Elements
database_name
AS
PERMANENT
TEMPORARY
SPOOL
ACCOUNT
FALLBACK
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
ANSI Compliance
Related Topics
DATABASE
Syntax Elements
database_name
ANSI Compliance
Related Topics
DROP DATABASE
Syntax Elements
database_name
Usage Considerations
Dropping a Populated Database
ANSI Compliance
Related Topics
HELP DATABASE
Syntax Elements
Database Name
Example: HELP DATABASE
Example: HELP DATABASE Output Showing a UDT and a Method
Example: Output with a UIF
ANSI Compliance
Related Topics
User, Profile, and Role Statements
CREATE PROFILE
Syntax Elements
profile_name
ACCOUNT = account_string
DEFAULT DATABASE = database_name
SPOOL
TEMPORARY
PASSWORD
QUERY_BAND
COST PROFILE = cost_profile_name
CONSTRAINT
ANSI Compliance
Usage Considerations
About Accounts
Creating Account Strings
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
Related Topics
MODIFY PROFILE
Syntax Elements
profile_name
ACCOUNT
DEFAULT DATABASE
SPOOL
Example: Modify profile spool space
Example: Using a Constant Expression to Specify the SPOOL Space for a Profile
TEMPORARY
PASSWORD
QUERY_BAND
COST PROFILE
CONSTRAINT
ANSI Compliance
Related Topics
CREATE ROLE
Syntax Elements
role_name
EXTERNAL
ANSI Compliance
Related Topics
CREATE USER
Syntax Elements
user_name
AS
PERMANENT
PASSWORD
STARTUP
TEMPORARY
SPOOL
DEFAULT DATABASE
COLLATION
ACCOUNT
FALLBACK
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
TIME ZONE
DATEFORM
DEFAULT CHARACTER SET
DEFAULT ROLE
PROFILE
DBA
CONSTRAINT
EXPORTWIDTH
ANSI Compliance
Usage Considerations
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
Related Topics
MODIFY USER
Syntax Elements
user_name
PERMANENT
STARTUP
PASSWORD
RELEASE PASSWORD LOCK
TEMPORARY
SPOOL
ACCOUNT
DEFAULT DATABASE
COLLATION
FALLBACK
JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE
DROP DEFAULT JOURNAL TABLE
TIME ZONE
DATEFORM
DEFAULT CHARACTER SET
DEFAULT ROLE
PROFILE
DBA
EXPORTWIDTH
CONSTRAINT
Usage Considerations
Valid Export Width Specifications
Compatibility Mode
Expected Mode
Maximum Mode
DEFAULT Mode
Effects of Shift Out and Shift In Characters on Export Widths
ANSI Compliance
Related Topics
SET ROLE
Syntax Elements
role_name
EXTERNAL
NONE
NULL
ALL
ANSI Compliance
Related Topics
DELETE USER
Syntax Elements
user_name
ALL
ANSI Compliance
Related Topics
DROP PROFILE
Syntax Elements
profile_name
Effects of Dropping a Profile
ANSI Compliance
Related Topics
DROP ROLE
Syntax Elements
Role Name
ANSI Compliance
Related Topics
DROP USER
Syntax Elements
user_name
Effects of Dropping a User on Database Privileges
Effects of Dropping a User on User-Created Roles and Profiles
ANSI Compliance
Related Topics
HELP USER
Syntax Elements
User Name
ANSI Compliance
Related Topics
Secure Zones Statements
CREATE ZONE
Syntax Elements
zone_name
ROOT
ALTER ZONE
Syntax Elements
zone_name
ADD ROOT
DROP ROOT
DROP ZONE
Syntax Elements
zone_name
Session Statements
SET SESSION
Using SET SESSION Statements with Connection Pooling
SET SESSION ACCOUNT
Syntax Elements
account_string
FOR
Example: Setting the Account to Change Resource Charges for a Session
Example: Setting the Account to Change the Priority for a Request
ANSI Compliance
Related Topics
SET SESSION CALENDAR
Syntax Elements
calendar_name
Example: Changing the Default Session Calendar Using SET SESSION CALENDAR
Example: Changing the Default Session Calendar Using a CREATE USER Request
ANSI Compliance
Related Topics
SET SESSION COLLATION
Usage Notes
Syntax Elements
COLLATION
About Collation
ASCII
Example: Setting a Session to Use the ASCII Collation Sequence
Example: Effect of the ASCII Collation Sequence on SQL Report Output
CHARSET_COLL
EBCDIC
Example: Setting a Session to Use the EBCDIC Collation Sequence
Example: Effect of the EBCDIC Collation Sequence on SQL Report Output
HOST
JIS_COLL
MULTINATIONAL Collation
ANSI Compliance
Related Topics
SET SESSION CONSTRAINT
Syntax Elements
CONSTRAINT
level_name
Constraint Definitions for the Examples
category_name
NULL
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
Example: Loading Tables without User OVERRIDE Privileges
Example: Loading Tables with User OVERRIDE Privileges
Using SET SESSION to Change the Session Security Constraint Value
Using HELP SESSION to Investigate Session Constraint Values
ANSI Compliance
Related Topics
SET SESSION DATABASE
Syntax Elements
DATABASE
Example
ANSI Compliance
Session Database and Called Procedures
SET SESSION DATEFORM
Syntax Elements
SET SESSION DATEFORM
ANSI Compliance
Usage Considerations
Session Dateform and Called Procedures
SET SESSION DEBUG FUNCTION
SET SESSION FOR ISOLATED LOADING
SET SESSION FUNCTION TRACE
Syntax Elements
FUNCTION TRACE
USING mask_string FOR
TRACE
OFF
ANSI Compliance
Related Topics
SET SESSION JSON IGNORE ERRORS
Syntax Elements
JSON IGNORE ERRORS
ANSI Compliance
Related Topics
SET SESSION SEARCHUIFDBPATH
Syntax Elements
database_name
Example: Setting the Database UIF Search Path
user_name
ANSI Compliance
SET SESSION TRANSACTION ISOLATION LEVEL
Syntax Elements
TRANSACTION ISOLATION LEVEL
isolation_level
ANSI Compliance
Related Topics
SET QUERY_BAND
Syntax Elements
pair_name
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
pair_value
Example: Setting a Query Band That Specifies the EQUALS SIGN Character as Part of the Value
NONE
Example: Removing a Query Band From a Session
UPDATE
Example: Query Band UPDATE Examples
FOR SESSION
Example: Setting a Query Band Using FOR SESSION VOLATILE
Example: Using UPDATE With a FOR SESSION VOLATILE Query Band
FOR TRANSACTION
Example: Setting a Query Band for the Current Transaction
Example: Removing a Query Band From the Current Transaction
Example: Using SET QUERY_BAND … FOR TRANSACTION As a 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
ANSI Compliance
Usage Considerations
About 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
Related Topics
SET TIME ZONE
Syntax Elements
LOCAL
USER
expression
quotestring
time_zone_string
ANSI Compliance
HELP SESSION
Syntax Elements
CONSTRAINT Option
ANSI Compliance
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
Related Topics
Logging Statements
BEGIN LOGGING
Syntax Elements
DENIALS
WITH TEXT
Logging Frequency
FOR CONSTRAINT constraint_name
ALL
operation
GRANT
BY user_name
Object for which to Log Access
ANSI Compliance
Usage Considerations
About Logging
Logging of External Users
Determining the Object Level for a 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 Topics
BEGIN QUERY CAPTURE
Syntax Elements
FOR INDEX_ANALYSIS
WITH
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
INTO qcd_name
AS WORKLOAD workload_name
ANSI Compliance
Related Topics
BEGIN QUERY LOGGING
Syntax Elements
WITH
MODE = m
LIMIT
ON
ANSI Compliance
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 Topics
FLUSH QUERY LOGGING
Syntax Elements
flush_option
ANSI Compliance
Related Topics
REPLACE QUERY LOGGING
Syntax Elements
WITH
MODE=m
LIMIT
Unit of Time
ON
ANSI Compliance
Related Topics
END LOGGING
Syntax Elements
DENIALS
WITH TEXT
ON operation Clause
FOR CONSTRAINT
BY database_name
ON object Clause
ANSI Compliance
Related Topics
END QUERY CAPTURE
Syntax Elements
END QUERY CAPTURE
Usage Considerations
Rules and Restrictions for END QUERY CAPTURE
ANSI Compliance
Related Topics
END QUERY LOGGING
Syntax Elements
ALL ACCOUNT = ‘account_string’
Example: Removing the ALL rule from the rule cache and DBC.DBQLRulesTbl
ALL RULES
Example: Remove all DBQL rules in effect from DBC.DBQLRuleTbl
user_name ACCOUNT = ‘account_string’
Example: Specify an account explicitly to discontinue request logging
user_name
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
database_name
APPLNAME = ‘application_name’
ANSI Compliance
Related Topics
SHOW QUERY LOGGING
Syntax Elements
ALL
User
Database
Application
ANSI Compliance
Usage Considerations
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
Statistics Statements
COLLECT STATISTICS (Optimizer Form)
Syntax Elements
SUMMARY
USING Clause
INDEX
COLUMN
ON Clause
FROM Clause
ANSI Compliance
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
Related Topics
SHOW STATISTICS
Syntax Elements
IN XML
CURRENT
SUMMARY
VALUES
Example: SHOW SUMMARY STATISTICS with VALUES Clause
COLUMN
AS statistics_name
INDEX
ORDER BY
ON Clause
FROM Clause
Example: Reporting Detailed QCD Statistics
ANSI Compliance
Usage Considerations
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
Related Topics
DROP STATISTICS (Optimizer Form)
Syntax Elements
INDEX
COLUMN
ORDER BY
table_name
join_index_name
hash_index_name
ANSI Compliance
Related Topics
HELP STATISTICS (Optimizer Form)
Syntax Elements
STATISTICS
Table Name
Join Index or Hash Index Name
ANSI Compliance
Usage Considerations
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 Topics
HELP STATISTICS (QCD Form)
Syntax Elements
STATISTICS
Object Name
FROM QCD_name
ANSI Compliance
Usage Considerations
How To Report Summary Statistics and Detailed Statistics
Related Topics
Row-Level Security Constraint Statements
CREATE CONSTRAINT
Syntax Elements
constraint_name
data_type
NULL
VALUES
SQL operations
ANSI Compliance
Examples
Example: Creating a Constraint Object for a Hierarchical Classification
Example: Creating a Constraint Object with a Non-Hierarchical Classification
Related Topics
ALTER CONSTRAINT
Syntax Elements
constraint_name
VALUES
FUNCTION
ADD
REPLACE
DROP
Related Topics
ANSI Compliance
DROP CONSTRAINT
constraint_name
Restrictions on Dropping a Row-Level Security Constraint
ANSI Compliance
Example: Dropping a Row-Level Security Constraint
Related Topics
Triggers Statements
ALTER TRIGGER
Syntax Elements
trigger_name
table_name
ANSI Compliance
CREATE TRIGGER and REPLACE TRIGGER
Syntax Elements
trigger_name
Triggering Event
Trigger
table_name
ORDER
REFERENCING Clause
OLD
NEW
OLD TABLE
NEW TABLE
OLD_NEW_TABLE
FOR EACH
WHEN
Triggered SQL Statement Clause
BEGIN ATOMIC
ANSI Compliance
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 Topics
DROP TRIGGER
Syntax Elements
trigger_name
ANSI Compliance
Related Topics
HELP TRIGGER
Syntax Elements
Trigger Name or Table Name
ANSI Compliance
Related Topics
RENAME TRIGGER
Syntax Elements
old_trigger_name
TO
new_trigger_name
ANSI Compliance
Related Topics
Comment, Help, and Show Statements
COMMENT (Comment Placing Form)
Syntax Elements
object_kind_1
Function Definition for Examples
Example: Commenting on the Parameter Pattern of a UDF
Example: Commenting on the Definition of an External UDF
Example: Commenting on an SQL Function
object_kind_2
object_name
Commenting on UDT and Method Definitions
Usage Considerations
Japanese Characters in Comments
ANSI Compliance
Related Topics
HELP ONLINE
Syntax Elements
SQL Statement or Utility Command
Examples: HELP Online Form
ANSI Compliance
SHOW object
Syntax Elements
SHOW Options
Hash Index
Join Index
Macro
Table
Error Table For
Error Table
Trigger
View
Procedure
Specific Function
Function
Specific Method
Method
FILE
CAST and TYPE
Type
Constraint
Authorization
GLOP Set
ANSI Compliance
SHOW request
Syntax Elements
DML_request
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
ANSI Compliance
Notation Conventions
About Notation Conventions
Syntax Diagram Conventions
Character Shorthand Notation Used in This Book
Object Data Types
Data Types Syntax