trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From gtap...@apache.org
Subject [03/15] incubator-trafodion git commit: Major reorganization of the Client Installation Guide.
Date Thu, 03 Nov 2016 06:05:33 GMT
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/da748b4d/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 85e27f9..2bc2a6d 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -1,8495 +1,8509 @@
-////
-/**
-* @@@ START COPYRIGHT @@@
-*
-* Licensed to the Apache Software Foundation (ASF) under one
-* or more contributor license agreements.  See the NOTICE file
-* distributed with this work for additional information
-* regarding copyright ownership.  The ASF licenses this file
-* to you under the Apache License, Version 2.0 (the
-* "License"); you may not use this file except in compliance
-* with the License.  You may obtain a copy of the License at
-*
-*   http://www.apache.org/licenses/LICENSE-2.0
-*
-* Unless required by applicable law or agreed to in writing,
-* software distributed under the License is distributed on an
-* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-* KIND, either express or implied.  See the License for the
-* specific language governing permissions and limitations
-* under the License.
-*
-* @@@ END COPYRIGHT @@@
-*/
-////
-
-[[sql_statements]]
-= SQL Statements
-
-This section describes the syntax and semantics of {project-name} SQL statements.
-
-{project-name} SQL statements are entered interactively or from script files using a client-based tool, such as the
-{project-name} Command Interface (TrafCI). To install and configure a client application that enables you to connect
-to and use a {project-name} database, see the
-{docs-url}/client_install/index.html[_{project-name} Client Installation_ _Guide_].
-
-[[sql_statements_categories]]
-== Categories
-
-The statements are categorized according to their functionality:
-
-* <<data_definition_language_statements,Data Definition Language (DDL) Statements>>
-* <<data_manipulation_language_statements,Data Manipulation Language (DML) Statements>>
-* <<transaction_control_statements,Transaction Control Statements>>
-* <<data_control_and_security_statements,Data Control and Security Statements>>
-* <<stored_procedure_and_user_defined_function_statements,Stored Procedure and User-Defined Function Statements>>
-* <<prepared_statements,Prepared Statements>>
-* <<control_statements,Control Statements>>
-* <<object_naming_statements,Object Naming Statements>>
-* <<show_get_and_explain_statements,"SHOW, GET, and EXPLAIN Statements">>
-
-<<<
-[[data_definition_language_statements]]
-=== Data Definition Language (DDL) Statements
-
-Use these DDL statements to create, drop, or alter the definition of a {project-name} SQL schema or object.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run DDL statements inside a user-defined
-transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run these statements, AUTOCOMMIT must be turned ON
-(the default) for the session.
-
-[cols="2*", options="head{docs-url}/sql_reference/index.html#limitser"]
-|===
-| Statement                                                  | What It Does
-// | <<alter_library_statement,ALTER LIBRARY Statement>>        | Updates the physical filename for a library object in a {project-name} database.
-| <<alter_table_statement,ALTER TABLE Statement>>            | Changes attributes for a table.
-| <<alter_user_statement,ALTER USER Statement>>              | Changes attributes for a user.
-| <<create_function_statement,CREATE FUNCTION Statement>>    | Registers a user-defined function (UDF) written in C as a function within a {project-name} database.
-| <<create_index_statement,CREATE INDEX Statement>>          | Creates an index on a table.
-| <<create_library_statement,CREATE LIBRARY Statement>>      | Registers a library object in a {project-name} database.
-| <<create_procedure_statement,CREATE PROCEDURE Statement>>  | Registers a Java method as a stored procedure in Java (SPJ) within a {project-name} database.
-| <<create_role_statement,CREATE ROLE Statement>>            | Creates a role.
-| <<create_schema_statement,CREATE SCHEMA Statement>>        | Creates a schema in the database.
-| <<create_table_statement,CREATE TABLE Statement>>          | Creates a table.
-| <<create_view_statement,CREATE VIEW Statement>>            | Creates a view.
-| <<drop_function_statement,DROP FUNCTION Statement>>        | Removes a user-defined function (UDF) from the {project-name} database.
-| <<drop_index_statement,DROP INDEX Statement>>              | Drops an index.
-| <<drop_library_statement,DROP LIBRARY Statement>>          | Removes a library object from the {project-name} database and also removes the library file
-referenced by the library object.
-| <<drop_procedure_statement,DROP PROCEDURE Statement>>      | Removes a stored procedure in Java (SPJ) from the {project-name} database.
-| <<drop_role_statement,DROP ROLE Statement>>                | Drops a role.
-| <<drop_schema_statement,DROP SCHEMA Statement>>            | Drops a schema from the database.
-| <<drop_table_statement,DROP TABLE Statement>>              | Drops a table.
-| <<drop_view_statement,DROP VIEW Statement>>                | Drops a view.
-| <<register_user_statement,REGISTER USER Statement>>        | Registers a user in the SQL database, associating the user's login name
-with a database user name.
-| <<unregister_user_statement, UNREGISTER USER Statement>>   | Removes a database user name from the SQL database.
-|===
-
-
-<<<
-[[data_manipulation_language_statements]]
-=== Data Manipulation Language (DML) Statements
-
-Use these DML statements to delete, insert, select, or update rows in one or more tables:
-
-[cols="2*", options="header"]
-|===
-| Statement                               | What It Does
-| <<delete_statement,DELETE Statement>> | Deletes rows from a table or view.
-| <<insert_statement,INSERT Statement>> | Inserts data into tables and views.
-| <<merge_statement,MERGE Statement>>   | Either performs an upsert operation (that is, updates a table if the row
-exists or inserts into a table if the row does not exist) or updates (merges) matching rows from one table to another.
-| <<select_statement,SELECT Statement>> | Retrieves data from tables and views.
-| <<table_statement,TABLE Statement>>   | Equivalent to the query specification SELECT * FROM _table_
-| <<update_statement,UPDATE Statement>> | Updates values in columns of a table or view.
-| <<upsert_statement,UPSERT Statement>> | Updates a table if the row exists or inserts into a table if the row does not exist.
-| <<values_statement,VALUES Statement>> | Displays the results of the evaluation of the expressions and the results of row subqueries
-within the row value constructors.
-|===
-
-[[transaction_control_statements]]
-=== Transaction Control Statements
-
-Use these statements to specify user-defined transactions and to set attributes for the next transaction:
-
-[cols="2*",options="header"]
-|===
-| Statement                                                 | What It Does
-| <<begin_work_statement,BEGIN WORK Statement>>           | Starts a transaction.
-| <<commit_work_statement,COMMIT WORK Statement>>         | Commits changes made during a transaction and ends the transaction.
-| <<rollback_work_statement,ROLLBACK WORK Statement>>     | Undoes changes made during a transaction and ends the transaction.
-| <<set_transaction_statement,SET TRANSACTION Statement>> | Sets attributes for the next SQL transaction — whether to automatically
-commit database changes.
-|===
-
-<<<
-[[data_control_and_security_statements]]
-=== Data Control and Security Statements
-
-Use these statements to register users, create roles, and grant and revoke privileges:
-
-[cols="2*",options="header"]
-|===
-| Statement                                                                     | What It Does
-| <<alter_user_statement,ALTER USER Statement>>                                 | Changes attributes associated with a user who is registered in the database.
-| <<create_role_statement,CREATE ROLE Statement>>                               | Creates an SQL role.
-| <<drop_role_statement,DROP ROLE Statement>>                                   | Deletes an SQL role.
-| <<grant_statement,GRANT Statement>>                                           | Grants access privileges on an SQL object to specified users or roles.
-| <<grant_component_privilege_statement,GRANT COMPONENT PRIVILEGE Statement>>   | Grants one or more component privileges to a user or role.
-| <<grant_role_statement,GRANT ROLE Statement>>                                 | Grants one or more roles to a user.
-| <<register_user_statement,REGISTER USER Statement>>                           | Registers a user in the SQL database, associating the user's login name with a database user name.
-| <<revoke_statement,REVOKE Statement>>                                         | Revokes access privileges on an SQL object from specified users or roles.
-| <<revoke_component_privilege_statement,REVOKE COMPONENT PRIVILEGE Statement>> | Removes one or more component privileges from a user or role.
-| <<revoke_role_statement,REVOKE ROLE Statement>>                               | Removes one or more roles from a user.
-| <<unregister_user_statement,UNREGISTER USER Statement>>                       | Removes a database user name from the SQL database.
-|===
-
-<<<
-[[stored_procedure_and_user_defined_function_statements]]
-=== Stored Procedure and User-Defined Function Statements
-
-Use these statements to create and execute stored procedures in Java (SPJs) or create user-defined functions (UDFs) and to modify
-authorization to access libraries or to execute SPJs or UDFs:
-
-[cols="2*",options="header"]
-|===
-| Statement                                                 | What It Does
-// | <<alter_library_statement,ALTER LIBRARY Statement>>       | Updates the physical filename for a library object in a {project-name} database.
-| <<call_statement,CALL Statement>>                         | Initiates the execution of a stored procedure in Java (SPJ) in a {project-name} database.
-| <<create_function_statement,CREATE FUNCTION Statement>>   | Registers a user-defined function (UDF) written in C as a function within a {project-name} database.
-| <<create_library_statement,CREATE LIBRARY Statement>>     | Registers a library object in a {project-name} database.
-| <<create_procedure_statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a {project-name} database.
-| <<drop_function_statement,DROP FUNCTION Statement>>       | Removes a user-defined function (UDF) from the {project-name} database.
-| <<drop_library_statement,DROP LIBRARY Statement>>         | Removes a library object from the {project-name} database and also removes the library file
-referenced by the library object.
-| <<drop_procedure_statement,DROP PROCEDURE Statement>>     | Removes a stored procedure in Java (SPJ) from the {project-name} database.
-| <<grant_statement,GRANT Statement>>                       | Grants privileges for accessing a library object or executing an SPJ or UDF to specified users.
-| <<revoke_statement,REVOKE Statement>>                     | Revokes privileges for accessing a library object or executing an SPJ or UDF from specified users.
-UDF from specified users.
-|===
-
-[[prepared_statements]]
-=== Prepared Statements
-
-Use these statements to prepare and execute an SQL statement:
-
-[cols="2*",options="header"]
-|===
-| Statement                                                 | What It Does
-| <<execute_statement,EXECUTE Statement>>                   | Executes an SQL statement previously compiled by a PREPARE statement.
-| <<prepare_statement,PREPARE Statement>>                   | Compiles an SQL statement for later use with the EXECUTE statement in the same session.
-|===
-
-
-<<<
-[[control_statements]]
-=== Control Statements
-
-Use these statements to control the execution, default options, plans, and performance of DML statements:
-
-[cols="2*",options="header"]
-|===
-| Statement                                                                     | What It Does
-| <<control_query_cancel_statement,CONTROL QUERY CANCEL Statement>>             | Cancels an executing query that you identify with a query ID.
-| <<control_query_default_statement,CONTROL QUERY DEFAULT Statement>>           | Changes a default attribute to influence a query plan.
-|===
-
-[[object_naming_statements]]
-=== Object Naming Statements
-
-Use this statements to specify default ANSI names for the schema:
-
-[cols="2*",options="header"]
-|===
-| Statement                                        | What It Does
-| <<set_schema_statement,SET SCHEMA Statement>>    | Sets the default ANSI schema for unqualified object names for the current session.
-|===
-
-<<<
-[[show_get_and_explain_statements]]
-=== SHOW, GET, and EXPLAIN Statements
-
-Use these statements to display information about database objects or query execution plans:
-
-[cols="2*",options="header"]
-|===
-| Statement                                                               | What It Does
-| <<explain_statement,EXPLAIN Statement>>                                 | Displays information contained in the query execution plan.
-| <<get_statement,GET Statement>>                                         | Displays the names of database objects, components, component
-privileges, roles, or users that exist in the {project-name} instance.
-| <<get_hbase_objects_statement,GET HBASE OBJECTS Statement>>             | Displays a list of HBase objects through an SQL interface
-| <<get_version_of_metadata_statement,GET VERSION OF METADATA Statement>> | Displays the version of the metadata in the {project-name} instance and
-indicates if the metadata is current.
-| <<get_version_of_software_statement,GET VERSION OF SOFTWARE Statement>> | Displays the version of the {project-name} software that is installed on the
-system and indicates if it is current.
-| <<invoke_statement,INVOKE Statement>>                                   | Generates a record description that corresponds to a row in the
-specified table or view.
-| <<showcontrol_statement,SHOWCONTROL Statement>>                         | Displays the CONTROL QUERY DEFAULT attributes in effect.
-| <<showddl_statement,SHOWDDL Statement>>                                 | Describes the DDL syntax used to create an object as it exists in the
-metadata, or it returns a description of a user, role, or component in the form of a GRANT statement.
-| <<showddl_schema_statement,SHOWDDL SCHEMA Statement>>                   | Displays the DDL syntax used to create a schema as it exists in the
-metadata and shows the authorization ID that owns the schema.
-| <<showstats_statement,SHOWSTATS Statement>>                             | Displays the histogram statistics for one or more groups of columns
-within a table. These statistics are used to devise optimized access plans.
-
-|===
-
-////
-<<<
-[[alter_library_statement]]
-== ALTER LIBRARY Statement
-
-The ALTER LIBRARY statement updates the physical filename for a library object in a {project-name} database.
-A library object can be an SPJ's JAR file or a UDF's library file.
-
-ALTER LIBRARY is a {project-name} SQL extension.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement inside 
-user-defined transaction (`BEGIN WORK&#8230;COMMIT WORK`) or when AUTOCOMMIT is OFF. To run this statement, AUTOCOMMIT must be
-turned ON (the default) for the session.
-
-```
-ALTER LIBRARY [[catalog-name.]schema-name.]library-name
-   file library-filename
-   [host name host-name]
-   [local file host-filename]
-```
-
-[[alter_library_syntax]]
-=== Syntax Description of ALTER LIBRARY
-
-* `\[[_catalog-name_.]_schema-name_.]_library-name_`
-+
-specifies the ANSI logical name of the library object, where each part of the name is a valid sql identifier with a maximum of 128 characters.
-specify the name of a library object that has already been registered in the schema. if you do not fully qualify the library name, trafodion sql
-qualifies it according to the schema of the current session. for more information, see <<identifiers,identifiers>> and
-<<_database_object_names,database object names>>.
-
-* `file _library-filename_`
-+
-specifies the full path of the redeployed library file, which either an SPJ's jar file or a UDF's library file.
-
-* `host name _host-name_`
-+
-specifies the name of the client host machine where the deployed file resides.
-
-* `local file _host-filename_`
-+
-specifies the path on the client host machine where the deployed file is stored.
-
-<<<
-[[alter_library_considerations]]
-=== Considerations for ALTER LIBRARY
-
-* HOST NAME and LOCAL FILE are position dependent.
-
-==== Required Privileges
-
-To issue an ALTER LIBRARY statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are the owner of the library.
-* You have the ALTER or ALTER_LIBRARY component privilege for the SQL_OPERATIONS component.
-
-[[alter_library_examples]]
-=== Examples of ALTER LIBRARY
-
-* This ALTER LIBRARY statement updates the JAR file (SPJs) for a library named SALESLIB in the SALES schema:
-+
-```
-ALTER LIBRARY sales.saleslib FILE Sales2.jar;`
-```
-
-* This ALTER LIBRARY statement updates the library file (UDFs) for a library named MYUDFS in the default schema:
-+
-```
-ALTER LIBRARY myudfs FILE $TMUDFLIB;
-```
-////
-
-<<<
-[[alter_table_statement]]
-== ALTER TABLE Statement
-
-The ALTER TABLE statement changes a {project-name} SQL table. See <<Tables,Tables>>.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this
-statement inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF.
-To run this statement, AUTOCOMMIT must be turned ON (the default) for the session.
-
-```
-ALTER TABLE name alter-action
-
-alter-action is:
-
-     ADD [IF NOT EXISTS][COLUMN] column-definition
-   | ADD [CONSTRAINT constraint-name] table-constraint
-   | DROP CONSTRAINT constraint-name [RESTRICT]
-   | RENAME TO new-name
-   | DROP COLUMN [IF EXISTS] column-name
-
-column-definition is:
-
-   column-name data-type
-      ([DEFAULT default]
-         [[constraint constraint-name] column-constraint])
-
-data-type is:
-
-     char[acter] [(length)[characters]]
-         [CHARACTER SET char-set-name]
-         [UPSHIFT] [[not] casespecific]
-   | char[acter] varying (length)
-         [character set char-set-name]
-         [upshift] [[not] casespecific]
-   | varchar (length) [character set char-set-name]
-         [upshift] [[not] casespecific]
-   | numeric [(precision [,scale])] [signed|unsigned]
-   | nchar [(length) [character set char-set-name]
-         [upshift] [[not] casespecific]
-   | nchar varying(length) [character set char-set-name]
-         [upshift] [[not] casespecific]
-   | smallint [signed|unsigned]
-   | int[eger] [signed|unsigned]
-   | largeint
-   | dec[imal] [(precision [,scale])] [signed|unsigned]
-   | float [(precision)]
-   | real
-   | double precision
-   | date
-   | time [(time-precision)]
-   | timestamp [(timestamp-precision)]
-   | interval { start-field to end-field | single-field }
-
-default is:
-
-     literal
-   | null
-   | currentdate
-   | currenttime
-   | currenttimestamp }
-
-column-constraint is:
-
-     not null
-   | unique
-   | check (condition)
-   | references ref-spec
-
-table-constraint is:
-
-     unique (column-list)
-   | check (condition)
-   | foreign key (column-list) references ref-spec
-
-ref-spec is:
-
-   referenced-table [(column-list)]
-
-column-list is:
-
-   column-name[, column-name]...
-```
-
-<<<
-[[alter_table_syntax]]
-=== Syntax Description of ALTER TABLE
-
-* `_name_`
-+
-specifies the current name of the object. See <<database_object_names,Database Object Names>>.
-
-* `ADD [COLUMN] _column-definition_`
-+
-adds a column to _table_.
-+
-The clauses for the _column-definition_ are:
-
-** `_column-name_`
-+
-specifies the name for the new column in the table. _column-name_ is an SQL identifier. _column-name_ must be
-unique among column names in the table. If the column name is a {project-name} SQL reserved word, you must
-delimit it by enclosing it in double quotes. For example: `"sql".myview`. See <<Identifiers,Identifiers>>.
-
-** `_data-type_`
-+
-specifies the data type of the values that can be stored in _column-name_. See <<Data_Types,Data Types>>
-If a default is not specified, NULL is used.
-
-** `DEFAULT _default_`
-+
-specifies a default value for the column or specifies that the column does not have a default value. You can declare the default value
-explicitly by using the DEFAULT clause, or you can enable null to be used as the default by omitting both the DEFAULT and NOT NULL clauses.
-If you omit the DEFAULT clause and specify NOT NULL, {project-name} SQL returns an error. For existing rows of the table, the added column takes
-on its default value.
-+
-If you set the default to the datetime value CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP, {project-name} SQL uses January 1, 1 A.D.
-12:00:00.000000 as the default date and time for the existing rows.
-+
-For any row that you add after the column is added, if no value is specified for the column as part of the add row operation, the column
-receives a default value based on the current timestamp at the time the row is added.
-
-<<<
-** `[[constraint _constraint-name_] _column-constraint_]`
-+
-specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be
-unique among constraint names in its schema. if you omit the schema portions of the name you specify in _constraint-name_,
-trafodion sql expands the constraint name by using the schema for _table_. see <<database_object_names,database object names>>.
-+
-if you do not specify a constraint name, trafodion sql constructs an sql identifier as the name for the constraint in the schema
-for _table._ the identifier consists of the fully qualified table name concatenated with a system-generated unique identifier.
-for example, a constraint on table a.b.c might be assigned a name such as a.b.c_123&#8230;_01&#8230;.
-
-*** `_column-constraint_` options:
-
-**** `not null` 
-+
-is a column constraint that specifies that the column cannot contain nulls. if you omit not null, nulls are allowed in the column.
-if you specify both not null and no default, then each row inserted in the table must include a value for the column. see <<null,null>>.
-
-**** `unique`
-+
-is a column constraint that specifies that the column cannot contain more than one occurrence of the same value. if you omit unique,
-duplicate values are allowed unless the column is part of the primary key. columns that you define as unique must be specified as not null.
-
-**** `check (_condition_)`
-+
-is a constraint that specifies a condition that must be satisfied for each row in the table. see <<search_condition,search condition>>.
-you cannot refer to the current_date, current_time, or current_timestamp function in a check constraint, and you cannot use
-subqueries in a check constraint.
-
-<<<
-**** `references _ref-spec_`
-+
-specifies a references column constraint. the maximum combined length of the columns for a references constraint is 2048 bytes. +
-
-***** `_ref-spec_` is:
-+
-`_referenced-table_ [(_column-list_)]`
-+
-`_referenced-table_` is the table referenced by the foreign key in a referential constraint. _referenced-table_ cannot be a view.
-_referenced-table_ cannot be the same as _table_. _referenced-table_ corresponds to the foreign key in the _table_.
- +
-`_column-list_` specifies the column or set of columns in the _referenced-table_ that corresponds to the foreign key in _table_. the
-columns in the column list associated with references must be in the same order as the columns in the column list associated with foreign
-key. if _column-list_ is omitted, the referenced table's primary key columns are the referenced columns.
-+
-a table can have an unlimited number of referential constraints, and you can specify the same foreign key in more than one referential
-constraint, but you must define each referential constraint separately. you cannot create self-referencing foreign key constraints.
-
-* `add [constraint _constraint-name_] _table-constraint_`
-+
-adds a constraint to the table and optionally specifies _constraint-name_ as the name for the constraint. the new constraint
-must be consistent with any data already present in the table. 
-
-<<<
-** `constraint _constraint-name_`
-+
-specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be unique among constraint
-names in its schema. if you omit the schema portions of the name you specify in _constraint-name_, trafodion sql expands the constraint
-name by using the schema for table. see <<database_object_names,database object names>>. 
-+
-if you do not specify a constraint name, trafodion sql constructs an sql identifier as the name for the constraint in the schema for table. the
-identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. for example, a constraint on table
-a.b.c might be assigned a name such as a.b.c_123&#8230;_01&#8230;.
-+
-** `_table-constraint_` options:
-
-*** `unique (_column-list_)`
-+
-is a table constraint that specifies that the column or set of columns cannot contain more 
-than one occurrence of the same value or set of values.
-+
-`_column-list_` cannot include more than one occurrence of the same column. in addition, the set of columns that you specify on a unique
-constraint cannot match the set of columns on any other unique constraint for the table or on the primary key constraint for the table.
-all columns defined as unique must be specified as not null.
-+
-a unique constraint is enforced with a unique index. if there is already a unique index on _column-list_, trafodion sql uses that index. if a
-unique index does not exist, the system creates a unique index.
-
-*** `check (_condition_)`
-+
-is a constraint that specifies a condition that must be satisfied for each row in the table.
-see <<search_condition,search condition>>. you cannot refer to the current_date, current_time, or current_timestamp function in a check
-constraint, and you cannot use subqueries in a check constraint.
-
-*** `foreign key (_column-list_) references _ref-spec_ not enforced`
-+
-is a table constraint that specifies a referential constraint for the table, declaring that a column or set of columns (called a foreign key)
-in _table_ can contain only values that match those in a column or set of columns in the table specified in the references
-clause. however, because not enforced is specified, this relationship is not checked.
-+
-the two columns or sets of columns must have the same characteristics (data type, length, scale, precision). without the foreign key clause,
-the foreign key in _table_ is the column being defined; with the foreign key clause, the foreign key is the column or set of columns specified in
-the foreign key clause. for information about _ref-spec_, see references _ref-spec_ not enforced.
-
-<<<
-* `drop constraint _constraint-name_ [restrict]`
-+
-drops a constraint from the table. +
-+
-if you drop a constraint, trafodion sql drops its dependent index if trafodion sql originally created the same index. if the constraint uses
-an existing index, the index is not dropped. +
-
-** `constraint _constraint-name_`
-+
-specifies a name for the column or table constraint. _constraint-name_ must have the same schema as _table_ and must be unique among constraint
-names in its schema. if you omit the schema portions of the name you specify in _constraint-name_, trafodion sql expands the constraint
-name by using the schema for table. see <<database_object_names,database object names>>.
-+
-if you do not specify a constraint name, trafodion sql constructs an sql identifier as the name for the constraint in the schema for table. the
-identifier consists of the fully qualified table name concatenated with a system-generated unique identifier. for example, a constraint on table
-a.b.c might be assigned a name such as a.b.c_123&#8230;_01&#8230;.
-
-* `rename to _new-name_`
-+
-changes the logical name of the object within the same schema.
-
-** `_new-name_`
-+
-specifies the new name of the object after the rename to operation occurs.
-
-<<<
-* `add if not exists _column-definition_`
-+
-adds a column to _table_ if it does not already exist in the table.
-+
-the clauses for the _column-definition_ are the same as described in add [column] _column-definition_.
-
-* `drop column [if exists] _column-name_`
-+
-drops the specified column from _table_, including the column’s data. you cannot drop a primary key column.
-
-<<<
-[[alter_table_considerations]]
-=== Considerations for ALTER TABLE
-
-[[effect_of_adding_a_column_on_view_definitions]]
-==== Effect of Adding a Column on View Definitions
-
-The addition of a column to a table has no effect on existing view definitions. Implicit column references specified by SELECT * in view
-definitions are replaced by explicit column references when the definition clauses are originally evaluated.
-
-[[authorization_and_availability_requirements]]
-==== Authorization and Availability Requirements
-
-ALTER TABLE works only on user-created tables.
-
-===== Required Privileges
-
-To issue an ALTER TABLE statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are the owner of the table.
-* You have the ALTER or ALTER_TABLE component privilege for the SQL_OPERATIONS component.
-
-===== Privileges Needed to Create a Referential Integrity Constraint
-
-To create a referential integrity constraint (that is, a constraint on the table that refers to a column in another table), one of the
-following must be true:
-
-* You are DB ROOT.
-* You are the owner of the referencing and referenced tables.
-* You have these privileges on the referencing and referenced table:
-** For the referencing table, you have the ALTER or ALTER_TABLE component privilege for the SQL_OPERATIONS component.
-** For the referenced table, you have the REFERENCES (or ALL) privilege on the referenced table through your user name or through a granted role.
-
-If the constraint refers to the other table in a query expression, you must also have SELECT privileges on the other table.
-
-[[alter_table_examples]]
-===  Example of ALTER TABLE
-
-This example adds a column:
-
-```
-ALTER TABLE persnl.project
-   ADD COLUMN projlead
-      NUMERIC (4) UNSIGNED
-```
-
-<<<
-[[alter_user_statement]]
-== ALTER USER Statement
-
-The ALTER USER statement changes attributes associated with a user who is registered in the database.
-
-ALTER USER is a {project-name} SQL extension.
-
-```
-ALTER USER database-username alter-action[, alter-action]
-
-alter-action is:
-     SET EXTERNAL NAME directory-service-username
-   | SET { ONLINE | OFFLINE }
-```
-
-[[alter_user_syntax]]
-=== Syntax Description of ALTER USER
-
-* `_database-username_`
-+
-is the name of a currently registered database user.
-
-* `SET EXTERNAL NAME`
-+
-changes the name that identifies the user in the directory service. This is also the name the user specifies when
-connecting to the database.
-
-** `_directory-service-username_`
-+
-specifies the new name of the user in the directory service.
-
-* _directory-service-username_ is a regular or delimited case-insensitive
-identifier. See <<Case_Insensitive_Delimited_Identifiers,Case-Insensitive Delimited Identifiers>>.
-
-* SET { ONLINE | OFFLINE }
-+
-changes the attribute that controls whether the user is allowed to connect to the database. +
-
-** `ONLINE`
-+
-specifies that the user is allowed to connect to the database.
-
-** `OFFLINE`
-+
-specifies that the user is not allowed to connect to the database.
-
-<<<
-[[alter_user_considerations]]
-=== Considerations for ALTER USER
-
-Only a user with user administrative privileges (that is, a user who has been granted the MANAGE_USERS component privilege)
-can do the following:
-
-* Set the EXTERNAL NAME for any user
-* Set the ONLINE | OFFLINE attribute for any user
-
-Initially, DB_ROOT is the only database user who has been granted the MANAGE_USERS component privilege.
-
-[[alter_user_examples]]
-=== Examples of ALTER USER
-
-* To change a user's external name:
-+
-```
-ALTER USER ajones SET EXTERNAL NAME "Americas\ArturoJones";
-```
-
-* To change a user's attribute to allow the user to connect to the database:
-+
-```
-ALTER USER ajones SET ONLINE;
-```
-
-<<<
-[[begin_work_statement]]
-== BEGIN WORK Statement
-
-The BEGIN WORK statement enables you to start a transaction explicitly—where the transaction consists of the set of operations
-defined by the sequence of SQL statements that begins immediately after BEGIN WORK and ends with the next COMMIT or ROLLBACK
-statement. See <<Transaction_Management,Transaction Management>>. BEGIN WORK will raise an error if a transaction is currently active.
-
-BEGIN WORK is a {project-name} SQL extension.
-
-```
-BEGIN WORK
-```
-
-[[begin_work_considerations]]
-=== Considerations for BEGIN WORK
-
-BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction.
-
-[[begin_work_examples]]
-=== Example of BEGIN WORK
-
-Group three separate statements—two INSERT statements and an UPDATE statement—that update the database within a single transaction:
-
-```
---- This statement initiates a transaction.
-BEGIN WORK;
-
---- SQL operation complete.
-
-INSERT INTO sales.orders VALUES (125, DATE '2008-03-23', DAT '2008-03-30', 75, 7654);
-
---- 1 row(s) inserted.
-
-INSERT INTO sales.odetail VALUES (125, 4102, 25000, 2);
-
---- 1 row(s) inserted.
-
-UPDATE invent.partloc SET qty_on_hand = qty_on_hand - 2 WHERE partnum = 4102 AND loc_code = 'G45';
-
---- 1 row(s) updated.
-
---- This statement ends a transaction.
-COMMIT WORK;
-
---- SQL operation complete.
-```
-
-<<<
-[[call_statement]]
-== CALL Statement
-
-The CALL statement invokes a stored procedure in Java (SPJ) in a {project-name} SQL database.
-
-```
-CALL procedure-ref ([argument-list])
-
-procedure-ref is:
-   [[catalog-name.]schema-name.]procedure-name
-
-argument-list is:
-   sql-expression[, sql-expression]...
-```
-
-[[call_syntax]]
-=== Syntax Description of CALL
-
-* `_procedure-ref_`
-+
-specifies an ANSI logical name of the form:
-+
-`\[[_catalog-name_.]_schema-name_.]_procedure-name_`
-+
-where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, see
-<<identifiers,identifiers>> and <<database_object_names,database object names>>.
-+
-if you do not fully qualify the procedure name, trafodion sql qualifies it according to the schema of the current session.
-
-* `_argument-list_`
-+
-accepts arguments for in, in-out, or out parameters. the arguments consist of sql expressions, including dynamic parameters,
-separated by commas:
-+
-`_sql-expression_[{, _sql-expression_}&#8230;]`
-+
-<<<
-+
-each expression must evaluate to a value of one of these data types:
-+
-** character value
-** date-time value
-** numeric value
-+
-interval value expressions are disallowed in SPJs. for more information, see
-<<call_input_parameter_arguments,input parameter arguments>> and
-<<call_output_parameter_arguments,output parameter arguments>>.
-+
-do not specify result sets in the argument list.
-
-[[call_considerations]]
-=== Considerations for CALL
-
-[[call_usage_restrictions]]
-==== Usage Restrictions
-
-You can use a CALL statement as a stand-alone SQL statement in applications or command-line interfaces,
-such as TrafCI. You cannot use a CALL statement inside a compound statement or with row sets.
-
-[[call_required_privileges]]
-==== Required Privileges
-
-To issue a CALL statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are the owner of the stored procedure.
-* You have the EXECUTE (or ALL) privileges, either directly through your username or through a granted role.
-For more information, see the <<GRANT_Statement,GRANT Statement>>.
-
-When the stored procedure executes, it executes as the {project-name} ID.
-
-<<<
-[[call_input_parameter_argument]]
-==== Input Parameter Arguments
-
-You pass data to an SPJ by using IN or INOUT parameters. For an IN
-parameter argument, use one of these SQL expressions:
-
-* Literal
-* SQL function (including CASE and CAST expressions)
-* Arithmetic or concatenation operation
-* Scalar subquery
-* Dynamic parameter (for example, ?) in an application
-* Named (for example, ?param) or unnamed (for example, ?) parameter in TrafCI
-
-For an INOUT parameter argument, you can use only a dynamic, named, or unnamed parameter. For more information, see
-<<Expressions,Expressions>>.
-
-[[call_output_parameter_arguments]]
-==== Output Parameter Arguments
-
-An SPJ returns values in OUT and INOUT parameters. Output parameter arguments must be dynamic parameters in an
-application (for example, ?) or named or unnamed parameters in DCI (for example, ?param or ?). Each
-calling application defines the semantics of the OUT and INOUT parameters in its environment.
-
-[[call_data_conversion_parameter_arguments]]
-==== Data Conversion of Parameter Arguments
-
-{project-name} SQL performs an implicit data conversion when the data type of a parameter argument is compatible with
-but does not match the formal data type of the stored procedure. For stored procedure input values,
-the conversion is from the actual argument value to the formal parameter type. For stored procedure output values,
-the conversion is from the actual output value, which has the data type of the formal parameter, to the declared
-type of the dynamic parameter.
-
-[[call_null_input_and_output]]
-==== Null Input and Output
-
-You can pass a null value as input to or output from an SPJ, provided that the corresponding Java data type of the
-parameter supports nulls. If a null is input or output for a parameter that does not support nulls, {project-name} SQL
-returns an error.
-
-<<<
-[[call_transaction_semantics]]
-==== Transaction Semantics
-
-The CALL statement automatically initiates a transaction if no active transaction exists. However, the failure of
-a CALL statement does not always automatically abort the transaction.
-
-[[call_examples]]
-=== Examples of CALL
-
-* In TrafCI, execute an SPJ named MONTHLYORDERS, which has one IN parameter represented by a literal and one OUT
-parameter represented by an unnamed parameter, ?:
-+
-```
-CALL sales.monthlyorders(3,?);
-```
-
-<<<
-* This CALL statement executes a stored procedure, which accepts one IN parameter (a date literal), returns one OUT
-parameter (a row from the column, NUM_ORDERS), and returns two result sets:
-+
-```
-CALL sales.ordersummary('01/01/2001', ?);
-
-NUM_ORDERS
---------------------
-                  13
-
-ORDERNUM   NUM_PARTS      AMOUNT          ORDER_DATE LAST_NAME
----------- -------------- --------------- ---------- ------------------
-    100210              4        19020.00 2006-04-10 HUGHES
-    100250              4        22625.00 2006-01-23 HUGHES
-    101220              4        45525.00 2006-07-21 SCHNABL
-    200300              3        52000.00 2006-02-06 SCHAEFFER
-    200320              4         9195.00 2006-02-17 KARAJAN
-    200490              2         1065.00 2006-03-19 WEIGL
-.
-.
-.
-
---- 13 row(s) selected.
-
-ORDERNUM   PARTNUM  UNIT_PRICE   QTY_ORDERED PARTDESC
----------- -------- ------------ ----------- ------------------
-    100210     2001      1100.00           3 GRAPHIC PRINTER,M1
-    100210     2403       620.00           6 DAISY PRINTER,T2
-    100210      244      3500.00           3 PC GOLD, 30 MB
-    100210     5100       150.00          10 MONITOR BW, TYPE 1
-    100250     6500        95.00          10 DISK CONTROLLER
-    100250     6301       245.00          15 GRAPHIC CARD, HR
-.
-.
-.
-
---- 70 row(s) selected.
-
---- SQL operation complete.
-```
-
-<<<
-[[commit_work_statement]]
-== COMMIT WORK Statement
-
-The COMMIT WORK statement commits any changes to objects made during the current transaction and ends
-the transaction. See <<Transaction_Management,Transaction Management>>.
-
-WORK is an optional keyword that has no effect.
-
-COMMIT WORK issued outside of an active transaction generates error 8605.
-
-```
-COMMIT [WORK]
-```
-
-[[commit_work_considerations]]
-=== Considerations for COMMIT WORK
-
-BEGIN WORK starts a transaction. COMMIT WORK or ROLLBACK WORK ends a transaction.
-
-<<<
-[[commit_work_examples]]
-=== Example of COMMIT WORK
-
-Suppose that your application adds information to the inventory. You have received 24 terminals from
-a new supplier and want to add the supplier and update the quantity on hand. The part number for the
-terminals is 5100, and the supplier is assigned supplier number 17. The cost of each terminal is $800.
-
-The transaction must add the order for terminals to PARTSUPP, add the supplier to the SUPPLIER table,
-and update QTY_ON_HAND in PARTLOC. After the INSERT and UPDATE statements execute successfully,
-you commit the transaction, as shown:
-
-```
--- This statement initiates a transaction.
-BEGIN WORK;
-
---- SQL operation complete.
-
--- This statement inserts a new entry into PARTSUPP.
-INSERT INTO invent.partsupp
-VALUES (5100, 17, 800.00, 24);
-
---- 1 row(s) inserted.
-
--- This statement inserts a new entry into SUPPLIER.
-INSERT INTO invent.supplier
-VALUES (17, 'Super Peripherals','751 Sanborn Way',
- 'Santa Rosa', 'California', '95405');
-
---- 1 row(s) inserted.
-
--- This statement updates the quantity in PARTLOC.
-UPDATE invent.partloc
-SET qty_on_hand = qty_on_hand + 24
-WHERE partnum = 5100 AND loc_code = 'G43';
-
---- 1 row(s) updated.
-
--- This statement ends a transaction.
-COMMIT WORK;
-
---- SQL operation complete.
-```
-
-<<<
-[[control_query_cancel_statement]]
-== CONTROL QUERY CANCEL Statement
-
-The CONTROL QUERY CANCEL statement cancels an executing query that you identify with a query ID.
-You can execute the CONTROL QUERY CANCEL statement in a client-based tool like TrafCI or through any ODBC or JDBC
-application.
-
-CONTROL QUERY CANCEL is a {project-name} SQL extension.
-
-```
-CONTROL QUERY CANCEL QID query-id [COMMENT 'comment-text']
-```
-
-[[control_query_cancel_syntax]]
-=== Syntax Description of CONTROL QUERY CANCEL
-
-* `_query-id_`
-+
-specifies the query ID of an executing query, which is a unique identifier generated by the SQL compiler.
-
-* `'_comment-text_'`
-+
-specifies an optional comment to be displayed in the canceled query’s error message.
-
-[[control_query_cancel_considerations]]
-=== Considerations for CONTROL QUERY CANCEL
-
-[[control_query_cancel_benefitsl]]
-==== Benefits of CONTROL QUERY CANCEL
-
-For many queries, the CONTROL QUERY CANCEL statement allows the termination of the query without stopping the
-master executor process (MXOSRVR). This type of cancellation has these benefits over standard ODBC/JDBC cancel
-methods:
-
-* An ANSI-defined error message is returned to the client session, and SQLSTATE is set to HY008.
-* Important cached objects persist after the query is canceled, including the master executor process and its
-compiler, the compiled statements cached in the master, and the compiler’s query cache and its cached metadata
-and histograms.
-* The client does not need to reestablish its connection, and its prepared statements are preserved.
-* When clients share connections using a middle-tier application server, the effects of canceling one client’s
-executing query no longer affect other clients sharing the same connection.
-
-[[control_query_cancel_restrictions]]
-==== Restrictions on CONTROL QUERY CANCEL
-
-Some executing queries may not respond to a CONTROL QUERY CANCEL statement within a 60-second interval. For those
-queries, {project-name} SQL stops their ESP processes if there are any. If this action allows the query to be canceled,
-you will see all the benefits listed above.
-
-If the executing query does not terminate within 120 seconds after the CONTROL QUERY CANCEL statement is issued,
-{project-name} SQL stops the master executor process, terminating the query and generating a lost connection error.
-In this case, you will not see any of the benefits listed above. Instead, you will lose your connection and will
-need to reconnect and re-prepare the query. This situation often occurs with the CALL, DDL, and utility statements
-and rarely with other statements.
-
-The CONTROL QUERY CANCEL statement does not work with these statements:
-
-* Unique queries, which operate on a single row and a single partition
-* Queries that are not executing, such as a query that is being compiled
-* CONTROL QUERY DEFAULT, BEGIN WORK, COMMIT WORK, ROLLBACK WORK, and EXPLAIN statements
-* Statically compiled metadata queries
-* Queries executed in anomalous conditions, such as queries without runtime statistics or without a query ID
-
-[[control_query_cancel_required_privileges]]
-==== Required Privileges
-
-To issue a CONTROL QUERY CANCEL statement, one of the following must be true:
-
-* You are DB ROOT.
-* You own (that is, issued) the query.
-* You have the QUERY_CANCEL component privilege for the SQL_OPERATIONS component.
-
-<<<
-[[control_query_cancel_examples]]
-=== Example of CONTROL QUERY CANCEL
-
-This CONTROL QUERY CANCEL statement cancels a specified query and provides a comment concerning the cancel
-operation:
-
-```
-control query cancel qid
-MXID11000010941212288634364991407000000003806U3333300_156016_S1 comment
-'Query is consuming too many resources.';
-```
-
-In a separate session, the client that issued the query will see this
-error message indicating that the query has been canceled:
-
-```
->>execute s1;
-
-*** ERROR[8007] The operation has been canceled. Query is consuming too many resources.
-```
-
-<<<
-[[control_query_default_statement]]
-== CONTROL QUERY DEFAULT Statement
-
-The CONTROL QUERY DEFAULT statement changes the default settings for the current process. You can execute
-the CONTROL QUERY DEFAULT statement in a client-based tool like TrafCI or through any ODBC or JDBC application.
-
-CONTROL QUERY DEFAULT is a {project-name} SQL extension.
-
-```
-{ CONTROL QUERY DEFAULT | CQD } control-default-option
-
-control-default-option is:
-  attribute {'attr-value' | RESET}
-```
-
-[[control_query_default_syntax]]
-=== Syntax Description of CONTROL QUERY DEFAULT
-
-* `_attribute_`
-+
-is a character string that represents an attribute name. For descriptions of these attributes,
-see the {docs-url}/cqd_reference/index.html[{project-name} Control Query Default (CQD) Reference Guide].
-
-* `_attr-value_`
-+
-is a character string that specifies an attribute value. You must specify _attr-value_ as a quoted string—even
-if the value is a number.
-
-* `RESET`
-
-specifies that the attribute that you set by using a CONTROL QUERY DEFAULT statement in the current session is
-to be reset to the value or values in effect at the start of the current session.
-
-<<<
-[[control_query_default_considerations]]
-=== Considerations for CONTROL QUERY DEFAULT
-
-[[control_query_default_scope]]
-==== Scope of CONTROL QUERY DEFAULT
-
-The result of the execution of a CONTROL QUERY DEFAULT statement stays in effect until the current process
-terminates or until the execution of another statement for the same attribute overrides it.
-
-CQDs are applied at compile time, so CQDs do not affect any statements that are already prepared. For example:
-
-```
-PREPARE x FROM SELECT * FROM t;
-CONTROL QUERY DEFAULT SCHEMA 'myschema';
-EXECUTE x;                              -- uses the default schema SEABASE
-SELECT * FROM t2;                       -- uses MYSCHEMA;
-PREPARE y FROM SELECT * FROM t3;
-CONTROL QUERY DEFAULT SCHEMA 'seabase';
-EXECUTE y;                              -- uses MYSCHEMA;
-```
-
-[[control_query_default_examples]]
-=== Examples of CONTROL QUERY DEFAULT
-
-* Increase the cache refresh time for the histogram cache to two hours (7,200 minutes).
-+
-```
-CONTROL QUERY DEFAULT CACHE_HISTOGRAMS_REFRESH_INTERVAL '7200';
-```
-
-* Reset the CACHE_HISTOGRAMS_REFRESH_INTERVAL attribute to its initial value in the current process:
-+
-```
-CONTROL QUERY DEFAULT CACHE_HISTOGRAMS_REFRESH_INTERVAL RESET;
-```
-
-<<<
-[[create_function_statement]]
-== CREATE FUNCTION Statement
-
-The CREATE FUNCTION statement registers a user-defined function (UDF) written in C as a function within
-a {project-name} database. Currently, {project-name} supports the creation of _scalar UDFs_, which return a single
-value or row when invoked. Scalar UDFs are invoked as SQL expressions in the SELECT list or WHERE clause
-of a SELECT statement.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this
-statement inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run
-this statement, AUTOCOMMIT must be turned ON (the default) for the session.
-
-```
-CREATE FUNCTION function-ref ([parameter-declaration[, parameter-declaration]...])
-    {RETURN | RETURNS}
-       (return-parameter-declaration[, return-parameter-declaration]...)
-    EXTERNAL NAME 'character-string-literal'
-    LIBRARY [[catalog-name.]schema-name.]library-name
-    [language c]
-    [parameter style sql]
-    [no sql]
-    [not deterministic | deterministic]
-    [final call | no final call]
-    [no state area | state area size]
-    [no parallelism | allow any parallelism]
-
-function-ref is:
-   [[catalog-name.]schema-name.]function-name
-
-parameter-declaration is:
-   [in] [sql-parameter-name] sql-datatype
-
-return-parameter-declaration is:
-   [out] [sql-parameter-name] sql-datatype
-```
-
-<<<
-[[create_function_syntax]]
-=== Syntax Description of CREATE FUNCTION
-
-* `_function-ref_ ( [_parameter-declaration_[,_parameter-declaration_]&#8230;] )`
-+
-specifies the name of the function and any SQL parameters that correspond to the signature of the external function.
-
-** `_function-ref_`
-+
-specifies an ANSI logical name of the form:
-+
-`\[[_catalog-name_.]_schema-name_.]_function-name_`
-+
-where each part of the name is a valid sql identifier with a maximum of 128 characters. for more information, see
-<<identifiers,identifiers>> and <<database_object_names,database object names>>.
-+
-specify a name that is unique and does not exist for any procedure or function in the same schema.
-+
-if you do not fully qualify the function name, trafodion sql qualifies it according to the schema of the current session.
-
-** `_parameter-declaration_`
-+
-specifies an sql parameter that corresponds to the signature of the external function:
-+
-`[in] [_sql-parameter-name_] _sql-datatype_`
-
-*** `in`
-+
-specifies that the parameter passes data to the function.
-
-*** `_sql-parameter-name_`
-+
-specifies an sql identifier for the parameter. for more information, see <<identifiers,identifiers>>.
-
-<<<
-*** `_sql-datatype_`
-+
-specifies an sql data type that corresponds to the data type of the parameter in the signature of the
-external function. _sql-datatype_ is one of the supported sql data types in trafodion. see
-<<data_types,data types>>.
-
-* `{return | returns} (_return-parameter-declaration_[,_return-parameter-declaration_]&#8230;)`
-+
-specifies the type of output of the function.
-
-** `_return-parameter-declaration_`
-+
-specifies an sql parameter for an output value:
-+
-`[out] [_sql-parameter-name_] _sql-datatype_`
-
-*** `out`
-+
-specifies that the parameter accepts data from the function.
-
-*** `_sql-parameter-name_`
-+
-specifies an sql identifier for the return parameter. for more information, see <<identifiers,identifiers>>.
-+
-*** `_sql-datatype_`
-+
-specifies an sql data type for the return parameter. _sql-datatype_ is one of the supported sql data types in
-trafodion. see <<data_types,data types>>.
-
-* `external name '_method-name_'`
-+
-specifies the case-sensitive name of the external function’s method.
-
-* `library \[[_catalog-name_.]_schema-name_.]_library-name_`
-+
-specifies the ANSI logical name of a library containing the external function. if you do not fully qualify the
-library name, trafodion sql qualifies it according to the schema of the current session.
-
-* `language c`
-+
-specifies that the external function is written in the c language. this clause is optional.
-
-* `parameter style sql`
-+
-specifies that the run-time conventions for arguments passed to the external function are those of the sql
-language. this clause is optional.
-
-* `no sql`
-+
-specifies that the function does not perform sql operations. this clause is optional.
-
-* `deterministic | not deterministic`
-+
-specifies whether the function always returns the same values for out parameters for a given set of argument
-values (deterministic, the default behavior) or does not return the same values (not deterministic). if the
-function is deterministic, trafodion sql is not required to execute the function each time to produce results;
-instead, trafodion sql caches the results and reuses them during subsequent executions, thus optimizing the execution.
-
-* `final call | no final call`
-+
-specifies whether or not a final call is made to the function. a final call enables the function to free up
-system resources. the default is final call.
-
-* `no state area | state area _size_`
-+
-specifies whether or not a state area is allocated to the function. _size_ is an integer denoting memory in
-bytes. acceptable values range from 0 to 16000. the default is no state area.
-
-* `no parallelism | allow any parallelism`
-+
-specifies whether or not parallelism is applied when the function is invoked. the default is allow any parallelism.
-
-<<<
-[[create_function_considerations]]
-=== Considerations for CREATE FUNCTION
-
-[[create_function_required_privileges]]
-==== Required Privileges
-
-To issue a CREATE FUNCTION statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are creating the function in a shared schema, and you have the USAGE (or ALL) privilege on the library that
-will be used in the creation of the function. The USAGE privilege provides you with read access to the library’s
-underlying library file.
-* You are the private schema owner and have the USAGE (or ALL) privilege on the library that will be used in the
-creation of the function. The USAGE privilege provides you with read access to the library’s underlying library file.
-* You have the CREATE or CREATE_ROUTINE component level privilege for the SQL_OPERATIONS component and have the
-USAGE (or ALL) privilege on the library that will be used in the creation of the function. The USAGE
-privilege provides you with read access to the library’s underlying library file.
-+
-NOTE: In this case, if you create a function in a private schema, it will be owned by the schema owner.
-
-<<<
-[[create_function_examples]]
-=== Examples of CREATE FUNCTION
-
-* This CREATE FUNCTION statement creates a function that adds two integers:
-+
-```
-create function add2 (int, int)
-       returns (total_value int)
-       external name 'add2'
-       library myudflib;
-```
-
-* This CREATE FUNCTION statement creates a function that returns the minimum, maximum, and average values of
-five input integers:
-+
-```
-create function mma5 (int, int, int, int, int)
-       returns (min_value int, max_value int, avg_value int)
-       external name 'mma5'
-       library myudflib;
-```
-
-* This CREATE FUNCTION statement creates a function that reverses an input string of at most 32 characters:
-+
-```
-create function reverse (varchar(32))
-       returns (reversed_string varchar(32))
-       external name 'reverse'
-       library myudflib;
-```
-
-<<<
-[[create_index_statement]]
-== CREATE INDEX Statement
-
-The CREATE INDEX statement creates an SQL index based on one or more columns of a table or table-like object.
-The CREATE VOLATILE INDEX statement creates an SQL index with a lifespan that is limited to the SQL session that
-the index is created. Volatile indexes are dropped automatically when the session ends. See <<Indexes,Indexes>>.
-
-CREATE INDEX is a {project-name} SQL extension.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement
-inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement,
-AUTOCOMMIT must be turned ON (the default) for the session.
-
-```
-CREATE [VOLATILE] INDEX index ON table
-   (column-name [ASC[ENDING] | DESC[ENDING]]
-   [,column-name [ASC[ENDING] | DESC[ENDING]]]...)
-   [HBASE_OPTIONS (hbase-options-list)]
-   [SALT LIKE TABLE]
-
-hbase-options-list is:
-   hbase-option = 'value'[, hbase-option = 'value']...
-```
-
-[[create_index_syntax]]
-=== Syntax Description of CREATE INDEX
-
-* `_index_`
-+
-is an SQL identifier that specifies the simple name for the new index. You cannot qualify _index_ with its schema
-name. Indexes have their own name space within a schema, so an index name might be the same as a table or constraint
-name. However, no two indexes in a schema can have the same name.
-
-* `_table_`
-+
-is the name of the table for which to create the index. See <<database_object_names,Database Object Names>>.
-
-* `_column-name_ [ASC[ENDING] | DESC[ENDING]] [,_column-name_ [ASC[ENDING] | DESC[ENDING]]]&#8230;`
-+
-specifies the columns in _table_ to include in the index. The order of the columns in the index need not correspond
-to the order of the columns in the table.
-+
-ASCENDING or DESCENDING specifies the storage and retrieval order for rows in the index. The default is ASCENDING.
-+
-Rows are ordered by values in the first column specified for the index. If multiple index rows share the same value
-for the first column, the values in the second column are used to order the rows, and so forth. If duplicate index
-rows occur in a non-unique index, their order is based on the sequence specified for the columns of the key of the
-underlying table. For ordering (but not for other purposes), nulls are greater than other values.
-
-* `HBASE_OPTIONS (_hbase-option_ = '_value_'[, _hbase-option_ = '_value_']&#8230;)`
-+
-a list of HBase options to set for the index. These options are applied independently of any HBase options set for
-the index’s table.
-
-// TODO: The Word document did not list all default values. 
-** `_hbase-option_ = '_value_'`
-+
-is one of the these HBase options and its assigned value:
-+
-[cols="35%,65%",options="header"]
-|===
-| HBase Option           | Accepted Values^1^
-| BLOCKCACHE             | 'true' \| 'false'
-| BLOCKSIZE              | *'65536'( \| '_positive-integer_'
-| BLOOMFILTER            | 'NONE' \| 'ROW' \| 'ROWCOL'
-| CACHE_BLOOMS_ON_WRITE  | 'true' \| 'false'
-| CACHE_DATA_ON_WRITE    | 'true' \| 'false'
-| CACHE_INDEXES_ON_WRITE | 'true' \| 'false'
-| COMPACT                | 'true' \| 'false'
-| COMPACT_COMPRESSION    | 'GZ' \| 'LZ4' \| 'LZO' \| 'NONE' \| 'SNAPPY'
-| COMPRESSION            | 'GZ' \| 'LZ4' \| 'LZO' \| 'NONE' \| 'SNAPPY'
-| DATA_BLOCK_ENCODING    | 'DIFF' \| 'FAST_DIFF' \| 'NONE' \| 'PREFIX'
-| DURABILITY             | 'USE_DEFAULT' \| 'SKIP_WAL' \| 'ASYNC_WAL' \| 'SYNC_WAL' \| 'FSYNC_WAL'
-| EVICT_BLOCKS_ON_CLOSE  | *'true'* \| 'false'
-| IN_MEMORY              | *'true'* \| 'false'
-| KEEP_DELETED_CELLS     | *'true'* \| 'false'
-| MAX_FILESIZE           | '_positive-integer_'
-| MAX_VERSIONS           | '1' \| '_positive-integer_'
-| MEMSTORE_FLUSH_SIZE    | '_positive-integer_'
-| MIN_VERSIONS           | '0' \| '_positive-integer_'
-| PREFIX_LENGTH_KEY      | '_positive-integer_', which should be less than maximum length of the key for the table.
-It applies only if the SPLIT_POLICY is `KeyPrefixRegionSplitPolicy`.
-| REPLICATION_SCOPE      | '0' \| *'1'*
-| SPLIT_POLICY           | 'org.apache.hadoop.hbase.regionserver. +
-ConstantSizeRegionSplitPolicy' \| +
-'org.apache.hadoop.hbase.regionserver. +
-IncreasingToUpperBoundRegionSplitPolicy' \| +
-'org.apache.hadoop.hbase.regionserver. +
-KeyPrefixRegionSplitPolicy'
-| TTL                    | '-1' (forever) \| '_positive-integer_'
-|===
-+
-^1^ Values in boldface are default values.
-
-* `SALT LIKE TABLE`
-+
-causes the index to use the same salting scheme (that is,
-`SALT USING _num_ PARTITIONS [ON (_column_[, _column_]&#8230;)])` as its base table.
-
-<<<
-[[create_index_considerations]]
-=== Considerations for CREATE INDEX
-
-Indexes are created under a single transaction. When an index is created, the following steps occur:
-
-* Transaction begins (either a user-started transaction or a system-started transaction).
-* Rows are written to the metadata.
-* Physical labels are created to hold the index (as non audited).
-* The base table is locked for read shared access which prevents inserts, updates, and deletes on the base table from occurring.
-* The index is loaded by reading the base table for read uncommitted access using side tree inserts.
-+
-NOTE: A side tree insert is a fast way of loading data that can perform specialized optimizations because the
-partitions are not audited and empty.
-
-* After load is complete, the index audit attribute is turned on and it is attached to the base table (to bring the index on-line).
-* The transaction is committed, either by the system or later by the requester.
-
-If the operation fails after basic semantic checks are performed, the index no longer exists and the entire transaction
-is rolled back even if it is a user-started transaction.
-
-[[create_index_authorization_and_availability_requirements]]
-==== Authorization and Availability Requirements
-
-An index always has the same security as the table it indexes.
-
-CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table being indexed. If other processes have rows in the table locked
-when the operation begins, CREATE INDEX waits until its lock request is granted or timeout occurs.
-
-You cannot access an index directly.
-
-<<<
-[[create_index_required_privileges]]
-==== Required Privileges
-
-To issue a CREATE INDEX statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are creating the table in a shared schema.
-* You are the private schema owner.
-* You are the owner of the table.
-* You have the ALTER, ALTER_TABLE, CREATE, or CREATE_INDEX component privilege for the SQL_OPERATIONS component.
-+
-NOTE: In this case, if you create an index in a private schema, it will be owned by the schema owner.
-
-[[create_index_limits]]
-==== Limits on Indexes
-
-For non-unique indexes, the sum of the lengths of the columns in the index plus the sum of the length of
-the clustering key of the underlying table cannot exceed 2048 bytes.
-
-No restriction exists on the number of indexes per table.
-
-[[create_index_examples]]
-=== Examples of CREATE INDEX
-
-* This example creates an index on two columns of a table:
-+
-```
-CREATE INDEX xempname
-ON persnl.employee (last_name, first_name);
-```
-
-<<<
-[[create_library_statement]]
-== CREATE LIBRARY Statement
-
-The CREATE LIBRARY statement registers a library object in a {project-name} database. A library object
-can be an SPJ's JAR file or a UDF's library file.
-
-CREATE LIBRARY is a {project-name} SQL extension.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run
-this statement inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT
-is OFF. To run this statement, AUTOCOMMIT must be turned ON (the default) for the session.
-
-```
-CREATE LIBRARY [[catalog-name.]schema-name.]library-name
-   file 'library-filename'
-   [host name 'host-name']
-   [local file 'host-filename']
-```
-
-[[create_library_syntax]]
-=== Syntax Description of CREATE LIBRARY
-
-* `\[[_catalog-name_.]_schema-name_.]_library-name_`
-+
-specifies the ANSI logical name of the library object, where each part of the name is a valid sql
-identifier with a maximum of 128 characters. specify a name that is unique and does not exist for
-libraries in the same schema. if you do not fully qualify the library name, trafodion sq qualifies
-it according to the schema of the current session. for more information, see <<identifiers,identifiers>>
-and <<database_object_names,database object names>>.
-
-<<<
-* `file '_library-filename_'`
-+
-specifies the full path of a deployed library file, which either an SPJ's jar file or a UDF's library file.
-+
-note: make sure to upload the library file to the trafodion cluster and then copy the library file to the
-same directory on all the nodes in the cluster before running the create library statement. otherwise, you
-will see an error message indicating that the jar or dll file was not found.
-
-* `host name '_host-name_'`
-+
-specifies the name of the client host machine where the deployed file resides.
-
-* `local file '_host-filename_'`
-+
-specifies the path on the client host machine where the deployed file is stored.
-
-[[create_library_considerations]]
-=== Considerations for CREATE LIBRARY
-
-* A library object cannot refer to a library file referenced by another library object. If the _library-filename_
-is in use by another library object, the CREATE LIBRARY command will fail.
-* The _library-filename_ must specify an existing file. Otherwise, the CREATE LIBRARY command will fail.
-* The CREATE LIBRARY command does not verify that the specified _library-filename_ is a valid executable file.
-* HOST NAME and LOCAL FILE are position dependent.
-
-<<<
-[[create_library_required_privileges]]
-==== Required Privileges
-
-To issue a CREATE LIBRARY statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are creating the library in a shared schema and have the MANAGE_LIBRARY privilege.
-* You are the private schema owner and have the MANAGE_LIBRARY privilege.
-* You have the CREATE or CREATE_LIBRARY component privilege for the SQL_OPERATIONS component and have
-the MANAGE_LIBRARY privilege.
-+
-NOTE: In this case, if you create a library in a private schema, it will be owned by the schema owner.
-
-[[create_library_examples]]
-=== Examples of CREATE LIBRARY
-
-* This CREATE LIBRARY statement registers a library named SALESLIB in the SALES schema for a JAR file (SPJs):
-+
-```
-CREATE LIBRARY sales.saleslib FILE '/opt/home/trafodion/spjjars/Sales.jar';
-```
-
-* This CREATE LIBRARY statement registers a library named MYUDFS in the default schema for a library file (UDFs):
-+
-```
-CREATE LIBRARY myudfs FILE $UDFLIB;
-```
-
-<<<
-[[create_procedure_statement]]
-== CREATE PROCEDURE Statement
-
-The CREATE PROCEDURE statement registers a Java method as a stored procedure in Java (SPJ) within a {project-name} database.
-
-NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement
-inside a user-defined transaction (BEGIN WORK&#8230;COMMIT WORK) or when AUTOCOMMIT is OFF. To run this statement,
-AUTOCOMMIT must be turned ON (the default) for the session.
-
-```
-CREATE PROCEDURE procedure-ref([sql-parameter-list])
-   EXTERNAL NAME 'java-method-name [java-signature]'
-   LIBRARY [[catalog-name.]schema-name.]library-name
-   [external security external-security-type]
-   language java
-   parameter style java
-   [no sql | contains sql | modifies sql data | reads sql data]
-   [dynamic result sets integer]
-   [transaction required | no transaction required]
-   [deterministic | not deterministic]
-   [no isolate | isolate]
-
-procedure-ref is:
-   [[catalog-name.]schema-name.]procedure-name
-
-sql-parameter-list is:
-   sql-parameter[, sql-parameter]...
-
-sql-parameter is:
-   [parameter-mode] [sql-identifier] sql-datatype
-
-parameter-mode is:
-   in
- | out
- | inout
-
-java-method-name is:
-   [package-name.]class-name.method-name
-
-java-signature is:
-   ([java-parameter-list])
-
-java-parameter-list is:
-   java-datatype[, java-datatype]...
-
-external-security-type is:
-   definer
- | invoker
-```
-
-NOTE: delimited variables in this syntax diagram are case-sensitive. case-sensitive variables include _java-method-name_,
-_java-signature_, and _class-file-path_, and any delimited part of the _procedure-ref_.
-the remaining syntax is not case-sensitive.
-
-[[create_procedure_syntax]]
-=== Syntax Description of CREATE PROCEDURE
-
-* `_procedure-ref_([_sql-parameter_[, _sql-parameter_]&#8230;])`
-+
-specifies the name of the stored procedure in Java (SPJ) and any SQL parameters that correspond to the signature of
-the SPJ method.
-
-** `_procedure-ref_`
-+
-specifies an ANSI logical name of the form:
-+
-`\[[_catalog-name_.]_schema-name_.]_procedure-name_`
-+
-where each part of the name is a valid SQL identifier with a maximum of 128 characters. For more information,
-see <<identifiers,identifiers>> and <<database_object_names,database object names>>.
-+
-specify a name that is unique and does not exist for any procedure or function in the same schema. {project-name}
-does not support the overloading of procedure names. That is, you cannot register the same procedure name more than
-once with different underlying SPJ methods.
-+
-If you do not fully qualify the procedure name, then {project-name} qualifies it according to the schema of the current session.
-
-** `_sql-parameter_`
-+
-specifies an SQL parameter that corresponds to the signature of the SPJ method:
-+
-`[_parameter-mode_] [_sql-identifier_] _sql-datatype_`
-
-*** `_parameter-mode_`
-+
-specifies the mode `in`, `out`, or `inout` of a parameter. The default is `in`.
-
-**** `in`
-+
-specifies a parameter that passes data to an SPJ.
-
-**** `out`
-+
-specifies a parameter that accepts data from an SPJ. The parameter must be an array.
-
-**** `inout`
-+
-specifies a parameter that passes data to and accepts data from an SPJ. The parameter must be an array.
-
-*** `_sql-identifier_`
-+
-specifies an SQL identifier for the parameter. For more information, see <<identifiers,identifiers>>.
-
-*** `_sql-datatype_`
-+
-specifies an SQL data type that corresponds to the Java parameter of the SPJ method.
-+
-_sql-datatype_ can be:
-+
-[cols="60%,40%",options="header"]
-|===
-| sql data type | maps to java data type&#8230;
-| char[acter] +
-char[acter] varying +
-varchar +
-pic[ture] x^1^ +
-nchar +
-nchar varying +
-national char[acter] +
-national char[acter] varying | java.lang.string
-| date | java.sql.date
-| time | java.sql.time
-| timestamp | java.sql.timestamp
-| dec[imal]^2^ +
-pic[ture] s9^3^ +
-numeric (including numeric with a precision greater than eighteen)^2^ | java.math.bigdecimal
-| smallint^2^ | short
-| int[eger]^2^ | int or java.lang.integer^4^
-| largeint^2^ | long or java.lang.long^4^
-| float | double or java.lang.double^4^
-| real | float or java.lang.float^4^
-| double precision | double or java.lang.double^4^
-|===
-+
-1. the trafodion database stores pic x as a char data type.
-2. numeric data types of sql parameters must be signed, which is the default in the trafodion database.
-3. the trafodion database stores pic s9 as a decimal or numeric data type.
-4. by default, the sql data type maps to a java primitive type. the sql data type maps to a java wrapper class
-only if you specify the wrapper class in the java signature of the external name clause.
-+
-for more information, see <<data_types,data types>>.
-
-* `external name '_java-method-name_ [_java-signature_]'`
-
-** `_java-method-name_`
-+
-specifies the case-sensitive name of the SPJ method of the form:
-+
-`[_package-name_.]_class-name_._method-name_`
-+
-The Java method must exist in a Java class file, _class-name_.class, within a library registered in the database.
-The Java method must be defined as `public` and `static` and have a return type of `void`.
-+
-If the class file that contains the SPJ method is part of a package, then you must also specify the package name.
-If you do not specify the package name, the create procedure statement fails to register the SPJ.
-
-** `_java-signature_`
-+
-specifies the signature of the SPJ method and consists of:
-+
-`([_java-datatype_[, _java-datatype_]&#8230;])`
-+
-The Java signature is necessary only if you want to specify a Java wrapper class (for example, `java.lang.integer`) instead of a java
-primitive data type (for example, `int`). An SQL data type maps to a Java primitive data type by default.
-+
-The Java signature is case-sensitive and must be placed within parentheses, such as `(java.lang.integer, java.lang.integer`).
-The signature must specify each of the parameter data types in the order they appear in the Java method definition within
-the class file. Each Java data type that corresponds to an out or inout parameter must be followed by empty square
-brackets (`[ ]`), such as `java.lang.integer[]`.
-+
-<<<
-*** `_java-datatype_`
-+
-Specifies a mappable Java data type. For the mapping of the Java data types to SQL data types, see _sql-datatype_.
-
-* `library \[[_catalog-name_.]_schema-name_.]_library-name_`
-+
-specifies the ANSI logical name of a library containing the SPJ method. If you do not fully qualify the library name,
-then {project-name} qualifies it according to the schema of the current session.
-
-* `external security _external-security-type_`
-+
-determines the privileges, or rights, that users have when executing (or calling) the SPJ. An SPJ can have one of these
-types of external security:
-
-** `invoker` determines that users can execute, or invoke, the stored procedure using the privileges of the user who invokes
-the stored procedure. This behavior is referred to as _invoker rights_ and is the default behavior if external security is
-not specified. Invoker rights allow a user who has the execute privilege on the SPJ to call the SPJ using his or her existing
-privileges. In this case, the user must be granted privileges to access the underlying database objects on which the SPJ operates.
-+
-NOTE: Granting a user privileges to the underlying database objects gives the user direct access to those database objects,
-which could pose a risk to more sensitive or critical data to which users should not have access. For example, an SPJ
-might operate on a subset of the data in an underlying database object but that database object might contain other
-more sensitive or critical data to which users should not have access.
-
-** `definer` determines that users can execute, or invoke, the stored procedure using the privileges of the user who created
-the stored procedure. This behavior is referred to as _definer rights_. The advantage of definer rights is that users are
-allowed to manipulate data by invoking the stored procedure without having to be granted privileges to the underlying
-database objects. That way, users are restricted from directly accessing or manipulating more sensitive or critical data in
-the database. However, be careful about the users to whom you grant execute privilege on an SPJ with definer external security
-because those users will be able to execute the SPJ without requiring privileges to the underlying database objects.
-
-<<<
-* `language java`
-+
-specifies that the external user-defined routine is written in the java language.
-
-* `parameter style java`
-+
-specifies that the run-time conventions for arguments passed to the external user-defined routine are those of the Java language.
-
-* `no sql`
-+
-specifies that the SPJ cannot perform SQL operations.
-
-* `contains sql | modifies sql data | reads sql data`
-+
-specifies that the SPJ can perform SQL operations. All these options behave the same as `contains sql`, meaning that the SPJ
-can read and modify SQL data. Use one of these options to register a method that contains SQL statements. Ff you do not specify
-an SQL access mode, then the default is `contains sql`.
-
-* `dynamic result sets _integer_`
-+
-specifies the maximum number of result sets that the SPJ can return. This option is applicable only if the method signature
-contains a `java.sql.resultset[]` object. If the method contains a result set object, then the valid range is 1 to 255 inclusive.
-The actual number of result sets returned by the SPJ method can be fewer than or equal to this number. If you do not specify
-this option, then the default value is 0 (zero), meaning that the SPJ does not return result sets.
-
-* `transaction required | no transaction required`
-+
-determines whether the SPJ must run in a transaction inherited from the calling application (`transaction required`, the default
-option) or whether the SPJ runs without inheriting the calling application’s transaction (`no transaction required`). Typically,
-you want the stored procedure to inherit the transaction from the calling application. However, if the SPJ method does
-not access the database or if you want the stored procedure to manage its own transactions, then you should set the stored
-procedure’s transaction attribute to no transaction required. For more information, see
-<<effects_of_the_transaction_attribute_on_spjs,effects of the transaction attribute on SPJs>>.
-
-<<<
-* `deterministic | not deterministic`
-+
-specifies whether the SPJ always returns the same values for out and inout parameters for a given set of argument values
-(`deterministic`) or does not return the same values (`not deterministic`, the default option). If you specify `deterministic`,
-{project-name} is not required to call the SPJ each time to produce results; instead, {project-name} caches the results and
-reuses them during subsequent calls, thus optimizing the CALL statement.
-
-* `no isolate | isolate`
-+
-specifies that the SPJ executes either in the environment of the database server (`no isolate`) or in an isolated environment
-(`isolate`, the default option). {project-name} allows both options but always executes the SPJ in the UDR server process (`isolate`).
-
-[[create_procedure_considerations]]
-=== Considerations for CREATE PROCEDURE
-
-[[create_procedure_required_privileges]]
-==== Required Privileges
-
-To issue a CREATE PROCEDURE statement, one of the following must be true:
-
-* You are DB ROOT.
-* You are creating the procedure in a shared schema, and you have the USAGE (or ALL) privilege on the library that will be
-used in the creation of the stored procedure. The USAGE privilege provides you with read access to the library’s underlying
-JAR file, which contains the SPJ Java method.
-* You are the private schema owner and have the USAGE (or ALL) privilege on the library that will be used in the creation of
-the stored procedure. The USAGE privilege provides you with read access to the library’s underlying JAR file, which contains
-the SPJ Java method.
-* You have the CREATE or CREATE_ROUTINE component level privilege for the SQL_OPERATIONS component and have the USAGE (or ALL)
-privilege on the library that will be used in the creation of the stored procedure. The USAGE privilege provides you with read
-access to the library’s underlying JAR file, which contains the SPJ Java method.
-+
-NOTE: In this case, if you create a stored procedure in a private schema, it will be owned by the schema owner.
-
-<<<
-[[effects_of_the_transaction_attribute_on_spjs]]
-==== Effects of the Transaction Attribute on SPJs
-
-===== Transaction Required
-
-_Using Transaction Control Statements or Methods_
-
-If you specify TRANSACTION REQUIRED (the default option), a CALL statement automatically initiates a transaction if there is
-no active transaction. In this case, you should not use transaction control statements (or equivalent JDBC transaction methods)
-in the SPJ method. Transaction control statements include COMMIT WORK and ROLLBACK WORK, and the equivalent JDBC transaction
-methods are `Connection.commit()` and `Connection.rollback()`. If you try to use transaction control statements or methods in an
-SPJ method when the stored procedure’s transaction attribute is set to TRANSACTION REQUIRED, then the transaction control statements
-or methods in the SPJ method are ignored, and the Java virtual machine (JVM) does not report any errors or warnings. When the
-stored procedure’s transaction attribute is set to TRANSACTION REQUIRED, then you should rely on the transaction control statements
-or methods in the application that calls the stored procedure and allow the calling application to manage the transactions.
-
-_Committing or Rolling Back a Transaction_
-
-If you do not use transaction control statements in the calling application, then the transaction initiated by the CALL statement
-might not automatically commit or roll back changes to the database. When AUTOCOMMIT is ON (the default setting), the database
-engine automatically commits or rolls back any changes made to the database at the end of the CALL statement execution. However,
-when AUTOCOMMIT is OFF, the current transaction remains active until the end of the client session or until you explicitly commit
-or roll back the transaction. To ensure an atomic unit of work when calling an SPJ, use the COMMIT WORK statement in the calling
-application to commit the transaction when the CALL statement succeeds, and use the ROLLBACK WORK statement to roll back the
-transaction when the CALL statement fails.
-
-<<<
-===== No Transaction Required
-
-In some cases, you might not want the SPJ method to inherit the transaction from the calling application. Instead, you might want
-the stored procedure to manage its own transactions or to run without a transaction. Not inheriting the calling application’s
-transaction is useful in these cases:
-
-* The stored procedure performs several long-running operations, such as multiple DDL or table maintenance operations, on the
-database. In this case, you might want to commit those operations periodically from within the SPJ method to avoid locking tables
-for a long time.
-* The stored procedure performs certain SQL operations that must run without an active transaction. For example, INSERT, UPDATE,
-and DELETE statements with the WITH NO ROLLBACK option are rejected when a transaction is already active, as is the case when a
-stored procedure inherits a transaction from the calling application. The PURGEDATA utility is also rejected when a transaction
-is already active.
-* The stored procedure does not access the database. In this case, the stored procedure does not need to inherit the transaction
-from the calling application. By setting the stored procedure’s transaction attribute to NO TRANSACTION REQUIRED, you can avoid
-the overhead of the calling application’s transaction being propagated to the stored procedure.
-
-In these cases, you should set the stored procedure’s transaction attribute to NO TRANSACTION REQUIRED when creating the stored
-procedure.
-
-If you specify NO TRANSACTION REQUIRED and if the SPJ method creates a JDBC default connection, that connection will have autocommit
-enabled by default. You can either use the autocommit transactions or disable autocommit (conn.setAutoCommit(false);) and use the
-JDBC transaction methods, `Connection.commit()` and `Connection.rollback()`, to commit or roll back work where needed.
-
-<<<
-[[create_procedure_examples]]
-=== Examples of CREATE PROCEDURE
-
-* This CREATE PROCEDURE statement registers an SPJ named LOWERPRICE, which does not accept any arguments:
-+
-```
-SET SCHEMA SALES;
-
-CREATE PROCEDURE lowerprice()
-   EXTERNAL NAME 'Sales.lowerPrice'
-   LIBRARY saleslib
-   LANGUAGE JAVA
-   PARAMETER STYLE JAVA
-   MODIFIES SQL DATA;
-```
-+
-Because the procedure name is not qualified by a catalog and schema, {project-name} qualifies it according to the current
-session settings, where the catalog is TRAFODION (by default) and the schema is set to SALES. Since the procedure needs
-to be able to read and modify SQL data, MODIFIES SQL DATA is specified in the CREATE PROCEDURE statement.
-+
-To call this SPJ, use this CALL statement:
-+
-```
-CALL lowerprice();
-```
-+
-The LOWERPRICE procedure lowers the price of items with 50 or fewer orders by 10 percent in the database.
-
-* This CREATE PROCEDURE statement registers an SPJ named TOTALPRICE, which accepts three input parameters and returns a numeric value, the
-total price to an INOUT parameter:
-+
-```
-CREATE PROCEDURE trafodion.sales.totalprice(IN qty NUMERIC (18),
-                                            IN rate VARCHAR (10),
-                                            INOUT price NUMERIC (18,2))
-   EXTERNAL NAME 'Sales.totalPrice'
-   LIBRARY sales.saleslib
-   LANGUAGE JAVA
-   PARAMETER STYLE JAVA
-   NO SQL;
-```
-+
-<<<
-+
-To call this SPJ in TrafCI, use these statements:
-+
-```
-SET PARAM ?p 10.00;
-CALL sales.totalprice(23, 'standard', ?p);
-
-p
---------------------
-              253.97
-
---- SQL operation complete.
-```
-+
-Since the procedure does not read and modify any SQL data, NO SQL is specified in the CREATE PROCEDURE statement.
-
-* This CREATE PROCEDURE statement registers an SPJ named MONTHLYORDERS, which accepts an integer value for the month
-and returns the number of orders:
-+
-```
-CREATE PROCEDURE sales.monthlyorders(IN INT, OUT number INT)
-   EXTERNAL NAME 'Sales.numMonthlyOrders (int, java.lang.Integer[])'
-   LIBRARY sales.saleslib
-   LANGUAGE JAVA
-   PARAMETER STYLE JAVA
-   READS SQL DATA;
-```
-+
-Because the OUT parameter is supposed to map to the Java wrapper class, java.lang.Integer, you must specify the Java
-signature in the EXTERNAL NAME clause. To invoke this SPJ, use this CALL statement:
-+
-```
-CALL sales.monthlyorders(3, ?);
-
-ORDERNUM
------------
-          4
-
---- SQL operation complete.
-```
-
-<<<
-* This CREATE PROCEDURE statement registers an SPJ named ORDERSUMMARY, which accepts a date (formatted as a string) and
-returns information about the orders on or after that date.
-+
-```
-CREATE PROCEDURE sales.ordersummary(IN on_or_after_date VARCHAR (20),
-                                    OUT num_orders LARGEINT)
-   EXTERNAL NAME 'Sales.orderSummary (int, long[])'
-   LIBRARY sales.saleslib
-   EXTERNAL SECURITY invoker
-   LANGUAGE JAVA
-   PARAMETER STYLE JAVA
-   READS SQL DATA
-   DYNAMIC RESULT SETS 2;
-```
-+
-To invoke this SPJ, use this CALL statement:
-+
-```
-CALL trafodion.sales.ordersummary('01-01-2014', ?);
-```
-+
-The ORDERSUMMARY procedure returns this information about the orders on or after the specified date, 01-01-2014:
-+
-```
-NUM_ORDERS
---------------------
-                  13
-
-ORDERNUM NUM_PARTS            AMOUNT               ORDER_DATE LAST_NAME
--------- -------------------- -------------------- ---------- --------------------
-  100210                    4             19020.00 2014-04-10 HUGHES
-  100250                    4             22625.00 2014-01-23 HUGHES
-  101220                    4             45525.00 2014-07-21 SCHNABL
-  ... ... ... ... ...
-
---- 13 row(s) selected.
-
-ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
--------- ------- ---------- ----------- ------------------
-  100210     244    3500.00           3 PC GOLD, 30 MB
-  100210    2001    1100.00           3 GRAPHIC PRINTER,M1
-  100210    2403     620.00           6 DAISY PRINTER,T2
-  ... ... ... ... ...
-
---- 70 row(s) selected.
-
---- SQL operation complete.
-```
-
-<<<
-[[create_role_statement]]
-== CREATE ROLE Statement
-
-The CREATE ROLE statement creates an SQL role. See <<Roles,Roles>>.
-
-```
-CREATE ROLE role-name [ WITH ADMIN grantor ]
-
-grantor is:
-   database-username
-```
-
-[[create_role_syntax]]
-=== Syntax Description of CREATE ROLE
-
-* `_role-name_`
-+
-is an SQL identifier that specifies the new role. _role-name_ is a regular or delimited
-case-insensitive identifier.
-See <<Case_Insensitive_Delimited_Identifiers,Case-Insensitive Delimited Identifiers>>.
-_role-name_ cannot be an existing role name, and it cannot be a registered database username. However,
-_role-name_ can be a configured directory-service username.
-
-* `WITH ADMIN _grantor_`
-+
-specifies a role owner other than the current user. This is an optional clause.
-
-* `_grantor_`
-
-specifies a registered database username to whom you assign the role owner.
-
-<<<
-[[create_role_considerations]]
-=== Considerations for CREATE ROLE
-
-* To create a role, you must either be DB ROOT or have been granted the MANAGE_ROLES component privilege for SQL_OPERATIONS.
-* PUBLIC, _SYSTEM, NONE, and database user names beginning with DB are reserved. You cannot specify a _role-name_ with any such name.
-
-[[create_role_ownership]]
-==== Role Ownership
-
-You can give role ownership to a user by specifying the user in the WITH ADMIN _grantor_ clause with the _grantor_ as the user.
-
-The role owner can perform these operations:
-
-* Grant and revoke the role to users.
-* Drop the role.
-
-Role ownership is permanent. After you create the role, the ownership of the role cannot b

<TRUNCATED>


Mime
View raw message