Backup checkin
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/5e15be3e Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/5e15be3e Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/5e15be3e Branch: refs/heads/master Commit: 5e15be3ec817f4a3dacee63d81a16566d8eb3eb4 Parents: 23bfb3a Author: Gunnar Tapper <[email protected]> Authored: Tue Mar 29 00:21:01 2016 -0600 Committer: Gunnar Tapper <[email protected]> Committed: Tue Mar 29 00:21:01 2016 -0600 ---------------------------------------------------------------------- docs/.gitignore | 2 - .../src/asciidoc/_chapters/create_spjs.adoc | 448 ++++---- .../src/asciidoc/_chapters/deploy_spjs.adoc | 540 +++------- .../src/asciidoc/_chapters/develop_spjs.adoc | 116 +- .../src/asciidoc/_chapters/execute_spjs.adoc | 38 +- .../src/asciidoc/_chapters/get_started.adoc | 37 +- .../asciidoc/_chapters/grant_privileges.adoc | 34 +- .../src/asciidoc/_chapters/introduction.adoc | 25 +- .../_chapters/performance_troubleshooting.adoc | 45 +- .../src/asciidoc/_chapters/sample_spjs.adoc | 1004 ++++++++++-------- .../src/resources/source/Inventory.java | 8 +- .../spj_guide/src/resources/source/Payroll.java | 12 +- docs/spj_guide/src/resources/source/Sales.java | 36 +- .../src/resources/source/adjustSalary.java | 4 +- .../src/resources/source/employeeJob.java | 2 +- .../resources/source/invent_partloc_table.sql | 98 +- .../resources/source/invent_partsupp_table.sql | 36 +- .../src/resources/source/invent_schema.sql | 4 +- .../resources/source/invent_supplier_table.sql | 8 +- .../src/resources/source/lowerPrice.java | 6 +- .../src/resources/source/numDailyOrders.java | 2 +- .../src/resources/source/numMonthlyOrders.java | 2 +- .../src/resources/source/orderSummary.java | 10 +- .../src/resources/source/partData.java | 16 +- .../src/resources/source/partlocations.java | 4 +- .../src/resources/source/persnl_dept_table.sql | 14 +- .../resources/source/persnl_employee_table.sql | 138 +-- .../src/resources/source/persnl_job_table.sql | 8 +- .../resources/source/persnl_project_table.sql | 8 +- .../src/resources/source/persnl_schema.sql | 4 +- .../src/resources/source/projectTeam.java | 2 +- .../resources/source/sales_customer_table.sql | 8 +- .../resources/source/sales_odetail_table.sql | 152 +-- .../src/resources/source/sales_orders_table.sql | 24 +- .../src/resources/source/sales_parts_table.sql | 10 +- .../src/resources/source/sales_schema.sql | 4 +- .../src/resources/source/supplierinfo.java | 2 +- .../src/resources/source/supplyquantities.java | 2 +- .../src/resources/source/topSalesReps.java | 4 +- .../src/asciidoc/_chapters/sql_statements.adoc | 163 +-- 40 files changed, 1395 insertions(+), 1685 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/.gitignore ---------------------------------------------------------------------- diff --git a/docs/.gitignore b/docs/.gitignore deleted file mode 100644 index 56256e4..0000000 --- a/docs/.gitignore +++ /dev/null @@ -1,2 +0,0 @@ -/target/ -/*/target/ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc index 3a89bad..cccc674 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/create_spjs.adoc @@ -23,37 +23,12 @@ [[create-spjs]] = Create SPJs -After creating libraries for the SPJ JAR files on the Trafodion platform, -you can create the procedures in the Trafodion database. To create or -drop a procedure from the Trafodion database, use the HP Database Manager -(HPDM). +You can create the procedures in the Trafodion database after you've deployed the libraries for the SPJ JAR files. +See <<deploy-spj-jar-files, Deploy SPJ JAR Files>>. -Follow these procedures: - -* <<create-a-procedure, Create a Procedure>> -* <<drop-a-procedure, Drop a Procedure>> -* <<display-procedures-and-their-properties, Display Procedures and Their Properties>> - -This chapter also covers: - -* <<required-privileges-for-creating-or-dropping-an-spj, Required Privileges for Creating or Dropping an SPJ>> -* <<altering-an-spj-and-its-java-bytecode, Altering an SPJ and Its Java Bytecode>> - -== Required Privileges for Creating or Dropping an SPJ - -To create an SPJ in a schema, you must be the schema owner, or you must -have the CREATE_PROCEDURE privilege for that schema, and you must have -the CREATE_PROCEDURE privilege for the SQL_OPERATIONS component. You -must also have the USAGE privilege for the library that will be used in -the creation of the stored procedure. For more information, see the -requirements in <<create-a-procedure, Create a Procedure>>. - -To drop an SPJ from a schema, you must be the schema owner, or you must -have the DROP_PROCEDURE privilege for that schema. For more information, -see the requirements in <<drop-a-procedure, Drop a Procedure>>. - -To display the current ownership and privileges, see -<<display-procedures-and-their-properties, Display Procedures and Their Properties>>. +The examples in this chapter are based on the sample database documented +in <<b-sample-database, Appendix B: Sample Database>>. Also, the `Sales.java` class are assumed to have been +added as a library object using the `CREATE LIBRARY` statement. See <<deploy-spj-jar-files, Deploy SPJ JAR Files>>. [[create-a-procedure]] == Create a Procedure @@ -61,250 +36,163 @@ To display the current ownership and privileges, see The Create Procedure tool registers an existing Java method as a stored procedure in Java (SPJ) within SQL. -NOTE: HPDM creates procedures with default privileges only. For more -information about privileges for SPJs, see the Grant/Revoke Privileges -Tool in HPDM or the GRANT PROCEDURE statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -Requirements: - -* You must have the CREATE_PROCEDURE privilege for the SQL_OPERATIONS -component. For more information, see the component privileges in the _HP -Database Manager (HPDM) User Guide_ or the online help in HPDM. - -* You must have been granted the USAGE privilege for the library that -will be used in the creation of the stored procedure. This privilege -provides you with read access to the library's underlying JAR file, -which contains the SPJ Java method. For more information, see the -Grant/Revoke Privileges Tool in HPDM or the GRANT LIBRARY statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] +for full documentation of the +http://trafodion.apache.org/docs/sql_reference/index.html#create_procedure_statement[CREATE PROCEDURE] +statement including considerations and required privileges. -* You must either be the owner of the schema or have the -CREATE_PROCEDURE privilege for the schema to create the procedure. The -schema owner can grant such a schema-level privilege to other users or -roles. For more information, see the Grant/Revoke Privileges Tool in -HPDM or the GRANT SCHEMA statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. +[[create-procedure-settings]] +=== Create Procedure Settings -To create a procedure: +Reference: +http://trafodion.apache.org/docs/sql_reference/index.html#create_procedure_syntax[Syntax Description of CREATE PROCEDURE]. -1. In HPDM, select the Database area. +You need to determine the following settings for the CREATE PROCEDURE statement: -2. Expand the navigation tree pane so that you can see the name of the -schema in which you want to create the procedure and the Procedures -folder under the schema. - -3. Right-click the Procedures folder, and select Create Procedure. The -Create Procedure dialog box appears. - -4. In the Procedure Name field, enter a name for the stored procedure. -For detailed information about the Procedure Name field, see -<<use-the-create-procedure-dialog-box, Use the Create Procedure Dialog Box>>. - -5. In the Code group box, click [ Browse ] to find a Java method. -+ -The [ Browse ] button opens Library Browser. Use the browser to select a -method from a JAR file within a library. See -<<use-the-library-browser, Use the Library Browser>>. If a library containing the JAR file does -not exist on the Trafodion platform, you can create one by clicking the -[ Create ] button in the Library Browser. See -<<use-the-create-library-dialog-box, Use the Create Library Dialog Box>>. - -6. In the Parameters group box, verify that the SQL parameters are -mapped correctly to the Java parameters of the SPJ method. -+ -NOTE: The result set parameters (`java.sql.ResultSet[]`) in the Java -signature do not have corresponding SQL parameters. -+ -a. To change an SQL parameter, such as the name of the parameter, the -SQL data type, or the parameter mode or direction (IN, INOUT, or OUT), select the -parameter and click [ Edit ]. The Edit Parameter dialog box appears. -+ -b. In the Edit Parameter dialog box, enter a new name for the -parameter, select a different SQL data type, if permitted, or select a -different parameter mode (direction), if permitted. -+ -When changing the parameter name, note that the parameter name: -+ -* Is not case-sensitive -* Must not be a reserved word -* Must not contain spaces -* Must begin with a letter, digit, or underscore -+ -When changing the SQL data type, select a data type that is appropriate -for the parameter of the underlying Java method. For information about -SQL data types, see the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. -+ -//// -For character string parameters, select either ISO88591 or UCS2 -depending on the column definition in the database. For guidelines, see -the __Trafodion Character Sets Guide__. -//// -+ -c. Click [ OK ] to accept the changes, or click [ Cancel ] to quit the Edit Parameter dialog box. -+ -d. Repeat steps a to c for each parameter that you want to change. - -7. In the Attributes group box, if your SPJ method returns result sets, -enter the maximum number of result sets to be returned for Number of -dynamic result sets. 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. -+ -NOTE: The Number of dynamic result sets control is enabled only if the -method signature contains a java.sql.ResultSet[] object. - -8. If your SPJ method performs any SQL operations, select the Accesses -Database option. -+ -NOTE: If you do not select the Accesses Database option and your SPJ -method performs SQL operations, SQL returns an error when trying to -execute the procedure. - -9. Select either Invoker or Definer for the external security of the -stored procedure. -+ -* The Invoker setting 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_. -+ -* The Definer setting 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. +[cols="15%,40%,45%",options="header",] +|=== +| Attribute | Syntax | Guidance +| *procedure-ref* | `[[catalog-name.]schema-name.]procedure-name` | +- *catalog*: must be `trafodion`. + +- *schema-name*: the schema the procedure is associated with. + +- *procedure-name*: a name that is unique and does not exist for any procedure or function in the same schema. It's a good habit to use the lowercase version of the procedure name. +| *sql-parameter* | `[parameter-mode] [sql-identifier] sql-datatype` | +- *parameter-mode*: IN (pass data to SPJ), OUT (accept data from SPJ), or INOUT (passed data to and accepts data from SPJ). + +- *sql-identifier*: The name of the parameter passed to the SPJ. + +- *sql-datatype*: SQL data type mapped to Java data type. See <<java-parameters, Java Parameters>> for mapping, +http://trafodion.incubator.apache.org/docs/sql_reference/index.html#data_types[Data Types] for SQL data types. + + + +Repeat for each parameter in the SPJ. +| *external name* | `external name java-method-name [java-signature]` | +- *java-method-name*: 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. If the class file that contains the SPJ method is part of a +package, then you must also specify the package name. + +- *java-signature*: specifies the signature of the SPJ method and consists of `([java-datatype[, java-datatype]…])` +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`). See +http://trafodion.apache.org/docs/sql_reference/index.html#create_procedure_syntax[Syntax Description of CREATE PROCEDURE]. +for more information. +| *library* | `library [[catalog-name.]schema-name.]library-name` | +- *catalog*: must be `trafodion`. + +- *schema-name*: the schema the library is associated with. + +- *library-name*: name of library containing the SPJ method. + -For more information, see <<understand-external-security, Understand External Security>>. - -10. For the Transaction Required attribute, select either Yes to require -the procedure to run in a transaction inherited from the calling -application (the default behavior) or No to allow the procedure to run -without an inherited transaction. For more information, see -<<transaction-behavior, Transaction Behavior>>. +See <<create-a-library, Create a Library>> for more information about creating libraries. +| *external security* | `external security [ invoker` (default) \| `definer ]` | +- *invoker*: determines that users can execute, or invoke, the stored procedure using the privileges of the user who invokes +the stored procedure. + +- *definer*: determines that users can execute, or invoke, the stored procedure using the privileges of the user who created +the stored procedure. + + + +See <<understand-external-security, Understand External Security>> for more information. +| *language java* | `language java` | Must be set specified the procedure is an SPJ rather than a UDF. +Compare: http://trafodion.incubator.apache.org/docs/sql_reference/index.html#create_function_statement[CREATE FUNCTION]. +| *parameter style java* | `parameter style java` | Must be set specified the procedure is an SPJ rather than a UDF. +Compare: http://trafodion.incubator.apache.org/docs/sql_reference/index.html#create_function_statement[CREATE FUNCTION]. +| *_sql access_* | +`no sql` \| + +`contains sql` (default) \| + +`modifies sql data` \| + +`reads sql data` | +- *no sql*: the SPJ cannot perform SQL operations. + +- *contains sql*: the SPJ can read and modify SQL data. + +- *modifies sql data*: the SPJ can read and modify SQL data. + +- *reads sql data*: the SPJ can read and modify SQL data. + + + +It's a good practice to specify `modifies sql data` and `reads sql data` over `contains sql` as it helps +users of the procedure to better understand its impact. +| *dynamic result sets* | `dynamic result sets 0` (default) to `_n_` | +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. +| *_transaction_* | +`transaction required` (default) \| + +`no transaction required` | +Determines whether the SPJ must run in a transaction inherited from the calling application (`transaction required` 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. +| *_determinism_* | +`deterministic` \| + +`not deterministic` (default) | +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`). `deterministic` means that Trafodion +can cache the result to improve performance. +| *_isolation_* | +`isolate` (default) \| + +`no isolate` | +The SPJ executes either in the environment of the database server (`no isolate`) or in an isolated environment +(`isolate`, the default option). Trafodion allows both options but always executes the SPJ in the UDR server process (`isolate`). +|=== -11. Click [ Create ] to create the procedure. +Consider the following method in the `Sales.java` class: -== Use the Create Procedure Dialog Box +[source, java] +---- +include::{sourcedir}/totalPrice.java[totalPrice procedure source code] +---- -This table shows reference information for the Create Procedure dialog -box: +The CREATE PROCEDURE attributes for `totalPrice` are defined as follows: -[cols="20%,30%,50%",options="header",] +[cols="15%,40%,45%",options="header",] |=== -| Group Box | Control or Field | Action -| *Name* | Catalog | Name of the catalog where the procedure is created. This is a read-only field. -| | Schema | Name of the schema where the procedure is created. This is a read-only field. -| | Procedure Name | Enter a name for the procedure. The name must be unique and must not -exist for any procedure, table, or view in the same schema. The -procedure name is not case-sensitive. The database engine automatically -qualifies the procedure name with the name of the catalog and schema in -which you are creating the procedure. + - + -For example, if you enter *monthlyorders* as the procedure name, the database engine stores the -procedure as `_catalog-name_._schema-name_.MONTHLYORDERS`. -| *Code* | Library | Click [ Browse ] to navigate to a library or JAR file. -| | Class Name | Select a class. -| | Method Name | When you select a class, the method names and parameter types for that -class appear in the right pane. + - + -NOTE: Only methods that can be used in a procedure are visible. For -details, see <<use-a-method-in-a-procedure, Use a Method in a Procedure>>. -| *Parameters* | Name | Is the name for this SQL parameter. -| | Direction | -- IN passes data to a procedure + -- INOUT passes data to and accepts data from a procedure. The parameter must be an array. + -- OUT accepts data from a procedure. The parameter must be an array. -| | SQL Data Type | Displays the SQL data type that is the best match for the Java signature in the Java Data Type column. -| | Java Data Type | Displays the signature for the Java method that you selected. -| | [ Edit ] | Click to edit the selected parameter. -| *Attributes* | Number of dynamic result sets | Controls the maximum number of resulat sets the procedure can return. + - + -This control is enabled 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. + - + - This value is automatically set to zero if the selected Java method does not have a `java.sql.ResultSet[]` object. -| | Accesses Database | If selected, then the procedure performs SQL operations. -If cleared, then the procedure does not perform SQL operations. + - + -NOTE: SQL returns an error when trying to execute the procedure if this -attribute is cleared and the procedure performs SQL operations. -| | External Security | Select either Invoker or Definer for the external security of the stored procedure. + - + -- The Invoker setting 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_. + - + -* The Definer setting 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. + - + -For more information, see <<understand-external-security, Understand External Security>>. -| | Transaction Required | -Select either Yes to require the procedure to run in a transaction -inherited from the calling application (the default behavior) or No to -allow the procedure to run without an inherited transaction. For more -information, see <<transaction-behavior, Transaction Behavior>>. +| Attribute | Definition | Discussion +| *procedure-ref* | `trafodion.sales.totalprice` | Used lowercase version of totalPrice for _procedure-name_. +| *sql-parameter-1* | `IN _qty_ NUMERIC(18)` | The first argument to the SPJ is defined as: `BigDecimal qtyOrdered`, which maps to `NUMERIC`. +The size is chosen to allow for large numbers. +| *sql-parameter-2* | `IN _speed_ VARCHAR(10)` | The second argument to the SPJ is defined as: `String shippingSpeed`. +Possible values are: `economy`, `standard`, or `nextday`. `VARCHAR(10)` is a good choice. +| *sql-parameter-3* | `INOUT _price_ NUMERIC(18,2)` | The third argument to the SPJ is defines as: `BigDecimal[] price`. +The SPJ uses an input price to calculate to total price including taxes and shipping cost. +Therefore, `INOUT` is the appropriate choice. Given that the `_qty_` argument is NUMERIC(18), then this argument should be too. The precision choice fits the the +type of calculation being peformed. +| *external name* | `EXTERNAL NAME Sales.totalPrice` | The class name is `Sales`. The procedure name is `totalPrice`. +| *library* | `library trafodion.sales.sales` | The library name was defined using the CREATE LIBRARY statement. +See the example in <<create-a-library, Create a Library>>. +| *external security* | `invoker`^1^ | Use the privileges of the SPJ invoker. +| *language java* | `LANGUAGE JAVA` | Required for SPJs. +| *parameter style java* | `PARAMETER STYLE JAVA` | Required for SPJs. +| *_sql access_* | `NO SQL` | This SPJ performs no SQL operations, just calculations. +| *dynamic result sets* | `DYNAMIC RESULT SETS 0` | The method does not contain a `java.sql.resultset[]` object. +| *_transaction_* | `TRANSACTION REQUIRED`^1^ | The method should inherit the calling application's transaction, if any. +| *_determinism_* | `NOT DETERMINISTIC`^1^ | The results must be recalculated each time; that is, cannot be cached. +| *_isolation_* | `ISOLATE`^1^ | Execute the SPJ in an isolated environment; that is, the UDR server process. |=== -*Related Topics* +^1^ Definition represents default value. -* <<create-a-procedure, Create a Procedure>> +Using the information above, you create the new procedure as follows: -[[use-a-method-in-a-procedure]] -== Use a Method in a Procedure - -To be used in a procedure, a Java method must: +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.totalprice( IN qty NUMERIC (18), + IN speed VARCHAR (10), + INOUT price NUMERIC (18,2) + ) + EXTERNAL NAME 'Sales.totalPrice' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + NO SQL + DYNAMIC RESULT SETS 0 + TRANSACTION REQUIRED + ISOLATE + ; +---- -* Be qualified as public static void. -* Have a `java.sql.ResultSet[]` parameter at the end of the method signature if the method uses result sets. -+ -NOTE: There can be more than one java.sql.ResultSet[] parameter, but they must all be at the end of the method signature. +Removing the default values to simplify the statement, you get: -* Have these parameter types: -+ -[cols="40%,60%", options="header"] -|=== -| Parameter Type or Class | Type -| -| *character* | -`java.lang.String` + -`java.lang.String[]` -| *numeric* | -`java.lang.Integer` + -`java.lang.Integer[]` + -`java.lang.Long` + -`java.lang.Long[]` + -`java.lang.Float` + -`java.lang.Float[]` + -`java.lang.Double` + -`java.lang.Double[]` + -`java.math.BigDecimal` + -`java.math.BigDecimal[]` -| *date/timestamp* | -`java.sql.Date` + -`java.sql.Date[]` + -`java.sql.Time` + -`java.sql.Time[]` + -`java.sql.Timestamp` + -`java.sql.Timestamp[]` -| *primitive short* | -`short[]` + -`int` + -`int[]` + -`long` + -`long[]` + -`float` + -`float[]`+ -`double` + -`double[]` -| *result sets* | `java.sql.ResultSet[]` -|=== +[source, sql] +---- +CREATE PROCEDURE trafodion.sales.totalprice( IN qty NUMERIC (18), + IN speed VARCHAR (10), + INOUT price NUMERIC (18,2) + ) + EXTERNAL NAME 'Sales.totalPrice' + LIBRARY trafodion.sales.sales + LANGUAGE JAVA + PARAMETER STYLE JAVA + NO SQL + ; +---- [[understand-external-security]] == Understand External Security @@ -351,6 +239,7 @@ execute the SPJ without requiring privileges to the underlying database objects. To set the external security of an SPJ, see <<create-a-procedure, Create a Procedure>>. + [[drop-a-procedure]] == Drop a Procedure @@ -362,20 +251,40 @@ For example, if the schema owner granted you privileges to drop all objects in t you could drop procedures. For more information, see the GRANT SCHEMA statement in the http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual] -To drop a procedure: +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] +for full documentation of the +http://trafodion.apache.org/docs/sql_reference/index.html#drop_procedure_statement[DROP PROCEDURE] +statement including considerations and required privileges. + +*Example* + +Your task is to remove Sales class jar file from the `SALES` schema. + +Do the following using trafci: -1. Under the navigation tree pane, click the Database area. +``` +$ trafci +Welcome to Apache Trafodion Command Interface +Copyright (c) 2015 Apache Software Foundation -2. Under My Systems, expand the tree for the Trafodion platform -containing the procedure until you can see the schema folder and the -Procedures folder underneath it. +Host Name/IP Address: localhost:23400 +User Name: zz -3. Expand the Procedures folder. +Connected to Trafodion -4. Right-click the name of the procedure that you want to drop, and -select Drop Procedure. HPDM asks you to confirm the operation. +SQL> get procedures for library trafodion.sales.saleslib ; -5. Click [ Yes ] to continue or [ No ] to quit the operation. +TOTALPRICE + +--- SQL operation complete. + +SQL> DROP PROCEDURE trafodion.sales.totalprice ; + +--- SQL operation complete. + +SQL> +``` [[display-procedures-and-their-properties}} == Display Procedures and Their Properties @@ -386,7 +295,7 @@ command displays a list of the procedures in the SALES schema: [source, sql] ---- -SQL> SET SCHEMA demo.sales ; +SQL> SET SCHEMA trafodion.sales ; --- SQL operation complete. @@ -417,6 +326,8 @@ SQL> For more information about trafci, see the http://trafodion.apache.org/docs/command_interface/index.html[Trafodion Command Interface Guide]. +//// +20160323: Trafodion does not yet support the ALTER LIBRARY command. [[altering-an-spj-and-its-java-bytecode]] === Altering an SPJ and Its Java Bytecode @@ -452,3 +363,4 @@ the calling application. Therefore, schedule a time to update the SPJ or its Java bytecode when client applications are not actively calling the SPJ. +//// http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc index d3ca8e0..7a1a263 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/deploy_spjs.adoc @@ -27,8 +27,7 @@ After developing and packaging the SPJ code into JAR files, you must move the JAR files from the client workstation to the Trafodion database. You can upload a SPJ JAR file and create a library by using -the ADD LIBRARY command in the Trafodion Command Interface (trafci). -You use the CREATE LIBRARY, ALTER LIBRARY, and DROP LIBRARY commands. +the CREATE LIBRARY command in the Trafodion Command Interface (trafci). Libraries provide greater security for JAR files because libraries are database objects whose access is controlled using standard SQL security. @@ -37,39 +36,8 @@ Deploying a JAR file to a Trafodion instance requires creating a library, and users must have the required privileges for creating libraries in order to deploy JAR files. -See these instructions: - -* <<create-a-library, Create a Library>> -* <<alter-a-library, Alter a Library>> -* <<drop-a-library, Drop a Library>> -* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> -* <<display-libraries-and-their-properties, Display Libraries and Their Properties>> - -Before you start, see -<<required-privileges-for-creating-altering-or-dropping-a-library, "Required Privileges for Creating, Altering, or Dropping a Library">>. - - -== Required Privileges for Creating, Altering, or Dropping a Library - -Deploying a JAR file to the Trafodion database requires creating a -library, and you must have the required privileges for creating -libraries in order to deploy a JAR file. To create a library in a -schema, you must have both the CREATE_LIBRARY component privilege and -the CREATE_LIBRARY schema-level privilege for the schema. For more -information, see the requirements in <<create-a-library, Create a Library>>. - -To alter a library in a schema, you must have both the UPDATE privilege -for the library and the ALTER_LIBRARY privilege for the schema. For more -information, see the requirements in -<<alter-a-library, Alter a Library>>. - -To drop a library from a schema, you must have the DROP_LIBRARY -privilege for the schema. For more information, see the requirements in -<<drop-a-library, Drop a Library>>. - -To download a JAR file from a library, you must have the CREATE_LIBRARY -component privilege. For more information, see the requirements in -<<download-a-jar-file-from-a-library, Download a JAR File From a Library>>. +The examples in this chapter are based on the sample database +documented in <<b-sample-database, Appendix B: Sample Database>>. [[create-a-library]] == Create a Library @@ -78,281 +46,143 @@ Refer to the http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] for full documentation of the http://trafodion.apache.org/docs/sql_reference/index.html#create_library_statement[CREATE LIBRARY] -statement. +statement including considerations and required privileges. + +NOTE: You must copy the jar 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 file is not found. + +*Example* -When you create a library, HPDM simultaneously uploads an SPJ JAR file -from the client workstation to the Trafodion database and creates a -library for the JAR file in a schema of the database. - -Requirements: - -* You must have the CREATE_LIBRARY privilege for the SQL_OPERATIONS -component. For more information, see the component privileges in the _HP -Database Manager (HPDM) User Guide_ or the online help in HPDM. -* You must either be the owner of the schema where you will create the -library or have the CREATE_LIBRARY privilege for the schema to create -the library. The schema owner can grant such a schema-level privilege to -other users or roles. For more information, see the - -Grant/Revoke Privileges Tool in HPDM or the GRANT SCHEMA statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -* The JAR file that you select for the library must contain valid Java -bytecode and must not be used by another library in the catalog. -* The JAR file that you select for the library must not exceed a size -threshold if one has been set for HPDM's internal WMS service, the data -source, or the system defaults. - -For example, if the CONTROL QUERY DEFAULT (CQD) `POS_ABSOLUTE_MAX_TABLE_SIZE` -has been set for the `HPS_MANAGEABILITY` service, it limits the size -of JAR files that can be uploaded by HPDM. If the JAR file's size -exceeds the threshold, a warning message is displayed, and the file is -not uploaded. By default, this CQD is not set for HPDM's internal WMS -service, the data source, or the system defaults, and HPDM allows you to -upload JAR files of any size. - -To upload a JAR file and create a library for the JAR file in a schema: - -1. In HPDM, select the Database area. -2. In the navigation tree pane, expand the tree to display the folders -of the schema where you want to create the library. -3. Right-click the Libraries folder, and select Create Library. The -Create Library dialog box appears. For more information, see -<<use-the-create-library-dialog-box, Use the Create Library Dialog Box>>. -4. In the Create Library dialog box, enter a name for the library. +This example uses the sample <<procedures-in-the-sales-schema, Sales Class>> documented in +<<a-sample-spjs, Appendix A: Sample SPJs>>. + +Your task is to add the Sales class jar file as a library object in the `SALES` schema. +Do the following: + +1. Compile the `Sales.java` source file. + -The name must be unique among the libraries in the schema and must not -exceed 128 characters. The library name is not case-sensitive. +*Example* + -For example, if you enter `Payroll` as the library name, the database engine -stores the library as `_catalog-name_._schema-name_.PAYROLL`. The database engine -automatically qualifies the library name with the name of the catalog -and schema in which you are creating the library. - -5. Click the [ Browse ] button to locate and select a JAR file on the -client workstation. The *Select a code file* dialog box appears. +``` +$ # Set up the environmental variables. +$ cd $HOME/trafodion-incubator +$ source env.sh +$ # Prepare the class jar file +$ cd $HOME/trafodion-spjs +$ ls +Inventory.java Payroll.java Sales.java +$ # Compile source +$ javac Sales.java +$ ls Sales.* +Sales.class Sales.java +$ # Package jar file +$ jar cvf Sales.jar Sales.class +$ ls Sales.* +Sales.class Sales.jar Sales.java +$ +``` + +2. Multi-node cluster only: Create the target directory, if necessary. ++ +*Example* ++ +``` +$ pdsh $MY_NODES -x $HOSTNAME mkdir $HOME/trafodion-spjs +``` -6. In the *Select a code file* dialog box, locate and select the JAR -file on the client workstation, and click [ Open ] to return to the -Create Library dialog box. The Create Library dialog box displays the -full path of the JAR file in the Code File field. +3. Multi-node cluster only: Copy the jar file to all nodes in the cluster. ++ +*Example* ++ +``` +$ pdcp $MY_NODES Sales.jar $PWD/. +``` -7. Click [ Create ] to create the library, or click [ Close ] to quit -the dialog box without creating the library. +4. Create the library object using trafci. + -If you click [ Create ], HPDM validates the JAR file, uploads the JAR -file, and creates a library for the JAR file in the schema. - -8. When notified that the library was created successfully, click [ OK ]. -9. To upload another JAR file and create a library, repeat steps 4 -through 8. -10. When you are done uploading JAR files and creating libraries, click -the [ Close ] button to quit the Create -Library dialog box and return to the HPDM main window. - -*Related Topics* - -* <<use-the-create-library-dialog-box, Use the Create Library Dialog Box>> -* <<alter-a-library, Alter a Library>> -* <<drop-a-library, Drop a Library>> -* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> -* <<display-libraries-and-their-properties, Display Libraries and Their Properties>> - -[[use-the-create-library-dialog-box]] -== Use the Create Library Dialog Box - -The Create Library dialog box allows you to upload an SPJ JAR file from -the client workstation to the Trafodion database and create a library for -the JAR file in a schema of the database. - -Requirements: - -* You must have the CREATE_LIBRARY privilege for the SQL_OPERATIONS -component. For more information, see the component privileges in the _HP -Database Manager (HPDM) User Guide_ or the online help in HPDM. - -* You must either be the owner of the schema where you will create the -library or have the CREATE_LIBRARY privilege for the schema to create -the library. The schema owner can grant such a schema-level privilege to -other users or roles. For more information, see the Grant/Revoke -Privileges Tool in HPDM or the GRANT SCHEMA statement in the -http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual]. - -* The JAR file that you select for the library must contain valid Java -bytecode and must not be used by another library in the catalog. - -* The JAR file that you select for the library must not exceed a size -threshold if one has been set for HPDM's internal WMS service, the data -source, or the system defaults. - -For example, if the CONTROL QUERY DEFAULT (CQD) `POS_ABSOLUTE_MAX_TABLE_SIZE` -has been set for the HPS_MANAGEABILITY service, it will limit the size -of JAR files that can be uploaded by HPDM. - -If the JAR file's size exceeds the threshold, then a warning message is displayed, -and the file is not uploaded. By default, this CQD is not set for HPDM's internal WMS -service, the data source, or the system defaults, and HPDM allows you to -upload JAR files of any size. - -[cols="20%,30%,50%",options="header",] -|=== -| Group Box | Control or Field | Description or Action -| *Library* | Catalog | Name of the catalog where the library is created. This is a read-only field. -| | Schema | Name of the schema where the library is created. This is a read-only field. -| | Library Name | Enter a name for the library. The name must be unique among the libraries -in the schema and must not exceed 128 characters. The library name is not case-sensitive. + - + -For example, if you enter Payroll as the library name, the database engine stores the library as -`_catalog-name_._schema-name_.PAYROLL`. The database engine automatically qualifies the library name with the -name of the catalog and schema in which you are creating the library. -| *Code File* | Code File | Click [ Browse ] to launch the *Select a code file* dialog box and -navigate to a JAR file on the client workstation. -| | [ Create ] | Creates the library. -| | [ Close ] | Closes the dialog box without creating a library. -| | [ Help ] | Displays context-sensitive help for this dialog box. -|=== - -*Related Topics* - -* <<create-a-library, Create a Library>> -* <<alter-a-library, Alter a Library>> -* <<drop-a-library, Drop a Library>> -* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> +``` +$ pwd +/home/trafodion/trafodion-jars +$ trafci + +Welcome to Apache Trafodion Command Interface +Copyright (c) 2015 Apache Software Foundation + +Host Name/IP Address: localhost:23400 +User Name: zz + +Connected to Trafodion + +SQL> CREATE LIBRARY trafodion.sales.sales FILE '/home/trafodion/trafodion-spjs/Sales.jar' ; + +--- SQL operation complete. + +SQL> get libraries in schema trafodion.sales ; + +SALES + +--- SQL operation complete. + +SQL> +``` + +//// +20160323 GTA: This feature isn't implemented yet. Uncomment this section once implemented. [[alter-a-library]] == Alter a Library When you alter a library, you can change the underlying JAR file of the library. -Requirements: - -* You must have been granted the UPDATE privilege for the library. For -more information, see the Grant/Revoke Privileges Tool in HPDM or the -GRANT LIBRARY statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -* You must either be the owner of the schema where you're altering the -library or have the ALTER_LIBRARY privilege for the schema to alter the -library. The schema owner can grant such a schema-level privilege to -other users or roles. For more information, see the Grant/Revoke -Privileges Tool in HPDM or the GRANT SCHEMA statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -* The JAR file that you select for the library must contain valid Java -bytecode and must not be used by another library in the catalog. - -* The JAR file that you select for the library must not exceed a size -threshold if one has been set for HPDM's internal WMS service, the data -source, or the system defaults. - -For example, if the CONTROL QUERY DEFAULT (CQD) `POS_ABSOLUTE_MAX_TABLE_SIZE` -has been set for the HPS_MANAGEABILITY service, then it limits the size -of JAR files that can be uploaded by HPDM. If the JAR file's size -exceeds the threshold, a warning message is displayed, and the file is -not uploaded. By default, this CQD is not set for HPDM's internal WMS -service, the data source, or the system defaults, and HPDM allows you to -upload JAR files of any size. - -To alter a library and change its underlying JAR file: - -1. In HPDM, select the Database area. -2. In the navigation tree pane, expand the tree to display the folders -of the schema that contains the library, and expand the Libraries folder -to display the libraries within it. -3. Right-click the name of the library, and select Alter Library. If -you selected a system library, a dialog box appears warning you that you -cannot alter a system library. If you selected a user library, the Alter -Library dialog box appears. For more information, see -<<use-the-alter-library-dialog-box, Use the Alter Library Dialog Box>>. - -4. In the Alter Library dialog box, click the [ Browse ] button to -locate and select a new JAR file on the client workstation. The *Select -a code file* dialog box appears. - -5. In the *Select a code file* dialog box, locate and select the JAR -file on the client workstation, and click [ Open ] to return to the -Alter Library dialog box. The Alter Library dialog box displays the full -path of the new JAR file in the Code File field. - -6. Click [ Alter ] to alter the library, or click [ Close ] to quit the -dialog box without altering the library. +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] +for full documentation of the +http://trafodion.apache.org/docs/sql_reference/index.html#alter_library_statement[ALTER LIBRARY] +statement including considerations and required privileges. + +*Example* + +Your task is to modify the Sales class jar file as a library object in the `SALES` schema +with a new jar file (`Sales2.jar`). + +Do the following: + +1. Multi-node cluster only: Copy the jar file to all nodes in the cluster. + -If you click [ Alter ], a dialog box appears warning you that altering a -library might affect stored procedures that use the library and asking -if you wish to continue the alter operation. Click [ Yes ] to proceed, -or click [ No ] to cancel the operation. If you click [ Yes ], HPDM -validates the JAR file, uploads the JAR file, and alters the library in -the schema. - -7. When notified that the library was altered successfully, click -[ OK ] to close the Alter Library dialog box and return to the HPDM main -window. - -*Related Topics* - -* <<use-the-alter-library-dialog-box, Use the Alter Library Dialog Box>> -* <<create-a-library, Create a Library>> -* <<drop-a-library, Drop a Library>> -* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> -* <<display-libraries-and-their-properties, Display Libraries and Their Properties>> - -[[use-the-alter-library-dialog-box]] -== Use the Alter Library Dialog Box - -The Alter Library dialog box allows you to alter a library and change -its underlying JAR file. - -Requirements: - -* You must have been granted the UPDATE privilege for the library. For -more information, see the Grant/Revoke Privileges Tool in HPDM or the -GRANT LIBRARY statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -* You must either be the owner of the schema where you will alter the -library or have the ALTER_LIBRARY privilege for the schema to alter the -library. The schema owner can grant such a schema-level privilege to -other users or roles. For more information, see the Grant/Revoke -Privileges Tool in HPDM or the GRANT SCHEMA statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -* The JAR file that you select for the library must contain valid Java -bytecode and must not be used by another library in the catalog. - -* The JAR file that you select for the library must not exceed a size -threshold if one has been set for HPDM's internal WMS service, the data -source, or the system defaults. +*Example* + -For example, if the CONTROL QUERY DEFAULT (CQD) `POS_ABSOLUTE_MAX_TABLE_SIZE` -has been set for the HPS_MANAGEABILITY service, then it limits the size -of JAR files that can be uploaded by HPDM. If the JAR file's size -exceeds the threshold, a warning message is displayed, and the file is -not uploaded. +``` +$ pdcp $MY_NODES Sales2.jar $PWD/. +``` + +2. Create the library object using trafci. + -By default, this CQD is not set for HPDM's internal WMS -service, the data source, or the system defaults, and HPDM allows you to -upload JAR files of any size. - - -[cols="20%,30%,50%",options="header",] -|=== -| Group Box | Control or Field | Description or Action -| *Library* | Catalog | Name of the catalog where the library is created. This is a read-only field. -| | Schema | Name of the schema where the library is created. This is a read-only field. -| | Library Name | Name of the library. This is a read-only field. -| *Code File* | Code File | Click [ Browse ] to launch the "Select a code file" dialog box and -navigate to a new JAR file on the client workstation. -| | [ Alter ] | Alters the library. -| | [ Close ] | Closes the dialog box without altering the library. -| | [ Help ] | Displays context-sensitive help for this dialog box. -|=== - -Related Topics - -* <<alter-a-library, Alter a Library>> -* <<create-a-library, Create a Library>> -* <<drop-a-library, Drop a Library>> -* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> +``` +$ pwd +/home/trafodion/trafodion-jars +$ trafci + +Welcome to Apache Trafodion Command Interface +Copyright (c) 2015 Apache Software Foundation + +Host Name/IP Address: localhost:23400 +User Name: zz + +Connected to Trafodion + +SQL> ALTER LIBRARY trafodion.sales.saleslib FILE '/home/trafodion/trafodion-spjs/Sales2.jar' ; + +--- SQL operation complete. + +SQL> get libraries in schema trafodion.sales ; + +SALES + +--- SQL operation complete. + +SQL> +``` +//// [[drop-a-library]] == Drop a Library @@ -361,101 +191,69 @@ Dropping a library removes the library from the schema in the database and removes the library's underlying JAR file from the Trafodion database. -Requirements: - -* You must either be the owner of the schema where you will drop the -library or have the DROP_LIBRARY privilege for the schema to drop the -library. The schema owner can grant such a schema-level privilege to -other users or roles. For more information, see the Grant/Revoke -Privileges Tool in HPDM or the GRANT SCHEMA statement in the -http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. - -To drop a library from the database: +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] +for full documentation of the +http://trafodion.apache.org/docs/sql_reference/index.html#drop_library_statement[DROP LIBRARY] +statement including considerations and required privileges. -1. In HPDM, select the Database area. -2. In the navigation tree pane, expand the tree to display the folders -of the schema that contains the library, and expand the Libraries folder -to display the libraries within it. -3. Right-click the name of the library, and select Drop Library. The -Drop Library dialog box appears. -4. In the Drop Library dialog box appears, select the Dropping all -procedures and routines using this library check box to drop all stored -procedures that are using the library. If you do not select this option -when stored procedures are using the library, the drop operation will -fail with an error message. -5. Click [ Yes ] to drop the library, or click [ No ] to quit the -dialog box without dropping the library. +*Example* -*Related Topics* +Your task is to remove Sales class jar file from the `SALES` schema. -* <<create-a-library, Create a Library>> -* <<alter-a-library, Alter a Library>> -* <<download-a-jar-file-from-a-library, Download a JAR File From a Library>> -* <<display-libraries-and-their-properties, Display Libraries and Their Properties>> +Do the following using trafci: -[[download-a-jar-file-from-a-library]] -== Download a JAR File From a Library +``` +$ trafci +Welcome to Apache Trafodion Command Interface +Copyright (c) 2015 Apache Software Foundation -Requirements: +Host Name/IP Address: localhost:23400 +User Name: zz -* You must have the CREATE_LIBRARY privilege for the SQL_OPERATIONS -component. For more information, see the component privileges in the _HP -Database Manager (HPDM) User Guide_ or the online help in HPDM. +Connected to Trafodion -To download a JAR file from a library: +SQL> get libraries in schema trafodion.sales ; -1. In HPDM, select the Database area. +SALES -2. In the navigation tree pane, expand the tree to display the folders -of the schema that contains the library, and expand the Libraries folder -to display the libraries within it. +--- SQL operation complete. -3. Right-click the name of the library, and select Browse Library. The -Browse Library dialog box appears with the name of the library selected -in the tree pane and displayed in the right pane. For more information, -see <<use-the-library-browser, Use the Library Browser>>. +SQL> DROP LIBRARY trafodion.sales.saleslib ; -4. Click the [ Download ] button. The *Browse For Folder* dialog box -appears. +--- SQL operation complete. -5. In the *Browse For Folder* dialog box, select a target folder on the -client workstation for downloading the JAR file. +SQL> +``` -6. Click [ OK ] to start downloading the library's underlying JAR file -to the client workstation, or click [ Cancel ] to quit the Browse For -Folder dialog box and return to the Browse Library dialog box. +[[display-libraries]] +== Display Libraries -7. When notified that the download completed successfully, click [ OK ] -to return to the Browse Library dialog box. +Refer to the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual] +for full documentation of the +http://trafodion.apache.org/docs/sql_reference/index.html#get_statement[GET] +statement. -*Related Topics* +Use the `GET libraries [ in schema [ catalog-name.] schema-name ] statement in trafci to +display the libraries in a schema. -* <<use-the-library-browser, Use the Library Browser>> -* <<create-a-library, Create a Library>> -* <<alter-a-library, Alter a Library>> -* <<drop-a-library, Drop a Library>> -* <<display-libraries-and-their-properties, Display Libraries and Their Properties>> +*Example* -[[use-the-library-browser]] -== Use the Library Browser +``` +$ trafci +Welcome to Apache Trafodion Command Interface +Copyright (c) 2015 Apache Software Foundation -Depending on the context in which the Library Browser is launched, you -can use it to view, create, or drop libraries in the database; to -download a JAR file from a library in the database to the client -workstation; or to select a Java method in a library when creating a -stored procedure. +Host Name/IP Address: localhost:23400 +User Name: zz -[[display-libraries-and-their-properties]] -== Display Libraries and Their Properties +Connected to Trafodion -To display the libraries in a schema: +SQL> get libraries in schema trafodion.sales ; -1. Start the HP Database Manager and log on using any user name. -2. Click the Database area. -3. In the navigation tree pane, select a Schema, and expand the schema -so that you can see the object folders underneath it. -4. Expand the Libraries tab in the right pane and select a library -name, or open the Libraries -folder and select a library name in the tree. HPDM displays the -libraries for this schema. +SALES +--- SQL operation complete. +SQL> +``` http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc index 6e9cba9..0b13648 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc @@ -27,13 +27,7 @@ Before creating, or registering, an SPJ in the database, you must write and compile the Java method to be used as the body of the SPJ. The manual refers to those Java methods as _SPJ methods_. -This chapter requires a familiarity with writing and compiling Java -programs and covers these topics: - -* <<guidelines-for-writing-spj-methods, Guidelines for Writing SPJ Methods>> -* <<accessing-a-trafodion-database, Accessing a Trafodion Database>> -* <<handling-java-exceptions,Handling Java Exceptions>> -* <<compiling-and-packaging-java-classes,Compiling and Packaging Java Classes>> +This chapter requires a familiarity with writing and compiling Java programs. [[guidelines-for-writing-spj-methods]] == Guidelines for Writing SPJ Methods @@ -41,14 +35,6 @@ programs and covers these topics: Follow the guidelines for these topics when you write SPJ methods to be used as SPJs in the database: -* <<signature of the java method, Signature of the Java Method>> -* <<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>> -* <<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>> -* <<using-the-main-method, Using the main() Method>> -* <<null-input-and-output, Null Input and Output>> -* <<static-java-variables, Static Java Variables>> -* <<nested-java-method-invocations, Nested Java Method Invocations>> - [[signature-of-the-java-method]] === Signature of the Java Method @@ -62,10 +48,10 @@ public static void myMethodName ( java-parameter-list ) [[public-access-and-static-modifiers]] ==== Public Access and Static Modifiers -The Java method must be defined as public and static. If a method is -private or protected, the database engine is unable to find the Java +The Java method must be defined as `public` and `static`. If a method is +`private` or `protected`, then Trafodion is unable to find the Java method when you try to register the SPJ and returns an error. The Java -method must be defined as static so that the method can be invoked +method must be defined as `static` so that the method can be invoked without having to instantiate its class. [[void-return-type]] @@ -86,44 +72,36 @@ type mappings, see the table below. [[table-1]] .Mapping of Java Data Types to SQL Data Types -[cols="40%,60%",options="header",] +[cols="60%,40%",options="header"] |=== -| Java Data Type | Maps to SQL Data Type. . . -| `java.lang.String` | -CHAR[ACTER] + +| 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.sql.Date` | DATE -| `java.sql.Time` | TIME -| `java.sql.Timestamp` | TIMESTAMP -| `java.math.BigDecimal` | -NUMERIC (including NUMERIC with a precision greater than eighteen)^2^ + -DEC[IMAL]^2^ + -PIC[TURE] S9^3^ -| `short` | SMALLINT^2^ -| `int or java.lang.Integer`^4^ | INT[EGER]^2^ -| `long or java.lang.Long`^4^ | LARGEINT2 -| `double or java.lang.Double`^4^ | FLOAT -| `float or java.lang.Float`^4^ | REAL -| `double or java.lang.Double`^4^ | DOUBLE PRECISION -| `java.sql.ResultSet[]` | None +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. Choose a Java wrapper class if you plan to pass null values as -arguments to or from the method. See -<<null-input-and-output, Null Input and Output>> +1. Trafodion stores PIC X as a CHAR data type. +2. Numeric data types of SQL parameters must be signed, which is the default in Trafodion. +3. Trafodion 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. Output parameters in the Java signature must be arrays (for example, `int[]` or `String[]`) that accept only one value in the first element of @@ -259,11 +237,11 @@ java.lang.String s = "SELECT amounts.*, orders.order_date, emps.last_name " + "FROM ( SELECT o.ordernum, COUNT( d.partnum ) AS num_parts, " + " SUM( d.unit_price * d.qty_ordered ) AS amount " - + " FROM demo.sales.orders o, demo.sales.odetail d " + + " FROM trafodion.sales.orders o, trafodion.sales.odetail d " + " WHERE o.ordernum = d.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + " GROUP BY o.ordernum ) amounts, " - + " demo.sales.orders orders, demo.persnl.employee emps " + + " trafodion.sales.orders orders, trafodion.persnl.employee emps " + "WHERE amounts.ordernum = orders.ordernum " + " AND orders.salesrep = emps.empnum " + "ORDER BY orders.ordernum " @@ -408,12 +386,6 @@ SPJ methods that access a Trafodion database must be from a Java class that uses JDBC method calls. Follow these guidelines when writing an SPJ method that accesses a Trafodion database: -* <<use-of-java.sql.connection-objects, Use of java.sql.Connection Objects>> -* <<using-jdbc-method-calls, Using JDBC Method Calls>> -* <<referring-to-database-objects-in-an-spj-method, Referring to Database Objects in an SPJ Method>> -* <<using-the-session_user-or-current_user-function-in-an-spj-method, Using the SESSION_USER or CURRENT_USER Function in an SPJ Method>> -* <<exception-handling, Exception Handling>> - [[use-of-java.sql.connection-objects]] === Use of java.sql.Connection Objects @@ -541,14 +513,14 @@ public class Payroll DriverManager.getConnection( "jdbc:default:connection" ) ; PreparedStatement setSalary = - conn.prepareStatement( "UPDATE demo.persnl.employee " + conn.prepareStatement( "UPDATE trafodion.persnl.employee " + "SET salary = salary * (1 + (? / 100)) " + "WHERE empnum = ?" ) ; PreparedStatement getSalary = conn.prepareStatement( "SELECT salary " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum = ?" ) ; @@ -584,7 +556,7 @@ connections created in the SPJ method are associated with that catalog and schema, meaning that unqualified database objects with one-part or two-part names in the SPJ method are qualified with the same catalog and/or schema name as the SPJ. For example, this SPJ method, which is -registered as an SPJ in the DEMO.SALES schema, refers to the unqualified +registered as an SPJ in the TRAFODION.SALES schema, refers to the unqualified database object, ORDERS: [source, java] @@ -616,7 +588,7 @@ public static void numDailyOrders( Date date ---- In the SPJ environment, the ORDERS table is qualified by default with -the same catalog and schema as the SPJ, DEMO.SALES. +the same catalog and schema as the SPJ, TRAFODION.SALES. The default behavior takes effect only when `getConnection()` does not contain catalog and schema properties. Catalog and schema property @@ -670,7 +642,7 @@ function, and CURRENT_USER (or USER) is an SQL function that returns the name of the database user who is authorized to invoke the function. If you plan to use the SESSION_USER or CURRENT_USER (or USER) function in an SPJ method, you should be aware of differences in their behavior -depending on how extenal security is defined for the stored procedure. +depending on how external security is defined for the stored procedure. Suppose that you write this Java method, which uses the CURRENT_USER function to return the name of the database user who is authorized to @@ -698,7 +670,7 @@ and called the stored procedure. For example, suppose that DB USERADMINUSER creates a stored procedure named GETINVOKER using the `getUser()` method and sets the external -security to invoker. If a database user named PAULLOW1, who has the +security to invoker. If a database user named GTAPPER, who has the EXECUTE privilege on the stored procedure, calls GETINVOKER, the procedure returns his name: @@ -706,15 +678,15 @@ returns his name: Welcome to Apache Trafodion Command Interface Copyright (c) 2015 Apache Software Foundation -User Name:PAULLOW1 Password: +User Name:GTAPPER Password: Connected to Data Source: TDM_Default_DataSource -SQL> CALL demo.persnl.getinvoker() ; +SQL> CALL trafodion.persnl.getinvoker() ; (EXPR) -------------------------------------------------------------------------------- -PAULLOW1 +GTAPPER --- 1 row(s) selected. @@ -732,17 +704,17 @@ user who created the stored procedure. For example, suppose that DB USERADMINUSER creates a stored procedure named GETDEFINER using the `getUser(`) method and sets the external -security to definer. If the database user named PAULLOW1, who has the +security to definer. If the database user named GTAPPER, who has the EXECUTE privilege on the stored procedure, calls GETDEFINER, the procedure returns the name of the stored procedures's creator, DB USERADMINUSER, -whose privileges PAULLOW1 is using to call the procedure: +whose privileges GTAPPER is using to call the procedure: ``` SQL> SHOW USER -USER PAULLOW1 (NONE) +USER GTAPPER (NONE) -SQL> CALL demo.persnl.getdefiner() ; +SQL> CALL trafodion.persnl.getdefiner() ; (EXPR) -------------------------------------------------------------------------------- @@ -776,7 +748,7 @@ external security setting of the stored procedure. For example, suppose that DB USERADMINUSER creates a stored procedure named GETSESSIONUSER using the `getSessionUser()` method and sets the external -security to definer. If the database user named PAULLOW1, who has the EXECUTE +security to definer. If the database user named GTAPPER, who has the EXECUTE privilege on the stored procedure, calls GETSESSIONUSER, the procedure returns his name because he is the authenticated user who started the session and invoked the function: @@ -784,13 +756,13 @@ session and invoked the function: ``` SQL> SHOW USER -USER PAULLOW1 (NONE) +USER GTAPPER (NONE) -SQL> CALL demo.persnl.getsessionuser() ; +SQL> CALL trafodion.persnl.getsessionuser() ; (EXPR) -------------------------------------------------------------------------------- -PAULLOW1 +GTAPPER --- 1 row(s) selected. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc b/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc index db287f7..0bd5ae5 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc @@ -182,9 +182,9 @@ work where needed. The Trafodion database engine manages a single thread of execution within an SPJ environment, even if the application that issues a CALL statement -is a multithreaded Java application. +is a multi-threaded Java application. -The CALL statements in a multithreaded application can execute in a nonblocking manner, +The CALL statements in a multi-threaded application can execute in a nonblocking manner, but the SPJ methods underlying those CALL statements execute serially within a given SPJ environment. @@ -210,14 +210,14 @@ example: [source, sql] ---- -CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; +CALL trafodion.persnl.adjustsalary( 202, 5.5, ? ) ; ---- Or, for example: [source, sql] ---- -SET SCHEMA demo.persnl ; +SET SCHEMA trafodion.persnl ; CALL adjustsalary( 202, 5.5, ? ) ; ---- @@ -241,7 +241,7 @@ CALL statement: [source, sql] ---- -CALL demo.persnl.adjustsalary( 202, 5, ? ) ; +CALL trafodion.persnl.adjustsalary( 202, 5, ? ) ; ---- If the SPJ does not accept arguments, you must specify empty @@ -249,7 +249,7 @@ parentheses, as shown below: [source, sql] ---- -CALL demo.sales.lowerprice() ; +CALL trafodion.sales.lowerprice() ; ---- If the SPJ has one IN parameter, one OUT parameter, and two result sets, @@ -257,7 +257,7 @@ you must list the IN and OUT parameters but not the result sets in the argument list: ``` -CALL demo.sales.ordersummary('01-01-2011', ?); +CALL trafodion.sales.ordersummary('01-01-2011', ?); ``` [[data-conversion-of-parameter-arguments]] @@ -388,7 +388,7 @@ command before calling the SPJ: ---- SQL> SET PARAM ?p 10 ; -SQL> CALL demo.sales.totalprice( 23, 'standard', ?p ) ; +SQL> CALL trafodion.sales.totalprice( 23, 'standard', ?p ) ; ---- The CALL statement returns the total price of the item: @@ -421,7 +421,7 @@ price: [source, sql] ---- -SQL> PREPARE stmt1 FROM CALL demo.sales.totalprice( 50, 'nextday', ? ) ; +SQL> PREPARE stmt1 FROM CALL trafodion.sales.totalprice( 50, 'nextday', ? ) ; --- SQL command prepared. @@ -445,7 +445,7 @@ INOUT parameter returns the total price: [source, sql] ---- -SQL> PREPARE stmt2 FROM CALL demo.sales.totalprice( ?, ?, ? ) ; +SQL> PREPARE stmt2 FROM CALL trafodion.sales.totalprice( ?, ?, ? ) ; --- SQL command prepared. @@ -475,7 +475,7 @@ for the number of orders and two result sets in the trafci session: [source, sql] ---- -SQL> CALL demo.sales.ordersummary( '01-01-2011', ? ) ; +SQL> CALL trafodion.sales.ordersummary( '01-01-2011', ? ) ; NUM_ORDERS -------------------- @@ -541,7 +541,7 @@ SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 0, 0, 5.5, SQLBindParameter( hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_NUMERIC, SQL_NUMERIC, 8, 2, &salary, 0, &cbParam ) ; /* Execute the CALL statement. */ -SQLExecDirect( hstmt, "{ CALL demo.persnl.adjustsalary( ?, ?, ? ) }", SQL_NTS ) ; +SQLExecDirect( hstmt, "{ CALL trafodion.persnl.adjustsalary( ?, ?, ? ) }", SQL_NTS ) ; ---- //// @@ -574,7 +574,7 @@ SQLHSTMT s ; RETCODE rc = SQLAllocHandle( SQL_HANDLE_STMT, myConnection, &s ) ; /* Prepare a CALL */ -char *stmtText = "{ CALL demo.sales.ordersummary( '01-01-2011', ? ) } "; +char *stmtText = "{ CALL trafodion.sales.ordersummary( '01-01-2011', ? ) } "; rc = SQLPrepare( s, (SQLCHAR *) stmtText, strlen( stmtText ) ) ; /* Bind the output parameter */ @@ -635,7 +635,7 @@ In this example, a CALL statement is executed from a JDBC client application: [source, java] ---- CallableStatement stmt = - con.prepareCall( "{ CALL demo.persnl.adjustsalary( ?, ?, ? ) }" ) ; + con.prepareCall( "{ CALL trafodion.persnl.adjustsalary( ?, ?, ? ) }" ) ; stmt.setBigDecimal( 1, 202 ) ; // x = 202 stmt.setDouble( 2, 5.5 ) ; // y = 5.5 @@ -668,7 +668,7 @@ and moves processing to the next available result set. ---- // Prepare a CALL statement java.sql.CallableStatement s = - myConnection.prepareCall( "{ CALL demo.sales.ordersummary( '01-01-2011', ? ) }" ) ; + myConnection.prepareCall( "{ CALL trafodion.sales.ordersummary( '01-01-2011', ? ) }" ) ; // Register an output parameter s.registerOutParameter( 1, java.sql.Types.BIGINT ) ; @@ -702,7 +702,7 @@ available. ---- // Prepare a CALL statement java.sql.CallableStatement s = - myConnection.prepareCall ( "{ CALL demo.sales.ordersummary( '01-01-2011', ? ) }" ) ; + myConnection.prepareCall ( "{ CALL trafodion.sales.ordersummary( '01-01-2011', ? ) }" ) ; // Register an output parameter s.registerOutParameter( 1, java.sql.Types.BIGINT ) ; @@ -757,11 +757,11 @@ when the QTY_ON_HAND column of the PARTLOC table is updated and exceeds [source, sql] ---- -CREATE TRIGGER demo.sales.setsalesprice AFTER UPDATE OF qty_on_hand -ON demo.invent.partloc +CREATE TRIGGER trafodion.sales.setsalesprice AFTER UPDATE OF qty_on_hand +ON trafodion.invent.partloc FOR EACH STATEMENT REFERENCING NEW as newqty WHEN ( SUM( newqty.qty_on_hand ) > 500 ) - CALL demo.sales.lowerprice() ; + CALL trafodion.sales.lowerprice() ; ---- For information about the CREATE TRIGGER syntax, see the http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc b/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc index 647ed54..a8e5c2b 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc @@ -25,7 +25,7 @@ Before you can start using SPJs on the Trafodion platform, verify that you have the required software installed on the client workstation. The -Trafodion platform is delivered to you ready to use and preconfigured +Trafodion platform is delivered to you ready to use and pre-configured with the software required to support SPJs. [[required-client-software]] @@ -42,40 +42,9 @@ http://www.oracle.com/technetwork/java/index.html The version of the JDK that you download and use on the client workstation should be the same as or lower than the Java version running on the Trafodion database. To check the Java version that is running in -the Trafodion database, use one of these approaches: +the Trafodion database, use this approach: -* Run `sqvers` in the `RUN_SCRIPT` stored procedure from `trafci`: -+ -``` -SQL> CALL demo.hp_sp.run_script( 'sqvers', '', '', ? ) ; - -OUTRESULT ------------------------------------------------------------- -Requesting user: DB ROOT -Current time: 2013-05-22 04:44:16.00 -Location of script: /opt/hp/sqtest4/M9V29713/export/bin64 -Results are stored in table: "sqvers@2013-05-22@04:44:16.00" -Command executed: sqvers - -RESULTS ----------------------------------- - -MY_SQROOT=/opt/hp/sqtest4/M9V29713 -who@host=sqtest4@n013 -JAVA_HOME=/usr/java/jdk1.7.0_09 - ---- 3 row(s) selected. - ---- SQL operation complete. - -SQL> -``` -+ -In this example, the returned `JAVA_HOME` indicates that the Trafodion -database supports Java SE 7 or JDK 7 (1.7.0), or earlier versions of the -JDK. - -* Launch `trafci` on the Trafodion platform, and run the `LOCALHOST` or `LH java -version` command. +* Launch `trafci` on the Trafodion platform, and run the `LOCALHOST` or `lh java -version` command. (To use the on-platform trafci client, see the http://trafodion.apache.org/docs/command_interface/index.html[Trafodion Command Interface Guide.) For example: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc b/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc index ac76226..9b1ed05 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc @@ -85,7 +85,7 @@ WITH GRANT OPTION privileges on ADJUSTSALARY to the Payroll directors. [source, sql] ---- GRANT EXECUTE -ON PROCEDURE demo.persnl.adjustsalary +ON PROCEDURE trafodion.persnl.adjustsalary TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; ---- @@ -95,7 +95,7 @@ ADJUSTSALARY to the regional department managers: [source, sql] ---- GRANT EXECUTE -ON PROCEDURE demo.persnl.adjustsalary TO "rgn1mgr", "rgn2mgr", "rgn3mgr" +ON PROCEDURE trafodion.persnl.adjustsalary TO "rgn1mgr", "rgn2mgr", "rgn3mgr" WITH GRANT OPTION ; ---- @@ -113,7 +113,7 @@ TOTALPRICE to PUBLIC, meaning all present and future database users and roles: [source, sql] ---- GRANT EXECUTE -ON PROCEDURE demo.sales.totalprice TO PUBLIC ; +ON PROCEDURE trafodion.sales.totalprice TO PUBLIC ; ---- After granting the EXECUTE privilege to PUBLIC, you cannot revoke the @@ -148,7 +148,7 @@ grants these access privileges to the Payroll directors: [source, sql] ---- GRANT SELECT, UPDATE (salary) -ON TABLE demo.persnl.employee +ON TABLE trafodion.persnl.employee TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; ---- @@ -158,7 +158,7 @@ regional department managers: [source, sql] ---- GRANT SELECT, UPDATE (salary) -ON TABLE demo.persnl.employee +ON TABLE trafodion.persnl.employee TO "rgn1mgr", "rgn2mgr", "rgn3mgr" ; ---- @@ -207,7 +207,7 @@ Region 2 department manager: [source, sql] ---- REVOKE EXECUTE -ON PROCEDURE demo.persnl.adjustsalary FROM "rgn2mgr" ; +ON PROCEDURE trafodion.persnl.adjustsalary FROM "rgn2mgr" ; ---- The `payrolldir1` user cannot revoke the EXECUTE or WITH GRANT OPTION @@ -223,7 +223,7 @@ privilege from the `payrolldir1` user: [source, sql] ---- REVOKE GRANT OPTION FOR EXECUTE -ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1" ; +ON PROCEDURE trafodion.persnl.adjustsalary FROM "payrolldir1" ; ---- A user can also revoke the EXECUTE privilege from any user or role to @@ -237,7 +237,7 @@ whom the `payrolldir1` user granted privileges: [source, sql] ---- REVOKE GRANT OPTION FOR EXECUTE -ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1" +ON PROCEDURE trafodion.persnl.adjustsalary FROM "payrolldir1" CASCADE ; ---- @@ -251,7 +251,7 @@ SPJ named TOTALPRICE from all users and roles (that is, PUBLIC): [source, sql] ---- REVOKE EXECUTE -ON PROCEDURE demo.sales.totalprice FROM PUBLIC; +ON PROCEDURE trafodion.sales.totalprice FROM PUBLIC; ---- == Using Script Files to Grant and Revoke Privileges @@ -273,20 +273,20 @@ GRANT PROCEDURE and GRANT statements: ?SECTION GrantSalesProcs GRANT EXECUTE -ON demo.sales.monthlyorders +ON trafodion.sales.monthlyorders TO PUBLIC ; GRANT SELECT -ON TABLE demo.sales.orders TO PUBLIC ; +ON TABLE trafodion.sales.orders TO PUBLIC ; ?SECTION GrantPersnlProcs GRANT EXECUTE -ON PROCEDURE demo.persnl.adjustsalary TO "payrolldir1", "payrolldir2" +ON PROCEDURE trafodion.persnl.adjustsalary TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; GRANT SELECT, UPDATE(salary) -ON TABLE demo.persnl.employee +ON TABLE trafodion.persnl.employee TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; ---- @@ -310,19 +310,19 @@ a series of REVOKE PROCEDURE and REVOKE statements: ?SECTION RevokeSalesProcs REVOKE EXECUTE -ON PROCEDURE demo.sales.monthlyorders FROM PUBLIC ; +ON PROCEDURE trafodion.sales.monthlyorders FROM PUBLIC ; REVOKE SELECT -ON TABLE demo.sales.orders FROM PUBLIC ; +ON TABLE trafodion.sales.orders FROM PUBLIC ; ?SECTION RevokePersnlProcs REVOKE EXECUTE -ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1", "payrolldir2" +ON PROCEDURE trafodion.persnl.adjustsalary FROM "payrolldir1", "payrolldir2" CASCADE ; REVOKE SELECT, UPDATE(salary) -ON TABLE demo.persnl.employee +ON TABLE trafodion.persnl.employee FROM "payrolldir1", "payrolldir2" CASCADE ; ---- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc b/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc index eb8d690..83fa291 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc @@ -54,7 +54,7 @@ Trafodion database. An SPJ method must be registered as a stored procedure in the database before a client application can execute it with a CALL statement. You upload the SPJ to -the cluster where the Trafodion datbase is running +the cluster where the Trafodion database is running and then you register the SPJ as a library object using the http://trafodion.apache.org/docs/sql_reference/index.html#create_library_statement[CREATE LIBRARY] statement. Next, you register the library object using the @@ -148,8 +148,13 @@ workstation. See <<get-started, Get Started>>. 2. Develop a Java method to be used as an SPJ: .. Write a static Java method: + +Create a file named `Payroll.java` with the following content: ++ [source, java] ---- +import java.sql.* ; +import java.math.* ; + public class Payroll { public static void adjustSalary( BigDecimal empNum @@ -160,14 +165,14 @@ public class Payroll DriverManager.getConnection( "jdbc:default:connection" ) ; PreparedStatement setSalary = - conn.prepareStatement( "UPDATE demo.persnl.employee " + conn.prepareStatement( "UPDATE trafodion.persnl.employee " + "SET salary = salary * (1 + (? / 100)) " + "WHERE empnum = ?" ) ; PreparedStatement getSalary = conn.prepareStatement( "SELECT salary " - + "FROM demo.persnl.employee " + + "FROM trafodion.persnl.employee " + "WHERE empnum = ?" ) ; @@ -190,7 +195,8 @@ public class Payroll .. Compile the Java source file to produce a class file: + ``` -javac Payroll.java +$ javac Payroll.java +$ ``` .. Package the SPJ class file in a JAR file: @@ -203,7 +209,10 @@ If the SPJ class refers to other classes, package the other classes in the same JAR file as the SPJ class: + ``` -jar cvf Payroll.jar Payroll.class other.class +$ jar cvf Payroll.jar Payroll.class other.class +added manifest +adding: Payroll.class(in = 1213) (out= 711)(deflated 41%) +$ ``` + For details, see <<develop-spj-methods, Develop SPJ Methods>>. @@ -222,12 +231,12 @@ GRANT statements in an trafci session, as shown below: [source,sql] ---- GRANT EXECUTE -ON PROCEDURE demo.persnl.adjustsalary +ON PROCEDURE trafodion.persnl.adjustsalary TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; GRANT SELECT, UPDATE (salary) -ON TABLE demo.persnl.employee +ON TABLE trafodion.persnl.employee TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; ---- @@ -239,7 +248,7 @@ For example, you can issue a CALL statement in an trafci session, as shown below, or in a JDBC or ODBC client application: + ``` -SQL> CALL demo.persnl.adjustsalary( 29, 2.5, ? ) ; +SQL> CALL trafodion.persnl.adjustsalary( 29, 2.5, ? ) ; NEWSALARY ------------ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5e15be3e/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc b/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc index 4d12dd3..d0e1e2f 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc @@ -60,25 +60,6 @@ in the Java method are specified as arrays. See that contains a different class name, method name, or method signature than the original JAR file, without dropping and re-creating the SPJ. -In HPDM, compare the library's class name, method name, and method -signature with the procedure's external file (class name), external name -(method name), and signature. - -To see the library's class name, method name, and method signature, launch -the Library Browser dialog box in HPDM by right-clicking the name of the library, -and selecting Browse Library. - -For more information, see <<use-the-library-browser, Use the Library Browser>>. -To see the procedure's external file (class name),external name (method name), -and signature in HPDM, see -<<using-hpdm-to-display-a-procedure-in-a-schema, Using HPDM to Display a Procedure in a Schema>>. -If you notice a discrepancy, then see -<<altering-an-spj-and-its-java-bytecode, Altering an SPJ and Its Java Bytecode>>. - -* Check the characteristics of the stored procedure in HPDM and compare -them with the SPJ method that resides on the Trafodion platform. Fix any -discrepancies. - * Check the syntax of the CALL statement in the application. See <<execute-spjs, Execute SPJs>>. Fix any problems. @@ -129,14 +110,14 @@ Suppose that you want to display the execution plan for this CALL statement: ``` -CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; +CALL trafodion.persnl.adjustsalary( 202, 5.5, ? ) ; ``` Enter this EXPLAIN statement in an trafci session: [source, sql] ---- -SQL> PREPARE spj1 FROM CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; +SQL> PREPARE spj1 FROM CALL trafodion.persnl.adjustsalary( 202, 5.5, ? ) ; --- SQL command prepared. @@ -148,7 +129,7 @@ STATEMENT_NAME ........... SPJ1 PLAN_ID .................. 212206487012085509 ROWS_OUT ................. 1 EST_TOTAL_COST ........... 0 -STATEMENT ................ CALL demo.persnl.adjustsalary( 202, 5.5, ? ) +STATEMENT ................ CALL trafodion.persnl.adjustsalary( 202, 5.5, ? ) ------------------------------------------------------------------ NODE LISTING ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 @@ -169,8 +150,8 @@ DESCRIPTION LDAP_USERNAME .......... sqluser_admin NVCI_PROCESS ........... ON SHOWCONTROL_UNEXTERNALI OFF - SCHEMA ................. DEMO.INVENT - CATALOG ................ NEO + SCHEMA ................. TRAFODION.INVENT + CATALOG ................ TRAFODION PRIORITY ............... 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY ............... 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.DATASOURCES) PRIORITY ............... 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.ENVIRONMENTVALUES) @@ -180,7 +161,7 @@ DESCRIPTION input_variables ........ ? CALL ====================================== SEQ_NO 1 NO CHILDREN -TABLE_NAME ............... DEMO.PERSNL.ADJUSTSALARY +TABLE_NAME ............... TRAFODION.PERSNL.ADJUSTSALARY REQUESTS_IN .............. 1 ROWS_OUT ................. 1 EST_OPER_COST ............ 0 @@ -190,11 +171,11 @@ DESCRIPTION fragment_id ............ 0 parent_frag ............ (none) fragment_type .......... master - routine_name ........... DEMO.PERSNL.ADJUSTSALARY + routine_name ........... TRAFODION.PERSNL.ADJUSTSALARY parameter_modes ........ I I O sql_access_mode ........ MODIFIES SQL DATA external_name .......... adjustSalary - library ................ DEMO.PERSNL.PAYROLL + library ................ TRAFODION.PERSNL.PAYROLL external_file .......... Payroll signature .............. (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V language ............... JAVA @@ -221,7 +202,7 @@ the result table of the EXPLAIN function, as shown below: [source, sql] ---- -SQL> PREPARE spj1 FROM CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; +SQL> PREPARE spj1 FROM CALL trafodion.persnl.adjustsalary( 202, 5.5, ? ) ; --- SQL command prepared. @@ -232,14 +213,14 @@ SQL> SELECT SUBSTRING( operator, 1, 8 ) AS "OPERATOR", operator_cost, OPERATOR OPERATOR_COST DESCRIPTION -------- -------------- -------------------------------------------------------------------------------------- CALL 0.0 max_card_est: -1 fragment_id: 0 parent_frag: (none) fragment_type: master routine_name: -DEMO.PERSNL.ADJUSTSALARY parameter_modes: I I O sql_access_mode: MODIFIES SQL DATA external_name: adjustSalary -library: DEMO.PERSNL.PAYROLL external_file: Payroll signature: (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V +TRAFODION.PERSNL.ADJUSTSALARY parameter_modes: I I O sql_access_mode: MODIFIES SQL DATA external_name: adjustSalary +library: TRAFODION.PERSNL.PAYROLL external_file: Payroll signature: (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V language: JAVA parameter_style: JAVA external_security: INVOKER max_result_sets: 0 parameters: cast(202), cast(cast((cast(5.5) / cast(10)))), NUMERIC(8,2) SIGNED ROOT 0.0 max_card_est: 1 fragment_id: 0 parent_frag: (none) fragment_type: master statement_index: 0 affinity_value: 3466211324 max_max_cardinality: 1 total_overflow_size: 0.00 KB statement: call -demo.persnl.adjustsalary( 202, 5.5 ,? ) xn_autoabort_interval: -1 plan_version: 2500 LDAP_USERNAME: sqluser_admin -NVCI_PROCESS: ON SHOWCONTROL_UNEXTERNALIZED_ATTRS: OFF SCHEMA: DEMO.INVENT CATALOG: NEO PRIORITY: 9 (for table +trafodion.persnl.adjustsalary( 202, 5.5 ,? ) xn_autoabort_interval: -1 plan_version: 2500 LDAP_USERNAME: sqluser_admin +NVCI_PROCESS: ON SHOWCONTROL_UNEXTERNALIZED_ATTRS: OFF SCHEMA: TRAFODION.INVENT CATALOG: TRAFODION PRIORITY: 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY: 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.D --- 2 row(s) selected. SQL>
