This table alphabetically lists SQL statements and their purpose.
Statement | Purpose |
---|---|
ALTER CONSTRAINT |
Modifies the definition of an existing row-level security constraint object. |
ALTER FUNCTION |
Performs either or both of the following functions.
Java UDFs must always run in protected mode, so you cannot use this statement to change their protection mode.
|
ALTER HASH INDEX |
Moves a hash index from one map to another or changes the colocation for a sparse map. |
ALTER JOIN INDEX |
Moves a join index from one map to another or changes the colocation for a sparse map. |
ALTER METHOD |
Performs either or both of the following functions.
|
ALTER PROCEDURE (External Form) |
Recompiles an existing external procedure and allows changes in the following compile time attributes of the procedure.
|
ALTER PROCEDURE (SQL Form) |
Recompiles an existing SQL procedure and allows changes in the following compile time attributes of the procedure.
|
ALTER TABLE |
Add one or more columns to a table or global temporary table, add or change attributes and options, including partitioning, constraints, and compression. You can also drop columns, change a join index, or revalidate a table. |
ALTER TABLE (Map and Colocation Form) |
Moves a table from one map to another. You can also change the colocation name for a sparse map. |
ALTER TABLE TO CURRENT |
Reconciles the row partitioning for a table or uncompressed join index to a newly resolved date or timestamp when its partitioning is based on the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions. |
ALTER TRIGGER |
Enables or disables a trigger or changes its creation timestamp. |
ALTER TYPE |
Performs any of the following operations for a UDT.
|
ALTER ZONE |
Add a database or user as the root to a secure zone or remove the root database or user from a secure zone. |
BEGIN ISOLATED LOADING |
Starts an explicit concurrent isolated load operation on a load isolated (LDI) table. You can perform concurrent read operations on committed rows while the table is being loaded. |
BEGIN LOGGING |
Starts the auditing of SQL requests that attempt to access data. |
BEGIN QUERY CAPTURE |
Starts the logging of database request information, including some database object creation and drop information. |
BEGIN QUERY LOGGING |
Collects demographic data for one or more columns, computes a statistical profile of the collected data, stores the synopsis in DBC.StatsTbl in the data dictionary, and optionally copies the statistics for one or more columns to a duplicate target table. |
CHECKPOINT ISOLATED LOADING |
Sets a checkpoint for the explicit isolated load operation and commits the data that has been loaded up to that point. |
COLLECT STATISTICS (Optimizer Form) |
Collects demographic data for one or more columns, computes a statistical profile of the collected data, stores the synopsis in DBC.StatsTbl in the data dictionary, and optionally copies the statistics for one or more columns to a duplicate target table. |
COMMENT (Comment Placing Form) |
Creates a user-defined description of a user-defined database object or definition in the data dictionary. |
CREATE AUTHORIZATION |
Creates or replaces an authorization object. |
CREATE CAST |
Creates a cast operation for a UDT. |
CREATE CONSTRAINT |
Creates the SQL row-level security constraint definition and associates it with specific UDFs to enforce that constraint. |
CREATE DATABASE |
Creates a database in which other database objects can be created. |
CREATE ERROR TABLE |
Defines the name and containing database of a new error table and specifies the name of the associated data table. |
CREATE FOREIGN TABLE | Foreign tables enable Vantage to access data in external object storage, such as semi-structured and unstructured data in Amazon S3, Azure Blob storage, and Google Cloud Storage. In-database integration of this data allows data scientists and analysts to read and process this data with Vantage, using standard SQL. You can join external data to relational data in Vantage, and process it using built-in Vantage analytics and functions. |
CREATE FUNCTION (External Form) |
Compiles and installs an external UDF and creates or replaces the SQL function definition used to invoke that UDF. |
CREATE FUNCTION (Table Form) |
Creates a table function definition. |
CREATE FUNCTION (SQL Form) |
Creates an SQL UDF. |
CREATE FUNCTION MAPPING | Creates a function mapping. |
CREATE GLOBAL TEMPORARY TRACE TABLE |
Creates a global temporary trace table to support the UDF- and external SQL procedure-related trace option of the SET SESSION statement. See SET SESSION FUNCTION TRACE. |
CREATE GLOP SET |
Creates the definition of a global persistent memory set. |
CREATE HASH INDEX |
Creates a hash index on a base table. |
CREATE INDEX |
Creates a secondary index on an existing data table or join index. |
CREATE JOIN INDEX |
Creates a join index for one or multiple tables, optionally with aggregation. |
CREATE MACRO |
Defines a set of statements that are frequently used or that perform a complex operation. The statements in the macro body are submitted when the macro is invoked by a subsequent EXECUTE statement. |
CREATE MAP |
Creates a sparse map. |
CREATE METHOD |
Defines the body of a method that is associated with a user-defined data type. |
CREATE ORDERING | Creates a map ordering routine used to compare UDT values. |
CREATE PROCEDURE (External Form) |
Compiles and installs an external SQL procedure routine and creates or replaces the SQL definition used to invoke the procedure. |
CREATE PROCEDURE (SQL Form) |
Directs the SQL procedure compiler to create a procedure from the SQL statements in the remainder of the source text and creates the SQL definition used to invoke the procedure. |
CREATE PROFILE |
Creates a profile that defines a set of parameters. You can then assign the profile to multiple users. |
CREATE RECURSIVE VIEW |
Creates or replaces a recursive view definition. |
CREATE ROLE |
Creates a role for managing user access privileges on database objects. A role is a shell database object to which sets of privileges can be granted using GRANT requests. |
CREATE storage_format SCHEMA |
Creates a schema for a specific storage format of the DATASET type. |
CREATE TABLE and CREATE TABLE … AS |
Defines the column names, column data types and attributes, primary and secondary indexes, column- and table-constraints, partitioning, and other attributes of a new table. The CREATE TABLE AS form copies column definitions to a new table. Optionally, data and statistics are copied to the new table. |
CREATE TABLE (Queue Table Form) |
Creates a queue table. |
CREATE TRANSFORM |
Creates transform groups for importing UDT data from a client system to Vantage and exporting UDT data from Vantage to a client system. You can use CREATE TRANSFORM to add transform groups in addition to the existing transform groups. |
CREATE TRIGGER |
Creates a new trigger definition. |
CREATE TYPE (ARRAY/VARRAY Form) |
Creates a user-defined ARRAY or VARRAY data type that is constructed from a predefined Vantage data type, a distinct UDT data type, a structured UDT data type, or an internal UDT data type. |
CREATE TYPE (Distinct Form) | Creates a user-defined data type that is constructed directly from a predefined Vantage data type. |
CREATE TYPE (Structured Form) |
Creates the body of a structured user-defined data type. |
CREATE USER |
Creates a permanent database user object, in which other database objects may be created, with a mandatory permanent space allocation and a mandatory password plus optional attributes. |
CREATE VIEW |
Creates a view on a set of tables or views or both. |
CREATE ZONE |
Defines a secure zone. You can also specify an existing database or user as the zone root. |
DATABASE |
Establishes a new default database for the current session for SQL requests that do not have fully-qualified table, view, or macro names. |
DELETE DATABASE |
Deletes all data tables, views, triggers, SQL procedures, macros, and user-installed files (UIFs) from a database. |
DELETE USER |
Deletes all data tables, views, triggers, SQL procedures, macros, and user-installed files (UIFs) from a user. |
DROP AUTHORIZATION |
Drops an authorization object. |
DROP CAST |
Drops a cast definition for a UDT from the data dictionary. |
DROP CONSTRAINT |
Drops the definition of a row-level security constraint object from the data dictionary. |
DROP DATABASE |
Drops the definition for an empty database from the Data Dictionary. |
DROP ERROR TABLE |
Deletes the specified error table object definition from the Data Dictionary and from the containing database or user. |
DROP FUNCTION |
Drops the definition of the specified function or specific function from the Data Dictionary and from the containing database or user. |
DROP FUNCTION MAPPING |
Drops a function mapping. |
DROP GLOP SET |
Drops the definition of the specified GLOP set from the Data Dictionary. |
DROP HASH INDEX |
Drops a hash index on a table. |
DROP INDEX |
Drops a secondary index on a table or join index. |
DROP JOIN INDEX |
Drops the join index. |
DROP MACRO |
Drops the definition for the specified macro from the dictionary. |
DROP MAP |
Drop a contiguous or sparse map. |
DROP ORDERING |
Drops the ordering definition for a UDT. |
DROP PROCEDURE |
Drops the definition for the specified procedure from the Data Dictionary and from the containing database or user. |
DROP PROFILE |
Drops a specified profile. |
DROP ROLE |
Drops a specified role. |
DROP storage_format SCHEMA |
Drops a schema. |
DROP STATISTICS (Optimizer Form) |
Drops the statistical data that was collected for specified columns of a table, hash index, or join index by a COLLECT STATISTICS (Optimizer Form) request. To drop the SUMMARY statistics for a database object, drop all statistics on that object. |
DROP TABLE |
Drops the definition for a specified table from the Data Dictionary and from the containing database or user, depending on the keyword specified. |
DROP TRANSFORM |
Drop transform groups for a specified UDT. |
DROP TRIGGER |
Drops the definition for the specified trigger from its subject table. |
DROP TYPE |
Drops the definition for a specified UDT. |
DROP USER |
Drops the definition for an empty user from the Data Dictionary. |
DROP VIEW |
Drops the definition of a specified view from the Data Dictionary. |
DROP ZONE |
Removes a secure zone. |
END ISOLATED LOADING |
Ends the explicit concurrent isolated load operation for the specified load group value. |
END LOGGING |
Ends the auditing of SQL requests that was started with a BEGIN LOGGING request. |
END QUERY CAPTURE |
Stops the capture of SQL requests initiated by a BEGIN QUERY CAPTURE request for the session. |
END QUERY LOGGING |
Stops the logging of SQL requests initiated by a BEGIN QUERY LOGGING request and commits the query log cache. |
FLUSH QUERY LOGGING |
Flushes one, several, or all DBQL caches or workload management caches to disk. |
HELP CAST |
Returns the available cast operations for the specified UDT. |
HELP COLUMN |
Displays the attributes of a column, including whether it is a single-column primary or secondary index and, if so, whether it is unique. |
HELP CONSTRAINT |
Displays the attributes for a specific named constraint on a table. Use the SHOW TABLE statement to obtain unnamed constraint information. See “SHOW object.” |
HELP DATABASE |
Displays the attributes, sorted by object name, for all tables, views, join indexes, hash indexes, SQL procedures, user-defined functions, and macros contained by a specified database. |
HELP ERROR TABLE |
Displays the attributes for a specified error table. |
HELP FUNCTION |
Reports the specific function name, list of parameters, the data types of the parameters, whether the function is used to compress or decompress character or graphic data, and any comments associated with the parameters for SQL, scalar, aggregate, and table functions. |
HELP HASH INDEX |
Displays the data types of the columns defined by a particular hash index. |
HELP INDEX |
Displays the attributes for the primary and secondary indexes defined for a base data table, hash index, or join index. |
HELP JOIN INDEX |
Displays the attributes of the columns defined by a particular join index. |
HELP MACRO |
Displays the attributes for the specified macro. |
HELP METHOD |
Displays the parameter list of the specified method. |
HELP ONLINE |
Displays syntax help for any SQL statement or client utility command. |
HELP PROCEDURE |
Displays the attribute and format parameters for each parameter of a procedure or just the creation time attributes for the specified procedure. |
HELP SESSION |
Displays attribute information for the user of the current session or just the row-level constraint attribute information for the user of the current session. |
HELP STATISTICS (Optimizer Form) |
Displays the attribute and format parameters for each parameter of a procedure or just the creation time attributes for the specified procedure. |
HELP STATISTICS (QCD Form) |
Displays summary or detailed attributes for the statistics that have been collected in the TableStatistics table of a specified query capture database (QCD) for a specified table. |
HELP storage_format SCHEMA |
Displays the name of a schema, the storage format of the DATASET type associated with the schema, and the length of the schema. The length is listed in UNICODE characters. |
HELP TABLE |
Displays the attributes for a specified base data table. |
HELP TRANSFORM |
Reports the fromsql and tosql transform routines for a specified UDT or predefined data type. |
HELP TRIGGER |
Displays the attributes for a specified trigger. |
HELP TYPE |
Displays the attributes for a UDT and, optionally, all the attributes of a structured UDT or all the methods associated with the UDT. |
HELP USER |
Displays the attributes, sorted by object name, for all tables, views, join indexes, hash indexes, SQL procedures, user-defined functions, and macros contained by the specified user. |
HELP VIEW |
Displays the attributes for the specified view or recursive view. |
HELP VOLATILE TABLE |
Displays the attributes for the requested volatile table. |
HELP ONLINE |
Displays syntax help for any SQL statement or client utility command. |
INCREMENTAL RESTORE ALLOW WRITE FOR object_list | Enables read and write access for the databases, users, or tables you specify after an incremental restore. Incremental restore sets tables to read-only access. |
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list | Disables incremental restore for databases and tables you specify. |
LOGGING INCREMENTAL ARCHIVE ON FOR object_list | Enables incremental restore for databases and tables you specify. |
MODIFY DATABASE |
Changes the parameters for the specified database. |
MODIFY PROFILE |
Changes the parameters for the specified profile. |
MODIFY USER |
Changes parameters assigned to the specified user. |
RENAME FUNCTION (External Form) |
Renames either the overloaded calling function name or specific function name for an external function. |
RENAME FUNCTION (SQL Form) |
Renames either the overloaded calling function name or specific function name for an SQL function. |
RENAME MACRO |
Renames an existing macro. |
RENAME PROCEDURE |
Renames an existing SQL procedure. |
RENAME TABLE |
Renames an existing table. |
RENAME TRIGGER |
Renames an existing trigger. |
RENAME VIEW |
Renames an existing view. |
REPLACE AUTHORIZATION |
Creates or replaces an authorization object. |
REPLACE CAST |
Replaces a cast operation for a UDT. |
REPLACE FUNCTION (External Form) |
Compiles and installs an external UDF and creates or replaces the SQL function definition used to invoke that UDF. |
REPLACE FUNCTION (SQL Form) |
Creates or replaces an SQL UDF. |
REPLACE FUNCTION MAPPING | Replaces the definition of an existing function mapping or, if the specified function mapping does not exist, creates a new function mapping by that name. |
REPLACE MACRO |
REPLACE MACRO redefines an existing macro. If the specified macro does not exist, REPLACE MACRO creates a new macro with that name. |
REPLACE METHOD |
Creates or replaces an existing method definition. |
REPLACE ORDERING |
Creates or replaces a map ordering routine used to compare UDT values. |
REPLACE PROCEDURE (External Form) |
Compiles and installs an external SQL procedure routine and creates or replaces the SQL definition used to invoke the procedure. |
REPLACE PROCEDURE (SQL Form) |
Directs the SQL procedure compiler to create or replace a procedure from the SQL statements in the remainder of the source text and creates the SQL definition used to invoke the procedure. |
REPLACE QUERY LOGGING |
Creates a rule or replaces the current rule with the rule you specify. |
REPLACE RECURSIVE VIEW |
Creates or replaces a recursive view definition. |
REPLACE TRANSFORM | Creates or replaces transform groups for importing UDT data from a client system to Vantage and exporting UDT data from Vantage to a client system. You can use CREATE TRANSFORM to add transform groups in addition to the existing transform groups. REPLACE TRANSFORM drops all of the existing transform groups and adds the transform groups you specify. |
REPLACE TRIGGER |
Creates a new trigger or replaces a trigger definition. |
REPLACE VIEW |
Creates or replaces a view on a set of tables or views or both. |
SET QUERY_BAND |
Sets or removes a query band for the current session or transaction. |
SET ROLE |
Sets the current role for a session. It does not distinguish between directory- and database-managed roles. |
SET SESSION |
Allows the setting of various session parameters for the entire session or individual requests within the session. |
SET SESSION ACCOUNT |
Dynamically changes your account or account priorities for the duration of a session or for one SQL request only. |
SET SESSION CALENDAR |
Sets the default calendar for the session to a system-defined calendar. |
SET SESSION CHARACTER SET UNICODE PASS THROUGH |
Enables or disables Unicode Pass Through processing for the session. Pass Through Characters (PTCs) include the Unicode characters that are not currently supported and character codes reserved for future use. |
SET SESSION COLLATION |
Changes the collation sequence for the current session. |
SET SESSION CONSTRAINT |
Overrides the default constraints assigned to a user for the current session. |
SET SESSION DATABASE |
Changes the default database for the session. |
SET SESSION DATEFORM |
Changes the default DATE format in field mode and the default format for importing and exporting DATE values for the session. |
SET SESSION DEBUG FUNCTION |
Identifies the UDF or stored procedure to be run in debug mode the next time the UDF is invoked. |
SET SESSION DOT NOTATION |
Sets the session response for dot notation query results that return a list of values. |
SET SESSION FOR ISOLATED LOADING |
Enables or disables isolated loading for DML operations for the session. |
SET SESSION FUNCTION TRACE |
Enables function trace output for debugging external user-defined functions and external SQL procedures. |
SET SESSION JSON IGNORE ERRORS |
Enables or disables the validation of JSON data on INSERT operations. |
SET SESSION SEARCHUIFDBPATH |
Sets the database search path for the SCRIPT execution in the SessionTbl.SearchUIFDBPath column. |
SET SESSION TRANSACTION ISOLATION LEVEL |
Changes the default transaction isolation level read-only semantics for the current session. |
SET TIME ZONE |
Changes the default time zone displacement for a session. |
SET TRANSFORM GROUP FOR TYPE |
Sets the active transform group for Complex Data Types (CDTs) that have multiple transform groups. |
SHOW FUNCTION MAPPING |
Displays the SQL data definition text for the function mapping. |
SHOW MAP |
Returns the equivalent CREATE MAP statement in SQL or, optionally, XML. |
SHOW object |
For tables, macros and views, displays the SQL data definition text for the original create text from DBC.TVM.RequestText. |
SHOW QUERY LOGGING |
Returns the query logging rule set applied to the specified user, database, user:account set, application set, or all users from the rules cache or from DBC.DBQLRuleTbl. |
SHOW request |
Displays the DDL for all database objects referenced by a specified DML request. |
SHOW STATISTICS |
Reports the SQL text for the Optimizer and QCD forms of COLLECT STATISTICS requests that collected the statistics and optionally reports the detailed or summary statistics. |
SHOW TABLE | Displays the most recent SQL create text. |