http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/bf688877/docs/2.3.0/sql_reference/index.html ---------------------------------------------------------------------- diff --git a/docs/2.3.0/sql_reference/index.html b/docs/2.3.0/sql_reference/index.html index d14094a..719172e 100644 --- a/docs/2.3.0/sql_reference/index.html +++ b/docs/2.3.0/sql_reference/index.html @@ -5,7 +5,7 @@ <!--[if IE]><meta http-equiv="X-UA-Compatible" content="IE=edge"><![endif]--> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta name="generator" content="Asciidoctor 1.5.4"> -<title>LOB Guide</title> +<title>SQL Reference Manual</title> <style> /** * @@@ START COPYRIGHT @@@ @@ -535,7 +535,7 @@ table.CodeRay td.code>pre{padding:0} </head> <body class="book toc2 toc-left"> <div id="header"> -<h1>LOB Guide</h1> +<h1>SQL Reference Manual</h1> <div class="details"> <span id="revnumber">version 2.3.0</span> </div> @@ -553,1774 +553,43166 @@ table.CodeRay td.code>pre{padding:0} </li> <li><a href="#introduction">2. Introduction</a> <ul class="sectlevel2"> -<li><a href="#_what_is_a_lob">2.1. What is a LOB</a></li> -<li><a href="#_lob_data_types">2.2. LOB Data Types</a></li> -<li><a href="#_lob_storage">2.3. LOB Storage</a></li> -<li><a href="#_lob_handle">2.4. LOB Handle</a> +<li><a href="#sql_language">2.1. SQL Language</a></li> +<li><a href="#using_trafodion_sql_to_access_hbase_tables">2.2. Using Trafodion SQL to Access HBase Tables</a> <ul class="sectlevel3"> -<li><a href="#_external_structure">2.4.1. External Structure</a></li> +<li><a href="#ways_to_access_hbase_tables">2.2.1. Ways to Access HBase Tables</a></li> +<li><a href="#trafodion_sql_tables_versus_native_hbase_tables">2.2.2. Trafodion SQL Tables Versus Native HBase Tables</a></li> +<li><a href="#supported_sql_statements_with_hbase_tables">2.2.3. Supported SQL Statements With HBase Tables</a></li> </ul> </li> -<li><a href="#_lob_restrictions">2.5. LOB Restrictions</a></li> -<li><a href="#_lob_related_sql_statements_and_functions">2.6. LOB Related SQL Statements and Functions</a> +<li><a href="#using_trafodion_sql_to_access_hive_tables">2.3. Using Trafodion SQL to Access Hive Tables</a> <ul class="sectlevel3"> -<li><a href="#_supported_sql_statements">2.6.1. Supported SQL Statements</a></li> -<li><a href="#_unsupported_sql_statements">2.6.2. Unsupported SQL Statements</a></li> -<li><a href="#_supported_lob_conversion_sql_functions">2.6.3. Supported LOB Conversion SQL Functions</a></li> +<li><a href="#ansi_names_for_hive_tables">2.3.1. ANSI Names for Hive Tables</a></li> +<li><a href="#type_mapping_from_hive_to_trafodion_sql">2.3.2. Type Mapping From Hive to Trafodion SQL</a></li> +<li><a href="#supported_sql_statements_with_hive_tables">2.3.3. Supported SQL Statements With Hive Tables</a></li> </ul> </li> +<li><a href="#data_consistency_and_access_options">2.4. Data Consistency and Access Options</a> +<ul class="sectlevel3"> +<li><a href="#read_committed">2.4.1. READ COMMITTED</a></li> +</ul> +</li> +<li><a href="#transaction_management">2.5. Transaction Management</a> +<ul class="sectlevel3"> +<li><a href="#user_defined_and_system_defined_transactions">2.5.1. User-Defined and System-Defined Transactions</a></li> +<li><a href="#rules_for_dml_statements">2.5.2. Rules for DML Statements</a></li> +<li><a href="#effect_of_autocommit_option">2.5.3. Effect of AUTOCOMMIT Option</a></li> +<li><a href="#concurrency">2.5.4. Concurrency</a></li> +<li><a href="#transaction_isolation_levels">2.5.5. Transaction Isolation Levels</a></li> +</ul> +</li> +<li><a href="#ansi_compliance_and_trafodion_sql_extensions">2.6. ANSI Compliance and Trafodion SQL Extensions</a> +<ul class="sectlevel3"> +<li><a href="#ansi_compliant_statements">2.6.1. ANSI-Compliant Statements</a></li> +<li><a href="#statements_that_are_trafodion_sql_extensions">2.6.2. Statements That Are Trafodion SQL Extensions</a></li> +<li><a href="#ansi_compliant_functions">2.6.3. ANSI-Compliant Functions</a></li> +</ul> +</li> +<li><a href="#_trafodion_sql_error_messages">2.7. Trafodion SQL Error Messages</a></li> </ul> </li> -<li><a href="#_working_with_lobs">3. Working with LOBs</a> +<li><a href="#sql_statements">3. SQL Statements</a> <ul class="sectlevel2"> -<li><a href="#_creating_a_sql_table_with_lob_columns">3.1. Creating a SQL Table with LOB Columns</a></li> -<li><a href="#syntax">3.2. Syntax</a> +<li><a href="#sql_statements_categories">3.1. Categories</a> <ul class="sectlevel3"> -<li><a href="#semantics">3.2.1. Semantics</a></li> -<li><a href="#examples">3.2.2. Examples</a></li> -<li><a href="#_hdfs_location_of_lob_data">3.2.3. HDFS Location of LOB Data</a></li> +<li><a href="#data_definition_language_statements">3.1.1. Data Definition Language (DDL) Statements</a></li> +<li><a href="#data_manipulation_language_statements">3.1.2. Data Manipulation Language (DML) Statements</a></li> +<li><a href="#transaction_control_statements">3.1.3. Transaction Control Statements</a></li> +<li><a href="#data_control_and_security_statements">3.1.4. Data Control and Security Statements</a></li> +<li><a href="#stored_procedure_and_user_defined_function_statements">3.1.5. Stored Procedure and User-Defined Function Statements</a></li> +<li><a href="#prepared_statements">3.1.6. Prepared Statements</a></li> +<li><a href="#control_statements">3.1.7. Control Statements</a></li> +<li><a href="#object_naming_statements">3.1.8. Object Naming Statements</a></li> +<li><a href="#show_get_and_explain_statements">3.1.9. SHOW, GET, and EXPLAIN Statements</a></li> </ul> </li> -<li><a href="#_inserting_into_a_sql_table_containing_lob_columns">3.3. Inserting into a SQL Table Containing LOB Columns</a> +<li><a href="#alter_sequence_statement">3.2. ALTER SEQUENCE Statement</a> <ul class="sectlevel3"> -<li><a href="#syntax">3.3.1. Syntax</a></li> -<li><a href="#semantics">3.3.2. Semantics</a></li> -<li><a href="#considerations">3.3.3. Considerations</a></li> -<li><a href="#examples">3.3.4. Examples</a></li> +<li><a href="#alter_sequence_syntax">3.2.1. Syntax Description of ALTER SEQUENCE</a></li> +<li><a href="#alter_sequence_considerations">3.2.2. Considerations for ALTER SEQUENCE</a></li> +<li><a href="#alter_sequence_examples">3.2.3. Examples of ALTER SEQUENCE</a></li> </ul> </li> -<li><a href="#_inserting_into_a_sql_table_containing_lob_columns_using_select_clause">3.4. Inserting into a SQL Table Containing LOB Columns Using Select Clause</a> +<li><a href="#alter_table_statement">3.3. ALTER TABLE Statement</a> <ul class="sectlevel3"> -<li><a href="#syntax">3.4.1. Syntax</a></li> -<li><a href="#semantics">3.4.2. semantics</a></li> -<li><a href="#considerations">3.4.3. Considerations</a></li> -<li><a href="#examples">3.4.4. Examples</a></li> +<li><a href="#alter_table_syntax">3.3.1. Syntax Description of ALTER TABLE</a></li> +<li><a href="#alter_table_considerations">3.3.2. Considerations for ALTER TABLE</a></li> +<li><a href="#alter_table_examples">3.3.3. Example of ALTER TABLE</a></li> </ul> </li> -<li><a href="#_updating_a_sql_table_containing_lob_columns">3.5. Updating a SQL Table Containing LOB Columns</a> +<li><a href="#alter_user_statement">3.4. ALTER USER Statement</a> <ul class="sectlevel3"> -<li><a href="#_updating_using_parameters_functions">3.5.1. Updating Using Parameters/Functions</a></li> -<li><a href="#_updating_using_lob_handle">3.5.2. Updating Using Lob Handle</a></li> -<li><a href="#considerations">3.5.3. Considerations</a></li> +<li><a href="#alter_user_syntax">3.4.1. Syntax Description of ALTER USER</a></li> +<li><a href="#alter_user_considerations">3.4.2. Considerations for ALTER USER</a></li> +<li><a href="#alter_user_examples">3.4.3. Examples of ALTER USER</a></li> </ul> </li> -<li><a href="#_selecting_column_from_a_sql_table_containing_lob_columns">3.6. Selecting Column from a SQL Table Containing LOB Columns</a> +<li><a href="#begin_work_statement">3.5. BEGIN WORK Statement</a> <ul class="sectlevel3"> -<li><a href="#syntax">3.6.1. Syntax</a></li> -<li><a href="#examples">3.6.2. Examples</a></li> +<li><a href="#begin_work_considerations">3.5.1. Considerations for BEGIN WORK</a></li> +<li><a href="#begin_work_examples">3.5.2. Example of BEGIN WORK</a></li> </ul> </li> -<li><a href="#_extracting_lob_data_from_a_sql_table_containing_lob_columns">3.7. Extracting LOB Data from a SQL Table Containing LOB Columns</a> +<li><a href="#call_statement">3.6. CALL Statement</a> <ul class="sectlevel3"> -<li><a href="#_extracting_lob_data_into_a_file_for_a_given_lob_handle">3.7.1. Extracting Lob Data into a File for a Given Lob Handle</a></li> -<li><a href="#_extracting_lob_data_into_a_user_specified_buffer">3.7.2. Extracting Lob Data into a User Specified Buffer</a></li> -<li><a href="#_extracting_lob_length_for_a_given_lob_handle">3.7.3. Extracting Lob Length for a Given Lob Handle</a></li> -<li><a href="#considerations">3.7.4. Considerations</a></li> +<li><a href="#call_syntax">3.6.1. Syntax Description of CALL</a></li> +<li><a href="#call_considerations">3.6.2. Considerations for CALL</a></li> +<li><a href="#call_examples">3.6.3. Examples of CALL</a></li> </ul> </li> -<li><a href="#_deleting_column_from_a_sql_table_containing_lob_columns">3.8. Deleting Column from a SQL Table Containing LOB columns</a> +<li><a href="#commit_work_statement">3.7. COMMIT WORK Statement</a> <ul class="sectlevel3"> -<li><a href="#syntax">3.8.1. Syntax</a></li> -<li><a href="#considerations">3.8.2. Considerations</a></li> +<li><a href="#commit_work_considerations">3.7.1. Considerations for COMMIT WORK</a></li> +<li><a href="#commit_work_examples">3.7.2. Example of COMMIT WORK</a></li> </ul> </li> -<li><a href="#_dropping_a_sql_table_containing_lob_columns">3.9. Dropping a SQL Table Containing LOB Columns</a></li> -<li><a href="#_garbage_collection">3.10. Garbage Collection</a></li> -<li><a href="#_cleanup_of_a_sql_table_containing_lob_columns">3.11. Cleanup of a SQL Table Containing LOB Columns</a></li> -<li><a href="#_showddl_for_lob">3.12. SHOWDDL for LOB</a> +<li><a href="#control_query_cancel_statement">3.8. CONTROL QUERY CANCEL Statement</a> <ul class="sectlevel3"> -<li><a href="#syntax">3.12.1. Syntax</a></li> -<li><a href="#examples">3.12.2. Examples</a></li> +<li><a href="#control_query_cancel_syntax">3.8.1. Syntax Description of CONTROL QUERY CANCEL</a></li> +<li><a href="#control_query_cancel_considerations">3.8.2. Considerations for CONTROL QUERY CANCEL</a></li> +<li><a href="#control_query_cancel_examples">3.8.3. Example of CONTROL QUERY CANCEL</a></li> </ul> </li> -<li><a href="#_get_lob_statistics_for_a_lob_table">3.13. Get Lob Statistics for a LOB Table</a> +<li><a href="#control_query_default_statement">3.9. CONTROL QUERY DEFAULT Statement</a> <ul class="sectlevel3"> -<li><a href="#_get_statement">3.13.1. Get Statement</a></li> -<li><a href="#_select_statement">3.13.2. Select Statement</a></li> +<li><a href="#control_query_default_syntax">3.9.1. Syntax Description of CONTROL QUERY DEFAULT</a></li> +<li><a href="#control_query_default_considerations">3.9.2. Considerations for CONTROL QUERY DEFAULT</a></li> +<li><a href="#control_query_default_examples">3.9.3. Examples of CONTROL QUERY DEFAULT</a></li> </ul> </li> +<li><a href="#create_function_statement">3.10. CREATE FUNCTION Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_function_syntax">3.10.1. Syntax Description of CREATE FUNCTION</a></li> +<li><a href="#create_function_considerations">3.10.2. Considerations for CREATE FUNCTION</a></li> +<li><a href="#create_function_examples">3.10.3. Examples of CREATE FUNCTION</a></li> </ul> </li> +<li><a href="#create_index_statement">3.11. CREATE INDEX Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_index_syntax">3.11.1. Syntax Description of CREATE INDEX</a></li> +<li><a href="#create_index_considerations">3.11.2. Considerations for CREATE INDEX</a></li> +<li><a href="#create_index_examples">3.11.3. Examples of CREATE INDEX</a></li> </ul> -</div> -</div> -<div id="content"> -<div id="preamble"> -<div class="sectionbody"> -<div class="paragraph"> -<p><strong>License Statement</strong></p> -</div> -<div class="paragraph"> -<p>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 <a href="http://www.apache.org/licenses/LICENSE-2.0"; class="bare">http://www.apache.org/licenses/LICENSE-2.0</a></p> -</div> -<div class="paragraph"> -<p>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.</p> -</div> -<div class="paragraph"> -<p><strong>Disclaimer:</strong> <em>Apache Trafodion is an effort undergoing incubation at the Apache Software Foundation (ASF), sponsored by -the Apache Incubator PMC. Incubation is required of all newly accepted projects until a further review indicates that -the infrastructure, communications, and decision making process have stabilized in a manner consistent with other -successful ASF projects. While incubation status is not necessarily a reflection of the completeness or stability of the code, -it does indicate that the project has yet to be fully endorsed by the ASF.</em> -<<<</p> -</div> -<div class="paragraph"> -<p><strong>Acknowledgements</strong></p> -</div> -<div class="paragraph"> -<p>Microsoft®, Windows®, Windows NT®, Windows® XP, and Windows Vista® are -U.S. registered trademarks of Microsoft Corporation. Intel® and Intel® -Itanium® are trademarks of Intel Corporation in the U.S. and other -countries. Java® is a registered trademark of Oracle and/or its -affiliates. Motif, OSF/1, UNIX®, X/Open®, and the X device is a -trademark of X/Open Company Ltd. in the UK and other countries.</p> -</div> -<div class="paragraph"> -<p>OSF, OSF/1, OSF/Motif, Motif, and Open Software Foundation are trademarks of -the Open Software Foundation in the U.S. and other countries. -© 1990, 1991, 1992, 1993 Open Software Foundation, Inc.</p> -</div> -<div class="paragraph"> -<p>The OSF documentation and the OSF software to which it relates are derived in -part from materials supplied by the following: © 1987, 1988, 1989 -Carnegie-Mellon University. © 1989, 1990, 1991 Digital Equipment -Corporation. © 1985, 1988, 1989, 1990 Encore Computer Corporation. © 1988 Free -Software Foundation, Inc. © 1987, 1988, 1989, 1990, 1991 Hewlett-Packard -Company. © 1985, 1987, 1988, 1989, 1990, 1991, 1992 International -Business Machines Corporation. © 1988, 1989 Massachusetts Institute of -Technology. © 1988, 1989, 1990 Mentat Inc. © 1988 Microsoft Corporation. -© 1987, 1988, 1989, 1990, 1991, -1992 SecureWare, Inc. © 1990, 1991 Siemens Nixdorf Informations systeme -AG. © 1986, 1989, 1996, 1997 Sun Microsystems, Inc. © 1989, 1990, 1991 -Transarc Corporation.</p> -</div> -<div class="paragraph"> -<p>OSF software and documentation are based in part -on the Fourth Berkeley Software Distribution under license from The -Regents of the University of California. OSF acknowledges the following -individuals and institutions for their role in its development: Kenneth -C.R.C. Arnold, Gregory S. Couch, Conrad C. Huang, Ed James, Symmetric -Computer Systems, Robert Elz. © 1980, 1981, 1982, 1983, 1985, 1986, -1987, 1988, 1989 Regents of the University of California. OSF MAKES NO -WARRANTY OF ANY KIND WITH REGARD TO THE OSF MATERIAL PROVIDED HEREIN, -INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY -AND FITNESS FOR A PARTICULAR PURPOSE. OSF shall not be liable for errors -contained herein or for incidental consequential damages in connection -with the furnishing, performance, or use of this material. -<<<</p> -</div> -<div class="paragraph"> -<p><strong>Revision History</strong></p> -</div> -<table class="tableblock frame-all grid-all spread"> -<colgroup> -<col style="width: 50%;"> -<col style="width: 50%;"> -</colgroup> -<thead> -<tr> -<th class="tableblock halign-left valign-top">Version</th> -<th class="tableblock halign-left valign-top">Date</th> -</tr> -</thead> -<tbody> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">2.2.0</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">TBD</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">2.1.0</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">May 1, 2017</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">2.0.1</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">July 7, 2016</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">2.0.0</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">June 6, 2016</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">1.3.0</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">January, 2016</p></td> -</tr> -</tbody> -</table> -</div> -</div> -<div class="sect1"> -<h2 id="About_This_Document">1. About This Document</h2> -<div class="sectionbody"> -<div class="paragraph"> -<p>This guide describes how to use Large Object (LOB) datatypes in Trafodion SQL.</p> -</div> -<div class="sect2"> -<h3 id="Intended_Audience">1.1. Intended Audience</h3> -<div class="paragraph"> -<p>This manual is intended for programmers who use LOB datatypes.</p> -</div> -</div> -<div class="sect2"> -<h3 id="New_and_Changed_Information">1.2. New and Changed Information</h3> -<div class="paragraph"> -<p>This is a new guide.</p> -</div> -</div> -<div class="sect2"> -<h3 id="Document_Organization">1.3. Document Organization</h3> -<table class="tableblock frame-all grid-all spread"> -<colgroup> -<col style="width: 30%;"> -<col style="width: 70%;"> -</colgroup> -<thead> -<tr> -<th class="tableblock halign-left valign-top">Chapter</th> -<th class="tableblock halign-left valign-top">Description</th> -</tr> -</thead> -<tbody> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock"><a href="#introduction">Introduction</a></p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">Introduces LOBs and covers following topics:<br> -• 1.1 <a href="#what is a lob">What is a LOB</a><br> -• 1.2 <a href="#lob data types">LOB Data Types</a><br> -• 1.3 <a href="#lob storage">LOB Storage</a><br> -• 1.4 <a href="#lob handle">LOB Handle</a><br> -• 1.5 <a href="#lob restrictions">LOB Restrictions</a><br> -• 1.6 <a href="#lob related sql statements and functions">LOB Related SQL Statements and Functions</a>.</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock"><a href="#working with LOBs">Working With LOBs</a></p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">Explains how to use a LOB with SQL statement and covers following topics:<br> -• 2.1 <a href="#creating a sql table with lob columns">Creating a SQL Table with LOB Columns</a><br> -• 2.2 <a href="#inserting into a sql table containing lob columns">Inserting into a SQL Table Containing LOB Columns</a><br> -• 2.3 <a href="#inserting into a sql table containing lob columns using select clause">Inserting into a SQL Table Containing LOB Columns Using Select Clause</a><br> -• 2.4 <a href="#updating a sql table containing lob columns">Updating a SQL Table Containing LOB Columns</a><br> -• 2.5 <a href="#selecting column from a sql table containing lob columns">Selecting Column from a SQL Table Containing LOB Columns</a><br> -• 2.6 <a href="#extracting lob data from a sql table containing lob columns">Extracting LOB Data from a SQL Table Containing LOB Columns</a><br> -• 2.7 <a href="#deleting column from a sql table containing lob columns">Deleting Column from a SQL Table Containing LOB columns</a><br> -• 2.8 <a href="#dropping a sql table containing lob columns">Dropping a SQL Table Containing LOB Columns</a><br> -• 2.9 <a href="#garbage collection">Garbage Collection</a><br> -• 2.10 <a href="#cleanup of a sql table containing lob columns">Cleanup of a SQL Table Containing LOB Columns</a><br> -• 2.11 <a href="#showddl for lobs">SHOWDDL for LOBs</a><br> -• 2.12 <a href="#getting statement for lob tables">Getting Statement for LOB Tables</a></p></td> -</tr> -</tbody> -</table> -</div> -<div class="sect2"> -<h3 id="_notation_conventions">1.4. Notation Conventions</h3> -<div class="paragraph"> -<p>This list summarizes the notation conventions for syntax presentation in this manual.</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>UPPERCASE LETTERS</p> -<div class="paragraph"> -<p>Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">SELECT</code></pre> -</div> -</div> </li> -<li> -<p>lowercase letters</p> -<div class="paragraph"> -<p>Lowercase letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">file-name</code></pre> -</div> -</div> +<li><a href="#create_library_statement">3.12. CREATE LIBRARY Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_library_syntax">3.12.1. Syntax Description of CREATE LIBRARY</a></li> +<li><a href="#create_library_considerations">3.12.2. Considerations for CREATE LIBRARY</a></li> +<li><a href="#create_library_examples">3.12.3. Examples of CREATE LIBRARY</a></li> +</ul> </li> -<li> -<p>[ ] Brackets</p> -<div class="paragraph"> -<p>Brackets enclose optional syntax items.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">DATETIME [start-field TO] end-field</code></pre> -</div> -</div> -<div class="paragraph"> -<p>A group of items enclosed in brackets is a list from which you can choose one item or none.</p> -</div> -<div class="paragraph"> -<p>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.</p> -</div> -<div class="paragraph"> -<p>For example:</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">DROP SCHEMA schema [CASCADE] -DROP SCHEMA schema [ CASCADE | RESTRICT ]</code></pre> -</div> -</div> +<li><a href="#create_procedure_statement">3.13. CREATE PROCEDURE Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_procedure_syntax">3.13.1. Syntax Description of CREATE PROCEDURE</a></li> +<li><a href="#create_procedure_considerations">3.13.2. Considerations for CREATE PROCEDURE</a></li> +<li><a href="#create_procedure_examples">3.13.3. Examples of CREATE PROCEDURE</a></li> +</ul> </li> +<li><a href="#create_role_statement">3.14. CREATE ROLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_role_syntax">3.14.1. Syntax Description of CREATE ROLE</a></li> +<li><a href="#create_role_considerations">3.14.2. Considerations for CREATE ROLE</a></li> +<li><a href="#create_role_examples">3.14.3. Examples of CREATE ROLE</a></li> </ul> -</div> -<div style="page-break-after: always;"></div> -<div class="ulist"> -<ul> -<li> -<p>{ } Braces</p> -<div class="paragraph"> -<p>Braces enclose required syntax items.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">FROM { grantee [, grantee ] ... }</code></pre> -</div> -</div> -<div class="paragraph"> -<p>A group of items enclosed in braces is a list from which you are required to choose one item.</p> -</div> -<div class="paragraph"> -<p>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.</p> -</div> -<div class="paragraph"> -<p>For example:</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INTERVAL { start-field TO end-field } -{ single-field } -INTERVAL { start-field TO end-field | single-field }</code></pre> -</div> -</div> </li> -<li> -<p>| Vertical Line</p> -<div class="paragraph"> -<p>A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">{expression | NULL}</code></pre> -</div> -</div> +<li><a href="#create_schema_statement">3.15. CREATE SCHEMA Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_schema_syntax">3.15.1. Syntax Description of CREATE SCHEMA</a></li> +<li><a href="#create_schema_considerations">3.15.2. Considerations for CREATE SCHEMA</a></li> +<li><a href="#create_schema_examples">3.15.3. Examples of CREATE SCHEMA</a></li> +</ul> </li> -<li> -<p>… Ellipsis</p> -<div class="paragraph"> -<p>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.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">ATTRIBUTE[S] attribute [, attribute] ... -{, sql-expression } ...</code></pre> -</div> -</div> -<div class="paragraph"> -<p>An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times.</p> -</div> -<div class="paragraph"> -<p>For example:</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">expression-n ...</code></pre> -</div> -</div> -</li> -<li> -<p>Punctuation</p> -<div class="paragraph"> -<p>Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">DAY (datetime-expression) -@script-file</code></pre> -</div> -</div> -<div class="paragraph"> -<p>Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown.</p> -</div> -<div class="paragraph"> -<p>For example:</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">"{" module-name [, module-name] ... "}"</code></pre> -</div> -</div> +<li><a href="#create_sequence_statement">3.16. CREATE SEQUENCE Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_sequence_syntax">3.16.1. Syntax Description of CREATE SEQUENCE</a></li> +<li><a href="#create_sequence_considerations">3.16.2. Considerations for CREATE SEQUENCE</a></li> +<li><a href="#create_sequence_examples">3.16.3. Examples of CREATE SEQUENCE</a></li> +</ul> </li> -<li> -<p>Item Spacing</p> -<div class="paragraph"> -<p>Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">DAY (datetime-expression) DAY(datetime-expression)</code></pre> -</div> -</div> -<div class="paragraph"> -<p>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:</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">myfile.sh</code></pre> -</div> -</div> +<li><a href="#create_table_statement">3.17. CREATE TABLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_table_syntax">3.17.1. Syntax Description of CREATE TABLE</a></li> +<li><a href="#create_table_considerations">3.17.2. Considerations for CREATE TABLE</a></li> +<li><a href="#create_table_trafodion_sql_extensions_to_create_table">3.17.3. Trafodion SQL Extensions to CREATE TABLE</a></li> +<li><a href="#create_table_examples">3.17.4. Examples of CREATE TABLE</a></li> +</ul> </li> -<li> -<p>Line Spacing</p> -<div class="paragraph"> -<p>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.</p> -</div> -<div class="paragraph"> -<p>This spacing distinguishes items in a continuation line from items in a vertical list of selections.</p> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">match-value [NOT] LIKE _pattern - [ESCAPE esc-char-expression]</code></pre> -</div> -</div> +<li><a href="#create_view_statement">3.18. CREATE VIEW Statement</a> +<ul class="sectlevel3"> +<li><a href="#create_view_syntax">3.18.1. Syntax Description of CREATE VIEW</a></li> +<li><a href="#create_view_considerations">3.18.2. Considerations for CREATE VIEW</a></li> +<li><a href="#create_view_examples">3.18.3. Examples of CREATE VIEW</a></li> +</ul> </li> +<li><a href="#delete_statement">3.19. DELETE Statement</a> +<ul class="sectlevel3"> +<li><a href="#delete_syntax">3.19.1. Syntax Description of DELETE</a></li> +<li><a href="#delete_considerations">3.19.2. Considerations for DELETE</a></li> +<li><a href="#delete_examples">3.19.3. Examples of DELETE</a></li> </ul> -</div> -</div> -<div class="sect2"> -<h3 id="_comments_encouraged">1.5. Comments Encouraged</h3> -<div class="paragraph"> -<p>We encourage your comments concerning this document. We are committed to providing documentation that meets your -needs. Send any errors found, suggestions for improvement, or compliments to <a href="mailto:u...@trafodion.incubator.apache.org";>u...@trafodion.incubator.apache.org</a>.</p> -</div> -<div class="paragraph"> -<p>Include the document title and any comment, error found, or suggestion for improvement you have concerning this document.</p> -</div> -</div> -</div> -</div> -<div class="sect1"> -<h2 id="introduction">2. Introduction</h2> -<div class="sectionbody"> -<div class="sect2"> -<h3 id="_what_is_a_lob">2.1. What is a LOB</h3> -<div class="paragraph"> -<p>LOB (Large Object), a set of large object data types used to store large volumes of data, provides random and piece-wise access to the data.</p> -</div> -<div class="paragraph"> -<p>Database support for LOB is not universal.</p> -</div> -</div> -<div class="sect2"> -<h3 id="_lob_data_types">2.2. LOB Data Types</h3> -<div class="paragraph"> -<p>The following are the data types supported by Trafodion.<br></p> -</div> -<table class="tableblock frame-all grid-all spread"> -<caption class="title">Table 1-1 Descriptions for BLOB and CLOB</caption> -<colgroup> -<col style="width: 30%;"> -<col style="width: 70%;"> -</colgroup> -<thead> -<tr> -<th class="tableblock halign-left valign-top"><strong>Data Type</strong></th> -<th class="tableblock halign-left valign-top"><strong>Desciprtion</strong></th> -</tr> -</thead> -<tbody> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">BLOB</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">Binary Large Object.<br> -</p><p class="tableblock">Holds large blocks of unstructured data in binary format.<br> -</p><p class="tableblock">BLOB is ideal for storing and handling unstructured data, such as images, audio, video and other multimedia objects.</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">CLOB</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">Character Large Object.<br> -</p><p class="tableblock">Stores unusually large amounts of string data in character set format.<br> -</p><p class="tableblock">CLOB is ideal for storing and processing semi-structured data, such as a large document or string.</p></td> -</tr> -</tbody> -</table> -</div> -<div class="sect2"> -<h3 id="_lob_storage">2.3. LOB Storage</h3> -<div class="paragraph"> -<p>The basic design idea is to allow a database row to have multiple LOB columns and a table to have multiple such rows. The design takes a combination approach of row store and column store.</p> -</div> -<div class="paragraph"> -<p>When a SQL table with a LOB column is created, there are several dependent objects that are created to hold metadata information on the LOB as well as the data.</p> -</div> -<div class="paragraph"> -<p>The LOB data itself is stored in an HDFS file while the Trafodion table contains a unique LOB handle for each LOB value. The LOB handle provides the identifier that is used to query the LOB metadata tables to find the location of the LOB data files.</p> -</div> -<div class="paragraph"> -<p>The naming conventions for the LOB tables are as follows:</p> -</div> -<table class="tableblock frame-all grid-all spread"> -<caption class="title">Table 1-2 Naming Conventions for LOB Tables</caption> -<colgroup> -<col style="width: 50%;"> -<col style="width: 50%;"> -</colgroup> -<thead> -<tr> -<th class="tableblock halign-left valign-top" colspan="2"><strong>Naming Conventions</strong></th> -</tr> -</thead> -<tbody> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">SQL Table</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">TLOB</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOB MD table<br> -(one per SQL table containing LOB columns)</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOBMD_{object UID}</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOB Descriptor Handle Table<br> -(one per LOB column)</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOBDescHandle_{object UID}_{LOB number}</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOB Descriptor Chunks Table<br> -(one per LOB column)</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOBDescChunks_{object UID}_{LOB number}</p></td> -</tr> -<tr> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOB Data Table<br> -(in HDFS one per LOB column)</p></td> -<td class="tableblock halign-left valign-top"><p class="tableblock">LOBP_{object UID}_{LOB number}</p></td> -</tr> -</tbody> -</table> -</div> -<div class="sect2"> -<h3 id="_lob_handle">2.4. LOB Handle</h3> -<div class="paragraph"> -<p>The LOB handle is used to describe a LOB value. A SQL table that contains a LOB column will contain this handle structure in each row for each LOB value inserted.</p> -</div> -<div class="paragraph"> -<p>The actual LOB data will be stored in unstructured HDFS files as column store whereas the LOB handleâthat describes the location, offset information, descriptor information and so on can be thought of as a unique identifier to the lob data, is stored in the database row of the table as a traditional row store.</p> -</div> -<div class="paragraph"> -<p>The handle can be thought of as a file locator as is more commonly referred to in LOB related terminology.</p> -</div> -<div class="sect3"> -<h4 id="_external_structure">2.4.1. External Structure</h4> -<div class="paragraph"> -<p>The following is an example of an external structure that is stored in the row of a SQL table:</p> -</div> -<div class="paragraph"> -<p>LOBH00000002000100047335557604604880171074381106028370118212279894381354363017"TRAFODION"."SCH"</p> -</div> -</div> -</div> -<div class="sect2"> -<h3 id="_lob_restrictions">2.5. LOB Restrictions</h3> -<div class="ulist"> -<ul> -<li> -<p>LOB columns cannot appear in FROM clauses as join predicates.</p> </li> -<li> -<p>LOB columns cannot appear in STORE BY, ORDER BY or GROUP BY clauses.</p> +<li><a href="#drop_function_statement">3.20. DROP FUNCTION Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_function_syntax">3.20.1. Syntax Description of DROP FUNCTION</a></li> +<li><a href="#drop_function_considerations">3.20.2. Considerations for DROP FUNCTION</a></li> +<li><a href="#drop_function_examples">3.20.3. Examples of DROP FUNCTION</a></li> +</ul> </li> -<li> -<p>LOB columns cannot appear in WHERE clauses as predicates other than LIKE (meaning that no comparison is allowed).</p> +<li><a href="#drop_index_statement">3.21. DROP INDEX Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_index_syntax">3.21.1. Syntax Description of DROP INDEX</a></li> +<li><a href="#drop_index_considerations">3.21.2. Considerations for DROP INDEX</a></li> +<li><a href="#drop_index_examples">3.21.3. Examples of DROP INDEX</a></li> +</ul> </li> -<li> -<p>LOB columns cannot appear in SELECT clauses as aggregate function arguments.</p> +<li><a href="#drop_library_statement">3.22. DROP LIBRARY Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_library_syntax">3.22.1. Syntax Description of DROP LIBRARY</a></li> +<li><a href="#drop_library_considerations">3.22.2. Considerations for DROP LIBRARY</a></li> +<li><a href="#drop_library_examples">3.22.3. Examples of DROP LIBRARY</a></li> +</ul> </li> -<li> -<p>LOB columns cannot appear in SELECT DISTINCT clauses.</p> +<li><a href="#drop_procedure_statement">3.23. DROP PROCEDURE Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_procedure_syntax">3.23.1. Syntax Description of DROP PROCEDURE</a></li> +<li><a href="#drop_procedure_considerations">3.23.2. Considerations for DROP PROCEDURE</a></li> +<li><a href="#drop_procedure_examples">3.23.3. Examples of DROP PROCEDURE</a></li> +</ul> </li> -<li> -<p>LOB columns cannot be used as primary keys.</p> +<li><a href="#drop_role_statement">3.24. DROP ROLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_role_syntax">3.24.1. Syntax Description of DROP ROLE</a></li> +<li><a href="#drop_role_considerations">3.24.2. Considerations for DROP ROLE</a></li> +<li><a href="#drop_role_examples">3.24.3. Examples of DROP ROLE</a></li> +</ul> </li> -<li> -<p>LOB columns cannot be used in CREATE INDEX statements.</p> +<li><a href="#drop_schema_statement">3.25. DROP SCHEMA Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_schema_syntax">3.25.1. Syntax Description of DROP SCHEMA</a></li> +<li><a href="#drop_schema_considerations">3.25.2. Considerations for DROP SCHEMA</a></li> +<li><a href="#drop_schema_examples">3.25.3. Example of DROP SCHEMA</a></li> +</ul> </li> -<li> -<p>LOB columns cannot be used in statistics update statements.</p> +<li><a href="#drop_sequence_statement">3.26. DROP SEQUENCE Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_sequence_syntax">3.26.1. Syntax Description of DROP SEQUENCE</a></li> +<li><a href="#drop_sequence_considerations">3.26.2. Considerations for DROP SEQUENCE</a></li> +<li><a href="#drop_sequence_examples">3.26.3. Examples of DROP SEQUENCE</a></li> +</ul> </li> -<li> -<p>The file that contains data to insert from or to extract to needs to be on platform as a Linux or HDFS files.</p> +<li><a href="#drop_table_statement">3.27. DROP TABLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_table_syntax">3.27.1. Syntax Description of DROP TABLE</a></li> +<li><a href="#drop_table_considerations">3.27.2. Considerations for DROP TABLE</a></li> +<li><a href="#drop_table_examples">3.27.3. Examples of DROP TABLE</a></li> +</ul> </li> +<li><a href="#drop_view_statement">3.28. DROP VIEW Statement</a> +<ul class="sectlevel3"> +<li><a href="#drop_view_syntax">3.28.1. Syntax Description of DROP VIEW</a></li> +<li><a href="#drop_view_considerations">3.28.2. Considerations for DROP VIEW</a></li> +<li><a href="#drop_view_examples">3.28.3. Example of DROP VIEW</a></li> </ul> -</div> -</div> -<div class="sect2"> -<h3 id="_lob_related_sql_statements_and_functions">2.6. LOB Related SQL Statements and Functions</h3> -<div class="sect3"> -<h4 id="_supported_sql_statements">2.6.1. Supported SQL Statements</h4> -<div class="paragraph"> -<p>The following SQL statements are supported:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>CREATE TABLE</p> </li> -<li> -<p>SELECT</p> +<li><a href="#execute_statement">3.29. Execute Statement</a> +<ul class="sectlevel3"> +<li><a href="#execute_syntax">3.29.1. Syntax Description of EXECUTE</a></li> +<li><a href="#execute_considerations">3.29.2. Considerations for EXECUTE</a></li> +<li><a href="#execute_examples">3.29.3. Examples of EXECUTE</a></li> +</ul> </li> -<li> -<p>INSERT</p> +<li><a href="#explain_statement">3.30. EXPLAIN Statement</a> +<ul class="sectlevel3"> +<li><a href="#explain_syntax">3.30.1. Syntax Description of EXPLAIN</a></li> +<li><a href="#explain_considerations">3.30.2. Considerations for EXPLAIN</a></li> +</ul> </li> -<li> -<p>UPDATE</p> +<li><a href="#get_statement">3.31. GET Statement</a> +<ul class="sectlevel3"> +<li><a href="#get_syntax">3.31.1. Syntax Description of GET</a></li> +<li><a href="#get_considerations">3.31.2. Considerations for GET</a></li> +<li><a href="#get_examples">3.31.3. Examples of GET</a></li> +</ul> </li> -<li> -<p>UPDATE with APPEND option</p> +<li><a href="#get_hbase_objects_statement">3.32. GET HBASE OBJECTS Statement</a> +<ul class="sectlevel3"> +<li><a href="#get_hbase_objects_syntax">3.32.1. Syntax Description of GET HBASE OBJECTS</a></li> +<li><a href="#get_hbase_objects_examples">3.32.2. Examples of GET HBASE OBJECTS</a></li> +</ul> </li> -<li> -<p>EXTRACT</p> +<li><a href="#get_version_of_metadata_statement">3.33. GET VERSION OF METADATA Statement</a> +<ul class="sectlevel3"> +<li><a href="#get_version_of_metadata_considerations">3.33.1. Considerations for GET VERSION OF METADATA</a></li> +<li><a href="#get_version_of_metadata_examples">3.33.2. Examples of GET VERSION OF METADATA</a></li> +</ul> </li> +<li><a href="#get_version_of_software_statement">3.34. GET VERSION OF SOFTWARE Statement</a> +<ul class="sectlevel3"> +<li><a href="#get_version_of_software_considerations">3.34.1. Considerations for GET VERSION OF SOFTWARE</a></li> +<li><a href="#get_version_of_software_examples">3.34.2. Examples of GET VERSION OF SOFTWARE</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="_unsupported_sql_statements">2.6.2. Unsupported SQL Statements</h4> -<div class="paragraph"> -<p>The following SQL statements are not supported:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>ALTER TABLE</p> </li> -<li> -<p>MERGE</p> +<li><a href="#grant_statement">3.35. GRANT Statement</a> +<ul class="sectlevel3"> +<li><a href="#_syntax_description_of_grant">3.35.1. syntax description of grant</a></li> +<li><a href="#grant_considerations">3.35.2. Considerations for GRANT</a></li> +<li><a href="#grant_examples">3.35.3. Examples of GRANT</a></li> +</ul> </li> +<li><a href="#grant_component_privilege_statement">3.36. GRANT COMPONENT PRIVILEGE Statement</a> +<ul class="sectlevel3"> +<li><a href="#grant_component_privilege_syntax">3.36.1. Syntax Description of GRANT COMPONENT PRIVILEGE</a></li> +<li><a href="#grant_component_privilege_considerations">3.36.2. Considerations for GRANT COMPONENT PRIVILEGE</a></li> +<li><a href="#grant_component_privilege_considerations">3.36.3. Examples of GRANT COMPONENT PRIVILEGE</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="_supported_lob_conversion_sql_functions">2.6.3. Supported LOB Conversion SQL Functions</h4> -<div class="paragraph"> -<p>The following LOB conversion SQL functions are supported:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>LOBTOSTRING</p> </li> -<li> -<p>LOBTOFILE</p> +<li><a href="#grant_role_statement">3.37. GRANT ROLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#grant_role_syntax">3.37.1. Syntax Description of GRANT ROLE</a></li> +<li><a href="#grant_role_considerations">3.37.2. Considerations for GRANT ROLE</a></li> +<li><a href="#grant_role_examples">3.37.3. Examples of GRANT ROLE</a></li> +</ul> </li> -<li> -<p>LOBTOBUFFER</p> +<li><a href="#insert_statement">3.38. INSERT Statement</a> +<ul class="sectlevel3"> +<li><a href="#insert_syntax">3.38.1. Syntax Description of INSERT</a></li> +<li><a href="#insert_considerations">3.38.2. Considerations for INSERT</a></li> +<li><a href="#insert_examples">3.38.3. Examples of INSERT</a></li> +</ul> </li> -<li> -<p>STRINGTOLOB</p> +<li><a href="#invoke_statement">3.39. INVOKE Statement</a> +<ul class="sectlevel3"> +<li><a href="#invoke_syntax">3.39.1. Syntax Description of INVOKE</a></li> +<li><a href="#invoke_considerations">3.39.2. Considerations for INVOKE</a></li> +<li><a href="#invoke_required_privileges">3.39.3. Required Privileges</a></li> +<li><a href="#invoke_examples">3.39.4. Examples of INVOKE</a></li> +</ul> </li> -<li> -<p>FILETOLOB</p> +<li><a href="#merge_statement">3.40. MERGE Statement</a> +<ul class="sectlevel3"> +<li><a href="#merge_syntax">3.40.1. Syntax Description of MERGE</a></li> +<li><a href="#merge_considerations">3.40.2. Considerations for MERGE</a></li> +<li><a href="#merge_upsert_using_single_row">3.40.3. Upsert Using Single Row</a></li> +<li><a href="#merge_examples">3.40.4. Examples of MERGE</a></li> +</ul> </li> -<li> -<p>BUFFERTOLOB</p> +<li><a href="#prepare_statement">3.41. PREPARE Statement</a> +<ul class="sectlevel3"> +<li><a href="#prepare_syntax">3.41.1. Syntax Description of PREPARE</a></li> +<li><a href="#prepare_considerations">3.41.2. Considerations for PREPARE</a></li> +<li><a href="#prepare_examples">3.41.3. Examples of PREPARE</a></li> +</ul> </li> -<li> -<p>EXTERNALTOLOB</p> +<li><a href="#register_user_statement">3.42. REGISTER USER Statement</a> +<ul class="sectlevel3"> +<li><a href="#register_user_syntax">3.42.1. Syntax Description of REGISTER USER</a></li> +<li><a href="#register_user_register_user_considerations">3.42.2. Considerations for REGISTER USER</a></li> +<li><a href="#register_user_examples">3.42.3. Examples of REGISTER USER</a></li> +</ul> </li> +<li><a href="#_revoke_statement">3.43. REVOKE Statement</a> +<ul class="sectlevel3"> +<li><a href="#_syntax_description_of_revoke">3.43.1. Syntax Description of REVOKE</a></li> +<li><a href="#revoke_considerations">3.43.2. Considerations for REVOKE</a></li> +<li><a href="#revoke_examples">3.43.3. Examples of REVOKE</a></li> </ul> -</div> -</div> -</div> -</div> -</div> -<div class="sect1"> -<h2 id="_working_with_lobs">3. Working with LOBs</h2> -<div class="sectionbody"> -<div class="sect2"> -<h3 id="_creating_a_sql_table_with_lob_columns">3.1. Creating a SQL Table with LOB Columns</h3> -<div class="paragraph"> -<p>When creating a SQL table with LOB columns, following relevant tables and files are created as well:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>One LOB MD table.</p> </li> -<li> -<p>Two dependent descriptor tables.</p> +<li><a href="#revoke_component_privilege_statement">3.44. REVOKE COMPONENT PRIVILEGE Statement</a> +<ul class="sectlevel3"> +<li><a href="#_syntax_description_of_revoke_component_privilege">3.44.1. Syntax Description of REVOKE COMPONENT PRIVILEGE</a></li> +<li><a href="#revoke_component_privilege_considerations">3.44.2. Considerations for REVOKE COMPONENT PRIVILEGE</a></li> +<li><a href="#revoke_component_examples">3.44.3. Examples of REVOKE COMPONENT PRIVILEGE</a></li> +</ul> </li> -<li> -<p>HDFS data file (locates at /user/trafodion/lobs) for each column.</p> +<li><a href="#revoke_role_statement">3.45. REVOKE ROLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#revoke_role_syntax">3.45.1. Syntax Description of REVOKE ROLE</a></li> +<li><a href="#revoke_role_considerations">3.45.2. Considerations for REVOKE ROLE</a></li> +<li><a href="#revoke_role_examples">3.45.3. Examples of REVOKE ROLE</a></li> +</ul> </li> +<li><a href="#rollback_work_statement">3.46. ROLLBACK WORK Statement</a> +<ul class="sectlevel3"> +<li><a href="#rollback_work_syntax">3.46.1. Syntax Description of ROLLBACK WORK</a></li> +<li><a href="#rollback_work_considerations">3.46.2. Considerations for ROLLBACK WORK</a></li> +<li><a href="#rollback_work_examples">3.46.3. Example of ROLLBACK WORK</a></li> </ul> -</div> -</div> -<div class="sect2"> -<h3 id="syntax">3.2. Syntax</h3> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">CREATE TABLE table-name (column-spec[, column-spec]â¦)</code></pre> -</div> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">column-spec is: -lob-column-spec | -other-column-spec - -lob-column-spec is: -column-name {lob-data-type}[column-constraint] - -other-column-spec is: -column-name {data-type}[column-constraint] - -lob-data-type is: -BLOB | CLOB [({numeric literal} [unit])] [STORAGE 'storage literal'] - -unit is: -empty | -K | -M | -G</code></pre> -</div> -</div> -<div class="sect3"> -<h4 id="semantics">3.2.1. Semantics</h4> -<div class="ulist"> -<ul> -<li> -<p><code><em>storage literal</em></code></p> -<div class="paragraph"> -<p>Currently Trafodion only supports <code>'EXTERNAL'</code> here.</p> -</div> -<div class="paragraph"> -<p>External LOB object that are not managed by Trafodion.</p> -</div> </li> -<li> -<p><code><em>empty</em></code></p> -<div class="paragraph"> -<p>Number of bytes specified by the numeric literal.</p> -</div> +<li><a href="#select_statement">3.47. SELECT Statement</a> +<ul class="sectlevel3"> +<li><a href="#select_syntax">3.47.1. Syntax Description of SELECT</a></li> +<li><a href="#select_considerations">3.47.2. Considerations for SELECT</a></li> +<li><a href="#select_examples">3.47.3. Examples of SELECT</a></li> +</ul> </li> -<li> -<p><code><em>K</em></code></p> -<div class="paragraph"> -<p>Numeric literal value * 1024.</p> -</div> +<li><a href="#set_schema_statement">3.48. SET SCHEMA Statement</a> +<ul class="sectlevel3"> +<li><a href="#set_schema_syntax">3.48.1. Syntax Description of SET SCHEMA</a></li> +<li><a href="#set_schema_considerations">3.48.2. Considerations for SET SCHEMA</a></li> +<li><a href="#set_schema_examples">3.48.3. Examples of SET SCHEMA</a></li> +</ul> </li> -<li> -<p><code><em>M</em></code></p> -<div class="paragraph"> -<p>Numeric literal value * 1024 * 1024.</p> -</div> +<li><a href="#set_transaction_statement">3.49. SET TRANSACTION Statement</a> +<ul class="sectlevel3"> +<li><a href="#set_transaction_syntax">3.49.1. Syntax Description of SET TRANSACTION</a></li> +<li><a href="#set_transaction_consideration">3.49.2. Considerations for SET TRANSACTION</a></li> +<li><a href="#set_transaction_examples">3.49.3. Examples of SET TRANSACTION</a></li> +</ul> </li> -<li> -<p><code><em>G</em></code></p> -<div class="paragraph"> -<p>Numeric literal value * 1024 * 1024 * 1024.</p> -</div> +<li><a href="#showcontrol_statement">3.50. SHOWCONTROL Statement</a> +<ul class="sectlevel3"> +<li><a href="#showcontrol_syntax">3.50.1. Syntax Description of SHOWCONTROL</a></li> +<li><a href="#showcontrol_examples">3.50.2. Examples of SHOWCONTROL</a></li> +</ul> </li> +<li><a href="#showddl_statement">3.51. SHOWDDL Statement</a> +<ul class="sectlevel3"> +<li><a href="#showddl_syntax">3.51.1. Syntax Description of SHOWDDL</a></li> +<li><a href="#showddl_considerations">3.51.2. Considerations for SHOWDDL</a></li> +<li><a href="#showddl_examples">3.51.3. Examples of SHOWDDL</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="examples">3.2.2. Examples</h4> -<div class="ulist"> -<ul> -<li> -<p>This example creates a table tlob1 with 2 columns and primary key on the c1.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">CREATE TABLE tlob1 (c1 INT NOT NULL, c2 BLOB, PRIMARY KEY (c1));</code></pre> -</div> -</div> </li> -<li> -<p>This example creates a table tlob2 with 3 columns and primary key on the c1.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">CREATE TABLE tlob2 (c1 INT NOT NULL, c2 BLOB, c3 CLOB, PRIMARY KEY (c1));</code></pre> -</div> -</div> +<li><a href="#showddl_schema_statement">3.52. SHOWDDL SCHEMA Statement</a> +<ul class="sectlevel3"> +<li><a href="#showddl_schema_syntax">3.52.1. Syntax Description for SHOWDDL SCHEMA</a></li> +<li><a href="#showddl_schema_considerations">3.52.2. Considerations for SHOWDDL SCHEMA</a></li> +<li><a href="#showddl_schema_examples">3.52.3. Examples of SHOWDDL SCHEMA</a></li> +</ul> </li> -<li> -<p>This example creates a table tlob130txt_limit50 with 2 columns and primary key on the c1.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">CREATE TABLE tlob130txt_limit50 (c1 INT NOT NULL, c2 CLOB(50), PRIMARY KEY (c1));</code></pre> -</div> -</div> +<li><a href="#_showstats_statement">3.53. SHOWSTATS Statement</a> +<ul class="sectlevel3"> +<li><a href="#showstats_syntax">3.53.1. Syntax Description of SHOWSTATS</a></li> +<li><a href="#showstats_considerations">3.53.2. Considerations for SHOWSTATS</a></li> +<li><a href="#showstats_examples">3.53.3. Examples of SHOWSTATS</a></li> +</ul> </li> -<li> -<p>This example creates a table tlob130bin_limit1K with 2 columns and primary key on the c1.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">CREATE TABLE tlob130bin_limit1K (c1 INT NOT NULL, c2 BLOB(1 K), PRIMARY KEY (c1));</code></pre> -</div> -</div> +<li><a href="#table_statement">3.54. TABLE Statement</a> +<ul class="sectlevel3"> +<li><a href="#table_syntax">3.54.1. Syntax Description of Table Statement</a></li> +<li><a href="#_considerations_for_table">3.54.2. Considerations for TABLE</a></li> +<li><a href="#_examples_of_table_statement">3.54.3. Examples of TABLE Statement</a></li> +</ul> </li> -<li> -<p>This example creates a table tlob130ext with 4 columns and primary key on the c1.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">CREATE TABLE tlob130ext (c1 INT NOT NULL, c2 BLOB, c3 CLOB, c4 BLOB STORAGE 'EXTERNAL', PRIMARY KEY (c1));</code></pre> -</div> -</div> +<li><a href="#unregister_user_statement">3.55. UNREGISTER USER Statement</a> +<ul class="sectlevel3"> +<li><a href="#unregister_user_syntax">3.55.1. Syntax Description of UNREGISTER USER</a></li> +<li><a href="#unregister_user_considerations">3.55.2. Considerations for UNREGISTER USER</a></li> +<li><a href="#unregister_user_examples">3.55.3. Examples of UNREGISTER USER</a></li> +</ul> </li> +<li><a href="#update_statement">3.56. UPDATE Statement</a> +<ul class="sectlevel3"> +<li><a href="#update_syntax">3.56.1. Syntax Description of UPDATE</a></li> +<li><a href="#update_considerations">3.56.2. Considerations for UPDATE</a></li> +<li><a href="#update_examples">3.56.3. Examples of UPDATE</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="_hdfs_location_of_lob_data">3.2.3. HDFS Location of LOB Data</h4> -<div class="paragraph"> -<p>When a LOB table is created, the underlying LOB data needs to be stored in HDFS. It is in the /user/trafodion/lobs by default.</p> -</div> -<div class="paragraph"> -<p>All columns of a table that are declared as LOB types will have all their data in one file derived from the table’s Object UID and the LOB number of that column which gets assigned during creation.</p> -</div> -<div class="paragraph"> -<p>The following is a LOB file with 2 columns you will see 2 files in HDFS:</p> -</div> -<div class="paragraph"> -<p>/user/trafodion/lobs/LOBP_03683514167332904796_0001</p> -</div> -<div class="paragraph"> -<p>/user/trafodion/lobs/LOBP_03683514167332904796_0002</p> -</div> -<div class="paragraph"> -<p>As rows are added to this table, the LOB data for each row gets appended to the corresponding columnâs LOB data file.</p> -</div> -</div> -</div> -<div class="sect2"> -<h3 id="_inserting_into_a_sql_table_containing_lob_columns">3.3. Inserting into a SQL Table Containing LOB Columns</h3> -<div class="sect3"> -<h4 id="syntax">3.3.1. Syntax</h4> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO table-name [(target-col-list)] insert-source</code></pre> -</div> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">target-col-list is: -colname[, colname]... - -insert-source is: -VALUES(column-expr[, column-expr]...) - -column-expr is: -lob-query-expr | -other-query-expr - -lob-query-expr is: -NULL | ? | -EMPTY_BLOB() | -EMPTY_CLOB() | -STRINGTOLOB('string literal expression') | -FILETOLOB('lob source file name') | -BUFFERTOLOB(LOCATION lob source buffer address, LENGTH lob length value) | -EXTERNALTOLOB('external lob source file name') - -lob source file name is: -hdfs:///{local hdfs file name} | -{local linux file name} | -{file:///linux file name} - -external lob source file name is: -hdfs:///{local hdfs file name}</code></pre> -</div> -</div> -</div> -<div class="sect3"> -<h4 id="semantics">3.3.2. Semantics</h4> -<div class="ulist"> -<ul> -<li> -<p><code><em>other-query-expr</em></code></p> -<div class="paragraph"> -<p>For the syntax and description of <code><em>other-query-expr</em></code>, see the <code><em>query-expr</em></code> in the <a href="#select_statement">SELECT Statement</a>.</p> -</div> </li> -<li> -<p><code><em>EMPTY_BLOB(), EMPTY_CLOB()</em></code></p> -<div class="paragraph"> -<p>Returns an empty LOB handle.</p> -</div> +<li><a href="#upsert_statement">3.57. UPSERT Statement</a> +<ul class="sectlevel3"> +<li><a href="#upsert_syntax">3.57.1. Syntax Description of UPSERT</a></li> +<li><a href="#upsert_examples">3.57.2. Examples of UPSERT</a></li> +</ul> </li> -<li> -<p><code><em>STRINGTOLOB</em></code></p> -<div class="paragraph"> -<p>Converts a simple string literal into LOB format.</p> -</div> -<div class="ulist"> -<ul> -<li> -<p><code><em>string literal expression</em></code></p> -<div class="paragraph"> -<p>is a series of characters enclosed in single quotes.</p> -</div> +<li><a href="#values_statement">3.58. VALUES Statement</a> +<ul class="sectlevel3"> +<li><a href="#values_syntax">3.58.1. Syntax Description of VALUES</a></li> +<li><a href="#values_considerations">3.58.2. Considerations for VALUES</a></li> +<li><a href="#values_examples">3.58.3. Examples of VALUES</a></li> +</ul> </li> </ul> -</div> </li> -<li> -<p><code><em>FILETOLOB</em></code></p> -<div class="paragraph"> -<p>Converts data from a local linux/hdfs file into LOB format.</p> -</div> +<li><a href="#sql_utilities">4. SQL Utilities</a> +<ul class="sectlevel2"> +<li><a href="#cleanup_statement">4.1. CLEANUP Statement</a> +<ul class="sectlevel3"> +<li><a href="#cleanup_syntax">4.1.1. Syntax Description of CLEANUP Statement</a></li> +<li><a href="#cleanup_considerations">4.1.2. Considerations for CLEANUP Statement</a></li> +<li><a href="#cleanup_examples">4.1.3. Examples of CLEANUP Statement</a></li> +</ul> </li> -<li> -<p><code><em>BUFFERTOLOB</em></code></p> -<div class="paragraph"> -<p>Takes an address and a size of an input buffer, and converts the data pointed to by that buffer into LOB.</p> -</div> -<div class="ulist"> -<ul> -<li> -<p><code><em>lob source buffer address</em></code></p> -<div class="paragraph"> -<p>The long value of the user buffer address in int64.</p> -</div> +<li><a href="#load_statement">4.2. LOAD Statement</a> +<ul class="sectlevel3"> +<li><a href="#load_syntax">4.2.1. Syntax Description of LOAD</a></li> +<li><a href="#load_considerations">4.2.2. Considerations for LOAD</a></li> +<li><a href="#load_examples">4.2.3. Examples of LOAD</a></li> +</ul> </li> -<li> -<p><code><em>lob length value</em></code></p> -<div class="paragraph"> -<p>The length of the user specified lob buffer in int64.</p> -</div> +<li><a href="#populate_index_utility">4.3. POPULATE INDEX Utility</a> +<ul class="sectlevel3"> +<li><a href="#populate_index_syntax">4.3.1. Syntax Description of POPULATE INDEX</a></li> +<li><a href="#populate_index_considerations">4.3.2. Considerations for POPULATE INDEX</a></li> +<li><a href="#populate_index_examples">4.3.3. Examples of POPULATE INDEX</a></li> +</ul> </li> +<li><a href="#purgedata_utility">4.4. PURGEDATA Utility</a> +<ul class="sectlevel3"> +<li><a href="#purgedata_syntax">4.4.1. Syntax Description of PURGEDATA</a></li> +<li><a href="#purgedata_considerations">4.4.2. Considerations for PURGEDATA</a></li> +<li><a href="#purgedata_examples">4.4.3. Examples of PURGEDATA</a></li> </ul> -</div> </li> +<li><a href="#unload_statement">4.5. UNLOAD Statement</a> +<ul class="sectlevel3"> +<li><a href="#unload_syntax">4.5.1. Syntax Description of UNLOAD</a></li> +<li><a href="#unload_considerations">4.5.2. Considerations for UNLOAD</a></li> +<li><a href="#unload_examples">4.5.3. Examples of UNLOAD</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="considerations">3.3.3. Considerations</h4> -<div class="paragraph"> -<p>The source for inserting into a LOB can be any of the following:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>A parameter.</p> -<div class="paragraph"> -<p>An unnamed parameter can be used to prepare a statement and then during an execution, either a function or a simple string parameter can be passed in which will be converted to LOB data.</p> -</div> </li> -<li> -<p><code>EMPTY_BLOB()</code> or <code>EMPTY_CLOB()</code></p> -<div class="ulist"> -<ul> -<li> -<p>If <code>EMPTY_BLOB()</code> or <code>EMPTY_CLOB()</code> is specified, then a dummy lob handle is created.</p> -<div class="ulist"> -<ul> -<li> -<p>No data is associated with the empty LOBs yet, but these dummy LOB handles can later be used to populate with new LOB data. If the LOB had data previously associated with it, it will be erased.</p> +<li><a href="#update_statistics_statement">4.6. UPDATE STATISTICS Statement</a> +<ul class="sectlevel3"> +<li><a href="#update_statistics_syntax">4.6.1. Syntax Description of UPDATE STATISTICS</a></li> +<li><a href="#update_statistics_considerations">4.6.2. Considerations for UPDATE STATISTICS</a></li> +<li><a href="#update_statistics_examples">4.6.3. Examples of UPDATE STATISTICS</a></li> +</ul> </li> -<li> -<p>The dummy LOB handle will get the same datatype as the underlying column.</p> -<div class="paragraph"> -<p>For example, if the LOB column was defined as <code>'EXTERNAL'</code> during table creation, then the LOB column gets that type. If itâs not defined, then it is considered as a regular LOB.</p> -</div> +</ul> </li> +<li><a href="#sql_language_elements">5. SQL Language Elements</a> +<ul class="sectlevel2"> +<li><a href="#_authorization_ids">5.1. Authorization IDs</a></li> +<li><a href="#character_sets">5.2. Character Sets</a></li> +<li><a href="#columns">5.3. Columns</a> +<ul class="sectlevel3"> +<li><a href="#column_references">5.3.1. Column References</a></li> +<li><a href="#derived_column_names">5.3.2. Derived Column Names</a></li> +<li><a href="#column_default_settings">5.3.3. Column Default Settings</a></li> </ul> -</div> </li> -<li> -<p>An empty LOB is distinct from a LOB containing a string of length zero or a null LOB.</p> +<li><a href="#constraints">5.4. Constraints</a> +<ul class="sectlevel3"> +<li><a href="#creating_or_adding_constraints_on_sql_tables">5.4.1. Creating or Adding Constraints on SQL Tables</a></li> +<li><a href="#constraint_names">5.4.2. Constraint Names</a></li> +</ul> </li> +<li><a href="#correlation_names">5.5. Correlation Names</a> +<ul class="sectlevel3"> +<li><a href="#explicit_correlation_names">5.5.1. Explicit Correlation Names</a></li> +<li><a href="#implicit_correlation_names">5.5.2. Implicit Correlation Names</a></li> +<li><a href="#examples_of_correlation_names">5.5.3. Examples of Correlation Names</a></li> </ul> -</div> </li> -<li> -<p>An in-memory LOB which is simple string data.</p> -<div class="paragraph"> -<p>To insert a string literal, you need to provide <code>STRINGTOLOB('string literal expression')</code>.</p> -</div> +<li><a href="#database_objects">5.6. Database Objects</a> +<ul class="sectlevel3"> +<li><a href="#ownership">5.6.1. Ownership</a></li> +</ul> </li> -<li> -<p>An on-platform file (linux/hdfs file) containing binary or text data.</p> -<div class="paragraph"> -<p>To insert an on-platform file, you need to provide <code>FILETOLOB('lob source file name')</code>.</p> -</div> +<li><a href="#database_object_names">5.7. Database Object Names</a> +<ul class="sectlevel3"> +<li><a href="#logical_names_for_sql_objects">5.7.1. Logical Names for SQL Objects</a></li> +<li><a href="#sql_object_namespaces">5.7.2. SQL Object Namespaces</a></li> +</ul> </li> -<li> -<p>A user buffer of a specified length allocated in user space.</p> -<div class="paragraph"> -<p>To insert a buffer, you need to provide the address and size of the buffer.</p> -</div> +<li><a href="#data_types">5.8. Data Types</a> +<ul class="sectlevel3"> +<li><a href="#comparable_and_compatible_data_types">5.8.1. Comparable and Compatible Data Types</a></li> +<li><a href="#character_string_data_types">5.8.2. Character String Data Types</a></li> +<li><a href="#datetime_data_types">5.8.3. Datetime Data Types</a></li> +<li><a href="#interval_data_types">5.8.4. Interval Data Types</a></li> +<li><a href="#numeric_data_types">5.8.5. Numeric Data Types</a></li> +</ul> </li> -<li> -<p>An external LOB.</p> -<div class="paragraph"> -<p>When an external LOB is specified via <code>EXTERNALTOLOB('external lob source file name')</code>, the data associated with the external HDFS file is not transferred into the Trafodion LOB. Instead, Trafodion stores the file path/handle of the external file.</p> -</div> -<div class="paragraph"> -<p>For example, if you have a directory of pictures, you can specify the full hdfs path to each picture file to this function and the path will get stored in the Trafodion table. Later during retrieval, the file name will be used to go to the actual file to retrieve the data.</p> -</div> +<li><a href="#expressions">5.9. Expressions</a> +<ul class="sectlevel3"> +<li><a href="#character_value_expressions">5.9.1. Character Value Expressions</a></li> +<li><a href="#datetime_value_expressions">5.9.2. Datetime Value Expressions</a></li> +<li><a href="#interval_value_expressions">5.9.3. Interval Value Expressions</a></li> +<li><a href="#numeric_value_expressions">5.9.4. Numeric Value Expressions</a></li> +</ul> </li> +<li><a href="#identifiers">5.10. Identifiers</a> +<ul class="sectlevel3"> +<li><a href="#regular_identifiers">5.10.1. Regular Identifiers</a></li> +<li><a href="#delimited_identifiers">5.10.2. Delimited Identifiers</a></li> +<li><a href="#case_insensitive_delimited_identifiers">5.10.3. Case-Insensitive Delimited Identifiers</a></li> +<li><a href="#examples_of_identifiers">5.10.4. Examples of Identifiers</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="examples">3.3.4. Examples</h4> -<div class="ulist"> -<ul> -<li> -<p>This example uses the <code>STRINGTOLOB</code> function that converts a simple string literal into LOB format before inserting.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO tlob1 VALUES(1,stringtolob('inserted row'));</code></pre> -</div> -</div> </li> -<li> -<p>This example uses the <code>FILETOLOB</code> function that converts data from a local file into LOB format, and stores all data into HDFS associated with that value.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO tlob130txt1 VALUES(1,filetolob('lob_input_a1.txt'));</code></pre> -</div> -</div> +<li><a href="#identity_column">5.11. Identity Column</a> +<ul class="sectlevel3"> +<li><a href="#syntax_description_of_identity_column">5.11.1. Syntax Description of Identity Column</a></li> +<li><a href="#examples_of_identity_column">5.11.2. Examples of Identity Column</a></li> +</ul> </li> -<li> -<p>This example takes an int64 value as an input which is an address to a buffer and a size parameter. The buffer contents are converted to LOB format and stored in HDFS.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO tlob1 VALUES (1, buffertolob(LOCATION 124647474, SIZE 2048));</code></pre> -</div> -</div> +<li><a href="#indexes">5.12. Indexes</a> +<ul class="sectlevel3"> +<li><a href="#sql_indexes">5.12.1. SQL Indexes</a></li> +</ul> </li> -<li> -<p>This example uses different functions to convert strings, files, external lob into LOB data. The EXTERNALTOLOB function takes an external file.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO tlob130ext VALUES(1, STRINGTOLOB('first lob'), -FILETOLOB('hdfs:///lobs/lob_input_a1.txt'), -EXTERNALTOLOB('hdfs:///lobs/lob_input_a1.txt'));</code></pre> -</div> -</div> +<li><a href="#keys">5.13. Keys</a> +<ul class="sectlevel3"> +<li><a href="#clustering_keys">5.13.1. Clustering Keys</a></li> +<li><a href="#syskey">5.13.2. SYSKEY</a></li> +<li><a href="#index_keys">5.13.3. Index Keys</a></li> +<li><a href="#primary_keys">5.13.4. Primary Keys</a></li> +</ul> </li> -<li> -<p>This example uses a parameter.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">PREPARE S FROM INSERT INTO t130lob2 VALUES (1, ?); -EXECUTE S USING 'fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll';</code></pre> -</div> -</div> +<li><a href="#literals">5.14. Literals</a> +<ul class="sectlevel3"> +<li><a href="#character_string_literals">5.14.1. Character String Literals</a></li> +<li><a href="#datetime_literals">5.14.2. Datetime Literals</a></li> +<li><a href="#interval_literals">5.14.3. Interval Literals</a></li> +<li><a href="#numeric_literals">5.14.4. Numeric Literals</a></li> +</ul> </li> -<li> -<p>This example uses the <code>EMPTY_BLOB</code> function to insert an empty lob and creates a dummy lob handle.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO t130lob2 VALUES (1, empty_blob());</code></pre> -</div> -</div> +<li><a href="#null">5.15. Null</a> +<ul class="sectlevel3"> +<li><a href="#using_null_versus_default_values">5.15.1. Using Null Versus Default Values</a></li> +<li><a href="#defining_columns_that_allow_or_prohibit_null">5.15.2. Defining Columns That Allow or Prohibit Null</a></li> +</ul> </li> +<li><a href="#predicates">5.16. Predicates</a> +<ul class="sectlevel3"> +<li><a href="#between_predicate">5.16.1. BETWEEN Predicate</a></li> +<li><a href="#comparison_predicates">5.16.2. Comparison Predicates</a></li> +<li><a href="#exists_predicate">5.16.3. EXISTS Predicate</a></li> +<li><a href="#in_predicate">5.16.4. IN Predicate</a></li> +<li><a href="#like_predicate">5.16.5. LIKE Predicate</a></li> +<li><a href="#regexp_predicate">5.16.6. REGEXP Predicate</a></li> +<li><a href="#null_predicate">5.16.7. NULL Predicate</a></li> +<li><a href="#quantified_comparison_predicates">5.16.8. Quantified Comparison Predicates</a></li> </ul> -</div> -</div> -</div> -<div class="sect2"> -<h3 id="_inserting_into_a_sql_table_containing_lob_columns_using_select_clause">3.4. Inserting into a SQL Table Containing LOB Columns Using Select Clause</h3> -<div class="sect3"> -<h4 id="syntax">3.4.1. Syntax</h4> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO target-table [(target-col-list-expr)] SELECT [source-col-list-expr] FROM source-table</code></pre> -</div> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">target-col-list-expr is: -target-colname[, target-colname]... - -target-colname is: -lob-column-name | -other-column-name - -source-col-list-expr is: -source-colname[, source-colname]... - -source-colname is: -hive varchar column name | -trafodion varchar column name | -trafodion char column name | -trafodion lob column name - -source-table is: -hive table | -trafodion table</code></pre> -</div> -</div> -</div> -<div class="sect3"> -<h4 id="semantics">3.4.2. semantics</h4> -<div class="ulist"> -<ul> -<li> -<p><code><em>target-col-list-expr</em></code></p> -<div class="paragraph"> -<p>names a single column or multiple columns enclosed in parentheses in the target table in which to insert values.</p> -</div> -<div class="paragraph"> -<p>The data type of each target column must be compatible with the data type of its corresponding source value.</p> -</div> -<div class="paragraph"> -<p>Within the list, each target column must have the same position as its associated source value.</p> -</div> -<div class="ulist"> -<ul> -<li> -<p><code><em>target-colname</em></code></p> -<div class="paragraph"> -<p>is a SQL identifier that specifies a target column name.</p> -</div> </li> +<li><a href="#privileges">5.17. Privileges</a></li> +<li><a href="#roles">5.18. Roles</a></li> +<li><a href="#schemas">5.19. Schemas</a> +<ul class="sectlevel3"> +<li><a href="#creating_and_dropping_schemas">5.19.1. Creating and Dropping Schemas</a></li> </ul> -</div> </li> -<li> -<p><code><em>source-col-list-expr</em></code></p> -<div class="paragraph"> -<p>names a single column or multiple columns enclosed in parentheses in the source table from which to get values.</p> -</div> -<div class="ulist"> -<ul> -<li> -<p><code><em>source-colname</em></code></p> -<div class="paragraph"> -<p>is a SQL identifier that specifies a source column name.</p> -</div> +<li><a href="#search_condition">5.20. Search Condition</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_search_condition">5.20.1. Considerations for Search Condition</a></li> +<li><a href="#examples_of_search_condition">5.20.2. Examples of Search Condition</a></li> +</ul> </li> +<li><a href="#subquery">5.21. Subquery</a> +<ul class="sectlevel3"> +<li><a href="#select_form_of_a_subquery">5.21.1. SELECT Form of a Subquery</a></li> +<li><a href="#using_subqueries_to_provide_comparison_values">5.21.2. Using Subqueries to Provide Comparison Values</a></li> +<li><a href="#nested_subqueries_when_providing_comparison_values">5.21.3. Nested Subqueries When Providing Comparison Values</a></li> +<li><a href="#correlated_subqueries_when_providing_comparison_values">5.21.4. Correlated Subqueries When Providing Comparison Values</a></li> </ul> -</div> </li> +<li><a href="#tables">5.22. Tables</a> +<ul class="sectlevel3"> +<li><a href="#base_tables_and_views">5.22.1. Base Tables and Views</a></li> +<li><a href="#example_of_a_base_table">5.22.2. Example of a Base Table</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="considerations">3.4.3. Considerations</h4> -<div class="paragraph"> -<p>When inserting from a source Trafodion table column into a target table, the source column subtype of the LOB column needs to match the target table column. That is, you cannot insert from an external LOB column into a regular LOB column in the target. They both need to be the same type.</p> -</div> -<div class="paragraph"> -<p>The source for the <strong>select clause</strong> can be any of the following:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>A source hive table column that is a hive varchar column</p> </li> -<li> -<p>A source table column that is a Trafodion varchar, char and LOB column</p> +<li><a href="#views">5.23. Views</a> +<ul class="sectlevel3"> +<li><a href="#sql_views">5.23.1. SQL Views</a></li> +<li><a href="#example_of_a_view">5.23.2. Example of a View</a></li> +</ul> </li> -<li> -<p>A source table column that is also a LOB datatype.</p> +</ul> </li> +<li><a href="#sql_clauses">6. SQL Clauses</a> +<ul class="sectlevel2"> +<li><a href="#default_clause">6.1. DEFAULT Clause</a> +<ul class="sectlevel3"> +<li><a href="#syntax_for_default_clause">6.1.1. Syntax for Default Clause</a></li> +<li><a href="#examples_of_default">6.1.2. Examples of DEFAULT</a></li> </ul> -</div> -</div> -<div class="sect3"> -<h4 id="examples">3.4.4. Examples</h4> -<div class="ulist"> -<ul> -<li> -<p>This example inserts the first 10 rows of d_date_sk and d_date_id selected from the source hive table hive.hive.date_dim into the target table t130lob2.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO t130lob2 SELECT [first 10] d_date_sk,d_date_id FROM hive.hive.date_dim;</code></pre> -</div> -</div> </li> -<li> -<p>This example inserts the c1 and c2 selected from the source Trafodion table t130var into the c1 and c2 of the target table t130lob2.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO t130lob2(c1,c2) SELECT c1,c2 FROM t130var;</code></pre> -</div> -</div> +<li><a href="#format_clause">6.2. FORMAT Clause</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_date_formats">6.2.1. Considerations for Date Formats</a></li> +<li><a href="#examples_of_format">6.2.2. Examples of FORMAT</a></li> +</ul> </li> -<li> -<p>This example inserts the c1 and c2 selected from the source Trafodion table t130char into the c1 and c2 of the target table t130lob2.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO t130lob2(c1,c2) SELECT c1,c2 FROM t130char;</code></pre> -</div> -</div> +<li><a href="#sample_clause">6.3. SAMPLE Clause</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_sample">6.3.1. Considerations for SAMPLE</a></li> +<li><a href="#examples_of_sample">6.3.2. Examples of SAMPLE</a></li> +</ul> </li> -<li> -<p>This example inserts the c1, c2 and c3 selected from the source Trafodion table t130lob2 into the target table t130lob3, and then shows the result.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">INSERT INTO t130lob3 SELECT c1,c2,c3 FROM t130lob2; -SELECT c1,lobtostring(c2,100),lobtostring(c3,100) FROM t130lob3;</code></pre> -</div> -</div> +<li><a href="#sequence_by_clause">6.4. SEQUENCE BY Clause</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_sequence_by">6.4.1. Considerations for SEQUENCE BY</a></li> +<li><a href="#examples_of_sequence_by">6.4.2. Examples of SEQUENCE BY</a></li> +</ul> </li> +<li><a href="#transpose_clause">6.5. TRANSPOSE Clause</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_transpose">6.5.1. Considerations for TRANSPOSE</a></li> +<li><a href="#examples_of_transpose">6.5.2. Examples of TRANSPOSE</a></li> </ul> -</div> -</div> -</div> -<div class="sect2"> -<h3 id="_updating_a_sql_table_containing_lob_columns">3.5. Updating a SQL Table Containing LOB Columns</h3> -<div class="paragraph"> -<p>The source for updating a LOB can be divided into 2 groups:</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>Update using parameters/functions</p> </li> -<li> -<p>Update using LOB handle</p> +</ul> </li> +<li><a href="#sql_functions_and_expressions">7. SQL Functions and Expressions</a> +<ul class="sectlevel2"> +<li><a href="#standard_normalization">7.1. Standard Normalization</a></li> +<li><a href="#_aggregate_set_functions">7.2. Aggregate (Set) Functions</a></li> +<li><a href="#character_string_functions">7.3. Character String Functions</a></li> +<li><a href="#datetime_functions">7.4. Datetime Functions</a></li> +<li><a href="#mathematical_functions">7.5. Mathematical Functions</a></li> +<li><a href="#encryption_functions">7.6. Encryption Functions</a></li> +<li><a href="#sequence_functions">7.7. Sequence Functions</a> +<ul class="sectlevel3"> +<li><a href="#difference_sequence_functions">7.7.1. Difference sequence functions</a></li> +<li><a href="#moving_sequence_functions">7.7.2. Moving sequence functions</a></li> +<li><a href="#running_sequence_functions">7.7.3. Running sequence functions</a></li> +<li><a href="#other_sequence_functions">7.7.4. Other sequence functions</a></li> </ul> -</div> -<div class="sect3"> -<h4 id="_updating_using_parameters_functions">3.5.1. Updating Using Parameters/Functions</h4> -<div class="paragraph"> -<p>The following parameters/functions can be used to update.</p> -</div> -<div class="ulist"> -<ul> -<li> -<p>A parameter</p> </li> -<li> -<p><code>EMPTY_BLOB</code> or <code>EMPTY_CLOB</code></p> +<li><a href="#other_functions_and_expressions">7.8. Other Functions and Expressions</a></li> +<li><a href="#abs_function">7.9. ABS Function</a> +<ul class="sectlevel3"> +<li><a href="#examples_of_abs">7.9.1. Examples of ABS</a></li> +</ul> </li> -<li> -<p>An in-memory lob which is a simple string data</p> -<div class="paragraph"> -<p>To insert this string, a literal needs to be provided</p> -</div> +<li><a href="#acos_function">7.10. ACOS Function</a> +<ul class="sectlevel3"> +<li><a href="#examples_of_acos">7.10.1. Examples of ACOS</a></li> +<li><a href="#add_months_function">7.10.2. ADD_MONTHS Function</a></li> +<li><a href="#examples_of_add_months">7.10.3. Examples of ADD_MONTHS</a></li> +</ul> </li> -<li> -<p>An on-platform file (linux/hdfs file) containing text or binary data</p> +<li><a href="#ascii_function">7.11. ASCII Function</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_ascii">7.11.1. Considerations For ASCII</a></li> +<li><a href="#examples_of_ascii">7.11.2. Examples of ASCII</a></li> +</ul> </li> -<li> -<p>A user buffer of a specified length allocated in user space</p> +<li><a href="#asin_function">7.12. ASIN Function</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_ascii">7.12.1. Considerations for ASCII</a></li> +<li><a href="#examples_of_ascii">7.12.2. Examples of ASCII</a></li> +</ul> </li> -<li> -<p>An external LOB file in HDFS</p> +<li><a href="#asin_function">7.13. ASIN Function</a> +<ul class="sectlevel3"> +<li><a href="#examples_of_asin">7.13.1. Examples of ASIN</a></li> +</ul> </li> +<li><a href="#atan_function">7.14. ATAN Function</a> +<ul class="sectlevel3"> +<li><a href="#examples_of_atan">7.14.1. Examples of ATAN</a></li> </ul> -</div> -<div class="sect4"> -<h5 id="syntax">Syntax</h5> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE table-name {set-clause-type1 | set-clause-type2}</code></pre> -</div> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">set-clause-type1 is: -SET set-clause[, set-clause ].. - -set-clause is: -lob_column-name = {lob_query-expr} - -lob_query-expr is: -NULL | ? | -EMPTY_BLOB() | -EMPTY_CLOB() | -STRINGTOLOB('string literal expression'[, APPEND]) | -FILETOLOB('lob source file name'[, APPEND]) | -BUFFERTOLOB(LOCATION lob source buffer address, LENGTH lob length value[, APPEND]) | -EXTERNALTOLOB('external lob source file name'[, APPEND]) - -lob source file name is: -hdfs:///{local hdfs file name} | -{local linux file name} | -{file:///linux file name} - -external lob source file name is: -hdfs:///{local hdfs file name}</code></pre> -</div> -</div> -</div> -<div class="sect4"> -<h5 id="semantics">Semantics</h5> -<div class="paragraph"> -<p>For more information, see Semantics in <a href="#inserting into a sql table containing lob columns">Inserting into a SQL Table Containing LOB Columns</a>.</p> -</div> -</div> -<div class="sect4"> -<h5 id="examples">Examples</h5> -<div class="ulist"> -<ul> -<li> -<p>In the table tlob1where c1 is 3, updates (appends) the value of c2 to lob_update.txt.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE tlob1 SET c2=filetolob('lob_update.txt', append) WHERE c1 = 3;</code></pre> -</div> -</div> </li> -<li> -<p>In the table tlob1, updates (overwrites) the value of c2 to anoush.jpg.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE tlob1 SET c2=filetolob('anoush.jpg');</code></pre> -</div> -</div> +<li><a href="#atan2_function">7.15. ATAN2 Function</a> +<ul class="sectlevel3"> +<li><a href="#examples_of_atan2">7.15.1. Examples of ATAN2</a></li> +</ul> </li> -<li> -<p>In the table tlob1 where c1 is 3, updates (appends) the value of c2 to lob_update.txt stored in hdfs:///lobs/.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE tlob1 SET c2=filetolob('hdfs:///lobs/lob_update.txt', append) WHERE c1 = 3;</code></pre> -</div> -</div> +<li><a href="#authname_function">7.16. AUTHNAME Function</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_authname">7.16.1. Considerations for AUTHNAME</a></li> +<li><a href="#examples_of_authname">7.16.2. Examples of AUTHNAME</a></li> +</ul> </li> -<li> -<p>In the table tlob1, updates (overwrites) the value of c2 to the buffer location at 1254674 with 4000-byte length.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE tlob1 SET c2=buffertolob(LOCATION 12546474, SIZE 4000);</code></pre> -</div> -</div> +<li><a href="#avg_function">7.17. AVG Function</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_avg">7.17.1. Considerations for AVG</a></li> +</ul> </li> -<li> -<p>In the table tlob130ext where c1 is 2, updates (overwrites) the value of c4 to lob_input_d1.txt stored in hdfs:///lobs/.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE tlob130ext SET c4=externaltolob('hdfs:///lobs/lob_input_d1.txt') WHERE c1=2;</code></pre> -</div> -</div> +<li><a href="#bitand_function">7.18. BITAND Function</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_bitand">7.18.1. Considerations for BITAND</a></li> +<li><a href="#examples_of_bitand">7.18.2. Examples of BITAND</a></li> +</ul> </li> -<li> -<p>In the table t130lob2 where c1 is 1, updates (overwrites) value of the c2 to xyxyxyxyxyxyx.</p> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">PREPARE S FROM UPDATE t130lob2 SET c2=? WHERE c1 =1; -EXECUTE S USING 'xyxyxyxyxyxyx';</code></pre> -</div> -</div> +<li><a href="#case_expression">7.19. CASE (Conditional) Expression</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_case">7.19.1. Considerations for CASE</a></li> +<li><a href="#examples_of_case">7.19.2. Examples of CASE</a></li> +</ul> </li> +<li><a href="#cast_expression">7.20. CAST Expression</a> +<ul class="sectlevel3"> +<li><a href="#considerations_for_cast">7.20.1. Considerations for CAST</a></li> +<li><a href="#examples_of_cast">7.20.2. Examples of CAST</a></li> </ul> -</div> -</div> -</div> -<div class="sect3"> -<h4 id="_updating_using_lob_handle">3.5.2. Updating Using Lob Handle</h4> -<div class="paragraph"> -<p>A LOB handle is specified to the update (similar to extract).</p> -</div> -<div class="sect4"> -<h5 id="syntax">Syntax</h5> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">UPDATE LOB (LOB 'lob handle', lob update expression)</code></pre> -</div> -</div> -<div class="listingblock"> -<div class="content"> -<pre class="CodeRay highlight"><code data-lang="text">lob update expression is: -LOCATION lob source buffer address, LENGTH lob length value [, APPEND]) | -EMPTY_BLOB() | -EMPTY_CLOB()</code></pre> -</div> -</div> -<div class="paragraph"> -<p>For more information about examples, see <a href="http://trafodion.incubator.apache.org/docs/jdbct4ref_guide/index.html";><strong>Trafodion JDBC Type 4 Programmerâs Reference Guide</strong></a>.</p> -</div> -</div> -</div> -<div class="sect3"> -<h4 id="considerations">3.5.3. Considerations</h4> -<div class="ulist"> -<ul> -<li> -<p>The keyword <code>APPEND</code> can be used to append to lob data that may already exist in a LOB column. If it is not specified, the data is overwritten.</p> </li> -<li> -<p>When a LOB column is updated or overwritten, a new section is written into the LOB data file. The old section remains in HDFS but w
<TRUNCATED>
