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…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…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…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…_01…. - -*** `_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…_01…. -+ -** `_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…_01…. - -* `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_}…]` -+ -<<< -+ -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…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_]…] )` -+ -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_]…)` -+ -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…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]]]…` -+ -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_']…)` -+ -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_]…)])` 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…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…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_]…])` -+ -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… -| 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_]…])` -+ -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>
