You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sv...@apache.org on 2016/01/14 19:18:25 UTC
[21/42] incubator-trafodion git commit: Completed the SQL Statements
chapter.
Completed the SQL Statements chapter.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/d99ffe0a
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/d99ffe0a
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/d99ffe0a
Branch: refs/heads/master
Commit: d99ffe0a8eab850190ebc2a2a36b9016aef94d0c
Parents: 964e27f
Author: Gunnar Tapper <gt...@esgyn.local>
Authored: Mon Jan 4 22:24:11 2016 -0700
Committer: Gunnar Tapper <gt...@esgyn.local>
Committed: Mon Jan 4 22:24:11 2016 -0700
----------------------------------------------------------------------
.../src/asciidoc/_chapters/about.adoc | 176 +-
.../src/asciidoc/_chapters/introduction.adoc | 260 +-
.../src/asciidoc/_chapters/sql_statements.adoc | 6302 +++++++++++++++---
.../sql_reference/src/theme/trafodion-theme.yml | 4 +-
4 files changed, 5726 insertions(+), 1016 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d99ffe0a/docs/sql_reference/src/asciidoc/_chapters/about.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/about.adoc b/docs/sql_reference/src/asciidoc/_chapters/about.adoc
index ba91a95..2f5839a 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/about.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/about.adoc
@@ -51,20 +51,20 @@ This edition includes updates for these new features:
[cols="50%,50%",options="header"]
|===
| New Feature | Location in the Manual
-| On Line Analytical Process (OLAP) window functions | <<OLAP_Functions,OLAP Functions>>
+| On Line Analytical Process (OLAP) window functions | <<olap_functions,OLAP Functions>>
| Ability to cancel DDL, update statistics, and
additional child query operations in addition to
-DML statements | <<CONTROL_QUERY_CANCEL_Statement,CONTROL QUERY CANCEL Statement>>
+DML statements | <<control_query_cancel_statement,CONTROL QUERY CANCEL Statement>>
| Authorization required to run the CONTROL QUERY
-CANCEL Statement | <<CONTROL_QUERY_CANCEL_Statement,CONTROL QUERY CANCEL Statement>>
+CANCEL Statement | <<control_query_cancel_statement,CONTROL QUERY CANCEL Statement>>
| Ability to grant privileges on behalf of a role
-using the GRANTED BY clause. | <<GRANT_COMPONENT_PRIVILEGE_Statement,GRANT COMPONENT PRIVILEGE Statement>>
-| Authorization required for all SHOWDDL commands | <<SHOWDDL_Statement,SHOWDDL Statement>> +
+using the GRANTED BY clause. | <<grant_component_privilege_statement,GRANT COMPONENT PRIVILEGE Statement>>
+| Authorization required for all SHOWDDL commands | <<showddl_statement,SHOWDDL Statement>> +
<<SHOWDDL_SCHEMA_Statement,SHOWDDL SCHEMA Statement>>
| Ability to display the DDL syntax of a library object
-using the SHOWDDL LIBRARY command | <<SHOWDDL_Statement,SHOWDDL Statement>>
+using the SHOWDDL LIBRARY command | <<showddl_statement,SHOWDDL Statement>>
| Listing of HBase objects using the GET HBASE OBJECTS
-command through an SQL interface | <<GET_HBASE_OBJECTS_Statement,GET HBASE OBJECTS Statement>>
+command through an SQL interface | <<get_hbase_objects_statement,GET HBASE OBJECTS Statement>>
|===
<<<
@@ -97,63 +97,125 @@ Describes the Control Query Default (CQD) attributes that are supported in a Tra
== Notation Conventions
This list summarizes the notation conventions for syntax presentation in this manual.
-[cols="15%,30%,25%,30%",options="header"]
-|===
-| Notation | Usage | Example | Notes
-| UPPERCASE LETTERS | Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. | SELECT |
-| _Italic Letters_ | Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. | _file-name_ |
-| `Computer Type` | Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in
-brackets are required. | `myfile.sh` |
-| *Bold Text* | Bold text in an example indicates user input typed at the terminal. | `ENTER RUN CODE:` +
- ?**123** +
- `CODE RECEIVED: 123.00` | The user must press the Return key after typing the input.
-| [ ] Brackets | Brackets enclose optional syntax items. |
-DATETIME [__start-field__ TO] +
-_end-field_
-| A group of items enclosed in brackets is a list from which you can choose one item or none.
-
+* UPPERCASE LETTERS
++
+Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required.
++
+```
+SELECT
+```
+
+* lowercase letters
++
+Lowercase letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required.
++
+```
+file-name
+```
+
+* [ ] Brackets
++
+Brackets enclose optional syntax items.
++
+```
+DATETIME [start-field TO] end-field
+```
++
+A group of items enclosed in brackets is a list from which you can choose one item or none.
++
The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines.
++
+For example:
++
+```
+DROP SCHEMA schema [CASCADE]
+DROP SCHEMA schema [ CASCADE | RESTRICT ]
+```
-For example:
-
-`DROP SCHEMA _schema_ [CASCADE]` +
-`DROP SCHEMA _schema_ [ CASCADE \| RESTRICT ]`
-| { } Braces | Braces enclose required syntax items. | `FROM { __grantee__[, __grantee__]…}` | A group of items enclosed in braces is a list from which you are required to choose one item.
-
+<<<
+* { } Braces
++
+Braces enclose required syntax items.
++
+```
+FROM { grantee [, grantee ] ... }
+```
++
+A group of items enclosed in braces is a list from which you are required to choose one item.
++
The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
-
++
For example:
-
-`INTERVAL { _start-field_ TO _end-field_ }` +
-`{ _single-field_ }` +
-`INTERVAL { _start-field_ TO _end-field_ \| _single-field_ }`
-| \| Vertical Line | A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. | `{__expression__ \| NULL}` |
-| … Ellipsis | An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. |
-`ATTRIBUTE[S] _attribute_ [, __attribute__]…` +
-`{, __sql-expression__}…`
-| An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times.
-
++
+```
+INTERVAL { start-field TO end-field }
+{ single-field }
+INTERVAL { start-field TO end-field | single-field }
+```
+* | Vertical Line
++
+A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces.
+```
+{expression | NULL}
+```
+* … Ellipsis
++
+An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times.
++
+```
+ATTRIBUTE[S] attribute [, attribute] ...
+{, sql-expression } ...
+```
++
+An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times.
++
For example:
+```
+expression-n ...
+```
-`__expression-n__…`
-| Punctuation | Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. |
-DAY (__datetime-expression__)` +
-`@__script-file__` | Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown.
-
+<<<
+* Punctuation
++
+Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown.
++
+```
+DAY (datetime-expression)
+@script-file
+```
++
+Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown.
++
For example:
-
-`"{" _module-name_ [, __module-name__]… "}"`
-| Item Spacing | Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. |
-`DAY (__datetime-expression__) DAY(__datetime-expression__)` | If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items:
-
-`__myfile__.sh`
-
-| Line Spacing | If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line.
-
-This spacing distinguishes items in a continuation line from items in a vertical list of selections. |
-`_match-value_ [NOT] LIKE _pattern_`
- [ESCAPE __esc-char-expression__] |
-|===
++
+```
+"{" module-name [, module-name] ... "}"
+```
+
+* Item Spacing
++
+Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.
++
+```
+DAY (datetime-expression) DAY(datetime-expression)
+```
++
+If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items:
++
+```
+myfile.sh
+```
+
+* Line Spacing
++
+If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line.
++
+This spacing distinguishes items in a continuation line from items in a vertical list of selections.
++
+```
+match-value [NOT] LIKE _pattern
+ [ESCAPE esc-char-expression]
+```
<<<
== Publishing History
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d99ffe0a/docs/sql_reference/src/asciidoc/_chapters/introduction.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/introduction.adoc b/docs/sql_reference/src/asciidoc/_chapters/introduction.adoc
index 1120edc..82ae2aa 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/introduction.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/introduction.adoc
@@ -23,7 +23,7 @@
*/
////
-[[Introduction]]
+[[introduction]]
= Introduction
The Trafodion SQL database software allows you to use SQL statements, which comply closely to
@@ -32,57 +32,57 @@ native HBase tables and Hive tables.
This introduction describes:
-* <<SQL_Language,SQL Language>>
-* <<Using_Trafodion_SQL_to_Access_HBase_Tables,Using Trafodion SQL to Access HBase Tables>>
-* <<Using_Trafodion_SQL_to_Access_Hive_Tables,Using Trafodion SQL to Access Hive Tables>>
-* <<Data_Consistency_and_Access_Options,Data Consistency and Access Options>>
-* <<Transaction_Management,Transaction Management>>
-* <<ANSI_Compliance_and_Trafodion_SQL_Extensions,ANSI Compliance and Trafodion SQL Extensions>>
-* <<Trafodion_SQL_Error_Messages,Trafodion SQL Error Messages>>
+* <<sql_language,SQL Language>>
+* <<using_trafodion_sql_to_access_hbase_tables,Using Trafodion SQL to Access HBase Tables>>
+* <<using_trafodion_sql_to_access_hive_tables,Using Trafodion SQL to Access Hive Tables>>
+* <<data_consistency_and_access_options,Data Consistency and Access Options>>
+* <<transaction_management,Transaction Management>>
+* <<ansi_compliance_and_trafodion_sql_extensions,ANSI Compliance and Trafodion SQL Extensions>>
+* <<trafodion_sql_error_messages,Trafodion SQL Error Messages>>
Other sections of this manual describe the syntax and semantics of individual statements, commands, and language elements.
-[[SQL_Language]]
+[[sql_language]]
== SQL Language
The SQL language consists of statements and other language elements that you can use to access SQL
-databases. For descriptions of individual SQL statements, see <<"SQL_Statements","SQL Statements">>.
+databases. For descriptions of individual SQL statements, see <<"sql_statements","SQL Statements">>.
SQL language elements are part of statements and commands and include data types, expressions, functions,
identifiers, literals, and predicates. For more information, see:
-* <<SQL_Language,SQL Language>>
-* <<Elements,Elements>>
-* <<SQL_Clauses,SQL Clauses>>
+* <<sql_language,SQL Language>>
+* <<elements,Elements>>
+* <<sql_clauses,SQL Clauses>>
For information on specific functions and expressions, see:
-* <<SQL_Functions_and_Expressions,SQL Functions and Expressions>>
-* <<OLAP_Functions,OLAP Functions>>
+* <<sql_functions_and_expressions,SQL Functions and Expressions>>
+* <<olap_functions,OLAP Functions>>
<<<
-[[Using_Trafodion_SQL_to_Access_HBase_Tables]]
+[[using_trafodion_sql_to_access_hbase_tables]]
== Using Trafodion SQL to Access HBase Tables
You can use Trafodion SQL statements to read, update, and create HBase tables.
-* <<Initializing_the_Trafodion_Metadata,Initializing the Trafodion Metadata>>
-* <<Ways_to_Access_HBase_Tables,Ways to Access HBase Tables>>
-* <<Trafodion_SQL_Tables_Versus_Native_HBase_Tables,Trafodion SQL Tables Versus Native HBase Tables>>
-* <<Supported_SQL_Statements_With_HBase_Tables,Supported SQL Statements With HBase Tables>>
+* <<initializing_the_trafodion_metadata,Initializing the Trafodion Metadata>>
+* <<ways_to_access_hbase_tables,Ways to Access HBase Tables>>
+* <<trafodion_sql_tables_versus_native_hbase_tables,Trafodion SQL Tables Versus Native HBase Tables>>
+* <<supported_sql_statements_with_hbase_tables,Supported SQL Statements With HBase Tables>>
For a list of Control Query Default (CQD) settings for the HBase environment, see <<HBase_Environment_CQDs,HBase Environment CQDs>>.
-[[Ways_to_Access_HBase_Tables]]
+[[ways_to_access_hbase_tables]]
=== Ways to Access HBase Tables
Trafodion SQL supports these ways to access HBase tables:
-* <<Accessing_Trafodion_SQL_Tables,Accessing Trafodion SQL Tables>>
-* <<Cell_Per_Row_Access_to_HBase_Tables,Cell-Per-Row Access to HBase Tables (Technology Preview)>>
-* <<Rowwise_Access_to_HBase_Tables,Rowwise Access to HBase Tables (Technology Preview)>>
+* <<accessing_trafodion_sql_tables,Accessing Trafodion SQL Tables>>
+* <<cell_per_row_access_to_hbase_tables,Cell-Per-Row Access to HBase Tables (Technology Preview)>>
+* <<rowwise_access_to_hbase_tables,Rowwise Access to HBase Tables (Technology Preview)>>
<<<
-[[Accessing_Trafodion_SQL_Tables]]
+[[accessing_trafodion_sql_tables]]
==== Accessing Trafodion SQL Tables
A Trafodion SQL table is a relational SQL table generated by a `CREATE TABLE` statement and mapped
@@ -95,8 +95,7 @@ to how you would specify an ANSI table name when running SQL statements in a rel
*Example*
-[source,sql]
-----
+```
CREATE TABLE trafodion.sales.odetail
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL
, partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL
@@ -110,10 +109,10 @@ INSERT INTO trafodion.sales.odetail VALUES ( 900000, 7301, 425.00, 100 );
SET SCHEMA trafodion.sales;
SELECT * FROM odetail;
-----
+```
For more information about Trafodion SQL tables, see
-<<Trafodion_SQL_Tables_Versus_Native_HBase_Tables,Trafodion SQL Tables Versus Native HBase Tables>>.
+<<trafodion_sql_tables_versus_native_hbase_tables,Trafodion SQL Tables Versus Native HBase Tables>>.
<<<
[[cell_per_row_access_to_hbase_tables]]
@@ -128,16 +127,14 @@ it was created through Trafodion SQL.
*Example*
-[source,sql]
-----
+```
select * from hbase."_CELL_"."TRAFODION.MYSCH.MYTAB";
select * from hbase."_CELL_"."table_created_in_HBase";
-----
+```
All tables accessed through this schema have the same column layout:
-[source,sql]
-----
+```
>>invoke hbase."_CELL_"."table_created_in_HBase";
(
ROW_ID VARCHAR(100) ...
@@ -149,7 +146,7 @@ All tables accessed through this schema have the same column layout:
PRIMARY KEY (ROW_ID)
>>select * from hbase."_CELL_"."mytab";
-----
+```
<<<
[[rowwise_access_to_hbase_tables]]
@@ -164,16 +161,14 @@ it was created through Trafodion SQL.
*Example*
-[source,sql]
-----
+```
select * from hbase."_ROW_"."TRAFODION.MYSCH.MYTAB";
select * from hbase."_ROW_"."table_created_in_HBase";
-----
+```
All column values of the row are returned as a single, big varchar:
-[source,sql]
-----
+```
>>invoke hbase."_ROW_"."mytab";
(
ROW_ID VARCHAR(100) ...
@@ -182,10 +177,10 @@ All column values of the row are returned as a single, big varchar:
PRIMARY KEY (ROW_ID)
>>select * from hbase."_ROW_"."mytab";
-----
+```
<<<
-[[Trafodion_SQL_Tables_Versus_Native_HBase_Tables]]
+[[trafodion_sql_tables_versus_native_hbase_tables]]
=== Trafodion SQL Tables Versus Native HBase Tables
Trafodion SQL tables have many advantages over regular HBase tables:
@@ -196,41 +191,41 @@ Trafodion SQL tables have many advantages over regular HBase tables:
* They support indexes.
* They support _salting_, which is a technique of adding a hash value of the row key as a
key prefix to avoid hot spots for sequential keys. For the syntax,
-see the <<CREATE_TABLE_Statement,CREATE TABLE Statement>>.
+see the <<create_table_statement,CREATE TABLE Statement>>.
The problem with Trafodion SQL tables is that they use a fixed format to represent column values,
making it harder for native HBase applications to access them. Also, they have a fixed structure,
so users lose the flexibility of dynamic columns that comes with HBase.
-[[Supported_SQL_Statements_With_HBase_Tables]]
+[[supported_sql_statements_with_hbase_tables]]
=== Supported SQL Statements With HBase Tables
You can use these SQL statements with HBase tables:
|===
-| <<SELECT_Statement,SELECT Statement>> | <<INSERT_Statement,INSERT Statement>>
-| <<UPDATE_Statement,UPDATE Statement>> | <<DELETE_Statement,DELETE Statement>>
-| <<MERGE_Statement,MERGE Statement>> | <<GET_Statement,GET Statement>>
-| <<INVOKE_Statement,INVOKE Statement>> | <<ALTER_TABLE_Statement,ALTER TABLE Statement>>
-| <<CREATE_INDEX_Statement,CREATE INDEX Statement>> | <<CREATE_TABLE_Statement,CREATE TABLE Statement>>
-| <<CREATE_VIEW_Statement,CREATE VIEW Statement>> | <<DROP_INDEX_Statement,DROP INDEX Statement>>
-| <<DROP_TABLE_Statement,DROP TABLE Statement>> | <<DROP_VIEW_Statement,DROP VIEW Statement>>
-| <<GRANT_Statement,GRANT Statement>> | <<REVOKE_Statement,REVOKE Statement>>
+| <<select_statement,SELECT Statement>> | <<insert_statement,INSERT Statement>>
+| <<update_statement,UPDATE Statement>> | <<delete_statement,DELETE Statement>>
+| <<merge_statement,MERGE Statement>> | <<get_statement,GET Statement>>
+| <<invoke_statement,INVOKE Statement>> | <<alter_table_statement,ALTER TABLE Statement>>
+| <<create_index_statement,CREATE INDEX Statement>> | <<create_table_statement,CREATE TABLE Statement>>
+| <<create_view_statement,CREATE VIEW Statement>> | <<drop_index_statement,DROP INDEX Statement>>
+| <<drop_table_statement,DROP TABLE Statement>> | <<drop_view_statement,DROP VIEW Statement>>
+| <<grant_statement,GRANT Statement>> | <<revoke_statement,REVOKE Statement>>
|===
<<<
-[[Using_Trafodion_SQL_to_Access_Hive_Tables]]
+[[using_trafodion_sql_to_access_hive_tables]]
== Using Trafodion SQL to Access Hive Tables
You can use Trafodion SQL statements to access Hive tables.
-* <<ANSI_Names_for_Hive_Tables,ANSI Names for Hive Tables>>
-* <<Type_Mapping_From_Hive_to_Trafodion_SQL,Type Mapping From Hive to Trafodion SQL>>
-* <<Supported_SQL_Statements_With_Hive_Tables,Supported SQL Statements With Hive Tables>>
+* <<ansi_names_for_hive_tables,ANSI Names for Hive Tables>>
+* <<type_mapping_from_hive_to_trafodion_sql,Type Mapping From Hive to Trafodion SQL>>
+* <<supported_sql_statements_with_hive_tables,Supported SQL Statements With Hive Tables>>
For a list of Control Query Default (CQD) settings for the Hive environment, see <<Hive_Environment_CQDs,Hive Environment CQDs>>.
-[[ANSI_Names_for_Hive_Tables]]
+[[ansi_names_for_hive_tables]]
=== ANSI Names for Hive Tables
Hive tables appear in the Trafodion Hive ANSI namespace in a special catalog and schema named `HIVE.HIVE`.
@@ -241,8 +236,7 @@ in a Trafodion SQL statement.
*Example*
This example should work if a Hive table named `T` has already been defined:
-[source,sql]
-----
+```
set schema hive.hive;
cqd hive_max_string_length '20'; -- creates a more readable display
@@ -251,11 +245,11 @@ select * from t; -- implicit table name
set schema trafodion.seabase;
select * from hive.hive.t; -- explicit table name
-----
+```
<<<
-[[Type_Mapping_From_Hive_to_Trafodion_SQL]]
+[[type_mapping_from_hive_to_trafodion_sql]]
=== Type Mapping From Hive to Trafodion SQL
Trafodion performs the following data-type mappings:
@@ -273,21 +267,21 @@ Trafodion performs the following data-type mappings:
| `timestamp` | `timestamp(6)`^2^
|===
-1. The value `_n_` is determined by `CQD HIVE_MAX_STRING_LENGTH`. See <<Hive_Environment_CQDs,Hive Environment CQDs>>.
+1. The value `_n_` is determined by `CQD HIVE_MAX_STRING_LENGTH`. See <<hive_environment_cqds,Hive Environment CQDs>>.
2. Hive supports timestamps with nanosecond resolution (precision of 9). Tafodion SQL supports only microsecond resolution (precision 6).
-[[Supported_SQL_Statements_With_Hive_Tables]]
+[[supported_sql_statements_with_hive_tables]]
=== Supported SQL Statements With Hive Tables
You can use these SQL statements with Hive tables:
-* <<SELECT_Statement,SELECT Statement>>
-* <<LOAD_Statement,LOAD Statement>>
-* GET TABLES (See the <<GET_Statement,GET Statement>>.)
-* <<INVOKE_Statement,INVOKE Statement>>
+* <<select_statement,SELECT Statement>>
+* <<load_statement,LOAD Statement>>
+* GET TABLES (See the <<get_statement,GET Statement>>.)
+* <<invoke_statement,INVOKE Statement>>
<<<
-[[Data_Consistency_and_Access_Options]]
+[[data_consistency_and_access_options]]
== Data Consistency and Access Options
Access options for DML statements affect the consistency of the data that your query accesses.
@@ -298,16 +292,16 @@ tables and views referenced in the FROM clause.
The possible settings for `_option_` in a DML statement are:
-* <<READ_COMMITTED,READ COMMITTED>>
+* <<read_committed,READ COMMITTED>>
Specifies that the data accessed by the DML statement must be from committed rows.
The SQL default access option for DML statements is `READ COMMITTED`.
For related information about transactions, see
-<<Transaction_Isolation_Levels,Transaction Isolation Levels>>.
+<<transaction_isolation_levels,Transaction Isolation Levels>>.
-[[READ_COMMITTED]]
+[[read_committed]]
=== READ COMMITTED
This option allows you to access only committed data.
@@ -327,7 +321,7 @@ capability.
READ COMMITTED is the default isolation level.
<<<
-[[Transaction_Management]]
+[[transaction_management]]
== Transaction Management
A transaction (a set of database changes that must be completed as a group) is the basic recoverable unit
@@ -345,13 +339,13 @@ the transaction so that the database is rolled back to its original state.
This subsection discusses these considerations for transaction management:
-* <<User_Defined_and_System_Defined_Transactions,User-Defined and System-Defined Transactions>>
-* <<Rules_for_DML_Statements,Rules for DML Statements>>
-* <<Effect_of_AUTOCOMMIT_Option,Effect of AUTOCOMMIT Option>>
-* <<Concurrency,Concurrency>>
-* <<Transaction_Isolation_Levels,Transaction Isolation Levels>>
+* <<user_defined_and_system_defined_transactions,User-Defined and System-Defined Transactions>>
+* <<rules_for_dml_statements,Rules for DML Statements>>
+* <<effect_of_autocommit_option,Effect of AUTOCOMMIT Option>>
+* <<concurrency,Concurrency>>
+* <<transaction_isolation_levels,Transaction Isolation Levels>>
-[[User_Defined_and_System_Defined_Transactions]]
+[[user_defined_and_system_defined_transactions]]
=== User-Defined and System-Defined Transactions
Transactions you define are called _user-defined transactions_. To be sure that a sequence of statements executes
successfully or not at all, you can define one transaction consisting of these statements by using the BEGIN WORK
@@ -361,32 +355,33 @@ If AUTOCOMMIT is on, then you do not have to end the transaction explicitly as T
automatically. Sometimes an error occurs that requires the user-defined transaction to be aborted. Trafodion SQL
will automatically abort the transaction and return an error indicating that the transaction was rolled back.
+<<<
[[system_defined_transactions]]
==== System-Defined Transactions
In some cases, Trafodion SQL defines transactions for you. These transactions are called _system-defined transactions_.
Most DML statements initiate transactions implicitly at the start of execution.
-See <<Implicit_Transactions,Implicit Transactions>>.
+See <<implicit_transactions,Implicit Transactions>>.
However, even if a transaction is initiated implicitly, you must end a transaction explicitly with the COMMIT WORK
statement or the ROLLBACK WORK statement. If AUTOCOMMIT is on, you do not need to end a transaction explicitly.
-[[Rules_for_DML_Statements]]
+[[rules_for_dml_statements]]
=== Rules for DML Statements
If deadlock occurs, the DML statement times out and receives an error.
-[[Effect_of_AUTOCOMMIT_Option]]
+[[effect_of_autocommit_option]]
=== Effect of AUTOCOMMIT Option
AUTOCOMMIT is an option that can be set in a SET TRANSACTION statement. It specifies whether Trafodion SQL will commit
automatically, or roll back if an error occurs, at the end of statement execution. This option applies to any statement
-for which the system initiates a transaction. See <<SET_TRANSACTION_Statement,SET TRANSACTION Statement>>.
+for which the system initiates a transaction. See <<set_transaction_statement,SET TRANSACTION Statement>>.
If this option is set to ON, Trafodion SQL automatically commits any changes, or rolls back any changes, made to the
database at the end of statement execution.
-[[Concurrency]]
+[[concurrency]]
=== Concurrency
Concurrency is defined by two or more processes accessing the same data at the same time. The degree of concurrency
@@ -396,12 +391,13 @@ isolation level is READ COMMITTED.
Trafodion SQL provides concurrent database access for most operations and controls database access through concurrency
control and the mechanism for opening and closing tables. For DML operations, the access option affects the degree of
-concurrency. See <<Data_Consistency_and_Access_Optiones,Data Consistency and Access Options>>.
+concurrency. See <<data_consistency_and_access_options,Data Consistency and Access Options>>.
-[[Transaction_Isolation_Levels]]
+<<<
+[[transaction_isolation_levels]]
=== Transaction Isolation Levels
-A transaction has an isolation level that is <<READ_COMMITTED,READ COMMITTED>>.
+A transaction has an isolation level that is <<read_committed,READ COMMITTED>>.
[[read_committed]]
==== READ COMMITTED
@@ -417,7 +413,7 @@ READ COMMITTED provides sufficient consistency for any transaction that does not
The default isolation level is READ COMMITTED.
<<<
-[[ANSI_Compliance_and_Trafodion_SQL_Extensions]]
+[[ansi_compliance_and_trafodion_sql_extensions]]
== ANSI Compliance and Trafodion SQL Extensions
Trafodion SQL complies most closely with Core SQL 99. Trafodion SQL also includes some features from SQL 99 and part of
@@ -425,72 +421,72 @@ the SQL 2003 standard, and special Trafodion SQL extensions to the SQL language.
Statements and SQL elements in this manual are ANSI compliant unless specified as Trafodion SQL extensions.
-[[ANSI_Compliant_Statements]]
+[[ansi_compliant_statements]]
=== ANSI-Compliant Statements
These statements are ANSI compliant, but some might contain Trafodion SQL extensions:
|===
-| <<ALTER_TABLE_Statement,ALTER TABLE Statement>> | <<CALL_Statement,CALL Statement>>
-| <<COMMIT_WORK_Statement,COMMIT WORK Statement>> | <<CREATE_FUNCTION_Statement,CREATE FUNCTION Statement>>
-| <<CREATE_PROCEDURE_Statement,CREATE PROCEDURE Statement>> | <<CREATE_ROLE_Statement,CREATE ROLE Statement>>
-| <<CREATE_SCHEMA_Statement,CREATE SCHEMA Statement>> | <<CREATE_TABLE_Statement,CREATE TABLE Statement>>
-| <<CREATE_VIEW_Statement,CREATE VIEW Statement>> | <<DELETE_Statement,DELETE Statement>>
-| <<DROP_FUNCTION_Statement,DROP FUNCTION Statement>> | <<DROP_PROCEDURE_Statement,DROP PROCEDURE Statement>>
-| <<DROP_ROLE_Statement,DROP ROLE Statement>> | <<DROP_SCHEMA_Statement,DROP SCHEMA Statement>>
-| <<DROP_TABLE_Statement,DROP TABLE Statement>> | <<DROP_VIEW_Statement,DROP VIEW Statement>>
-| <<EXECUTE_Statement,EXECUTE Statement>> | <<GRANT_Statement,GRANT Statement>>
-| <<GRANT_ROLE_Statement,GRANT ROLE Statement>> | <<INSERT_Statement,INSERT Statement>>
-| <<MERGE_Statement,MERGE Statement>> | <<PREPARE_Statement,PREPARE Statement>>
-| <<REVOKE_Statement,REVOKE Statement>> | <<REVOKE_ROLE_Statement,REVOKE ROLE Statement>>
-| <<ROLLBACK_WORK_Statement,ROLLBACK WORK Statement>> | <<SELECT_Statement,SELECT Statement>>
-| <<SET_SCHEMA_Statement,SET SCHEMA Statement>> | <<SET_TRANSACTION_Statement,SET TRANSACTION Statement>>
-| <<TABLE_Statement,TABLE Statement>> | <<UPDATE_Statement,UPDATE Statement>>
-| <<VALUES_Statement,VALUES Statement>>
+| <<alter_table_statement,ALTER TABLE Statement>> | <<call_statement,CALL Statement>>
+| <<commit_work_statement,COMMIT WORK Statement>> | <<create_function_statement,CREATE FUNCTION Statement>>
+| <<create_procedure_statement,CREATE PROCEDURE Statement>> | <<create_role_statement,CREATE ROLE Statement>>
+| <<create_schema_statement,CREATE SCHEMA Statement>> | <<create_table_statement,CREATE TABLE Statement>>
+| <<create_view_statement,CREATE VIEW Statement>> | <<delete_statement,DELETE Statement>>
+| <<drop_function_statement,DROP FUNCTION Statement>> | <<drop_procedure_statement,DROP PROCEDURE Statement>>
+| <<drop_role_statement,DROP ROLE Statement>> | <<drop_schema_statement,DROP SCHEMA Statement>>
+| <<drop_table_statement,DROP TABLE Statement>> | <<drop_view_statement,DROP VIEW Statement>>
+| <<execute_statement,EXECUTE Statement>> | <<grant_statement,GRANT Statement>>
+| <<grant_role_statement,GRANT ROLE Statement>> | <<insert_statement,INSERT Statement>>
+| <<merge_statement,MERGE Statement>> | <<prepare_statement,PREPARE Statement>>
+| <<revoke_statement,REVOKE Statement>> | <<revoke_role_statement,REVOKE ROLE Statement>>
+| <<rollback_work_statement,ROLLBACK WORK Statement>> | <<select_statement,SELECT Statement>>
+| <<set_schema_statement,SET SCHEMA Statement>> | <<set_transaction_statement,SET TRANSACTION Statement>>
+| <<table_statement,TABLE Statement>> | <<update_statement,UPDATE Statement>>
+| <<values_statement,VALUES Statement>>
|===
<<<
-[[Statements_That_Are_Trafodion_SQL_Extensions]]
+[[statements_that_are_trafodion_sql_extensions]]
=== Statements That Are Trafodion SQL Extensions
These statements are Trafodion SQL extensions to the ANSI standard.
|===
-| <<ALTER_LIBRARY_Statement,ALTER LIBRARY Statement>> | <<ALTER_USER_Statement,ALTER USER Statement>>
-| <<BEGIN_WORK_Statement,BEGIN WORK Statement>> | <<CONTROL_QUERY_CANCEL_Statement,CONTROL QUERY CANCEL Statement>>
-| <<CONTROL_QUERY_DEFAULT_Statement,CONTROL QUERY DEFAULT Statement>> | <<CREATE_INDEX_Statement,CREATE INDEX Statement>>
-| <<CREATE_LIBRARY_Statement,CREATE LIBRARY Statement>> | <<DROP_INDEX_Statement,DROP INDEX Statement>>
-| <<DROP_LIBRARY_Statement,DROP LIBRARY Statement>> | <<EXPLAIN_Statement,EXPLAIN Statement>>
-| <<GET_Statement,GET Statement>> | <<GET_HBASE_OBJECTS_Statement,GET HBASE OBJECTS Statement>>
-| <<GET_VERSION_OF_METADATA_Statement,GET VERSION OF METADATA Statement>> | <<GET_VERSION_OF_SOFTWARE_Statement,GET VERSION OF SOFTWARE Statement>>
-| <<GRANT_COMPONENT_PRIVILEGE_Statement,GRANT COMPONENT PRIVILEGE Statement>> | <<INVOKE_Statement,INVOKE Statement>>
-| <<LOAD_Statement,LOAD Statement>> | <<REGISTER_USER_Statement,REGISTER USER Statement>>
-| <<REVOKE_COMPONENT_PRIVILEGE_Statement,REVOKE COMPONENT PRIVILEGE Statement>> | <<SHOWCONTROL_Statement,SHOWCONTROL Statement>>
-| <<SHOWDDL_Statement,SHOWDDL Statement>> | <<SHOWDDL_SCHEMA_Statement,SHOWDDL SCHEMA Statement>>
-| <<SHOWSTATS_Statement,SHOWSTATS Statement>> | <<UNLOAD_Statement,UNLOAD Statement>>
-| <<UNREGISTER_USER_Statement,UNREGISTER USER Statement>> | <<UPDATE_STATISTICS_Statement,UPDATE STATISTICS Statement>>
-| <<UPSERT_Statement,UPSERT Statement>>
+| <<alter_library_statement,ALTER LIBRARY Statement>> | <<alter_user_statement,ALTER USER Statement>>
+| <<begin_work_statement,BEGIN WORK Statement>> | <<control_query_cancel_statement,CONTROL QUERY CANCEL Statement>>
+| <<control_query_default_statement,CONTROL QUERY DEFAULT Statement>> | <<create_index_statement,CREATE INDEX Statement>>
+| <<create_library_statement,CREATE LIBRARY Statement>> | <<drop_index_statement,DROP INDEX Statement>>
+| <<drop_library_statement,DROP LIBRARY Statement>> | <<explain_statement,EXPLAIN Statement>>
+| <<get_statement,GET Statement>> | <<get_hbase_objects_statement,GET HBASE OBJECTS Statement>>
+| <<get_version_of_metadata_statement,GET VERSION OF METADATA Statement>> | <<get_version_of_software_statement,GET VERSION OF SOFTWARE Statement>>
+| <<grant_component_privilege_statement,GRANT COMPONENT PRIVILEGE Statement>> | <<invoke_statement,INVOKE Statement>>
+| <<load_statement,LOAD Statement>> | <<register_user_statement,REGISTER USER Statement>>
+| <<revoke_component_privilege_statement,REVOKE COMPONENT PRIVILEGE Statement>> | <<showcontrol_statement,SHOWCONTROL Statement>>
+| <<showddl_statement,SHOWDDL Statement>> | <<showddl_schema_statement,SHOWDDL SCHEMA Statement>>
+| <<showstats_statement,SHOWSTATS Statement>> | <<unload_statement,UNLOAD Statement>>
+| <<unregister_user_statement,UNREGISTER USER Statement>> | <<update_statistics_statement,UPDATE STATISTICS Statement>>
+| <<upsert_statement,UPSERT Statement>>
|===
<<<
-[[ANSI_Compliant_Functions]]
+[[ansi_compliant_functions]]
=== ANSI-Compliant Functions
These functions are ANSI compliant, but some might contain Trafodion SQL extensions:
|===
-| <<AVG,AVG function>> | <<CASE, CASE expression>>
-| <<CAST,CAST expression>> | <<CHAR_LENGTH,CHAR_LENGTH>>
-| <<COALESCE,COALESCE>> | <<COUNT,COUNT Function>>
-| <<CURRENT,CURRENT>> | <<CURRENT_DATE,CURRENT_DATE>>
-| <<CURRENT_TIME,CURRENT_TIME>> | <<CURRENT_TIMESTAMP,CURRENT_TIMESTAMP>>
-| <<CURRENT_USER,CURRENT_USER>> | <<EXTRACT,EXTRACT>>
-| <<LOWER,LOWER>> | <<MAX,MAX>>
-| <<MIN,MIN>> | <<NULLIF,NULLIF>>
-| <<OCTET_LENGTH,OCTET_LENGTH>> | <<POSITION,POSITION>>
-| <<SESSION_USER,SESSION_USER>> | <<SUBSTRING,SUBSTRING>>
-| <<SUM,SUM>> | <<TRIM,TRIM>>
-| <<UPPER,UPPER>>
+| <<avg,AVG function>> | <<case, CASE expression>>
+| <<cast,CAST expression>> | <<char_length,CHAR_LENGTH>>
+| <<coalesce,COALESCE>> | <<count,COUNT Function>>
+| <<current,CURRENT>> | <<current_date,CURRENT_DATE>>
+| <<current_time,CURRENT_TIME>> | <<current_timestamp,CURRENT_TIMESTAMP>>
+| <<current_user,CURRENT_USER>> | <<extract,EXTRACT>>
+| <<lower,LOWER>> | <<max,MAX>>
+| <<min,MIN>> | <<nullif,NULLIF>>
+| <<octet_length,OCTET_LENGTH>> | <<position,POSITION>>
+| <<session_user,SESSION_USER>> | <<substring,SUBSTRING>>
+| <<sum,SUM>> | <<trim,TRIM>>
+| <<upper,UPPER>>
|===
All other functions are Trafodion SQL extensions.