blob: 4f6c4fbfc05ffc2c0a23fa7dcdff8e2bf6319f4a [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]
[DETERMINISTIC | NOT DETERMINISTIC]
[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>>.
<<<
* `DETERMINISTIC | NOT DETERMINISTIC`
+
specifies whether the SPJ always returns the same values for out and inout parameters for a given set of argument values
(`DETERMINISTIC`) or does not return the same values (`NOT DETERMINISTIC`, the default option). If you specify `DETERMINISTIC`,
{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