http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6f5d4cc4/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 a2557c9..db287f7 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc @@ -39,7 +39,11 @@ This chapter covers these topics: * <<calling-spjs-in-trafci, Calling SPJs in trafci>> * <<calling-spjs-from-an-odbc-client-application, Calling SPJs From an ODBC Client Application>> * <<calling-spjs-from-a-jdbc-client-application, Calling SPJs From a JDBC Client Application>> +//// +GTA 20160321: Triggers aren't supported yet. Uncommented as needed. + * <<calling-an-spj-in-a-trigger, Calling an SPJ in a Trigger>> +//// The CALL statement invokes an SPJ in the database. You can issue a CALL statement from any of these applications or interfaces supported by the @@ -55,12 +59,14 @@ use a CALL statement in a trigger but not inside a compound statement or with rowsets. The SPJ that you use in a trigger must not have any OUT or INOUT parameters or return any result sets. +[[required-privileges-for-calling-an-spj]] == Required Privileges for Calling an SPJ To execute the CALL statement, you must have the EXECUTE privilege on the procedure. For more information, see <<grant-privileges, Grant Privileges>>. +[[transaction-behavior]] == Transaction Behavior The stored procedure's transaction attribute determines whether it @@ -78,40 +84,45 @@ stored procedure to manage its own transactions, you should set the stored procedure's transaction attribute to NO TRANSACTION REQUIRED. See <<no-transaction-required>>. +[[transaction-required]] === Transaction Required If you want the SPJ method to inherit the transaction from the calling application, set the stored procedure's transaction attribute to TRANSACTION REQUIRED (the default setting) when creating the stored procedure. For more information, see the Transaction Required attribute -in link:#_bookmark116["Create a] -link:#_bookmark116[Procedure" (page 37)]. When a stored procedure's +in <<create-a-procedure, Create a Procedure>>. When a stored procedure's transaction attribute is TRANSACTION REQUIRED, a CALL statement automatically initiates a transaction if there is no active transaction. +[[using-transaction-control-statements-or-methods]] ==== Using Transaction Control Statements or Methods If you select Yes for the Transaction Required attribute when creating a -stored procedure, you should not use transaction control statements (or +stored procedure, then you should not use transaction control statements (or equivalent JDBC transaction methods) in the SPJ method. Transaction control statements include COMMIT WORK and ROLLBACK WORK, and the -equivalent JDBC transaction methods are Connection.commit() and -Connection.rollback(). If you try to use transaction control statements -or methods in an SPJ method when the stored procedure's transaction -attribute is set to TRANSACTION REQUIRED, the transaction control -statements or methods in the SPJ method are ignored, and the Java -virtual machine (JVM) does not report any errors or warnings. When the -stored procedure's transaction attribute is set to TRANSACTION REQUIRED, -you should rely on the transaction control statements or methods in the +equivalent JDBC transaction methods are `Connection.commit()` and +`Connection.rollback()`. + +If you try to use transaction control statements or methods in an SPJ method +when the stored procedure's transaction attribute is set to TRANSACTION REQUIRED, +then the transaction control statements or methods in the SPJ method are ignored +and the Java virtual machine (JVM) does not report any errors or warnings. + +When the stored procedure's transaction attribute is set to TRANSACTION REQUIRED, +then you should rely on the transaction control statements or methods in the application that calls the stored procedure and allow the calling application to manage the transactions. +[[committing-or-rolling-back-a-transaction]] ===== Committing or Rolling Back a Transaction If you do not use transaction control statements in the calling -application, the transaction initiated by the CALL statement might not -automatically commit or roll back changes to the database. When -AUTOCOMMIT is ON (the default setting), the database engine +application, then the transaction initiated by the CALL statement might not +automatically commit or roll back changes to the database. + +When AUTOCOMMIT is ON (the default setting), then the database engine automatically commits or rolls back any changes made to the database at the end of the CALL statement execution. However, when AUTOCOMMIT is OFF, the current transaction remains active until the end of the client @@ -121,8 +132,10 @@ To ensure an atomic unit of work when calling an SPJ, use the COMMIT WORK statement in the calling application to commit the transaction when the CALL statement succeeds, and use the ROLLBACK WORK statement to roll back the transaction when the CALL statement fails. For more information -about transaction management, see the __Trafodion SQL Reference Manual__. +about transaction management, see the +http://trafodion.incubator.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual]. +[[no-transaction-required]] === No Transaction Required In some cases, you might not want the SPJ method to inherit the @@ -135,53 +148,59 @@ useful in these cases: multiple DDL or table maintenance operations, on the database. In this case, you might want to commit those operations periodically from within the SPJ method to avoid locking tables for a long time. + * The stored procedure performs certain SQL operations that must run without an active transaction. For example, INSERT, UPDATE, and DELETE statements with the WITH NO ROLLBACK option are rejected when a transaction is already active, as is the case when a stored procedure inherits a transaction from the calling application. The PURGEDATA utility is also rejected when a transaction is already active. + * The stored procedure does not access the database. (For an example, -see the link:#_bookmark250["TOTALPRICE] link:#_bookmark250[Procedure" -(page 73)].) In this case, the stored procedure does not need to inherit -the transaction from the calling application. By setting the stored -procedure's transaction attribute to NO TRANSACTION REQUIRED, you can -avoid the overhead of the calling application's transaction being -propagated to the stored procedure. +see the <<totalprice-procedure, TOTALPRICE Procedure>>.) In this case, +the stored procedure does not need to inherit the transaction from the +calling application. By setting the stored procedure's transaction +attribute to NO TRANSACTION REQUIRED, you can avoid the overhead of +the calling application's transaction being propagated to the stored +procedure. In these cases, you should set the stored procedure's transaction attribute to NO TRANSACTION REQUIRED when creating the stored procedure. For more information, see the Transaction Required attribute in -link:#_bookmark116["Create a Procedure" (page 37)]. +<<create-a-procedure, Create a Procedure>>. If you select No for the Transaction Required attribute when creating a stored procedure and if the SPJ method creates a JDBC default -connection, that connection will have autocommit enabled by default. You +connection, then that connection has autocommit enabled by default. You can either use the autocommit transactions or disable autocommit -(conn.setAutoCommit(false);) and use the JDBC transaction methods, -Connection.commit() and Connection.rollback(), to commit or roll back +(`conn.setAutoCommit(false);`) and use the JDBC transaction methods, +`Connection.commit()` and `Connection.rollback()`, to commit or roll back work where needed. +[[multithreading]] == Multithreading 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. The CALL statements in a -multithreaded application can execute in a nonblocking manner, but the -SPJ methods underlying those CALL statements execute serially within a +is a multithreaded Java application. + +The CALL statements in a multithreaded application can execute in a nonblocking manner, +but the SPJ methods underlying those CALL statements execute serially within a given SPJ environment. +[[using-the-call-statement]] == Using the CALL Statement To invoke a stored procedure, specify the name of the stored procedure -and its arguments in a CALL statement, as shown in -link:#_bookmark167[Figure 2]: +and its arguments in a CALL statement, as shown in the figure below. -image:media/image8.png[image]Figure 2 CALL Statement Elements +.CALL Statement Elements +image:{images}/call-statement-elements.jpg[CALL Statement Elements] -For the syntax of the CALL statement, see the __Trafodion SQL Reference -Manual__. +For the syntax of the CALL statement, see the +http://trafodion.apache.org/docs/sql_reference/index.html#call_statement[Trafodion SQL Reference Manual]. +[[specifying-the-name-of-the-spj]] === Specifying the Name of the SPJ In the CALL statement, specify the name of an SPJ that you have already @@ -189,22 +208,25 @@ created in the database. Qualify the procedure name with the same catalog and schema that you specified when you registered the SPJ. For example: -``` -CALL demo.persnl.adjustsalary(202, 5.5, ?); -``` +[source, sql] +---- +CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; +---- Or, for example: -``` -SET SCHEMA demo.persnl; +[source, sql] +---- +SET SCHEMA demo.persnl ; -CALL adjustsalary(202, 5.5, ?); -``` +CALL adjustsalary( 202, 5.5, ? ) ; +---- -If you do not fully qualify the procedure name, the database engine +If you do not fully qualify the procedure name, then the database engine qualifies the procedure according to the catalog and schema of the current session. +[[listing-the-parameter-arguments-of-the-spj]] === Listing the Parameter Arguments of the SPJ Each argument that you list in the CALL statement must correspond to an @@ -213,20 +235,22 @@ method does not correspond to an SQL parameter. Do not specify result sets in the argument list. For example, if you registered the stored procedure with three SQL -parameters (two IN parameters and one OUT parameter), you must list -three formal parameter arguments, separated by commas, in the CALL -statement: +parameters (two IN parameters and one OUT parameter), then you must +list three formal parameter arguments, separated by commas, in the +CALL statement: -``` -CALL demo.persnl.adjustsalary(202, 5, ?); -``` +[source, sql] +---- +CALL demo.persnl.adjustsalary( 202, 5, ? ) ; +---- If the SPJ does not accept arguments, you must specify empty parentheses, as shown below: -``` -CALL demo.sales.lowerprice(); -``` +[source, sql] +---- +CALL demo.sales.lowerprice() ; +---- If the SPJ has one IN parameter, one OUT parameter, and two result sets, you must list the IN and OUT parameters but not the result sets in the @@ -236,111 +260,98 @@ argument list: CALL demo.sales.ordersummary('01-01-2011', ?); ``` +[[data-conversion-of-parameter-arguments]] ==== Data Conversion of Parameter Arguments The database engine performs an implicit data conversion when the data type of a parameter argument is compatible with but does not match the -formal data type of the stored procedure. For stored procedure input -values, the conversion is from the actual argument value to the formal -parameter type. For stored procedure output values, the conversion is -from the actual output value, which has the data type of the formal -parameter, to the declared type of the dynamic parameter. +formal data type of the stored procedure. + +For stored procedure input values, the conversion is from the actual +argument value to the formal parameter type. +For stored procedure output values, the conversion is from the actual +output value, which has the data type of the formal parameter, to the +declared type of the dynamic parameter. + +[[input-parameter-arguments]] ==== Input Parameter Arguments To pass data to an IN or INOUT parameter of an SPJ, specify an SQL value expression that evaluates to a character, date-time, or numeric value. The SQL value expression can evaluate to NULL provided that the underlying Java parameter supports null values. For more information, -see link:#_bookmark58["Null Input and Output" (page 22)]. +see <<null-input-and-output, Null Input and Output>>. For an IN parameter argument, use one of these SQL value expressions in -link:#_bookmark175[Table 2]: - -Table 2 Input Parameter Argument Types - -Type of Argument Examples - -Literal - -CALL adjustsalary(**202**, 5.5, ?); - -CALL dailyorders(**DATE '2011-03-19**', ?); CALL totalprice(23, -*'nextday'* , ?param); - -SQL function (including CASE and CAST expressions) - -CALL dailyorders(**CURRENT_DATE**, ?); - -Arithmetic expression CALL adjustsalary(202, **?percent * 0.25**, :OUT -newsalary); - -Concatenation operation - -CALL totalprice(23, **'next' || 'day'**, ?param); - -Scalar subquery - -Dynamic parameter - -CALL totalprice(**(SELECT qty_ordered** - -*FROM odetail* - -**WHERE ordernum = 100210 AND partnum = 5100)**, - -'nextday', ?param); - -CALL adjustsalary(**?**, ?, ?); - -CALL adjustsalary(**?param1**, ?param2, ?param3); - -For more information about SQL value expressions, see the __Trafodion SQL -Reference Manual__. +that table below: + +[[table-2]] +.Input Parameter Argument Types +[cols="30%,70%", options="header"] +|=== +| Type of Argument | Examples +| Literal | +`CALL adjustsalary( *202*, 5.5, ? ) ;` + +`CALL dailyorders( *DATE '2011-03-19'*, ? )` ; + +`CALL totalprice(23, *'nextday'* , ?param ) ;` +| SQL function (including CASE and CAST expressions) | +`CALL dailyorders( *CURRENT_DATE*, ? ) ;` +| Arithmetic expression | +`CALL adjustsalary( 202, *?percent \* 0.25*, :OUT newsalary ) ;` +| Concatenation operation | +`CALL totalprice( 23, *'next' \|\| 'day'*, ?param ) ;` +| Scalar subquery | +`CALL totalprice ( ( SELECT qty_ordered FROM odetail WHERE ordernum = 100210 AND partnum = 5100 ) , 'nextday', ?param ) ;` +| Dynamic parameter | +`CALL adjustsalary( *?*, ?, ?) ;` + +`CALL adjustsalary( *?param1*, ?param2, ?param3 ) ;` +|=== + +For more information about SQL value expressions, see the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual]. Because an INOUT parameter passes a single value to and accepts a single value from an SPJ, you can specify only dynamic parameters for INOUT parameter arguments in a CALL statement. +[[output-parameter-arguments]] ==== Output Parameter Arguments Except for result sets, an SPJ returns values in OUT and INOUT parameters. Each OUT or INOUT parameter accepts only one value from an SPJ. Any attempt to return more than one value to an output parameter -results in a Java exception. See link:#_bookmark48["Returning Output -Values From the Java Method"] link:#_bookmark48[(page 19)]. +results in a Java exception. See +<<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>>. OUT and INOUT parameter arguments must be dynamic parameters in a client -application (for example, ?) or named or unnamed parameters in trafci -(for example, ?param or ?). +application (for example, `?`) or named or unnamed parameters in trafci +(for example, `?param` or `?`). For information about how to call SPJs in different applications, see: -* link:#_bookmark188["Calling SPJs in trafci" (page 53)] -* link:#_bookmark196["Calling SPJs From an ODBC Client Application" -(page 54)] -* link:#_bookmark200["Calling SPJs From a JDBC Client Application" (page -56)] +* <<calling-spjs-in-trafci, Calling SPJs in trafci>> +* <<calling-spjs-from-an-odbc-client-application, Calling SPJs From an ODBC Client Application>> +* <<calling-spjs-from-a-jdbc-client-application, Calling SPJs From a JDBC Client Application>> +[[result-sets]] ==== Result Sets Result sets are an ordered set of open cursors that the SPJ method -returns to the calling application in java.sql.ResultSet[] parameter -arrays. The java.sql.ResultSet[] parameters do not correspond to SQL +returns to the calling application in `java.sql.ResultSet[]` parameter +arrays. The `java.sql.ResultSet[]` parameters do not correspond to SQL parameters, so you must not include them in the parameter argument list of a CALL statement. The calling application can retrieve multiple rows of data from the -java.sql.ResultSet[] -parameters. For information about how to process result sets in -different applications, see: +`java.sql.ResultSet[]` parameters. For information about how to process +result sets in different applications, see: -* link:#_bookmark194["Returning Result Sets in trafci" (page 54)] -* link:#_bookmark198["Returning Result Sets in an ODBC Client -Application" (page 55)] -* link:#_bookmark204["Returning Result Sets in a JDBC Client -Application" (page 56)] +* <<returning-result-sets-in-trafci, Returning Result Sets in trafci>> +* <<returning-result-sets-in-an-odbc-client, Returning Result Sets in an ODBC Client>> +* <<returning-result-sets-in-a-jdbc-client, Returning Result Sets in a JDBC Client>> +[[calling-spjs-in-trafci]] == Calling SPJs in trafci In trafci, you can invoke an SPJ by issuing a CALL statement directly or @@ -356,26 +367,29 @@ displays all output parameter values and result sets after you issue the CALL statement. The procedure call changes the value of a named parameter that you use as an OUT or INOUT parameter. -For more information about named and unnamed parameters, see the _HP -Database Command_ __Interface (trafci) Guide__. +For more information about named and unnamed parameters, see the +http://trafodion.incubator.apache.org/docs/command_interface/index.html[Trafodion Command Interface Guide]. +[[using-named-parameters]] === Using Named Parameters In an trafci session, invoke the SPJ named TOTALPRICE, which has two IN -parameters and one INOUT parameter. This SPJ accepts the quantity, -shipping speed, and price of an item, calculates the total price, -including tax and shipping charges, and returns the total price. For -more information, see the link:#_bookmark250["TOTALPRICE Procedure" -(page 73)]. +parameters and one INOUT parameter. + +This SPJ accepts the quantity, shipping speed, and price of an item, +calculates the total price, including tax and shipping charges, and +returns the total price. For more information, see the +<<totalprice-procedure, TOTALPRICE Procedure>>. Set the input value for the INOUT parameter by entering a SET PARAM command before calling the SPJ: -``` -SQL>**set param ?p 10;** +[source, sql] +---- +SQL> SET PARAM ?p 10 ; -SQL>**call demo.sales.totalprice(23, 'standard', ?p);** -``` +SQL> CALL demo.sales.totalprice( 23, 'standard', ?p ) ; +---- The CALL statement returns the total price of the item: @@ -390,12 +404,14 @@ p The value of the named parameter, ?p, changes from 10 to the returned value, 253.97: -``` -SQL>**show param** +[source, sql] +---- +SQL> SHOW PARAM p 253.97 -``` +---- +[[using-unnamed-parameters]] === Using Unnamed Parameters In an trafci session, invoke the SPJ named TOTALPRICE by preparing and @@ -403,13 +419,14 @@ executing a CALL statement. The INOUT parameter accepts a value that is set by the USING clause of the EXECUTE statement and returns the total price: -``` -SQL>**prepare stmt1 from call demo.sales.totalprice(50, 'nextday',?);** +[source, sql] +---- +SQL> PREPARE stmt1 FROM CALL demo.sales.totalprice( 50, 'nextday', ? ) ; --- SQL command prepared. -SQL>**execute stmt1 using 2.25;** -``` +SQL> EXECUTE stmt1 USING 2.25 ; +---- The output of the prepared CALL statement is: @@ -426,25 +443,28 @@ and executing a CALL statement in which all three parameters accept values that are set by the USING clause of the EXECUTE statement. The INOUT parameter returns the total price: -``` -SQL>**prepare stmt2 from call demo.sales.totalprice(?,?,?);** +[source, sql] +---- +SQL> PREPARE stmt2 FROM CALL demo.sales.totalprice( ?, ?, ? ) ; --- SQL command prepared. -SQL>**execute stmt2 using 3, 'economy', 16.99;** -``` +SQL> EXECUTE stmt2 USING 3, 'economy', 16.99 ; +---- The output of the prepared CALL statement is: -``` +[source, sql] +---- PRICE -------------------- 57.13 --- SQL operation complete. -``` +---- +[[returning-result-sets-in-trafci]] === Returning Result Sets in trafci If a CALL statement returns result sets, trafci displays column headings @@ -452,8 +472,10 @@ and data for each returned result set in the same format as SELECT statements. For example, this CALL statement returns an output parameter for the number of orders and two result sets in the trafci session: -``` -SQL>**call demo.sales.ordersummary('01-01-2011', ?);** + +[source, sql] +---- +SQL> CALL demo.sales.ordersummary( '01-01-2011', ? ) ; NUM_ORDERS -------------------- @@ -461,195 +483,213 @@ NUM_ORDERS ORDERNUM NUM_PARTS AMOUNT ORDER_DATE LAST_NAME -------- -------------------- -------------------- ---------- -------------------- -HUGHES HUGHES SCHNABL - -... + 100210 4 19020.00 2011-04-10 HUGHES + 100250 4 22625.00 2011-01-23 HUGHES + 101220 4 45525.00 2011-07-21 SCHNABL + ... ... ... ... ... --- 13 row(s) selected. ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC - --------- ------- ---------- ----------- ------------------ 100210 244 -3500.00 3 PC GOLD, 30 MB - -100210 2001 1100.00 3 GRAPHIC PRINTER,M1 - -100210 2403 620.00 6 DAISY PRINTER,T2 - -... ... ... ... ... +-------- ------- ---------- ----------- ------------------ + 100210 244 3500.00 3 PC GOLD, 30 MB + 100210 2001 1100.00 3 GRAPHIC PRINTER,M1 + 100210 2403 620.00 6 DAISY PRINTER,T2 + ... ... ... ... ... --- 70 row(s) selected. --- SQL operation complete. -``` +---- -For other result set examples, see link:#_bookmark221[Appendix A (page -62)]. +For other result set examples, see <<sample-spjs, Appendix A: Sample SPJs>>. +[[calling-spjs-from-an-odbc-client-application]] == Calling SPJs From an ODBC Client Application You can execute a CALL statement in an ODBC client application. Microsoft ODBC requires that you put the CALL statement in an escape clause: -``` -{call _procedure-name_ ([__parameter__][,[__parameter__]]...)} -``` +[source, sql] +---- +{ CALL procedure-name ( [ parameter ] [ , [ parameter ] ] ... ) } +---- For IN or INOUT parameters, use a literal or a parameter marker (?). You cannot use an empty string as an IN or INOUT parameter in the argument list. If you specify a literal for an INOUT parameter, the driver discards the output value. -For OUT parameters, you can use only a parameter marker (?). You must -bind all parameter markers with the SQLBindParameter function before you +For OUT parameters, you can use only a parameter marker (`?`). You must +bind all parameter markers with the `SQLBindParameter` function before you can execute the CALL statement. In this example, a CALL statement is executed from an ODBC client application: -``` +[source, cplusplus] +---- /* Declare variables. */ -SQLHSTMT hstmt; SQL_NUMERIC_STRUCT salary; SDWORD cbParam = SQL_NTS; +SQLHSTMT hstmt ; +SQL_NUMERIC_STRUCT salary ; +SDWORD cbParam = SQL_NTS ; /* Bind the parameter markers. */ - -SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 4, 0, 202, 0, &cbParam); - -SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 0, 0, 5.5, 0, &cbParam); - -SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_NUMERIC, SQL_NUMERIC, 8, 2, &salary, 0, &cbParam); +SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 4, 0, 202, 0, &cbParam ) ; +SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 0, 0, 5.5, 0, &cbParam ) ; +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 demo.persnl.adjustsalary(?,?,?)}", SQL_NTS); -``` +//// +GTA 20160321: The JDBC T4 manual does not exist yet. Enable this line +once written. -For more information about ODBC client applications, see the __Neoview -ODBC Drivers Manual__. +For more information about the ODBC client applications, +see the +http://trafodion.incubator.apache.org/docs/odbc_reference/index.html[Trafodion ODBC Driver Programmer's Reference]. +//// + +[[returning-result-sets-in-an-odbc-client-application]] === Returning Result Sets in an ODBC Client Application This example shows how an ODBC client application processes the result -sets returned by a CALL statement. The SQLMoreResults() function closes +sets returned by a CALL statement. The `SQLMoreResults()` function closes the current result set and moves processing to the next available result set. -NOTE: The HP ODBC API does not currently support interleaved result set +NOTE: The Trafodion ODBC API does not currently support interleaved result set processing, where more than one returned result set can be open at a time. -``` +[source, cplusplus] +---- /* Allocate a statement handle */ -SQLHSTMT s; +SQLHSTMT s ; -RETCODE rc = SQLAllocHandle(SQL_HANDLE_STMT, myConnection, &s); +RETCODE rc = SQLAllocHandle( SQL_HANDLE_STMT, myConnection, &s ) ; /* Prepare a CALL */ -char *stmtText = "{call demo.sales.ordersummary('01-01-2011', ?)}"; -rc = SQLPrepare(s, (SQLCHAR *) stmtText, strlen(stmtText)); +char *stmtText = "{ CALL demo.sales.ordersummary( '01-01-2011', ? ) } "; +rc = SQLPrepare( s, (SQLCHAR *) stmtText, strlen( stmtText ) ) ; /* Bind the output parameter */ -_int64 num_orders = 0; SQLINTEGER indicator; - -rc = SQLBindParameter(s, 2, SQL_PARAM_OUTPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, &num_orders, 0, &indicator); +_int64 num_orders = 0 ; +SQLINTEGER indicator ; + +rc = SQLBindParameter( s + , 2 + , SQL_PARAM_OUTPUT + , SQL_C_SBIGINT + , SQL_BIGINT + , 0 + , 0 + , &num_orders + , 0 + , &indicator + ) ; /* Execute the CALL */ -rc = SQLExecute(s); +rc = SQLExecute( s ) ; /* Process all returned result sets. The outer while loop repeats */ - /* until there are no more result sets. */ -while ((rc = SQLMoreResults(s)) != SQL_NO_DATA) +while ( ( rc = SQLMoreResults( s ) ) != SQL_NO_DATA ) { /* The inner while loop processes each row of the current result set */ - while (SQL_SUCCEEDED(rc = SQLFetch(hStmt))) + while ( SQL_SUCCEEDED( rc = SQLFetch( hStmt ) ) ) { /* Process the row */ } } +---- +[[calling-spjs-from-a-jdbc-client-application]] == Calling SPJs From a JDBC Client Application You can execute a CALL statement in a JDBC client application by using the JDBC CallableStatement interface. The HP JDBC Type 4 driver requires that you put the CALL statement in an escape clause: -``` -{call _procedure-name_ ([__parameter__[{, __parameter__}...]])} -``` +[source, sql] +---- +{ CALL procedure-name ( [ parameter [ { , parameter } ... ] ] ) } +---- -Set input values for IN and INOUT parameters by using the set__type__() -methods of the CallableStatement interface. +Set input values for IN and INOUT parameters by using the `set_type()` +methods of the `CallableStatement` interface. Retrieve output values from OUT and INOUT parameters by using the -get__type__() methods of the CallableStatement interface. +`get_type()` methods of the `CallableStatement` interface. -If the parameter mode is OUT or INOUT, you must register the parameter -as an output parameter by using the registerOutParameter() method of the -CallableStatement interface before executing the CALL statement. +If the parameter mode is OUT or INOUT, then you must register the parameter +as an output parameter by using the `registerOutParameter()` method of the +`CallableStatement` interface before executing the CALL statement. 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 demo.persnl.adjustsalary(?,?,?)}"); +stmt.setBigDecimal( 1, 202 ) ; // x = 202 +stmt.setDouble( 2, 5.5 ) ; // y = 5.5 +stmt.registerOutParameter( 3, java.sql.Types.NUMERIC ) ; +stmt.execute() ; -stmt.setBigDecimal(1,202); // x = 202 stmt.setDouble(2,5.5); // y = 5.5 -stmt.registerOutParameter(3, java.sql.Types.NUMERIC); +BigDecimal z = stmt.getBigDecimal( 3 ) ; // Retrieve the value of the OUT parameter +---- -stmt.execute(); +//// +GTA 20160321: The JDBC T4 manual does not exist yet. Enable this line +once written. -BigDecimal z = stmt.getBigDecimal(3); // Retrieve the value of the -// OUT parameter -``` -For more information about the HP JDBC Type 4 driver and mappings of SQL -to JDBC data types, see the __Neoview JDBC Type 4 Driver Programmer's -Reference__. +For more information about the Trafodion JDBC Type 4 driver and mappings of SQL to JDBC data types, +see the +http://trafodion.incubator.apache.org/docs/jdbct4_reference/index.html[Trafodion JDBC Type 4 Driver Programmer's Reference]. +//// + +[[returning-result-sets-in-a-jdbc-client-application]] === Returning Result Sets in a JDBC Client Application This example shows serial result set processing in a JDBC client application where the result sets are processed in order and one at a time after the CALL statement executes. The -java.sql.Statement.getMoreResults() method closes the current result set +`java.sql.Statement.getMoreResults()` method closes the current result set and moves processing to the next available result set. -``` +[source, java] +---- // Prepare a CALL statement +java.sql.CallableStatement s = + myConnection.prepareCall( "{ CALL demo.sales.ordersummary( '01-01-2011', ? ) }" ) ; -java.sql.CallableStatement s = myConnection.prepareCall ( "{call -demo.sales.ordersummary('01-01-2011', ?)}" ); - -// Register an output parameter s.registerOutParameter(1, -java.sql.Types.BIGINT); +// Register an output parameter +s.registerOutParameter( 1, java.sql.Types.BIGINT ) ; // Execute the CALL - -boolean rsAvailable = s.execute(); +boolean rsAvailable = s.execute() ; // Process all returned result sets. The outer while loop continues - -// until there are no more result sets. while (rsAvailable) - +// until there are no more result sets. +while ( rsAvailable ) { - -// The inner while loop processes each row of the current result set -java.sql.ResultSet rs = s.getResultSet(); - -while (rs.next()) - -{ - -// Process the row - -} - -rsAvailable = s.getMoreResults(); - + // The inner while loop processes each row of the current result set + java.sql.ResultSet rs = s.getResultSet() ; + while ( rs.next() ) + { + // Process the row + } + + rsAvailable = s.getMoreResults() ; } -``` +---- This example shows how a JDBC client application can have more than one stored procedure result set open at a given time. The @@ -658,73 +698,72 @@ uses its input argument to decide whether currently open result sets should remain open or be closed before the next result set is made available. -``` +[source, java] +---- // Prepare a CALL statement +java.sql.CallableStatement s = + myConnection.prepareCall ( "{ CALL demo.sales.ordersummary( '01-01-2011', ? ) }" ) ; -java.sql.CallableStatement s = myConnection.prepareCall ( "{call -demo.sales.ordersummary('01-01-2011', ?)}" ); +// Register an output parameter +s.registerOutParameter( 1, java.sql.Types.BIGINT ) ; -// Register an output parameter s.registerOutParameter(1, -java.sql.Types.BIGINT); - -// Execute the CALL s.execute(); +// Execute the CALL +s.execute() ; -// Open the FIRST result set java.sql.ResultSet firstRS = -s.getResultSet(); +// Open the FIRST result set +java.sql.ResultSet firstRS = s.getResultSet() ; // Open the SECOND result set but do not close the FIRST -s.getMoreResults(java.sql.Statement.KEEP_CURRENT_RESULT); -java.sql.ResultSet secondRS = s.getResultSet(); +s.getMoreResults( java.sql.Statement.KEEP_CURRENT_RESULT ) ; +java.sql.ResultSet secondRS = s.getResultSet() ; // The outer loop processes each row of the FIRST result set while -(firstRS.next()) - +( firstRS.next()) { - -// Process a row from the FIRST result set -// The inner loop processes some number of rows from the SECOND -// result set. The number depends on data extracted from the -// current row of the FIRST result set. - -for (int i = 0; i < NUM_ROWS_TO_PROCESS; i++) - -{ - -// Process a row from the SECOND result set secondRS.next(); - + // Process a row from the FIRST result set + // The inner loop processes some number of rows from the SECOND + // result set. The number depends on data extracted from the + // current row of the FIRST result set. + for ( int i = 0 ; i < NUM_ROWS_TO_PROCESS ; i++ ) + { + // Process a row from the SECOND result set + secondRS.next() ; + } } +---- -} -``` +//// +GTA 20160321: Triggers aren't supported yet. Uncommented as needed. +[[calling-an-spj-in-a-trigger]] == Calling an SPJ in a Trigger A trigger is a mechanism in the database that enables the database engine to perform certain actions when a specified event occurs. SPJs are useful as triggered actions because they can help you encapsulate and enforce rules in the database. For more information about the -benefits of using SPJs, see link:#_bookmark11["Benefits of SPJs" (page -11)]. +benefits of using SPJs, see <<benefits-of-spjs, Benefits of SPJs>> Trafodion SQL supports a CALL statement in a trigger provided that the SPJ in the CALL statement does not have any OUT or INOUT parameters or return any result sets. For more information, see -link:#_bookmark48["Returning Output Values From the Java Method" (page -19)], link:#_bookmark183["Output Parameter Arguments"] -link:#_bookmark183[(page 52)], or link:#_bookmark54["Returning Stored -Procedure Result Sets" (page 20)]. +<<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>>, +<<output-parameter-arguments, Output Parameter Arguments>>, or +<<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>>. This example creates a trigger that executes an SPJ named LOWERPRICE when the QTY_ON_HAND column of the PARTLOC table is updated and exceeds 500 parts: -``` +[source, sql] +---- CREATE TRIGGER demo.sales.setsalesprice AFTER UPDATE OF qty_on_hand -ON demo.invent.partloc FOR EACH STATEMENT -REFERENCING NEW as newqty -WHEN ( SUM(newqty.qty_on_hand) > 500 ) CALL demo.sales.lowerprice(); -``` - -For information about the CREATE TRIGGER syntax, see the __Trafodion SQL -Reference Manual__. - +ON demo.invent.partloc +FOR EACH STATEMENT REFERENCING NEW as newqty + WHEN ( SUM( newqty.qty_on_hand ) > 500 ) + CALL demo.sales.lowerprice() ; +---- + +For information about the CREATE TRIGGER syntax, see the +http://trafodion.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual]. +////
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6f5d4cc4/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 9ff668c..ac76226 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc @@ -21,7 +21,7 @@ //// [[grant-privileges]] -== Grant Privileges += Grant Privileges Security for SPJs is implemented by schema ownership rules and by granting privileges to specified database users and roles. @@ -33,47 +33,40 @@ automatically has these privileges: * Ability to create and drop SPJs in the schema. You can create SPJs in the schema provided that you also 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 +for the SQL_OPERATIONS component. * EXECUTE and WITH GRANT OPTION privileges on the SPJs in the schema To create or drop an SPJ in a schema, you must be the schema owner or have the appropriate create or drop privileges for the schema. For more -information, see link:#_bookmark114["Required Privileges for Creating] -link:#_bookmark114[or Dropping an SPJ" (page 37)]. To invoke an SPJ, you -must have the EXECUTE privilege on the SPJ. The EXECUTE privilege allows -a user to invoke an SPJ by issuing a CALL statement. The WITH GRANT +information, see +<<required-privileges-for-creating-or-dropping-an-spj, Required Privileges for Creating or Dropping an SPJ>>. + +You must have the EXECUTE privilege on the SPJ to it. The EXECUTE privilege +allows a user to invoke an SPJ by issuing a CALL statement. The WITH GRANT OPTION privilege allows a user to grant the EXECUTE and WITH GRANT OPTION privileges to other users and roles. For more information, see: -* link:#_bookmark141["Granting Execute Privileges on an SPJ" (page 45)] -* link:#_bookmark143["Granting Privileges on Referenced Database -Objects" (page 46)] -* link:#_bookmark145["Revoking Execute Privileges on an SPJ" (page 47)] -* link:#_bookmark147["Using Script Files to Grant and Revoke Privileges" -(page 47)] +* <<granting-execute-privileges-on-an-spj, Granting Execute Privileges on an SPJ>> +* <<granting-privileges-on-referenced-database-objects, Granting Privileges on Referenced Database Objects>> +* <<revoking-execute-privileges-on-an-spj, Revoking Execute Privileges on an SPJ>> +* <<using-script-files-to-grant-and-revoke-privileges, Using Script Files to Grant and Revoke Privileges>> To display the current ownership and privileges, see -link:#bookmark130["Display Procedures and Their Properties"] -link:#bookmark130[(page 42)]. - -NOTE: You can also grant or revoke the EXECUTE privilege on an SPJ by -using the Grant/Revoke Privileges Tool in HPDM. For more information, -see the _HP Database Manager (HPDM) User Guide_ or the online help in -HPDM. +<<display-procedures-and-their-properties, Display Procedures and Their Properties>>. +[[granting-execute-privileges-on-an-spj]] == Granting Execute Privileges on an SPJ Use the GRANT PROCEDURE or GRANT statement to assign the EXECUTE and WITH GRANT OPTION privileges on an SPJ to specific database users and roles. In a GRANT statement, specify ALL PRIVILEGES to grant the EXECUTE privilege on an SPJ. For the syntax of the GRANT PROCEDURE and GRANT -statements, see the __Trafodion SQL Reference Manual__. +statements, see the +http://trafodion.incubator.apache.org/docs/sql_reference/index.html[Trafodion SQL Reference Manual]. -If you own the SPJ, you can grant the EXECUTE and WITH GRANT OPTION +If you own the SPJ, then you can grant the EXECUTE and WITH GRANT OPTION privileges on the SPJ to any database user or role. If you are not the -owner of the SPJ, you must have been granted the EXECUTE and WITH GRANT +owner of the SPJ, then you must have been granted the EXECUTE and WITH GRANT OPTION privileges on the SPJ to grant privileges to other database users and roles, or you must be associated with a role that has the EXECUTE and WITH GRANT OPTION privileges on the SPJ. @@ -82,62 +75,69 @@ As the owner of an SPJ, you can selectively grant the EXECUTE and WITH GRANT OPTION privileges to specified database users and roles. For some SPJs, particularly ones that handle sensitive information or modify data, you should grant the EXECUTE and WITH GRANT OPTION privileges to a -restricted group of users or roles. For example, the SPJ named -ADJUSTSALARY changes an employee's salary in the database. Therefore, -only specific users or roles should be allowed to invoke this SPJ. In -this example, the SPJ owner (or creator) grants the EXECUTE and WITH -GRANT OPTION privileges on ADJUSTSALARY to the Payroll directors. +restricted group of users or roles. -``` -GRANT EXECUTE +For example, the SPJ named ADJUSTSALARY changes an employee's salary in the +database. Therefore, only specific users or roles should be allowed to invoke +this SPJ. In this example, the SPJ owner (or creator) grants the EXECUTE and +WITH GRANT OPTION privileges on ADJUSTSALARY to the Payroll directors. +[source, sql] +---- +GRANT EXECUTE ON PROCEDURE demo.persnl.adjustsalary - -TO "payrolldir1", "payrolldir2" WITH GRANT OPTION; -``` +TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; +---- One of the Payroll directors grants the EXECUTE privilege on ADJUSTSALARY to the regional department managers: -``` +[source, sql] +---- GRANT EXECUTE ON PROCEDURE demo.persnl.adjustsalary TO "rgn1mgr", "rgn2mgr", "rgn3mgr" -WITH GRANT OPTION; -``` +WITH GRANT OPTION ; +---- In some cases, all users of a database system might need to invoke an -SPJ. For example, the SPJ named TOTALPRICE calculates the total price of +SPJ. + +For example, the SPJ named TOTALPRICE calculates the total price of an item, including tax and shipping charges. This SPJ does not handle sensitive information or modify data and might be useful to customers or -anyone within the company. Therefore, the SPJ owner (or creator) grants -the EXECUTE privilege on TOTALPRICE to PUBLIC, meaning all present and -future database users and roles: +anyone within the company. -``` +Therefore, the SPJ owner (or creator) grants the EXECUTE privilege on +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 demo.sales.totalprice TO PUBLIC ; +---- After granting the EXECUTE privilege to PUBLIC, you cannot revoke the privilege from a subset of database users or roles. You must revoke the privilege from PUBLIC and then grant the privilege to specific database users and roles. +[[granting-privileges-on-referenced-database-objects]] == Granting Privileges on Referenced Database Objects If the SPJ operates on a database object and the SPJ has been created with the external security specified as invoker (EXTERNAL SECURITY -INVOKER), the database users that invoke the SPJ must have the -appropriate privileges on that database object. If the SPJ has been -created with the external security specified as definer (EXTERNAL -SECURITY DEFINER), users other than the procedure's creator may invoke +INVOKER), then the database users that invoke the SPJ must have the +appropriate privileges on that database object. + +If the SPJ has been created with the external security specified as definer (EXTERNAL +SECURITY DEFINER), then users other than the procedure's creator may invoke the SPJ without needing privileges on the underlying database objects. + When the SPJ's external security is definer, users execute, or invoke, the stored procedure using the privileges of the user who created the stored procedure. The user who creates the stored procedure must have the appropriate privileges on the underlying database objects. For more -information, see link:#_bookmark124["Understand External Security" (page -41)]. +information, see <<understand-external-security, Understand External Security>>. For example, users with the EXECUTE privilege on the SPJ named ADJUSTSALARY, which is defined with EXTERNAL SECURITY INVOKER and which @@ -145,20 +145,22 @@ selects data from and updates the EMPLOYEE table, must have the SELECT and UPDATE privileges on that SQL table. The SPJ owner (or creator) grants these access privileges to the Payroll directors: -``` +[source, sql] +---- GRANT SELECT, UPDATE (salary) ON TABLE demo.persnl.employee -TO "payrolldir1", "payrolldir2" WITH GRANT OPTION; -``` +TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; +---- One of the Payroll directors then grants these access privileges to the regional department managers: -``` +[source, sql] +---- GRANT SELECT, UPDATE (salary) ON TABLE demo.persnl.employee -TO "rgn1mgr", "rgn2mgr", "rgn3mgr"; -``` +TO "rgn1mgr", "rgn2mgr", "rgn3mgr" ; +---- Users with the EXECUTE privilege on the SPJ named TOTALPRICE, which does not access the database, are not required to have privileges on any @@ -168,74 +170,89 @@ The types of SQL statements in the underlying SPJ method, such as SELECT, UPDATE, DELETE, and INSERT, indicate which types of privileges are required for the referenced database objects. -For the syntax of the GRANT statement, see the __Trafodion SQL Reference -Manual__. +For the syntax of the GRANT statement, see the +http://trafodion.apache.org/docs/sql_reference/index.html#grant_statement[Trafodion SQL Reference Manual]. +[[revoking-execute-privileges-on-an-spj]] == Revoking Execute Privileges on an SPJ Use the REVOKE PROCEDURE or REVOKE statement to remove the EXECUTE or WITH GRANT OPTION privilege on an SPJ from specific database users or roles. In a REVOKE statement, specify ALL PRIVILEGES to revoke the EXECUTE privilege on an SPJ. For the syntax of the REVOKE PROCEDURE and -REVOKE statements, see the __Trafodion SQL Reference Manual__. +REVOKE statements, see the +http://trafodion.apache.org/docs/sql_reference/index.html#revoke_statement[Trafodion SQL Reference Manual]. -If you own the SPJ, you can revoke the EXECUTE and WITH GRANT OPTION +If you own the SPJ, then you can revoke the EXECUTE and WITH GRANT OPTION privileges on the SPJ from any database user or role to whom you granted those privileges or, if you did not directly grant those privileges, on behalf of the role that granted those privileges, provided that you were -granted that role. If you are not the owner of the SPJ, you must have -been granted the EXECUTE and WITH GRANT OPTION privileges on the SPJ to +granted that role. + +If you are not the owner of the SPJ, then you must have been granted the +EXECUTE and WITH GRANT OPTION privileges on the SPJ to revoke privileges from other database users or roles, and you can revoke the privileges only from other users or roles to whom you have granted those privileges or, if you did not directly grant those privileges, on behalf of the role that granted those privileges, provided that you were -granted that role. For example, the payrolldir1 user can revoke the -EXECUTE privilege on ADJUSTSALARY from one or more regional department -managers to whom the payrolldir1 user granted those privileges. In this -example, the payrolldir1 user revokes the EXECUTE privilege from the +granted that role. + +For example, the `payrolldir1` user can revoke the EXECUTE privilege on +ADJUSTSALARY from one or more regional department managers to whom the +`payrolldir1` user granted those privileges. + +In this example, the `payrolldir1` user revokes the EXECUTE privilege from the Region 2 department manager: -``` +[source, sql] +---- REVOKE EXECUTE -ON PROCEDURE demo.persnl.adjustsalary FROM "rgn2mgr"; -``` +ON PROCEDURE demo.persnl.adjustsalary FROM "rgn2mgr" ; +---- -The payrolldir1 user cannot revoke the EXECUTE or WITH GRANT OPTION -privilege from the payrolldir2 user because it was the SPJ owner (or +The `payrolldir1` user cannot revoke the EXECUTE or WITH GRANT OPTION +privilege from the `payrolldir2` user because it was the SPJ owner (or creator) who granted those privileges. A user can revoke the WITH GRANT OPTION privilege on ADJUSTSALARY from -any user or role to whom the user granted this privilege. In this -example, the SPJ owner (or creator) revokes the WITH GRANT OPTION -privilege from the payrolldir1 user: +any user or role to whom the user granted this privilege. -``` +In this example, the SPJ owner (or creator) revokes the WITH GRANT OPTION +privilege from the `payrolldir1` user: + +[source, sql] +---- REVOKE GRANT OPTION FOR EXECUTE -ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1"; -``` +ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1" ; +---- A user can also revoke the EXECUTE privilege from any user or role to whom the user granted this privilege and from any dependent privileges -by using the CASCADE option. In this example, the SPJ owner (or creator) -revokes the EXECUTE privilege from the payrolldir1 user and from the -regional department managers to whom the payrolldir1 user granted -privileges: +by using the CASCADE option. -``` +In this example, the SPJ owner (or creator) revokes the EXECUTE privilege +from the `payrolldir1` user and from the regional department managers to +whom the `payrolldir1` user granted privileges: + +[source, sql] +---- REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1" -CASCADE; -``` +CASCADE ; +---- For SPJs on which all users (that is, PUBLIC) have privileges, you can revoke privileges from PUBLIC but not from one or more specific users or -roles. For example, this statement revokes the EXECUTE privilege on the +roles. + +For example, this statement revokes the EXECUTE privilege on the SPJ named TOTALPRICE from all users and roles (that is, PUBLIC): -``` +[source, sql] +---- REVOKE EXECUTE ON PROCEDURE demo.sales.totalprice FROM PUBLIC; -``` +---- == Using Script Files to Grant and Revoke Privileges @@ -246,62 +263,68 @@ as needed. === Script File for Granting Privileges You can use another or the same script file to grant privileges on a -series of SPJs. For example, the script file, grantprocs.sql, contains a -series of GRANT PROCEDURE and GRANT statements: +series of SPJs. -``` +For example, the script file, `grantprocs.sql`, contains a series of +GRANT PROCEDURE and GRANT statements: + +[source, sql] +---- ?SECTION GrantSalesProcs GRANT EXECUTE ON demo.sales.monthlyorders -TO PUBLIC; +TO PUBLIC ; GRANT SELECT -ON TABLE demo.sales.orders TO PUBLIC; +ON TABLE demo.sales.orders TO PUBLIC ; ?SECTION GrantPersnlProcs GRANT EXECUTE ON PROCEDURE demo.persnl.adjustsalary TO "payrolldir1", "payrolldir2" -WITH GRANT OPTION; +WITH GRANT OPTION ; GRANT SELECT, UPDATE(salary) ON TABLE demo.persnl.employee -TO "payrolldir1", "payrolldir2" WITH GRANT OPTION; -``` +TO "payrolldir1", "payrolldir2" WITH GRANT OPTION ; +---- To grant privileges on the SPJs, run the script file in the trafci interface: ``` -obey c:\grantprocs.sql (GrantSalesProcs) +OBEY c:\grantprocs.sql (GrantSalesProcs) ``` === Script File for Revoking Privileges You can use another or the same script file to revoke privileges on a -series of SPJs. For example, the script file, revokeprocs.sql, contains +series of SPJs. + +For example, the script file, `revokeprocs.sql`, contains a series of REVOKE PROCEDURE and REVOKE statements: -``` +[source, sql] +---- ?SECTION RevokeSalesProcs REVOKE EXECUTE -ON PROCEDURE demo.sales.monthlyorders FROM PUBLIC; +ON PROCEDURE demo.sales.monthlyorders FROM PUBLIC ; REVOKE SELECT -ON TABLE demo.sales.orders FROM PUBLIC; +ON TABLE demo.sales.orders FROM PUBLIC ; ?SECTION RevokePersnlProcs REVOKE EXECUTE ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1", "payrolldir2" -CASCADE; +CASCADE ; REVOKE SELECT, UPDATE(salary) ON TABLE demo.persnl.employee -FROM "payrolldir1", "payrolldir2" CASCADE; -``` +FROM "payrolldir1", "payrolldir2" CASCADE ; +---- To revoke privileges on the SPJs, run the script file in the trafci interface: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6f5d4cc4/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 a9fbe86..4d12dd3 100644 --- a/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc +++ b/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc @@ -27,10 +27,9 @@ This chapter describes how to improve and monitor the performance of SPJs on the Trafodion platform and provides guidelines for troubleshooting common problems: -* link:#_bookmark209["Troubleshooting Common Problems"] -* link:#_bookmark212["Performance Tip"] -* link:#_bookmark214["Displaying an Execution Plan of a CALL Statement" -(page 59)] +* <<troubleshooting-common-problems, Troubleshooting Common Problems>> +* <<performance-tip, Performance Tip>> +* <<displaying-an-execution-plan-of-a-call-statement, Displaying an Execution Plan of a CALL Statement>> [[troubleshooting-common-problems]] == Troubleshooting Common Problems @@ -39,66 +38,74 @@ To resolve problems that occur when you register or execute an SPJ, follow these guidelines: * Note the SQLCODE or SQLSTATE value of the error messages and locate -the information in the __Neoview Messages Manual__, which provides -cause, effect, and recovery information for all SQL errors. +the information in the +http://trafodion.apache.org/docs/messages_guide/index.html[[Trafodion Messages Manual]], +which provides cause, effect, and recovery information for all SQL errors. + * Check that the user has the appropriate permissions to create or call the SPJ. See these sections: -** link:#_bookmark114["Required Privileges for Creating or Dropping an -SPJ" (page 37)] -** link:#_bookmark156["Required Privileges for Calling an SPJ" (page -49)] -* Check the code of the SPJ method. See link:#bookmark36[Chapter 3 (page -18)]. Fix any problems. +** <<required-privileges-for-creating-or-dropping-an-spj, Required Privileges for Creating or Dropping an SPJ>>. +** <<required-privileges-for-calling-an-spj, Required Privileges for Calling an SPJ>>. + +* Check the code of the SPJ method. See <<develop-spj-methods, Develop SPJ Methods>>. +Fix any problems. + * If you successfully compiled, deployed, and registered the SPJ but are receiving errors when calling the SPJ, check that the output parameters in the Java method are specified as arrays. See -link:#_bookmark48["Returning Output Values From the Java Method" (page -19)]. +<<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>>. + + * Verify that someone did not alter the library by selecting a JAR file 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 link:#_bookmark109["Use the Library -Browser" (page 35)]. To see the procedure's external file (class name), -external name (method name), and signature in HPDM, see -link:#_bookmark131["Using HPDM] link:#_bookmark131[to Display a -Procedure in a Schema" (page 42)]. If you notice a discrepancy, see -link:#_bookmark136["Altering] link:#_bookmark136[an SPJ and Its Java -Bytecode" (page 44)]. +(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 -link:#bookmark151[Chapter 7 (page 49)]. Fix any problems. +<<execute-spjs, Execute SPJs>>. Fix any problems. + * If the SPJ is supposed to return result sets, but the result sets are -not being returned to the calling application, check that the SPJ method -does not explicitly close a java.sql.Connection object. See -link:#_bookmark66["Use of java.sql.Connection Objects" (page 23)]. -* If a java.lang.ArrayIndexOutOfBoundsException occurs, check that the +not being returned to the calling application, then check that the SPJ method +does not explicitly close a `java.sql.Connection` object. See +<<Use-of-java-sql-Connection-Objects, Use of java.sql.Connection Objects>>. + +* If a `java.lang.ArrayIndexOutOfBoundsException` occurs, then check that the SPJ method is not trying to insert more than one array element into a -java.sql.ResultSets[] array. For more information, see -link:#_bookmark54["Returning Stored Procedure Result Sets" (page 20)]. +`java.sql.ResultSets[]` array. For more information, see +<<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>>. + * To identify Java-related errors, execute the SPJ method outside the Trafodion database by invoking the Java method directly in a Java -application that you run on a client workstation, using the HP JDBC Type -4 driver to connect to the Trafodion platform. -* If you are using JDBC tracing and logging, follow these guidelines: +application that you run on a client workstation, using the Trafodion +JDBC Type-4 driver to connect to the Trafodion platform. + +* If you are using JDBC tracing and logging, then follow these guidelines: + ** Execute the SPJ method outside the database by invoking the Java method directly from a Java application that you run on a client -workstation, using the HP JDBC Type 4 driver to connect to the Trafodion +workstation, using the Trafodion JDBC Type-4 driver to connect to the Trafodion platform. -** Verify that the file directory specified in the T4LogFile property -exists on the client workstation and that you have write access to it. - -For more information about JDBC tracing and logging, see the __Neoview -JDBC Type 4 Driver Programmer's Reference__. +** Verify that the file directory specified in the `T4LogFile` property +exists on the client workstation and that you have write access to it. [[performance-tip]] == Performance Tip @@ -106,7 +113,7 @@ JDBC Type 4 Driver Programmer's Reference__. To ensure the optimal performance of SPJs on the Trafodion platform, avoid nesting CALL statements in an SPJ method, which wastes resources and might diminish performance. For more information, see -link:#_bookmark62["Nested Java Method Invocations" (page 22)]. +<<nested-java-method-invocations, Nested Java Method Invocations>>. [[displaying-an-execution-plan-of-a-call-statement]] == Displaying an Execution Plan of a CALL Statement @@ -122,133 +129,89 @@ Suppose that you want to display the execution plan for this CALL statement: ``` -CALL demo.persnl.adjustsalary(202,5.5,?); +CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; ``` Enter this EXPLAIN statement in an trafci session: -``` -SQL>**prepare spj1 from call demo.persnl.adjustsalary(202,5.5,?);** +[source, sql] +---- +SQL> PREPARE spj1 FROM CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; ---- SQL command prepared. SQL>**explain spj1;** +--- SQL command prepared. ------------------------------------------------------------------- PLAN -SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED +SQL> EXPLAIN spj1 ; +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED STATEMENT_NAME ........... SPJ1 - PLAN_ID .................. 212206487012085509 - -ROWS_OUT 1 - -EST_TOTAL_COST 0 - -STATEMENT ................ call demo.persnl.adjustsalary(202,5.5,?) - ------------------------------------------------------------------- NODE -LISTING ROOT ====================================== SEQ_NO 2 ONLY CHILD -1 REQUESTS_IN 1 - -ROWS_OUT 1 - -EST_OPER_COST 0 - -EST_TOTAL_COST 0 - +ROWS_OUT ................. 1 +EST_TOTAL_COST ........... 0 +STATEMENT ................ CALL demo.persnl.adjustsalary( 202, 5.5, ? ) + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................. 1 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0 DESCRIPTION - -max_card_est 1 - -fragment_id 0 - -parent_frag ............ (none) - -fragment_type .......... master - -statement_index 0 - -affinity_value 3,466,211,324 - -max_max_cardinality 1 - -total_overflow_size .... 0.00 KB xn_autoabort_interval -1 - -plan_version ....... 2,500 - -LDAP_USERNAME .......... sqluser_admin NVCI_PROCESS ........... ON -SHOWCONTROL_UNEXTERNALI OFF - -SCHEMA ................. DEMO.INVENT - -CATALOG ................ NEO - -PRIORITY ............... 9 (for table -HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY ............... 9 (for -table HP_SYSTEM_CATALOG.MXCS_SCHEMA.DATASOURC - -ES) - -PRIORITY ............... 9 (for table -HP_SYSTEM_CATALOG.MXCS_SCHEMA.ENVIRONME - -NTVALUES) - -PRIORITY ............... 9 (for table -HP_SYSTEM_CATALOG.MXCS_SCHEMA.NAME2ID) PRIORITY ............... 9 (for -table HP_SYSTEM_CATALOG.MXCS_SCHEMA.RESOURCEP - -OLICIES) select_list ............ NUMERIC(8,2) SIGNED - -input_variables ........ ? - -CALL ====================================== SEQ_NO 1 NO CHILDREN + max_card_est ........... 1 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 3,466,211,324 + max_max_cardinality .... 1 + total_overflow_size .... 0.00 KB xn_autoabort_interval -1 + plan_version ........... 2,500 + LDAP_USERNAME .......... sqluser_admin + NVCI_PROCESS ........... ON + SHOWCONTROL_UNEXTERNALI OFF + SCHEMA ................. DEMO.INVENT + CATALOG ................ NEO + 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) + PRIORITY ............... 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.NAME2ID) + PRIORITY ............... 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.RESOURCEPOLICIES) + select_list ............ NUMERIC(8,2) SIGNED + input_variables ........ ? + +CALL ====================================== SEQ_NO 1 NO CHILDREN TABLE_NAME ............... DEMO.PERSNL.ADJUSTSALARY - -REQUESTS_IN 1 - -ROWS_OUT 1 - -EST_OPER_COST 0 - -EST_TOTAL_COST 0 - +REQUESTS_IN .............. 1 +ROWS_OUT ................. 1 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0 DESCRIPTION - -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 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 - ---- SQL operation complete. SQL> -``` + 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 + 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 + +--- SQL operation complete. + +SQL> +---- The EXPLAIN statement generates and displays all the columns of the result table of the EXPLAIN function. For the syntax of the EXPLAIN -statement, see the __Trafodion SQL Reference Manual__. For more -information about optimizing query execution plans, see the __Trafodion -Query Guide__. +statement, see the +http://trafodion.apache.org/docs/sql_reference/index.html#explain_statement[Trafodion SQL Reference Manual]. [[using-the-explain-function]] === Using the EXPLAIN Function @@ -256,161 +219,66 @@ Query Guide__. You can also prepare the CALL statement and select specific columns from the result table of the EXPLAIN function, as shown below: -``` -SQL>**prepare spj1 from call demo.persnl.adjustsalary(202,5.5,?);** +[source, sql] +---- +SQL> PREPARE spj1 FROM CALL demo.persnl.adjustsalary( 202, 5.5, ? ) ; --- SQL command prepared. -SQL>**select substring(operator,1,8) as "OPERATOR", operator_cost,** - -+>**substring(description,1,500) as "DESCRIPTION"** - -+>**from table (explain(null, 'SPJ1'));** +SQL> SELECT SUBSTRING( operator, 1, 8 ) AS "OPERATOR", operator_cost, ++> SUBSTRING( description, 1, 500 ) AS "DESCRIPTION" ++> FROM TABLE ( EXPLAIN( NULL, 'SPJ1' ) ) ; 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 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 -HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY: 9 (for table -HP_SYSTEM_CATALOG.MXCS_SCHEMA.D +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 +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 +SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY: 9 (for table SYSTEM_CATALOG.MXCS_SCHEMA.D --- 2 row(s) selected. SQL> -``` +---- For a CALL statement, the OPERATOR column of the result table contains a row named CALL. The DESCRIPTION column contains special token pairs for the CALL operator. For descriptions of the token pairs, see this table: -Token - -Token Description - -Data Type - -max_card_est - -The upper limit for the operator cardinality in the query tree. - -integer - -fragment_id - -A sequential number assigned to the fragment. 0 is always the master -executor, and 1 is reserved for the Explain plan. Numbers 2 to _n_ will -be ESP or TSE fragments. - -integer - -parent_frag - -The fragment_id for the parent fragment of the current fragment. The -value is (none) for the master executor. - -integer - -fragment_type - -Type of fragment, which can be either master, ESP, or TSE. - -text - -routine_name - -ANSI name of the procedure. - -text - -parameter_modes - -A sequence of characters that specifies SQL parameter modes for the +.Token Pairs Description +[cols="30%,60%,10%", options="header"] +|=== +| Token | Token Description | Data Type +| `max_card_est` | The upper limit for the operator cardinality in the query tree. | integer +| `fragment_id` | A sequential number assigned to the fragment. 0 is always the master +executor, and 1 is reserved for the Explain plan. Numbers 2 to _n_ are ESP or storage-engine fragments. | integer +| `parent_frag` | The fragment_id for the parent fragment of the current fragment. The +value is (none) for the master executor. | integer +| `fragment_type` | Type of fragment, which can be either master, ESP, or storage engine. | text +| `routine_name` | ANSI name of the procedure. | text +| `parameter_modes` | A sequence of characters that specifies SQL parameter modes for the procedure. I is used for an IN parameter, O for an OUT parameter, and N for an INOUT parameter. Characters are separated by a single space. The -value none is returned if the procedure has no SQL parameters. - -text - -sql_access_mode - -SQL access mode of the procedure. - -text - -external_name - -Java method name. - -text - -library - -ANSI name of the library object that maps to the procedure's JAR file. - -text - -external_file - -Java class name, possibly prefixed by a package name, that contains the -SPJ method. - -text - -signature - -Java signature of the SPJ method in internal Java Virtual Machine (JVM) -format. - -text - -language - -Language in which the SPJ method is written, which is always Java. - -text - -parameter_style - -Convention of passing parameter arguments to the stored procedure, which -conforms to the Java language for SPJs. - -text - -external_security - -External security of the stored procedure, indicating the privileges or -rights that users have when executing (or calling) the procedure. The -value is either INVOKER or DEFINER. For more information, see -link:#_bookmark124["Understand External Security" (page 41)]. - -text - -max_result_sets - -The maximum number of result sets that this procedure can return. - -integer - -parameters - -The parameter arguments that are passed to or from the procedure. - -text - -For the syntax of the EXPLAIN function, see the __Trafodion SQL Reference -Manual__. +value none is returned if the procedure has no SQL parameters. | text +| `sql_access_mode` | SQL access mode of the procedure. ` | text +| `external_name` | Java method name. | text +| `library` | ANSI name of the library object that maps to the procedure's JAR file. | text +| `external_file` | Java class name, possibly prefixed by a package name, that contains the SPJ method. | text +| `signature` | Java signature of the SPJ method in internal Java Virtual Machine (JVM) format. | text +| `language` | Language in which the SPJ method is written, which is always Java. | text +| `parameter_style` | Convention of passing parameter arguments to the stored procedure, which +conforms to the Java language for SPJs. | text +| `external_security` | External security of the stored procedure, indicating the privileges or +rights that users have when executing (or calling) the procedure. The value is either INVOKER or +DEFINER. For more information, see <<understand-external-security, Understand External Security>>. | text +| `max_result_sets` | The maximum number of result sets that this procedure can return. | integer +| `parameters` | The parameter arguments that are passed to or from the procedure. | text +|=== + +For the syntax of the EXPLAIN function, see the +http://trafodion.apache.org/docs/sql_reference/index.html#explain_statement[Trafodion SQL Reference Manual].
