You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sv...@apache.org on 2016/01/14 19:18:24 UTC
[20/42] incubator-trafodion git commit: Completed the SQL Statements
chapter.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d99ffe0a/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 7ff9e68..5e34f5f 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -38,18 +38,18 @@ http://trafodion.incubator.apache.org/client_install/index.html[_Trafodion Clien
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_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 Trafodion SQL schema or object.
@@ -61,34 +61,34 @@ transaction (BEGIN WORK…COMMIT WORK) or when AUTOCOMMIT is OFF. To run the
[cols="2*", options="header"]
|===
| Statement | What It Does
-| <<ALTER_LIBRARY_Statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion 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 functionwithin a Trafodion 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 Trafodion database.
-| <<CREATE_PROCEDURE_Statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a Trafodion 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 Trafodion database.
-| <<DROP_INDEX_Statement,DROP INDEX Statement>> | Drops an index.
-| <<DROP_LIBRARY_Statement,DROP LIBRARY Statement>> | Removes a library object from the Trafodion database and also removes the library file
+| <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion 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 functionwithin a Trafodion 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 Trafodion database.
+| <<create_procedure_statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a Trafodion 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 Trafodion database.
+| <<drop_index_statement,DROP INDEX Statement>> | Drops an index.
+| <<drop_library_statement,DROP LIBRARY Statement>> | Removes a library object from the Trafodion 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 Trafodion 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
+| <<drop_procedure_statement,DROP PROCEDURE Statement>> | Removes a stored procedure in Java (SPJ) from the Trafodion 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 username.
-| <<UNREGISTER_USER_Statement, UNREGISTER USER Statement>> | Removes a database username from the SQL database.
+| <<unregister_user_statement, UNREGISTER USER Statement>> | Removes a database username from the SQL database.
|===
<<<
-[[Data_Manipulation_Language_Statements]]
+[[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:
@@ -96,19 +96,19 @@ Use these DML statements to delete, insert, select, or update rows in one or mor
[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
+| <<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
+| <<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]]
=== Transaction Control Statements
Use these statements to specify user-defined transactions and to set attributes for the next transaction:
@@ -116,15 +116,15 @@ Use these statements to specify user-defined transactions and to set attributes
[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
+| <<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]]
=== Data Control and Security Statements
Use these statements to register users, create roles, and grant and revoke privileges:
@@ -132,21 +132,21 @@ Use these statements to register users, create roles, and grant and revoke privi
[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 username.
-| <<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 username from the SQL database.
+| <<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 username.
+| <<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 username from the SQL database.
|===
<<<
-[[Stored_Procedure_and_User_Defined_Function_Statements]]
+[[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
@@ -155,21 +155,21 @@ 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 Trafodion database.
-| <<CALL_Statement,CALL Statement>> | Initiates the execution of a stored procedure in Java (SPJ) in a Trafodion database.
-| <<CREATE_FUNCTION_Statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within a Trafodion database.
-| <<CREATE_LIBRARY_Statement,CREATE LIBRARY Statement>> | Registers a library object in a Trafodion database.
-| <<CREATE_PROCEDURE_Statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a Trafodion database.
-| <<DROP_FUNCTION_Statement,DROP FUNCTION Statement>> | Removes a user-defined function (UDF) from the Trafodion database.
-| <<DROP_LIBRARY_Statement,DROP LIBRARY Statement>> | Removes a library object from the Trafodion database and also removes the library file
+| <<alter_library_statement,ALTER LIBRARY Statement>> | Updates the physical filename for a library object in a Trafodion database.
+| <<call_statement,CALL Statement>> | Initiates the execution of a stored procedure in Java (SPJ) in a Trafodion database.
+| <<create_function_statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within a Trafodion database.
+| <<create_library_statement,CREATE LIBRARY Statement>> | Registers a library object in a Trafodion database.
+| <<create_procedure_statement,CREATE PROCEDURE Statement>> | Registers a Java method as a stored procedure in Java (SPJ) within a Trafodion database.
+| <<drop_function_statement,DROP FUNCTION Statement>> | Removes a user-defined function (UDF) from the Trafodion database.
+| <<drop_library_statement,DROP LIBRARY Statement>> | Removes a library object from the Trafodion 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 Trafodion 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.
+| <<drop_procedure_statement,DROP PROCEDURE Statement>> | Removes a stored procedure in Java (SPJ) from the Trafodion 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]]
=== Prepared Statements
Use these statements to prepare and execute an SQL statement:
@@ -177,13 +177,13 @@ 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.
+| <<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]]
=== Control Statements
Use these statements to control the execution, default options, plans, and performance of DML statements:
@@ -191,11 +191,11 @@ Use these statements to control the execution, default options, plans, and perfo
[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.
+| <<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]]
=== Object Naming Statements
Use this statements to specify default ANSI names for the schema:
@@ -203,11 +203,11 @@ 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.
+| <<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]]
=== SHOW, GET, and EXPLAIN Statements
Use these statements to display information about database objects or query execution plans:
@@ -215,34 +215,30 @@ Use these statements to display information about database objects or query exec
[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
+| <<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 Trafodion 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 Trafodion instance and
+| <<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 Trafodion instance and
indicates if the metadata is current.
-| <<GET_VERSION_OF_SOFTWARE_Statement,GET VERSION OF SOFTWARE Statement>> | Displays the version of the Trafodion software that is installed on the
+| <<get_version_of_software_statement,GET VERSION OF SOFTWARE Statement>> | Displays the version of the Trafodion 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
+| <<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
+| <<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
+| <<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
+| <<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]]
== ALTER LIBRARY Statement
-* <<alter_library_syntax,Syntax Description of ALTER LIBRARY>>
-* <<alter_library_considerations,Considerations for ALTER LIBRARY>>
-* <<alter_library_examples,Examples of ALTER LIBRARY>>
-
The ALTER LIBRARY statement updates the physical filename for a library object in a Trafodion database.
A library object can be an SPJ's JAR file or a UDF's library file.
@@ -254,34 +250,34 @@ 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]
+ 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>>.
+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_`
+* `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.
+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_`
+* `host name _host-name_`
+
specifies the name of the client host machine where the deployed file resides.
-* `LOCAL FILE _host-filename_`
+* `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
@@ -295,7 +291,6 @@ To issue an ALTER LIBRARY statement, one of the following must be true:
* 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
@@ -313,13 +308,9 @@ ALTER LIBRARY myudfs FILE $TMUDFLIB;
<<<
-[[ALTER_TABLE_Statement]]
+[[alter_table_statement]]
== ALTER TABLE Statement
-* <<alter_table_syntax,Syntax Description of ALTER TABLE>>
-* <<alter_table_considerations,Considerations for ALTER TABLE>>
-* <<alter_table_examples,Examples of ALTER TABLE>>
-
The ALTER TABLE statement changes a Trafodion SQL table. See <<Tables,Tables>>.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this
@@ -342,55 +333,55 @@ column-definition is:
column-name data-type
([DEFAULT default]
- [[CONSTRAINT constraint-name] column-constraint])
+ [[constraint constraint-name] column-constraint])
data-type is:
- CHAR[ACTER] [(length)[CHARACTERS]]
- [CHARACTER SET char-set-name]
- [UPSHIFT] [[NOT] CASESPECIFIC]
- | CHAR[ACTER] VARYING (length)
+ char[acter] [(length)[characters]]
[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 }
+ [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 }
+ | null
+ | currentdate
+ | currenttime
+ | currenttimestamp }
column-constraint is:
- NOT NULL
- | UNIQUE
- | CHECK (condition)
- | REFERENCES ref-spec
+ not null
+ | unique
+ | check (condition)
+ | references ref-spec
table-constraint is:
- UNIQUE (column-list)
- | CHECK (condition)
- | FOREIGN KEY (column-list) REFERENCES ref-spec
+ unique (column-list)
+ | check (condition)
+ | foreign key (column-list) references ref-spec
ref-spec is:
@@ -407,7 +398,7 @@ column-list is:
* `_name_`
+
-specifies the current name of the object. See <<Database_Object_Names,Database Object Names>>.
+specifies the current name of the object. See <<database_object_names,Database Object Names>>.
* `ADD [COLUMN] _column-definition_`
+
@@ -440,38 +431,38 @@ For any row that you add after the column is added, if no value is specified for
receives a default value based on the current timestamp at the time the row is added.
<<<
-** `[[CONSTRAINT _constraint-name_] _column-constraint_]`
+** `[[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>>.
+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…_01….
+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…_01….
*** `_column-constraint_` options:
-**** `NOT NULL`
+**** `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>>.
+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`
+**** `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.
+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_)`
+**** `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.
+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_`
+**** `references _ref-spec_`
+
-specifies a REFERENCES column constraint. The maximum combined length of the columns for a REFERENCES constraint is 2048 bytes. +
+specifies a references column constraint. the maximum combined length of the columns for a references constraint is 2048 bytes. +
***** `_ref-spec_` is:
+
@@ -480,99 +471,99 @@ specifies a REFERENCES column constraint. The maximum combined length of the col
`_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.
+`_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.
+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_`
+* `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
+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_`
+** `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>>.
+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…_01….
+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…_01….
+
** `_table-constraint_` options:
-*** `UNIQUE (_column-list_)`
+*** `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.
+`_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
+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_)`
+*** `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.
+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`
+*** `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.
+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.
+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]`
+* `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
+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_`
+** `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>>.
+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…_01….
+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…_01….
-* `RENAME TO _new-name_ [CASCADE]`
+* `rename to _new-name_ [cascade]`
+
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.
+specifies the new name of the object after the rename to operation occurs.
<<<
-** `CASCADE`
+** `cascade`
+
specifies that indexes and constraints on the renamed object will be renamed.
-* `ADD IF NOT EXISTS _column-definition_`
+* `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_.
+the clauses for the _column-definition_ are the same as described in add [column] _column-definition_.
-* `DROP COLUMN [IF EXISTS] _column-name_`
+* `drop column [if exists] _column-name_`
+
-drops the specified column from _table_, including the column’s data. You cannot drop a primary key column.
+drops the specified column from _table_, including the column’s data. you cannot drop a primary key column.
<<<
[[alter_table_considerations]]
@@ -615,35 +606,28 @@ If the constraint refers to the other table in a query expression, you must also
This example adds a column:
-[source,sql]
-----
+```
ALTER TABLE persnl.project
ADD COLUMN projlead
NUMERIC (4) UNSIGNED
-----
+```
<<<
[[alter_user_statement]]
== ALTER USER Statement
-* <<alter_user_syntax,Syntax Description of ALTER USER>>
-* <<alter_user_considerations,Considerations for ALTER USER>>
-* <<alter_user_examples,Examples of ALTER USER>>
-
The ALTER USER statement changes attributes associated with a user who is registered in the database.
ALTER USER is a Trafodion SQL extension.
-[source,text,subs="quotes"]
-----
+```
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
@@ -692,25 +676,20 @@ Initially, DB_ROOT is the only database user who has been granted the MANAGE_USE
* To change a user's external name:
+
-[source,sql]
-----
+```
ALTER USER ajones SET EXTERNAL NAME "Americas\ArturoJones";
-----
+```
* To change a user's attribute to allow the user to connect to the database:
+
-[source,sql]
-----
+```
ALTER USER ajones SET ONLINE;
-----
+```
<<<
[[begin_work_statement]]
== BEGIN WORK Statement
-* <<begin_work_considerations,Considerations for BEGIN WORK>>
-* <<begin_work_examples,Example of BEGIN WORK>>
-
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.
@@ -726,14 +705,12 @@ 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:
-[source,sql]
-----
+```
--- This statement initiates a transaction.
BEGIN WORK;
@@ -755,16 +732,12 @@ UPDATE invent.partloc SET qty_on_hand = qty_on_hand - 2 WHERE partnum = 4102 AND
COMMIT WORK;
--- SQL operation complete.
-----
+```
<<<
[[call_statement]]
== CALL Statement
-* <<call_syntax,Syntax Description of CALL>>
-* <<call_considerations,Considerations for CALL>>
-* <<call_examples,Examples of CALL>>
-
The CALL statement invokes a stored procedure in Java (SPJ) in a Trafodion SQL database.
```
@@ -774,7 +747,7 @@ procedure-ref is:
[[catalog-name.]schema-name.]procedure-name
argument-list is:
- SQL-expression[, SQL-expression]...
+ sql-expression[, sql-expression]...
```
[[call_syntax]]
@@ -786,31 +759,31 @@ 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>>.
+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.
+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, INOUT, or OUT parameters. The arguments consist of SQL expressions, including dynamic parameters,
+accepts arguments for in, inout, or out parameters. the arguments consist of sql expressions, including dynamic parameters,
separated by commas:
+
-`_SQL-expression_[\{, _SQL-expression_}…]`
+`_sql-expression_[{, _sql-expression_}…]`
+
<<<
+
-Each expression must evaluate to a value of one of these data types:
+each expression must evaluate to a value of one of these data types:
+
-** Character value
-** Date-time value
-** Numeric value
+** 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>>.
+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.
+do not specify result sets in the argument list.
[[call_considerations]]
=== Considerations for CALL
@@ -873,6 +846,7 @@ You can pass a null value as input to or output from an SPJ, provided that the c
parameter supports nulls. If a null is input or output for a parameter that does not support nulls, Trafodion SQL
returns an error.
+<<<
[[call_transaction_semantics]]
==== Transaction Semantics
@@ -885,17 +859,15 @@ a CALL statement does not always automatically abort the transaction.
* 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, ?:
+
-[source,sql]
-----
+```
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:
+
-[source,sql]
-----
+```
CALL sales.ordersummary('01/01/2001', ?);
NUM_ORDERS
@@ -931,15 +903,12 @@ ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
--- 70 row(s) selected.
--- SQL operation complete.
-----
+```
<<<
[[commit_work_statement]]
== COMMIT WORK Statement
-* <<commit_work_considerations,Considerations for COMMIT WORK>>
-* <<commit_work_examples,Example of COMMIT WORK>>
-
The COMMIT WORK statement commits any changes to objects made during the current transaction and ends
the transaction. See <<Transaction_Management,Transaction Management>>.
@@ -968,8 +937,7 @@ The transaction must add the order for terminals to PARTSUPP, add the supplier t
and update QTY_ON_HAND in PARTLOC. After the INSERT and UPDATE statements execute successfully,
you commit the transaction, as shown:
-[source,sql]
-----
+```
-- This statement initiates a transaction.
BEGIN WORK;
@@ -999,7 +967,7 @@ WHERE partnum = 5100 AND loc_code = 'G43';
COMMIT WORK;
--- SQL operation complete.
-----
+```
<<<
[[control_query_cancel_statement]]
@@ -1081,22 +1049,20 @@ To issue a CONTROL QUERY CANCEL statement, one of the following must be true:
This CONTROL QUERY CANCEL statement cancels a specified query and provides a comment concerning the cancel
operation:
-[source,sql]
-----
+```
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:
-[source,sql]
-----
+```
>>execute s1;
*** ERROR[8007] The operation has been canceled. Query is consuming too many resources.
-----
+```
<<<
[[control_query_default_statement]]
@@ -1144,8 +1110,7 @@ terminates or until the execution of another statement for the same attribute ov
CQDs are applied at compile time, so CQDs do not affect any statements that are already prepared. For example:
-[source,sql]
-----
+```
PREPARE x FROM SELECT * FROM t;
CONTROL QUERY DEFAULT SCHEMA 'myschema';
EXECUTE x; -- uses the default schema SEABASE
@@ -1153,33 +1118,27 @@ 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
* Change the maximum supported length of the column names to 200 for the current process:
+
-[source,sql]
-----
+```
CONTROL QUERY DEFAULT HBASE_MAX_COLUMN_NAME_LENGTH '200';
-----
+```
* Reset the HBASE_MAX_COLUMN_NAME_LENGTH attribute to its initial value in the current process:
+
-[source,sql]
-----
+```
CONTROL QUERY DEFAULT HBASE_MAX_COLUMN_NAME_LENGTH RESET;
-----
+```
<<<
[[create_function_statement]]
== CREATE FUNCTION Statement
-* <<create_function_syntax,Syntax Description of CREATE FUNCTION>>
-* <<create_function_considerations,Considerations for CREATE FUNCTION>>
-* <<create_function_examples,Examples of CREATE FUNCTION>>
-
The CREATE FUNCTION statement registers a user-defined function (UDF) written in C as a function within
a Trafodion database. Currently, Trafodion 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
@@ -1195,22 +1154,22 @@ CREATE FUNCTION function-ref ([parameter-declaration[, parameter-declaration]...
(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]
+ [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
+ [in] [sql-parameter-name] sql-datatype
return-parameter-declaration is:
- [OUT] [sql-parameter-name] sql-datatype
+ [out] [sql-parameter-name] sql-datatype
```
<<<
@@ -1227,99 +1186,99 @@ 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>>.
+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.
+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.
+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:
+specifies an sql parameter that corresponds to the signature of the external function:
+
-`[IN] [_sql-parameter-name_] _sql-datatype_`
+`[in] [_sql-parameter-name_] _sql-datatype_`
-*** `IN`
+*** `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>>.
+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>>.
+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_]…)`
+* `{return | returns} (_return-parameter-declaration_[,_return-parameter-declaration_]…)`
+
specifies the type of output of the function.
** `_return-parameter-declaration_`
+
-specifies an SQL parameter for an output value:
+specifies an sql parameter for an output value:
+
-`[OUT] [_sql-parameter-name_] _sql-datatype_`
+`[out] [_sql-parameter-name_] _sql-datatype_`
-*** `OUT`
+*** `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>>.
+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>>.
+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_'`
+* `external name '_method-name_'`
+
specifies the case-sensitive name of the external function’s method.
-* `LIBRARY [[_catalog-name_.]_schema-name_.]_library-name_`
+* `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.
+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`
+* `language c`
+
-specifies that the external function is written in the C language. This clause is optional.
+specifies that the external function is written in the c language. this clause is optional.
-* `PARAMETER STYLE SQL`
+* `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.
+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`
+* `no sql`
+
-specifies that the function does not perform SQL operations. This clause is optional.
+specifies that the function does not perform sql operations. this clause is optional.
-* `DETERMINISTIC | NOT DETERMINISTIC`
+* `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 ETERMINISTIC). 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.
+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 eterministic). 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`
+* `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.
+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_`
+* `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.
+bytes. acceptable values range from 0 to 16000. the default is no state area.
-* `NO PARALLELISM | ALLOW ANY PARALLELISM`
+* `no parallelism | allow any parallelism`
+
-specifies whether or not parallelism is applied when the function is invoked. The default is ALLOW ANY PARALLELISM.
+specifies whether or not parallelism is applied when the function is invoked. the default is allow any parallelism.
<<<
[[create_function_considerations]]
@@ -1348,43 +1307,36 @@ NOTE: In this case, if you create a function in a private schema, it will be own
* This CREATE FUNCTION statement creates a function that adds two integers:
+
-[source,sql]
-----
+```
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:
+
-[source,sql]
-----
+```
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:
+
-[source,sql]
-----
+```
create function reverse (varchar(32))
returns (reversed_string varchar(32))
external name 'reverse'
library myudflib;
-----
+```
<<<
[[create_index_statement]]
== CREATE INDEX Statement
-* <<create_index_syntax,Syntax Description of CREATE INDEX>>
-* <<create_index_considerations,Considerations for CREATE INDEX>>
-* <<create_index_examples ,Examples of CREATE INDEX>>
-
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>>.
@@ -1417,7 +1369,7 @@ 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>>.
+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]]]…`
+
@@ -1539,20 +1491,15 @@ No restriction exists on the number of indexes per table.
* This example creates an index on two columns of a table:
+
-[source,sql]
-----
+```
CREATE INDEX xempname
ON persnl.employee (last_name, first_name);
-----
+```
<<<
[[create_library_statement]]
== CREATE LIBRARY Statement
-* <<create_library_syntax,Syntax Description of CREATE LIBRARY>>
-* <<create_library_considerations,Considerations for CREATE LIBRARY>>
-* <<create_library_examples,Examples of CREATE LIBRARY>>
-
The CREATE LIBRARY statement registers a library object in a Trafodion database. A library object
can be an SPJ's JAR file or a UDF's library file.
@@ -1564,9 +1511,9 @@ is OFF. To run this statement, AUTOCOMMIT must be turned ON (the default) for th
```
CREATE LIBRARY [[catalog-name.]schema-name.]library-name
- FILE 'library-filename'
- [HOST NAME 'host-name']
- [LOCAL FILE 'host-filename']
+ file 'library-filename'
+ [host name 'host-name']
+ [local file 'host-filename']
```
[[create_library_syntax]]
@@ -1574,26 +1521,26 @@ CREATE LIBRARY [[catalog-name.]schema-name.]library-name
* `[[_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>>.
+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_'`
+* `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.
+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.
+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_'`
+* `host name '_host-name_'`
+
specifies the name of the client host machine where the deployed file resides.
-* `LOCAL FILE '_host-filename_'`
+* `local file '_host-filename_'`
+
specifies the path on the client host machine where the deployed file is stored.
@@ -1625,26 +1572,20 @@ NOTE: In this case, if you create a library in a private schema, it will be owne
* This CREATE LIBRARY statement registers a library named SALESLIB in the SALES schema for a JAR file (SPJs):
+
-[source,sql]
-----
+```
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):
+
-[source,sql]
-----
+```
CREATE LIBRARY myudfs FILE $UDFLIB;
-----
+```
<<<
[[create_procedure_statement]]
== CREATE PROCEDURE Statement
-* <<create_procedure_syntax,Syntax Description of CREATE PROCEDURE>>
-* <<create_procedure_considerations,Considerations for CREATE PROCEDURE>>
-* <<create_procedure_examples,Examples of CREATE PROCEDURE>>
-
The CREATE PROCEDURE statement registers a Java method as a stored procedure in Java (SPJ) within a Trafodion database.
NOTE: DDL statements are not currently supported in transactions. That means that you cannot run this statement
@@ -1655,14 +1596,14 @@ 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]
+ [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
@@ -1674,9 +1615,9 @@ sql-parameter is:
[parameter-mode] [sql-identifier] sql-datatype
parameter-mode is:
- IN
- | OUT
- | INOUT
+ in
+ | out
+ | inout
java-method-name is:
[package-name.]class-name.method-name
@@ -1688,15 +1629,14 @@ java-parameter-list is:
java-datatype[, java-datatype]...
external-security-type is:
- DEFINER
- | INVOKER
+ definer
+ | invoker
```
-NOTE: Delimited variables in this syntax diagram are case-sensitive. Case-sensitive variables include _java-method-name_,
+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.
+the remaining syntax is not case-sensitive.
-<<<
[[create_procedure_syntax]]
=== Syntax Description of CREATE PROCEDURE
@@ -1711,187 +1651,187 @@ 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>>.
+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. Trafodion SQL
-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.
+specify a name that is unique and does not exist for any procedure or function in the same schema. trafodion sql
+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, Trafodion SQL qualifies it according to the schema of the current session.
+if you do not fully qualify the procedure name, trafodion sql 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:
+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.
+specifies the mode in, out, or inout of a parameter. the default is in.
-**** `IN`
+**** `in`
+
-specifies a parameter that passes data to an SPJ.
+specifies a parameter that passes data to an spj.
-**** `OUT`
+**** `out`
+
-specifies a parameter that accepts data from an SPJ. The parameter must be an array.
+specifies a parameter that accepts data from an spj. the parameter must be an array.
-**** `INOUT`
+**** `inout`
+
-specifies a parameter that passes data to and accepts data from an SPJ. The parameter must be an array.
+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>>.
+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.
+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…
-| 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^
+| sql data type | maps to java data type…
+| 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.
+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>>.
+for more information, see <<data_types,data types>>.
-* `EXTERNAL NAME '_java-method-name_ [_java-signature_]'`
+* `external name '_java-method-name_ [_java-signature_]'`
** `_java-method-name_`
+
-specifies the case-sensitive name of the SPJ method of the form:
+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.
+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, you must also specify the package name.
-If you do not specify the package name, the CREATE PROCEDURE statement fails to register the SPJ.
+if the class file that contains the spj method is part of a package, 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:
+specifies the signature of the spj method and consists of:
+
`([_java-datatype_[, _java-datatype_]…])`
+
-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 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 befollowed by empty square
-brackets ([ ]), such as java.lang.Integer[].
+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 befollowed 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_.
+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_`
+* `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,
-Trafodion SQL qualifies it according to the schema of the current session.
+specifies the ansi logical name of a library containing the spj method. if you do not fully qualify the library name,
+trafodion sql qualifies it according to the schema of the current session.
-* `EXTERNAL SECURITY _external-security-type_`
+* `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
+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.
+** 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
+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
+** 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.
+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`
+* `language java`
+
-specifies that the external user-defined routine is written in the Java language.
+specifies that the external user-defined routine is written in the java language.
-* `PARAMETER STYLE JAVA`
+* `parameter style java`
+
-specifies that the run-time conventions for arguments passed to the external user-defined routine are those of the Java language.
+specifies that the run-time conventions for arguments passed to the external user-defined routine are those of the java language.
-* `NO SQL`
+* `no sql`
+
-specifies that the SPJ cannot perform SQL operations.
+specifies that the spj cannot perform sql operations.
-* `CONTAINS SQL | MODIFIES SQL DATA | READS SQL DATA`
+* `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. If you do not specify
-an SQL access mode, the default is CONTAINS SQL.
+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. if you do not specify
+an sql access mode, the default is contains sql.
-* `DYNAMIC RESULT SETS _integer_`
+* `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, the valid range is 1 to 255 inclusive.
-The actual number of result sets returned by the SPJ method can be less than or equal to this number. If you do not specify
-this option, the default value is 0 (zero), meaning that the SPJ does not return result sets.
+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, the valid range is 1 to 255 inclusive.
+the actual number of result sets returned by the spj method can be less than or equal to this number. if you do not specify
+this option, the default value is 0 (zero), meaning that the spj does not return result sets.
-* `TRANSACTION REQUIRED | NO TRANSACTION REQUIRED`
+* `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 will want the stored procedure to inherit the transaction from the calling application. However, if the SPJ method does
+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 will 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, 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>>.
+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`
+* `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,
-Trafodion SQL is not required to call the SPJ each time to produce results; instead, Trafodion SQL caches the results and
-reuses them during subsequent calls, thus optimizing the CALL statement.
+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,
+trafodion sql is not required to call the spj each time to produce results; instead, trafodion sql caches the results and
+reuses them during subsequent calls, thus optimizing the call statement.
-* `NO ISOLATE | ISOLATE`
+* `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). Trafodion SQL allows both options but always executes the SPJ in the UDR server process (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). trafodion sql allows both options but always executes the spj in the udr server process (isolate).
[[create_procedure_considerations]]
=== Considerations for CREATE PROCEDURE
@@ -1972,8 +1912,7 @@ JDBC transaction methods, Connection.commit() and Connection.rollback(), to comm
* This CREATE PROCEDURE statement registers an SPJ named LOWERPRICE, which does not accept any arguments:
+
-[source,sql]
-----
+```
SET SCHEMA SALES;
CREATE PROCEDURE lowerprice()
@@ -1982,7 +1921,7 @@ CREATE PROCEDURE lowerprice()
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA;
-----
+```
+
Because the procedure name is not qualified by a catalog and schema, Trafodion SQL 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
@@ -1990,18 +1929,16 @@ to be able to read and modify SQL data, MODIFIES SQL DATA is specified in the CR
+
To call this SPJ, use this CALL statement:
+
-[source,sql]
-----
+```
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:
+
-[source,sql]
-----
+```
CREATE PROCEDURE trafodion.sales.totalprice(IN qty NUMERIC (18),
IN rate VARCHAR (10),
INOUT price NUMERIC (18,2))
@@ -2010,14 +1947,13 @@ CREATE PROCEDURE trafodion.sales.totalprice(IN qty NUMERIC (18),
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL;
-----
+```
+
<<<
+
To call this SPJ in TrafCI, use these statements:
+
-[source,sql]
-----
+```
SET PARAM ?p 10.00;
CALL sales.totalprice(23, 'standard', ?p);
@@ -2026,28 +1962,26 @@ 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:
+
-[source,sql]
-----
+```
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:
+
-[source,sql]
-----
+```
CALL sales.monthlyorders(3, ?);
ORDERNUM
@@ -2055,14 +1989,13 @@ 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.
+
-[source,sql]
-----
+```
CREATE PROCEDURE sales.ordersummary(I
<TRUNCATED>