Google Git
Sign in
apache / trafodion / 76987d15200ef917714f6482be69b131ffd6a9f2 / . / docs / sql_reference / src / asciidoc / _chapters / sql_statements.adoc
blob: 24aba0d6161de21440c6627f94c436d0124f17aa [file] [log] [blame]
////
/**
* @@@ START COPYRIGHT @@@
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*
* @@@ END COPYRIGHT @@@
*/
////
[[sql_statements]]
= SQL Statements
This section describes the syntax and semantics of {project-name} SQL statements.
{project-name} SQL statements are entered interactively or from script files using a client-based tool, such as the
{project-name} Command Interface (TrafCI). To install and configure a client application that enables you to connect
to and use a {project-name} database, see the
{docs-url}/client_install/index.html[_{project-name} Client Installation_ _Guide_].
[[sql_statements_categories]]
== Categories
The statements are categorized according to their functionality:
* <<data_definition_language_statements,Data Definition Language (DDL) Statements>>
* <<data_manipulation_language_statements,Data Manipulation Language (DML) Statements>>
* <<transaction_control_statements,Transaction Control Statements>>
* <<data_control_and_security_statements,Data Control and Security Statements>>
* <<stored_procedure_and_user_defined_function_statements,Stored Procedure and User-Defined Function Statements>>
* <<prepared_statements,Prepared Statements>>
* <<control_statements,Control Statements>>
* <<object_naming_statements,Object Naming Statements>>
* <<show_get_and_explain_statements,"SHOW, GET, and EXPLAIN Statements">>
<<<
[[data_definition_language_statements]]
=== Data Definition Language (DDL) Statements
Use these DDL statements to create, drop, or alter the definition of a {project-name} SQL schema or object.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run DDL statements inside a user-defined
transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run these statements, AUTOCOMMIT must be turned ON
(the default) for the session.
[cols="2*", options="head{docs-url}/sql_reference/index.html#limitser"]
|===
| Statement | What It Does
// | <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a {project-name} database.
| <<alter_table_statement,ALTER TABLE Statement>> | Changes attributes for a table.
| <<alter_user_statement,ALTER USER Statement>> | Changes attributes for a user.
| <<create_function_statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within a {project-name} database.
| <<create_index_statement,CREATE INDEX Statement>> | Creates an index on a table.
| <<create_library_statement,CREATE LIBRARY Statement>> | Registers a library object in a {project-name} database.
| <<create_procedure_statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a {project-name} database.
| <<create_role_statement,CREATE ROLE Statement>> | Creates a role.
| <<create_schema_statement,CREATE SCHEMA Statement>> | Creates a schema in the database.
| <<create_table_statement,CREATE TABLE Statement>> | Creates a table.
| <<create_view_statement,CREATE VIEW Statement>> | Creates a view.
| <<drop_function_statement,DROP FUNCTION Statement>> | Removes a user-defined function (UDF) from the {project-name} database.
| <<drop_index_statement,DROP INDEX Statement>> | Drops an index.
| <<drop_library_statement,DROP LIBRARY Statement>> | Removes a library object from the {project-name} database and also removes the library file
referenced by the library object.
| <<drop_procedure_statement,DROP PROCEDURE Statement>> | Removes a stored procedure in Java (SPJ) from the {project-name} database.
| <<drop_role_statement,DROP ROLE Statement>> | Drops a role.
| <<drop_schema_statement,DROP SCHEMA Statement>> | Drops a schema from the database.
| <<drop_table_statement,DROP TABLE Statement>> | Drops a table.
| <<drop_view_statement,DROP VIEW Statement>> | Drops a view.
| <<register_user_statement,REGISTER USER Statement>> | Registers a user in the SQL database, associating the user's login name
with a database user name.
| <<unregister_user_statement, UNREGISTER USER Statement>> | Removes a database user name from the SQL database.
|===
<<<
[[data_manipulation_language_statements]]
=== Data Manipulation Language (DML) Statements
Use these DML statements to delete, insert, select, or update rows in one or more tables:
[cols="2*", options="header"]
|===
| Statement | What It Does
| <<delete_statement,DELETE Statement>> | Deletes rows from a table or view.
| <<insert_statement,INSERT Statement>> | Inserts data into tables and views.
| <<merge_statement,MERGE Statement>> | Either performs an upsert operation (that is, updates a table if the row
exists or inserts into a table if the row does not exist) or updates (merges) matching rows from one table to another.
| <<select_statement,SELECT Statement>> | Retrieves data from tables and views.
| <<table_statement,TABLE Statement>> | Equivalent to the query specification SELECT * FROM _table_
| <<update_statement,UPDATE Statement>> | Updates values in columns of a table or view.
| <<upsert_statement,UPSERT Statement>> | Updates a table if the row exists or inserts into a table if the row does not exist.
| <<values_statement,VALUES Statement>> | Displays the results of the evaluation of the expressions and the results of row subqueries
within the row value constructors.
|===
[[transaction_control_statements]]
=== Transaction Control Statements
Use these statements to specify user-defined transactions and to set attributes for the next transaction:
[cols="2*",options="header"]
|===
| Statement | What It Does
| <<begin_work_statement,BEGIN WORK Statement>> | Starts a transaction.
| <<commit_work_statement,COMMIT WORK Statement>> | Commits changes made during a transaction and ends the transaction.
| <<rollback_work_statement,ROLLBACK WORK Statement>> | Undoes changes made during a transaction and ends the transaction.
| <<set_transaction_statement,SET TRANSACTION Statement>> | Sets attributes for the next SQL transaction — whether to automatically
commit database changes.
|===
<<<
[[data_control_and_security_statements]]
=== Data Control and Security Statements
Use these statements to register users, create roles, and grant and revoke privileges:
[cols="2*",options="header"]
|===
| Statement | What It Does
| <<alter_user_statement,ALTER USER Statement>> | Changes attributes associated with a user who is registered in the database.
| <<create_role_statement,CREATE ROLE Statement>> | Creates an SQL role.
| <<drop_role_statement,DROP ROLE Statement>> | Deletes an SQL role.
| <<grant_statement,GRANT Statement>> | Grants access privileges on an SQL object or an SQL object's columns to specified users or roles.
| <<grant_component_privilege_statement,GRANT COMPONENT PRIVILEGE Statement>> | Grants one or more component privileges to a user or role.
| <<grant_role_statement,GRANT ROLE Statement>> | Grants one or more roles to a user.
| <<register_user_statement,REGISTER USER Statement>> | Registers a user in the SQL database, associating the user's login name with a database user name.
| <<revoke_statement,REVOKE Statement>> | Revokes access privileges on an SQL object from specified users or roles.
| <<revoke_component_privilege_statement,REVOKE COMPONENT PRIVILEGE Statement>> | Removes one or more component privileges from a user or role.
| <<revoke_role_statement,REVOKE ROLE Statement>> | Removes one or more roles from a user.
| <<unregister_user_statement,UNREGISTER USER Statement>> | Removes a database user name from the SQL database.
|===
<<<
[[stored_procedure_and_user_defined_function_statements]]
=== Stored Procedure and User-Defined Function Statements
Use these statements to create and execute stored procedures in Java (SPJs) or create user-defined functions (UDFs) and to modify
authorization to access libraries or to execute SPJs or UDFs:
[cols="2*",options="header"]
|===
| Statement | What It Does
// | <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a {project-name} database.
| <<call_statement,CALL Statement>> | Initiates the execution of a stored procedure in Java (SPJ) in a {project-name} database.
| <<create_function_statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within a {project-name} database.
| <<create_library_statement,CREATE LIBRARY Statement>> | Registers a library object in a {project-name} database.
| <<create_procedure_statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a {project-name} database.
| <<drop_function_statement,DROP FUNCTION Statement>> | Removes a user-defined function (UDF) from the {project-name} database.
| <<drop_library_statement,DROP LIBRARY Statement>> | Removes a library object from the {project-name} database and also removes the library file
referenced by the library object.
| <<drop_procedure_statement,DROP PROCEDURE Statement>> | Removes a stored procedure in Java (SPJ) from the {project-name} database.
| <<grant_statement,GRANT Statement>> | Grants privileges for accessing a library object or executing an SPJ or UDF to specified users.
| <<revoke_statement,REVOKE Statement>> | Revokes privileges for accessing a library object or executing an SPJ or UDF from specified users.
UDF from specified users.
|===
[[prepared_statements]]
=== Prepared Statements
Use these statements to prepare and execute an SQL statement:
[cols="2*",options="header"]
|===
| Statement | What It Does
| <<execute_statement,EXECUTE Statement>> | Executes an SQL statement previously compiled by a PREPARE statement.
| <<prepare_statement,PREPARE Statement>> | Compiles an SQL statement for later use with the EXECUTE statement in the same session.
|===
<<<
[[control_statements]]
=== Control Statements
Use these statements to control the execution, default options, plans, and performance of DML statements:
[cols="2*",options="header"]
|===
| Statement | What It Does
| <<control_query_cancel_statement,CONTROL QUERY CANCEL Statement>> | Cancels an executing query that you identify with a query ID.
| <<control_query_default_statement,CONTROL QUERY DEFAULT Statement>> | Changes a default attribute to influence a query plan.
|===
[[object_naming_statements]]
=== Object Naming Statements
Use this statements to specify default ANSI names for the schema:
[cols="2*",options="header"]
|===
| Statement | What It Does
| <<set_schema_statement,SET SCHEMA Statement>> | Sets the default ANSI schema for unqualified object names for the current session.
|===
<<<
[[show_get_and_explain_statements]]
=== SHOW, GET, and EXPLAIN Statements
Use these statements to display information about database objects or query execution plans:
[cols="2*",options="header"]
|===
| Statement | What It Does
| <<explain_statement,EXPLAIN Statement>> | Displays information contained in the query execution plan.
| <<get_statement,GET Statement>> | Displays the names of database objects, components, component
privileges, roles, or users that exist in the {project-name} instance.
| <<get_hbase_objects_statement,GET HBASE OBJECTS Statement>> | Displays a list of HBase objects through an SQL interface
| <<get_version_of_metadata_statement,GET VERSION OF METADATA Statement>> | Displays the version of the metadata in the {project-name} instance and
indicates if the metadata is current.
| <<get_version_of_software_statement,GET VERSION OF SOFTWARE Statement>> | Displays the version of the {project-name} software that is installed on the
system and indicates if it is current.
| <<invoke_statement,INVOKE Statement>> | Generates a record description that corresponds to a row in the
specified table or view.
| <<showcontrol_statement,SHOWCONTROL Statement>> | Displays the CONTROL QUERY DEFAULT attributes in effect.
| <<showddl_statement,SHOWDDL Statement>> | Describes the DDL syntax used to create an object as it exists in the
metadata, or it returns a description of a user, role, or component in the form of a GRANT statement.
| <<showddl_schema_statement,SHOWDDL SCHEMA Statement>> | Displays the DDL syntax used to create a schema as it exists in the
metadata and shows the authorization ID that owns the schema.
| <<showstats_statement,SHOWSTATS Statement>> | Displays the histogram statistics for one or more groups of columns
within a table. These statistics are used to devise optimized access plans.
|===
////
<<<
[[alter_library_statement]]
== ALTER LIBRARY Statement
The ALTER LIBRARY statement updates the physical filename for a library object in a {project-name} database.
A library object can be an SPJ's JAR file or a UDF's library file.
ALTER LIBRARY is a {project-name} SQL extension.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside
user-defined transaction (`BEGIN WORK&#8230;COMMIT WORK`) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT must be
turned ON (the default) for the session.
```
ALTER LIBRARY [[catalog-name.]schema-name.]library-name
file library-filename
[host name host-name]
[local file host-filename]
```
[[alter_library_syntax]]
=== Syntax Description of ALTER LIBRARY
* `\[[_catalog-name_.]_schema-name_.]_library-name_`
+
specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a maximum of 128 characters.
specify the name of a library object that has already been registered in the schema. if you do not fully qualify the library name, trafodion sql
qualifies it according to the schema of the current session. for more information, see <<identifiers,identifiers>> and
<<_database_object_names,database object names>>.
* `file _library-filename_`
+
specifies the full path of the redeployed library file, which either an SPJ's jar file or a UDF's library file.
* `host name _host-name_`
+
specifies the name of the client host machine where the deployed file resides.
* `local file _host-filename_`
+
specifies the path on the client host machine where the deployed file is stored.
<<<
[[alter_library_considerations]]
=== Considerations for ALTER LIBRARY
* HOST NAME and LOCAL FILE are position dependent.
==== Required Privileges
To issue an ALTER LIBRARY statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the library.
* You have the ALTER or ALTER_LIBRARY component privilege for the SQL_OPERATIONS component.
[[alter_library_examples]]
=== Examples of ALTER LIBRARY
* This ALTER LIBRARY statement updates the JAR file (SPJs) for a library named SALESLIB in the SALES schema:
+
```
ALTER LIBRARY sales.saleslib FILE Sales2.jar;`
```
* This ALTER LIBRARY statement updates the library file (UDFs) for a library named MYUDFS in the default schema:
+
```
ALTER LIBRARY myudfs FILE $TMUDFLIB;
```
////
<<<
[[alter_sequence_statement]]
== ALTER SEQUENCE Statement
The ALTER SEQUENCE Statement changes the future behavior of exiting sequence, including INCREMENT, MAXVALUE, CACHE and CYCLE.
Multiple options can be concurrently specified and separated by a space. Any options not altered will
retain their previous settings.
ALTER SEQUENCE is a Trafodion SQL extension.
For more inforamation, see <<create_sequence_statement,CREATE SEQUENCE Statement>> or <<drop_sequence_statement,DROP SEQUENCE Statement>>.
```
ALTER SEQUENCE [[catalog-name.]schema-name.]sequence
[INCREMENT BY integer]
[MAXVALUE integer | NO MAXVALUE]
[CACHE integer]
[CYCLE | NO CYCLE]
```
<<<
[[alter_sequence_syntax]]
=== Syntax Description of ALTER SEQUENCE
The options above serve the same purposes as they serve when you create a sequence. For more information, see <<create_sequence_statement,CREATE SEQUENCE Statement>>.
NOTE:
* START WITH, MINVALUE, NO CACHE and DATA TYPE cannot be changed. Instead, drop and recreate the sequence specifying the desired options.
* The unused preallocated values will be lost if a sequence is altered.
* Some sequence values will be skipped if you change the INCREMENT BY before the first reference to `seqnum(SEQUENCE, next)`.
<<<
[[alter_sequence_considerations]]
=== Considerations for ALTER SEQUENCE
To issue an ALTER SEQUENCE statement, one of the following must be true:
* You are DB ROOT.
* You have the ALTER or ALTER_SEQUENCE component privilege for the SQL_OPERATIONS component.
<<<
[[alter_sequence_examples]]
=== Examples of ALTER SEQUENCE
The following statement changes the MAXVALUE and CACHE for the sequence named `employee_seq`, which is created in <<create_sequence_examples,Examples of CRAETE SEQUENCE>>.
```
ALTER SEQUENCE employee_seq
MAXVALUE 20000
CACHE 30
```
<<<
[[alter_table_statement]]
== ALTER TABLE Statement
The ALTER TABLE statement changes a {project-name} SQL table. See <<Tables,Tables>>.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this
statement inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF.
To run this statement, AUTOCOMMIT must be turned ON (the default) for the session.
```
ALTER TABLE table-name alter-action
alter-action is:
ADD [IF NOT EXISTS][COLUMN] column-definition
| ADD [CONSTRAINT constraint-name] table-constraint
| ENABLE ALL [UNIQUE] INDEXES
| ENABLE INDEX index-name
| DISABLE ALL [UNIQUE] INDEXES
| DISABLE INDEX index-name
| DROP CONSTRAINT constraint-name [RESTRICT]
| RENAME TO new-name
| DROP COLUMN [IF EXISTS] column-name
| ALTER COLUMN column-definition
column-definition is:
column-name data-type
([DEFAULT default]
[[constraint constraint-name] column-constraint])
data-type is:
char[acter] [(length)[characters]]
[CHARACTER SET char-set-name]
[UPSHIFT] [[not] casespecific]
| char[acter] varying (length)
[character set char-set-name]
[upshift] [[not] casespecific]
| varchar (length) [character set char-set-name]
[upshift] [[not] casespecific]
| varchar2 (length) [character set char-set-name]
[upshift] [[not] casespecific]
| numeric [(precision [,scale])] [signed|unsigned]
| nchar [(length) [character set char-set-name]
[upshift] [[not] casespecific]
| nchar varying(length) [character set char-set-name]
[upshift] [[not] casespecific]
| smallint [signed|unsigned]
| int[eger] [signed|unsigned]
| largeint
| dec[imal] [(precision [,scale])] [signed|unsigned]
| float [(precision)]
| real
| double precision
| date
| time [(time-precision)]
| timestamp [(timestamp-precision)]
| interval { start-field to end-field | single-field }
| blob [({numeric literal} [unit])] [STORAGE 'storage literal']
| clob [({numeric literal} [unit])] [STORAGE 'storage literal']
default is:
literal
| null
| currentdate
| currenttime
| currenttimestamp }
column-constraint is:
not null
| unique
| check (condition)
| references ref-spec
table-constraint is:
unique (column-list)
| check (condition)
| foreign key (column-list) references ref-spec
ref-spec is:
referenced-table [(column-list)]
column-list is:
column-name[, column-name]...
```
<<<
[[alter_table_syntax]]
=== Syntax Description of ALTER TABLE
* `_name_`
+
specifies the current name of the object. See <<database_object_names,Database Object Names>>.
* `ADD [COLUMN] _column-definition_`
+
adds a column to _table_.
+
The clauses for the _column-definition_ are:
** `_column-name_`
+
specifies the name for the new column in the table. _column-name_ is an SQL identifier. _column-name_ must be
unique among column names in the table. If the column name is a {project-name} SQL reserved word, you must
delimit it by enclosing it in double quotes. For example: `"sql".myview`. See <<Identifiers,Identifiers>>.
** `_data-type_`
+
specifies the data type of the values that can be stored in _column-name_. See <<Data_Types,Data Types>>
If a default is not specified, NULL is used.
** `DEFAULT _default_`
+
specifies a default value for the column or specifies that the column does not have a default value. You can declare the default value
explicitly by using the DEFAULT clause, or you can enable null to be used as the default by omitting both the DEFAULT and NOT NULL clauses.
If you omit the DEFAULT clause and specify NOT NULL, {project-name} SQL returns an error. For existing rows of the table, the added column takes
on its default value.
+
If you set the default to the datetime value CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, {project-name} SQL uses January 1, 1 A.D.
12:00:00.000000 as the default date and time for the existing rows.
+
For any row that you add after the column is added, if no value is specified for the column as part of the add row operation, the column
receives a default value based on the current timestamp at the time the row is added.
<<<
** `[[constraint _constraint-name_] _column-constraint_]`
+
specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be
unique among constraint names in its schema. if you omit the schema portions of the name you specify in _constraint-name_,
trafodion sql expands the constraint name by using the schema for _table_. see <<database_object_names,database object names>>.
+
if you do not specify a constraint name, trafodion sql constructs an sql identifier as the name for the constraint in the schema
for _table._ the identifier consists of the fully qualified table name concatenated with a system-generated unique identifier.
for example, a constraint on table a.b.c might be assigned a name such as a.b.c_123&#8230;_01&#8230;.
*** `_column-constraint_` options:
**** `not null`
+
is a column constraint that specifies that the column cannot contain nulls. if you omit not null, nulls are allowed in the column.
if you specify both not null and no default, then each row inserted in the table must include a value for the column. see <<null,null>>.
**** `unique`
+
is a column constraint that specifies that the column cannot contain more than one occurrence of the same value. if you omit unique,
duplicate values are allowed unless the column is part of the primary key. columns that you define as unique must be specified as not null.
**** `check (_condition_)`
+
is a constraint that specifies a condition that must be satisfied for each row in the table. see <<search_condition,search condition>>.
you cannot refer to the current_date, current_time, or current_timestamp function in a check constraint, and you cannot use
subqueries in a check constraint.
<<<
**** `references _ref-spec_`
+
specifies a references column constraint. the maximum combined length of the columns for a references constraint is 2048 bytes. +
***** `_ref-spec_` is:
+
`_referenced-table_ [(_column-list_)]`
+
`_referenced-table_` is the table referenced by the foreign key in a referential constraint. _referenced-table_ cannot be a view.
_referenced-table_ cannot be the same as _table_. _referenced-table_ corresponds to the foreign key in the _table_.
+
`_column-list_` specifies the column or set of columns in the _referenced-table_ that corresponds to the foreign key in _table_. the
columns in the column list associated with references must be in the same order as the columns in the column list associated with foreign
key. if _column-list_ is omitted, the referenced table's primary key columns are the referenced columns.
+
a table can have an unlimited number of referential constraints, and you can specify the same foreign key in more than one referential
constraint, but you must define each referential constraint separately. you cannot create self-referencing foreign key constraints.
* `add [constraint _constraint-name_] _table-constraint_`
+
adds a constraint to the table and optionally specifies _constraint-name_ as the name for the constraint. the new constraint
must be consistent with any data already present in the table.
<<<
** `constraint _constraint-name_`
+
specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be unique among constraint
names in its schema. if you omit the schema portions of the name you specify in _constraint-name_, trafodion sql expands the constraint
name by using the schema for table. see <<database_object_names,database object names>>.
+
if you do not specify a constraint name, trafodion sql constructs an sql identifier as the name for the constraint in the schema for table. the
identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. for example, a constraint on table
a.b.c might be assigned a name such as a.b.c_123&#8230;_01&#8230;.
+
** `_table-constraint_` options:
*** `unique (_column-list_)`
+
is a table constraint that specifies that the column or set of columns cannot contain more
than one occurrence of the same value or set of values.
+
`_column-list_` cannot include more than one occurrence of the same column. in addition, the set of columns that you specify on a unique
constraint cannot match the set of columns on any other unique constraint for the table or on the primary key constraint for the table.
all columns defined as unique must be specified as not null.
+
a unique constraint is enforced with a unique index. if there is already a unique index on _column-list_, trafodion sql uses that index. if a
unique index does not exist, the system creates a unique index.
*** `check (_condition_)`
+
is a constraint that specifies a condition that must be satisfied for each row in the table.
see <<search_condition,search condition>>. you cannot refer to the current_date, current_time, or current_timestamp function in a check
constraint, and you cannot use subqueries in a check constraint.
*** `foreign key (_column-list_) references _ref-spec_ not enforced`
+
is a table constraint that specifies a referential constraint for the table, declaring that a column or set of columns (called a foreign key)
in _table_ can contain only values that match those in a column or set of columns in the table specified in the references
clause. however, because not enforced is specified, this relationship is not checked.
+
the two columns or sets of columns must have the same characteristics (data type, length, scale, precision). without the foreign key clause,
the foreign key in _table_ is the column being defined; with the foreign key clause, the foreign key is the column or set of columns specified in
the foreign key clause. for information about _ref-spec_, see references _ref-spec_ not enforced.
<<<
* ENABLE ALL [UNIQUE] INDEXES
** ENABLE ALL UNIQUE INDEXES
+
enables all unique indexes on the table.
** ENABLE ALL INDEXES
+
enables all indexes (including both non-unique and unique indexes) on the table.
* _index-name_
+
is an SQL identifier that specifies the simple name for the index. You cannot qualify index-name with its schema name.
Indexes have their own name space within a schema,
so an index name might be the same as a table or constraint name. However, no two indexes in a schema can have the same name.
* ENABLE INDEX _index-name_
+
enables an index on the table.
* DISABLE ALL [UNIQUE] INDEXES
** DISABLE ALL UNIQUE INDEXES
+
disables all unique indexes on the table.
** DISABLE ALL INDEXES
+
disables all indexes (including both non-unique and unique indexes) on the table.
* DISABLE INDEX _index-name_
+
disables an index on the table.
The index still exists in the data, but it will not
be chosen by the optimizer as an access path.
<<<
* `drop constraint _constraint-name_ [restrict]`
+
drops a constraint from the table. +
+
if you drop a constraint, trafodion sql drops its dependent index if trafodion sql originally created the same index. if the constraint uses
an existing index, the index is not dropped. +
** `constraint _constraint-name_`
+
specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be unique among constraint
names in its schema. if you omit the schema portions of the name you specify in _constraint-name_, trafodion sql expands the constraint
name by using the schema for table. see <<database_object_names,database object names>>.
+
if you do not specify a constraint name, trafodion sql constructs an sql identifier as the name for the constraint in the schema for table. the
identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. for example, a constraint on table
a.b.c might be assigned a name such as a.b.c_123&#8230;_01&#8230;.
* `rename to _new-name_`
+
changes the logical name of the object within the same schema.
** `_new-name_`
+
specifies the new name of the object after the rename to operation occurs.
<<<
* `add if not exists _column-definition_`
+
adds a column to _table_ if it does not already exist in the table.
+
the clauses for the _column-definition_ are the same as described in add [column] _column-definition_.
* `drop column [if exists] _column-name_`
+
drops the specified column from _table_, including the column’s data. you cannot drop a primary key column.
* `alter column _column-definition_`
+
alters the data-type of a column in an existing Trafodion table. The following conditions have to be met, else SQL error 1404 will be returned.
** the old (existing) column datatype and new column datatype must be VARCHAR (variable length string datatype).
** new column length must be greater than or equal to old column length.
** old and new character sets must be the same.
** altered columns cannot be part of primary key or secondary index key.
** table cannot be a VOLATILE table.
<<<
[[alter_table_considerations]]
=== Considerations for ALTER TABLE
[[effect_of_adding_a_column_on_view_definitions]]
==== Effect of Adding a Column on View Definitions
The addition of a column to a table has no effect on existing view definitions. Implicit column references specified by SELECT * in view
definitions are replaced by explicit column references when the definition clauses are originally evaluated.
[[authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
ALTER TABLE works only on user-created tables.
===== Required Privileges
To issue an ALTER TABLE statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the table.
* You have the ALTER or ALTER_TABLE component privilege for the SQL_OPERATIONS component.
===== Privileges Needed to Create a Referential Integrity Constraint
To create a referential integrity constraint (that is, a constraint on the table that refers to a column in another table), one of the
following must be true:
* You are DB ROOT.
* You are the owner of the referencing and referenced tables.
* You have these privileges on the referencing and referenced table:
** For the referencing table, you have the ALTER or ALTER_TABLE component privilege for the SQL_OPERATIONS component.
** For the referenced table, you have the REFERENCES (or ALL) privilege on the referenced table through your user name or through a granted role.
If the constraint refers to the other table in a query expression, you must also have SELECT privileges on the other table.
[[alter_table_examples]]
=== Example of ALTER TABLE
* This example adds a column:
+
```
ALTER TABLE persnl.project
ADD COLUMN projlead
NUMERIC (4) UNSIGNED
```
* This example alters a column of an existing table – showing both positive and negative cases:
+
```
>>INVOKE T;
-- Definition of Trafodion table TRAFODION.SEABASE.T
-- Definition current Wed Jul 8 01:28:40 2015
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, A INT NO DEFAULT NOT NULL NOT DROPPABLE
, B VARCHAR(30) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL
)
--- SQL operation complete.
>>ALTER TABLE T ALTER COLUMN B VARCHAR(40);
--- SQL operation complete.
>>INVOKE T;
-- Definition of Trafodion table TRAFODION.SEABASE.T
-- Definition current Wed Jul 8 01:29:03 2015
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, A INT NO DEFAULT NOT NULL NOT DROPPABLE
, B VARCHAR(40) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL
)
--- SQL operation complete.
>>ALTER TABLE T ALTER COLUMN B VARCHAR(30);
*** ERROR[1404] Datatype for column B cannot be altered.
--- SQL operation failed with errors.
```
* This example alters the name of the column:
+
```
Alter TABLE PRODUCT
ALTER COLUMN vend_id RENAME TO cstm_id;
```
* The following table _orders_ has two non-unique indexes
(_index_orders1_ and _index_orders2_) and
two unique indexes (_unique_index_orders1_ and _unique_index_orders2_).
+
```
SQL>SHOWDDL orders;
CREATE TABLE TRAFODION.SEABASE.ORDERS
(
ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
, ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
, DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
, SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
, CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
(
ORDER_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
DELIV_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
(
SALESREP ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
CUSTNUM ASC
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
+
Disable one unique index (_unique_index_orders1_) and verify that all non-unique indexes (_index_orders1_ and _index_orders2_)
and the rest unique index (_unique_index_orders2_) are still enabled.
+
```
SQL>ALTER TABLE ORDERS DISABLE INDEX unique_index_orders1;
--- SQL operation complete.
SQL>SHOWDDL orders;
CREATE TABLE TRAFODION.SEABASE.ORDERS
(
ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
, ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
, DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
, SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
, CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
(
ORDER_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
DELIV_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
CUSTNUM ASC
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
+
Disable one non-unique index (_index_orders1_) and verify that the rest non-unique index (_index_orders2_)
and the rest unique index (_unique_index_orders2_) are still enabled.
+
```
SQL>ALTER TABLE ORDERS DISABLE INDEX index_orders1;
--- SQL operation complete.
SQL>SHOWDDL orders;
CREATE TABLE TRAFODION.SEABASE.ORDERS
(
ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
, ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
, DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
, SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
, CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
DELIV_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
CUSTNUM ASC
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
+
Disable all indexes and verify that all of them (including both non-unique and unique indexes) are disabled.
+
```
SQL>ALTER TABLE ORDERS DISABLE ALL INDEXES;
--- SQL operation complete.
SQL>SHOWDDL orders;
CREATE TABLE TRAFODION.SEABASE.ORDERS
(
ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
, ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
, DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
, SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
, CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
+
Enable all indexes and verify the result.
+
```
SQL>ALTER TABLE orders ENABLE ALL INDEXES;
--- SQL operation complete.
SQL>SHOWDDL orders;
CREATE TABLE TRAFODION.SEABASE.ORDERS
(
ORDERNUM NUMERIC(6, 0) DEFAULT NULL NOT SERIALIZED
, ORDER_DATE DATE DEFAULT NULL NOT SERIALIZED
, DELIV_DATE DATE DEFAULT NULL NOT SERIALIZED
, SALESREP NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
, CUSTNUM NUMERIC(4, 0) DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
(
ORDER_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE INDEX INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
DELIV_DATE ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS1 ON TRAFODION.SEABASE.ORDERS
(
SALESREP ASC
)
ATTRIBUTES ALIGNED FORMAT
;
CREATE UNIQUE INDEX UNIQUE_INDEX_ORDERS2 ON TRAFODION.SEABASE.ORDERS
(
CUSTNUM ASC
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.ORDERS TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.
```
<<<
[[alter_user_statement]]
== ALTER USER Statement
The ALTER USER statement changes attributes associated with a user who is registered in the database.
ALTER USER is a {project-name} SQL extension.
```
ALTER USER database-username alter-action[, alter-action]
alter-action is:
SET EXTERNAL NAME directory-service-username
| SET { ONLINE | OFFLINE }
```
[[alter_user_syntax]]
=== Syntax Description of ALTER USER
* `_database-username_`
+
is the name of a currently registered database user.
* `SET EXTERNAL NAME`
+
changes the name that identifies the user in the directory service. This is also the name the user specifies when
connecting to the database.
** `_directory-service-username_`
+
specifies the new name of the user in the directory service.
** `_directory-service-username_` is a regular or delimited case-insensitive
identifier. See <<Case_Insensitive_Delimited_Identifiers,Case-Insensitive Delimited Identifiers>>.
* `SET { ONLINE | OFFLINE }`
+
changes the attribute that controls whether the user is allowed to connect to the database. +
** `ONLINE`
+
specifies that the user is allowed to connect to the database.
** `OFFLINE`
+
specifies that the user is not allowed to connect to the database.
<<<
[[alter_user_considerations]]
=== Considerations for ALTER USER
Only a user with user administrative privileges (that is, a user who has been granted the MANAGE_USERS component privilege)
can do the following:
* Set the EXTERNAL NAME for any user
* Set the ONLINE | OFFLINE attribute for any user
Initially, DB_ROOT is the only database user who has been granted the MANAGE_USERS component privilege.
[[alter_user_examples]]
=== Examples of ALTER USER
* To change a user's external name:
+
```
ALTER USER ajones SET EXTERNAL NAME "Americas\ArturoJones";
```
* To change a user's attribute to allow the user to connect to the database:
+
```
ALTER USER ajones SET ONLINE;
```
<<<
[[begin_work_statement]]
== BEGIN WORK Statement
The BEGIN WORK statement enables you to start a transaction explicitly—where the transaction consists of the set of operations
defined by the sequence of SQL statements that begins immediately after BEGIN WORK and ends with the next COMMIT or ROLLBACK
statement. See <<Transaction_Management,Transaction Management>>. BEGIN WORK will raise an error if a transaction is currently active.
BEGIN WORK is a {project-name} SQL extension.
```
BEGIN WORK
```
[[begin_work_considerations]]
=== Considerations for BEGIN WORK
BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction.
[[begin_work_examples]]
=== Example of BEGIN WORK
Group three separate statements—two INSERT statements and an UPDATE statement—that update the database within a single transaction:
```
--- This statement initiates a transaction.
BEGIN WORK;
--- SQL operation complete.
INSERT INTO sales.orders VALUES (125, DATE '2008-03-23', DAT '2008-03-30', 75, 7654);
--- 1 row(s) inserted.
INSERT INTO sales.odetail VALUES (125, 4102, 25000, 2);
--- 1 row(s) inserted.
UPDATE invent.partloc SET qty_on_hand = qty_on_hand - 2
WHERE partnum = 4102 AND loc_code = 'G45';
--- 1 row(s) updated.
--- This statement ends a transaction.
COMMIT WORK;
--- SQL operation complete.
```
<<<
[[call_statement]]
== CALL Statement
The CALL statement invokes a stored procedure in Java (SPJ) in a {project-name} SQL database.
```
CALL procedure-ref ([argument-list])
procedure-ref is:
[[catalog-name.]schema-name.]procedure-name
argument-list is:
sql-expression[, sql-expression]...
```
[[call_syntax]]
=== Syntax Description of CALL
* `_procedure-ref_`
+
specifies an ANSI logical name of the form:
+
`\[[_catalog-name_.]_schema-name_.]_procedure-name_`
+
where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, see
<<identifiers,identifiers>> and <<database_object_names,database object names>>.
+
if you do not fully qualify the procedure name, trafodion sql qualifies it according to the schema of the current session.
* `_argument-list_`
+
accepts arguments for in, in-out, or out parameters. the arguments consist of sql expressions, including dynamic parameters,
separated by commas:
+
`_sql-expression_[{, _sql-expression_}&#8230;]`
+
<<<
+
each expression must evaluate to a value of one of these data types:
+
** character value
** date-time value
** numeric value
+
interval value expressions are disallowed in SPJs. for more information, see
<<call_input_parameter_arguments,input parameter arguments>> and
<<call_output_parameter_arguments,output parameter arguments>>.
+
do not specify result sets in the argument list.
[[call_considerations]]
=== Considerations for CALL
[[call_usage_restrictions]]
==== Usage Restrictions
You can use a CALL statement as a stand-alone SQL statement in applications or command-line interfaces,
such as TrafCI. You cannot use a CALL statement inside a compound statement or with row sets.
[[call_required_privileges]]
==== Required Privileges
To issue a CALL statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the stored procedure.
* You have the EXECUTE (or ALL) privileges, either directly through your username or through a granted role.
For more information, see the <<GRANT_Statement,GRANT Statement>>.
When the stored procedure executes, it executes as the {project-name} ID.
<<<
[[call_input_parameter_argument]]
==== Input Parameter Arguments
You pass data to an SPJ by using IN or INOUT parameters. For an IN
parameter argument, use one of these SQL expressions:
* Literal
* SQL function (including CASE and CAST expressions)
* Arithmetic or concatenation operation
* Scalar subquery
* Dynamic parameter (for example, ?) in an application
* Named (for example, ?param) or unnamed (for example, ?) parameter in TrafCI
For an INOUT parameter argument, you can use only a dynamic, named, or unnamed parameter. For more information, see
<<Expressions,Expressions>>.
[[call_output_parameter_arguments]]
==== Output Parameter Arguments
An SPJ returns values in OUT and INOUT parameters. Output parameter arguments must be dynamic parameters in an
application (for example, ?) or named or unnamed parameters in DCI (for example, ?param or ?). Each
calling application defines the semantics of the OUT and INOUT parameters in its environment.
[[call_data_conversion_parameter_arguments]]
==== Data Conversion of Parameter Arguments
{project-name} SQL performs an implicit data conversion when the data type of a parameter argument is compatible with
but does not match the formal data type of the stored procedure. For stored procedure input values,
the conversion is from the actual argument value to the formal parameter type. For stored procedure output values,
the conversion is from the actual output value, which has the data type of the formal parameter, to the declared
type of the dynamic parameter.
[[call_null_input_and_output]]
==== Null Input and Output
You can pass a null value as input to or output from an SPJ, provided that the corresponding Java data type of the
parameter supports nulls. If a null is input or output for a parameter that does not support nulls, {project-name} SQL
returns an error.
<<<
[[call_transaction_semantics]]
==== Transaction Semantics
The CALL statement automatically initiates a transaction if no active transaction exists. However, the failure of
a CALL statement does not always automatically abort the transaction.
[[call_examples]]
=== Examples of CALL
* In TrafCI, execute an SPJ named MONTHLYORDERS, which has one IN parameter represented by a literal and one OUT
parameter represented by an unnamed parameter, ?:
+
```
CALL sales.monthlyorders(3,?);
```
<<<
* This CALL statement executes a stored procedure, which accepts one IN parameter (a date literal), returns one OUT
parameter (a row from the column, NUM_ORDERS), and returns two result sets:
+
```
CALL sales.ordersummary('01/01/2001', ?);
NUM_ORDERS
--------------------
13
ORDERNUM NUM_PARTS AMOUNT ORDER_DATE LAST_NAME
---------- -------------- --------------- ---------- ------------------
100210 4 19020.00 2006-04-10 HUGHES
100250 4 22625.00 2006-01-23 HUGHES
101220 4 45525.00 2006-07-21 SCHNABL
200300 3 52000.00 2006-02-06 SCHAEFFER
200320 4 9195.00 2006-02-17 KARAJAN
200490 2 1065.00 2006-03-19 WEIGL
.
.
.
--- 13 row(s) selected.
ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
---------- -------- ------------ ----------- ------------------
100210 2001 1100.00 3 GRAPHIC PRINTER,M1
100210 2403 620.00 6 DAISY PRINTER,T2
100210 244 3500.00 3 PC GOLD, 30 MB
100210 5100 150.00 10 MONITOR BW, TYPE 1
100250 6500 95.00 10 DISK CONTROLLER
100250 6301 245.00 15 GRAPHIC CARD, HR
.
.
.
--- 70 row(s) selected.
--- SQL operation complete.
```
<<<
[[commit_work_statement]]
== COMMIT WORK Statement
The COMMIT WORK statement commits any changes to objects made during the current transaction and ends
the transaction. See <<Transaction_Management,Transaction Management>>.
WORK is an optional keyword that has no effect.
COMMIT WORK issued outside of an active transaction generates error 8605.
```
COMMIT [WORK]
```
[[commit_work_considerations]]
=== Considerations for COMMIT WORK
BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction.
<<<
[[commit_work_examples]]
=== Example of COMMIT WORK
Suppose that your application adds information to the inventory. You have received 24 terminals from
a new supplier and want to add the supplier and update the quantity on hand. The part number for the
terminals is 5100, and the supplier is assigned supplier number 17. The cost of each terminal is $800.
The transaction must add the order for terminals to PARTSUPP, add the supplier to the SUPPLIER table,
and update QTY_ON_HAND in PARTLOC. After the INSERT and UPDATE statements execute successfully,
you commit the transaction, as shown:
```
-- This statement initiates a transaction.
BEGIN WORK;
--- SQL operation complete.
-- This statement inserts a new entry into PARTSUPP.
INSERT INTO invent.partsupp
VALUES (5100, 17, 800.00, 24);
--- 1 row(s) inserted.
-- This statement inserts a new entry into SUPPLIER.
INSERT INTO invent.supplier
VALUES (17, 'Super Peripherals','751 Sanborn Way',
'Santa Rosa', 'California', '95405');
--- 1 row(s) inserted.
-- This statement updates the quantity in PARTLOC.
UPDATE invent.partloc
SET qty_on_hand = qty_on_hand + 24
WHERE partnum = 5100 AND loc_code = 'G43';
--- 1 row(s) updated.
-- This statement ends a transaction.
COMMIT WORK;
--- SQL operation complete.
```
<<<
[[control_query_cancel_statement]]
== CONTROL QUERY CANCEL Statement
The CONTROL QUERY CANCEL statement cancels an executing query that you identify with a query ID.
You can execute the CONTROL QUERY CANCEL statement in a client-based tool like TrafCI or through any ODBC or JDBC
application.
CONTROL QUERY CANCEL is a {project-name} SQL extension.
```
CONTROL QUERY CANCEL QID query-id [COMMENT 'comment-text']
```
[[control_query_cancel_syntax]]
=== Syntax Description of CONTROL QUERY CANCEL
* `_query-id_`
+
specifies the query ID of an executing query, which is a unique identifier generated by the SQL compiler.
* `'_comment-text_'`
+
specifies an optional comment to be displayed in the canceled query’s error message.
[[control_query_cancel_considerations]]
=== Considerations for CONTROL QUERY CANCEL
[[control_query_cancel_benefitsl]]
==== Benefits of CONTROL QUERY CANCEL
For many queries, the CONTROL QUERY CANCEL statement allows the termination of the query without stopping the
master executor process (MXOSRVR). This type of cancellation has these benefits over standard ODBC/JDBC cancel
methods:
* An ANSI-defined error message is returned to the client session, and SQLSTATE is set to HY008.
* Important cached objects persist after the query is canceled, including the master executor process and its
compiler, the compiled statements cached in the master, and the compiler’s query cache and its cached metadata
and histograms.
* The client does not need to reestablish its connection, and its prepared statements are preserved.
* When clients share connections using a middle-tier application server, the effects of canceling one client’s
executing query no longer affect other clients sharing the same connection.
[[control_query_cancel_restrictions]]
==== Restrictions on CONTROL QUERY CANCEL
Some executing queries may not respond to a CONTROL QUERY CANCEL statement within a 60-second interval. For those
queries, {project-name} SQL stops their ESP processes if there are any. If this action allows the query to be canceled,
you will see all the benefits listed above.
If the executing query does not terminate within 120 seconds after the CONTROL QUERY CANCEL statement is issued,
{project-name} SQL stops the master executor process, terminating the query and generating a lost connection error.
In this case, you will not see any of the benefits listed above. Instead, you will lose your connection and will
need to reconnect and re-prepare the query. This situation often occurs with the CALL, DDL, and utility statements
and rarely with other statements.
The CONTROL QUERY CANCEL statement does not work with these statements:
* Unique queries, which operate on a single row and a single partition
* Queries that are not executing, such as a query that is being compiled
* CONTROL QUERY DEFAULT, BEGIN WORK, COMMIT WORK, ROLLBACK WORK, and EXPLAIN statements
* Statically compiled metadata queries
* Queries executed in anomalous conditions, such as queries without runtime statistics or without a query ID
[[control_query_cancel_required_privileges]]
==== Required Privileges
To issue a CONTROL QUERY CANCEL statement, one of the following must be true:
* You are DB ROOT.
* You own (that is, issued) the query.
* You have the QUERY_CANCEL component privilege for the SQL_OPERATIONS component.
<<<
[[control_query_cancel_examples]]
=== Example of CONTROL QUERY CANCEL
This CONTROL QUERY CANCEL statement cancels a specified query and provides a comment concerning the cancel
operation:
```
control query cancel qid
MXID11000010941212288634364991407000000003806U3333300_156016_S1 comment
'Query is consuming too many resources.';
```
In a separate session, the client that issued the query will see this
error message indicating that the query has been canceled:
```
>>execute s1;
*** ERROR[8007] The operation has been canceled. Query is consuming too many resources.
```
<<<
[[control_query_default_statement]]
== CONTROL QUERY DEFAULT Statement
The CONTROL QUERY DEFAULT statement changes the default settings for the current process. You can execute
the CONTROL QUERY DEFAULT statement in a client-based tool like TrafCI or through any ODBC or JDBC application.
CONTROL QUERY DEFAULT is a {project-name} SQL extension.
```
{ CONTROL QUERY DEFAULT | CQD } control-default-option
control-default-option is:
attribute {'attr-value' | RESET}
```
[[control_query_default_syntax]]
=== Syntax Description of CONTROL QUERY DEFAULT
* `_attribute_`
+
is a character string that represents an attribute name. For descriptions of these attributes,
see the {docs-url}/cqd_reference/index.html[{project-name} Control Query Default (CQD) Reference Guide].
* `_attr-value_`
+
is a character string that specifies an attribute value. You must specify _attr-value_ as a quoted string—even
if the value is a number.
* `RESET`
specifies that the attribute that you set by using a CONTROL QUERY DEFAULT statement in the current session is
to be reset to the value or values in effect at the start of the current session.
<<<
[[control_query_default_considerations]]
=== Considerations for CONTROL QUERY DEFAULT
[[control_query_default_scope]]
==== Scope of CONTROL QUERY DEFAULT
The result of the execution of a CONTROL QUERY DEFAULT statement stays in effect until the current process
terminates or until the execution of another statement for the same attribute overrides it.
CQDs are applied at compile time, so CQDs do not affect any statements that are already prepared. For example:
```
PREPARE x FROM SELECT * FROM t;
CONTROL QUERY DEFAULT SCHEMA 'myschema';
EXECUTE x; -- uses the default schema SEABASE
SELECT * FROM t2; -- uses MYSCHEMA;
PREPARE y FROM SELECT * FROM t3;
CONTROL QUERY DEFAULT SCHEMA 'seabase';
EXECUTE y; -- uses MYSCHEMA;
```
[[control_query_default_examples]]
=== Examples of CONTROL QUERY DEFAULT
* Increase the cache refresh time for the histogram cache to two hours (7,200 minutes).
+
```
CONTROL QUERY DEFAULT CACHE_HISTOGRAMS_REFRESH_INTERVAL '7200';
```
* Reset the CACHE_HISTOGRAMS_REFRESH_INTERVAL attribute to its initial value in the current process:
+
```
CONTROL QUERY DEFAULT CACHE_HISTOGRAMS_REFRESH_INTERVAL RESET;
```
<<<
[[create_function_statement]]
== CREATE FUNCTION Statement
The CREATE FUNCTION statement registers a user-defined function (UDF) written in C as a function within
a {project-name} database. Currently, {project-name} supports the creation of _scalar UDFs_, which return a single
value or row when invoked. Scalar UDFs are invoked as SQL expressions in the SELECT list or WHERE clause
of a SELECT statement.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this
statement inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run
this statement, AUTOCOMMIT must be turned ON (the default) for the session.
```
CREATE FUNCTION function-ref ([parameter-declaration[, parameter-declaration]...])
{RETURN | RETURNS}
(return-parameter-declaration[, return-parameter-declaration]...)
EXTERNAL NAME 'character-string-literal'
LIBRARY [[catalog-name.]schema-name.]library-name
[language c]
[parameter style sql]
[no sql]
[not deterministic | deterministic]
[final call | no final call]
[no state area | state area size]
[no parallelism | allow any parallelism]
function-ref is:
[[catalog-name.]schema-name.]function-name
parameter-declaration is:
[in] [sql-parameter-name] sql-datatype
return-parameter-declaration is:
[out] [sql-parameter-name] sql-datatype
```
<<<
[[create_function_syntax]]
=== Syntax Description of CREATE FUNCTION
* `_function-ref_ ( [_parameter-declaration_[,_parameter-declaration_]&#8230;] )`
+
specifies the name of the function and any SQL parameters that correspond to the signature of the external function.
** `_function-ref_`
+
specifies an ANSI logical name of the form:
+
`\[[_catalog-name_.]_schema-name_.]_function-name_`
+
where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, see
<<identifiers,identifiers>> and <<database_object_names,database object names>>.
+
specify a name that is unique and does not exist for any procedure or function in the same schema.
+
if you do not fully qualify the function name, trafodion sql qualifies it according to the schema of the current session.
** `_parameter-declaration_`
+
specifies an sql parameter that corresponds to the signature of the external function:
+
`[in] [_sql-parameter-name_] _sql-datatype_`
*** `in`
+
specifies that the parameter passes data to the function.
*** `_sql-parameter-name_`
+
specifies an sql identifier for the parameter. for more information, see <<identifiers,identifiers>>.
<<<
*** `_sql-datatype_`
+
specifies an sql data type that corresponds to the data type of the parameter in the signature of the
external function. _sql-datatype_ is one of the supported sql data types in trafodion. see
<<data_types,data types>>.
* `{return | returns} (_return-parameter-declaration_[,_return-parameter-declaration_]&#8230;)`
+
specifies the type of output of the function.
** `_return-parameter-declaration_`
+
specifies an sql parameter for an output value:
+
`[out] [_sql-parameter-name_] _sql-datatype_`
*** `out`
+
specifies that the parameter accepts data from the function.
*** `_sql-parameter-name_`
+
specifies an sql identifier for the return parameter. for more information, see <<identifiers,identifiers>>.
+
*** `_sql-datatype_`
+
specifies an sql data type for the return parameter. _sql-datatype_ is one of the supported sql data types in
trafodion. see <<data_types,data types>>.
* `external name '_method-name_'`
+
specifies the case-sensitive name of the external function’s method.
* `library \[[_catalog-name_.]_schema-name_.]_library-name_`
+
specifies the ANSI logical name of a library containing the external function. if you do not fully qualify the
library name, trafodion sql qualifies it according to the schema of the current session.
* `language c`
+
specifies that the external function is written in the c language. this clause is optional.
* `parameter style sql`
+
specifies that the run-time conventions for arguments passed to the external function are those of the sql
language. this clause is optional.
* `no sql`
+
specifies that the function does not perform sql operations. this clause is optional.
* `deterministic | not deterministic`
+
specifies whether the function always returns the same values for out parameters for a given set of argument
values (deterministic, the default behavior) or does not return the same values (not deterministic). if the
function is deterministic, trafodion sql is not required to execute the function each time to produce results;
instead, trafodion sql caches the results and reuses them during subsequent executions, thus optimizing the execution.
* `final call | no final call`
+
specifies whether or not a final call is made to the function. a final call enables the function to free up
system resources. the default is final call.
* `no state area | state area _size_`
+
specifies whether or not a state area is allocated to the function. _size_ is an integer denoting memory in
bytes. acceptable values range from 0 to 16000. the default is no state area.
* `no parallelism | allow any parallelism`
+
specifies whether or not parallelism is applied when the function is invoked. the default is allow any parallelism.
<<<
[[create_function_considerations]]
=== Considerations for CREATE FUNCTION
[[create_function_required_privileges]]
==== Required Privileges
To issue a CREATE FUNCTION statement, one of the following must be true:
* You are DB ROOT.
* You are creating the function in a shared schema, and you have the USAGE (or ALL) privilege on the library that
will be used in the creation of the function. The USAGE privilege provides you with read access to the library’s
underlying library file.
* You are the private schema owner and have the USAGE (or ALL) privilege on the library that will be used in the
creation of the function. The USAGE privilege provides you with read access to the library’s underlying library file.
* You have the CREATE or CREATE_ROUTINE component level privilege for the SQL_OPERATIONS component and have the
USAGE (or ALL) privilege on the library that will be used in the creation of the function. The USAGE
privilege provides you with read access to the library’s underlying library file.
+
NOTE: In this case, if you create a function in a private schema, it will be owned by the schema owner.
<<<
[[create_function_examples]]
=== Examples of CREATE FUNCTION
* This CREATE FUNCTION statement creates a function that adds two integers:
+
```
create function add2 (int, int)
returns (total_value int)
external name 'add2'
library myudflib;
```
* This CREATE FUNCTION statement creates a function that returns the minimum, maximum, and average values of
five input integers:
+
```
create function mma5 (int, int, int, int, int)
returns (min_value int, max_value int, avg_value int)
external name 'mma5'
library myudflib;
```
* This CREATE FUNCTION statement creates a function that reverses an input string of at most 32 characters:
+
```
create function reverse (varchar(32))
returns (reversed_string varchar(32))
external name 'reverse'
library myudflib;
```
<<<
[[create_index_statement]]
== CREATE INDEX Statement
The CREATE INDEX statement creates an SQL index based on one or more columns of a table or table-like object.
CREATE INDEX is a Trafodion SQL extension.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement
inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement,
AUTOCOMMIT must be turned ON (the default) for the session.
```
CREATE [VOLATILE | UNIQUE] INDEX index-name ON table
(column-name [ASC[ENDING] | DESC[ENDING]]
[,column-name [ASC[ENDING] | DESC[ENDING]]]...)
[populate-option]
[HBASE_OPTIONS (hbase-options-list)]
[SALT LIKE TABLE]
populate-option is:
POPULATE
| NO POPULATE
hbase-options-list is:
hbase-option = 'value'[, hbase-option = 'value']...
```
[[create_index_syntax]]
=== Syntax Description of CREATE INDEX
* `VOLATILE`
+
The CREATE VOLATILE INDEX statement creates an SQL index with a lifespan that is limited to the SQL session that the index is created. Volatile indexes are dropped automatically when the session ends. See <<indexes,Indexes>>.
* `UNIQUE`
+
The CREATE UNIQUE INDEX statement creates a unique index on a table and enforces uniqueness for the indexed field, that is, it imposes the restriction that any duplicate column values are disallowed and must be eliminated before creating a unique index on a column.
+
Unique index not only improves the query performance, but also provides data integrity checking. The constraint is checked when rows of the table are inserted or updated.
+
NOTE: Multiple NULL values are treated as duplicate values for a column which can contain only one NULL value.
* `_index-name_`
+
is an SQL identifier that specifies the simple name for the new index. You cannot qualify _index_ with its schema
name. Indexes have their own name space within a schema, so an index name might be the same as a table or constraint
name. However, no two indexes in a schema can have the same name.
* `_table_`
+
is the name of the table for which to create the index. See <<database_object_names,Database Object Names>>.
* `_column-name_ [ASC[ENDING] | DESC[ENDING]] [,_column-name_ [ASC[ENDING] | DESC[ENDING]]]&#8230;`
+
specifies the columns in _table_ to include in the index. The order of the columns in the index need not correspond
to the order of the columns in the table.
+
ASCENDING or DESCENDING specifies the storage and retrieval order for rows in the index. The default is ASCENDING.
+
Rows are ordered by values in the first column specified for the index. If multiple index rows share the same value
for the first column, the values in the second column are used to order the rows, and so forth. If duplicate index
rows occur in a non-unique index, their order is based on the sequence specified for the columns of the key of the
underlying table. For ordering (but not for other purposes), nulls are greater than other values.
* `populate-option`
** `NO POPULATE`
+
specifies that the index is not to be populated when it is created.
+
The index’s partition(s) are created, but no data is written to the index, and it is marked “offline”. You can drop an offline index with the DROP INDEX statement.
The DROP TABLE statement also drops offline indexes of the specified table.
DML statements have no effect on offline indexes.
+
If an index is created with the intention of using it for a constraint, it must be populated before creating the constraint.
You can populate an offline index and remove its offline designation by using the POPULATE INDEX utility.
** `POPULATE`
+
specifies that the index is to be created and populated.
+
The default is `POPULATE`.
* `HBASE_OPTIONS (_hbase-option_ = '_value_'[, _hbase-option_ = '_value_']&#8230;)`
+
a list of HBase options to set for the index. These options are applied independently of any HBase options set for
the index’s table.
** `_hbase-option_ = '_value_'`
+
is one of these HBase options and its assigned value:
+
[options="header"]
|===
| HBase Option | Accepted Values | Default Values
| BLOCKCACHE | 'true' \| 'false' | 'true'
| BLOCKSIZE | '1024' - '64*1024*1024' | '64*1024'
| BLOOMFILTER | 'NONE' \| 'ROW' \| 'ROWCOL' | 'ROW'
| CACHE_BLOOMS_ON_WRITE | 'true' \| 'false' | 'false'
| CACHE_DATA_ON_WRITE | 'true' \| 'false' | 'false'
| CACHE_INDEXES_ON_WRITE | 'true' \| 'false' | 'false'
| COMPACT | 'true' \| 'false' | 'true'
| COMPACT_COMPRESSION | 'GZ' \| 'LZ4' \| 'LZO' \| 'NONE' \| 'SNAPPY' | 'NONE'
| COMPRESSION | 'GZ' \| 'LZ4' \| 'LZO' \| 'NONE' \| 'SNAPPY' | 'NONE'
| DATA_BLOCK_ENCODING | 'DIFF' \| 'FAST_DIFF' \| 'NONE' \| 'PREFIX' | 'NONE'
| DURABILITY | 'USE_DEFAULT' \| 'SKIP_WAL' \| 'ASYNC_WAL' \| 'SYNC_WAL' \| 'FSYNC_WAL' | 'SYNC_WAL'
| EVICT_BLOCKS_ON_CLOSE | 'true' \| 'false' | 'false'
| IN_MEMORY | 'true' \| 'false' | 'false'
| KEEP_DELETED_CELLS | 'true' \| 'false' | 'false'
| MAX_FILESIZE | '2*1024*1024' - '2^63^-1' | '10*1024*1024*1024'
| MAX_VERSIONS | '1' \| '_positive-integer_' | '1'
| MEMSTORE_FLUSH_SIZE | '1024*1024' - '2^63^-1' | '128*1024*1024'
| MIN_VERSIONS | '0' \| '_positive-integer_' | '0'
| PREFIX_LENGTH_KEY | '_positive-integer_', which should be less than maximum length of the key for the table.
It applies only if the SPLIT_POLICY is `KeyPrefixRegionSplitPolicy`. | '2'
| REPLICATION_SCOPE | '0' \| '1' | '1'
| SPLIT_POLICY | 'org.apache.hadoop.hbase.regionserver. +
ConstantSizeRegionSplitPolicy' \| +
'org.apache.hadoop.hbase.regionserver. +
IncreasingToUpperBoundRegionSplitPolicy' \| +
'org.apache.hadoop.hbase.regionserver. +
KeyPrefixRegionSplitPolicy'
|'org.apache.hadoop.hbase.regionserver. +
IncreasingToUpperBoundRegionSplitPolicy'
| TTL | '_positive-integer_', which should be less than equal to 2147483447.
| '2147483647' (forever)
|===
* `SALT LIKE TABLE`
+
causes the index to use the same salting scheme (that is,
`SALT USING _num_ PARTITIONS [ON (_column_[, _column_]&#8230;)])` as its base table.
<<<
[[create_index_considerations]]
=== Considerations for CREATE INDEX
Indexes are created under a single transaction. When an index is created, the following steps occur:
* Transaction begins (either a user-started transaction or a system-started transaction).
* Rows are written to the metadata.
* Physical labels are created to hold the index (as non audited).
* The base table is locked for read shared access which prevents inserts, updates, and deletes on the base table from occurring.
* The index is loaded by reading the base table for read uncommitted access using side tree inserts.
+
NOTE: A side tree insert is a fast way of loading data that can perform specialized optimizations because the
partitions are not audited and empty.
* After load is complete, the index audit attribute is turned on and it is attached to the base table (to bring the index on-line).
* The transaction is committed, either by the system or later by the requester.
If the operation fails after basic semantic checks are performed, the index no longer exists and the entire transaction
is rolled back even if it is a user-started transaction.
If you are creating an index on a large Trafodion table that is already populated,
you should use the NO POPULATE option, and then run the POPULATE INDEX utility to load the index.
Because CREATE INDEX executes in a single transaction, it could experience transactional limitations such as a transaction timeout
if a large amount of data is to be moved.
[[create_index_authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
An index always has the same security as the table it indexes.
CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table being indexed. If other processes have rows in the table locked
when the operation begins, CREATE INDEX waits until its lock request is granted or timeout occurs.
You cannot access an index directly.
<<<
[[create_index_required_privileges]]
==== Required Privileges
To issue a CREATE INDEX statement, one of the following must be true:
* You are DB ROOT.
* You are creating the table in a shared schema.
* You are the private schema owner.
* You are the owner of the table.
* You have the ALTER, ALTER_TABLE, CREATE, or CREATE_INDEX component privilege for the SQL_OPERATIONS component.
+
NOTE: In this case, if you create an index in a private schema, it will be owned by the schema owner.
[[create_index_limits]]
==== Limits on Indexes
For non-unique indexes, the sum of the lengths of the columns in the index plus the sum of the length of
the clustering key of the underlying table cannot exceed 2048 bytes.
No restriction exists on the number of indexes per table.
[[create_index_examples]]
=== Examples of CREATE INDEX
* This example creates an index on two columns of a table:
+
```
CREATE INDEX xempname
ON persnl.employee (last_name, first_name);
```
* This example shows that a volatile index can be created only on a volatile table.
+
```
SQL>create volatile table test2(c1 int);
--- SQL operation complete.
SQL>create volatile index idx_test2 on test2(c1);
--- SQL operation complete.
SQL>create table test1(c1 int);
--- SQL operation complete.
SQL>create volatile index idx_test1 on test1 (c1);
*** ERROR[4082] Object TEST1 does not exist or is inaccessible. [2017-01-13 11:35:26]
```
* This example shows a unique index can be created on a table which has only one NULL value.
+
```
SQL>select * from t25;
A B
----------- ----------
1 NULL
2 aaa
--- 2 row(s) selected.
SQL>create unique index idx_t25 on t25(b);
--- SQL operation complete.
SQL>showddl t25;
CREATE TABLE TRAFODION.SEABASE.T25
(
A INT DEFAULT NULL NOT SERIALIZED,
B CHAR(10) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT;
CREATE UNIQUE INDEX IDX_T25 ON TRAFODION.SEABASE.T25
(
B ASC
)
ATTRIBUTES ALIGNED FORMAT;
--- SQL operation complete.
```
* This example shows a unique index cannot be created on a table which has multiple NULL values.
+
```
SQL>select * from t25;
A B
----------- ----------
1 NULL
2 aaa
--- 2 row(s) selected.
SQL>create unique index idx_t25 on t25(b);
*** ERROR[8110] Duplicate rows detected. [2017-01-12 17:17:51]
*** ERROR[1053] Unique index TRAFODION.SEABASE.IDX_T25 could not be created because the specified column(s) contain duplicate data. [2017-01-12 17:17:51]
```
* This exmaple creates the index _index_test_ which is not to be populated on the column _c1_ of the table _test_.
+
```
SQL>create index index_test on test (c1) no populate;
--- SQL operation complete.
```
<<<
[[create_library_statement]]
== CREATE LIBRARY Statement
The CREATE LIBRARY statement registers a library object in a {project-name} database. A library object
can be an SPJ's JAR file or a UDF's library file.
CREATE LIBRARY is a {project-name} SQL extension.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run
this statement inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT
is OFF. To run this statement, AUTOCOMMIT must be turned ON (the default) for the session.
```
CREATE LIBRARY [[catalog-name.]schema-name.]library-name
file 'library-filename'
[host name 'host-name']
[local file 'host-filename']
```
[[create_library_syntax]]
=== Syntax Description of CREATE LIBRARY
* `\[[_catalog-name_.]_schema-name_.]_library-name_`
+
specifies the ANSI logical name of the library object, where each part of the name is a valid sql
identifier with a maximum of 128 characters. specify a name that is unique and does not exist for
libraries in the same schema. if you do not fully qualify the library name, trafodion sq qualifies
it according to the schema of the current session. for more information, see <<identifiers,identifiers>>
and <<database_object_names,database object names>>.
<<<
* `file '_library-filename_'`
+
specifies the full path of a deployed library file, which either an SPJ's jar file or a UDF's library file.
+
NOTE: Make sure to upload the library file to the trafodion cluster and then copy the library file to the
same directory on all the nodes in the cluster before running the create library statement. otherwise, you
will see an error message indicating that the jar or dll file was not found.
* `host name '_host-name_'`
+
specifies the name of the client host machine where the deployed file resides.
* `local file '_host-filename_'`
+
specifies the path on the client host machine where the deployed file is stored.
[[create_library_considerations]]
=== Considerations for CREATE LIBRARY
* A library object cannot refer to a library file referenced by another library object. If the _library-filename_
is in use by another library object, the CREATE LIBRARY command will fail.
* The _library-filename_ must specify an existing file. Otherwise, the CREATE LIBRARY command will fail.
* The CREATE LIBRARY command does not verify that the specified _library-filename_ is a valid executable file.
* HOST NAME and LOCAL FILE are position dependent.
<<<
[[create_library_required_privileges]]
==== Required Privileges
To issue a CREATE LIBRARY statement, one of the following must be true:
* You are DB ROOT.
* You are creating the library in a shared schema and have the MANAGE_LIBRARY privilege.
* You are the private schema owner and have the MANAGE_LIBRARY privilege.
* You have the CREATE or CREATE_LIBRARY component privilege for the SQL_OPERATIONS component and have
the MANAGE_LIBRARY privilege.
+
NOTE: In this case, if you create a library in a private schema, it will be owned by the schema owner.
[[create_library_examples]]
=== Examples of CREATE LIBRARY
* This CREATE LIBRARY statement registers a library named SALESLIB in the SALES schema for a JAR file (SPJs):
+
```
CREATE LIBRARY sales.saleslib FILE '/opt/home/trafodion/spjjars/Sales.jar';
```
* This CREATE LIBRARY statement registers a library named MYUDFS in the default schema for a library file (UDFs):
+
```
CREATE LIBRARY myudfs FILE $UDFLIB;
```
<<<
[[create_procedure_statement]]
== CREATE PROCEDURE Statement
The CREATE PROCEDURE statement registers a Java method as a stored procedure in Java (SPJ) within a {project-name} database.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement
inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement,
AUTOCOMMIT must be turned ON (the default) for the session.
```
CREATE PROCEDURE procedure-ref([sql-parameter-list])
EXTERNAL NAME 'java-method-name [java-signature]'
LIBRARY [[catalog-name.]schema-name.]library-name
[external security external-security-type]
LANGUAGE java
parameter style java
[no sql | contains sql | modifies sql data | reads sql data]
[dynamic result sets integer]
[TRANSACTION REQUIRED | NO TRANSACTION REQUIRED]
[DETERMINISTIRC | NOT DETERMINISTRIC]
[NO ISOLATE| ISOLATE]
procedure-ref is:
[[catalog-name.]schema-name.]procedure-name
sql-parameter-list is:
sql-parameter[, sql-parameter]...
sql-parameter is:
[parameter-mode] [sql-identifier] sql-datatype
parameter-mode is:
in
| out
| inout
java-method-name is:
[package-name.]class-name.method-name
java-signature is:
([java-parameter-list])
java-parameter-list is:
java-datatype[, java-datatype]...
external-security-type is:
definer
| invoker
```
NOTE: delimited variables in this syntax diagram are case-sensitive. case-sensitive variables include _java-method-name_,
_java-signature_, and _class-file-path_, and any delimited part of the _procedure-ref_.
the remaining syntax is not case-sensitive.
[[create_procedure_syntax]]
=== Syntax Description of CREATE PROCEDURE
* `_procedure-ref_([_sql-parameter_[, _sql-parameter_]&#8230;])`
+
specifies the name of the stored procedure in Java (SPJ) and any SQL parameters that correspond to the signature of
the SPJ method.
** `_procedure-ref_`
+
specifies an ANSI logical name of the form:
+
`\[[_catalog-name_.]_schema-name_.]_procedure-name_`
+
where each part of the name is a valid SQL identifier with a maximum of 128 characters. For more information,
see <<identifiers,identifiers>> and <<database_object_names,database object names>>.
+
specify a name that is unique and does not exist for any procedure or function in the same schema. {project-name}
does not support the overloading of procedure names. That is, you cannot register the same procedure name more than
once with different underlying SPJ methods.
+
If you do not fully qualify the procedure name, then {project-name} qualifies it according to the schema of the current session.
** `_sql-parameter_`
+
specifies an SQL parameter that corresponds to the signature of the SPJ method:
+
`[_parameter-mode_] [_sql-identifier_] _sql-datatype_`
*** `_parameter-mode_`
+
specifies the mode `in`, `out`, or `inout` of a parameter. The default is `in`.
**** `in`
+
specifies a parameter that passes data to an SPJ.
**** `out`
+
specifies a parameter that accepts data from an SPJ. The parameter must be an array.
**** `inout`
+
specifies a parameter that passes data to and accepts data from an SPJ. The parameter must be an array.
*** `_sql-identifier_`
+
specifies an SQL identifier for the parameter. For more information, see <<identifiers,identifiers>>.
*** `_sql-datatype_`
+
specifies an SQL data type that corresponds to the Java parameter of the SPJ method.
+
_sql-datatype_ can be:
+
[cols="60%,40%",options="header"]
|===
| sql data type | maps to java data type&#8230;
| char[acter] +
char[acter] varying +
varchar +
varchar2 +
pic[ture] x^1^ +
nchar +
nchar varying +
national char[acter] +
national char[acter] varying | java.lang.string
| date | java.sql.date
| time | java.sql.time
| timestamp | java.sql.timestamp
| dec[imal]^2^ +
pic[ture] s9^3^ +
numeric (including numeric with a precision greater than eighteen)^2^ | java.math.bigdecimal
| smallint^2^ | short
| int[eger]^2^ | int or java.lang.integer^4^
| largeint^2^ | long or java.lang.long^4^
| float | double or java.lang.double^4^
| real | float or java.lang.float^4^
| double precision | double or java.lang.double^4^
|===
+
1. the trafodion database stores pic x as a char data type.
2. numeric data types of sql parameters must be signed, which is the default in the trafodion database.
3. the trafodion database stores pic s9 as a decimal or numeric data type.
4. by default, the sql data type maps to a java primitive type. the sql data type maps to a java wrapper class
only if you specify the wrapper class in the java signature of the external name clause.
+
for more information, see <<data_types,data types>>.
* `external name '_java-method-name_ [_java-signature_]'`
** `_java-method-name_`
+
specifies the case-sensitive name of the SPJ method of the form:
+
`[_package-name_.]_class-name_._method-name_`
+
The Java method must exist in a Java class file, _class-name_.class, within a library registered in the database.
The Java method must be defined as `public` and `static` and have a return type of `void`.
+
If the class file that contains the SPJ method is part of a package, then you must also specify the package name.
If you do not specify the package name, the create procedure statement fails to register the SPJ.
** `_java-signature_`
+
specifies the signature of the SPJ method and consists of:
+
`([_java-datatype_[, _java-datatype_]&#8230;])`
+
The Java signature is necessary only if you want to specify a Java wrapper class (for example, `java.lang.integer`) instead of a java
primitive data type (for example, `int`). An SQL data type maps to a Java primitive data type by default.
+
The Java signature is case-sensitive and must be placed within parentheses, such as `(java.lang.integer, java.lang.integer`).
The signature must specify each of the parameter data types in the order they appear in the Java method definition within
the class file. Each Java data type that corresponds to an out or inout parameter must be followed by empty square
brackets (`[ ]`), such as `java.lang.integer[]`.
+
<<<
*** `_java-datatype_`
+
Specifies a mappable Java data type. For the mapping of the Java data types to SQL data types, see _sql-datatype_.
* `library \[[_catalog-name_.]_schema-name_.]_library-name_`
+
specifies the ANSI logical name of a library containing the SPJ method. If you do not fully qualify the library name,
then {project-name} qualifies it according to the schema of the current session.
* `external security _external-security-type_`
+
determines the privileges, or rights, that users have when executing (or calling) the SPJ. An SPJ can have one of these
types of external security:
** `invoker` determines that users can execute, or invoke, the stored procedure using the privileges of the user who invokes
the stored procedure. This behavior is referred to as _invoker rights_ and is the default behavior if external security is
not specified. Invoker rights allow a user who has the execute privilege on the SPJ to call the SPJ using his or her existing
privileges. In this case, the user must be granted privileges to access the underlying database objects on which the SPJ operates.
+
NOTE: Granting a user privileges to the underlying database objects gives the user direct access to those database objects,
which could pose a risk to more sensitive or critical data to which users should not have access. For example, an SPJ
might operate on a subset of the data in an underlying database object but that database object might contain other
more sensitive or critical data to which users should not have access.
** `definer` determines that users can execute, or invoke, the stored procedure using the privileges of the user who created
the stored procedure. This behavior is referred to as _definer rights_. The advantage of definer rights is that users are
allowed to manipulate data by invoking the stored procedure without having to be granted privileges to the underlying
database objects. That way, users are restricted from directly accessing or manipulating more sensitive or critical data in
the database. However, be careful about the users to whom you grant execute privilege on an SPJ with definer external security
because those users will be able to execute the SPJ without requiring privileges to the underlying database objects.
<<<
* `LANGUAGE java`
+
specifies that the external user-defined routine is written in the java language.
* `parameter style java`
+
specifies that the run-time conventions for arguments passed to the external user-defined routine are those of the Java language.
* `no sql`
+
specifies that the SPJ cannot perform SQL operations.
* `contains sql | modifies sql data | reads sql data`
+
specifies that the SPJ can perform SQL operations. All these options behave the same as `contains sql`, meaning that the SPJ
can read and modify SQL data. Use one of these options to register a method that contains SQL statements. Ff you do not specify
an SQL access mode, then the default is `contains sql`.
* `dynamic result sets _integer_`
+
specifies the maximum number of result sets that the SPJ can return. This option is applicable only if the method signature
contains a `java.sql.resultset[]` object. If the method contains a result set object, then the valid range is 1 to 255 inclusive.
The actual number of result sets returned by the SPJ method can be fewer than or equal to this number. If you do not specify
this option, then the default value is 0 (zero), meaning that the SPJ does not return result sets.
* `TRANSACTION REQUIRED | NO TRANSACTION REQUIRED`
+
determines whether the SPJ must run in a transaction inherited from the calling application (`TRANSACTION REQUIRED`, the default
option) or whether the SPJ runs without inheriting the calling application’s transaction (`NO TRANSACTION REQUIRED`). Typically,
you want the stored procedure to inherit the transaction from the calling application. However, if the SPJ method does
not access the database or if you want the stored procedure to manage its own transactions, then you should set the stored
procedure’s transaction attribute to `NO TRANSACTION REQUIRED`. For more information, see
<<effects_of_the_transaction_attribute_on_spjs,effects of the transaction attribute on SPJs>>.
<<<
* `DETERMINISTRIC | NOT DETERMINISTRIC`
+
specifies whether the SPJ always returns the same values for out and inout parameters for a given set of argument values
(`DETERMINISTRIC`) or does not return the same values (`NOT DETERMINISTRIC`, the default option). If you specify `DETERMINISTRIC`,
{project-name} is not required to call the SPJ each time to produce results; instead, {project-name} caches the results and
reuses them during subsequent calls, thus optimizing the CALL statement.
* `NO ISOLATE | ISOLATE`
+
specifies that the SPJ executes either in the environment of the database server (`NO ISOLATE`) or in an isolated environment
(`ISOLATE`, the default option). {project-name} allows both options but always executes the SPJ in the UDR server process (`ISOLATE`).
[[create_procedure_considerations]]
=== Considerations for CREATE PROCEDURE
[[create_procedure_required_privileges]]
==== Required Privileges
To issue a CREATE PROCEDURE statement, one of the following must be true:
* You are DB ROOT.
* You are creating the procedure in a shared schema, and you have the USAGE (or ALL) privilege on the library that will be
used in the creation of the stored procedure. The USAGE privilege provides you with read access to the library’s underlying
JAR file, which contains the SPJ Java method.
* You are the private schema owner and have the USAGE (or ALL) privilege on the library that will be used in the creation of
the stored procedure. The USAGE privilege provides you with read access to the library’s underlying JAR file, which contains
the SPJ Java method.
* You have the CREATE or CREATE_ROUTINE component level privilege for the SQL_OPERATIONS component and have the USAGE (or ALL)
privilege on the library that will be used in the creation of the stored procedure. The USAGE privilege provides you with read
access to the library’s underlying JAR file, which contains the SPJ Java method.
+
NOTE: In this case, if you create a stored procedure in a private schema, it will be owned by the schema owner.
<<<
[[effects_of_the_transaction_attribute_on_spjs]]
==== Effects of the Transaction Attribute on SPJs
===== Transaction Required
_Using Transaction Control Statements or Methods_
If you specify TRANSACTION REQUIRED (the default option), a CALL statement automatically initiates a transaction if there is
no active transaction. In this case, you should not use transaction control statements (or equivalent JDBC transaction methods)
in the SPJ method. Transaction control statements include COMMIT WORK and ROLLBACK WORK, and the equivalent JDBC transaction
methods are `Connection.commit()` and `Connection.rollback()`. If you try to use transaction control statements or methods in an
SPJ method when the stored procedure’s transaction attribute is set to TRANSACTION REQUIRED, then the transaction control statements
or methods in the SPJ method are ignored, and the Java virtual machine (JVM) does not report any errors or warnings. When the
stored procedure’s transaction attribute is set to TRANSACTION REQUIRED, then you should rely on the transaction control statements
or methods in the application that calls the stored procedure and allow the calling application to manage the transactions.
_Committing or Rolling Back a Transaction_
If you do not use transaction control statements in the calling application, then the transaction initiated by the CALL statement
might not automatically commit or roll back changes to the database. When AUTOCOMMIT is ON (the default setting), the database
engine automatically commits or rolls back any changes made to the database at the end of the CALL statement execution. However,
when AUTOCOMMIT is OFF, the current transaction remains active until the end of the client session or until you explicitly commit
or roll back the transaction. To ensure an atomic unit of work when calling an SPJ, use the COMMIT WORK statement in the calling
application to commit the transaction when the CALL statement succeeds, and use the ROLLBACK WORK statement to roll back the
transaction when the CALL statement fails.
<<<
===== No Transaction Required
In some cases, you might not want the SPJ method to inherit the transaction from the calling application. Instead, you might want
the stored procedure to manage its own transactions or to run without a transaction. Not inheriting the calling application’s
transaction is useful in these cases:
* The stored procedure performs several long-running operations, such as multiple DDL or table maintenance operations, on the
database. In this case, you might want to commit those operations periodically from within the SPJ method to avoid locking tables
for a long time.
* The stored procedure performs certain SQL operations that must run without an active transaction. For example, INSERT, UPDATE,
and DELETE statements with the WITH NO ROLLBACK option are rejected when a transaction is already active, as is the case when a
stored procedure inherits a transaction from the calling application. The PURGEDATA utility is also rejected when a transaction
is already active.
* The stored procedure does not access the database. In this case, the stored procedure does not need to inherit the transaction
from the calling application. By setting the stored procedure’s transaction attribute to NO TRANSACTION REQUIRED, you can avoid
the overhead of the calling application’s transaction being propagated to the stored procedure.
In these cases, you should set the stored procedure’s transaction attribute to NO TRANSACTION REQUIRED when creating the stored
procedure.
If you specify NO TRANSACTION REQUIRED and if the SPJ method creates a JDBC default connection, that connection will have autocommit
enabled by default. You can either use the autocommit transactions or disable autocommit (conn.setAutoCommit(false);) and use the
JDBC transaction methods, `Connection.commit()` and `Connection.rollback()`, to commit or roll back work where needed.
<<<
[[create_procedure_examples]]
=== Examples of CREATE PROCEDURE
* This CREATE PROCEDURE statement registers an SPJ named LOWERPRICE, which does not accept any arguments:
+
```
SET SCHEMA SALES;
CREATE PROCEDURE lowerprice()
EXTERNAL NAME 'Sales.lowerPrice'
LIBRARY saleslib
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA;
```
+
Because the procedure name is not qualified by a catalog and schema, {project-name} qualifies it according to the current
session settings, where the catalog is TRAFODION (by default) and the schema is set to SALES. Since the procedure needs
to be able to read and modify SQL data, MODIFIES SQL DATA is specified in the CREATE PROCEDURE statement.
+
To call this SPJ, use this CALL statement:
+
```
CALL lowerprice();
```
+
The LOWERPRICE procedure lowers the price of items with 50 or fewer orders by 10 percent in the database.
* This CREATE PROCEDURE statement registers an SPJ named TOTALPRICE, which accepts three input parameters and returns a numeric value, the
total price to an INOUT parameter:
+
```
CREATE PROCEDURE trafodion.sales.totalprice(IN qty NUMERIC (18),
IN rate VARCHAR (10),
INOUT price NUMERIC (18,2))
EXTERNAL NAME 'Sales.totalPrice'
LIBRARY sales.saleslib
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL;
```
+
<<<
+
To call this SPJ in TrafCI, use these statements:
+
```
SET PARAM ?p 10.00;
CALL sales.totalprice(23, 'standard', ?p);
p
--------------------
253.97
--- SQL operation complete.
```
+
Since the procedure does not read and modify any SQL data, NO SQL is specified in the CREATE PROCEDURE statement.
* This CREATE PROCEDURE statement registers an SPJ named MONTHLYORDERS, which accepts an integer value for the month
and returns the number of orders:
+
```
CREATE PROCEDURE sales.monthlyorders(IN INT, OUT number INT)
EXTERNAL NAME 'Sales.numMonthlyOrders (int, java.lang.Integer[])'
LIBRARY sales.saleslib
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA;
```
+
Because the OUT parameter is supposed to map to the Java wrapper class, java.lang.Integer, you must specify the Java
signature in the EXTERNAL NAME clause. To invoke this SPJ, use this CALL statement:
+
```
CALL sales.monthlyorders(3, ?);
ORDERNUM
-----------
4
--- SQL operation complete.
```
<<<
* This CREATE PROCEDURE statement registers an SPJ named ORDERSUMMARY, which accepts a date (formatted as a string) and
returns information about the orders on or after that date.
+
```
CREATE PROCEDURE sales.ordersummary(IN on_or_after_date VARCHAR (20),
OUT num_orders LARGEINT)
EXTERNAL NAME 'Sales.orderSummary (int, long[])'
LIBRARY sales.saleslib
EXTERNAL SECURITY invoker
LANGUAGE JAVA
PARAMETER STYLE JAVA
READS SQL DATA
DYNAMIC RESULT SETS 2;
```
+
To invoke this SPJ, use this CALL statement:
+
```
CALL trafodion.sales.ordersummary('01-01-2014', ?);
```
+
The ORDERSUMMARY procedure returns this information about the orders on or after the specified date, 01-01-2014:
+
```
NUM_ORDERS
--------------------
13
ORDERNUM NUM_PARTS AMOUNT ORDER_DATE LAST_NAME
-------- -------------------- -------------------- ---------- --------------------
100210 4 19020.00 2014-04-10 HUGHES
100250 4 22625.00 2014-01-23 HUGHES
101220 4 45525.00 2014-07-21 SCHNABL
... ... ... ... ...
--- 13 row(s) selected.
ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
-------- ------- ---------- ----------- ------------------
100210 244 3500.00 3 PC GOLD, 30 MB
100210 2001 1100.00 3 GRAPHIC PRINTER,M1
100210 2403 620.00 6 DAISY PRINTER,T2
... ... ... ... ...
--- 70 row(s) selected.
--- SQL operation complete.
```
<<<
[[create_role_statement]]
== CREATE ROLE Statement
The CREATE ROLE statement creates an SQL role. See <<Roles,Roles>>.
```
CREATE ROLE role-name [ WITH ADMIN grantor ]
grantor is:
database-username
```
[[create_role_syntax]]
=== Syntax Description of CREATE ROLE
* `_role-name_`
+
is an SQL identifier that specifies the new role. _role-name_ is a regular or delimited
case-insensitive identifier.
See <<Case_Insensitive_Delimited_Identifiers,Case-Insensitive Delimited Identifiers>>.
_role-name_ cannot be an existing role name, and it cannot be a registered database username. However,
_role-name_ can be a configured directory-service username.
* `WITH ADMIN _grantor_`
+
specifies a role owner other than the current user. This is an optional clause.
* `_grantor_`
specifies a registered database username to whom you assign the role owner.
<<<
[[create_role_considerations]]
=== Considerations for CREATE ROLE
* To create a role, you must either be DB ROOT or have been granted the MANAGE_ROLES component privilege for SQL_OPERATIONS.
* PUBLIC, _SYSTEM, NONE, and database user names beginning with DB are reserved. You cannot specify a _role-name_ with any such name.
[[create_role_ownership]]
==== Role Ownership
You can give role ownership to a user by specifying the user in the WITH ADMIN _grantor_ clause with the _grantor_ as the user.
The role owner can perform these operations:
* Grant and revoke the role to users.
* Drop the role.
Role ownership is permanent. After you create the role, the ownership of the role cannot be changed or assigned to another user.
[[create_role_examples]]
=== Examples of CREATE ROLE
* To create a role and assign the current user as the role owner:
+
```
CREATE ROLE clerks;
```
* To create a role and assign another user as the role owner:
+
```
CREATE ROLE sales WITH ADMIN cmiller;
```
<<<
[[create_schema_statement]]
== CREATE SCHEMA Statement
The CREATE SCHEMA statement creates a schema in the database. See <<Schemas,Schemas>>.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run DDL statements
inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run these statements,
AUTOCOMMIT must be turned ON (the default) for the session.
```
CREATE [schema-class] SCHEMA [IF NOT EXISTS] schema-clause
schema-class is:
[ PRIVATE | SHARED ]
schema-clause is:
{ schema-name [AUTHORIZATION authid] | AUTHORIZATION authid }
```
[[create_schema_syntax]]
=== Syntax Description of CREATE SCHEMA
* `_schema-class_`
+
indicates whether access to the schema is restricted to the authorization ID by default (PRIVATE) or whether
any database user may add objects to the schema (SHARED). The default class is PRIVATE.
+
NOTE: Schemas created in {project-name} Release 0.9 or earlier are SHARED schemas.
* `IF NOT EXISTS`
+
creates the schema if it does not already exist. If omitted, then an error will be raised if the schema
already exists.
* `_schema-name_`
+
is a name for the new schema and is an SQL identifier that specifies a unique name that is not currently a schema name.
This parameter is optional. However, if you do not specify a schema name, you must specify the authorization clause. If
a schema name is not provided, the authorization ID is used for the schema name. If the authorization ID name matches an
existing schema, the CREATE SCHEMA command fails.
* `_authid_`
+
is the name of the database user or role will own and administer the schema. If this clause is not present, the current
user becomes the schema owner.
[[create_schema_considerations]]
=== Considerations for CREATE SCHEMA
[[create_schema_reserved_schema_names]]
==== Reserved Schema Names
Schema names that begin with a leading underscore (_) are reserved for future use.
[[create_schema_authorization_clause]]
==== AUTHORIZATION Clause
The AUTHORIZATION clause is optional. If you omit this clause, the current user becomes the schema owner.
NOTE: An authorization ID is assigned to a schema name even if authorization is not enabled for the {project-name} database.
However, no enforcement occurs unless authorization is enabled.
The schema owner can perform operations on the schema and on objects within the schema. For example:
* Alter DDL of objects
* Drop the schema
* Drop objects
* Manage objects with utility commands such as UPDATE STATISTICS and PURGEDATA
[[create_schema_who_can_create_a_schema]]
==== Who Can Create a Schema
The privilege to create a schema is controlled by the component privilege CREATE_SCHEMA for the SQL_OPERATIONS component.
By default, this privilege is granted to PUBLIC, but it can be revoked by DB ROOT.
When authorization is initialized, these authorization IDs are granted the CREATE_SCHEMA privilege:
* PUBLIC
* DB ROOT
* DB ROOTROLE
DB ROOT or anyone granted the DB_ROOTROLE role can grant the CREATE_SCHEMA privilege.
<<<
[[create_schema_examples]]
=== Examples of CREATE SCHEMA
* This example creates a private schema named MYSCHEMA, which will be owned by the current user:
+
```
CREATE SCHEMA myschema;
```
* This example creates a shared schema and designates CliffG as the schema owner:
+
```
CREATE SHARED SCHEMA hockey_league AUTHORIZATION "CliffG";
```
* This example creates a private schema and designates the role DBA as the schema owner:
+
```
CREATE PRIVATE SCHEMA contracts AUTHORIZATION DBA;
```
+
Users with the role DBA granted to them can grant access to objects in the CONTRACTS schema to other users and roles.
* This example creates a schema named JSMITH:
+
```
CREATE PRIVATE SCHEMA AUTHORIZATION JSmith;
```
<<<
[[create_sequence_statement]]
== CREATE SEQUENCE Statement
The CREATE SEQUENCE Statement produces an automatic ascending sequence of numeric values, which can be used by multiple users as primary key values. For example, to generate only odd numbers in a sequence, you can create a sequence defined with START WITH=1, INCREMENT BY=2.
Multiple options can be concurrently specified and separated by a space.
There are two expressions can be used with sequence:
* `seqnum(SEQUENCE, current)`: get the current value which is returned from the sequence on the current session rather than the current value of the sequence.
+
For example, a sequence object called `employee_seq` which starts with 1 and caches up to 25 with only three users.
+
If User A calls `seqnum(SEQUENCE, current)`, it will return 1 and reserve from 1 to 25, and User B immediately calls `seqnum(SEQUENCE, current)` and it will return 26 and reserve from 26 to 51, then User C immediately calls `seqnum(SEQUENCE, current)` and it will return 51 and reserve from 51 to 75.
+
Now, if User A immediately calls `seqnum(SEQUENCE, next)`, it will return 2 rather than 76. 76 will be returned if User A immediately calls `seqnum(SEQUENCE, next)` after achieving 25 where the cache for User A is exhausted.
* `seqnum(SEQUENCE, next)`: advance the sequence and retrieve the next value. For an ascending sequence, this value is increasing.
+
NOTE:
** It always returns the initial value of the sequence by calling `seqnum(SEQUENCE, current)` or `seqnum(SEQUENCE, next)` for the first time.
** It returns a new incremented value of the sequence with each subsequent reference to `seqnum(SEQUENCE, next)`.
CREATE SEQUENCE is a Trafodion SQL extension.
For more inforamation, see <<drop_sequence_statement,DROP SEQUENCE Statement>> or <<alter_sequence_statement,ALTER SEQUENCE Statement>>.
```
CREATE SEQUENCE [[catalog-name.]schema-name.]sequence
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer]
[CACHE integer | NO CACHE]
[CYCLE | NO CYCLE]
[DATA TYPE]
```
By default, it is an ascending sequence that starts with 1, increments by 1, has cache value of 25 and the
maximum value of 2^63^-2, does not cycle and belongs to LARGEINT data type.
[[create_sequence_syntax]]
=== Syntax Description of CREATE SEQUENCE
* `[[catalog-name.]schema-name.]sequence`
+
Specifies the name of the sequence to be created, with optional catalog and schema name, where each part of the name is a valid sql identifier with a maximum of 128 characters.
+
The name should be unique and does not exist for any session in the same schema. Trafodion does not support the overloading of session names. That is, you cannot register the same session name more than once.
+
If you do not fully qualify the session name, Trafodion qualifies it according to the schema of the current session. For more information, see <<identifiers,Identifiers>> and <<database_object_names,Database Object Names>>.
* `START WITH`
+
Specifies the initial value. This value can only be positive, and must be greater than or equal to MINVALUE (if NO CACHE is specified) and less than MAXVALUE.
+
NOTE: If the initial value is beyond the range specified by MINVALUE or MAXVALUE, an error will be raised.
+
If not specified, the START WITH defaults to 1. For an ascending sequence, this value is MINVALUE.
* `INCREMENT BY`
+
Specifies the increment value between consecutive sequence values.
+
NOTE: This value can only be positive, so that Trafodion only supports ascending sequence. The value must be smaller than or equal to the difference between MAXVALUE and MINVALUE.
+
If not specified, the INCREMENT BY defaults to 1.
* `MAXVALUE`
+
Specifies the maximum value where an ascending sequence stops generating values or cycles. The default is 2^^63^-2.
+
The MAXVALUE of a sequence depends on the maximum value supported by the data type, and you can also specify a MAXVALUE within the range.
+
NOTE:
** An error is returned if MAXVALUE is beyond supported range, or if subsequent value is generated for an ascending sequence when the MAXVALUE is exceeded with NO CYCLE specified.
** MAXVALUE might not be same as the actual maximum value that the sequence generated if the INCREAMENT BY is a value other than 1. For example, when a sequence is specified with START WITH 3, INCREMENT 4 and MAXVALUE 12 will return a maximum value of 11.
* `NONMAXVALUE`
+
If NOMAXVALUE is specified, for an ascending sequence, the MAXVALUE is the value you specified or the maximum value supported by the data type.
* `MINVALUE`
+
Specifies the minimum value where an ascending sequence cycles after hitting the maximum limit. The default is 1.
+
The MINVALUE of a sequence depends on the minimum value supported by the data type, and you can also specify a MINVALUE within the range.
+
If MINVALUE and START WITH are not specified, for an ascending sequence, the MINVALUE defaults to 1.
* `CYCLE`
+
Specifies that the sequence exhausts its range and wraps around after reaching its MAXVALUE.
+
If specified, for an ascending sequence, it restarts from MINVALUE after reaching MAXVALUE.
* `NOCYCLE`
+
Specifies that the sequence cannot cycle once the MAXVALUE is reached, and throws an exception when generating subsequent value if the limit is exceeded.
+
NOCYCLE is the default.
* `CACHE`
+
Specifies the range of upcoming successive values preallocated and stored in memory for speeding up future request.
+
The default CACHE value is 25.
+
The minimum CACHE value is 2.
+
The CACHE value must be less than or equal to the value determined by following formula:
+
`(MAXVALUE – START WITH + 1) / INCREMENT BY`
+
The sequence cache will be repopulated when the cache is exhausted.
+
NOTE: The unused preallocated values will be lost and leave unintended gaps if unexpected shutdown occurs or the sequence object is manually restarted, Trafodion will continue to cache new values from where it left off, so that the same value will never appear twice unless CYCLE is specified.
* `NOCACHE`
+
Specifies the values of the sequence are not preallocated, which lowers the risk of losing values even if it might cause performance degradation. In this case, every request for a new value will be synchronously updated in the system table.
+
If both CACHE and NOCACHE are not specified, it will cache 25 values by default.
* `DATA TYPE`
+
A sequence can be specified as following data types:
+
[cols="1,1,1,options="header"]
|===
| Type | Minimum value | Maximum value
| UNSIGNED SMALLINT | 1 | 2^16^-1
| UNSIGHED INTEGER | 1 | 2^32^-1
| LARGEINT | 1 | 2^63^-2
|===
+
If not specified, the default data type is LARGEINT.
<<<
[[create_sequence_considerations]]
=== Considerations for CREATE SEQUENCE
To issue a CREATE SEQUENCE statement, one of the following must be true:
* You are DB ROOT.
* You are creating the sequence in a shared schema.
* You are the private schema owner.
* You have the CREATE or CREATE_SEQUENCE component privilege for the SQL_OPERATIONS component.
+
NOTE: In this case, if you create a sequence in a private schema, it will be owned by the schema owner.
<<<
[[create_sequence_examples]]
=== Examples of CREATE SEQUENCE
The following statement creates a sequence named `employee_seq`, that starts with 1, increments by 1, has maximum value of 10000, does not cycle, caches 20 at a time and belongs to UNSIGNED SAMLLINT.
```
CREATE SEQUENCE employee_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE
CACHE 20
UNSIGNED SMALLINT
```
The first reference to `seqnum(empolyee_seq, next)` will return 10000, and the second reference will return 10001. Each subsequent reference will return a value 1 greater than the previous value.
<<<
[[create_table_statement]]
== CREATE TABLE Statement
The CREATE TABLE statement creates a {project-name} SQL table, which is a mapping of a relational SQL table to an HBase table.
The CREATE VOLATILE TABLE statement creates a temporary {project-name} SQL table that exists only during an SQL session. The
CREATE TABLE AS statement creates a table based on the data attributes of a SELECT query and populates the table using the
data returned by the SELECT query. See <<tables,Tables>>.
NOTE: DDL statements are not currently supported in transactions. That
means that you cannot run this statement inside a user-defined
transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run
this statement, AUTOCOMMIT must be turned ON (the default) for the
session.
```
CREATE [VOLATILE] TABLE [IF NOT EXISTS] table
table-spec
[SALT USING num PARTITIONS [ON (column[, column]...)]]
[STORE BY {PRIMARY KEY | (key-column-list)}]
[HBASE_OPTIONS (hbase-options-list)]
[LOAD IF EXISTS | NO LOAD]
[AS select-query]
[DIVISION BY (division-expr-list)]
CREATE [VOLATILE] TABLE [IF NOT EXISTS] table
like-spec
[SALT USING num PARTITIONS [ON (column[, column]...)]]
Note: Support for SALT on CREATE TABLE LIKE is added in Trafodion release 2.1.
table-spec is:
(table-element [,table-element]...)
table-element is:
column-definition
| [CONSTRAINT constraint-name] table-constraint
column-definition is:
column data-type
[DEFAULT default | NO DEFAULT]
[[constraint constraint-name] column-constraint]...
```
<<<
```
data-type is:
char[acter] [(length [characters])]
[character set char-set-name]
[upshift] [[not]casespecific]
| char[acter] varying (length [characters])
[character set char-set-name]
[upshift] [[not]casespecific]
| varchar (length) [character set char-set-name]
[upshift] [[not]casespecific]
| varchar2 (length) [character set char-set-name]
[upshift] [[not]casespecific]
| nchar (length) [characters] [upshift] [[not]casespecific]
| nchar varying(length [characters]) [upshift] [[not] casespecific]
| numeric [(precision [,scale])] [signed|unsigned]
| smallint [signed|unsigned]
| int[eger] [signed|unsigned]
| largeint
| dec[imal] [(precision [,scale])] [signed|unsigned]
| float [(precision)]
| real
| double precision
| date
| time [(time-precision)]
| timestamp [(timestamp-precision)]
| interval { start-field to end-field | single-field }
default is:
literal
| null
| currentdate
| currenttime
| currenttimestamp
column-constraint is:
not null
| unique
| primary key [asc[ending] | desc[ending]]
| CHECK (condition)
| REFERENCES ref-spec
table-constraint is:
UNIQUE (column-list)
| PRIMARY KEY (key-column-list)
| CHECK (condition)
| FOREIGN KEY (column-list) REFERENCES ref-spec
ref-spec is:
referenced-table [(column-list)]
```
<<<
```
column-list is:
column-name [,column-name]...
key-column-list is:
column-name [ASC[ENDING] | DESC[ENDING]]
[,column-name [ASC[ENDING] | DESC[ENDING]]]...
like-spec is:
LIKE source-table [include-option]...
hbase-options-list is:
hbase-option = 'value'[, hbase-option = 'value']...
```
[[create_table_syntax]]
=== Syntax Description of CREATE TABLE
* `VOLATILE`
+
specifies a volatile table, which is a table limited to the session that creates the table. After the session ends, the volatile table will be automatically dropped.
* `IF NOT EXISTS`
+
creates an HBase table if it does not already exist when the table is created. This option does not apply to volatile tables.
* `_table_`
+
specifies the ANSI logical name of the table. See <<database_object_names,Database Object Names>>. This name must be
unique among names of tables and views within its schema.
+
TIP: Avoid using table names that begin with "TRAF_SAMPLE_", as {project-name} SQL assumes that
such tables are persistent sample tables. See also <<update_statistics_incremental_update_statistics,
Incremental Update Statistics>>.
* `SALT USING _num_ PARTITIONS [ON (_column_[, _column_]&#8230;)]`
+
pre-splits the table into multiple regions when the table is created. Salting adds a hash value of the row key as a key
prefix, thus avoiding hot spots for sequential keys. The number of partitions that you specify can be a function of the
number of region servers present in the HBase cluster. You can specify a number from 2 to 1024. If you do not specify
columns, the default is to use all primary key columns.
+
If SALT is specified with LIKE, then this specification overrides that of the _source-table_. Note:
this is a new feature in Trafodion 2.1. In earlier releases, the SALT clause is ignored if specified
with LIKE.
<<<
* `STORE BY { PRIMARY KEY | (_key-column-list_)}`
+
specifies a set of columns on which to base the clustering key. The clustering key determines the order of rows within
the physical file that holds the table. The storage order has an effect on how you can partition the object.
** `PRIMARY KEY`
+
bases the clustering key on the primary key columns.
** `_key-column-list_`
+
bases the clustering key on the columns in the _key-column-list_. The key columns in _key-column-list_ must be specified
as NOT NULL and must be the same as the primary key columns that are defined on the table. If STORE BY is not specified,
then the clustering key is the PRIMARY KEY.
* `HBASE_OPTIONS (_hbase-option_ = '_value_'[, _hbase-option_ = '_value_']&#8230;)`
+
a list of HBase options to set for the table.
** `_hbase-option_ = '_value_'`
+
is one of these HBase options and its assigned value:
+
[options="header"]
|===
| HBase Option | Accepted Values | Default Values
| BLOCKCACHE | 'true' \| 'false' | 'true'
| BLOCKSIZE | '1024' - '64*1024*1024' | '64*1024'
| BLOOMFILTER | 'NONE' \| 'ROW' \| 'ROWCOL' | 'ROW'
| CACHE_BLOOMS_ON_WRITE | 'true' \| 'false' | 'false'
| CACHE_DATA_ON_WRITE | 'true' \| 'false' | 'false'
| CACHE_INDEXES_ON_WRITE | 'true' \| 'false' | 'false'
| COMPACT | 'true' \| 'false' | 'true'
| COMPACT_COMPRESSION | 'GZ' \| 'LZ4' \| 'LZO' \| 'NONE' \| 'SNAPPY' | 'NONE'
| COMPRESSION | 'GZ' \| 'LZ4' \| 'LZO' \| 'NONE' \| 'SNAPPY' | 'NONE'
| DATA_BLOCK_ENCODING | 'DIFF' \| 'FAST_DIFF' \| 'NONE' \| 'PREFIX' | 'NONE'
| DURABILITY | 'USE_DEFAULT' \| 'SKIP_WAL' \| 'ASYNC_WAL' \| 'SYNC_WAL' \| 'FSYNC_WAL' | 'SYNC_WAL'
| EVICT_BLOCKS_ON_CLOSE | 'true' \| 'false' | 'false'
| IN_MEMORY | 'true' \| 'false' | 'false'
| KEEP_DELETED_CELLS | 'true' \| 'false' | 'false'
| MAX_FILESIZE | '2*1024*1024' - '2^63^-1' | '10*1024*1024*1024'
| MAX_VERSIONS | '1' \| '_positive-integer_' | '1'
| MEMSTORE_FLUSH_SIZE | '1024*1024' - '2^63^-1' | '128*1024*1024'
| MIN_VERSIONS | '0' \| '_positive-integer_' | '0'
| PREFIX_LENGTH_KEY | '_positive-integer_', which should be less than maximum length of the key for the table.
It applies only if the SPLIT_POLICY is `KeyPrefixRegionSplitPolicy`. | '2'
| REPLICATION_SCOPE | '0' \| '1' | '1'
| SPLIT_POLICY | 'org.apache.hadoop.hbase.regionserver. +
ConstantSizeRegionSplitPolicy' \| +
'org.apache.hadoop.hbase.regionserver. +
IncreasingToUpperBoundRegionSplitPolicy' \| +
'org.apache.hadoop.hbase.regionserver. +
KeyPrefixRegionSplitPolicy'
|'org.apache.hadoop.hbase.regionserver. +
IncreasingToUpperBoundRegionSplitPolicy'
| TTL | '_positive-integer_', which should be less than equal to 2147483447.
| '2147483647' (forever)
|===
* `LOAD IF EXISTS`
+
loads data into an existing table. Must be used with AS _select-query_.
See <<create_table_considerations_for_load_if_exists_and_no_load_options_of_create_table_as,
Considerations For LOAD IF EXISTS and NO LOAD options of CREATE TABLE AS>>.
* `NO LOAD`
+
creates a table with the CREATE TABLE AS statement, but does not load data into the table.
See <<create_table_considerations_for_load_if_exists_and_no_load_options_of_create_table_as,
Considerations for LOAD IF EXISTS and NO LOAD options of CREATE TABLE AS>>.
* `AS _select-query_`
+
specifies a select query which is used to populate the created table. A select query can be any SQL select statement.
* `_division-expr-list_`
+
is a list of expressions that can only refer to primary key or STORE BY columns.
+
The following are allowed expressions in the DIVISON BY clause, they are monotonically increasing expressions:
+
** cast((exact_numeric_column[ + const1 ]) / const2 as numeric_data_type)
** DATE_PART('YEAR', date_part_arg )
** DATE_PART('YEARQUARTER', date_part_arg )
** DATE_PART('YEARMONTH', date_part_arg )
** DATE_PART('YEARWEEK', date_part_arg )
*** date_part_arg is one of the following:
**** datetime_col
**** datetime_col + const
**** datetime_col - const
**** ADD_MONTHS(datetime_col, const [, 0])
**** DATE_ADD(datetime_col, const)
**** DATE_SUB(datetime_col, const)
** DATE_TRUNC(const, datetime_col)
** DATEDIFF(YEAR, const, datetime_col)
** DATEDIFF(QUARTER, const, datetime_col)
** DATEDIFF(MONTH, const, datetime_col)
** DATEDIFF(WEEK, const, datetime_col)
** LEFT(character_col, const)
** SUBSTR[ING](character_col, 1, const)
** SUBSTR[ING](character_col FROM 1 FOR const)
<<<
* `_column data-type_`
+
specifies the name and data type for a column in the table. At least one column definition is required in a
CREATE TABLE statement.
** _column_ is an SQL identifier. _column_ must be unique among column names in the table. If the name is a {project-name}
SQL reserved word, you must delimit it by enclosing it in double quotes. Such delimited parts are case-sensitive.
For example: "join".
** _data-type_ is the data type of the values that can be stored in _column_. A default value must be of the same type as
the column, including the character set for a character column. See "Data Types" Data type also includes case specific
information, such as UPSHIFT.
* `[NOT] CASESPECIFIC`
+
specifies that the column contains strings that are not case specific. The default is CASESPECIFIC. Comparison between two
values is done in a case insensitive way only if both are case insensitive. This applies to comparison in a binary predicate,
LIKE predicate, and POSITION/REPLACE string function searches.
* `DEFAULT _default_ | NO DEFAULT`
+
specifies a default value for the column or specifies that the column does not have a default value.
See <<default_clause,DEFAULT Clause>>.
* `CONSTRAINT _constraint-name_`
+
specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be
unique among constraint names in its schema. If you omit the schema portions of the name you specify in _constraint-name_,
{project-name} SQL expands the constraint name by using the schema for _table_. See <<constraint_names,Constraint Names>> and
<<database_object_names,Database Object Names>>.
* `NOT NULL`
+
is a column constraint that specifies that the column cannot contain nulls. If you omit NOT NULL, nulls are allowed in
the column. If you specify both NOT NULL and NO DEFAULT, each row inserted in the table must include a value for the column.
See <<null,Null>>.
<<<
* `UNIQUE, or, UNIQUE (_column-list_)`
+
is a column or table constraint, respectively, that specifies that the column or set of columns cannot contain more than
one occurrence of the same value or set of values. If you omit UNIQUE, duplicate values are allowed unless the column or set of columns is the PRIMARY KEY.
** _column-list_ cannot include more than one occurrence of the same column. In addition, the set of columns that you
specify on a UNIQUE constraint cannot match the set of columns on any other UNIQUE constraint for the table or on the
PRIMARY KEY constraint for the table. All columns defined as unique must be specified as NOT NULL.
+
A UNIQUE constraint is enforced with a unique index. If there is already a unique index on _column-list_, {project-name} SQL
uses that index. If a unique index does not exist, the system creates a unique index.
* `PRIMARY KEY [ASC[ENDING] | DESC[ENDING]], or, PRIMARY KEY (_key-column-list_)`
+
is a column or table constraint, respectively, that specifies a column or set of columns as the primary key for the table.
_key-column-list_ cannot include more than one occurrence of the same column.
+
ASCENDING and DESCENDING specify the direction for entries in one column within the key. The default is ASCENDING.
+
The PRIMARY KEY value in each row of the table must be unique within the table. A PRIMARY KEY defined for a set of columns
implies that the column values are unique and not null. You can specify PRIMARY KEY only once on any CREATE TABLE statement.
+
{project-name} SQL uses the primary key as the clustering key of the table to avoid creating a separate, unique index to implement
the primary key constraint.
+
A PRIMARY KEY constraint is required in {project-name} SQL.
<<<
* `CHECK (_condition_)`
+
is a constraint that specifies a condition that must be satisfied for each row in the table.
See <<search_condition,Search Condition>>.
+
You cannot refer to the CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP function in a CHECK constraint, and you cannot use
subqueries in a CHECK constraint.
* `REFERENCES _ref-spec_`
+
specifies a REFERENCES column constraint. The maximum combined length of the columns for a REFERENCES constraint is 2048 bytes.
+
_ref-spec_ is:
+
`_referenced-table_ [(_column-list_)]`
** _referenced-table_ is the table referenced by the foreign key in a referential constraint. _referenced-table_ cannot be a view.
_referenced-table_ cannot be the same as _table_. _referenced-table_ corresponds to the foreign key in the _table_.
** _column-list_ specifies the column or set of columns in the _referenced-table_ that corresponds to the foreign key in _table_.
The columns in the column list associated with REFERENCES must be in the same order as the columns in the column list associated
with FOREIGN KEY. If _column-list_ is omitted, the referenced table's PRIMARY KEY columns are the referenced columns.
+
A table can have an unlimited number of referential constraints, and you can specify the same foreign key in more than one
referential constraint, but you must define each referential constraint separately. You cannot create self-referencing foreign
key constraints.
* `FOREIGN KEY (_column-list_) REFERENCES _ref-spec_`
+
is a table constraint that specifies a referential constraint for the table, declaring that a column or set of columns (called
a foreign key) in _table_ can contain only values that match those in a column or set of columns in the table specified in the
REFERENCES clause.
+
The two columns or sets of columns must have the same characteristics (data type, length, scale, precision). Without the FOREIGN
KEY clause, the foreign key in _table_ is the column being defined; with the FOREIGN KEY clause, the foreign key is the column or
set of columns specified in the FOREIGN KEY clause. For information about _ref-spec_, see REFERENCES _ref-spec_.
<<<
* `LIKE _source-table_ [_include-option_]&#8230;`
+
directs {project-name} SQL to create a table like the existing table, _source-table_, omitting constraints (with the exception of the NOT
NULL and PRIMARY KEY constraints) and partitions unless the _include-option_ clauses are specified.
** `_source-table_`
+
is the ANSI logical name for the existing table and must be unique among names of tables and views within its schema.
** `_include-option_`
*** `WITH CONSTRAINTS`
+
directs {project-name} SQL to use constraints from _source-table_. Constraint names for _table_ are randomly generated unique names.
+
When you perform a CREATE TABLE LIKE, whether or not you include the WITH CONSTRAINTS clause, the target table will have all
the NOT NULL column constraints that exist for the source table with different constraint names.
*** `WITHOUT DIVISION`
+
directs {project-name} SQL to not use divisioning from _source-table_. If this clause is omitted, then
the _table_ will have the same divisioning as the _source-table_.
*** `WITHOUT SALT`
+
directs {project-name} SQL to not use salting for _table_. If this clause is omitted,
and no SALT clause is specified,
the _table_ will have the same divisioning as the _source-table_.
+
This option cannot be specified if a SALT clause is also specified.
<<<
[[create_table_considerations]]
=== Considerations for CREATE TABLE
The following subsections provide considerations for various CREATE TABLE options:
[[create_table_authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
[[create_table_required_privileges]]
===== Required Privileges
To issue a CREATE TABLE statement, one of the following must be true:
* You are DB ROOT.
* You are creating the table in a shared schema.
* You are the private schema owner.
* You have the CREATE or CREATE_TABLE component privilege for the SQL_OPERATIONS component.
NOTE: In this case, if you create a table in a private schema, it will be owned by the schema owner.
<<<
[[create_table_privileges_needed_to_create_a_referential_integrity_constraint]]
===== Privileges Needed to Create a Referential Integrity Constraint
To create a referential integrity constraint (that is, a constraint on he table that refers to a column in another table),
one of the following must be true:
* You are DB ROOT.
* You are the owner of the referencing and referenced tables.
* You have these privileges on the referencing and referenced table:
** For the referencing table, you have the CREATE or CREATE_TABLE component privilege for the SQL_OPERATIONS component.
** For the referenced table, you have the REFERENCES (or ALL) privilege on the referenced table through your username
or through a granted role.
If the constraint refers to the other table in a query expression, you must also have SELECT privileges on the other table.
[[create_volatile_table_considerations]]
==== Considerations for CREATE VOLATILE TABLE
* Volatile temporary tables are closely linked to the session. Their name space is unique across multiple concurrent
sessions, and therefore allow multiple sessions to use the same volatile temporary table names simultaneously without
any conflicts.
* Volatile tables support creation of indexes. Volatile index can be created only on volatile table.
* Volatile tables are partitioned by the system. The number of partitions is limited to four partitions by default.
The partitions will be distributed across the cluster. The default value is four partitions regardless of the system
configuration.
* UPDATE STATISTICS is not supported for volatile tables. If you need statistics, you must use a non-volatile table instead.
* Volatile tables can be created and accessed using one-part, two-part, or three-part names. However, you must use the
same name (one part, two part, or three part) for any further DDL or DML statements on the created volatile table.
See <<create_table_examples,Examples of CREATE TABLE>>.
* {project-name} SQL allows users to explicitly specify primary key and STORE BY clauses on columns that contain null values.
<<<
* {project-name} SQL does not require that the first column in a volatile table contain not null values and be the primary key.
Instead, {project-name} SQL attempts to partition the table, if possible, using an appropriate suitable key column as the
primary and partitioning key. For more information,
see <<create_table_how_trafodion_sql_selects_suitable_keys_for_volatile_tables,
How {project-name} SQL Selects Suitable Keys for Volatile Tables>>.
[[create_table_restrictions_for_create_volatile_table]]
===== Restrictions for CREATE VOLATILE TABLE
These items are not supported for volatile tables:
* ALTER statement
* User constraints
* Creating views
* Creating non-volatile indexes on a volatile table or a volatile index on a non-volatile table
* CREATE TABLE LIKE operations
[[create_table_how_trafodion_sql_supports_nullable_keys_for_volatile_tables]]
===== How {project-name} SQL Supports Nullable Keys for Volatile Tables
* Allows nullable keys in primary key, STORE BY, and unique constraints.
* A null value is treated as the highest value for that column.
* A null value as equal to other null values and only one value is allowed for that column.
[[create_table_how_trafodion_sql_selects_suitable_keys_for_volatile_tables]]
===== How {project-name} SQL Selects Suitable Keys for Volatile Tables
{project-name} SQL searches for the first suitable column in the list of columns of the table being created. Once the column
is located, the table is partitioned on it. The searched columns in the table might be explicitly specified (as in a
CREATE TABLE statement) or implicitly created (as in a CREATE TABLE AS SELECT statement).
The suitable key column is selected only if no primary key or STORE BY clause has been specified in the statement. If any
of these clauses have been specified, they are used to select the key columns.
<<<
{project-name} SQL follows these guidelines to search for and select suitable keys:
* A suitable column can be a nullable column.
* Certain data types in {project-name} SQL cannot be used as a partitioning key. Currently, this includes any floating point
columns (REAL, DOUBLE PRECISION, and FLOAT).
* {project-name} SQL searches for a suitable column according to this predefined order:
** Numeric columns are chosen first, followed by fixed CHAR, DATETIME, INTERVAL, and VARCHAR data types.
** Within numeric data types, the order is binary NUMERIC (LARGEINT, INTEGER, SMALLINT), and DECIMAL.
** An unsigned column is given preference over a signed column.
** A non-nullable column is given preference over a nullable column.
** If all data types are the same, the first column is selected.
* If a suitable column is not located, the volatile table becomes a non-partitioned table with a system-defined SYSKEY as its primary key.
* If a suitable column is located, it becomes the partitioning key where the primary key is _suitable_column_, SYSKEY.
This causes the table to be partitioned while preventing the duplicate key and null-to-non-null errors.
The list below shows the order of precedence, from low to high, of data types when {project-name} SQL searches for a suitable
key. A data type appearing later has precedence over previously-appearing data types. Data types that do not appear in
the list below cannot be chosen as a key column.
<<<
Precedence of Data Types (From Low to High):
* VARCHAR
* INTERVAL
* DATETIME
* CHAR(ACTER)
* DECIMAL (signed, unsigned)
* SMALLINT (signed, unsigned)
* INTEGER (signed,unsigned)
* LARGEINT (signed only)
[[create_table_creating_nullable_constraints_in_a_volatile_table]]
===== Creating Nullable Constraints in a Volatile Table
These examples show the creation of nullable constraints (primary key, STORE BY, and unique) in a volatile table:
```
create volatile table t (a int, primary key(a));
create volatile table t (a int, store by primary key);
create volatile table t (a int unique);
```
[[create_table_creating_a_volatile_table_with_a_nullable_primary_key]]
===== Creating a Volatile Table With a Nullable Primary Key
This example creates a volatile table with a nullable primary key:
```
>>create volatile table t (a int, primary key(a));
--- SQL operation complete.
```
<<<
Only one unique null value is allowed:
```
>>insert into t values (null);
--- 1 row(s) inserted.
>>insert into t values (null);
*** ERROR[8102] The operation is prevented by a unique constraint.
--- 0 row(s) inserted.
```
[[create_table_examples_for_selecting_suitable_keys_for_volatile_tables]]
===== Examples for Selecting Suitable Keys for Volatile Tables
These examples show the order by which {project-name} SQL selects a suitable key based on the precedence rules described in
<<create_table_how_trafodion_sql_selects_suitable_keys_for_volatile_tables,
How {project-name} SQL Selects Suitable Keys for Volatile Tables>>:
* Selects column a as the primary and partitioning key:
+
```
create volatile table t (a int);
```
* Selects column b because int has a higher precedence than char:
+
```
create volatile table t (a char(10), b int);
```
* Selects column b because not null has precedence over nullable columns:
+
```
create volatile table t (a int, b int not null);
```
* Selects column b because int has precedence over decimal:
+
```
create volatile table t (a decimal(10), b int);
```
* Selects the first column, a, because both columns have the same data type:
+
```
create volatile table t (a int not null, b int not null);
```
<<<
* Selects column b because char has precedence over date:
+
```
create volatile table t (a date, b char(10));
```
* Selects column b because the real data type is not part of the columns to be examined:
+
```
create volatile table t (a real, b date);
```
* Does not select any column as the primary/partitioning key. SYSKEY is used automatically.
+
```
create volatile table t (a real, b double precision not null);
```
Similar examples would be used for CREATE TABLE AS SELECT queries.
[[create_table_considerations_for_create_table_like]]
==== Considerations for CREATE TABLE &#8230; LIKE
The CREATE TABLE LIKE statement does not create views, owner information, or privileges for the new table based on the
source table. Privileges associated with a new table created by using the LIKE specification are defined as if the new
table is created explicitly by the current user.
[[create_table_like_and_file_attributes]]
===== CREATE TABLE &#8230; LIKE and File Attributes
CREATE TABLE &#8230; LIKE creates a table like another table, with the exception of file attributes. File attributes
include COMPRESSION, and so on. If you do not include the attribute value as part of the CREATE TABLE &#8230; LIKE
command, SQL creates the table with the default value for the attributes and not the value from the source object.
For example, to create a table like another table that specifies compression, you must specify the compression attribute
value as part of the CREATE TABLE&#8230; LIKE statement. In the following example, the original CREATE TABLE statement
creates a table without compression. However, in the CREATE TABLE &#8230; LIKE statement, compression is specified.
```
-- Original Table create table NPTEST
(FIRST_NAME CHAR(12) CHARACTER SET ISO88591 COLLATE DEFAULT NO DEFAULT NOT NULL
, LAST_NAME CHAR(24) CHARACTER SET ISO88591 COLLATE DEFAULT NO DEFAULT NOT NULL
, ADDRESS CHAR(128) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL
, ZIP INT DEFAULT 0
, PHONE CHAR(10) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL
, SSN LARGEINT NO DEFAULT NOT NULL
, INFO1 CHAR(128) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL
, INFO2 CHAR(128) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL
, primary key (SSN,first_name,last_name)
)
max table size 512
-- CREATE TABLE LIKE
create table LSCE002 like NPTEST ATTRIBUTE compression type hardware;
```
[[create_table_considerations_for_create_table_as]]
==== Considerations for CREATE TABLE AS
These considerations apply to CREATE TABLE AS:
* Access to the table built by CREATE TABLE AS will be a full table scan because a primary and clustering key cannot
be easily defined.
* Compile time estimates and runtime information is not generated for CREATE TABLE AS tables.
* You cannot manage CREATE TABLE AS tables using WMS compile time or runtime rules.
* You cannot specify a primary key for a CREATE TABLE AS table without explicitly defining all the columns in the
CREATE TABLE statement.
* You cannot generate an explain plan for a CREATE TABLE AS &#8230;INSERT/SELECT statement. You can, however, use the
EXPLAIN plan for a CREATE TABLE AS &#8230; INSERT/SELECT statement if you use the NO LOAD option.
* You cannot use the ORDER BY clause in a CREATE TABLE AS statement. The compiler transparently orders the selected
rows to improve the efficiency of the insert.
<<<
[[create_table_considerations_for_load_if_exists_and_no_load_options_of_create_table_as]]
===== Considerations for LOAD IF EXISTS and NO LOAD options of CREATE TABLE AS
The LOAD IF EXISTS option in a CREATE TABLE AS statement causes data to be loaded into an existing table. If you do not
specify the LOAD IF EXISTS option and try to load data into an existing table, the CREATE TABLE AS statement fails to
execute. Use the LOAD IF EXISTS option with the AS clause in these scenarios:
* Running CREATE TABLE AS without re-creating the table. The table must be empty. Otherwise, the CREATE TABLE AS statement
returns an error. Delete the data in the table by using a DELETE statement before issuing the CREATE TABLE AS statement.
* Using CREATE TABLE AS to incrementally add data to an existing table. You must start a user-defined transaction before
issuing the CREATE TABLE AS statement. If you try to execute the CREATE TABLE AS statement without starting a user-defined
transaction, an error is returned, stating that data already exists in the table. With a user-defined transaction, newly
added rows are rolled back if an error occurs.
The NO LOAD option in a CREATE TABLE AS statement creates a table with the CREATE TABLE AS statement, but does not load data
into the table. The option is useful if you must create a table to review its structure and to analyze the SELECT part of the
CREATE TABLE AS statement with the EXPLAIN statement. You can also use EXPLAIN to analyze the implicated INSERT/SELECT part of
the CREATE TABLE AS &#8230; NO LOAD statement. For example:
```
CREATE TABLE ttgt NO LOAD AS (SELECT ...);
```
[[create_table_trafodion_sql_extensions_to_create_table]]
=== {project-name} SQL Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. {project-name} SQL extensions to the CREATE TABLE
statement are ASCENDING, DESCENDING, and PARTITION clauses. CREATE TABLE LIKE is also an extension.
<<<
[[create_table_examples]]
=== Examples of CREATE TABLE
==== Examples of CREATE TABLE
* This example creates a table. The clustering key is the primary key.
+
```
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL, partnum NUMERIC (4)
UNSIGNED NO DEFAULT NOT NULL, unit_price NUMERIC (8,2) NO DEFAULT NOT
NULL, qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) );
```
* This example creates a table like the JOB table with the same constraints:
+
```
CREATE TABLE PERSNL.JOB_CORPORATE LIKE PERSNL.JOB WITH CONSTRAINTS;
```
* This is an example of NOT CASESPECIFIC usage:
+
```
CREATE TABLE T (a char(10) NOT CASESPECIFIC, b char(10));
INSERT INTO T values ('a', 'A');
```
* A row is not returned in this example. Constant ‘A’ is case sensitive, whereas column ‘a’ is insensitive.
+
```
SELECT * FROM T WHERE a = 'A';
```
* The row is returned in this example. Both sides are case sensitive.
+
```
SELECT * FROM T WHERE a = 'A' (not casespecific);
```
* The row is returned in this example. A case sensitive comparison is done because column ‘b’ is case sensitive.
+
```
SELECT * FROM T WHERE b = 'A';
```
* The row is returned in this example. A case sensitive comparison is done because column ‘b’ is case sensitive.
+
```
SELECT * FROM T WHERE b = 'A' (not casespecific);
```
* This is the first example of DIVISION BY usage.
+
```
CREATE TABLE call_home_data
(id LARGEINT NOT NULL,
ts TIMESTAMP(6) NOT NULL,
device_status VARCHAR(200),
PRIMARY KEY (id, ts))
SALT USING 16 PARTITIONS ON (id)
DIVISION BY (date_trunc('day', ts));
```
* This is the second example of DIVISION BY usage.
+
```
CREATE TABLE sales1
(store_id INT NOT NULL,
item_id INT NOT NULL,
sale_date DATE DEFAULT DATE '2000-01-01' NOT NULL,
sale_amt NUMERIC(10,2),
PRIMARY KEY (store_id, item_id, sale_date))
DIVISION BY (DATEDIFF(YEAR, '2017-11-02', sale_date));
```
* This is the third example of DIVISION BY usage.
+
```
CREATE TABLE sales2
(store_id INT NOT NULL,
item_id INT NOT NULL,
sale_date DATE DEFAULT DATE '2000-01-01' NOT NULL,
sale_amt NUMERIC(10,2),
chcol CHAR(20) NOT NULL,
PRIMARY KEY (store_id, item_id, sale_date, chcol))
DIVISION BY (SUBSTR(chcol, 1, 5));
```
* This example creates a table using varchar2 data type which is same as varchar.
+
```
CREATE TABLE testvarchar2(c1 varchar(32), c2 varchar2(32) upshift not casespecific);
--- SQL operation complete.
SHOWDDL testvarchar2;
CREATE TABLE TRAFODION.SEABASE.TESTVARCHAR2
(
C1 VARCHAR(32) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
, C2 VARCHAR(32) CHARACTER SET ISO88591 COLLATE
DEFAULT UPSHIFT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
```
<<<
[[create_table_examples_create_table_as]]
==== Examples of CREATE TABLE AS
This section shows the column attribute rules used to generate and specify the column names and data types of the table
being created.
* If _column-attributes_ are not specified, the select list items of the select-query are used to generate the column
names and data attributes of the created table. If the select list item is a column, then it is used as the name of the
created column. For example:
+
```
create table t as select a,b from t1
```
+
Table t has 2 columns named (a,b) and the same data attributes as columns from table t1.
* If the select list item is an expression, it must be renamed with an AS clause. An error is returned if expressions are
not named. For example:
+
```
create table t as select a+1 as c from t1
```
+
Table t has 1 column named (c) and data attribute of (a+1)
+
```
create table t as select a+1 from t1
```
+
An error is returned, expression must be renamed.
* If _column-attributes_ are specified and contains _datatype-info_, then they override the attributes of the select items
in the select query. These data attributes must be compatible with the corresponding data attributes of the select list items
in the select-query.
+
```
create table t(a int) as select b from t1
```
+
Table t has one column named "a" with data type "int".
+
```
create table t(a char(10)) as select a+1 b from t1;
```
+
An error is returned because the data attribute of column "a", a char, does not match the data attribute of the select list
item "b" a numeric.
* If _column-attributes_ are specified and they only contain _column-name_, then the specified column-name override any name
that was derived from the select query.
+
```
create table t(c,d) as select a,b from t1
```
+
Table t has 2 columns, c and d, which has the data attributes of columns a and b from table t1.
* If _column-attributes_ are specified, then they must contain attributes corresponding to all select list items in the
_select-query_. An error is returned, if a mismatch exists.
+
```
create table t(a int) as select b,c from t1
```
+
An error is returned. Two items need to be specified as part of the table-attributes.
* The _column-attributes_ must specify either the _column-name datatype-info_ pair or just the _column-name_ for all
columns. You cannot specify some columns with just the name and others with name and data type.
+
```
create table t(a int, b) as select c,d from t1
```
+
An error is returned.
* In the following example, table t1 is created. Table t2 is created using the CREATE TABLE AS syntax without table attributes:
+
```
CREATE TABLE t1 (c1 int not null primary key, c2 char(50));
CREATE TABLE t2 (c1 int, c2 char (50) UPSHIFT NOT NULL) AS SELECT * FROM t1;
```
<<<
[[create_table_examples_create_external_table]]
==== Examples of CREATE EXTERNAL TABLE
This example compares the execution time of using external table and not using external table when reading hive tables.
The former takes less time than the latter, since the trafodion external table supplies upper bounds for varchar lengths, which may lead to better plans and/or run-time behavior.
TIP: Either running UPDATE STATISTICS or using a trafodion external table may improve performance. To get full performance benefit, you must run UPDATE STATISTICS and use the trafodion external table.
This is the definition of the *hive table* _test_mix_ which has a trafodion external table, the size is 137.6G.
```
SQL>SHOWDDL test_mix;
CREATE EXTERNAL TABLE test_mix(
mix_id int not null not droppable primary key,
mix_age int,
mix_name string,
mix_timestamp01 string,
mix_timestamp02 string,
mix_other01 string,
mix_other02 string,
mix_other03 string,
mix_other04 string,
mix_other05 string,
mix_other06 string,
mix_other07 string,
mix_other08 string,
mix_other09 string,
mix_other10 string,
mix_other11 string,
mix_other12 string,
mix_other13 string,
mix_other14 string,
mix_other15 string
)
row format delimited fields terminated by '|'
location '/user/trafodion/data/ExternalTable_data';
-- 01-06 short 06-11medium 12-15 long
```
This is the definition of the *trafodion external table* _test_mix_, it has the same structure and size as the hive table _test_mix_.
```
SQL>SHOWDDL text_mix;
CREATE EXTERNAL TABLE test_mix(
mix_id int,
mix_age int,
mix_name varchar(20),
mix_timestamp01 timestamp,
mix_timestamp02 varchar(20),
mix_other01 varchar(12),
mix_other02 varchar(12),
mix_other03 varchar(12),
mix_other04 varchar(12),
mix_other05 varchar(12),
mix_other06 varchar(12),
mix_other07 varchar(64),
mix_other08 varchar(64),
mix_other09 varchar(64),
mix_other10 varchar(64),
mix_other11 varchar(128),
mix_other12 varchar(128),
mix_other13 varchar(128),
mix_other14 varchar(1024),
mix_other15 varchar(1024)
)for hive.hive.test_mix;
-- 01-06 short 07-11medium 12-15 long
```
* When executing the following query:
+
```
SELECT [LAST 1] * FROM hive.hive.test_mix WHERE mix_other02 = 'Ot';
```
+
it takes approximately *6 minutes* (average value) to get the result using the trafodion external table.
+
[cols="20%,20%,20%,20%,20%",options="header"]
|=====
| | First Result | Second Result | Third Result | Average Value
| Start Time | 2018/03/07 18:40:31.655159 | 2018/03/07 09:37:50.801345 | 2018/03/07 09:45:05.921706 |
| End Time | 2018/03/07 18:49:08.879780 | 2018/03/07 09:43:16.695492 | 2018/03/07 09:48:58.251764 |
| Elapsed Time | 2018/03/07 00:08:37.224621 | 2018/03/07 00:05:25.894147 | 2018/03/07 00:03:52.330058 | 00:06:12.23
| Compile Time | 2018/03/07 00:00:03.497624 | 2018/03/07 00:00:11.595054 | 2018/03/07 00:00:00.551781 | 00:00:04.8
| Execution Time | 2018/03/07 00:08:33.715742 | 2018/03/07 00:05:14.295840 | 2018/03/07 00:03:51.708673 | *00:06:12*
|=====
+
while it takes approximately *14 minutes* (average value) to get the result without using the trafodion external table.
+
[cols="20%,20%,20%,20%,20%",options="header"]
|=====
| | First Result | Second Result | Third Result | Average Value
| Start Time | 2018/03/07 13:33:46.722646 | 2018/03/07 14:39:30.323730 | 2018/03/07 14:54:58.177258 |
| End Time | 2018/03/07 13:48:35.028916 | 2018/03/07 14:53:53.887911 | 2018/03/07 15:09:11.517646 |
| Elapsed Time | 2018/03/07 00:14:48.306270 | 2018/03/07 00:14:23.564181 | 2018/03/07 00:14:13.340388 | 00:14:28.40
| Compile Time | 2018/03/07 00:00:00.773770 | 2018/03/07 00:00:00.388777 | 2018/03/07 00:00:14.856643 | 00:00:04
| Execution Time | 2018/03/07 00:14:47.530017 | 2018/03/07 00:14:23.146420 | 2018/03/07 00:13:58.463850 | *00:13:58*
|=====
* When executing the following query:
+
```
SELECT [LAST 1] mix_other02, substring(mix_other12 from 1 for 10) FROM hive.hive.test_mix WHERE substring(mix_other02 from 1 for 1) = 'O';
```
+
it takes approximately *6 minutes* (average value) to get the result using the trafodion external table.
+
[cols="20%,20%,20%,20%,20%",options="header"]
|=====
| | First Result | Second Result | Third Result | Average Value
| Start Time | 2018/03/09 14:07:59.353015 | 2018/03/09 14:16:27.725035 | 2018/03/09 14:41:01.454408 |
| End Time | 2018/03/09 14:15:05.979546 | 2018/03/09 14:20:44.939776 | 2018/03/09 14:46:58.238246 |
| Elapsed Time | 2018/03/09 00:07:06.626531 | 2018/03/09 00:04:17.214741 | 2018/03/09 00:05:56.783838 | 00:05:59
| Compile Time | 2018/03/09 00:00:00.197789 | 2018/03/09 00:00:00.296705 | 2018/03/09 00:00:00.227511 | 00:00:00.23
| Execution Time | 2018/03/09 00:07:06.411065 | 2018/03/09 00:04:16.873090 | 2018/03/09 00:05:56.554411 | *00:05:59*
|=====
+
while it takes approximately 35 minutes (average value) to get the result without using the trafodion external table.
+
[cols="20%,20%,20%,20%,20%",options="header"]
|=====
| | First Result | Second Result | Third Result | Average Value
| Start Time | 2018/03/09 11:01:12.676307 | 2018/03/09 11:35:54.514479 | 2018/03/09 13:15:07.006658 |
| End Time | 2018/03/09 11:35:16.264756 | 2018/03/09 12:11:09.587147 | 2018/03/09 13:49:23.740406 |
| Elapsed Time | 2018/03/09 00:34:03.588449 | 2018/03/09 00:35:15.072668 | 2018/03/09 00:34:16.733748 | 34:44:00
| Compile Time | 2018/03/09 00:00:00.703053 | 2018/03/09 00:00:00.280146 | 2018/03/09 00:00:00.536929 | 00:00:00.5
| Execution Time | 2018/03/09 00:34:02.828529 | 2018/03/09 00:35:14.743914 | 2018/03/09 00:34:16.155336 | *34:44:00*
|=====
<<<
[[create_view_statement]]
== CREATE VIEW Statement
The CREATE VIEW statement creates a {project-name} SQL view. See <<views,Views>>.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside
a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT
must be turned ON (the default) for the session.
```
CREATE [OR REPLACE] VIEW view
[(column-name ] [,column-name ...)]
AS query-expr [order-by-clause]
[WITH CHECK OPTION]
```
[[create_view_syntax]]
=== Syntax Description of CREATE VIEW
* `OR REPLACE`
+
creates a view if one does not exist or replaces a view if a view of the same name exists. The view being replaced might
have the same view definition or a different view definition.
* `_view_`
+
specifies the ANSI logical name of the view. See <<database_object_names,Database Object Names>>. This name must be unique
among names of tables and views within its schema.
* `(_column-name_ [,_column-name_ ]&#8230;)`
+
specifies names for the columns of the view. Column names in the list must match one-for-one with columns in the table
specified by _query-expr_.
+
<<<
+
If you omit this clause, columns in the view have the same names as the corresponding columns in _query-expr_. You must
specify this clause if any two columns in the table specified by _query-expr_ have the same name or if any column of that
table does not have a name. For example, this query expression SELECT MAX(salary), AVG(salary) AS average_salary FROM
employee the first column does not have a name.
+
No two columns of the view can have the same name; if a view refers to more than one table and the select list refers to
columns from different tables with the same name, you must specify new names for columns that would otherwise have duplicate
names.
* `AS _query-expr_`
+
specifies the columns for the view and sets the selection criteria that determines the rows that make up the view. For
information about character string literals, see <<character_string_literals,Character String Literals>>.
For the syntax and syntax description of _query-expr_, see <<select_statement,SELECT Statement>>. The CREATE VIEW statement
provides this restriction with regard to the _query-expr_ syntax: [ANY_N_], [FIRST _N_] select list items are not allowed
in a view.
* `_order-by-clause_`
+
specifies the order in which to sort the rows of the final result table. For the syntax and syntax description of the
_order-by-clause_, see <<select_statement,SELECT Statement>>. The CREATE VIEW statement restricts the _order-by-clause_
with regard to the _access-clause_ and _mode-clause_. The _access-mode_ and _mode-clause_ cannot follow the
_order-by-clause_.
* `WITH CHECK OPTION`
+
specifies that no row can be inserted or updated in the database through the view unless the row satisfies the view
definition—that is, the search condition in the WHERE clause of the query expression must evaluate to true for any
row that is inserted or updated. This option is only allowed for updatable views.
+
If you omit this option, a newly inserted row or an updated row need not satisfy the view definition, which means that
such a row can be inserted or updated in the table but does not appear in the view. This check is performed each time a
row is inserted or updated.
+
WITH CHECK OPTION does not affect the query expression; rows must always satisfy the view definition.
<<<
[[create_view_considerations]]
=== Considerations for CREATE VIEW
* If you specify CREATE OR REPLACE VIEW:
** A new view is created if a view of the same name does not exist.
** If a view of same name exists, the old view definition is dropped, and a view with a new definition is created. No check
will be done to see if the new view is identical to the view it is replacing. The CREATE OR REPLACE VIEW command will
unilaterally drop the old view definition and replace it with the new view definition.
** The privileges granted on the old view will be re-granted on the new view. If the re-grant of privileges fails, the
CREATE OR REPLACE VIEW operation fails.
** When CREATE OR REPLACE VIEW replaces an existing view, any dependent views will be dropped.
* You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list.
For example, GROUP BY 3, 2, 1.
* Dynamic parameters are not allowed.
[[create_view_effect_of_adding_a_column_on_view_definitions]]
==== Effect of Adding a Column on View Definitions
The addition of a column to a table has no effect on any existing view definitions or conditions included in constraint
definitions. Any implicit column references specified by SELECT * in view or constraint definitions are replaced by
explicit column references when the definition clauses are originally evaluated.
[[create_view_authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
To issue a CREATE VIEW statement, you must have SELECT privileges on the objects underlying the view or be the owner of
the objects underlying the view, and one of the following must be true:
* You are DB ROOT.
* You are creating the view in a shared schema.
* You are the private schema owner.
* You have the CREATE or CREATE_VIEW component privilege for the SQL_OPERATIONS component.
+
NOTE: In this case, if you create a view in a private schema, it will be owned by the schema owner.
When you create a view on a single table, the owner of the view is automatically given all privileges WITH GRANT OPTION
on the view. However, when you create a view that spans multiple tables, the owner of the view is given only SELECT
privileges WITH GRANT OPTION. If you try to grant privileges to another user on the view other than SELECT, you will
receive a warning that you lack the grant option for that privilege.
[[create_view_updatable_and_non_updatable_views]]
==== Updatable and Non-Updatable Views
Single table views can be updatable. Multi-table views cannot be updatable.
To define an updatable view, a query expression must also meet these requirements:
* It cannot contain a JOIN, UNION, or EXCEPT clause.
* It cannot contain a GROUP BY or HAVING clause.
* It cannot directly contain the keyword DISTINCT.
* The FROM clause must refer to exactly one table or one updatable view.
* It cannot contain a WHERE clause that contains a subquery.
* The select list cannot include expressions or functions or duplicate column names.
[[create_view_order_by_clause_guidelines]]
==== ORDER BY Clause Guidelines
The ORDER BY clause can be specified in the SELECT portion of a CREATE VIEW definition. Any SELECT syntax that is valid
when the SELECT portion is specified on its own is also valid during the view definition. An ORDER BY clause can contain
either the column name from the SELECT list or from _select-list-index_.
When a DML statement is issued against the view, the rules documented in the following sections are used to apply the
ORDER BY clause.
<<<
===== When to Use ORDER BY
An ORDER BY clause is used in a view definition only when the clause is under the root of the Select query that uses that
view. If the ORDER BY clause appears in other intermediate locations or in a subquery, it is ignored.
Consider this CREATE VIEW statement:
```
create view v as select a from t order by a; select * from v x, v y;
```
Or this INSERT statement:
```
insert into t1 select * from v;
```
In these two examples, the ORDER BY clause is ignored during DML processing because the first appears as part of a
derived table and the second as a subquery selects, both created after the view expansion.
If the same query is issued using explicit derived tables instead of a view, a syntax error is returned:
```
select * from (select a from t order by a) x, (select a from t order by a) y;
```
This example returns a syntax error because an ORDER BY clause is not supported in a subquery.
The ORDER BY clause is ignored if it is part of a view and used in places where it is not supported. This is different
than returning an error when the same query was written with explicit ORDER BY clause, as is shown in the preceding examples.
===== ORDER BY in a View Definition With No Override
If the SELECT query reads from the view with no explicit ORDER BY override, the ORDER BY semantics of the view definition
are used.
In this example, the ordering column is the one specified in the CREATE VIEW statement:
```
create view v as select * from t order by a Select * from v
```
<<<
The SELECT query becomes equivalent to:
```
select * from t order by a;
```
===== ORDER BY in a View Definition With User Override
If a SELECT query contains an explicit ORDER BY clause, it overrides the ORDER BY clause specified in the view definition.
For example:
```
create view v as select a,b from t order by a; select * from v order by b;
```
In this example, order by b overrides the order by a specified in the view definition. The SELECT query becomes equivalent to:
```
select a,b from t order by b;
```
==== Nested View Definitions
In case of nested view definitions, the ORDER BY clause in the topmost view definition overrides the ORDER BY clause of any
nested view definitions.
For example:
```
create view v1 as select a,b from t1 order by a;
create view v2 as select a,b from v1 order by b;
select * from v2;
```
In this example, the ORDER BY specified in the definition of view v2 overrides the ORDER BY specified in the definition of view v1.
The SELECT query becomes equivalent to:
```
select a,b from (select a, b from t) x order by b;
```
<<<
[[create_view_examples]]
=== Examples of CREATE VIEW
* This example creates a view on a single table without a view column list:
+
```
CREATE VIEW SALES.MYVIEW1 AS
SELECT ordernum, qty_ordered FROM SALES.ODETAIL;
```
* This example replaces the view, MYVIEW1, with a different view definition:
+
```
CREATE OR REPLACE VIEW SALES.MYVIEW1 AS
SELECT ordernum, qty_ordered
FROM SALES.ODETAIL WHERE unit_price > 100;
```
* This example creates a view with a column list:
+
```
CREATE VIEW SALES.MYVIEW2
(v_ordernum, t_partnum) AS SELECT v.ordernum, t.partnum
FROM SALES.MYVIEW1 v, SALES.ODETAIL t;
```
* This example creates a view from two tables by using an INNER JOIN:
+
```
CREATE VIEW MYVIEW4 (v_ordernum, v_partnum) AS
SELECT od.ordernum, p.partnum
FROM SALES.ODETAIL OD INNER JOIN SALES.PARTS P
ON od.partnum = p.partnum;
```
<<<
[[create_view_vertical_partition_example]]
==== Vertical Partition Example
This example creates three logical vertical partitions for a table, vp0, vp1, and vp2 and then creates a view vp to access them.
A view can be used to obtain a composite representation of a set of closely related tables. In the following example
tables vp0, vp1 and vp2 all have a key column a. This key column is known to contain identical rows for all three tables.
The three tables vp0, vp1 and vp2 also contain columns b, c and d respectively. We can create a view vp that combines
these three tables and provides the interface of columns a, b, c and d belonging to a single object.
{project-name} SQL has the ability to eliminate redundant joins in a query. Redundant joins occur when:
* Output of join contains expressions from only one of its two children
* Every row from this child will match one and only one row from the other child
Suppose tables A and B denote generic tables. To check if the rule "every row from this child will match one and only one row
from the other child" is true, {project-name} SQL uses the fact that the join of Table A with table or subquery B preserves all the
rows of A if the join predicate contains an equi-join predicate that references a key of B, and one of the following is true:
The join is a left outer join where B is the inner table. In this example, for the join between vp0 and vp1,vp0 fills the role
of table A and vp1 fills the role of table B. For the join between vp1 and vp2, vp1 fills the role of table A and vp2 fills
the role of table B.
The view vp shown in this example uses left outer joins to combine the three underlying tables. Therefore, if the select list
in a query that accesses vp does not contain column d from vp2 then the join to table vp2 in the view vp will not be performed.
```
create table vp0(a integer not null, b integer, primary key(a));
create table vp1(a integer not null, c integer, primary key(a));
create table vp2(a integer not null, d integer, primary key(a));
create view vp(a,b,c,d) as
select vp0.a, b, c, d
from vp0
left outer join vp1 on vp0.a=vp1.a
left outer join vp2 on vp0.a=vp2.a;
select a, b from vp; -- reads only vp0
select a, c from vp; -- reads vp0 and vp1
select d from vp; -- reads vp0 and vp2
```
<<<
[[delete_statement]]
== DELETE Statement
The DELETE statement is a DML statement that deletes a row or rows from a table or an updatable view. Deleting rows from
a view deletes the rows from the table on which the view is based. DELETE does not remove a table or view, even if you
delete the last row in the table or view.
{project-name} SQL provides searched DELETE—deletes rows whose selection depends on a search condition.
For the searched DELETE form, if no WHERE clause exists, all rows are deleted from the table or view.
```
Searched DELETE is:
DELETE FROM table
[WHERE search-condition ]
[[for] access-option access]
access-option is:
read committed
```
[[delete_syntax]]
=== Syntax Description of DELETE
* `_table_`
+
names the user table or view from which to delete rows. _table_ must be a base table or an updatable view. To refer to a
table or view, use the ANSI logical name. See <<database_object_names,Database Object Names>>.
* `WHERE _search-condition_`
+
specifies a search condition that selects rows to delete. Within the search condition, any columns being compared are
columns in the table or view being deleted from. See <<search_condition,Search Condition>>
+
If you do not specify a search condition, all rows in the table or view are deleted.
* `[FOR] _access-option_ ACCESS`
+
specifies the access option required for data used to evaluate the search condition.
See <<data_consistency_and_access_options,Data Consistency and Access Options>>.
** `READ `COMMITTED
+
specifies that any data used to evaluate the search condition must come from committed rows.
+
The default access option is the isolation level of the containing transaction.
[[delete_considerations]]
=== Considerations for DELETE
[[delete_authorization_requirements]]
==== Authorization Requirements
DELETE requires authority to read and write to the table or view being deleted from and authority to read tables or views
specified in subqueries used in the search condition.
[[delete_transaction-initiation-and-termination]]
==== Transaction Initiation and Termination
The DELETE statement automatically initiates a transaction if no transaction is active. Otherwise, you can explicitly
initiate a transaction with the BEGIN WORK statement. When a transaction is started, the SQL statements execute within
that transaction until a COMMIT or ROLLBACK is encountered or an error occurs.
[[delete_isolation-levels-of-transactions-and-access-options-of-statements]]
==== Isolation Levels of Transactions and Access Options of Statements
The isolation level of an SQL transaction defines the degree to which the operations on data within that transaction
are affected by operations of concurrent transactions. When you specify access options for the DML statements within
a transaction, you override the isolation level of the containing transaction. Each statement then executes with
its individual access option.
[[delete_examples]]
=== Examples of DELETE
* Remove all rows from the JOB table:
+
```
DELETE FROM persnl.job;
--- 10 row(s) deleted.
```
* Remove from the table ORDERS any orders placed with sales representative 220 by any customer except customer number 1234:
+
```
DELETE FROM sales.orders
WHERE salesrep = 220 AND custnum <> 1234;
--- 2 row(s) deleted.
```
* Remove all suppliers not in Texas from the table PARTSUPP:
+
```
DELETE FROM invent.partsupp WHERE suppnum IN
(SELECT suppnum FROM samdbcat.invent.supplier WHERE state <> 'TEXAS');
--- 41 row(s) deleted.
```
+
This statement achieves the same result:
+
```
DELETE FROM invent.partsupp WHERE suppnum NOT IN
SELECT suppnum FROM samdbcat.invent.supplier WHERE state = 'TEXAS');
--- 41 row(s) deleted.
```
* This is an example of a self-referencing DELETE statement, where the table from which rows are deleted is scanned in a subquery:
+
```
delete from table1 where a in (select a from table1 where b > 200)
```
<<<
[[drop_function_statement]]
== DROP FUNCTION Statement
The DROP FUNCTION statement removes a user-defined function (UDF) from the {project-name} database.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside
a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT
must be turned ON (the default) for the session.
```
DROP FUNCTION [[catalog-name.]schema-name.]function-name
```
[[drop_function_syntax]]
=== Syntax Description of DROP FUNCTION
* `\[[_catalog-name_.]_schema-name_.]_function-name_`
+
specifies the ANSI logical name of the function, where each part of the name is a valid sql identifier with a maximum of
128 characters. specify the name of a function that has already been registered in the schema. if you do not fully qualify
the function name, trafodion sql qualifies it according to the schema of the current session. for more information,
see <<identifiers,identifiers>> and <<database_object_names,database object names>>.
[[drop_function_considerations]]
=== Considerations for DROP FUNCTION
[[drop_function_required_privileges]]
==== Required Privileges
To issue a DROP FUNCTION statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the function.
* You have the DROP or DROP_ROUTINE component privilege for SQL_OPERATIONS component.
<<<
[[drop_function_examples]]
=== Examples of DROP FUNCTION
* This DROP FUNCTION statement removes the function named ADD2 from the default schema:
+
```
DROP FUNCTION add2;
```
* This DROP FUNCTION statement removes the function named MMA5 from the default schema:
+
```
DROP PROCEDURE mma5;
```
* This DROP FUNCTION statement removes the function named REVERSE from the default schema:
+
```
DROP PROCEDURE reverse;
```
<<<
[[drop_index_statement]]
== DROP INDEX Statement
The DROP INDEX statement drops a {project-name} SQL index. See <<indexes,Indexes>>.
DROP INDEX is a {project-name} SQL extension.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside
a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT
must be turned ON (the default) for the session.
```
DROP [VOLATILE] INDEX index
```
[[drop_index_syntax]]
=== Syntax Description of DROP INDEX
* `_index_`
+
is the index to drop.
+
For information, see <<database_object_names,Database Object Names>>.
[[drop_index_considerations]]
=== Considerations for DROP INDEX
[[drop_index_required_privileges]]
==== Required Privileges
To issue a DROP INDEX statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the index or the table associated with the index.
* You have the DROP or DROP_INDEX component privilege for the SQL_OPERATIONS component.
<<<
[[drop_index_examples]]
=== Examples of DROP INDEX
* This example drops an index:
+
```
DROP INDEX myindex;
```
* This example drops a volatile index:
+
```
DROP VOLATILE INDEX vindex;
```
<<<
[[drop_library_statement]]
== DROP LIBRARY Statement
The DROP LIBRARY statement removes a library object from the {project-name} database and also removes the library file
referenced by the library object.
DROP LIBRARY is a {project-name} SQL extension.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement
inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement,
AUTOCOMMIT must be turned ON (the default) for the session.
```
DROP LIBRARY [[catalog-name.]schema-name.]library-name [restrict | cascade]
```
[[drop_library_syntax]]
=== Syntax Description of DROP LIBRARY
* `\[[_catalog-name_.]_schema-name_.]_library-name_`
+
specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a
maximum of 128 characters. specify the name of a library object that has already been registered in the schema. if
you do not fully qualify the library name, trafodion sql qualifies it according to the schema of the current session.
for more information, see <<identifiers,identifiers>> and <<database_object_names,database object names>>.
* `[restrict | cascade]`
+
if you specify restrict, the drop library operation fails if any stored procedures in java (spjs) or user-defined
functions (UDFs) were created based on the specified library.
+
if you specify cascade, any such dependent procedures or functions are removed as part of the drop library operation.
+
the default value is restrict.
<<<
[[drop_library_considerations]]
=== Considerations for DROP LIBRARY
* RESTRICT requires that all procedures and functions that refer to the library object be dropped before you drop the
library object. CASCADE automatically drops any procedures or functions that are using the library.
* If the library filename referenced by the library object does not exist, {project-name} SQL issues a warning.
[[drop_library_required_privileges]]
==== Required Privileges
To issue a DROP LIBRARY statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the library.
* You have the DROP or DROP_LIBRARY component privilege for the SQL_OPERATIONS component.
[[drop_library_examples]]
=== Examples of DROP LIBRARY
* This DROP LIBRARY statement removes the library named SALESLIB from the SALES schema, removes the Sales2.jar file
referenced by the library, and drops any stored procedures in Java (SPJs) that were created based on this library:
+
```
DROP LIBRARY sales.saleslib CASCADE;
```
* This DROP LIBRARY statement removes the library named MYUDFS from the default schema and removes the $TMUDFS library
file referenced by the library:
+
```
DROP LIBRARY myudfs RESTRICT;
```
RESTRICT prevents the DROP LIBRARY operation from dropping any user-defined functions (UDFs) that were created based on
this library. If any UDFs were created based on this library, the DROP LIBRARY operation fails.
<<<
[[drop_procedure_statement]]
== DROP PROCEDURE Statement
The DROP PROCEDURE statement removes a stored procedure in Java (SPJ) from the {project-name} database.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside
a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT
must be turned ON (the default) for the session.
```
DROP PROCEDURE [[catalog-name.]schema-name.]procedure-name
```
[[drop_procedure_syntax]]
=== Syntax Description of DROP PROCEDURE
* `\[[_catalog-name_.]_schema-name_.]_procedure-name_`
+
specifies the ANSI logical name of the stored procedure in java (SPJ), where each part of the name is a valid sql
identifier with a maximum of 128 characters. specify the name of a procedure that has already been registered in the
schema. if you do not fully qualify the procedure name, trafodion sql qualifies it according to the schema of the
current session.
+
for more information, see <<identifiers,identifiers>> and <<database_object_names,database object names>>.
[[drop_procedure_considerations]]
=== Considerations for DROP PROCEDURE
[[drop_procedure_required_privileges]]
==== Required Privileges
To issue a DROP PROCEDURE statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the procedure.
* You have the DROP or DROP_ROUTINE component privilege for SQL_OPERATIONS component.
<<<
[[drop_procedure_examples]]
=== Examples of DROP PROCEDURE
* This DROP PROCEDURE statement removes the procedure named LOWERPRICE from the SALES schema:
+
```
DROP PROCEDURE sales.lowerprice;
```
* This DROP PROCEDURE statement removes the procedure TOTALPRICE from the default schema for the session, which is the SALES schema:
+
```
SET SCHEMA sales;
DROP PROCEDURE totalprice;
```
<<<
[[drop_role_statement]]
== DROP ROLE Statement
The DROP ROLE statement deletes an SQL role. See <<roles,Roles>>.
```
DROP ROLE role-name
```
[[drop_role_syntax]]
=== Syntax Description of DROP ROLE
* `_role-name_`
+
is an existing role name. The role cannot be dropped if any of the following are true:
* Any privileges are granted to the role.
* The role is granted to any users.
* The role owns any schemas.
[[drop_role_considerations]]
=== Considerations for DROP ROLE
* To drop a role, you must own the role or have user administrative privileges for the role. You have user administrative
privileges for the role if you have been granted the MANAGE_ROLES component privilege. Initially, DB ROOT is the only
database user who has been granted the MANAGE_ROLES component privilege.
* Role names beginning with DB are reserved and can only be dropped by DB ROOT.
* You can determine all users to whom a role has been granted by using the SHOWDDL ROLE statement.
See the <<showddl_statement,SHOWDDL Statement>>.
<<<
==== Before You Drop a Role
Before dropping a role, follow these guidelines:
* You must revoke all privileges granted to the role.
* You must revoke the role from all users to whom it was granted.
* You must drop all schemas the role is a manager (or owner) of.
You can determine all users to whom a role has been granted with the SHOWDDL statement.
See the <<showddl_statement,SHOWDDL Statement>>.
==== Active Sessions for the User
When you revoke a role from a user, then the change in privileges is automatically propagated to and detected by
active sessions. There is no need for users to disconnect from and reconnect to a session to see the updated set of
privileges.
<<<
[[drop_role_examples]]
=== Examples of DROP ROLE
* To drop a role:
+
```
DROP ROLE clerks;
```
* To drop a role with dependent privileges:
+
```
-- User administrator creates a role:
CREATE ROLE clerks;
-- User administrator grants privileges on a table to the role:
GRANT ALL PRIVILEGES ON TABLE invent.partloc TO clerks;
-- User administrator grants the role to a user:
GRANT ROLE clerks TO JSmith;
-- JSmith creates a view based upon the granted privilege:
CREATE VIEW invent.partlocView (partnum, loc_code)
AS SELECT partnum, loc_code FROM invent.partloc;
-- If the user administrator attempts to drop the role, this
-- would fail because of the view created based on
-- the granted privilege.
-- To successfully drop the role, the dependent view
-- and grant must be removed first. For this example:
-- 1. JSmith drops the view:
DROP VIEW invent.partlocView;
-- 2. User administrator revokes the role from the user:
REVOKE ROLE clerks FROM JSmith;
-- 3. User administrator revokes all privileges the role has been granted
REVOKE ALL ON invent.partloc FROM clerks;
-- 4. User administrator drops the role:
DROP ROLE clerks;
-- The DROP ROLE operation succeeds.
```
<<<
[[drop_schema_statement]]
== DROP SCHEMA Statement
The DROP SCHEMA statement drops a schema from the database. See <<schemas,Schemas>>.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run DDL statements
inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run these statements,
AUTOCOMMIT must be turned ON (the default) for the session.
```
DROP SCHEMA [IF EXISTS] schema-name [RESTRICT|CASCADE]
```
[[drop_schema_syntax]]
=== Syntax Description of DROP SCHEMA
* `_schema-name_`
+
is the name of the schema to delete.
* `IF EXISTS`
+
drops the schema if it exists. If omitted, an error is raised if the schema doesn't exist.
* `RESTRICT`
+
If you specify RESTRICT, an error is reported if the specified schema is not empty. The default is RESTRICT.
* `CASCADE`
+
If you specify CASCADE, objects in the specified schema and the schema itself are dropped. Any objects in other schemas
that were dependent on objects in this schema are dropped as well.
<<<
[[drop_schema_considerations]]
=== Considerations for DROP SCHEMA
[[drop_schema_authorization_requirements]]
==== Authorization Requirements
To drop a schema, one of the following must be true:
* You are the owner of the schema.
* You have been granted the role that owns the schema.
* You have been granted the DROP_SCHEMA privilege.
[[drop_schema_examples]]
=== Example of DROP SCHEMA
* This example drops an empty schema:
+
```
DROP SCHEMA sales;
```
<<<
[[drop_sequence_statement]]
== DROP SEQUENCE Statement
The DROP SEQUENCE Statement removes a sequence from the Trafodion database.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run DDL statements inside a user-defined transaction (BEGIN WORK…COMMIT WORK) or when AUTOCOMMIT is OFF. To run these statements, AUTOCOMMIT must be turned ON (the default) for the session.
If you want to change the initial value of a sequence, you can drop and then recreate a sequence with the same name and different START WITH value.
DROP SEQUENCE is a Trafodion SQL extension.
For more inforamation, see <<create_sequence_statement,CREATE SEQUENCE Statement>> or <<alter_sequence_statement,ALTER SEQUENCE Statement>>.
```
DROP SEQUENCE [[catalog-name.]schema-name.]sequence
```
<<<
[[drop_sequence_syntax]]
=== Syntax Description of DROP SEQUENCE
* `[[catalog-name.]schema-name.]sequence`
+
Specifies the name of the sequence to be dropped, with optional catalog and schema name, where each part of the name is a valid sql identifier with a maximum of 128 characters.
<<<
[[drop_sequence_considerations]]
=== Considerations for DROP SEQUENCE
To issue a DROP SEQUENCE statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the sequence.
* You have the DROP or DROP_SEQUENCE component privilege for the SQL_OPERATIONS component.
<<<
[[drop_sequence_examples]]
=== Examples of DROP SEQUENCE
The following statement drops the sequence named `employee_seq`, which is created in <<create_sequence_examples,Examples of CRAETE SEQUENCE>>.
```
DROP SEQUENCE employee_seq
```
<<<
[[drop_table_statement]]
== DROP TABLE Statement
The DROP TABLE statement deletes a {project-name} SQL table and its dependent objects such as indexes and constraints.
See <<tables,Tables>>.
NOTE: DDL statements are not currently supported in transactions. That
means that you cannot run this statement inside a user-defined
transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run
this statement, AUTOCOMMIT must be turned ON (the default) for the
session.
```
DROP [VOLATILE] TABLE [IF EXISTS] table [RESTRICT|CASCADE]
```
[[drop_table_syntax]]
=== Syntax Description of DROP TABLE
* `VOLATILE`
+
specifies that the table to be dropped is a volatile table.
* `IF EXISTS`
+
drops the HBase table if it exists. This option does not apply to volatile tables.
* `_table_`
+
is the name of the table to delete.
* `RESTRICT`
+
If you specify RESTRICT and the table is referenced by another object, the specified table cannot be dropped.
The default is RESTRICT.
* `CASCADE`
+
If you specify CASCADE, the table and all objects referencing the table (such as a view) are dropped.
<<<
[[drop_table_considerations]]
=== Considerations for DROP TABLE
[[drop_table_authorization_requirements]]
==== Authorization Requirements
To issue a DROP TABLE statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the table.
* You have the DROP or DROP_TABLE component privilege for the SQL_OPERATIONS component.
[[drop_table_examples]]
=== Examples of DROP TABLE
* This example drops a table:
+
```
DROP TABLE mysch.mytable;
```
* This example drops a volatile table:
+
```
DROP VOLATILE TABLE vtable;
```
<<<
[[drop_view_statement]]
== DROP VIEW Statement
The DROP VIEW statement deletes a {project-name} SQL view. See <<views,Views>>.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside
a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT
must be turned ON (the default) for the session.
```
DROP VIEW view [RESTRICT|CASCADE]
```
[[drop_view_syntax]]
=== Syntax Description of DROP VIEW
* `_view_`
+
is the name of the view to delete.
* `RESTRICT`
+
If you specify RESTRICT, you cannot drop the specified view if it is referenced in the query expression of any other
view or in the search condition of another object's constraint. The default is RESTRICT.
* `CASCADE`
+
If you specify CASCADE, any dependent objects are dropped.
<<<
[[drop_view_considerations]]
=== Considerations for DROP VIEW
[[drop_view_authorization_requirements]]
==== Authorization Requirements
To issue a DROP VIEW statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the view.
* You have the DROP or DROP_VIEW component privilege for the SQL_OPERATIONS component.
[[drop_view_examples]]
=== Example of DROP VIEW
* This example drops a view:
+
```
DROP VIEW mysch.myview;
```
<<<
[[execute_statement]]
== Execute Statement
The EXECUTE statement executes an SQL statement previously compiled by a PREPARE statement in a {project-name} Command Interface
(TrafCI) session.
```
EXECUTE statement-name
[ USING param [,param]...] ]
param is:
?param-name | literal-value
```
[[execute_syntax]]
=== Syntax Description of EXECUTE
* `_statement-name_`
+
is the name of a prepared SQL statement—that is, the statement name used in the PREPARE statement. _statement-name_ is an
SQL identifier. See <<identifiers,Identifiers>>.
* `USING _param_ [,_param_]&#8230;`
+
`_param_ is: ?_param-name_ | _literal-value_`
+
specifies values for unnamed parameters (represented by ?) in the prepared statement in the form of either a parameter
name (?_param-name_) or a literal value (_literal-value_). The data type of a parameter value must be compatible with
the data type of the associated parameter in the prepared statement.
+
Parameter values (_param_) are substituted for unnamed parameters in the prepared statement by position—the i-th value
in the USING clause is the value for the i-th parameter in the statement. If fewer parameter values exist in the USING
clause than unnamed parameters in the PREPARE statement, {project-name} SQL returns an error. If more parameter values exist
in the USING clause than the unnamed parameters in the PREPARE statement, {project-name} SQL issues warning 15019.
+
<<<
+
The USING clause does not set parameter values for named parameters (represented by ?_param-name_) in a prepared statement.
To set parameter values for named parameters, use the SET PARAM command. For more information, see the
{docs-url}/command_interface/index.html[_{project-name} Command Interface Guide_].
** `?_param-name_`
+
The value for a ?_param-name_ must be previously specified with the SET PARAM command. The _param-name_ is case-sensitive.
For information about the SET PARAM command, see the
{docs-url}/command_interface/index.html[_{project-name} Command Interface Guide_].
** `_literal-value_`
+
is a numeric or character literal that specifies the value for the unnamed parameter.
+
If _literal-value_ is a character literal and the target column type is character, you do not have to enclose it in single
quotation marks. Its data type is determined from the data type of the column to which the literal is assigned. If the
_literal-value_ contains leading or trailing spaces, commas, or if it matches any parameter names that are already
set, enclose the _literal-value_ in single quotes.
See the <<prepare_statement,PREPARE Statement>>. For information about the SET PARAM command, see the
{docs-url}/command_interface/index.html[_{project-name} Command Interface Guide_].
[[execute_considerations]]
=== Considerations for EXECUTE
[[execute_scope_of_execute]]
==== Scope of EXECUTE
A statement must be compiled by PREPARE before you EXECUTE it, but after it is compiled, you can execute the statement
multiple times without recompiling it. The statement must have been compiled during the same TrafCI session as its
execution.
<<<
[[execute_examples]]
=== Examples of EXECUTE
* Use PREPARE to compile a statement once, and then execute the statement multiple times with different parameter values.
This example uses the SET PARAM command to set parameter values for named parameters (represented by ?_param-name_) in
the prepared statement.
+
```
SQL>prepare findemp from
+>select * from persnl.employee
+>where salary > ?sal and jobcode = ?job;
--- SQL command prepared.
SQL>set param ?sal 40000.00;
SQL>set param ?job 450;
SQL>execute findemp;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ --------------- -------------- ------- ------- --------
232 THOMAS SPINNER 4000 450 45000.00
--- 1 row(s) selected.
SQL>set param ?sal 20000.00;
SQL>set param ?job 300;
SQL>execute findemp;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ --------------- -------------- ------- ------- --------
75 TIM WALKER 3000 300 32000.00
89 PETER SMITH 3300 300 37000.40
...
--- 13 row(s) selected.
```
<<<
* Specify literal values in the USING clause of the EXECUTE statement for unnamed parameters in the prepared statement:
+
```
SQL>prepare findemp from
+>select * from persnl.employee
+>where salary > ? and jobcode = ?;
--- SQL command prepared.
SQL>execute findemp using 40000.00,450;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ --------------- -------------- ------- ------- --------
232 THOMAS SPINNER 4000 450 45000.00
--- 1 row(s) selected.
SQL>execute findemp using 20000.00, 300;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ --------------- -------------- ------- ------- --------
75 TIM WALKER 3000 300 32000.00
89 PETER SMITH 3300 300 37000.40
...
--- 13 row(s) selected.
```
* Use SET PARAM to assign a value to a parameter name and specify both the parameter name and a literal value in the
EXECUTE USING clause:
+
```
SQL>prepare findemp from
+>select * from persnl.employee
+>where salary > ? and jobcode = ?;
--- SQL command prepared.
SQL>set param ?Salary 40000.00;
SQL>execute findemp using ?Salary, 450;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ --------------- -------------- ------- ------- --------
232 THOMAS SPINNER 4000 450 45000.00
```
<<<
[[explain_statement]]
== EXPLAIN Statement
The EXPLAIN statement helps you to review query execution plans. You can use the EXPLAIN statement anywhere you can
execute other SQL statements (for example, SELECT). For more information on the EXPLAIN function, see
<<"explain_function","EXPLAIN Function">>.
EXPLAIN is a {project-name} SQL extension.
```
EXPLAIN [OPTIONS {'f'}] { FOR QID query-id | FOR QID CURRENT | prepared-stmt-name | query-text }
```
Plans displayed by the EXPLAIN statement are ordered from top (root operator) to bottom (leaf operators).
[[explain_syntax]]
=== Syntax Description of EXPLAIN
* `f`
+
Provides the simple, basic information contained in the query execution plan. This information is formatted for
readability and limited to 79 characters (one line) per operator.
See <<explain_formatted_considerations,Formatted [OPTIONS 'f'] Considerations>>.
* `_CURRENT_`
+
provide information for the latest compiled query.
* `_query-id_`
+
specifies the query ID of a prepared or executing query, which is a unique identifier generated by the SQL compiler.
* `_prepared-stmt-name_`
+
an SQL identifier containing the name of a statement already prepared in this session. An SQL identifier is
case-insensitive (will be in uppercase) unless it is double-quoted. It must be double-quoted if it contains blanks,
lowercase letters, or special characters. It must start with a letter. When you refer to the prepared query in a
SELECT statement, you must use uppercase.
Note: A few non-reserved words that can be used for prepared statement names cannot be used directly in EXPLAIN.
For example, 'EXPLAIN access;' results in a syntax error. You can still use the name, however, by using a
delimited identifier. That is, specify the name in upper case, surrounded by double-quotes. For example,
'EXPLAIN "ACCESS";' will work.
* `_query-text_`
+
specifies the text of a query.
<<<
[[explain_considerations]]
=== Considerations for EXPLAIN
[[explain_required_privileges]]
==== Required Privileges
To issue an EXPLAIN statement, one of the following must be true:
* You are DB ROOT.
* You own (that is, issued) the query specified in the EXPLAIN statement.
* You have the SHOW component privilege for the SQL_OPERATIONS component. The SHOW component privilege is granted
to PUBLIC by default.
[[explain_obtaining_explain_plans]]
==== Obtaining EXPLAIN Plans While Queries Are Running
{project-name} SQL provides the ability to capture an EXPLAIN plan for a query at any time while the query is running
with the FOR QID option. By default, this behavior is disabled for a {project-name} database session.
NOTE: Enable this feature before you start preparing and executing queries.
After the feature is enabled, use the FOR QID option in an EXPLAIN statement to get the query execution plan of
a running query.
The EXPLAIN function or statement returns the plan that was generated when the query was prepared. EXPLAIN with
the FOR QID option retrieves all the information from the original plan of the executing query. The plan is available
until the query finishes executing and is removed or deallocated.
<<<
[[explain_case_considerations]]
==== Case Considerations
In most cases, words in the commands can be in uppercase or lowercase. The options letter must be single quoted and in
lowercase.
[[explain_number_considerations]]
==== Number Considerations
Costs are given in a generic unit of effort. They show relative costs of an operation.
When trailing decimal digits are zero, they are dropped. For example, 6.4200 would display as
6.42 and 5.0 would display as 5, without a decimal point.
[[explain_formatted_considerations]]
==== Formatted [OPTIONS 'f'] Considerations
The formatted option is the simplest option. It provides essential, brief information about the plan and shows the
operators and their order within the query execution plan.
OPTIONS 'f' formats the EXPLAIN output into these fields:
[cols="25%,75%"]
|===
| LC | Left child sequence number
| RC | Right child sequence number
| OP | The sequence number of the operator in the query plan
| OPERATOR | The operator type
| OPT | Query optimizations that were applied
| DESCRIPTION | Additional information about the operator
| CARD | Estimated number of rows returned by the plan. CARDINALITY and ROWS_OUT are the same.
|===
This example uses OPTIONS 'f ':
```
>>explain options 'f' for qid MXID11002015016212343685134956677000000000206U3333300_652_XX;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
1 . 2 root 1.00E+000
. . 1 hbase_aggr 1.00E+000
--- SQL operation complete.
```
<<<
To use the EXPLAIN statement with a prepared statement, first prepare the query. Then use the EXPLAIN statement:
```
PREPARE q FROM SELECT * FROM REGION;
EXPLAIN options 'f' q;
```
EXPLAIN can also be used with the query text directly:
```
EXPLAIN options 'f' SELECT * FROM REGION;
```
<<<
[[get_statement]]
== GET Statement
The GET statement displays the names of database objects, components, component privileges, roles, or users that exist
in the {project-name} instance.
GET is a {project-name} SQL extension.
```
GET option
option is:
COMPONENT PRIVILEGES ON component-name [FOR auth-name]
| COMPONENTS
| FUNCTIONS FOR LIBRARY [[catalog-name.]schema-name.]library-name
| FUNCTIONS [IN SCHEMA [catalog-name.]schema-name]
| LIBRARIES [IN SCHEMA [catalog-name.]schema-name]
| PROCEDURES FOR LIBRARY [[catalog-name.]schema-name.]library-name
| PROCEDURES [IN SCHEMA [catalog-name.]schema-name]
| ROLES [FOR USER database-username]
| SCHEMAS [IN CATALOG catalog-name]
| SCHEMAS FOR [USER | ROLE] authorization-id
| TABLES [IN SCHEMA [catalog-name.]schema-name]
| USERS [FOR ROLE role-name]
| VIEWS [IN SCHEMA [catalog-name.]schema-name]
| VIEWS ON TABLE [[catalog-name.]schema-name.]table-name
| PRIVILEGES FOR {USER database-username | ROLE role-name}
```
[[get_syntax]]
=== Syntax Description of GET
* `COMPONENT PRIVILEGES ON _component-name_`
+
displays the names of the component privileges available for the specified component.
* `COMPONENT PRIVILEGES ON _component-name_ FOR _auth-name_`
+
displays the component privileges that have been granted to the specified authorization name for the specified component.
The _auth-name_ is either a registered database username or an existing role name and can be a regular or delimited
case-insensitive identifier. See <<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
* `COMPONENTS`
+
displays a list of all the existing components.
<<<
* `FUNCTIONS`
+
displays the names of all the user-defined functions (UDFs) in the catalog and schema of the current session. By default,
the catalog is TRAFODION, and the schema is SEABASE.
* `FUNCTIONS FOR LIBRARY \[[_catalog-name_.]_schema-name_.]_library-name_`
+
displays the UDFs that reference the specified library.
* `functions in schema [_catalog-name_.]_schema-name_`
+
displays the names of all the UDFs in the specified schema.
* `libraries`
+
displays the names of all the libraries in the catalog and schema of the current session. By default, the catalog is trafodion,
and the schema is seabase.
* `libraries in schema [_catalog-name_.]_schema-name_`
+
displays the libraries in the specified schema.
* `procedures`
+
displays the names of all the procedures in the catalog and schema of the current session. By default, the catalog is trafodion,
and the schema is seabase.
* `procedures for library \[[_catalog-name_.]_schema-name_.]_library-name_`
+
displays the procedures that reference the specified library.
* `procedures in schema [_catalog-name_.]_schema-name_`
+
displays the names of all the procedures in the specified schema.
* `roles`
+
displays a list of all the existing roles.
* `roles for user _database-username_`
+
displays all the roles that have been granted to the specified database user. The _database-username_ can be a regular or delimited
case-insensitive identifier. see <<case_insensitive_delimited_identifiers,case-insensitive delimited identifiers>>.
* `schemas`
+
displays the names of all the schemas in the catalog of the current session. By default, the catalog is trafodion.
* `schemas in catalog _catalog-name_`
+
displays the names of all the schemas in the specified catalog. For the _catalog-name_, you can specify only trafodion.
* `schemas for [user | role] _authorization-id_`
+
displays all the schemas managed (or owned) by a specified user or role.
* `_authorization-id_`
+
is the name of a user or role. you may specify either user or role for users or roles.
* `tables`
+
displays the names of all the tables in the catalog and schema of the current session. By default, the catalog is trafodion,
and the schema is seabase.
* `tables in schema [_catalog-name_.]_schema-name_`
+
displays the names of all the tables in the specified schema.
* `users`
+
displays a list of all the registered database users.
* `users for role _role-name_`
+
displays all the database users who have been granted the specified role. The _role-name_ can be a regular or delimited
case-insensitive identifier. For more information, see <<case_insensitive_delimited_identifiers,case-insensitive delimited identifiers>>.
<<<
* `views`
+
displays the names of all the views in the catalog and schema of the current session. By default, the catalog is trafodion,
and the schema is seabase.
* `views in schema [_catalog-name_.]_schema-name_`
+
displays the names of all the views in the specified schema. For the _catalog-name_, you can specify only trafodion.
* `views on table \[[_catalog-name_.]_schema-name_.]_table-name_`
+
displays the names of all the views that were created for the specified table. If you do not qualify the table name with
catalog and schema names, get uses the catalog and schema of the current session. For the _catalog-name_, you can specify
only trafodion.
[[get_considerations]]
=== Considerations for GET
IMPORTANT: The GET COMPONENT PRIVILEGES, GET COMPONENTS, GET ROLES FOR USER, GET USERS FOR ROLE statements and GET PRIVILEGES FOR {USER database-username | ROLE role-name} statements work only when
authentication and authorization are enabled in {project-name}. For more information, see
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
The GET statement displays delimited object names in their internal format. For example, the GET statement returns the delimited
name "my ""table""" as my "table".
[[get_required_privileges]]
==== Required Privileges
To issue a GET statement, one of the following must be true:
* You are DB ROOT.
* You have the SHOW component privilege for the SQL_OPERATIONS component. The SHOW component privilege is granted to PUBLIC by default.
<<<
[[get_examples]]
=== Examples of GET
* This GET statement displays the names of all the schemas in the catalog of the current session, which happens to be the TRAFODION
catalog:
+
```
GET SCHEMAS;
```
* This GET statement displays the names of all the schemas in the specified catalog, TRAFODION:
+
```
GET SCHEMAS IN CATALOG TRAFODION;
```
* This GET statement displays the names of schemas owned by DB ROOT:
+
```
GET SCHEMAS FOR USER DB ROOT;
```
* This GET statement displays the names of all the tables in the catalog and schema of the current session, which happens to be TRAFODION.SEABASE:
+
```
GET TABLES;
```
* This GET statement displays the names of all the tables in the specified schema, SEABASE2, in the TRAFODION catalog:
+
```
GET TABLES IN SCHEMA SEABASE2;
```
* This GET statement displays the names of all the views in the catalog and schema of the current session, which happens to be
TRAFODION.SEABASE:
+
```
GET VIEWS;
```
* This GET statement displays the names of all the views in the specified schema, SEABASE2, the TRAFODION catalog:
+
```
GET VIEWS IN SCHEMA SEABASE2;
```
<<<
* This GET statement displays the names of all the views that were created for the specified table, T, in the TRAFODION.SEABASE schema:
+
```
GET VIEWS ON TABLE T;
```
* This GET statement displays the names of the libraries in the catalog and schema of the current session, which happens to be
TRAFODION.SEABASE:
+
```
GET LIBRARIES;
```
* This GET statement displays the names of the libraries in the TRAFODION._MD_ schema:
+
```
GET LIBRARIES IN SCHEMA "_MD_";
```
* This GET statement displays the names of procedures registered in the library, TRAFODION._MD_.UDR_LIBRARY:
+
```
GET PROCEDURES FOR LIBRARY "_MD_".UDR_LIBRARY;
```
* This GET statement displays the names of procedures in the TRAFODION._MD_ schema:
+
```
GET PROCEDURES IN SCHEMA "_MD_";
```
* This GET statement displays the names of procedures in the catalog and schema of the current session, which happens to be TRAFODION.SEABASE:
+
```
GET PROCEDURES;
```
* This GET statement displays the names of user-defined functions (UDFs) in the catalog and schema of the current session, which happens to be
TRAFODION.SEABASE:
+
```
GET FUNCTIONS;
```
* This GET statement displays the names of UDFs in MYSCHEMA:
+
```
GET FUNCTIONS IN SCHEMA MYSCHEMA;
```
<<<
* This GET statement displays the names of UDFs created in the library, TRAFODION.MYSCHEMA.MYUDFS:
+
```
GET FUNCTIONS FOR LIBRARY MYSCHEMA.MYUDFS;
```
* This GET statement displays a list of all the existing components:
+
```
GET COMPONENTS;
```
* This GET statement displays the names of the component privileges available for the SQL_OPERATIONS component:
+
```
GET COMPONENT PRIVILEGES ON sql_operations;
```
* This GET statement displays the component privileges that have been granted to the DB ROOT user for the SQL_OPERATIONS component:
+
```
GET COMPONENT PRIVILEGES ON sql_operations FOR db root;
```
* This GET statement displays a list of all the existing roles:
+
```
GET ROLES;
```
* This GET statement displays all the roles that have been granted to the DB ROOT user:
+
```
GET ROLES FOR USER db root;
```
* This GET statement displays a list of all the registered database users:
+
```
GET USERS;
```
* This GET statement displays all the database users who have been granted the DB ROOTROLE role:
+
```
GET USERS FOR ROLE db rootrole;
```
* This example demonstrates how to grant privileges to the specified database user _testuser1_ and display privileges.
+
The _testuser1_ grants some privileges to the _testuser2_ on the table _project_.
+
```
SQL>GRANT DELETE, INSERT, REFERENCES, SELECT (PROJCODE,PROJDESC), UPDATE ON TABLE project TO testuser2;
--- SQL operation complete.
```
+
This GET statement displays all the privileges that have been granted to the _testuser2_.
+
```
SQL>GET PRIVILEGES FOR USER testuser2;
-IDU-R- TRAFODION.SEABASE.PROJECT
S------ TRAFODION.SEABASE.PROJECT <Column> PROJCODE
S------ TRAFODION.SEABASE.PROJECT <Column> PROJDESC
--- SQL operation complete.
```
* This example shows how to grant privileges to the specified role _"TECHNICAL_WRITER"_ and display privileges.
+
The _testuser1_ grants some privileges to the _"TECHNICAL_WRITER"_ on the table _project_.
+
```
SQL>GRANT DELETE, INSERT, REFERENCES, SELECT (start_date,ship_timestamp,est_complete), UPDATE ON TABLE project TO "TECHNICAL_WRITER";
--- SQL operation complete.
```
+
This GET statement displays all the privileges that have been granted to the _"TECHNICAL_WRITER"_.
+
```
SQL>GET PRIVILEGES FOR ROLE "TECHNICAL_WRITER";
-IDU-R- TRAFODION.SEABASE.PROJECT
S------ TRAFODION.SEABASE.PROJECT <Column> EST_COMPLETE
S------ TRAFODION.SEABASE.PROJECT <Column> SHIP_TIMESTAMP
S------ TRAFODION.SEABASE.PROJECT <Column> START_DATE
--- SQL operation complete.
```
<<<
[[get_hbase_objects_statement]]
== GET HBASE OBJECTS Statement
The GET HBASE OBJECTS statement displays a list of HBase objects directly from HBase, not from the {project-name} metadata,
and it can be run in any SQL interface, such as the {project-name} Command Interface (TrafCI). This command is equivalent
to running a list command from an HBase shell, but without having to start and connect to an HBase shell.
GET HBASE OBJECTS is a {project-name} SQL extension.
```
GET { USER | SYSTEM | EXTERNAL | ALL } HBASE OBJECTS
```
[[get_hbase_objects_syntax]]
=== Syntax Description of GET HBASE OBJECTS
* `USER`
+
displays a list of the {project-name} user objects.
* `SYSTEM`
+
displays a list of the {project-name} system objects, such as metadata, repository, privileges, and Distributed Transaction
Manager (DTM) tables.
* `EXTERNAL`
+
displays a list of non-{project-name} objects.
* `ALL`
+
displays a list of all objects, including user, system, and external objects.
<<<
[[get_hbase_objects_examples]]
=== Examples of GET HBASE OBJECTS
* This GET HBASE OBJECTS statement displays the {project-name} user objects in HBase:
+
```
Trafodion Conversational Interface 1.1.0
(c) Copyright 2015 Apache Software Foundation
>>get user hbase objects;
TRAFODION.SCH.SB_HISTOGRAMS
TRAFODION.SCH.SB_HISTOGRAM_INTERVALS
TRAFODION.SCH.T006T1
TRAFODION.SCH.T006T2
TRAFODION.SCH.T006T3
TRAFODION.SCH.T006T4
TRAFODION.SCH.T006T5
TRAFODION.SCH.T006T6
TRAFODION.SCH.T006T7
TRAFODION.SCH.T006T8
TRAFODION.SCH.X1
TRAFODION.SCH.X2
TRAFODION.SCH.X3
--- SQL operation complete.
```
<<<
* This GET HBASE OBJECTS statement displays the {project-name} system objects in HBase:
+
```
>>get system hbase objects;
TRAFODION._DTM_.TLOG0_CONTROL_POINT
...
TRAFODION._DTM_.TLOG1_LOG_f
TRAFODION._MD_.AUTHS
TRAFODION._MD_.COLUMNS
TRAFODION._MD_.DEFAULTS
TRAFODION._MD_.INDEXES
TRAFODION._MD_.KEYS
TRAFODION._MD_.LIBRARIES
TRAFODION._MD_.LIBRARIES_USAGE
TRAFODION._MD_.OBJECTS
TRAFODION._MD_.OBJECTS_UNIQ_IDX
TRAFODION._MD_.REF_CONSTRAINTS
TRAFODION._MD_.ROUTINES
TRAFODION._MD_.SEQ_GEN TRAFODION._MD_.TABLES
TRAFODION._MD_.TABLE_CONSTRAINTS
TRAFODION._MD_.TEXT
TRAFODION._MD_.UNIQUE_REF_CONSTR_USAGE
TRAFODION._MD_.VERSIONS
TRAFODION._MD_.VIEWS
TRAFODION._MD_.VIEWS_USAGE
TRAFODION._REPOS_.METRIC_QUERY_AGGR_TABLE
TRAFODION._REPOS_.METRIC_QUERY_TABLE
TRAFODION._REPOS_.METRIC_SESSION_TABLE
TRAFODION._REPOS_.METRIC_TEXT_TABLE
--- SQL operation complete.
```
* This GET HBASE OBJECTS statement displays the external, non-{project-name} objects in HBase:
+
```
>>get external hbase objects;
obj1
obj2
--- SQL operation complete.
```
<<<
[[get_version_of_metadata_statement]]
== GET VERSION OF METADATA Statement
The GET VERSION OF METADATA statement displays the version of the metadata in the {project-name} instance and indicates if
the metadata is current.
GET VERSION OF METADATA is a {project-name} SQL extension.
```
GET VERSION OF METADATA
```
[[get_version_of_metadata_considerations]]
=== Considerations for GET VERSION OF METADATA
* If the metadata is compatible with the installed {project-name} software version, the GET VERSION OF METADATA statement
indicates that the metadata is current:
+
```
Current Version 3.0. Expected Version 3.0.
Metadata is current.
```
* If the metadata is incompatible with the installed {project-name} software version, the GET VERSION OF METADATA statement
indicates that you need to upgrade or reinitialize the metadata:
+
```
Current Version 2.3. Expected Version 3.0.
Metadata need to be upgraded or reinitialized.
```
[[get_version_of_metadata_examples]]
=== Examples of GET VERSION OF METADATA
* This GET VERSION OF METADATA statement displays the metadata version in a {project-name} Release 1.0.0 instance:
+
```
>> get version of metadata;
Current Version 3.0. Expected Version 3.0.
Metadata is current.
--- SQL operation complete.
```
<<<
* This GET VERSION OF METADATA statement displays the metadata version in a {project-name} Release 0.9.0 instance:
+
```
>> get version of metadata;
Current Version 2.3. Expected Version 2.3.
Metadata is current.
--- SQL operation complete.
```
* If the metadata is incompatible with the installed {project-name} software version, you will see this output indicating
that you need to upgrade or reinitialize the metadata:
+
```
get version of metadata;
Current Version 2.3. Expected Version 3.0.
Metadata need to be upgraded or reinitialized.
--- SQL operation complete.
```
<<<
[[get_version_of_software_statement]]
== GET VERSION OF SOFTWARE Statement
The GET VERSION OF SOFTWARE statement displays the version of the {project-name} software that is installed on the system and
indicates if it is current.
GET VERSION OF SOFTWARE is a {project-name} SQL extension.
```
GET VERSION OF SOFTWARE
```
[[get_version_of_software_considerations]]
=== Considerations for GET VERSION OF SOFTWARE
* If the software on the system is current, the GET VERSION OF SOFTWARE statement displays this output:
+
```
System Version 1.0.0. Expected Version 1.0.0.
Software is current.
```
* In rare circumstances where something went wrong with the {project-name} software installation and mismatched objects were
installed, the GET VERSION OF SOFTWARE statement displays this output:
+
```
System Version 0.9.1. Expected Version 1.0.0.
Version of software being used is not compatible with version of software on the system.
```
<<<
[[get_version_of_software_examples]]
=== Examples of GET VERSION OF SOFTWARE
* This GET VERSION OF SOFTWARE statement displays the software version for {project-name} Release 1.0.0:
+
```
>> get version of software;
System Version 1.0.0. Expected Version 1.0.0.
Software is current.
--- SQL operation complete.
```
* This GET VERSION OF SOFTWARE statement displays the software version for {project-name} Release 0.9.0:
+
```
get version of software;
System Version 0.9.0. Expected Version 0.9.0.
Software is current.
--- SQL operation complete.
```
* If something went wrong with the {project-name} software installation and if mismatched objects were installed, you will
see this output indicating that the software being used is incompatible with the software on the system:
+
```
get version of software;
System Version 0.9.1. Expected Version 1.0.0.
Version of software being used is not compatible with version of software on the system.
--- SQL operation complete.
```
<<<
[[grant_statement]]
== GRANT Statement
The GRANT statement grants access privileges on an SQL object and its columns to specified users or roles.
Privileges can be granted on the object, on one or more columns, or both.
IMPORTANT: This statement works only when authentication and
authorization are enabled in {project-name}. For more information, see
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
```
GRANT {privilege [,privilege]... |ALL [PRIVILEGES]}
ON [object-type] [schema.]object
TO grantee
[WITH GRANT OPTION]
[[GRANTED] BY grantor]
privilege is:
DELETE
| EXECUTE
| INSERT [column-list]
| REFERENCES [column-list]
| SELECT [column-list]
| UPDATE [column-list]
| USAGE
object-type is:
FUNCTION
| PROCEDURE
| LIBRARY
| SEQUENCE
| TABLE
grantee is:
auth-name
grantor is:
role-name
column-list is:
(column [,colummn] ...)
```
<<<
=== syntax description of grant
* `_privilege_ [,_privilege_ ] &#8230; | all [privileges]`
+
Specifies the privileges to grant. You can specify these privileges for an object.
+
[cols="25%,75%"]
|===
| DELETE | Can use the delete statement.
| EXECUTE | Can execute a stored procedure using a call statement or can execute a user-defined function (UDF).
| INSERT [column-list] | Can use the insert statement.
| REFERENCES [column-list] | Can create constraints that reference the object.
| SELECT [column-list] | Can use the select statement.
| UPDATE [column-list] | Can use the update statement on table objects.
| USAGE | For libraries, can create procedures and functions on library objects.
For sequence generators, can use the sequence in a SQL statement.
| ALL | All the applicable privileges. When you specify all for a table or view,
this includes the select, delete, insert, references, and update privileges. When the object is a
stored procedure or user-defined function (UDF), only the execute privilege is applied. When the
object is a library, only the update and usage privileges are applied. When the object is a sequence
generator, only the usage privilege is applied.
|===
* `ON [_object-type_] [_schema_.]_object_`
+
Specifies an object on which to grant privileges. If none is specified, it defaults to TABLE. See <<database_object_names,"Database Object Names>> for more details. _object-type_ can be:
** `FUNCTION [_schema_.]_function-name_`, where _function-name_ is the name of a user-defined function (UDF) in the database.
** `LIBRARY [_schema_.]_library-name_`, where _library-name_ is the name of a library object in the database.
** `PROCEDURE [_schema_.]_procedure-name_`, where _procedure-name_ is the name of a stored procedure in java (SPJ)
registered in the database.
** `SEQUENCE [_schema_.]_sequence-name_`, where _sequence-name_ is the name of a sequence object in the database.
** `[TABLE] [_schema_.]_object_`, where _object_ is a table or view.
* `TO {_grantee_ &#8230; }`
+
Specifies the _auth-name_ to which you grant privileges.
* `_auth-name_`
+
Specifies the name of an authorization id to which you grant privileges. See <<authorization_ids,authorization ids>>.
The authorization id must be a registered database username, an existing role name, or public. the name is a regular
or delimited case-insensitive identifier. See <<case_insensitive_delimited_identifiers,case-insensitive delimited identifiers>>.
If you grant a privilege to public, the privilege remains available to all users, unless it is later revoked from public.
* `WITH GRANT OPTION`
+
Specifies that the _auth-name_ to which a privilege is granted may in turn grant the same privilege to other users or roles.
* `[GRANTED] BY _grantor_`
+
Allows you to grant privileges on behalf of a role. If not specified, the privileges will be granted on your behalf as
the current user/grantor.
* `_role-name_`
+
Specifies a role on whose behalf the grant operation is performed. To grant the privileges on behalf of a role, you must
be a member of the role, and the role must have the authority to grant the privileges; that is, the role must have been
granted the privileges with grant option.
* `_column-list_`
+
Specifies the list of columns to grant the requested privilege to.
[[grant_considerations]]
=== Considerations for GRANT
[[grant_authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
To grant a privilege on an object, you must have both that privilege and the right to grant that privilege. Privileges can
be granted directly to you or to one of the roles you have been granted. You can grant a privilege on an object if you are
the owner of the object (by which you are implicitly granted all privileges on the object) or the owner of the schema containing
the object, or if you have been granted both the privilege and the WITH GRANT OPTION for the privilege.
If granting privileges on behalf of a role, you must specify the role in the [GRANTED] BY clause. To grant the privileges on
behalf of a role, you must be a member of the role, and the role must have the authority to grant the privileges; that is, the
role must have been granted the privileges WITH GRANT OPTION.
If you lack authority to grant one or more of the specified privileges, SQL returns a warning (yet does grant the specified
privileges for which you do have authority to grant). If you lack authority to grant any of the specified privileges, SQL returns
an error.
<<<
[[grant_examples]]
=== Examples of GRANT
* To grant column level SELECT and object level DELETE privileges on a table:
+
```
GRANT SELECT (part_no, part_name), DELETE ON TABLE invent.partloc
TO "MO.Neill@company.com";]
```
* To grant SELECT privileges on a table to a user:
+
```
GRANT SELECT ON TABLE invent.partloc TO ajones;
```
<<<
[[grant_component_privilege_statement]]
== GRANT COMPONENT PRIVILEGE Statement
The GRANT COMPONENT PRIVILEGE statement grants one or more component privileges to a user or role. See <<"Privileges","Privileges">> and <<"Roles","Roles">>.
GRANT COMPONENT PRIVILEGE is a {project-name} SQL extension.
IMPORTANT: This statement works only when authentication and authorization are enabled in {project-name}. For more information, see
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
```
GRANT COMPONENT PRIVILEGE {privilege-name [, privilege-name]...}
ON component-name
TO grantee
[WITH GRANT OPTION] [[GRANTED] BY grantor]
grantee is:
auth-name
grantor is:
role-name
```
[[grant_component_privilege_syntax]]
=== Syntax Description of GRANT COMPONENT PRIVILEGE
* `_privilege-name_`
+
specifies one or more component privileges to grant. The comma-separated list can include only privileges within the same component.
+
[cols="20%,20%,60%",options="header"]
|===
| Component | Component Privilege | Description
| SQL_OPERATIONS | ALTER | Privilege to alter database objects
// | | ALTER_LIBRARY | Privilege to alter libraries
| | ALTER_SCHEMA | Privilege to alter schemas
| | ALTER_SEQUENCE | Privilege to alter sequence generators
| | ALTER_TABLE | Privilege to alter tables
| | ALTER_VIEW | Privilege to alter views
| | CREATE | Privilege to create database objects
| | CREATE_INDEX | Privilege to create indexes
| | CREATE_LIBRARY | Privilege to create libraries in the database
| | CREATE_PROCEDURE | Privilege to create stored procedures in Java (SPJs)
| | CREATE_ROUTINE | Privilege to create user-defined functions (UDFs),
table-mapping functions, and other routines in the database
| | CREATE_SCHEMA | Privilege to create schemas in the database
| | CREATE_SEQUENCE | Privilege to create sequence generators in the database
| | CREATE_TABLE | Privilege to create tables in the database
| | CREATE_VIEW | Privilege to create views in the database
| | DROP | Privilege to drop database objects
| | DROP_INDEX | Privilege to drop indexes
| | DROP_LIBRARY | Privilege to drop libraries
| | DROP_PROCEDURE | Privilege to drop stored procedures in Java (SPJs)
| | DROP_ROUTINE | Privilege to drop user-defined functions (UDFs),
table-mapping functions, and other routines from the database
| | DROP_SCHEMA | Privilege to drop schemas
| | DROP_SEQUENCE | Privilege to drop sequence generators
| | DROP_TABLE | Privilege to drop tables
| | DROP_VIEW | Privilege to drop views
| | MANAGE_COMPONENT | Privilege to perform component commands, such as register, create, and grant privileges
| | MANAGE_LIBRARY | Privilege to perform library-related commands, such as creating and dropping libraries
| | MANAGE_LOAD | Privilege to perform LOAD and UNLOAD commands
| | MANAGE_ROLES | Privilege to create, alter, drop, grant, and revoke roles
| | MANAGE_STATISTICS | Privilege to update and display statistics
| | MANAGE_USERS | Privilege to register or unregister users, alter users, and grant or revoke
component privileges.
| | QUERY_ACTIVATE | Privilege to activate and executing query after it has been suspended
| | QUERY_CANCEL | Privilege to cancel an executing query
| | QUERY_SUSPEND | Privilege to suspend an executing query
| | SHOW | Privilege to run EXPLAIN, GET, INVOKE, and SHOW commands. The SHOW privilege
has been granted to PUBLIC by default.
|===
* `ON _component-name_`
+
specifies a component name on which to grant component privileges. Currently, the only valid component name is SQL_OPERATIONS.
* `TO _grantee_`
specifies an _auth-name_ to which you grant component privileges.
* `_auth-name_`
+
specifies the name of an authorization ID to which you grant privileges. See <<authorization_ids,Authorization IDs>>.
The authorization ID must be a registered database username, existing role name, or PUBLIC. The name is a regular or
delimited case-insensitive identifier. See <<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
If you grant a privilege to PUBLIC, the privilege remains available to all users, unless it is later revoked from PUBLIC.
* `WITH GRANT OPTION`
+
specifies that the _auth-name_ to which a component privilege is granted may in turn grant the same component privilege
to other users or roles.
* `[GRANTED] BY _grantor_`
+
allows you to grant component privileges on behalf of a role. If not specified, the privileges will be granted on your
behalf as the current user/grantor.
* `_role-name_`
+
specifies a role on whose behalf the GRANT COMPONENT PRIVILEGE operation is performed. To grant the privileges on behalf
of a role, you must be a member of the role, and the role must have the authority to grant the privileges; that is, the
role must have been granted the privileges WITH GRANT OPTION.
[[grant_component_privilege_considerations]]
=== Considerations for GRANT COMPONENT PRIVILEGE
* A user or role granted a component privilege WITH GRANT OPTION can grant the same component privilege to other users or roles.
* If all of the component privileges have already been granted, SQL returns an error.
* If one or more component privileges has already been granted, SQL silently ignores the granted privileges and proceeds
with the grant operation.
[[grant_component_privilege_syntaxauthorization_and_availability_requirements]]
==== Authorization and Availability Requirements
To grant a component privilege, you must have one of these privileges:
* User administrative privileges (that is, a user who has been granted the MANAGE_USERS component privilege). Initially,
DB_ROOT is the only database user who has been granted the MANAGE_USERS component privilege.
* A user other than a user administrator who has the WITH GRANT OPTION for the component privilege.
* A user who was granted a role that has the WITH GRANT OPTION privilege for the component privilege.
[[grant_component_privilege_considerations]]
=== Examples of GRANT COMPONENT PRIVILEGE
* Grant a component privilege, CREATE_TABLE, on a component, SQL_OPERATIONS, to SQLUSER1:
+
```
GRANT COMPONENT PRIVILEGE CREATE_TABLE ON SQL_OPERATIONS TO sqluser1;
```
<<<
[[grant_role_statement]]
== GRANT ROLE Statement
The GRANT ROLE statement grants one or more roles to a user. See <<roles,Roles>>.
IMPORTANT: This statement works only when authentication and authorization are enabled in {project-name}. For more information,
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
```
GRANT ROLE {role-name [,role-name ]...}
TO grantee
grantee is:
database-username
```
[[grant_role_syntax]]
=== Syntax Description of GRANT ROLE
* `_role-name_ [,_role-name_]_`
+
specifies the existing roles to grant.
* `TO _grantee_`
+
specifies the registered database username to whom to grant the roles.
[[grant_role_considerations]]
=== Considerations for GRANT ROLE
* To grant roles to other grantees, you must own the roles or have user administrative privileges for the roles. You
have user administrative privileges for roles if you have been granted the MANAGE_ROLES component privilege. Initially,
DB ROOT is the only database user who has been granted the MANAGE_ROLES component privilege.
* When you grant a role to a user, the additional privileges are automatically propagated to and detected by active
sessions. There is no need for users to disconnect from and reconnect to a session to see the updated set of privileges.
* If any errors occur in processing a GRANT ROLE statement that names multiple roles, then no grants are done.
* If you attempt to grant a role but a grant with the same role and grantee already exists, SQL ignores the request and
returns a successful operation.
[[grant_role_examples]]
=== Examples of GRANT ROLE
* To grant multiple roles to a grantee:
+
```
GRANT ROLE clerks, sales TO jsmith;
```
<<<
[[insert_statement]]
== INSERT Statement
The INSERT statement is a DML statement that inserts rows in a table or
view.
```
INSERT INTO table [(target-col-list)] insert-source
target-col-list is:
colname [,colname]...
insert-source is:
query-expr [order-by-clause] [access-clause] | DEFAULT VALUES
```
[[insert_syntax]]
=== Syntax Description of INSERT
* `_table_`
+
names the user table or view in which to insert rows. _table_ must be a base table or an updatable view.
* `_(target-col-list)_`
+
names the columns in the table or view in which to insert values. The data type of each target column must be
compatible with the data type of its corresponding source value. Within the list, each target column must
have the same position as its associated source value, whose position is determined by the columns in the table
derived from the evaluation of the query expression (_query-expr_).
+
If you do not specify all of the columns in _table_ in the _target-col-list_, column default values are inserted
into the columns that do not appear in the list. See <<column_default_settings,Column Default Settings>>.
+
If you do not specify _target-col-list_, row values from the source table are inserted into all columns in _table_.
The order of the column values in the source table must be the same order as that of the columns specified in the
CREATE TABLE for _table_. (This order is the same as that of the columns listed in the result table of SHOWDDL
_table_.)
* `_insert-source_`
+
specifies the rows of values to be inserted into all columns of _table_ or, optionally, into specified columns of _table_.
* `_query-expr_`
+
For the description of _query-expr_, _order-by-clause_, and _access-clause_, see the <<select_statement,SELECT Statement>>.
** `DEFAULT VALUES`
+
is equivalent to a _query-expr_ of the form VALUES (DEFAULT, &#8230;). The value of each DEFAULT is the default value defined in
the column descriptor of _colname_, which is contained in the table descriptor of _table_. Each default value is inserted into
its column to form a new row. If you specify DEFAULT VALUES, you cannot specify a column list. You can use DEFAULT VALUES only
when all columns in _table_ have default values.
[[insert_considerations]]
=== Considerations for INSERT
[[insert_authorization_requirements]]
==== Authorization Requirements
INSERT requires authority to read and write to the table or view receiving the data and authority to read tables or views
specified in the query expression (or any of its subqueries) in the INSERT statement.
[[insert_transaction_initiation_and_termination]]
==== Transaction Initiation and Termination
The INSERT statement automatically initiates a transaction if no transaction is active. Alternatively, you can explicitly initiate
a transaction with the BEGIN WORK statement. After a transaction is started, the SQL statements execute within that transaction
until a COMMIT or ROLLBACK is encountered or an error occurs. If AUTOCOMMIT is ON, the transaction terminates at the end of the
INSERT statement.
[[insert_self_referencing_insert_and_begin_work_or_autocommit_off]]
==== Self-Referencing INSERT and BEGIN WORK or AUTOCOMMIT OFF
A self-referencing INSERT statement is one that references, in the statement's _insert-source_, the same table or view into which
rows will be inserted (see <<examples_of_self_referencing_inserts,Examples of Self-Referencing Inserts>>). A self-referencing INSERT
statement will not execute correctly and an error is raised if either BEGIN WORK or AUTOCOMMIT OFF is used unless the compiler's
plan sorts the rows before they are inserted. If you want to use a self-referencing INSERT statement, you should avoid the use of
BEGIN WORK or AUTOCOMMIT OFF. For information about AUTOCOMMIT, see the <<set_transaction_statement,SET TRANSACTION Statement>>.
[[insert_isolation-levels-of-transactions-and-access-options-of-statements]]
==== Isolation Levels of Transactions and Access Options of Statements
The isolation level of an SQL transaction defines the degree to which the operations on data within that transaction are affected by
operations of concurrent transactions. When you specify access options for the DML statements within a transaction, you override the
isolation level of the containing transaction. Each statement then executes with its individual access option.
[[insert_use_of_a_values_clause_for_the_source_query_expression]]
==== Use of a VALUES Clause for the Source Query Expression
If the query expression consists of the VALUES keyword followed by rows of values, each row consists of a list of value expressions
or a row subquery (a subquery that returns a single row of column values). A value in a row can also be a scalar subquery (a subquery
that returns a single row consisting of a single column value).
Within a VALUES clause, the operands of a value expression can be numeric, string, datetime, or interval values; however, an operand
cannot reference a column (except in the case of a scalar or row subquery returning a value or values in its result table).
[[insert_requirements_for_inserted_rows]]
==== Requirements for Inserted Rows
Each row to be inserted must satisfy the constraints of the table or underlying base table of the view. A table constraint is satisfied if
the check condition is not false—it is either true or has an unknown value.
[[insert_using_compatible_data_types]]
==== Using Compatible Data Types
To insert a row, you must provide a value for each column in the table that has no default value. The data types of the values in each row to
be inserted must be compatible with the data types of the corresponding target columns.
===== Inserting Character Values
Any character string data type is compatible with all other character string data types that have the same character set. For fixed length, an
inserted value shorter than the column length is padded on the right with blank characters of the appropriate character set (for example,
ISO88591 blanks (HEX20). If the value is longer than the column length, string truncation of non-blank trailing characters returns an error, and
the truncated string is not inserted.
For variable length, a shorter inserted value is not padded. As is the case for fixed length, if the value is longer than the column length,
string truncation of non-blank trailing characters returns an error, and the truncated string is not inserted.
===== Inserting Numeric Values
Any numeric data type is compatible with all other numeric data types. If you insert a value into a numeric column that
is not large enough, an overflow error occurs. If a value has more digits to the right of the decimal point than
specified by the scale for the column definition, the value is truncated.
===== Inserting Interval Values
A value of interval data type is compatible with another value of interval data type only if the two data types are both
year-month or both day-time intervals.
===== Inserting Date and Time Values
Date, time, and timestamp are the three {project-name} SQL datetime data types. A value with a datetime data type is compatible
with another value with a datetime data type only if the values have the same datetime fields.
===== Inserting Nulls
and inserting values with specific data types, you might want to insert nulls. To insert null, use the keyword NULL. NULL
only works with the VALUES clause. Use cast (null as _type_) for select-list.
[[insert_examples]]
=== Examples of INSERT
* Insert a row into the CUSTOMER table without using a _target-col-list_:
+
```
INSERT INTO sales.customer
VALUES (4777, 'ZYROTECHNIKS', '11211 40TH ST.', 'BURLINGTON', 'MASS.',
'01803', 'A2');
--- 1 row(s) inserted.
```
+
The column name list is not specified for this INSERT statement. This operation works because the number of values listed
in the VALUES clause is equal to the number of columns in the CUSTOMER table, and the listed values appear in the same
order as the columns specified in the CREATE TABLE statement for the CUSTOMER table.
+
By issuing this SELECT statement, this specific order is displayed:
+
```
SELECT * FROM sales.customer WHERE custnum = 4777;
CUSTNUM CUSTNAME STREET ... POSTCODE CREDIT
------------- -------------- --------------- -------- ------
4777 ZYROTECHNIKS 11211 4OTH ST. ... 01803 A2
--- 1 row(s) selected.
```
* Insert a row into the CUSTOMER table using a _target-col-list_:
+
```
INSERT INTO sales.customer
(custnum, custname, street, city, state, postcode)
VALUES (1120, 'EXPERT MAILERS', '5769 N. 25TH PL',
'PHOENIX', 'ARIZONA', '85016');
--- 1 row(s) inserted.
```
Unlike the previous example, the insert source of this statement does not contain a value for the CREDIT column, which
has a default value. Asa result, this INSERT must include the column name list.
+
This SELECT statement shows the default value 'C1' for CREDIT:
+
```
SELECT * FROM sales.customer WHERE custnum = 1120;
CUSTNUM CUSTNAME STREET ... POSTCODE CREDIT
------------- -------------- --------------- -------- ------
1120 EXPERT MAILERS 5769 N. 25TH PL ... 85016 C1
--- 1 row(s) selected.
```
* Insert multiple rows into the JOB table by using only one INSERT statement:
+
```
INSERT INTO persnl.job
VALUES (100,'MANAGER'),
(200,'PRODUCTION SUPV'),
(250,'ASSEMBLER'),
(300,'SALESREP'),
(400,'SYSTEM ANALYST'),
(420,'ENGINEER'),
(450,'PROGRAMMER'),
(500,'ACCOUNTANT'),
(600,'ADMINISTRATOR'),
(900,'SECRETARY');
--- 10 row(s) inserted.
```
* The PROJECT table consists of five columns using the data types numeric, varchar, date, timestamp, and interval.
Insert values by using these types:
+
```
INSERT INTO persnl.project
VALUES (1000, 'SALT LAKE CITY', DATE '2007-10-02',
TIMESTAMP '2007-12-21 08:15:00.00', INTERVAL '30' DAY);
--- 1 row(s) inserted.
```
<<<
* Suppose that CUSTLIST is a view of all columns of the CUSTOMER table except the credit rating. Insert information
from the SUPPLIER table into the CUSTOMER table through the CUSTLIST view, and then update the credit rating:
+
```
INSERT INTO sales.custlist
(SELECT * FROM invent.supplier
WHERE suppnum = 10);
UPDATE sales.customer
SET credit = 'A4'
WHERE custnum = 10;
```
+
You could use this sequence in the following situation. Suppose that one of your suppliers has become a customer.
If you use the same number for both the customer and supplier numbers, you can select the information from the SUPPLIER
table for the new customer and insert it into the CUSTOMER table through the CUSTLIST view (as shown in the example).
This operation works because the columns of the SUPPLIER table contain values that correspond to the columns of the
CUSTLIST view. Further, the credit rating column in the CUSTOMER table is specified with a default value. If you want
a credit rating that is different from the default, you must update this column in the row of new customer data.
[[insert_examples_of_self_referencing_inserts]]
==== Examples of Self-Referencing Inserts
* This is an example of a self-referencing insert:
+
```
insert into table1 select pk+?, b, c from table1
```
* This is an example of a self-referencing insert where the target of the insert, table1, is also used in a subquery of the insert-source:
+
```
insert into table1
select a+16, b, c from table2 where table2.b not in
(select b from table1 where a > 16)
```
+
The source table is not affected by the insert.
<<<
[[invoke_statement]]
== INVOKE Statement
The INVOKE statement generates a record description that corresponds to a row in the specified table, view, or index.
The record description includes a data item for each column in the table, view, or index, including the primary key but
excluding the SYSKEY column. It includes the SYSKEY column of a view only if the view explicitly listed the column in its
definition.
INVOKE is a {project-name} SQL extension.
```
INVOKE table-name
```
[[invoke_syntax]]
=== Syntax Description of INVOKE
* `_table-name_`
_
specifies the name of a table, view, or index for which to generate a record description.
See <<database_object_names,Database Object Names>>.
[[invoke_considerations]]
=== Considerations for INVOKE
[[invoke_required_privileges]]
=== Required Privileges
To issue an INVOKE statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the table.
* You have the SHOW component privilege for the SQL_OPERATIONS component. The SHOW component privilege is granted to PUBLIC by default.
* You have the SELECT privilege on the target table.
[[invoke_examples]]
=== Examples of INVOKE
* This command generates a record description of the table T:
+
```
SQL> invoke trafodion.seabase.t;
-- Definition of Trafodion table TRAFODION.SEABASE.T
-- Definition current Wed Mar 5 10:36:06 2014
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE
)
PRIMARY KEY (A ASC)
--- SQL operation complete.
```
<<<
[[merge_statement]]
== MERGE Statement
The MERGE statement:
* Updates a table if the row exists or inserts into a table if the row does not exist. This is upsert functionality.
* Updates (merges) matching rows from one table to another.
```
MERGE INTO table [using-clause]
on-clause
{[when-matched-clause]|[when-not-matched-clause]} ...
using-clause is:
USING (select-query) AS derived-table-name [derived-column-names]
on-clause is:
ON predicate
when-matched-clause is:
WHEN MATCHED THEN UPDATE SET set-clause [WHERE predicate]
WHEN MATCHED THEN DELETE
when-not-matched-clause is:
WHEN NOT MATCHED THEN INSERT insert-values-list
insert-values-list is:
[(column1, ..., columnN )] VALUES (value1, ..., valueN)
```
[[merge_syntax]]
=== Syntax Description of MERGE
* `_table_`
+
is the ANSI logical name for the table.
* `ON _predicate_`
+
used to determine if a row is or is not present in the table. The ON predicate must be a predicate on the clustering
key of the table if the MERGE has a _when-not-matched-clause_. The clustering key can be a single or multi-column key.
+
The ON predicate must select a unique row if the MERGE has a _when-not-matched-clause_.
<<<
[[merge_considerations]]
=== Considerations for MERGE
[[merge_upsert_using_single_row]]
=== Upsert Using Single Row
A MERGE statement allows you to specify a set of column values that should be updated if the row is found, and another
row to be inserted if the row is not found. The ON predicate must select exactly one row that is to be updated if the
MERGE statement has an INSERT clause.
In a MERGE statement, at least one of the clauses _when-matched_ or _when-not-matched_ must be specified. Note the following:
* If a _when-matched_ clause is present and the WHERE predicate in the UPDATE is satisfied, the columns in the SET clause are updated.
* If a _when-matched_ clause is present and the WHERE predicate in the UPDATE is not satisfied, the columns in the SET clause are not updated.
* If a _when-matched_ clause is present and the UPDATE has no WHERE predicate, the columns in the SET clause are updated.
* If a _when-not-matched_ clause is present and columns are explicitly specified in the INSERT clause, the specified values for those columns
are inserted. Missing columns are updated using the default values for those columns.
This example updates column b to 20 if the row with key column a with value 10 is found. A new row (10, 30) is inserted if the row is not
found in table t.
```
MERGE INTO t ON a = 10
WHEN MATCHED THEN UPDATE SET b = 20
WHEN NOT MATCHED THEN INSERT VALUES (10, 30)
```
This example updates column b to 20 if column a with value 10 is found. If column a with value 10 is not found, nothing is done.
```
MERGE INTO t ON a = 10
WHEN MATCHED THEN UPDATE SET b = 20
```
<<<
This example inserts values (10, 30) if column a with value 10 is not found. If column a with value 10 is found, nothing is done.
```
MERGE INTO t ON a = 10
WHEN NOT MATCHED THEN INSERT VALUES (10, 30)
```
[[merge_conditional_upsert_using_single_row]]
==== Conditional Upsert Using Single Row
In this example, the MERGE statement uses a single-row conditional upsert that inserts one row (keycol, col, seqnum) value
if a row with that keycol (parameter-specified) value is not yet in table d. Otherwise, the MERGE statement updates that
row's col and seqnum columns if that row's seqnum is higher than the current (parameter-specified) sequence number. If the
matching row's seqnum column value is not higher than the current sequence number, then that matched row is not updated.
```
MERGE INTO d ON keycol = ?
WHEN MATCHED THEN UPDATE SET (col, seqnum) = (?, ?) WHERE seqnum < ?
WHEN NOT MATCHED THEN INSERT (keycol, col, seqnum) VALUES (?, ?, ?)
```
The optional WHERE predicate in the _when-matched-then-update_ clause is useful when the update is wanted only if the given
condition is satisfied. Consider this use case. Suppose object
X is represented as a row in table T. Also, suppose a stream of updates exists for object X. The updates are marked by a
sequence number at their source. However, the updates flow through a network which does not guarantee first-in, first-out
delivery. In fact, the updates may arrive out-of-order to the database. In this case, the last update (the one with the current
highest sequence number) should always win in the database. The MERGE statement shown above can be used to satisfy this
use case:
* A stream of updates for table d exists that are sequenced by a sequence number seqnum at their source
* The updates race through the network and may arrive in the database in any order, and
* You want to guarantee that the last update (the one with the highest seqnum) always wins in the database.
[[merge_restrictions]]
==== Restrictions
* The MERGE statement does not use ESP parallelism.
* A merged table cannot be a view.
* Merge is not allowed if the table has constraints.
* The _on-clause_ cannot contain a subquery. This statement is not allowed:
+
```
MERGE INTO t ON a = (SELECT a FROM t1) WHEN ...
```
* The optional WHERE predicate in the when-matched clause cannot contain a subquery or an aggregate function.
These statements are not allowed:
+
```
MERGE INTO t ON a = 10
WHEN MATCHED THEN UPDATE SET b=4 WHERE b=(SELECT b FROM t1)
WHEN NOT MATCHED THEN INSERT VALUES (10,30);
MERGE INTO t ON a=10
WHEN MATCHED THEN UPDATE SET b=4 WHERE b=MAX(b)
WHEN NOT MATCHED THEN INSERT VALUES (10,30);
```
* The UPDATE SET clause in a MERGE statement cannot contain a subquery.
This statement is not allowed:
+
```
MERGE INTO t ON a = 1 WHEN MATCHED THEN UPDATE SET b = (SELECT a FROM t1)
```
* The _insert-values-list_ clause in a MERGE statement cannot contain a subquery. This statement is not allowed:
+
```
MERGE INTO t ON a = 1 WHEN NOT MATCHED THEN INSERT VALUES ((SELECT a FROM t1))
```
* Use of a non-unique _on-clause_ for a MERGE update is allowed only if no INSERT clause exists.
+
```
MERGE INTO t USING (SELECT a,b FROM t1) x ON t.a=x.a
WHEN MATCHED THEN UPDATE SET b=x.b;
```
+
In this example, t.a=x.a is not a fully qualified unique primary key predicate.
* Use of a non-unique _on-clause_ for a MERGE delete is allowed only if no INSERT clause exists.
+
```
MERGE INTO t USING (SELECT a,b FROM t1) x ON t.a=x.a
WHEN MATCHED THEN DELETE;
```
<<<
[[merge_merge_from_one_table_into_another]]
==== MERGE From One Table Into Another
The MERGE statement can be used to upsert all matching rows from the source table into the target table.
Each row from the source table is treated as the source of a single upsert statement. The _using-clause_
contains the _select-query_ whose output is used as the source to the MERGE statement.
The source _select-query_ must be renamed using the AS clause.
```
MERGE INTO t ON
USING (_select-query_) AS Z(X) ON col = Z.X
WHEN MATCHED THEN . . .
```
For each row selected out of the select-query, the MERGE statement is evaluated. Values selected are used
in the _on-clause_ to join with the column of the merged table. If the value is found, it is updated. If it
is not found, the insert is done. The restrictions are the same as those for <<upsert_using_single_row, Upsert Using Single Row>.
[[merge_examples]]
=== Examples of MERGE
* This query extracts derived columns a and b from the USING query as derived table z and use each row to join to the
merged table t based on the _on-clause_. For each matched row, column b in table t is updated using column b in derived
table z. For rows that are not matched, values z.a and z.b are inserted.
+
```
MERGE INTO t USING
(SELECT * FROM t1) z(a,b) on a = z.a
WHEN MATCHED THEN UPDATE SET b = z.b
WHEN NOT MATCHED THEN INSERT VALUES (z.a, z.b);
```
<<<
[[prepare_statement]]
== PREPARE Statement
The PREPARE statement compiles an SQL statement for later use with the EXECUTE statement in the same {project-name} Command
Interface (TrafCI) session.
You can also use PREPARE to check the syntax of a statement without executing the statement in the same TrafCI session.
```
PREPARE statement-name FROM statement
```
[[prepare_syntax]]
=== Syntax Description of PREPARE
* `_statement-name_`
+
is an SQL identifier that specifies a name to be used for the prepared statement. See <<identifiers,Identifiers>>.
The statement name should be a character string and not a numeric value. If you specify the name of an existing
prepared statement, the new statement overwrites the previous one.
* `_statement_`
+
specifies the SQL statement to prepare.
[[prepare_considerations]]
=== Considerations for PREPARE
[[prepare_availability_of_a_prepared_statement]]
==== Availability of a Prepared Statement
If a PREPARE statement fails, any subsequent attempt to run EXECUTE on the named statement fails. Only the TrafCI
session that executes the PREPARE can run EXECUTE on the prepared statement.
The prepared statement is available for running EXECUTE until you terminate the TrafCI session.
A statement must be compiled by PREPARE before you can run EXECUTE on it. However, after the statement is compiled,
you can run EXECUTE on the statement multiple times without recompiling the statement.
<<<
[[prepare_examples]]
=== Examples of PREPARE
* Prepare a SELECT statement, checking for syntax errors:
+
```
SQL>prepare empsal from
+>select salary from employee
+>where jobcode = 100;
*** ERROR[4082] Table, view or stored procedure TRAFODION.INVENT.EMPLOYEE does not exist or is inaccessible.
*** ERROR[8822] The statement was not prepared.
SQL>
```
* Prepare a SELECT statement with an unnamed parameter (?) and later run EXECUTE on it:
+
```
SQL>prepare findsal from
+>select salary from persnl.employee
+>where jobcode = ?;
--- SQL command prepared.
SQL>execute findsal using 450;
SALARY
----------
32000.00
33000.50
40000.00
32000.00
45000.00
--- 5 row(s) selected.
SQL>
```
<<<
* Prepare a SELECT statement with a named parameter (?_param-name_) and later run EXECUTE on it:
+
```
SQL>prepare findsal from
+>select salary from persnl.employee
+>where jobcode = ?job;
--- SQL command prepared.
SQL>set param ?job 450
SQL>execute findsal;
SALARY
----------
32000.00
33000.50
40000.00
32000.00
45000.00
--- 5 row(s) selected.
SQL>
```
For more information, see the <<execute_statement,EXECUTE Statement>>.
<<<
[[register_user_statement]]
== REGISTER USER Statement
The REGISTER USER statement registers a user in the SQL database, associating the user's login name with a database username.
REGISTER USER is a {project-name} SQL extension.
NOTE: The user's login name is also the name by which the user is defined in the directory service, so the syntax description
below refers to it as the _directory-service username_.
```
REGISTER USER directory-service-username [ AS database-username ]
```
[[register_user_syntax]]
=== Syntax Description of REGISTER USER
* `_directory-service-username_`
+
is the name that identifies the user in the directory service. This is also the name the user specifies when logging in to
a {project-name} database. The _directory-service-username_ is a regular or delimited case-insensitive identifier.
See <<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
* `_database-username_`
+
is a regular or delimited case-insensitive identifier that denotes the username as defined in the database. The database
username cannot be identical to a registered database username or an existing role name. However, it can be the same as
the directory-service username. If you omit the AS _database-username_ clause, the database username will be the same as
the directory-service username.
[[register_user_register_user_considerations]]
=== Considerations for REGISTER USER
[[register_user_who_can_register_a_user]]
==== Who Can Register a User
To register a user, you must have user administrative privileges. You have user administrative privileges if you have
been granted the MANAGE_USERS component privilege. Initially, DB ROOT is the only database user who has been granted
the MANAGE_USERS component privilege.
<<<
[[register_user_add_the_user_to_the_directory_before_registering_the_user]]
==== Add the User to the Directory Before Registering the User
Add the user to the appropriate directory service before you register the user. Otherwise, REGISTER USER will fail.
[[register_user_as_database_user_clause]]
==== AS _database-username_ Clause
Use the AS _database-username_ clause to assign a database username that is different than the username defined in
the directory service. In particular, it is often convenient to assign a database username that is shorter and easier
to type than the directory-service username.
For example, if the user logs on as `John.Allen.Doe.the.Second@mycompany.com`, you might want to assign the user a
database username of JDoe.
Database user names are authorization IDs. If you specify a name already assigned to another user or to an existing
role, the command will fail. For more information, see <<authorization_ids,Authorization IDs>>.
[[register_user_reserved_names]]
==== Reserved Names
PUBLIC, _SYSTEM, NONE, and database user names beginning with DB are reserved. You cannot register users with any
such name.
[[register_user_username_length]]
==== Username Length
Database user names are limited to 128 characters.
[[register_user_examples]]
=== Examples of REGISTER USER
* To register a user and assign a database username different than the user's login name:
+
```
REGISTER USER "jsmith@company.com" AS jsmith;
```
* To register a user without specifying a database username, so the database username will be the same as the user's login name:
+
```
REGISTER USER "jsmith@company.com";
```
<<<
[[]]
== REVOKE Statement
The REVOKE statement revokes access privileges on an SQL object from specified users or roles.
Privileges can be revoked from the object, from one or more columns, or both.
IMPORTANT: This statement works only when authentication and authorization are enabled in {project-name}. For more information,
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
```
REVOKE [GRANT OPTION FOR]
{privilege [,privilege]...| ALL [PRIVILEGES]}
ON [object-type] [schema.]object
FROM {grantee}
[[GRANTED] BY grantor]
privilege is:
DELETE
| EXECUTE
| INSERT [column-list]
| REFERENCES [column-list]
| SELECT [column-list]
| UPDATE [column-list]
| USAGE
object-type is:
FUNCTION
| LIBRARY
| PROCEDURE
| SEQUENCE
| TABLE
grantee is:
auth-name
grantor is:
role-name
column-list is:
(column [,column]...)
```
=== Syntax Description of REVOKE
* `GRANT OPTION FOR`
+
Specifies that the grantee’s authority to grant the specified privileges to other users or roles (that is, WITH GRANT
OPTION) be revoked. This is an optional clause. When this clause is specified, only the ability to grant the privilege
to another user is revoked.
* `_privilege_ [, _privilege_ ] &#8230; | ALL [ PRIVILEGES ]`
+
Specifies the privileges to revoke. You can specify these privileges for an object:
+
[cols="20%,80%"]
|===
| DELETE | Revokes the ability to use the DELETE statement.
| EXECUTE | Revokes the ability to execute a stored procedure using a CALL statement or revokes the ability
| INSERT [columm-list] | Revokes the ability to use the INSERT statement.
| REFERENCES [column-list] | Revokes the ability to create constraints that reference the object.
| SELECT [column-list] | Revokes the ability to use the SELECT statement.
| UPDATE [column-list] | Revokes the ability to use the UPDATE statement.
to execute a user-defined function (UDF).
| USAGE | For libraries, revokes the ability to access a library using the
CREATE PROCEDURE or CREATE FUNCTION statement.
For sequence generators, revokes the ability to access a sequence in a SQL query.
| ALL | Revokes the ability to use all privileges that apply to the object type.
When you specify ALL for a table or view, this includes the SELECT, DELETE, INSERT, REFERENCES, and
UPDATE privileges. When the object is a stored procedure or user-defined function (UDF), this includes
the EXECUTE privilege. When the object is a library, this includes the UPDATE and USAGE privileges.
When the object is a sequence generator, only the usage privilege is applied.
|===
* `ON [_object-type_] [_schema_.]_object_`
+
Specifies an object on which to grant privileges. If none is specified, it defaults to TABLE. See <<database_object_names,"Database Object Names>> for more details. _object-type_ can be:
** FUNCTION [_schema_.]_function-name_, where _function-name_ is the name of a user-defined function in the database.
** LIBRARY [_schema_.]_library-name_, where _library-name_ is the name of a library object in the database.
** PROCEDURE [_schema_.]_procedure-name_, where _procedure-name_ is the name of a stored procedure in Java (SPJ)
registered in the database.
** SEQUENCE [_schema_.]_sequence-name_, where _sequence-name_ is the name of a sequence generator.
** [TABLE] [_schema_.]_object-name_, where _object-name_ is the name of a table or view.
* `FROM {_grantee_ [,_grantee_] &#8230; }`
+
Specifies an _auth-name_ from which you revoke privileges.
* `_auth-name_`
+
Specifies the name of an authorization ID from which you revoke privileges. See <<authorization_ids,Authorization IDs>>.
The authorization ID must be a registered database username, existing role name, or PUBLIC. The name is
a regular or delimited case-insensitive identifier. See
<<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
* `[GRANTED] BY _grantor_`
+
Allows you to revoke privileges on behalf of a role. If not specified, the privileges will be revoked
on your behalf as the current user/grantor.
* `_role-name_`
+
Specifies a role on whose behalf the GRANT operation was performed. To revoke the privileges on behalf of a role,
you must be a member of the role, and the role must have the authority to revoke the privileges;
that is, the role must have been granted the privileges WITH GRANT OPTION.
* `_column-list_`
+
Specifies the list of columns to revoke the requested privilege from.
[[revoke_considerations]]
=== Considerations for REVOKE
[[revoke_authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
You can revoke privileges for which you are the grantor, either through a direct grant or a grant done on your
behalf. If you are revoking privileges that were granted on behalf of a role, you must be a member of the role,
and you must specify the role in the [GRANTED] BY clause.
If one or more privileges have not been granted, SQL returns a warning.
When you specify the CASCADE option, all objects that were created based upon the privileges being revoked are removed.
[[revoke_examples]]
=== Examples of REVOKE
* To revoke GRANT OPTION for column level SELECT and table level DELETE privileges on a table from a user:
+
```
REVOKE GRANT OPTION FOR SELECT (part_no, part_name), DELETE ON TABLE invent.partloc FROM jsmith;
```
* To revoke the privilege to grant SELECT and DELETE privileges on a table from a user and a role:
+
```
REVOKE SELECT (part_no, part_name), DELETE ON TABLE invent.partloc FROM jsmith, clerks;
```
* To revoke a user’s SELECT privileges on a table:
+
```
-- User administrator grants the SELECT privilege to JSMITH:
GRANT SELECT ON TABLE invent.partloc TO jsmith WITH GRANT OPTION;
-- JSMITH grants the SELECT privilege to AJONES:
GRANT SELECT ON TABLE invent.partloc TO ajones;
-- If the user administrator attempts to revoke the SELECT
-- privilege from JSMITH, this would fail because
-- of the privilege granted to AJONES based on the
-- privilege granted to JSMITH.
-- To successfully revoke the SELECT privilege from
-- JSMITH, the SELECT privilege granted to AJONES
-- must be revoked first. For this example:
-- 1. JSMITH revokes the SELECT privilege granted to AJONES:
REVOKE SELECT ON TABLE invent.partloc FROM ajones;
-- 2. User administrator revokes the SELECT privilege on the
-- table from JSMITH:
REVOKE SELECT ON TABLE invent.partloc FROM jsmith RESTRICT;
-- The REVOKE operation succeeds.
-- An easier way to make the REVOKE operation successful is
-- to use the CASCADE option:
REVOKE SELECT ON TABLE invent.partloc FROM jsmith CASCADE;
-- The REVOKE operation succeeds because the CASCADE option
-- causes all specified privileges, and all privileges that
-- were granted based upon the specified privileges, to be
-- removed.
```
* Administration in the shipping department decides that the CLERKS role should no longer be able to grant
privileges on the invent.partloc table. Fred has recently moved to another department, so JSMITH revokes
the SELECT privilege on the invent.partloc table from Fred, who was granted the privilege by CLERKS. Then,
JSMITH revokes the grant option from CLERKS:
+
```
REVOKE SELECT on table invent.partloc FROM fred GRANTED BY clerks;
REVOKE GRANT OPTION FOR SELECT ON TABLE invent.partloc FROM clerks;
```
<<<
[[revoke_component_privilege_statement]]
== REVOKE COMPONENT PRIVILEGE Statement
The REVOKE COMPONENT PRIVILEGE statement removes one or more component
privileges from a user or role. See <<privileges,Privileges>> and <<roles,Roles>>.
REVOKE COMPONENT PRIVILEGE is a {project-name} SQL extension.
IMPORTANT: This statement works only when authentication and authorization are enabled in {project-name}. For more information,
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
```
REVOKE [GRANT OPTION FOR]
COMPONENT PRIVILEGE {privilege-name[, privilege-name]...}
ON component-name
FROM grantee
[[GRANTED] BY grantor]
grantee is:
auth-name
grantor is:
role-name
```
=== Syntax Description of REVOKE COMPONENT PRIVILEGE
* `GRANT OPTION FOR`
+
specifies that the grantee’s authority to grant the specified component privileges to other users or roles (that is,
WITH GRANT OPTION) be revoked. This is an optional clause. When this clause is specified, only the ability to grant
the component privilege to another user is revoked.
* `_privilege-name_`
+
specifies one or more component privileges to revoke. The comma-separated list can include only privileges within the
same component.
* `ON _component-name_`
+
specifies a valid component name on which to revoke component privileges. Currently, the only valid component name is
SQL_OPERATIONS.
* `FROM _grantee_`
+
specifies an _auth-name_ from which you revoke the component privileges.
* `_auth-name_`
+
specifies the name of an authorization ID from which you revoke privileges. See <<authorization_ids,Authorization IDs>>.
The authorization ID must be a registered database username, existing role name, or PUBLIC. The name is a regular or
delimited case-insensitive identifier. See
<<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
* `[ GRANTED ] BY _grantor_`
+
allows you to revoke component privileges on behalf of a role. If not specified, the component privileges will be revoked
on your behalf as the current user/grantor.
* `_role-name_`
+
specifies a role on whose behalf the GRANT COMPONENT PRIVILEGE operation was performed. To revoke the privileges on behalf
of a role, you must be a member of the role, and the role must have the authority to revoke the privileges; that is, the
role must have been granted the privileges WITH GRANT OPTION.
[[revoke_component_privilege_considerations]]
=== Considerations for REVOKE COMPONENT PRIVILEGE
* At revoke time, all privileges granted WITH GRANT OPTION are removed. That is, the revoke behavior is CASCADE.
* If none of the component privileges has been granted, SQL returns an error.
* If one or more component privileges have not been granted, SQL silently ignores those privileges and proceeds with
the revoke operation.
* Component privileges must be revoked before a role can be dropped or a user unregistered. If any privileges have been
granted to a role or user, an error is returned when that role is dropped or the user unregistered. For more information,
see the <<drop_role_statement,DROP ROLE Statement>> and the <<unregister_user_statement,UNREGISTER USER Statement>>.
[[revoke_component_privilege_authorization_and_availability_requirements]]
==== Authorization and Availability Requirements
You can revoke component privileges for which you are the grantor, either through a direct grant or a grant done on your
behalf. If you are revoking privileges that were granted on behalf of a role, you must be a member of the role, and you
must specify the role in the [GRANTED] BY clause.
<<<
[[revoke_component_examples]]
=== Examples of REVOKE COMPONENT PRIVILEGE
* Revoke a component privilege from SQLUSER1:
+
```
REVOKE COMPONENT PRIVILEGE CREATE_TABLE ON SQL_OPERATIONS FROM sqluser1;
```
<<<
[[revoke_role_statement]]
== REVOKE ROLE Statement
The REVOKE ROLE statement removes one or more roles from a user. See
<<roles,Roles>>.
IMPORTANT: This statement works only when authentication and authorization are enabled in {project-name}. For more information,
{docs-url}/provisioning_guide/index.html#enable-security [Enable Secure {project-name}].
```
REVOKE ROLE {role-name [,role-name]...}
FROM grantee
[RESTRICT | CASCADE]
grantee is:
database-username
```
[[revoke_role_syntax]]
=== Syntax Description of REVOKE ROLE
* `_role-name_ [, _role-name_ ] &#8230;`
+
specifies the valid roles to revoke.
* `FROM _grantee_`
+
specifies the registered database username from whom you revoke the roles.
* `[ RESTRICT | CASCADE ]`
+
If you specify RESTRICT, the REVOKE ROLE operation fails if any privileges were granted to
the role or any objects were created based upon those privileges.
+
If you specify CASCADE, any dependent privileges are removed as part of the REVOKE ROLE operation.
+
The default value is RESTRICT.
<<<
[[revoke_role_considerations]]
=== Considerations for REVOKE ROLE
* To revoke roles from users, you must own the roles or have user administrative privileges for the roles. You have
user administrative privileges for roles if have been granted the MANAGE_ROLES component privilege. Initially,
DB ROOT is the only database user who has been granted the MANAGE_ROLES component privilege.
* If RESTRICT (or nothing) is specified and if you want to revoke a role from a user that has created objects based
solely on role privileges, you must drop the objects before revoking the role. However, if you specify CASCADE, the
dependent objects are automatically dropped, and the role is revoked.
* All of the specified roles must have been granted to the specified user. If any role has not been granted to the user,
the operation returns an error, and no roles are revoked.
* When you revoke a role from a user, the reduction in privileges is automatically propagated to and detected by
active sessions. There is no need for users to disconnect from and reconnect to a session to see the updated set of privileges.
* If the REVOKE ROLE names multiple roles and any errors occur in processing, no revokes are performed.
[[revoke_role_examples]]
=== Examples of REVOKE ROLE
* To revoke multiple roles from a user:
+
```
REVOKE ROLE clerks, sales FROM jsmith;
```
<<<
* To revoke a role with dependent objects from a user:
+
```
-- CMILLER grants a role to AJONES:
GRANT ROLE sales TO ajones;
-- CMILLER grants a privilege to the role:
GRANT SELECT ON TABLE invent.partloc TO sales;
-- AJONES creates a view based upon the privilege granted
-- to the role granted to him:
CREATE VIEW invent.partlocview (partnum, loc_code) AS
SELECT partnum, loc_code FROM invent.partloc;
-- If CMILLER attempts to revoke the role from AJONES,
-- this would fail because of the view created based
-- upon the privilege granted to the role granted to
-- AJONES.
-- CMILLER revokes the role from AJONES with the CASCADE option:
REVOKE ROLE sales from AJONES CASCADE;
-- The REVOKE ROLE operation succeeds, and all dependent object privileges are revoked.
```
<<<
[[rollback_work_statement]]
== ROLLBACK WORK Statement
The ROLLBACK WORK statement undoes all database modifications to objects made during the current transaction and ends
the transaction. See <<transaction_management,Transaction Management>>.
```
ROLLBACK [WORK]
```
[[rollback_work_syntax]]
=== Syntax Description of ROLLBACK WORK
WORK is an optional keyword that has no effect.
ROLLBACK WORK issued outside of an active transaction generates error 8609.
[[rollback_work_considerations]]
=== Considerations for ROLLBACK WORK
[[rollback_work_begin_and_end_a_transaction]]
==== Begin and End a Transaction
BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction.
[[rollback_work_examples]]
=== Example of ROLLBACK WORK
* Suppose that you add an order for two parts numbered 4130 to the ORDERS and ODETAIL tables. When you update the PARTLOC
table to decrement the quantity available, you discover no such part number exists in the given location.
+
Use ROLLBACK WORK to terminate the transaction without committing the database changes:
+
```
BEGIN WORK;
INSERT INTO sales.orders
VALUES (124, DATE '2007-04-10',
DATE '2007-06-10', 75, 7654);
INSERT INTO sales.odetail VALUES (124, 4130, 25000, 2);
UPDATE invent.partloc
SET qty_on_hand = qty_on_hand - 2
WHERE partnum = 4130 AND loc_code = 'K43';
ROLLBACK WORK;
```
+
ROLLBACK WORK cancels the insert and update that occurred during the transaction.
<<<
[[select_statement]]
== SELECT Statement
The SELECT statement is a DML statement that retrieves values from tables, views, and derived tables determined by the
evaluation of query expressions, or joined tables.
```
sql-query is:
query-specification
| query-expr-and-order
query-specification is:
[with-clause]
SELECT [ "[" ANY N "]" | "[" FIRST N "]" | "[" LAST N "]" ] [ALL | DISTINCT] select-list
FROM table-ref [,table-ref]...
[WHERE search-condition]
[SAMPLE sampling-method]
[TRANSPOSE transpose-set [transpose-set]...
[KEY BY key-colname]]...
[SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]]
[,colname [ASC[ENDING] | DESC[ENDING]]]...]
[GROUP BY {colname | colnum} [,{colname | colnum}]...]
[HAVING search-condition]
[access-clause]
[mode-clause]
With-clause is:
WITH with_clause_elements
with_clause_elements is:
with_clause_element [,with_clause_element] …
with_clause_element is:
cte-table-name AS (sql-query)
query-expr-and-order is:
query-expr [order-by-clause] [access-clause] [mode-clause]
query-expr is:
query-primary
| query-expr UNION [ALL] query-primary
| query-expr INTERSECT query-primary
| query-expr EXCEPT query-primary
query-primary is:
simple-table | (query-expr)
simple-table is:
VALUES (row-value-const) [,(row-value-const)]...
| TABLE table
| query-specification
row-value-const is:
row-subquery
| {expression | NULL} [,{expression | NULL}]...
order-by-clause is:
[ORDER BY {colname | colnum} [ASC[ENDING] | DESC[ENDING]]
[,{colname | colnum} [ASC[ENDING] | DESC[ENDING]]]...]
[access-clause]
access clause is:
[FOR] access-option ACCESS
```
<<<
```
access-option is:
READ COMMITTED
[LIMIT num]
select-list is:
* | select-sublist [,select-sublist]...
select-sublist is:
corr.* | [corr.]single-col [[AS]name] | col-expr [[AS] name]
table-ref is:
table [[AS] corr [(col-expr-list)]]
| view [[AS] corr [(col-expr-list)]]
| (query-expr) [AS] corr [(col-expr-list)]
| (delete-statement [RETURN select-list])
[AS] corr [(col-expr-list)]
| (update-statement [RETURN select-list])
[AS] corr [(col-expr-list)]
| (insert-statement) [AS] corr [(col-expr-list)]
| joined-table
joined-table is:
table-ref [join-type] JOIN table-ref join-spec
| table-ref NATURAL [join-type] JOIN table-ref
| table-ref CROSS JOIN table-ref
| (joined-table)
join-type is:
INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]
join-spec is:
ON search-condition
sampling-method is:
RANDOM percent-size
| FIRST rows-size
[SORT BY colname [ASC[ENDING] | DESC[ENDING]]
[,colname [ASC[ENDING] | DESC[ENDING]]...]
| PERIODIC rows-size EVERY number-rows ROWS
[SORT BY colname [ASC[ENDING] | DESC[ENDING]]
[,colname [ASC[ENDING] | DESC[ENDING]]...]
percent-size is:
percent-result PERCENT [ROWS]
| BALANCE WHEN condition
THEN percent-result PERCENT [ROWS]
[WHEN condition
THEN percent-result PERCENT [ROWS]]...
[ELSE percent-result PERCENT [ROWS]] END
```
<<<
```
rows-size is:
number-rows ROWS
| BALANCE WHEN condition
THEN number-rows ROWS
[WHEN condition
THEN number-rows ROWS]...
[ELSE number-rows ROWS] END
transpose-set is:
transpose-item-list AS transpose-col-list
transpose-item-list is:
expression-list | (expression-list) [,(expression-list)]...
```
[[select_syntax]]
=== Syntax Description of SELECT
* with-clause
+
With-clause, known as Common Table Expressions (CTEs) or subquery factoring clause, was introduced in the SQL-99 standard and has been implemented into Trafodion R2.1.
+
The with-clause assigns an alias to a complex expression. The alias is treated like a temporary table or an inline view that lasts only for the duration of the query and can be referenced multiple times in the same query.
+
By abstracting the complicated parts of the query into simpler, separate and logical blocks, and possibly materializing the results of these parts to avoid recomputing it multiple times, the with-clause has following advantages:
** Simplifies complicated queries, increasing readability and reducing repeated references.
** Builds reusable units and decreases maintenance cost.
** Shortens response time and enhances performance of the query.
** Improves compatibility with other systems which support with-clause as well.
* cte-table-name
+
specifies the unique name of the CTE to be created, which is a valid SQL identifier with a maximum of 128 characters. Duplicate names are not allowed in a single with-clause.
* `"[" ANY _N_ "]" | "[" FIRST _N_ "]" | "[" LAST _N_ "]" `
+
specifies that _N_ rows are to be returned (assuming the table has at least _N_ rows and that the qualification
criteria specified in the WHERE clause, if any, would select at least _N_ rows) and you do not care which _N_ rows
are chosen (out of the qualified rows) to actually be returned.
+
You must enclose `ANY _N_`, `FIRST _N_` or `LAST _N_` in square brackets ([]). The quotation marks ("") around each square bracket in
the syntax diagram indicate that the bracket is a required character that you must type as shown (for example, [ANY 10]
or [FIRST 5]). Do not include quotation marks in ANY or FIRST clauses.
+
[FIRST _N_] is different from [ANY _N_] only if you use ORDER BY on any of the columns in the select list to sort the
result table of the SELECT statement. _N_ is an unsigned numeric literal with no scale. If _N_ is greater than the number
of rows in the table, all rows are returned. [ANY _N_] and [FIRST _N_] are disallowed in nested SELECT statements and on
either side of a UNION operation.
+
`[LAST _N_]` performs the entire query and calculates elapsed time. The _N_ must be 0 or 1. `[LAST _0_]` does not return any rows. `[LAST _1_]` returns only the last qualified row.
* `ALL | DISTINCT`
+
specifies whether to retrieve all rows whose columns are specified by the _select-list_ (ALL) or only rows that are not
duplicates (DISTINCT). Nulls are considered equal for the purpose of removing duplicates. The default is ALL.
<<<
* `_select-list_`
+
specifies the columns or column expressions to select from the table references in the FROM clause.
See the discussion of limitations in <<considerations_for_select_list,Considerations for Select List>>.
** *
+
specifies all columns in a table, view, joined table, or derived table determined by the evaluation of a query expression,
as specified in the FROM clause.
** `_corr_.*`
+
specifies all columns of specific table references by using the correlation name _corr_ of the table references, as
specified in the FROM clause. See <<correlation_names,Correlation Names>>.
** `_corr_._single-col_ [[AS] _name_]`
+
specifies one column of specific table references by using the correlation name of the table reference, as specified in
the FROM clause. See <<correlation_names,Correlation Names>>. By using the AS clause, you can associate the column with
a _name_. _name_ is an SQL identifier. See <<identifiers,Identifiers>>.
** `_single-col_ [[AS] _name_]`
+
specifies a column. By using the AS clause, you can associate the column with a _name_. _name_ is an SQL identifier. See
<<identifiers,Identifiers>>.
** `_col-expr_ [[AS] _name_]`
+
specifies a derived column determined by the evaluation of an SQL value expression in the list. By using the AS clause,
you can associate a derived column, _col-expr_, with a _name_. _name_ is an SQL identifier. See <<identifiers,Identifiers>>.
* `FROM _table-ref_ [,_table-ref_]&#8230;`
+
specifies a list of tables, views, derived tables, or joined tables that determine the contents of an intermediate result
table from which {project-name} SQL returns the columns you specify in _select-list_.
+
If you specify only one _table-ref_, the intermediate result table consists of rows derived from that table reference. If you specify more
than one _table-ref_, the intermediate result table is the cross-product of result tables derived from the individual table
references.
<<<
** `table [[AS] corr [(col-expr-list)]] | view [[AS] corr [(col-expr-list)]] |
(query-expr) [AS] corr [(col-expr-list)] | (delete-statement [RETURN
select-list]) [AS] corr [(col-expr-list)] | (update-statement [RETURN
select-list]) [AS] corr [(col-expr-list)] | (insert-statement) [AS] corr
[(col-expr-list)] | joined-table`
+
specifies a _table-ref_ as a single table, view, derived table determined by the evaluation of a query expression, or a joined table.
+
You can specify this optional clause for a table or view. This clause is required for a derived table:
*** `[AS] _corr_ [(_col-expr-list)_]`
+
specifies a correlation name, _corr_, for the preceding table reference _table-ref_ in the FROM clause.
See <<correlation_names,Correlation Names>>.
*** `_col-expr_ [[AS] _name_] [,_col-expr_ [[AS] _name_]] &#8230;`
+
specifies the items in _col-expr-list_, a list of derived columns. By using the AS clause, you can associate a derived column,
_col-expr_, with a _name_. _name_ is an SQL identifier. See <<identifiers,Identifiers>>.
+
For the specification of a query expression, see the syntax diagram for _query-expr_ above.
** `(_delete-statement_ [RETURN _select-list_]) [AS] _corr_ [(_col-expr-list_)]`
+
enables an application to read and delete rows with a single operation. For the syntax of
_delete-statement_, see the <<delete_statement, DELETE Statement>>.
*** `RETURN _select-list_`
+
specifies the columns or column expressions returned from the deleted row. the items in the _select-list_ can be of these forms:
**** `[old.]*`
+
specifies the row from the old table exposed by the embedded delete. the old table refers to column values before the delete
operation. new is not allowed. an implicit old.* return list is assumed for a delete operation that does not specify a return list.
<<<
**** `_col-expr_ [[AS] _name_]`
+
specifies a derived column determined by the evaluation of an sql value expression in the list. any column referred to in a value
expression is from the row in the old table exposed by the delete. the old table refers to column values before the delete operation.
+
By using the as clause, you can associate a derived column, _col-expr_, with a _name_. _name_ is an SQL identifier. See
<<identifiers,Identifier>>.
*** `[AS] _corr_ [(_col-expr-list_)]`
+
specifies a correlation name, _corr_, and an optional column list for the preceding items in the select list RETURN _select-list_.
See <<correlation_names,Correlation Names>>.
** `(_update-statement_ [RETURN _select-list_]) [AS] _corr_ [(_col-expr-list_)]`
+
enables an application to read and update rows with a single operation. For the syntax of _update-statement_, see the
<<update_statement,UPDATE Statement>>.
*** `RETURN _select-list_`
+
specifies the columns or column expressions returned from the updated row. The items in the _select-list_ can be of these forms:
**** `[OLD.| NEW.]*`
+
specifies the row from the old or new table exposed by the update. The old table refers to column values before the update
operation; the new table refers to column values after the update operation. If a column has not been updated, the new value
is equivalent to the old value.
+
An implicit NEW.* return list is assumed for an update operation that
does not specify a return list.
<<<
**** `_col-expr_ [[AS] _name_]`
+
specifies a derived column determined by the evaluation of an SQL value expression in the list. Any column referred to in
a value expression can be specified as being from the row in the old table exposed by the update or can be specified as being
from the row in the new table exposed by the update.
+
For example:
+
```
RETURN old.empno,old.salary,new.salary, (new.salary - old.salary).
```
+
By using the AS clause, you can associate a derived column, _col-expr_, with a _name_. _name_ is an SQL identifier.
See <<identifiers,Identifiers>>.
*** `[AS] _corr_ [(_col-expr-list_)]`
+
specifies a correlation name, _corr_, and an optional column list for the preceding items in the select list RETURN
_select-list_. See <<correlation_names,Correlation Names>>. For example:
+
```
RETURN old.empno,old.salary,new.salary,
(new.salary - old.salary)
AS emp (empno, oldsalary, newsalary, increase).
```
** `(_insert-statement_) [AS] _corr_ [(_col-expr-list_)]`
+
For the syntax of _insert-statement_, see the <<insert_statement,INSERT Statement>>.
*** `[AS] _corr_ [(_col-expr-list_)]`
+
specifies a correlation name, _corr_, and an optional column list. See <<correlation_names,Correlation Names>>.
** `_joined-table_`
+
A _joined-table_ can be specified as:
+
```
_table-ref_ [_join-type_] JOIN _table-ref join-spec_
| _table-ref_ NATURAL [_join-type_] JOIN _table-ref_
| _table-ref_ CROSS JOIN _table-ref_
| (_joined-table_)
```
<<<
*** `_join-type_ is: INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]`
+
is a joined table. You specify the _join-type_ by using the CROSS, INNER, OUTER, LEFT, RIGHT, and FULL keywords.
If you omit the optional OUTER keyword and use LEFT, RIGHT, or FULL in a join, {project-name} SQL assumes the join is
an outer join.
+
If you specify a CROSS join as the _join-type_, you cannot specify a NATURAL join or a
*** `_join-spec_.`
+
If you specify an INNER, LEFT, RIGHT, or FULL join as the _join-type_ and you do not specify a NATURAL join, you
must use an ON clause as the _join-spec_, as follows: subqueries are not allowed in the join predicate of FULL OUTER JOIN.
*** `ON _search-condition_`
+
specifies a _search-condition_ for the join. Each column reference in _search-condition_ must be a column that exists
in either of the two result tables derived from the table references to the left and right of the JOIN keyword. A join
of two rows in the result tables occurs if the condition is satisfied for those rows.
+
The type of join and the join specification if used determine which rows are joined from the two table references, as follows:
**** `_table-ref_ CROSS JOIN _table-ref_`
+
joins each row of the left _table-ref_ with each row of the right _table-ref_.
**** `_table-ref_ NATURAL JOIN _table-ref_`
+
joins rows only where the values of all columns that have the same name in both tables match. This option is equivalent
to NATURAL INNER.
**** `_table-ref_ NATURAL LEFT JOIN _table-ref_`
+
joins rows where the values of all columns that have the same name in both tables match, plus rows from the left _table-ref_
that do not meet this condition.
**** `_table-ref_ NATURAL RIGHT JOIN _table-ref_`
+
joins rows where the values of all columns that have the same name in both tables match, plus rows from the right _table-ref_
that do not meet this condition.
<<<
**** `_table-ref_ NATURAL FULL JOIN _table-ref_`
+
joins rows where the values of all columns that have the same name in both tables match, plus rows from either side that
do not meet this condition, filling in NULLs for missing values.
**** `_table-ref_ JOIN _table-ref join-spec_`
+
joins only rows that satisfy the condition in the _join-spec_ clause. This option is equivalent to INNER JOIN &#8230; ON.
**** `_table-ref_ LEFT JOIN _table-ref join-spec_`
+
joins rows that satisfy the condition in the _join-spec_ clause, plus rows from the left _table-ref_ that do not
satisfy the condition.
**** `_table-ref_ RIGHT JOIN _table-ref join-spec_`
+
joins rows that satisfy the condition in the _join-spec_ clause, plus rows from the right
_table-ref_ that do not satisfy the condition.
**** `_table-ref_ FULL OUTER JOIN _table-ref join-spec_`
+
combines the results of both left and right outer joins. These joins show records from both tables and fill in NULLs for missing matches on
either side
* `_simple-table_`
+
A _simple-table_ can be specified as:
+
```
VALUES (_row-value-const_) [,(_row-value-const_)]...
| TABLE _table_
| _query-specification_
```
+
A _simple-table_ can be a table value constructor. It starts with the VALUES keyword followed by a sequence of row
value constructors, each of which is enclosed in parentheses. A _row-value-const_ is a list of expressions (or NULL)
or a row subquery (a subquery that returns a single row of column values). An operand of an expression cannot reference
a column (except when the operand is a scalar subquery returning a single column value in its result table).
+
The use of NULL as a _row-value-const_ element is a {project-name} SQL extension.
+
A _simple-table_ can be specified by using the TABLE keyword followed by a table name, which is equivalent to the
query specification SELECT * FROM _table_.
+
A _simple-table_ can be a _query-specification_—that is, a SELECT statement consisting of SELECT &#8230; FROM &#8230; with
optionally the WHERE, SAMPLE, TRANSPOSE, SEQUENCE BY, GROUP BY, and HAVING clauses.
* `WHERE _search-condition_`
+
specifies a _search-condition_ for selecting rows. See <<search_condition,Search Condition>>. The WHERE clause
cannot contain an aggregate (set) function.
+
The _search-condition_ is applied to each row of the result table derived from the table reference in the FROM clause
or, in the case of multiple table references, the cross-product of result tables derived from the individual table references.
+
Each column you specify in _search-condition_ is typically a column in this intermediate result table. In the case of nested
subqueries used to provide comparison values, the column can also be an outer reference. See<<subquery,Subquery>>.
+
To comply with ANSI standards, {project-name} SQL does not move aggregate predicates from the WHERE clause to a HAVING clause and
does not move non-aggregate predicates from the HAVING clause to the WHERE clause.
* `SAMPLE _sampling-method_`
+
specifies the sampling method used to select a subset of the intermediate result table of a SELECT statement. Each of the
methods uses a sampling size. The three sampling methods—random, first, and periodic—are specified as:
** `RANDOM _percent-size_`
+
directs {project-name} SQL to choose rows randomly (each row having an unbiased probability of being chosen) without replacement
from the result table. The sampling size is determined by using a percent of the result table.
** `FIRST _rows-size_ [SORT BY _colname_ [,_colname_]&#8230;]`
+
directs {project-name} SQL to choose the first _rows-size_ rows from the sorted result table. The sampling size is determined
by using the specified number of rows.
** `PERIODIC _rows-size_ EVERY _number-rows_ ROWS [SORT BY _colname_ [,_colname_] &#8230;]`
+
directs {project-name} SQL to choose the first rows from each block (period) of contiguous sorted rows. The sampling size is
determined by using the specified number of rows chosen from each block.
** SAMPLE is a {project-name} SQL extension. See <<sample_clause,SAMPLE Clause>>.
* `TRANSPOSE _transpose-set_[_transpose-set_]&#8230; [KEY BY _key-colname_]`
+
specifies the _transpose-sets_ and an optional key clause within a TRANSPOSE clause. You can use multiple TRANSPOSE
clauses in a SELECT statement.
** `_transpose-item-list_ AS _transpose-col-list_`
+
specifies a _transpose-set._ You can use multiple transpose sets within a TRANSPOSE clause. The TRANSPOSE clause generates,
for each row of the source table derived from the table reference or references in the FROM clause, a row for each item in
each _transpose-item-list_ of all the transpose sets.
+
The result table of a TRANSPOSE clause has all the columns of the source table plus a value column or columns, as specified
in each _transpose-col-list_ of all the transpose sets, and an optional key column _key-colname_.
** `KEY BY _key-colname_`
+
optionally specifies an optional key column _key-colname_. It identifies which expression the value in the transpose column
list corresponds to by its position in the _transpose-item-list_. _key-colname_ is an SQL identifier. The data type is exact
numeric, and the value is NOT NULL.
** TRANSPOSE is a {project-name} SQL extension. See <<transpose_clause,TRANSPOSE Clause>>.
* `SEQUENCE BY _colname_ [ASC[ENDING] | DESC[ENDING]] [,_colname_ [ASC[ENDING] | DESC[ENDING]]] &#8230;`
+
specifies the order in which to sort the rows of the intermediate result table for calculating sequence functions. You must
include a SEQUENCE BY clause if you include a sequence function in _select-list_. Otherwise, {project-name} SQL returns an error.
Further, you cannot include a SEQUENCE BY clause if no sequence function is in _select-list_.
** `_colname_`
+
names a column in _select-list_ or a column in a table reference in the FROM clause of the SELECT statement. _colname_ is
optionally qualified by a table, view, or correlation name; for example, CUSTOMER.CITY.
** `ASC | DESC`
+
specifies the sort order. The default is ASC. When {project-name} SQL orders an intermediate result table on a column that can
contain null, nulls are considered equal to one another but greater than all other non-null values.
<<<
* `GROUP BY [_col-expr_] {_colname_ | _colnum_} [,{_colname_ | _colnum_}]&#8230;]`
+
specifies grouping columns that define a set of groups for the result table of the SELECT statement. The expression in the
GROUP BY clause must be exactly the same as the expression in the select list. These columns must appear in the list of
columns in the table references in the FROM clause of the SELECT statement.
+
If you include a GROUP BY clause, the columns you refer to in the _select-list_ must be grouping columns or arguments of
an aggregate (or set) function.
+
The grouping columns define a set of groups in which each group consists of rows with identical values in the specified
columns. The column names can be qualified by a table or view name or a correlation name; for example, CUSTOMER.CITY.
+
For example, if you specify AGE, the result table contains one group of rows with AGE equal to 40 and one group of rows
with AGE equal to 50. If you specify AGE and then JOB, the result table contains one group for each age and, within each
age group, subgroups for each job code.
+
You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list. For example, GROUP BY 3, 2, 1.
+
For grouping purposes, all nulls are considered equal to one another. The result table of a GROUP BY clause can have only
one null group. See <<considerations_for_group_by,Considerations for GROUP BY>>.
* `HAVING _search-condition_`
+
specifies a _search-condition_ to apply to each group of the grouped table resulting from the preceding GROUP BY clause
in the SELECT statement.
+
To comply with ANSI standards, {project-name} SQL does not move aggregate predicates from the WHERE clause to a HAVING clause
and does not move non-aggregate predicates from the HAVING clause to the WHERE clause.
+
If no GROUP BY clause exists, the _search-condition_ is applied to the entire table (which consists of one group) resulting
from the WHERE clause (or the FROM clause if no WHERE clause exists).
+
In _search-condition_, you can specify any column as the argument of an aggregate (or set) function; for example, AVG (SALARY).
An aggregate function is applied to each group in the grouped table.
+
A column that is not an argument of an aggregate function must be a grouping column. When you refer to a grouping column,
you are referring to a single value because each row in the group contains the same value in the grouping column.
See <<search_condition,Search Condition>>.
<<<
* `[FOR] _access-option_ ACCESS`
+
specifies the _access-option_ when accessing data specified by the SELECT statement or by a table reference in the FROM
clause derived from the evaluation of a query expression that is a SELECT statement.
See <<data_consistency_and_access_options,Data Consistency and Access Options>>.
* `READ COMMITTED`
+
specifies that any data accessed must be from committed rows.
* `UNION [ALL] _select-stmt_`
+
specifies a set union operation between the result table of a SELECT statement and the result table of another SELECT statement.
+
The result of the union operation is a table that consists of rows belonging to either of the two contributing tables. If
you specify UNION ALL, the table contains all the rows retrieved by each SELECT statement. Otherwise, duplicate rows are removed.
+
The select lists in the two SELECT statements of a union operation must have the same number of columns, and columns in
corresponding positions within the lists must have compatible data types. The select lists must not be preceded by
[ANY _N_] or [FIRST N].
+
The number of columns in the result table of the union operation is the same as the number of columns in each select list.
The column names in the result table of the union are the same as the corresponding names in the select list of the left
SELECT statement. A column resulting from the union of expressions or constants has the name (EXPR).
See <<considerations_for_union,Considerations for UNION>>.
* `_select-stmt_ INTERSECT _select-stmt_`
+
specifies a set intersect operation between the result table of a SELECT statement and the result table of another SELECT statement.
The result of the intersect operation is a table that consists of rows appear in both result sets.
+
The result of INTERSECT does not contain any duplicate rows.
+
The select lists in the two SELECT statements of an intersect operation must have the same number of columns, and columns in
corresponding positions within the lists must have compatible data types. The select lists must not be preceded by
[ANY _N_] or [FIRST N].
* `_select-stmt_ EXCEPT _select-stmt_`
+
specifies a set except operation between the result table of a SELECT statement and the result table of another SELECT statement.
The result of the except operation is a table that consists of rows that are in the result of the left SELECT statement but not in
the result of the right one.
+
The result of EXCEPT does not contain any duplicate rows.
+
The select lists in the two SELECT statements of an except operation must have the same number of columns, and columns in
corresponding positions within the lists must have compatible data types. The select lists must not be preceded by
[ANY _N_] or [FIRST N].
* `ORDER BY {_colname_ | _colnum_} [ASC[ENDING] | DESC[ENDING]] [,{_colname_ | _colnum_} [ASC[ENDING] | DESC[ENDING]]]&#8230;`
+
specifies the order in which to sort the rows of the final result table.
** `_colname_`
+
names a column in _select-list_ or a column in a table reference in the FROM clause of the SELECT statement.
_colname_ is optionally qualified by a table, view, or correlation name; for example, CUSTOMER.CITY. If a
column has been aliased to another name you must use the alias name.
** `_colnum_`
+
specifies a column by its position in _select-list_. Use _colnum_ to refer to unnamed columns, such as derived columns.
** `ASC | DESC`
+
specifies the sort order. The default is ASC. For ordering a result table on a column that can contain null, nulls are
considered equal to one another but greater than all other non-null values.
+
See <<considerations_for_order_by,Considerations for ORDER BY>>.
* `LIMIT _num_`
+
limits the number of rows returned by the query with no limit applied if _num_ is null or less than zero. The LIMIT
clause is executed after the ORDER BY clause to support TopN queries.
[[select_considerations]]
=== Considerations for SELECT
[[select_authorization_requirements]]
==== Authorization Requirements
SELECT requires authority to read all views and tables referred to in the statement, including the underlying tables
of views referred to in the statement.
[[select_use_of_views_with_select]]
==== Use of Views With SELECT
When a view is referenced in a SELECT statement, the specification that defines the view is combined with the statement.
The combination can cause the SELECT statement to be invalid. If you receive an error message that indicates a problem
but the SELECT statement seems to be valid, check the view definition.
For example, suppose that the view named AVESAL includes column A defined as AVG (X). The SELECT statement that
contains MAX (A) in its select list is invalid because the select list actually contains MAX (AVG (X)), and an aggregate
function cannot have an argument that includes another aggregate function.
[[select_join_limits]]
==== Join Limits
NOTE: We recommend that you limit the number of tables in a join to a maximum of 64, which includes base tables of views
referenced in joins. Queries with joins that involve a larger number of tables are not guaranteed to compile.
[[select_object_names_in_select]]
==== Object Names in SELECT
You can use fully qualified names only in the FROM clause of a SELECT statement.
[[select_as_and_order_by_conflicts]]
==== AS and ORDER BY Conflicts
When you use the AS verb to rename a column in a SELECT statement, and the ORDER BY clause uses the original column name,
the query fails. If a column has been aliased to another name, you must use the alias name. The ANSI standard does not
support this type of query.
[[select_restrictions_on_embedded_inserts]]
==== Restrictions on Embedded Inserts
* An embedded INSERT cannot be used in a join.
* An embedded INSERT cannot appear in a subquery.
* An embedded INSERT statement cannot have a subquery in the WHERE clause.
* An INSERT statement cannot contain an embedded INSERT statement.
* A union between embedded INSERT expressions is not supported.
* Declaring a cursor on an embedded INSERT statement is not supported.
[[select_distinct_aggregate_functions]]
==== DISTINCT Aggregate Functions
An aggregate function can accept an argument specified as DISTINCT, which eliminates duplicate values before the aggregate
function is applied. For a given grouping, multiple DISTINCT aggregates are allowed and can be used with non distinct aggregates.
A restriction exists that DISTINCT STDDEV and VARIANCE cannot be used with multiple DISTINCT aggregates.
[[select_limitations_of_distinct_aggregates]]
==== Limitations of DISTINCT Aggregates
* No limit exists to the number of distinct aggregates.
* Distinct STDDEV and distinct VARIANCE are not supported with multiple distinct aggregates. For example, this statement will
result in an error.
+
```
SELECT sum(distinct a), stddev(distinct b) from T group by d;
```
<<<
[[select_examples_of_multiple_distinct_aggregates]]
==== Examples of Multiple Distinct Aggregates
* This statement contains distinct aggregates:
+
```
SELECT sum(distinct a), count(distinct b), avg(distinct c) from T group by d;
```
* This statement does not contain multiple distincts. Because each distinct aggregate is on the same column (a), this is
treated as one distinct value.
+
```
SELECT sum(distinct a), count(distinct a), avg(distinct a) from T group by d;
```
* This statement shows that multiple distinct aggregates can be used with non distinct aggregates:
+
```
SELECT sum(distinct a), avg(distinct b), sum(c) from T group by d;
```
[[select_considerations_for_with-clause]]
==== Considerations for with-clause
* Materialization of CTEs in temporary tables is not yet enabled by default.
* Trafodion only supports non-recursive common table expressions, which means with-clause cannot be self-referencing, but it can reference a previously defined CTE within the same with-clause.
* The with-clause in Trafodion can be specified only once, at the beginning of a SELECT statement. INSERT, UPDATE, DELETE and CREATE VIEW statements are not supported yet.
[[select_considerations_for_select_list]]
==== Considerations for Select List
* The * and _corr_.* forms of a _select-list_ specification are convenient. However, such specifications make the order
of columns in the SELECT result table dependent on the order of columns in the current definition of the referenced tables
or views.
* A _col-expr_ is a single column name or a derived column. A derived column is an SQL value expression; its operands can
be numeric, string, datetime, or interval literals, columns, functions (including aggregate unctions) defined on columns,
scalar subqueries, CASE expressions, or CAST expressions. Any single columns named in _col-expr_ must be from tables or views
specified in the FROM clause. For a list of aggregate functions, see <<aggregate_functions,Aggregate (Set) Functions>>.
* If _col-expr_ is a single column name, that column of the SELECT result table is a named column. All other columns are
unnamed columns in the result table (and have the (EXPR) heading) unless you use the AS clause to specify a name for a
derived column.
[[select_considerations_for_group_by]]
==== Considerations for GROUP BY
* If you include a GROUP BY clause, the columns you refer to in the _select-list_ must be either grouping columns or
arguments of an aggregate (or set) function. For example, if AGE is not a grouping column, you can refer to AGE only
as the argument of a function, such as AVG (AGE).
* The expression in the GROUP BY clause must be exactly the same as the expression in the select list. An error will
be returned if it is not. It cannot contain aggregate functions or subqueries.
* If the value of _col-expr_ is a numeric constant, it refers to the position of the select list item and is treated
as the current GROUP BY using the ordinal feature.
* You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list. For example,
GROUP BY 3, 2, 1.
* If you do not include a GROUP BY clause but you specify an aggregate function in the _select-list_, all rows of the
result table form the one and only group. The result of AVG, for example, is a single value for the entire table.
[[select_considerations_for_order_by]]
==== Considerations for ORDER BY
When you specify an ORDER BY clause and its ordering columns, consider:
* ORDER BY is allowed only in the outer level of a query or in the SELECT part of an INSERT/SELECT statement. It is not
allowed inside nested SELECT expressions, such as subqueries.
* If you specify DISTINCT, the ordering column must be in _select-list_.
* If you specify a GROUP BY clause, the ordering column must also be a grouping column.
* If an ORDER BY clause applies to a union of SELECT statements, the ordering column must be explicitly referenced, and
not within an aggregate function or an expression, in the _select-list_ of the leftmost SELECT statement.
* SQL does not guarantee a specific or consistent order of rows unless you specify an ORDER BY clause. ORDER BY can
reduce performance, however, so use it only if you require a specific order.
==== Considerations for UNION
Suppose that the contributing SELECT statements are named SELECT1 and SELECT2, the contributing tables resulting from
the SELECT statements are named TABLE1 and TABLE2, and the table resulting from the UNION operation is named RESULT.
[[select_characteristics_of_the_union_columns]]
===== Characteristics of the UNION Columns
For columns in TABLE1 and TABLE2 that contribute to the RESULT table:
* If both columns contain character strings, the corresponding column in RESULT contains a character string whose length
is equal to the greater of the two contributing columns.
* If both columns contain variable-length character strings, RESULT contains a variable-length character string whose
length is equal to the greater of the two contributing columns.
* If both columns are of exact numeric data types, RESULT contains an exact numeric value whose precision and scale are
equal to the greater of the two contributing columns.
* If both columns are of approximate numeric data types, RESULT contains an approximate numeric value whose precision is
equal to the greater of the two contributing columns.
* If both columns are of datetime data type (DATE, TIME, or TIMESTAMP), the corresponding column in RESULT has the same data type.
* If both columns are INTERVAL data type and both columns are year-month or day-time, RESULT contains an INTERVAL value
whose range of fields is the most significant start field to the least significant end field of the INTERVAL fields in the
contributing columns. (The year-month fields are YEAR and MONTH. The day-time fields are DAY, HOUR, MINUTE, and SECOND.)
+
For example, suppose that the column in TABLE1 has the data type INTERVAL HOUR TO MINUTE, and the column in TABLE2 has the
data type INTERVAL DAY TO HOUR. The data type of the column resulting from the union operation is INTERVAL DAY TO MINUTE.
* If both columns are described with NOT NULL, the corresponding column of RESULT cannot be null. Otherwise, the column can be null.
[[select_order_by_clause_and_the_union_operator]]
===== ORDER BY Clause and the UNION Operator
In a query containing a UNION operator, the ORDER BY clause defines an ordering on the result of the union. In this case,
the SELECT statement cannot have an individual ORDER BY clause.
You can specify an ORDER BY clause only as the last clause following the final SELECT statement (SELECT2 in this example).
The ORDER BY clause in RESULT specifies the ordinal position of the sort column either by using an integer or by using the
column name from the select list of SELECT1.
* This SELECT statement shows correct use of the ORDER BY clause:
+
```
SELECT A FROM T1 UNION SELECT B FROM T2 ORDER BY A
```
* This SELECT statement is incorrect because the ORDER BY clause does not follow the final SELECT statement:
+
```
SELECT A FROM T1 ORDER BY A UNION SELECT B FROM T2
```
<<<
* This SELECT statement is also incorrect:
+
```
SELECT A FROM T1 UNION (SELECT B FROM T2 ORDER BY A)
```
+
Because the subquery (SELECT B FROM T2&#8230;) is processed first, the ORDER BY clause does not follow the final SELECT.
[[select_group_by_clause_having_clause_and_the_union_operator]]
===== GROUP BY Clause, HAVING Clause, and the UNION Operator
In a query containing a UNION operator, the GROUP BY or HAVING clause is associated with the SELECT statement it is a
part of (unlike the ORDER BY clause, which can be associated with the result of a union operation). The groups are visible
in the result table of the particular SELECT statement. The GROUP BY and HAVING clauses cannot be used to form groups in
the result of a union operation.
[[select_union_all_and_associativity]]
===== UNION ALL and Associativity
The UNION ALL operation is left associative, meaning that these two queries return the same result:
```
(SELECT * FROM TABLE1 UNION ALL
SELECT * FROM TABLE2) UNION ALL SELECT * FROM TABLE3;
SELECT * FROM TABLE1 UNION ALL
(SELECT * FROM TABLE2 UNION ALL SELECT * FROM TABLE3);
```
If both the UNION ALL and UNION operators are present in the query, the order of evaluation is always from left to right.
A parenthesized union of SELECT statements is evaluated first, from left to right, followed by the remaining union of SELECT statements.
<<<
[[select_examples]]
=== Examples of SELECT
* The following example defines two CTEs, w1 and w2. w2 references w1 which is defined before w2.
```
>>select * from t1
C1 C2
----------
1 1
2 2
3 3
4 4
5 5
--- SQL operation complete.
>>with w1 as (select * from t1),
>>w2 as (select * from w1)
>>select * from w2;
C1 C2
----------
1 1
2 2
3 3
4 4
5 5
--- SQL operation complete.
```
* The following example defines two CTEs, w1 and w2, and then perform a JOIN between them.
```
>>select * from t1
C1 C2
----------
1 1
2 2
3 3
4 4
5 5
--- SQL operation complete.
>>select * from t2
C1 C2
----------
3 3
4 4
5 5
6 6
7 7
--- SQL operation complete.
>>with w1 as (select c1, c2 from t1),
>>w2 as (select c1, c2 from t2)
>>select * from w1, w2 where w1.c1 = w2.c1;
C1 C2 C1 C2
----------
3 3 3 3
4 4 4 4
5 5 5 5
--- SQL operation complete.
```
* Retrieve information from the EMPLOYEE table for employees with a job code greater than 500 and who are in departments
with numbers less than or equal to 3000, displaying the results in ascending order by job code:
+
```
SELECT jobcode, deptnum, first_name, last_name, salary
FROM persnl.employee
WHERE jobcode > 500
AND deptnum <= 3000
ORDER BY jobcode;
DEPTNUM FIRST_NAME LAST_NAME SALARY
------- --------------- ----------- ----------
1500 JONATHAN MITCHELL 32000.00
1500 JIMMY SCHNEIDER 26000.00
2500 MIRIAM KING 18000.00
1000 SUE CRAMER 19000.00
. . .
```
* Display selected rows grouped by job code in ascending order:
+
```
SELECT jobcode, AVG(salary)
FROM persnl.employee
WHERE jobcode > 500
AND deptnum <= 3000
GROUP BY jobcode
ORDER BY jobcode;
JOBCODE EXPR
------- ----------------------
600 29000.00
900 25100.00
--- 2 row(s) selected.
```
+
This select list contains only grouping columns and aggregate functions. Each row of the output summarizes the
selected data within one group.
<<<
* Select data from more than one table by specifying the table names in the FROM clause and specifying the
condition for selecting rows of the result in the WHERE clause:
+
```
SELECT jobdesc, first_name, last_name, salary
FROM persnl.employee E, persnl.job J
WHERE E.jobcode = J.jobcode
AND E.jobcode IN (900, 300, 420);
JOBDESC FIRST_NAME LAST_NAME SALARY
------------ ------------ --------------- -----------
SALESREP TIM WALKER 32000.00
SALESREP HERBERT KARAJAN 29000.00
...
ENGINEER MARK FOLEY 33000.00
ENGINEER MARIA JOSEF 18000.10
...
SECRETARY BILL WINN 32000.00
SECRETARY DINAH CLARK 37000.00
...
--- 27 row(s) selected.
```
+
This type of condition is sometimes called a join predicate. The query first joins the EMPLOYEE and JOB tables by
combining each row of the EMPLOYEE table with each row of the JOB table; the intermediate result is the Cartesian
product of the two tables.
+
This join predicate specifies that any row (in the intermediate result) with equal job codes is included in the
result table. The WHERE condition further specifies that the job code must be 900, 300, or 420. All other rows
are eliminated.
+
The four logical steps that determine the intermediate and final results of the previous query are:
+
1. Join the tables.
+
[cols="5*",options="header"]
|===
3+| EMPLOYEE Table 2+| JOB Table
| *EMPNUM &#8230;* | *JOBCODE &#8230;* | *SALARY* | *JOBCODE* | *JOBDESC*
|===
+
<<<
+
2. Drop rows with unequal job codes.
+
[cols="5*",options="header"]
|===
3+| EMPLOYEE Table 2+| JOB Table
| *EMPNUM &#8230;* | *JOBCODE &#8230;* | *SALARY* | *JOBCODE* | *JOBDESC*
| 1 | 100 | 175500 | 100 | MANAGER
| . . . | . . . | . . . | . . . | . . .
| 75 | 300 | 32000 | 300 | SALESREP
| . . . | . . . | . . . | . . . | . . .
| 178 | 900 | 28000 | 900 | SECRETARY
| . . . | . . . | . . . | . . . | . . .
| 207 | 420 | 33000 | 420 | ENGINEER
| . . . | . . . | . . . | . . . | . . .
| 568 | 300 | 39500 | 300 | SALESREP
|===
+
3. Drop rows with job codes not equal to 900, 300, or 420.
+
[cols="5*",options="header"]
|===
3+| EMPLOYEE Table 2+| JOB Table
| *EMPNUM &#8230;* | *JOBCODE &#8230;* | *SALARY* | *JOBCODE* | *JOBDESC*
| 75 | 300 | 32000 | 300 | SALESREP
| . . . | . . . | . . . | . . . | . . .
| 178 | 900 | 28000 | 900 | SECRETARY
| . . . | . . . | . . . | . . . | . . .
| 207 | 420 | 33000 | 420 | ENGINEER
| . . . | . . . | . . . | . . . | . . .
| 568 | 300 | 39500 | 300 | SALESREP
|===
+
4. Process the select list, leaving only four columns.
+
[cols="4*",options="header"]
|===
| JOBDESC | FIRST_NAME | LAST_NAME | SALARY
| SALESREP | TIM | WALKER | 32000
| . . . | . . . | . . . | . . .
| SECRETARY | JOHN | CHOU | 28000
| . . . | . . . | . . . | . . .
| ENGINEER | MARK | FOLEY | 33000
| . . . | . . . | . . . | . . .
| SALESREP | JESSICA | CRINER | 39500
|===
+
The final result is shown in the output:
+
```
JOBDESC FIRST_NAME LAST_NAME SALARY
------------ ------------ --------------- -----------
SALESREP TIM WALKER 32000.00
...
SECRETARY JOHN CHOU 28000.00
...
```
<<<
* Select from three tables, group the rows by job code and (within job code) by department number, and order the
groups by the maximum salary of each group:
+
```
SELECT E.jobcode, E.deptnum, MIN (salary), MAX (salary)
FROM persnl.employee E, persnl.dept D, persnl.job J
WHERE E.deptnum = D.deptnum
AND E.jobcode = J.jobcode
AND E.jobcode IN (900, 300, 420)
GROUP BY E.jobcode, E.deptnum
ORDER BY 4;
JOBCODE DEPTNUM (EXPR) (EXPR)
------- ------- ----------- -----------
900 1500 17000.00 17000.00
900 2500 18000.00 18000.00
...
300 3000 19000.00 32000.00
900 2000 32000.00 32000.00
...
300 3200 22000.00 33000.10
420 4000 18000.10 36000.00
...
--- 16 row(s) selected.
```
+
Only job codes 300, 420, and 900 are selected. The minimum and maximum salary for the same job in each department
are computed, and the rows are ordered by maximum salary.
* Select from two tables that have been joined by using an INNER JOIN on matching part numbers:
+
```
SELECT OD.*, P.*
FROM sales.odetail OD
INNER JOIN sales.parts P ON OD.partnum = P.partnum;
Order/Num Part/Num Unit/Price Qty/Ord Part/Num Part Description PRICE Qty/Avail
---------- -------- ------------ ---------- -------- ------------------ ------------ -----------
400410 212 2450.00 12 212 PCSILVER, 20 MB 2500.00 3525
500450 212 2500.00 8 212 PCSILVER, 20 MB 2500.00 3525
100210 244 3500.00 3 244 PCGOLD, 30 MB 3000.00 4426
800660 244 3000.00 6 244 PCGOLD, 30 MB 3000.00 4426
... ... ... ... ... ... ... ...
--- 72 row(s) selected.
```
<<<
* Select from three tables and display them in employee number order. Two tables are joined by using a LEFT JOIN on matching department
numbers, then an additional table is joined on matching job codes:
+
```
SELECT empnum, first_name, last_name, deptname, location, jobdesc
FROM employee e
LEFT JOIN dept d ON e.deptnum = d.deptnum
LEFT JOIN job j ON e.jobcode = j.jobcode
ORDER BY empnum;
```
* Suppose that the JOB_CORPORATE table has been created from the JOB table by using the CREATE LIKE statement.
Form the union of these two tables:
+
```
SELECT * FROM job UNION SELECT * FROM job_corporate;
JOBCODE JOBDESC
------- ------------------
100 MANAGER
200 PRODUCTION SUPV
250 ASSEMBLER
300 SALESREP
400 SYSTEM ANALYST
420 ENGINEER
450 PROGRAMMER
500 ACCOUNTANT
600 ADMINISTRATOR
900 SECRETARY
100 CORP MANAGER
300 CORP SALESREP
400 CORP SYSTEM ANALYS
500 CORP ACCOUNTANT
600 CORP ADMINISTRATOR
900 CORP SECRETARY
--- 16 row(s) selected.
```
<<<
* A FULL OUTER JOIN combines the results of both left and right outer joins. These joins show records from both tables
and fill in NULLs for missing matches on either side:
+
```
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
LastName DepartmentID DepartmentName DepartmentID
-------- ------------ -------------- ------------
Smith 34 Clerical 34
Jones 33 Engineering 33
Robinson 34 Clerical 34
Jasper 36 NULL NULL
Steinberg 33 Engineering 33
Rafferty 31 Sales 31
NULL NULL Marketing 35
```
* Present two ways to select the same data submitted by customers from California. The first way:
+
```
SELECT OD.ordernum, SUM (qty_ordered * price)
FROM sales.parts P, sales.odetail OD
WHERE OD.partnum = P.partnum AND OD.ordernum IN
( SELECT O.ordernum
FROM sales.orders O, sales.customer C
WHERE O.custnum = C.custnum AND state = 'CALIFORNIA'
)
GROUP BY OD.ordernum;
ORDERNUM (EXPR)
---------- ---------------------
200490 1030.00
300350 71025.00
300380 28560.00
--- 3 row(s) selected.
```
+
<<<
+
The second way:
+
```
SELECT OD.ordernum, SUM (qty_ordered * price)
FROM sales.parts P, sales.odetail OD
WHERE OD.partnum = P.partnum AND OD.ordernum IN
( SELECT O.ordernum
FROM sales.orders O WHERE custnum IN
( SELECT custnum
FROM sales.customer
WHERE state = 'CALIFORNIA'
)
)
GROUP BY OD.ordernum;
ORDERNUM (EXPR)
---------- ---------------------
200490 1030.00
300350 71025.00
300380 28560.00
--- 3 row(s) selected.
```
+
The price for the total quantity ordered is computed for each order number.
* Show employees, their salaries, and the percentage of the total payroll that their salaries represent.
Note the subquery as part of the expression in the select list:
+
```
SELECT
empnum
, first_name
, last_name
, salary
, CAST(salary * 100 / ( SELECT SUM(salary) FROM persnl.employee) AS NUMERIC(4,2))
FROM persnl.employee ORDER BY salary, empnum;
Employee/Number First Name Last Name salary (EXPR)
--------------- --------------- -------------------- ----------- -------
209 SUSAN CHAPMAN 17000.00 .61
235 MIRIAM KING 18000.00 .65
224 MARIA JOSEF 18000.10 .65
...
23 JERRY HOWARD 137000.10 4.94
32 THOMAS RUDLOFF 138000.40 4.98
1 ROGER GREEN 175500.00 6.33
...
--- 62 row(s) selected.
```
<<<
* Examples of using expressions in the GROUP BY clause:
+
```
SELECT a+1 FROM t GROUP BY a+1;
SELECT cast(a AS int) FROM t GROUP BY cast(a AS int);
SELECT a+1 FROM t GROUP BY 1;
```
* Examples of unsupported expressions in the GROUP BY clause:
+
```
SELECT sum(a) FROM t GROUP BY sum(a);
SELECT (SELECT a FROM t1) FROM t GROUP BY (SELECT a FROM t1);
SELECT a+1 FROM t GROUP BY 1+a;
```
* Examples of using `[LAST _N_]` option:
+
```
SQL>SELECT [LAST 0] * FROM employee;
--- 0 row(s) selected.
```
+
```
SQL>SELECT [LAST 1] * FROM employee WHERE jobcode <> 100 AND salary = 30000;
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE SALARY
------ --------------- -------------------- ------- ------- ----------
227 XAVIER SEDLEMEYER 3300 300 30000.00
--- 1 row(s) selected.
```
+
```
SQL>SELECT [LAST 2] * FROM employee;
*** ERROR[15002] Internal parser error: Number of rows must be 0 or 1 with LAST option.
. [2018-02-28 18:05:12]
```
<<<
[[set_schema_statement]]
== SET SCHEMA Statement
The SET SCHEMA statement sets the default logical schema for unqualified object names for the current SQL session.
```
SET SCHEMA default-schema-name
```
[[set_schema_syntax]]
=== Syntax Description of SET SCHEMA
* `_default-schema-name_`
+
specifies the name of a schema. See <<schemas,Schemas>>.
+
_default-schema-name_ is an SQL identifier. For example, you can use
MYSCHEMA or myschema or a delimited identifier "My_Schema". See <<identifiers,Identifiers>>.
[[set_schema_considerations]]
=== Considerations for SET SCHEMA
The default schema you specify with SET SCHEMA remains in effect until the end of the session or until you execute
another SET SCHEMA statement. If you do not set a schema name for the session using SET SCHEMA, the default schema
is SEABASE, which exists in the TRAFODION catalog.
For information on how to create a schema, see <<creating_and_dropping_schemas,Creating and Dropping Schemas>>.
[[set_schema_examples]]
=== Examples of SET SCHEMA
* Set the default schema name:
+
```
SET SCHEMA myschema;
```
<<<
[[set_transaction_statement]]
== SET TRANSACTION Statement
The SET TRANSACTION statement sets the autocommit attribute for transactions. It stays in effect until the end of
the session or until the next SET TRANSACTION statement, whichever comes first. Therefore, the SET TRANSACTION statement
can set the autocommit attribute of all subsequent transactions in the session.
```
SET TRANSACTION autocommit-option
autocommit-option is:
AUTOCOMMIT [ON] | AUTOCOMMIT OFF
```
[[set_transaction_syntax]]
=== Syntax Description of SET TRANSACTION
* `_autocommit-option_`
+
specifies whether {project-name} SQL commits or rolls back automatically at the end of statement execution. This option
applies to any statement for which the system initiates a transaction.
+
If this option is set to ON, {project-name} SQL automatically commits any changes or rolls back any changes made to the
database at the end of statement execution. AUTOCOMMIT is on by default at the start of a session.
+
If this option is set to OFF, the current transaction remains active until the end of the session unless you explicitly
commit or rollback the transaction. AUTOCOMMIT is a {project-name} SQL extension; you cannot use in it with any other option.
+
Using the AUTOCOMMIT option in a SET TRANSACTION statement does not reset other transaction attributes that may have
been specified in a previous SET TRANSACTION statement. Similarly, a SET TRANSACTION statement that does not specify
the AUTOCOMMIT attribute does not reset this attribute.
[[set_transaction_consideration]]
=== Considerations for SET TRANSACTION
[[set_transaction_implicit_transactions]]
==== Implicit Transactions
Most DML statements are transaction initiating—the system automatically initiates a transaction when the statement
begins executing.
<<<
The exceptions (statements that are not transaction initiating) are:
* COMMIT, FETCH, ROLLBACK, and SET TRANSACTION
* EXECUTE, which is transaction initiating only if the associated statement is transaction-initiating
[[set_transaction_explicit_transactions]]
==== Explicit Transactions
You can issue an explicit BEGIN WORK even if the autocommit option is on. The autocommit option is temporarily disabled
until you explicitly issue COMMIT or ROLLBACK.
[[set_transaction_examples]]
=== Examples of SET TRANSACTION
* The following SET TRANSACTION statement turns off autocommit so that the current transaction remains active until the
end of the session unless you explicitly commit or rollback the transaction. {project-name} SQL does not automatically commit
or roll back any changes made to the database at the end of statement execution. Instead, {project-name} SQL commits all the
changes when you issue the COMMIT WORK statement.
+
```
SET TRANSACTION AUTOCOMMIT OFF;
--- SQL operation complete.
BEGIN WORK;
--- SQL operation complete.
DELETE FROM persnl.employee WHERE empnum = 23;
--- 1 row(s) deleted.
INSERT INTO persnl.employee
(empnum, first_name, last_name, deptnum, salary)
VALUES (50, 'JERRY','HOWARD', 1000, 137000.00);
--- 1 row(s) inserted.
UPDATE persnl.dept SET manager = 50
WHERE deptnum = 1000;
--- 1 row(s) updated.
COMMIT WORK;
--- SQL operation complete.
```
<<<
[[showcontrol_statement]]
== SHOWCONTROL Statement
The SHOWCONTROL statement displays the default attributes in effect.
SHOWCONTROL is a {project-name} SQL extension.
```
SHOWCONTROL {ALL | [QUERY] DEFAULT [attribute-name[, MATCH {FULL | PARTIAL }]]}
```
[[showcontrol_syntax]]
=== Syntax Description of SHOWCONTROL
* `ALL`
+
displays all the hard-coded default attributes that have been set for the {project-name} instance.
* `[QUERY] DEFAULT`
+
displays the CONTROL QUERY DEFAULT statements in effect for the session. For more information,
see the <<control_query default_statement,Control Query Default Statement>>.
* `_attribute-name_[, MATCH {FULL | PARTIAL }]`
+
displays only the defaults that match, either fully or partially, the _attribute_ used in CONTROL QUERY DEFAULT
statements. The match is not case-sensitive. For descriptions of these attributes, see the
{docs-url}/cqd_reference/index.html[{project-name} Control Query Default (CQD) Reference Guide].
+
MATCH FULL specifies that _attribute-name_ must be the same as the attribute name used in a control query default
statement. match partial specifies that _attribute-name_ must be included in the attribute name used in a control
query default statement. the default is match partial.
+
If _attribute-name_ is a reserved word, such as MIN, MAX, or TIME, you must capitalize _attribute-name_ and delimit
it within double quotes ("). The only exceptions to this rule are the reserved words CATALOG and SCHEMA, which you
can either capitalize and delimit within double quotes or specify without quotation marks.
<<<
[[showcontrol_examples]]
=== Examples of SHOWCONTROL
* Issue multiple CONTROL QUERY DEFAULT statements followed by a SHOWCONTROL DEFAULT command:
+
```
CONTROL QUERY DEFAULT CACHE_HISTOGRAMS_REFRESH_INTERVAL '7200';
--- SQL operation complete.
CONTROL QUERY DEFAULT HIST_NO_STATS_REFRESH_INTERVAL '7200';
--- SQL operation complete.
SHOWCONTROL DEFAULT;
CONTROL QUERY DEFAULT
CACHE_HISTOGRAMS_REFRESH_INTERVAL 7200
HIST_NO_STATS_REFRESH_INTERVAL 7200
--- SQL operation complete.
```
<<<
[[showddl_statement]]
== SHOWDDL Statement
The SHOWDDL statement describes the DDL syntax used to create an object as it exists in the metadata, or it returns
a description of a user, role, or component in the form of a GRANT statement.
SHOWDDL is a {project-name} SQL extension.
```
SHOWDDL showddl-spec
showddl-spec
[TABLE | LIBRARY | PROCEDURE] [schema-name.]object-name[, PRIVILEGES ]
| COMPONENT component-name
| USER database-username
| ROLE role-name[, GRANTEES ]
```
[[showddl_syntax]]
=== Syntax Description of SHOWDDL
* `[_schema-name_.]_object-name_`
+
specifies the ANSI name of an existing table, view, library, or procedure.
See <<database_object_names,Database Object Names>>. If _object-name_ is not fully qualified, SHOWDDL
uses the default schema for the session.
* `PRIVILEGES`
+
describes the PRIVILEGES associated with the object. If specified, privileges are displayed for an object
in the form of GRANT statements.
* `_component-name_`
+
specifies an existing component. Currently, the only valid component name is SQL_OPERATIONS.
* `_database-username_`
+
specifies a registered database username.
* `_role-name_`
+
specifies an existing role.
* `GRANTEES`
+
displays all users who have been granted the role in the form of GRANT ROLE statements. This is an optional clause.
[[showddl_considerations]]
=== Considerations for SHOWDDL
* SHOWDDL can differ from the original DDL used to create an object.
* SHOWDDL can be used within TrafCI.
* SHOWDDL [TABLE \| LIBRARY \| PROCEDURE] displays the following information:
** A constraint may be disabled.
** A table may be off line.
** An active DDL lock may exist on an object.
* SHOWDDL USER displays user information as a REGISTER USER statement.
* SHOWDDL ROLE displays the role information as a CREATE ROLE statement.
[[showddl_required_privileges]]
==== Required Privileges
To issue a SHOWDDL statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the database object.
* You have the SHOW component privilege for the SQL_OPERATIONS component.
The SHOW component privilege is granted to PUBLIC by default.
* You have the SELECT privilege on the target object.
[[showddl_differences_between_showddl_output_and_original_ddl]]
==== Differences Between SHOWDDL Output and Original DDL
* All column constraints (NOT NULL, PRIMARY KEY, and CHECK) are transformed into table constraints.
All NOT NULL constraints are consolidated into a single check constraint.
* Check constraints are moved out of the CREATE TABLE statement and encapsulated in a separate ALTER TABLE ADD CONSTRAINT
statement.
* SHOWDDL generates ALTER TABLE ADD COLUMN statements for each column that was added to the table.
* All ANSI names in the output are qualified with the schema name.
* SHOWDDL displays constraint names even though they might not have been specified during the creation of the constraint.
* SHOWDDL always generates a Java signature for the SPJ.
[[showddl_privileges_option]]
==== PRIVILEGES Option
The PRIVILEGES option includes the GRANT statements as they apply to the option. Each privilege is specified in
separate GRANT statements even if they were granted in a single statement.
[[showddl_examples]]
=== Examples of SHOWDDL
* This SHOWDDL statement displays the statement that created the specified table in the database and the privileges
granted on that table:
+
```
SQL>showddl tab41;
CREATE TABLE TRAFODION.SCH41.TAB41 (
A INT DEFAULT NULL
, B INT DEFAULT NULL
)
;
-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION."SCH41"."TAB41" TO PAULLOW41 WITH GRANT OPTION;
--- SQL operation complete.
```
* This SHOWDDL statement displays the statement that registered the specified user in the database:
+
```
SQL>showddl user sqluser_admin;
REGISTER USER "SQLUSER_ADMIN";
--- SQL operation complete.
```
* This SHOWDDL statement displays the statement that created the specified role in the database and the users who
have been granted this role:
+
```
SQL>showddl role db rootrole;
CREATE ROLE "DB ROOTROLE";
SHOWDDL Statement 161
-- GRANT ROLE "DB ROOTROLE" TO "DB ROOT" WITH ADMIN OPTION;
--- SQL operation complete.
```
<<<
[[showddl_schema_statement]]
== SHOWDDL SCHEMA Statement
The SHOWDDL SCHEMA statement displays the DDL syntax used to create a schema as it exists in the metadata and shows
the authorization ID that owns the schema.
SHOWDDL SCHEMA is a {project-name} SQL extension.
```
SHOWDDL SCHEMA [catalog-name.]schema-name
```
[[showddl_schema_syntax]]
=== Syntax Description for SHOWDDL SCHEMA
* `[_catalog-name_.]_schema-name_`
specifies the ANSI name of an existing catalog and schema. If _schema-name_ is not fully qualified, SHOWDDL uses the default catalog
for the session, TRAFODION. For more information, see <<database_object_names,Database Object Names>>.
[[showddl_schema_considerations]]
=== Considerations for SHOWDDL SCHEMA
If not specified, the catalog is the current default catalog, TRAFODION.
[[showddl_schema_required_privileges]]
==== Required Privileges
To issue a SHOWDDL SCHEMA statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the schema.
* You have the SHOW component privilege for the SQL_OPERATIONS component. The SHOW component privilege is granted to PUBLIC
by default.
<<<
[[showddl_schema_examples]]
=== Examples of SHOWDDL SCHEMA
* This SHOWDDL SCHEMA statement displays the DDL syntax used to create the schema, MYSCHEMA, as it exists in the metadata
and shows the authorization ID that owns the schema:
+
```
SHOWDDL SCHEMA MYSCHEMA;
CREATE PRIVATE SCHEMA "TRAFODION"."MYSCHEMA" AUTHORIZATION "DB ROOT";
--- SQL operation complete.
```
<<<
[[]]
== SHOWSTATS Statement
The SHOWSTATS statement displays the histogram statistics for one or
more groups of columns within a table. These statistics are used to
devise optimized access plans.
SHOWSTATS is a {project-name} SQL extension.
```
SHOWSTATS FOR TABLE table-name ON group-list [DETAIL]
group-list is:
column-list[, column-list]...
| EVERY COLUMN[, column-list]...
| EVERY KEY[, column-list]...
| EXISTING COLUMN[S][, column-list]...
column-list for a single-column group is:
column-name
| (column-name)
| column-name TO column-name
| (column-name) TO (column-name)
| column-name TO (column-name)
| (column-name) TO column-name
column-list for a multicolumn group is:
(column-name, column-name[, column-name]...)
```
[[showstats_syntax]]
=== Syntax Description of SHOWSTATS
* `_table-name_`
+
is the ANSI name of the table for which statistics are to be displayed.
* `ON _group-list_`
+
specifies one or more groups of columns, _group-list_, for which to display histogram statistics.
* `_group-list_ is: _column-list_ [, _column-list_]&#8230; | EVERY COLUMN [,_column-list_]&#8230; | EVERY KEY [, _column-list_]&#8230; | EXISTING
COLUMN[S] [, _column-list_]&#8230;`
+
specifies the ways in which _group-list_ can be defined. The column list represents both a single-column group and a
multi-column group.
<<<
** `EVERY COLUMN`
+
indicates that histogram statistics are to be displayed for each individual column of _table_ and any multi columns that
make up the primary key and indexes. For columns that do not have histograms, this option returns
`No histogram data for column(s) ---&#93;`.
** `EVERY KEY`
+
indicates that histogram statistics are to be displayed for columns that make up the primary key and indexes.
** `EXISTING COLUMN[S]`
+
indicates that histogram statistics are to be displayed only for columns of _table_ that actually have histograms.
This option yields a more concise report because columns with no histogram data are omitted. This option includes
any existing multicolumn histograms.
** `DETAIL`
+
displays statistics for corresponding histogram intervals and other details.
+
If you do not select the DETAIL keyword, the default display lists the basic histogram information, including the
histogram ID, number of intervals, total rows, total UEC, and the column names. The detailed display additionally
includes the low value and high value as well as interval data.
* `_column-list_ for a single-column group is: _column-name_ |
(_column-name_) | _column-name_ TO _column-name_ | (_column-name_)
TO (_column-name_) | _column-name_ TO (_column-name_) |
(_column-name_) TO _column-name_`
+
specifies the ways in which the _column-name_ can be defined for single-column groups. A range of columns specified
using the TO keyword causes all columns in that range to be included, defined by their order of declaration in the table.
* `_column-list_ for a multicolumn group is: (_column-name, column-name_[,_column-name_]&#8230;)`
+
specifies the ways in which the _column-name_ can be defined for multicolumn groups. For example, (abc, def) indicates
the multicolumn histogram consisting of columns abc and def, not two single-column histograms.
+
For more information about the column list syntax and specifying columns, see the
<<update_statistics_statement,UPDATE STATISTICS Statement>>.
<<<
[[showstats_considerations]]
=== Considerations for SHOWSTATS
[[showstats_required_privileges]]
==== Required Privileges
To issue a SHOWSTATS statement, one of the following must be true:
* You are DB ROOT.
* You are the owner of the database object.
* You have the SHOW component privilege for the SQL_OPERATIONS component. The SHOW component privilege is granted
to PUBLIC by default.
* You have the SELECT privilege on the target object.
* You have the MANAGE_STATISTICS component privilege for the SQL_OPERATIONS component.
[[showstats_examples]]
=== Examples of SHOWSTATS
* This example displays histogram statistics for table A using the EVERY KEY keyword. In addition, the DETAIL
keyword is selected:
+
```
SHOWSTATS FOR TABLE A ON EVERY KEY DETAIL;
```
* This example displays statistics for table CAT.SCH.A and selects all columns from abc through def:
+
```
SHOWSTATS FOR TABLE CAT.SCH.A ON ABC TO DEF;
```
* This example displays statistics for table A. The list of column names contained within parenthesis refers
to a multicolumn group:
+
```
SHOWSTATS FOR TABLE A ON (ABC,DEF);
```
* This example displays statistics for table A using the EXISTING COLUMNS keyword. In addition, the DETAIL
keyword is selected:
+
```
SHOWSTATS FOR TABLE A ON EXISTING COLUMNS DETAIL;
```
+
<<<
+
Default output example:
+
```
>>SHOWSTATS FOR TABLE A ON EXISTING COLUMNS;
Histogram data for Table CAT.SCH.A Table ID: 341261536378386
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== ==============================
623327638 1 11 10 ABC, DEF, GHI
623327633 10 11 10 ABC
623327628 9 11 9 DEF
623327623 10 11 10 GHI
--- SQL operation complete.
>>SHOWSTATS FOR TABLE A ON ABC;
Histogram data for Table CAT.SCH.A Table ID: 341261536378386
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== ==============================
623327633 10 11 10 ABC
--- SQL operation complete.
>>SHOWSTATS FOR TABLE A ON DEF DETAIL;
Detailed Histogram data for Table CAT.SCH.A Table ID: 341261536378386
Hist ID: 623327628
Column(s): DEF
Total Rows: 11
Total UEC: 9
Low Value: (1)
High Value: (199)
Intervals: 9
Number Rowcount UEC Boundary
====== =========== ==============================
0 0 0 (1)
1 1 1 (1)
2 3 1 (2)
3 1 1 (4)
4 1 1 (11)
5 1 1 (12)
6 1 1 (14)
7 1 1 (99)
8 1 1 (123)
9 1 1 (199)
--- SQL operation complete.
```
<<<
[[table_statement]]
== TABLE Statement
The TABLE statement is equivalent to the query specification SELECT *
FROM _table_.
```
TABLE table
```
[[table_syntax]]
=== Syntax Description of Table Statement
* `_table_`
+
names the user table or view.
=== Considerations for TABLE
[[table_relationship_to_select_statement]]
==== Relationship to SELECT Statement
The result of the TABLE statement is one form of a simple-table, which refers to the definition of a table reference
within a SELECT statement. See the <<select_statement,SELECT Statement>>.
=== Examples of TABLE Statement
* This TABLE statement returns the same result as SELECT * FROM job:
+
```
TABLE job;
Job/Code Job Description
-------- --------------------
100 MANAGER
200 PRODUCTION SUPV
250 ASSEMBLER
300 SALESREP
400 SYSTEM ANALYST
420 ENGINEER
450 PROGRAMMER
500 ACCOUNTANT
600 ADMINISTRATOR
900 SECRETARY
--- 10 row(s) selected.
```
<<<
[[unregister_user_statement]]
== UNREGISTER USER Statement
The UNREGISTER USER statement removes a database username from the SQL
database. The user can no longer log on to the database.
UNREGISTER USER is a {project-name} SQL extension.
```
UNREGISTER USER database-username [RESTRICT | CASCADE]
```
[[unregister_user_syntax]]
=== Syntax Description of UNREGISTER USER
* `_database-username_`
+
is the name of a currently registered database user. _database-username_ is a regular or delimited case-insensitive
identifier. See <<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>.
* `[ RESTRICT | CASCADE ]`
+
If you specify RESTRICT, the UNREGISTER USER operation fails if there
are any objects or schemas in the database owned by the user or any
privileges or roles granted to the user.
+
If you specify CASCADE, all objects and schemas owned by the user are
dropped, and all privileges and roles granted to the user are revoked as
part of the UNREGISTER USER operation.
+
The default value is RESTRICT.
[[unregister_user_considerations]]
=== Considerations for UNREGISTER USER
* To unregister a user, you must have user administrative privileges.
You have user administrative privileges if you have been granted the
MANAGE_USERS component privilege. Initially,
+
DB ROOT is the only database user who has been granted the MANAGE_USERS
component privilege.
* You cannot unregister any username beginning with DB . Role names
beginning with DB are reserved by {project-name}.
* UNREGISTER USER fails if you specify RESTRICT (or nothing) and if the
user owns any objects or schemas or if the user has been granted any
privileges or roles.
[[unregister_user_examples]]
=== Examples of UNREGISTER USER
* To unregister a user:
+
```
UNREGISTER USER "jsmith@company.com";
```
<<<
[[update_statement]]
== UPDATE Statement
The UPDATE statement is a DML statement that updates data in a row or
rows in a table or updatable view. Updating rows in a view updates the
rows in the table on which the view is based.
```
Searched UPDATE is:
UPDATE table
{ set-clause-type1 | set-clause-type2 }
set-clause-type1 is:
SET set-clause[, set-clause ]..
set-clause is:
column-name = {expression | NULL}
set-clause-type2 is:
SET (column1, ..., columnN) = {(value1, ..., valueN) | (query-expr)}
[WHERE search-condition]
[[FOR] access-option ACCESS]
access-option is:
READ COMMITTED
```
[[update_syntax]]
=== Syntax Description of UPDATE
* `_table_`
+
names the user table or view to update. _table_ must be a base table or
an updatable view. To refer to a table or view, use the ANSI logical
name. See <<database_object_names,Database Object Names>>.
* `_set-clause-type1_`
+
This type of SET clause associates a value with a specific column in the
table being updated. For each _set-clause_, the value of the specified
target _column-name_ is replaced by the value of the update source
_expression_ (or NULL). The data type of each target column must be
compatible with the data type of its source value.
** `_column-name_`
+
names a column in _table_ to update. You cannot qualify or repeat a
column name. You cannot update the value of a column that is part of the
primary key.
** `_expression_`
+
is an SQL value expression that specifies a value for the column. The
_expression_ cannot contain an aggregate function defined on a column.
The data type of _expression_ must be compatible with the data type of
_column-name_.
+
If _expression_ refers to columns being updated, {project-name} SQL uses the
original values to evaluate the expression and determine the new value.
See <<expressions,Expressions>>.
** `NULL`
+
can also specify the value of the update source.
* `_set-clause-type2_`
+
This type of SET clause allows multiple columns to be specified on the
left side of the assignment operator. These columns are updated using
multiple values specified on the right side of the assignment operator.
The right side of the assignment operator could be simple values or a
subquery.
** `_column1_, &#8230;, _columnN_`
+
names columns in _table_ to update. You cannot qualify or repeat a
column name. You cannot update the value of a column that is part of the
primary key.
** `_value1_, &#8230;, _valueN_`
+
are values specified on the right side of the assignment operator for
the columns specified on the left side of the assignment operator. The
data type of each value must be compatible with the data type of the
corresponding column on the left side of the assignment operator.
* `_query-expr_`
+
is a SELECT subquery. Only one subquery can be specified on the right
side of a SET clause. The subquery cannot refer to the table being
updated. For the syntax and description of _query-expr_, see the
<<select_statement,SELECT Statement>>.
* `WHERE _search-condition_`
+
specifies a _search-condition_ that selects rows to update. Within the
_search-condition_, columns being compared are also being updated in
the table or view. See <<search_condition,Search Condition>>.
+
If you do not specify a _search-condition_, all rows in the table or
view are updated.
+
<<<
+
Do not use an UPDATE statement with a WHERE clause that contains a
SELECT for the same table. Reading from and inserting into, updating in,
or deleting from the same table generates an error. Use a positioned
(WHERE CURRENT OF) UPDATE instead. See <<merge_statement,MERGE Statement>>.
* `[FOR] _access-option_ ACCESS`
+
specifies the _access-option_ required for data used in the evaluation
of a search condition. See <<data_consistency_and_access_options,
Data Consistency and Access Options>>.
* `READ COMMITTED`
+
specifies that any data used in the evaluation of the search condition
must be from committed rows.
[[update_considerations]]
=== Considerations for UPDATE
[[update_performance]]
==== Performance
An UPDATE of primary key columns could perform poorly when compared to
an UPDATE of non-key columns. This is because the UPDATE operation
involves moving records in disk by deleting all the records in the
before-image and then inserting the records in the after-image back into
the table.
[[update_authorization_requirements]]
==== Authorization Requirements
UPDATE requires authority to read and write to the table or view being
updated and authority to read any table or view specified in subqueries
used in the search condition. A column of a view can be updated if its
underlying column in the base table can be updated.
[[update_transaction_initiation_and_termination]]
==== Transaction Initiation and Termination
The UPDATE statement automatically initiates a transaction if no active
transaction exists. Otherwise, you can explicitly initiate a transaction
with the BEGIN WORK statement. When a transaction is started, the SQL
statements execute within that transaction until a COMMIT or ROLLBACK is
encountered or an error occurs.
[[update_isolation_levels_of_transactions_and_access_options_of_statements]]
==== Isolation Levels of Transactions and Access Options of Statements
The isolation level of a {project-name} SQL transaction defines the degree to
which the operations on data within that transaction are affected by
operations of concurrent transactions. When you specify
access options for the DML statements within a transaction, you override
the isolation level of the containing transaction. Each statement then
executes with its individual access option.
[[update_conflicting_updates_in_concurrent_applications]]
==== Conflicting Updates in Concurrent Applications
If you are using the READ COMMITTED isolation level within a
transaction, your application can read different committed values for
the same data at different times. Further, two concurrent applications
can update (possibly in error) the same column in the same row.
[[update_requirements_for_data_in_row]]
==== Requirements for Data in Row
Each row to be updated must satisfy the constraints of the table or
underlying base table of the view. No column updates can occur unless
all of these constraints are satisfied. (A table constraint is satisfied
if the check condition is not false—that is, it is either true or has an
unknown value.)
In addition, a candidate row from a view created with the WITH CHECK
OPTION must satisfy the view selection criteria. The selection criteria
are specified in the WHERE clause of the AS _query-expr_ clause in the
CREATE VIEW statement.
[[update_reporting_of_updates]]
==== Reporting of Updates
When an UPDATE completes successfully, {project-name} SQL reports the number
of times rows were updated during the operation.
Under certain conditions, updating a table with indexes can cause
{project-name} SQL to update the same row more than once, causing the number
of reported updates to be higher than the actual number of changed rows.
However, both the data in the table and the number of reported updates
are correct. This behavior occurs when all of these conditions are true:
* The optimizer chooses an alternate index as the access path.
* The index columns specified in WHERE _search-condition_ are not changed by the update.
* Another column within the same index is updated to a higher value (if
that column is stored in ascending order), or a lower value (if that
column is stored in descending order).
When these conditions occur, the order of the index entries ensures that
{project-name} SQL will encounter the same row (satisfying the same
_search-condition)_ at a later time during the processing of the table.
The row is then updated again by using the same value or values.
For example, suppose that the index of MYTABLE consists of columns A and
B, and the UPDATE statement is specified:
```
UPDATE MYTABLE SET B = 20 WHERE A > 10;
```
If the contents of columns A and B are 1 and 12 respectively before the
UPDATE, after the UPDATE {project-name} SQL will encounter the same row
indexed by the values 1 and 20.
[[update_updating_character_values]]
==== Updating Character Values
For a fixed-length character column, an update value shorter than the
column length is padded with single-byte ASCII blanks (HEX20) to fill
the column. If the update value is longer than the column length, string
truncation of non blank trailing characters returns an error, and the
column is not updated.
For a variable-length character column, an update value is not padded;
its length is the length of the value specified. As is the case for
fixed length, if the update value is longer than the column length,
string truncation of non blank trailing characters returns an error, and
the column is not updated.
[[update_set_clause_restrictions_and_error_cases]]
==== SET Clause Restrictions and Error Cases
The SET clause has the following restrictions:
* The number of columns on the left side of each assignment operator
should match the number of values or SELECT list elements on the right
side. The following examples are not allowed:
+
```
UPDATE t SET (a,b)=(10,20,30)
UPDATE t set (b,c)=(SELECT r,t,s FROM x)
```
* If multi-column update syntax is specified and the right side contains
a subquery, only one element, the subquery, is not allowed.
+
```
UPDATE t SET (a,b)=(10, (SELECT a FROM t1))
```
* More than one subquery is not allowed if multiple-column syntax is
used.
+
```
UPDATE t SET (a,b)=(SELECT x,y FROM z), (c,d)=(SELECT x,y FROM a))
```
* If a subquery is used, it must return at most one row.
<<<
[[update_examples]]
=== Examples of UPDATE
* Update a single row of the ORDERS table that contains information
about order number 200300 and change the delivery date:
+
```
UPDATE sales.orders
SET deliv_date = DATE '2008-05-02'
WHERE ordernum = 200300;
```
* Update several rows of the CUSTOMER table:
+
```
UPDATE sales.customer SET credit = 'A1'
WHERE custnum IN (21, 3333, 324);
```
* Update all rows of the CUSTOMER table to the default credit 'C1':
+
```
UPDATE sales.customer SET credit = 'C1';
```
* Update the salary of each employee working for all departments located
in Chicago:
+
```
UPDATE persnl.employee
SET salary = salary * 1.1
WHERE deptnum IN
(SELECT deptnum FROM persnl.dept
WHERE location = 'CHICAGO');
```
+
The subquery is evaluated for each row of the DEPT table and returns
department numbers for departments located in Chicago.
* This is an example of a self-referencing UPDATE statement, where the
table being updated is scanned in a subquery:
+
```
UPDATE table3 SET b = b + 2000 WHERE a, b =
(SELECT a, b FROM table3 WHERE b > 200);
```
<<<
[[upsert_statement]]
== UPSERT Statement
The UPSERT statement either updates a table if the row exists or inserts
into a table if the row does not exist.
UPSERT is a {project-name} SQL extension.
```
UPSERT [USING LOAD] INTO table [(target-col-list)] {query-expr | values-clause}
target-col-list is:
column-name[, column-name]...
values-clause is:
VALUES ( expression[, expression]... )
```
[[upsert_syntax]]
=== Syntax Description of UPSERT
* `USING LOAD`
+
allows the UPSERT to occur without a transaction. Use this clause when
inserting data into an empty table. If you do not specify this clause,
the UPSERT occurs within a transaction.
* `_table_`
+
names the user table in which to insert or update rows. _table_ must be
a base table. (_target-col-list_)
+
names the columns in the table in which to insert or update values. The
data type of each target
+
column must be compatible with the data type of its corresponding source
value. Within the list, each target column must have the same position
as its associated source value, whose position is determined by the
columns in the table derived from the evaluation of the query expression
(_query-expr_).
+
If you do not specify all of the columns in the target _table_ in the
_target-col-list_, column default values are inserted into or updated
in the columns that do not appear in the list. See
<<column_default_settings,Column Default Settings>>.
+
If you do not specify _target-col-list_, row values from the source
table are inserted into or updated in all columns in table. The order of
the column values in the source table must be the same order as that of
the columns specified in the CREATE TABLE for _table_. (This order is
the same as that of the columns listed in the result table of SHOWDDL
_table_.)
* `_column-name_`
+
names a column in the target _table_ in which to either insert or update
data. You cannot qualify or repeat a column name.
* `_query-expr_`
+
is a SELECT subquery that returns data to be inserted into or updated in
the target _table_. The subquery cannot refer to the table being
operated on. For the syntax and description of _query-expr_, see the
<<select_statement,SELECT Statement>>.
* `VALUES ( _expression_[, _expression_]&#8230; )`
+
specifies an SQL value expression or a set of expressions that specify
values to be inserted into or updated in the target _table_. The data
type of _expression_ must be compatible with the data type of the
corresponding column in the target _table_. See
<<expressions,Expressions>>.
[[upsert_examples]]
=== Examples of UPSERT
* This UPSERT statement inserts 1,000,000 rows from _source_table_ into _target_table_.
+
Suppose that we have following tables:
+
_source_table_:
+
```
SQL>SELECT COUNT(*) FROM source_table;
(EXPR)
--------------------
1000000
--- 1 row(s) selected.
```
+
_target_table_:
+
```
SQL>CREATE TABLE target_table
  (
    ID                               INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , NUM                              INT DEFAULT NULL NOT SERIALIZED
  , CARD_ID                          LARGEINT DEFAULT NULL NOT SERIALIZED
  , PRICE                            DECIMAL(11, 3) DEFAULT NULL NOT SERIALIZED
  , START_DATE                       DATE DEFAULT NULL NOT SERIALIZED
  , START_TIME                       TIME(0) DEFAULT NULL NOT SERIALIZED
  , END_TIME                         TIMESTAMP(6) DEFAULT NULL NOT SERIALIZED
  , B_YEAR                           INTERVAL YEAR(10) DEFAULT NULL NOT
      SERIALIZED
  , B_YM                             INTERVAL YEAR(5) TO MONTH DEFAULT NULL NOT
      SERIALIZED
  , B_DS                             INTERVAL DAY(10) TO SECOND(3) DEFAULT NULL
      NOT SERIALIZED
  , PRIMARY KEY (ID ASC)
  )
  SALT USING 9 PARTITIONS
ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_1500000'
  HBASE_OPTIONS
  (
    MEMSTORE_FLUSH_SIZE = '1073741824'
  )
;
```
+
It takes approximately 10 seconds to finish loading 1,000,000 rows.
+
```
SQL>SET STATISTICS ON;
SQL>UPSERT USING LOAD INTO target_table SELECT * FROM source_table;
--- 1000000 row(s) inserted.
Start Time 2018/01/18 11:38:02.739433
End Time 2018/01/18 11:48:25.822903
Elapsed Time 00:10:23.083470
Compile Time 00:00:00.381337
Execution Time 00:10:22.700870
```
+
```
SQL>SELECT COUNT(*) FROM target_table;
(EXPR)
--------------------
             1000000
--- 1 row(s) selected.
```
* This UPSERT statement either inserts or updates the part number and
price in the PARTS table using the part number and unit price from the
ODETAIL table where the part number is 244:
+
```
UPSERT INTO sales.parts (partnum, price)
SELECT partnum, unit_price FROM sales.odetail WHERE partnum = 244;
```
* This UPSERT statement either inserts or updates rows in the EMPLOYEE
table using the results of querying the EMPLOYEE_EUROPE table:
+
```
UPSERT INTO persnl.employee SELECT * FROM persnl.employee_europe;
```
* This UPSERT statement either inserts or updates a row in the DEPT
table using the specified values:
+
```
UPSERT INTO persnl.dept
VALUES (3500,'CHINA SALES',111,3000,'HONG KONG');
```
* This UPSERT statement either inserts or updates a row in the DEPT
table using the specified values:
+
```
UPSERT INTO persnl.dept (deptnum, deptname, manager)
VALUES (3600,‘JAPAN SALES’, 996);
```
<<<
[[values_statement]]
== VALUES Statement
The VALUES statements starts with the VALUES keyword followed by a
sequence of row value constructors, each of which is enclosed in
parenthesis. It displays the results of the evaluation of the
expressions and the results of row subqueries within the row value
constructors.
```
VALUES (row-value-const) [, (row-value-const)]...
row-value-const is:
row-subquery
| {expression | NULL} [,{expression | NULL}...
```
[[values_syntax]]
=== Syntax Description of VALUES
* `_row-value-const_`
+
specifies a list of expressions (or NULL) or a row subquery (a subquery
that returns a single row of column values). An operand of an expression
cannot reference a column (except when the operand is a scalar subquery
returning a single column value in its result table).
+
The results of the evaluation of the expressions and the results of the
row subqueries in the row value constructors must have compatible data
types.
[[values_considerations]]
=== Considerations for VALUES
[[values_relationship_to_select_statement]]
==== Relationship to SELECT Statement
The result of the VALUES statement is one form of a _simple-table_,
which is part of the definition of a table reference within a SELECT
statement. See the <<select statement,SELECT Statement>>.
[[values_relationship_to_insert_statement]]
==== Relationship to INSERT Statement
For a VALUES clause that is the direct source of an INSERT statement,
{project-name} SQL also allows the keyword DEFAULT in a VALUES clause, just
like NULL is allowed. For more information, see the
<<insert statement,INSERT Statement>>.
<<<
[[values_examples]]
=== Examples of VALUES
* This VALUES statement displays two rows with simple constants:
+
```
VALUES (1,2,3), (4,5,6);
(EXPR) (EXPR) (EXPR)
------ ------ -----
1 2 3
4 5 6
--- 2 row(s) selected.
```
* This VALUES statement displays the results of the expressions and the
row subquery in the lists:
+
```
VALUES (1+2, 3+4), (5, (select count (*) from t));
(EXPR) (EXPR)
------ ----------------- ------
3 7
5 2
--- 2 row(s) selected.
```