http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/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 28411be..ef79c62 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc @@ -64,7 +64,7 @@ transaction (BEGIN WORK…COMMIT WORK) or when AUTOCOMMIT is OFF. To run the | <<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_function_statement,CREATE FUNCTION Statement>> | Registers a user-defined function (UDF) written in C as a function within 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. @@ -82,8 +82,8 @@ referenced by the library object. | <<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. +with a database user name. +| <<unregister_user_statement, UNREGISTER USER Statement>> | Removes a database user name from the SQL database. |=== @@ -104,7 +104,7 @@ exists or inserts into a table if the row does not exist) or updates (merges) ma | <<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 +| <<values_statement,VALUES Statement>> | Displays the results of the evaluation of the expressions and the results of row sub queries within the row value constructors. |=== @@ -138,11 +138,11 @@ Use these statements to register users, create roles, and grant and revoke privi | <<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. +| <<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 username from the SQL database. +| <<unregister_user_statement,UNREGISTER USER Statement>> | Removes a database user name from the SQL database. |=== <<< @@ -260,14 +260,14 @@ ALTER 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. +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. +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_` + @@ -457,7 +457,7 @@ duplicate values are allowed unless the column is part of the primary key. colum + 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. +sub queries in a check constraint. <<< **** `references _ref-spec_` @@ -512,7 +512,7 @@ unique index does not exist, the system creates a unique index. + 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. +constraint, and you cannot use sub queries in a check constraint. <<< *** `foreign key (_column-list_) references _ref-spec_ not enforced` @@ -597,7 +597,7 @@ following must be true: * 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 username or through a granted role. +** 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. @@ -766,7 +766,7 @@ if you do not fully qualify the procedure name, trafodion sql qualifies it accor * `_argument-list_` + -accepts arguments for in, inout, or out parameters. the arguments consist of sql expressions, including dynamic parameters, +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_}…]` @@ -779,7 +779,7 @@ each expression must evaluate to a value of one of these data types: ** date-time value ** numeric value + -interval value expressions are disallowed in spjs. for more information, see +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>>. + @@ -792,7 +792,7 @@ do not specify result sets in the argument list. ==== 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 rowsets. +such as TrafCI. You cannot use a CALL statement inside a compound statement or with row sets. [[call_required_privileges]] ==== Required Privileges @@ -816,7 +816,7 @@ parameter argument, use one of these SQL expressions: * Literal * SQL function (including CASE and CAST expressions) * Arithmetic or concatenation operation -* Scalar subquery +* Scalar sub query * Dynamic parameter (for example, ?) in an application * Named (for example, ?param) or unnamed (for example, ?) parameter in TrafCI @@ -827,7 +827,7 @@ For an INOUT parameter argument, you can use only a dynamic, named, or unnamed p ==== 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 HPDCI (for example, ?param or ?). Each +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]] @@ -1243,7 +1243,7 @@ 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 +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` @@ -1262,7 +1262,7 @@ specifies that the function does not perform sql operations. this clause is opti * `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 +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. @@ -1364,7 +1364,7 @@ hbase-options-list is: * `_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 namespace within a schema, so an index name might be the same as a table or constraint +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_` @@ -1380,7 +1380,7 @@ ASCENDING or DESCENDING specifies the storage and retrieval order for rows in th + 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 nonunique index, their order is based on the sequence specified for the columns of the key of the +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_']…)` @@ -1448,8 +1448,8 @@ Indexes are created under a single transaction. When an index is created, the fo 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 online). -* The transaction is committed, either by the system or later by the requestor. +* 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. @@ -1465,7 +1465,7 @@ when the operation begins, CREATE INDEX waits until its lock request is granted You cannot access an index directly. <<< -[[create_index_required_priviges]] +[[create_index_required_privileges]] ==== Required Privileges To issue a CREATE INDEX statement, one of the following must be true: @@ -1481,7 +1481,7 @@ NOTE: In this case, if you create an index in a private schema, it will be owned [[create_index_limits]] ==== Limits on Indexes -For nonunique indexes, the sum of the lengths of the columns in the index plus the sum of the length of +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. @@ -1521,7 +1521,7 @@ 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 +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>> @@ -1530,7 +1530,7 @@ 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. +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 @@ -1643,7 +1643,7 @@ the remaining syntax is not case-sensitive. * `_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 methaod. +the SPJ method. ** `_procedure-ref_` + @@ -1656,13 +1656,13 @@ see <<identifiers,identifiers>> and <<database_object_names,database object name + 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. +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. ** _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_` @@ -1672,15 +1672,15 @@ specifies the mode in, out, or inout of a parameter. the default is in. **** `in` + -specifies a parameter that passes data to an spj. +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. +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. +specifies a parameter that passes data to and accepts data from an SPJ. the parameter must be an array. *** `_sql-identifier_` + @@ -1688,7 +1688,7 @@ specifies an sql identifier for the parameter. for more information, see <<ident *** `_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: + @@ -1729,19 +1729,19 @@ for more information, see <<data_types,data types>>. ** `_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. + -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_]…])` + @@ -1750,7 +1750,7 @@ primitive data type (for example, int). an sql data type maps to a java primitiv + 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 +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[]. <<< @@ -1760,21 +1760,21 @@ specifies a mappable java data type. for the mapping of the java data types to s * `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, +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_` + -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. +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 +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. @@ -1782,8 +1782,8 @@ more sensitive or critical data to which users should not have access. 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. +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` @@ -1796,42 +1796,42 @@ specifies that the run-time conventions for arguments passed to the external use * `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` + -specifies that the spj can perform sql operations. all these options behave the same as contains sql, meaning that the spj +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_` + -specifies the maximum number of result sets that the spj can return. this option is applicable only if the method signature +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. +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` + -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>>. +<<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 +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 +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` + -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 @@ -2078,7 +2078,7 @@ specifies a registered database username to whom you assign the role owner. === 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 usernames beginning with DB are reserved. You cannot specify a _role-name_ with any such name. +* 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 @@ -2486,7 +2486,7 @@ is a constraint that specifies a condition that must be satisfied for each row i 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. +sub queries in a CHECK constraint. * `REFERENCES _ref-spec_` + @@ -2542,7 +2542,7 @@ directs Trafodion SQL to use partition definitions from _source-table_. Each new as its original _source-table_ counterpart. The new table partitions do not inherit partition names from the original table. Instead, Trafodion SQL generates new names based on the physical file location. + -If you specify the LIKE clause and the SALT USING _num_ PARITIONS clause, you cannot specify WITH PARTITIONS. +If you specify the LIKE clause and the SALT USING _num_ PARTITIONS clause, you cannot specify WITH PARTITIONS. <<< [[create_table_considerations]] @@ -2585,7 +2585,7 @@ If the constraint refers to the other table in a query expression, you must also [[create_volatile_table_considerations]] ==== Considerations for CREATE VOLATILE TABLE -* Volatile temporary tables are closely linked to the session. Their namespace is unique across multiple concurrent +* Volatile temporary tables are closely linked to the session. Their name space is unique across multiple concurrent sessions, and therefore allow multiple sessions to use the same volatile temporary table names simultaneously without any conflicts. * Volatile tables support creation of indexes. @@ -2962,7 +2962,7 @@ create table t(a int) as select b,c from t1 An error is returned. Two items need to be specified as part of the table-attributes. * The _column-attributes_ must specify either the _column-name datatype-info_ pair or just the _column-name_ for all -columns. Youcannot specify some columns with just the name and others with name and data type. +columns. You cannot specify some columns with just the name and others with name and data type. + ``` create table t(a int, b) as select c,d from t1 @@ -3089,7 +3089,7 @@ the objects underlying the view, and one of the following must be true: NOTE: In this case, if you create a view in a private schema, it will be owned by the schema owner. When you create a view on a single table, the owner of the view is automatically given all privileges WITH GRANT OPTION -on the view. However, when you create a view that spans multiple tables, the owner ofthe view is given only SELECT +on the view. However, when you create a view that spans multiple tables, the owner of the view is given only SELECT privileges WITH GRANT OPTION. If you try to grant privileges to another user on the view other than SELECT, you will receive a warning that you lack the grant option for that privilege. @@ -3104,7 +3104,7 @@ To define an updatable view, a query expression must also meet these requirement * It cannot contain a GROUP BY or HAVING clause. * It cannot directly contain the keyword DISTINCT. * The FROM clause must refer to exactly one table or one updatable view. -* It cannot contain a WHERE clause that contains a subquery. +* It cannot contain a WHERE clause that contains a sub query. * The select list cannot include expressions or functions or duplicate column names. [[create_view_order_by_clause_guidelines]] @@ -3121,7 +3121,7 @@ ORDER BY clause. ===== When to Use ORDER BY An ORDER BY clause is used in a view definition only when the clause is under the root of the Select query that uses that -view. If the ORDER BY clause appears in other intermediate locations or in a subquery, it is ignored. +view. If the ORDER BY clause appears in other intermediate locations or in a sub query, it is ignored. Consider this CREATE VIEW statement: @@ -3136,7 +3136,7 @@ insert into t1 select * from v; ``` In these two examples, the ORDER BY clause is ignored during DML processing because the first appears as part of a -derived table and the second as a subquery selects, both created after the view expansion. +derived table and the second as a sub query selects, both created after the view expansion. If the same query is issued using explicit derived tables instead of a view, a syntax error is returned: @@ -3144,7 +3144,7 @@ If the same query is issued using explicit derived tables instead of a view, a s select * from (select a from t order by a) x, (select a from t order by a) y; ``` -This example returns a syntax error because an ORDER BY clause is not supported in a subquery. +This example returns a syntax error because an ORDER BY clause is not supported in a sub query. The ORDER BY clause is ignored if it is part of a view and used in places where it is not supported. This is different than returning an error when the same query was written with explicit ORDER BY clause, as is shown in the preceding examples. @@ -3257,14 +3257,14 @@ Trafodion SQL has the ability to eliminate redundant joins in a query. Redundant * Every row from this child will match one and only one row from the other child Suppose tables A and B denote generic tables. To check if the rule "every row from this child will match one and only one row -from the other child" is true, Trafodion SQL uses the fact that the join of Table A with table or subquery B preserves all the +from the other child" is true, Trafodion SQL uses the fact that the join of Table A with table or sub query B preserves all the rows of A if the join predicate contains an equi-join predicate that references a key of B, and one of the following is true: The join is a left outer join where B is the inner table. In this example, for the join between vp0 and vp1,vp0 fills the role of table A and vp1 fills the role of table B. For the join between vp1 and vp2, vp1 fills the role of table A and vp2 fills the role of table B. -The view vp shown in this example uses left outer joins to combine thethree underlying tables. Therefore, if the select list -in a query that accesses vp does not contain column d from vp2 then the join to tablevp2 in the view vp will not be performed. +The view vp shown in this example uses left outer joins to combine the three underlying tables. Therefore, if the select list +in a query that accesses vp does not contain column d from vp2 then the join to table vp2 in the view vp will not be performed. ``` create table vp0(a integer not null, b integer, primary key(a)); @@ -3339,7 +3339,7 @@ The default access option is the isolation level of the containing transaction. ==== Authorization Requirements DELETE requires authority to read and write to the table or view being deleted from and authority to read tables or views -specified in subqueries used in the search condition. +specified in sub queries used in the search condition. [[delete_transaction-initiation-and-termination]] ==== Transaction Initiation and Termination @@ -3394,7 +3394,7 @@ DELETE FROM invent.partsupp WHERE suppnum NOT IN --- 41 row(s) deleted. ``` -* This is an example of a self-referencing DELETE statement, where the table from which rows are deleted is scanned in a subquery: +* This is an example of a self-referencing DELETE statement, where the table from which rows are deleted is scanned in a sub query: + ``` delete from table1 where a in (select a from table1 where b > 200) @@ -3419,7 +3419,7 @@ DROP FUNCTION [[catalog-name.]schema-name.]function-name * `[[_catalog-name_.]_schema-name_.]_function-name_` + -specifies the ansi logical name of the function, where each part of the name is a valid sql identifier with a maximum of +specifies the ANSI logical name of the function, where each part of the name is a valid sql identifier with a maximum of 128 characters. specify the name of a function that has already been registered in the schema. if you do not fully qualify the function 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>>. @@ -3532,7 +3532,7 @@ DROP LIBRARY [[catalog-name.]schema-name.]library-name [restrict | cascade] * `[[_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 +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>>. @@ -3540,7 +3540,7 @@ for more information, see <<identifiers,identifiers>> and <<database_object_name * `[restrict | cascade]` + if you specify restrict, the drop library operation fails if any stored procedures in java (spjs) or user-defined -functions (udfs) were created based on the specified library. +functions (UDFs) were created based on the specified library. + if you specify cascade, any such dependent procedures or functions are removed as part of the drop library operation. + @@ -3602,7 +3602,7 @@ DROP PROCEDURE [[catalog-name.]schema-name.]procedure-name * `[[_catalog-name_.]_schema-name_.]_procedure-name_` + -specifies the ansi logical name of the stored procedure in java (spj), where each part of the name is a valid sql +specifies the ANSI logical name of the stored procedure in java (SPJ), where each part of the name is a valid sql identifier with a maximum of 128 characters. specify the name of a procedure that has already been registered in the schema. if you do not fully qualify the procedure name, trafodion sql qualifies it according to the schema of the current session. @@ -3639,7 +3639,7 @@ DROP PROCEDURE totalprice; ``` <<< -[[drop_role_statemment]] +[[drop_role_statement]] == DROP ROLE Statement The DROP ROLE statement deletes an SQL role. See <<roles,Roles>>. @@ -4238,11 +4238,11 @@ the catalog is TRAFODION, and the schema is SEABASE. * `FUNCTIONS FOR LIBRARY [[_catalog-name_.]_schema-name_.]_library-name_` + -displays the udfs that reference the specified library. +displays the UDFs that reference the specified library. * `functions in schema [_catalog-name_.]_schema-name_` + -displays the names of all the udfs in the specified schema. +displays the names of all the UDFs in the specified schema. * `libraries` + @@ -4698,7 +4698,7 @@ System Version 0.9.1. Expected Version 1.0.0. ``` <<< -[[get_version_of_software_examles]] +[[get_version_of_software_examples]] === Examples of GET VERSION OF SOFTWARE * This GET VERSION OF SOFTWARE statement displays the software version for Trafodion Release 1.0.0: @@ -4789,11 +4789,11 @@ specifies the privileges to grant. you can specify these privileges for an objec | insert | can use the insert statement. | references | can create constraints that reference the object. | update | can use the update statement on table objects. -| execute | can execute a stored procedure using a call statement or can execute a user-defined function (udf). +| execute | can execute a stored procedure using a call statement or can execute a user-defined function (UDF). | usage | can access a library using the create procedure or create function statement. this privilege provides you with read access to the libraryâs underlying library file. | all | all the applicable privileges. when you specify all for a table or view, this includes the select, delete, -insert, references, and update privileges. when the object is a stored procedure or user-defined function (udf), only the +insert, references, and update privileges. when the object is a stored procedure or user-defined function (UDF), only the execute privilege is applied. when the object is a library, only the update and usage privileges are applied. |=== @@ -4802,10 +4802,10 @@ execute privilege is applied. when the object is a library, only the update and specifies an object on which to grant privileges. _object-type_ can be: ** `[table] [_schema_.]_object_`, where _object_ is a table or view. see <<database_object_names,database object names>>. -** `[procedure] [_schema_.]_procedure-name_`, where _procedure-name_ is the name of a stored procedure in java (spj) +** `[procedure] [_schema_.]_procedure-name_`, where _procedure-name_ is the name of a stored procedure in java (SPJ) registered in the database. ** `[library] [_schema_.]_library-name_`, where _library-name_ is the name of a library object in the database. -** `[function] [_schema_.]_function-name_`, where _function-name_ is the name of a user-defined function (udf) in the database. +** `[function] [_schema_.]_function-name_`, where _function-name_ is the name of a user-defined function (UDF) in the database. * `to {_grantee_ [, _grantee_] … }` + @@ -4947,7 +4947,7 @@ specifies an _auth-name_ to which you grant component privileges. + specifies the name of an authorization ID to which you grant privileges. See <<authorization_ids,Authorization IDs>>. The authorization ID must be a registered database username, existing role name, or PUBLIC. The name is a regular or -delimited case-insensitive identifier. See <<case_insensitive_delimited_iddentifiers,Case-Insensitive Delimited Identifiers>>. +delimited case-insensitive identifier. See <<case_insensitive_delimited_identifiers,Case-Insensitive Delimited Identifiers>>. If you grant a privilege to PUBLIC, the privilege remains available to all users, unless it is later revoked from PUBLIC. * `WITH GRANT OPTION` @@ -5103,7 +5103,7 @@ when all columns in _table_ have default values. ==== Authorization Requirements INSERT requires authority to read and write to the table or view receiving the data and authority to read tables or views -specified in the query expression (or any of its subqueries) in the INSERT statement. +specified in the query expression (or any of its sub queries) in the INSERT statement. [[insert_transaction_initiation_and_termination]] ==== Transaction Initiation and Termination @@ -5133,11 +5133,11 @@ isolation level of the containing transaction. Each statement then executes with ==== Use of a VALUES Clause for the Source Query Expression If the query expression consists of the VALUES keyword followed by rows of values, each row consists of a list of value expressions -or a row subquery (a subquery that returns a single row of column values). A value in a row can also be a scalar subquery (a subquery +or a row sub query (a sub query that returns a single row of column values). A value in a row can also be a scalar sub query (a sub query that returns a single row consisting of a single column value). Within a VALUES clause, the operands of a value expression can be numeric, string, datetime, or interval values; however, an operand -cannot reference a column (except in the case of a scalar or row subquery returning a value or values in its result table). +cannot reference a column (except in the case of a scalar or row sub query returning a value or values in its result table). [[insert_requirements_for_inserted_rows]] ==== Requirements for Inserted Rows @@ -5155,11 +5155,11 @@ be inserted must be compatible with the data types of the corresponding target c Any character string data type is compatible with all other character string data types that have the same character set. For fixed length, an inserted value shorter than the column length is padded on the right with blank characters of the appropriate character set (for example, -ISO88591 blanks (HEX20). If the value is longer than the column length, string truncation of nonblank trailing characters returns an error, and +ISO88591 blanks (HEX20). If the value is longer than the column length, string truncation of non-blank trailing characters returns an error, and the truncated string is not inserted. For variable length, a shorter inserted value is not padded. As is the case for fixed length, if the value is longer than the column length, -string truncation of nonblank trailing characters returns an error, and the truncated string is not inserted. +string truncation of non-blank trailing characters returns an error, and the truncated string is not inserted. ===== Inserting Numeric Values @@ -5297,7 +5297,7 @@ a credit rating that is different from the default, you must update this column insert into table1 select pk+?, b, c from table1 ``` -* This is an example of a self-referencing insert where the target of the insert, table1, is also used in a subquery of the insert-source: +* This is an example of a self-referencing insert where the target of the insert, table1, is also used in a sub query of the insert-source: + ``` insert into table1 @@ -5481,13 +5481,13 @@ use case: * The MERGE statement does not use ESP parallelism. * A merged table cannot be a view. * Merge is not allowed if the table has constraints. -* The _on-clause_ cannot contain a subquery. This statement is not allowed: +* The _on-clause_ cannot contain a sub query. This statement is not allowed: + ``` MERGE INTO t ON a = (SELECT a FROM t1) WHEN ... ``` -* The optional WHERE predicate in the when-matched clause cannot contain a subquery or an aggregate function. +* The optional WHERE predicate in the when-matched clause cannot contain a sub query or an aggregate function. These statements are not allowed: + ``` @@ -5500,14 +5500,14 @@ MERGE INTO t ON a=10 WHEN NOT MATCHED THEN INSERT VALUES (10,30); ``` -* The UPDATE SET clause in a MERGE statement cannot contain a subquery. +* The UPDATE SET clause in a MERGE statement cannot contain a sub query. This statement is not allowed: + ``` MERGE INTO t ON a = 1 WHEN MATCHED THEN UPDATE SET b = (SELECT a FROM t1) ``` -* The _insert-values-list_ clause in a MERGE statement cannot contain a subquery. This statement is not allowed: +* The _insert-values-list_ clause in a MERGE statement cannot contain a sub query. This statement is not allowed: + ``` MERGE INTO t ON a = 1 WHEN NOT MATCHED THEN INSERT VALUES ((SELECT a FROM t1)) @@ -5724,19 +5724,19 @@ to type than the directory-service username. For example, if the user logs on as `[email protected]`, you might want to assign the user a database username of JDoe. -Database usernames are authorization IDs. If you specify a name already assigned to another user or to an existing +Database user names are authorization IDs. If you specify a name already assigned to another user or to an existing role, the command will fail. For more information, see <<authorization_ids,Authorization IDs>>. [[register_user_reserved_names]] ==== Reserved Names -PUBLIC, _SYSTEM, NONE, and database usernames beginning with DB are reserved. You cannot register users with any +PUBLIC, _SYSTEM, NONE, and database user names beginning with DB are reserved. You cannot register users with any such name. [[register_user_username_length]] ==== Username Length -Database usernames are limited to 128 characters. +Database user names are limited to 128 characters. [[register_user_examples]] === Examples of REGISTER USER @@ -6434,7 +6434,7 @@ does not specify a return list. **** `_col-expr_ [[AS] _name_]` + specifies a derived column determined by the evaluation of an SQL value expression in the list. Any column referred to in -a value expression can be specified as being from the row in the old table exposed by the pdate or can be specified as being +a value expression can be specified as being from the row in the old table exposed by the update or can be specified as being from the row in the new table exposed by the update. + For example: @@ -6488,7 +6488,7 @@ If you specify a CROSS join as the _join-type_, you cannot specify a NATURAL joi *** `_join-spec_.` + If you specify an INNER, LEFT, RIGHT, or FULL join as the _join-type_ and you do not specify a NATURAL join, you -must use an ON clause as the _join-spec_, as follows: Subqueries are not allowed in the join predicate of FULL OUTER JOIN. +must use an ON clause as the _join-spec_, as follows: Sub queries are not allowed in the join predicate of FULL OUTER JOIN. *** `ON _search-condition_` + @@ -6554,8 +6554,8 @@ A _simple-table_ can be specified as: + A _simple-table_ can be a table value constructor. It starts with the VALUES keyword followed by a sequence of row value constructors, each of which is enclosed in parentheses. A _row-value-const_ is a list of expressions (or NULL) -or a row subquery (a subquery that returns a single row of column values). An operand of an expression cannot reference -a column (except when the operand is a scalar subquery returning a single column value in its result table). +or a row sub query (a sub query that returns a single row of column values). An operand of an expression cannot reference +a column (except when the operand is a scalar sub query returning a single column value in its result table). + The use of NULL as a _row-value-const_ element is a Trafodion SQL extension. + @@ -6574,7 +6574,7 @@ The _search-condition_ is applied to each row of the result table derived from t or, in the case of multiple table references, the cross-product of result tables derived from the individual table references. + Each column you specify in _search-condition_ is typically a column in this intermediate result table. In the case of nested -subqueries used to provide comparison values, the column can also be an outer reference. See<<subquery,Subquery>>. +sub queries used to provide comparison values, the column can also be an outer reference. See<<subquery,Subquery>>. + To comply with ANSI standards, Trafodion SQL does not move aggregate predicates from the WHERE clause to a HAVING clause and does not move non-aggregate predicates from the HAVING clause to the WHERE clause. @@ -6637,7 +6637,7 @@ optionally qualified by a table, view, or correlation name; for example, CUSTOME ** `ASC | DESC` + specifies the sort order. The default is ASC. When Trafodion SQL orders an intermediate result table on a column that can -contain null, nulls are considered equal to one another but greater than all other nonnull values. +contain null, nulls are considered equal to one another but greater than all other non-null values. <<< * `GROUP BY [_col-expr_] {_colname_ | _colnum_} [,{_colname_ | _colnum_}]…]` @@ -6723,7 +6723,7 @@ specifies a column by its position in _select-list_. Use _colnum_ to refer to un ** `ASC | DESC` + specifies the sort order. The default is ASC. For ordering a result table on a column that can contain null, nulls are -considered equal to one another but greater than all other nonnull values. +considered equal to one another but greater than all other non-null values. + See <<considerations_for_order_by,Considerations for ORDER BY>>. @@ -6774,8 +6774,8 @@ support this type of query. ==== Restrictions on Embedded Inserts * An embedded INSERT cannot be used in a join. -* An embedded INSERT cannot appear in a subquery. -* An embedded INSERT statement cannot have a subquery in the WHERE clause. +* An embedded INSERT cannot appear in a sub query. +* An embedded INSERT statement cannot have a sub query in the WHERE clause. * An INSERT statement cannot contain an embedded INSERT statement. * A union between embedded INSERT expressions is not supported. * Declaring a cursor on an embedded INSERT statement is not supported. @@ -6829,7 +6829,7 @@ of columns in the SELECT result table dependent on the order of columns in the c or views. * A _col-expr_ is a single column name or a derived column. A derived column is an SQL value expression; its operands can be numeric, string, datetime, or interval literals, columns, functions (including aggregate unctions) defined on columns, -scalar subqueries, CASE expressions, or CAST expressions. Any single columns named in _col-expr_ must be from tables or views +scalar sub queries, CASE expressions, or CAST expressions. Any single columns named in _col-expr_ must be from tables or views specified in the FROM clause. For a list of aggregate functions, see <<aggregate_functions,Aggregate (Set) Functions>>. * If _col-expr_ is a single column name, that column of the SELECT result table is a named column. All other columns are unnamed columns in the result table (and have the (EXPR) heading) unless you use the AS clause to specify a name for a @@ -6843,7 +6843,7 @@ derived column. arguments of an aggregate (or set) function. For example, if AGE is not a grouping column, you can refer to AGE only as the argument of a function, such as AVG (AGE). * The expression in the GROUP BY clause must be exactly the same as the expression in the select list. An error will -be returned if it is not. It cannot contain aggregate functions or subqueries. +be returned if it is not. It cannot contain aggregate functions or sub queries. * If the value of _col-expr_ is a numeric constant, it refers to the position of the select list item and is treated as the current GROUP BY using the ordinal feature. * You can specify GROUP BY using ordinals to refer to the relative position within the SELECT list. For example, @@ -6857,7 +6857,7 @@ result table form the one and only group. The result of AVG, for example, is a s When you specify an ORDER BY clause and its ordering columns, consider: * ORDER BY is allowed only in the outer level of a query or in the SELECT part of an INSERT/SELECT statement. It is not -allowed inside nested SELECT expressions, such as subqueries. +allowed inside nested SELECT expressions, such as sub queries. * If you specify DISTINCT, the ordering column must be in _select-list_. * If you specify a GROUP BY clause, the ordering column must also be a grouping column. * If an ORDER BY clause applies to a union of SELECT statements, the ordering column must be explicitly referenced, and @@ -6922,7 +6922,7 @@ SELECT A FROM T1 ORDER BY A UNION SELECT B FROM T2 SELECT A FROM T1 UNION (SELECT B FROM T2 ORDER BY A) ``` + -Because the subquery (SELECT B FROM T2…) is processed first, the ORDER BY clause does not follow the final SELECT. +Because the sub query (SELECT B FROM T2…) is processed first, the ORDER BY clause does not follow the final SELECT. [[select_group_by_clause_having_clause_and_the_union_operator]] ===== GROUP BY Clause, HAVING Clause, and the UNION Operator @@ -7139,7 +7139,7 @@ Order/Num Part/Num Unit/Price Qty/Ord Part/Num Part Description PRICE <<< * Select from three tables and display them in employee number order. Two tables are joined by using a LEFT JOIN on matching department -numbers, then an additional table is joined on matching jobcodes: +numbers, then an additional table is joined on matching job codes: + ``` SELECT empnum, first_name, last_name, deptname, location, jobdesc @@ -7248,7 +7248,7 @@ ORDERNUM (EXPR) The price for the total quantity ordered is computed for each order number. * Show employees, their salaries, and the percentage of the total payroll that their salaries represent. -Note the subquery as part of the expression in the select list: +Note the sub query as part of the expression in the select list: + ``` SELECT @@ -7534,7 +7534,7 @@ displays all users who have been granted the role in the form of GRANT ROLE stat * SHOWDDL can be used within TrafCI. * SHOWDDL [TABLE \| LIBRARY \| PROCEDURE] displays the following information: ** A constraint may be disabled. -** A table may be offline. +** A table may be off line. ** An active DDL lock may exist on an object. * SHOWDDL USER displays user information as a REGISTER USER statement. * SHOWDDL ROLE displays the role information as a CREATE ROLE statement. @@ -7716,7 +7716,7 @@ multi-column group. <<< ** `EVERY COLUMN` + -indicates that histogram statistics are to be displayed for each individual column of _table_ and any multicolumns that +indicates that histogram statistics are to be displayed for each individual column of _table_ and any multi columns that make up the primary key and indexes. For columns that do not have histograms, this option returns `No histogram data for column(s) ---]`. @@ -8036,7 +8036,7 @@ This type of SET clause allows multiple columns to be specified on the left side of the assignment operator. These columns are updated using multiple values specified on the right side of the assignment operator. The right side of the assignment operator could be simple values or a -subquery. +sub query. ** `_column1_, …, _columnN_` + @@ -8053,8 +8053,8 @@ corresponding column on the left side of the assignment operator. * `_query-expr_` + -is a SELECT subquery. Only one subquery can be specified on the right -side of a SET clause. The subquery cannot refer to the table being +is a SELECT sub query. Only one sub query can be specified on the right +side of a SET clause. The sub query cannot refer to the table being updated. For the syntax and description of _query-expr_, see the <<select_statement,SELECT Statement>>. @@ -8101,7 +8101,7 @@ the table. ==== Authorization Requirements UPDATE requires authority to read and write to the table or view being -updated and authority to read any table or view specified in subqueries +updated and authority to read any table or view specified in sub queries used in the search condition. A column of a view can be updated if its underlying column in the base table can be updated. @@ -8211,20 +8211,20 @@ UPDATE t set (b,c)=(SELECT r,t,s FROM x) ``` * If multi-column update syntax is specified and the right side contains -a subquery, only one element, the subquery, is not allowed. +a sub query, only one element, the sub query, is not allowed. + ``` UPDATE t SET (a,b)=(10, (SELECT a FROM t1)) ``` -* More than one subquery is not allowed if multiple-column syntax is +* More than one sub query is not allowed if multiple-column syntax is used. + ``` UPDATE t SET (a,b)=(SELECT x,y FROM z), (c,d)=(SELECT x,y FROM a)) ``` -* If a subquery is used, it must return at most one row. +* If a sub query is used, it must return at most one row. <<< [[update_examples]] @@ -8263,11 +8263,11 @@ WHERE deptnum IN WHERE location = 'CHICAGO'); ``` + -The subquery is evaluated for each row of the DEPT table and returns +The sub query is evaluated for each row of the DEPT table and returns department numbers for departments located in Chicago. * This is an example of a self-referencing UPDATE statement, where the -table being updated is scanned in a subquery: +table being updated is scanned in a sub query: + ``` UPDATE table3 SET b = b + 2000 WHERE a, b = @@ -8335,8 +8335,8 @@ data. You cannot qualify or repeat a column name. * `_query-expr_` + -is a SELECT subquery that returns data to be inserted into or updated in -the target _table_. The subquery cannot refer to the table being +is a SELECT sub query that returns data to be inserted into or updated in +the target _table_. The sub query cannot refer to the table being operated on. For the syntax and description of _query-expr_, see the <<select_statement,SELECT Statement>>. @@ -8390,7 +8390,7 @@ VALUES (3600,âJAPAN SALESâ, 996); The VALUES statements starts with the VALUES keyword followed by a sequence of row value constructors, each of which is enclosed in parenthesis. It displays the results of the evaluation of the -expressions and the results of row subqueries within the row value +expressions and the results of row sub queries within the row value constructors. ``` @@ -8406,13 +8406,13 @@ row-value-const is: * `_row-value-const_` + -specifies a list of expressions (or NULL) or a row subquery (a subquery +specifies a list of expressions (or NULL) or a row sub query (a sub query that returns a single row of column values). An operand of an expression -cannot reference a column (except when the operand is a scalar subquery +cannot reference a column (except when the operand is a scalar sub query returning a single column value in its result table). + The results of the evaluation of the expressions and the results of the -row subqueries in the row value constructors must have compatible data +row sub queries in the row value constructors must have compatible data types. [[values_considerations]] @@ -8451,7 +8451,7 @@ VALUES (1,2,3), (4,5,6); ``` * This VALUES statement displays the results of the expressions and the -row subquery in the lists: +row sub query in the lists: + ``` VALUES (1+2, 3+4), (5, (select count (*) from t));
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---------------------------------------------------------------------- diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc index 63c57d6..bdf25cf 100644 --- a/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc +++ b/docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc @@ -64,7 +64,7 @@ The Trafodion bulk load process takes place in two phases: * _Preparation phase_: In this phase, Trafodion reads the data from the source files in Hive or HDFS, partitions the data based on the target table's partitioning scheme, sorts the data, and then generates -KeyValue pairs that will populate the HFiles. Trafodion also encodes the +Key-value pairs that will populate the HFiles. Trafodion also encodes the data for faster storage and retrieval. * _Loading-the-files-into-HBase phase_: This phase uses the LoadIncrementalHFiles (also known as the computebulkload tool) and load @@ -147,7 +147,7 @@ data from the parent table. * `UPSERT USING LOAD` + -specifies that the data be inserted into the target table using rowset +specifies that the data be inserted into the target table using row set inserts without a transaction. <<< @@ -294,7 +294,7 @@ index-option is: + is an SQL identifier that specifies the simple name for the index. You cannot qualify _index_ with its schema name. Indexes have their own -namespace within a schema, so an index name might be the same as a table +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. @@ -305,7 +305,7 @@ is the name of the table for which to populate the index. See * `ONLINE` + -specifies that the populate operation should be done online. That is, +specifies that the populate operation should be done on-line. That is, ONLINE allows read and write DML access on the base table while the populate operation occurs. Additionally, ONLINE reads the audit trail to replay updates to the base table during the populate phase. If a lot of @@ -315,7 +315,7 @@ contention to the audit trail. The default is ONLINE. * `OFFLINE` + -specifies that the populate should be done offline. OFFLINE allows only +specifies that the populate should be done off-line. OFFLINE allows only read DML access to the base table. The base table is unavailable for write operations at this time. OFFLINE must be specified explicitly. SELECT is allowed. @@ -331,16 +331,16 @@ When POPULATE INDEX is executed, the following steps occur: If a failure occurs, the rollback is faster because it does not have to process a lot of audit. Also, if a failure occurs, the index remains -empty, unaudited, and not attached to the base table (offline). +empty, unaudited, and not attached to the base table (off-line). -* When an offline POPULATE INDEX is being executed, the base table is -accessible for read DML operations. When an online POPULATE INDEX is +* When an off-line POPULATE INDEX is being executed, the base table is +accessible for read DML operations. When an on-line POPULATE INDEX is being executed, the base table is accessible for read and write DML operations during that time period, except during the commit phase at the very end. * If the POPULATE INDEX operation fails unexpectedly, you may need to drop the index again and re-create and repopulate. -* Online POPULATE INDEX reads the audit trail to replay updates by +* On-line POPULATE INDEX reads the audit trail to replay updates by allowing read/write access. If you plan to create many indexes in parallel or if you have a high level of activity on the audit trail, you should consider using the OFFLINE option. @@ -704,7 +704,7 @@ For more information about specifying columns, see * `EVERY COLUMN` + The EVERY COLUMN keyword indicates that histogram statistics are to be -generated for each individual column of _table_ and any multicolumns +generated for each individual column of _table_ and any multi-columns that make up the primary key and indexes. For example, _table_ has columns A, B, C, D defined, where A, B, C compose the primary key. In this case, the ON EVERY COLUMN option generates a single column @@ -914,7 +914,7 @@ Generate statistics for columns most often used in data access plans for a tableâthat is, the primary key, indexes defined on the table, and any other columns frequently referenced in predicates in WHERE or GROUP BY clauses of queries issued on the table. Use the EVERY COLUMN option to -generate histograms for every individual column or multicolumns that +generate histograms for every individual column or multi columns that make up the primary key and indexes. The EVERY KEY option generates histograms that make up the primary key http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/advocate.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/advocate.md b/docs/src/site/markdown/advocate.md deleted file mode 100644 index 425c70e..0000000 --- a/docs/src/site/markdown/advocate.md +++ /dev/null @@ -1,15 +0,0 @@ -<!-- - Licensed 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. ---> -This page provides how to contribute to Trafodion via advocacy. Please refer to the [Contribute](contribute.html) page for information about other ways to contribute to the Trafodion project. \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/build-tools-manual.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/build-tools-manual.md b/docs/src/site/markdown/build-tools-manual.md deleted file mode 100644 index 6c02481..0000000 --- a/docs/src/site/markdown/build-tools-manual.md +++ /dev/null @@ -1,197 +0,0 @@ -<!-- - Licensed 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. ---> -This page describes how to perform manual installs of the required [Trafodion Build Tools](setup-build-environment.html#Install_Build_Tools). - -In the sections below, the **```<tool installation directory>```** is the directory where you want the tool to be installed. - -# MPICH -**Tested Version**: 3.0.4 - -**Download**: http://www.mpich.org/static/downloads/3.0.4/mpich-3.0.4.tar.gz (http://www.mpich.org/downloads) - -**Considerations**: For more detailed instructions, see the README file that comes with the source. - -**Install**: - - tar -xzf mpich-3.0.4.tar.gz - cd mpich-3.0.4 - ./configure --prefix=<tool installation directory>/dest-mpich-3.0.4 --with-device=ch3:sock --disable-f77 --disable-fc - make - make check - make install - -**```<tool installation directory>```** is the directory where you want MPICH to be installed. If you do not specify the **```--prefix```** option, the default location is **```/usr/local``**. - -# Bison -**Tested Version**: 3.0 - -**Download**: http://ftp.gnu.org/gnu/bison/bison-3.0.tar.gz (http://ftp.gnu.org/gnu/bison/) - -**Considerations**: Refer to the bison INSTALL file for detailed instructions. - -**Determine Bison Version**: - - which bison - bison --version - -If the version is older than 3.0, then do the following: - - tar -xzf bison-3.0.tar.gz - cd bison-3.0 - ./configure --prefix=<tool installation directory>/bison_3_linux - make - make check - make install - -**Note**: The **```make check```** step may return errors like the following that can be ignored: - - make[3]: Entering directory `<mydir>/bison-3.0' - YACC examples/calc++/calc++-parser.stamp - CXX examples/calc++/examples_calc___calc__-calc++-driver.o - LEX examples/calc++/calc++-scanner.cc - CXX examples/calc++/examples_calc___calc__-calc++-scanner.o - g++: ./examples/calc++/calc++-scanner.cc: No such file or directory - g++: no input file - -Adjust your **```PATH```** to ensure that the correct version is chosen. Rerun the **```bison --version```** to verify. - -# Udis86 -**Tested Version**: 1.7.2 - -**Download**: http://sourceforge.net/projects/udis86/files/udis86/1.7/udis86-1.7.2.tar.gz (http://udis86.sourceforge.net) - -**Consideration**: Udis86 is a prerequisite to building the LLVM product. - -**Install**: - - tar xzf udis86-1.7.2.tar.gz - cd udis86-1.7.2 - ./configure --prefix=<tool installation directory>/udis86-1.7.2 --enable-shared - make - make install - -# LLVM -**Tested Version**: 3.2 - -**Download**: http://llvm.org/releases/3.2/llvm-3.2.src.tar.gz (http://llvm.org/releases/download.html) - -**Consideration**: Udis86 must be installed on the system before LLVM is built and installed. Building LLVM takes some time to complete, be patient. - -**Install**: - - # Set BASE_DIR to the top-level directory where the LLVM source will be - # unpacked and the objects compiled. - BASE_DIR=<your-base-dir> - cd $BASE_DIR - tar xzf llvm-3.2.src.tar.gz - - export MY_UDIS_INSTALL_DIR=<udis-installation-directory>/udis86-1.7.2 - export MY_LLVM_INSTALL_DIR=<llvm-installation-directory>/dest-llvm-3.2/ - export MY_LLVM_SRC_DIR=$BASE_DIR/llvm-3.2.src - export MY_LLVM_OBJ_DIR=$BASE_DIR/llvm-3.2.obj/ - export LD_LIBRARY_PATH=$MY_UDIS_INSTALL_DIR/lib:$LD_LIBRARY_PATH - export C_INCLUDE_PATH=$MY_UDIS_INSTALL_DIR/include - export CPATH=$MY_UDIS_INSTALL_DIR/include - - mkdir -p $MY_LLVM_OBJ_DIR/release - cd $MY_LLVM_OBJ_DIR/release - - $MY_LLVM_SRC_DIR/configure --prefix=$MY_LLVM_INSTALL_DIR/release \ - --enable-optimized --enable-jit \ - --enable-shared --enable-targets=x86,x86_64,cpp \ - --with-udis86=$MY_UDIS_INSTALL_DIR/lib \ - CFLAGS=-fgnu89-inline - - make libs-only - make install-libs - - mkdir -p $MY_LLVM_OBJ_DIR/debug - cd $MY_LLVM_OBJ_DIR/debug - - $MY_LLVM_SRC_DIR/configure --prefix=$MY_LLVM_INSTALL_DIR/debug \ - --enable-optimized --enable-jit \ - --enable-debug-runtime --enable-debug-symbols \ - --enable-shared --enable-targets=x86,x86_64,cpp \ - --with-udis86=$MY_UDIS_INSTALL_DIR/lib \ - CFLAGS=-fgnu89-inline - - make libs-only - make install-libs - -# ICU -**Tested Version**: 4.4.0 - -**Download**: http://download.icu-project.org/files/icu4c/4.4/icu4c-4_4-src.tgz (http://site.icu-project.org/download) - -**Install**: - - tar -xzf icu4c-4_4-src.tgz - cd icu/source - ./runConfigureICU Linux --with-library-suffix=Nv44 --prefix=<tool installation directory>/icu4.4/linux64 - make && make check - make install - -**Note**: The following **```make check```** errors can be ignored. - - [All tests passed successfully...] - Elapsed Time: 00:00:12.126 - make[2]: Leaving directory `/home/centos/icu/source/test/cintltst' - --------------- - ALL TESTS SUMMARY: - ok: testdata iotest cintltst - ===== ERRS: intltest - make[1]: *** [check-recursive] Error 1 - make[1]: Leaving directory `/home/centos/icu/source/test' - make: *** [check-recursive] Error 2 - -# Zookeeper -**Tested Version**: 3.4.5 - -**Download**: https://archive.apache.org/dist/zookeeper/zookeeper-3.4.5/zookeeper-3.4.5.tar.gz - -**Install**: - - tar -xzf zookeeper-3.4.5.tar.gz - cd zookeeper-3.4.5/src/c - ./configure --prefix=<tool installation directory>/zookeeper-3.4.5 - make - make install - -# Thrift -**Tested Version**: 0.9.0 - -**Download**: http://archive.apache.org/dist/thrift/0.9.0/ - -**Consideration**: Behind a firewall, you may need the ant flags to specify a proxy. - -**Install**: - - tar -xzf thrift-0.9.0.tar.gz - cd thrift-0.9.0 - ./configure --prefix=<tool installation dir>/thrift-0.9.0 --without-qt - make - make install - -# Maven -**Tested Version**: 3.3.3 - -**Download**: http://archive.apache.org/dist/maven/maven-3/3.3.3/binaries/apache-maven-3.3.3-bin.tar.gz. - -**Considerations**: Add Maven to your **```PATH```** once it has been installed. - -**Install**: - - tar -xzf apache-maven-3.3.3-bin.tar.gz -C <tool installation directory> - \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/build.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/build.md b/docs/src/site/markdown/build.md deleted file mode 100644 index 3140e1e..0000000 --- a/docs/src/site/markdown/build.md +++ /dev/null @@ -1,80 +0,0 @@ -<!-- - Licensed 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. ---> -This page describes how to build the Trafodion source code. - -# Prerequisites -You need to [Setup Build Environment](setup-build-environment.html) before trying to build the Trafodion source code. - -# Download Source -You should already have downloaded the source code when setting up the build environment. - -## Git -Please refer to [Making Changes](develop.html#making_changes) on the [Develop](develop.html) page. - -## tar file -The source code for Apache Trafodion can be downloaded from [Apache Trafodion Incubator Release](https://dist.apache.org/repos/dist/release/incubator) as a tar file. - -* Download the source tar file to your **```<trafodion download directory>```**. -* Check the tar file validity by checking signatures, please refer to [Verify Signatures](release.html#Verify_Signatures). The Trafodion releases have been signed using The GNU Privacy Guard. - -**Unpack the tar file** - - cd <trafodion download directory> - tar -xzf <tar file> - -# Set Up Environmental Variables -Start a new **```ssh```** session. Use the following commands to set up the Trafodion environmental variables. - - cd <Trafodion source directory> - export TOOLSDIR=<tools installation directory> - source ./env.sh - -* **```<Trafodion source directory>```**: Source tree base for Trafodion. -* **```<tools installation directory>```**: where Trafodion required tools are located. The following example assumes that you installed all the required tools in a single location. If you installed or used pre-installed tools in different directories, then you need to export the location of each tool as described in [Build Tools Manual Installation](build-tools-manual.html) prior to sourcing in **```env.sh```**. - -# Build Commands -Build a debug version of Trafodion using one of the following options. - -Command | What It Builds -------------------------------------|---------------------------------------------------------------------------------- -**```make all```** | Trafodion, DCS, and REST. -**```make package```** | Trafodion, DCS, REST, and Client Drivers. -**```make package-all```** | Trafodion, DCS, REST, Client Drivers, and tests for all components. - -If the build fails, you might want to rerun the **```make```** step. Trafodion downloads many dependencies and sometimes one of the download operations fail. Rerunning the build generally works. - -# Verify Build -Use **```sqvers -u```** to verify the build. - - $ sqvers -u - MY_SQROOT=/home/centos/apache-trafodion-1.3.0-incubating/core/sqf - who@host=centos@mysystem - JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.91.x86_64 - SQ_MBTYPE=64d (64-debug) - linux=2.6.32-504.1.3.el6.x86_64 - redhat=6.7 - NO patches - Most common Apache_Trafodion Release 1.3.0 (Build debug [centos], branch -, date 06Nov15) - UTT count is 1 - [6] Release 1.3.0 (Build debug [centos], branch -, date 06Nov15) - export/lib/hbase-trx-cdh5_3-1.3.0.jar - export/lib/hbase-trx-hbase_98_4-1.3.0.jar - export/lib/hbase-trx-hdp2_2-1.3.0.jar - export/lib/sqmanvers.jar - export/lib/trafodion-dtm-1.3.0.jar - export/lib/trafodion-sql-1.3.0.jar - -The output from the **```sqvers -u```** commands should show several jar files. The number of files differs based on the version of Trafodion you downloaded. - http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/code-organization.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/code-organization.md b/docs/src/site/markdown/code-organization.md deleted file mode 100644 index 1ba2057..0000000 --- a/docs/src/site/markdown/code-organization.md +++ /dev/null @@ -1,27 +0,0 @@ -<!-- - Licensed 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. ---> -This page describes how the Trafodion source tree is organized. - -**Under Construction** - -# Connectivity - -Directory | Contains -------------------------------------------------|---------------------------------------------- -**```core/conn/jdbc_type2```** | JDBC type-4 client-side driver -**```jdbc_type4````** | | -**```odb```** | | -**```odbc```** | | -**```security_dll```** | | -**```unixodbc```** | | http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/code.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/code.md b/docs/src/site/markdown/code.md deleted file mode 100644 index 71b76ab..0000000 --- a/docs/src/site/markdown/code.md +++ /dev/null @@ -1,32 +0,0 @@ -<!-- - Licensed 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. ---> -This page describes how you set up the Trafodion build and development-test environment. - -# Supported Platforms -Red Hat or Centos 6.x (6.4 or later) versions are supported as development and production platforms. - -# Setup Build Environment -Please refer to [Setup Build Environment](setup-build-environment.html). - -# Build Trafodion -Please refer to [Build Trafodion](build.html). - -# Setup Test Environment -Please refer to the [Test Environment](test-environment.html) page. - -# Make Changes -Please refer to [Making Changes](develop.html#Making_Changes). Ensure that you run the [Regression Tests](testing.html) before checking in changes. - - http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/contribute.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/contribute.md b/docs/src/site/markdown/contribute.md deleted file mode 100644 index c971b57..0000000 --- a/docs/src/site/markdown/contribute.md +++ /dev/null @@ -1,69 +0,0 @@ -<!-- - Licensed 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. ---> -This page describes how you can contribute to the Trafodion project. - -<div class="alert alert-dismissible alert-info"> - <button type="button" class="close" data-dismiss="alert">&close;</button> - <p style="color:black">Contributing to this project is open to every person interested in the improvement of its product(s) and/or its community â anyone willing to further the project.</p> - <p style="color:black">If you want to do more; for example, be recognized for your commitment or be eligible for privileges (privileges to commit changes to the code base or have a binding vote on the directions of this project), then please follow steps outlined on this page.</p> -</div> - -First of all, a big **Thank You** from us for your interest in evaluating and/or using Trafodion. We truly appreciate it! Trafodion exists and continues to improve thanks to you. But you can help us and yourself more! - -We are a community of volunteers working with the guidelines and policies of the Apache Software Foundation (the Apache Way) in mind. In that spirit, we welcome any kind of input you can provide to create better products. These contributions, whether you think of reporting bugs, supplying code enhancements, making improvements to our documentation, answering questions in our [mailing lists](mail-lists.html), or anywhere else are valuable to everyone. Because each of these helps all of us make Trafodion better. - -Your first step toward more involvement in our project is to subscribe to one or more of our [mailing lists](mail-lists.html). Next, please consider making your first contribution by simply sending an e-mail to our community discussing; for example: - -* What you like about Trafodion and its documentation. -* What you find missing. -* An issue you encountered. - -Or, help us by writing articles, blog posts, or sharing information about Trafodion at conferences and on social media such as Twitter (\#trafodion). - ---- - -## Contribution Ideas -You've made your first contribution. Now, weâre eager to help you get more involved. For example, you can: - -* Reply to questions, and/or share your viewpoint on how to get issues resolved. -* Add to our [FAQ](faq.html), [Web Site](website.html), or [Wiki](wiki.html). -* Share your code and document patches through our [issue tracking system](issue-tracking.html) (Jira). -* Evaluate bugs and proposed improvements provided by others in our team. -* Help with outlining and evaluating the future direction of our project and the development iterations of our products. - -## Contribution Areas -Want to be even more involved? Some areas you could consider: - -* Source code (bugs and improvements) contributions -* Documentation assistance -* Product and feature suggestions -* Detailed and constructive feedback -* Articles (case studies, white papers, etc) and presentations (speeches) about how our products helps or helped you. - -These are just ideas to get you started. If you feel we missed something there, please let us know via our mailing lists. Together, we can grow Trafodion and its community. - ----- - -## Becoming a Registered Contributor -Do you want to be recognized on the [Team page](team-list.html) like many other contributors? If so, please do the following: - -1. Send an email to the dev mailing list of the project stating that you, as a contributor, want to do more and be listed on the Team page. Any community member with edit permissions to the source code can then add your details to the list. -2. Send your [**ICLA**](https://www.apache.org/licenses/icla.txt) to the secretary of the Apache Software Foundation. Refer to the [Licenses](http://www.apache.org/licenses/) page for information about this requirement. - - As soon as you have your ICLA registration confirmed, inform the community. You can now be added (by designated space admins) to: - - * The Trafodion Confluence Contributors group, so that you can add new and edit existing wiki pages. - * The Trafodion JIRA Contributors group, so that you can assign yourself to any open issue in order to bring it further to resolution and/or closure. - http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/3063990d/docs/src/site/markdown/contributing-redirect.md ---------------------------------------------------------------------- diff --git a/docs/src/site/markdown/contributing-redirect.md b/docs/src/site/markdown/contributing-redirect.md new file mode 100644 index 0000000..2692169 --- /dev/null +++ b/docs/src/site/markdown/contributing-redirect.md @@ -0,0 +1,18 @@ +<!-- + Licensed 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. +--> +Redirecting to the Trafodion wiki... +<p><meta http-equiv="refresh" content="0; url=https://cwiki.apache.org/confluence/display/TRAFODION/Trafodion+Contributor+Guide";></meta></p> + +<!-- This page is here to ensure that we track page requests in Google Analytics. --> \ No newline at end of file
