trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dbirds...@apache.org
Subject [42/50] [abbrv] incubator-trafodion git commit: update
Date Thu, 31 Mar 2016 22:16:53 GMT
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/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
new file mode 100644
index 0000000..6e9cba9
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/_chapters/develop_spjs.adoc
@@ -0,0 +1,899 @@
+////
+/**
+ *@@@ START COPYRIGHT @@@
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * @@@ END COPYRIGHT @@@
+ */
+////
+
+[[develop-spj-methods]]
+= Develop SPJ Methods
+
+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>>
+
+[[guidelines-for-writing-spj-methods]]
+== Guidelines for Writing SPJ Methods
+
+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
+
+A Java method that you use as an SPJ must have this general signature:
+
+[source, java]
+----
+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
+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
+without having to instantiate its class.
+
+[[void-return-type]]
+==== Void Return Type
+
+The return type of the Java method must be `void`. The method must not
+return a value directly to the caller.
+
+[[java-parameters]]
+==== Java Parameters
+
+Except for result sets, which are described in
+<<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>>,
+the parameter types in the Java signature must correspond to the SQL
+parameters of the stored procedure that you are planning to create. For
+type mappings, see the table below.
+
+[[table-1]]
+.Mapping of Java Data Types to SQL Data Types
+
+[cols="40%,60%",options="header",]
+|===
+| Java Data Type | Maps to SQL Data Type. . .
+| `java.lang.String`             |
+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
+|===
+
+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>>
+
+Output parameters in the Java signature must be arrays (for example,
+`int[]` or `String[]`) that accept only one value in the first element of
+the array at index 0. For more information, see
+<<returning-output-values-from-the-java-method, Returning Output Values From the Java Method>>.
+
+[[returning-output-values-from-the-java-method]]
+=== Returning Output Values From the Java Method
+
+The Java method can return data to the calling application in the form
+of output parameters or result sets.
+
+_Output parameters_ in the Java signature are parameter arrays that
+accept only one value in the first element of the array at index 0. The
+array objects have a fixed size of one element.
+
+IMPORTANT: You cannot return more than one value to an output parameter of an SPJ.
+Any attempt to return more than one value to an output parameter results in a Java exception,
+`ArrayIndexOutOfBounds`.
+
+_Result sets_ in the Java signature are one-element `java.sql.ResultSet[]`
+arrays that contain ResultSet objects that have multiple rows of data.
+For more information about result sets, see
+<<returning-stored-procedure-result-sets, Returning Stored Procedure Result Sets>>
+
+This subsection covers these topics related to output parameters:
+
+* <<using-arrays-for-output-parameters, Using Arrays for Output Parameters>>
+* <<type-mapping-of-output-parameters, Type Mapping of Output Parameters>>
+
+[[using-arrays-for-output-parameters]]
+==== Using Arrays for Output Parameters
+
+You must use arrays for the output parameters of a Java method because
+of how Java handles the arguments of a method. Java supports arguments
+that are passed by value to a method and does not support arguments that
+are passed by reference. As a result, Java primitive types can be passed
+only to a method, not out of a method. Because a Java array is an
+object, its reference is passed by value to a method, and changes to the
+array are visible to the caller of the method. Therefore, arrays must be
+used for output parameters in a Java method.
+
+IMPORTANT: An output parameter accepts only one value in the first element
+of the array at index 0. Any attempt to return more than one value to an
+output parameter results in a Java exception, `ArrayIndexOutOfBounds`.
+
+For each output parameter, specify the Java type followed by empty
+square brackets (`[]`) to indicate that the type is an array. For example,
+specify an int type as `int[]` for an output parameter in the Java
+signature.
+
+To return multiple values from a Java method, use an output parameter
+for each returned value. For example, the `supplierInfo()` method returns
+a supplier's name, address, city, state, and post code, each as a single
+string in an output parameter:
+
+The `supplyQuantities()` method returns an average quantity, a minimum
+quantity, and a maximum quantity to separate output parameters of the
+integer type:
+
+[source, java]
+----
+public static void supplyQuantities( int[] avgQty
+                                   , int[] minQty
+                                   , int[] maxQty
+                                   )
+{
+
+...
+
+throws SQLException
+----
+
+For more information about the SPJ examples, see
+<<sample-spjs, Appendix A: Sample SPJs>>.
+
+[[type-mapping-of-output-parameters]]
+==== Type Mapping of Output Parameters
+
+When writing an SPJ method, consider how the output of the SPJ is
+used in the calling application. For output parameters, the Java data
+type of the SPJ method must map to an SQL data type. See
+<<table-1, Table 1>>.
+
+The SQL data type must then map to a compatible data type in the calling
+application. For the client application programming interfaces (APIs) that
+support SPJs and for cross-references to the appropriate manuals for type
+mappings between Trafodion SQL and each API, see
+<<execute-spjs, Execute SPJs>> below.
+
+[[returning-stored-procedure-result-sets]]
+=== Returning Stored Procedure Result Sets
+
+The Trafodion database engine supports SPJs that return stored procedure
+result sets. A stored procedure result set is a cursor that is left open
+after the SPJ method executes (that is, after the CALL statement
+executes successfully). After the CALL statement executes successfully,
+the calling application can issue requests to open and then retrieve
+multiple rows of data from the returned result sets.
+
+An SPJ method returns an ordered collection of result sets to the
+calling application by executing SELECT statements and placing each
+returned ResultSet object into a one-element Java array of type
+`java.sql.ResultSet[]`. The `java.sql.ResultSet[]` array is part of the Java
+method's signature and is recognized by the database engine as a
+container for a single stored procedure result set.
+
+Place the `java.sql.ResultSet[]` parameters after the other Java
+parameters, if any, in the Java signature. If you do not place the
+`java.sql.ResultSet[]` parameters after the other
+parameters in the signature, the database engine prevents you from
+creating an SPJ using that Java method. This example shows the
+declaration of an SPJ method, `orderSummary()`, which returns a maximum of
+two result sets:
+
+[source, java]
+----
+public static void orderSummary( java.lang.String onOrAfter
+                               , long[] numOrders
+                               , java.sql.ResultSet[] orders
+                               , java.sql.ResultSet[] detail
+                               )
+----
+
+This code fragment shows how the `orderSummary()` method returns one of
+its result sets by executing a SELECT statement and assigning the
+`java.sql.ResultSet` object to a `java.sql.ResultSet[]` output array:
+
+[source,java]
+----
+// Open a result set for order num, order info rows
+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 "
+   + "       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 "
+   + "WHERE amounts.ordernum = orders.ordernum "
+   + "  AND orders.salesrep = emps.empnum "
+   + "ORDER BY orders.ordernum "
+   ;
+
+java.sql.PreparedStatement ps2 = conn.prepareStatement(s) ;
+ps2.setString( 1, onOrAfter ) ;
+
+// Assign the returned result set object to the first element of a
+// java.sql.ResultSet[] output array
+orders[0] = ps2.executeQuery() ;
+----
+
+For the entire example, see 
+<<ordersummary-procedure, ORDERSUMMARY Procedure>>.
+
+IMPORTANT: In an SPJ method that returns result sets, do not explicitly close
+the default connection or the statement object. The database engine closes the
+connection used to return result sets after it finishes processing the result
+sets. If you close the connection on which the result sets are being returned,
+those result sets will be lost, and the calling application will not be able
+to process them.
+
+An SPJ method can return result sets that contain any data types, except
+large object (LOB) data. An SPJ method can return a holdable or
+updatable cursor as a result set. However, Trafodion SQL does not expose
+those attributes in the calling application. An SPJ method can return a
+ResultSet object that is a stored procedure result set acquired from a
+nested CALL statement executed by the SPJ method. However, you are
+discouraged from nesting CALL statements in SPJ methods. For more
+information, see <<nested-java-method-invocations, Nested Java Method Invocations>>.
+
+If an SPJ method returns multiple ResultSet objects, the database engine
+sorts the collection of valid result sets in chronological order
+according to when the underlying SQL statements were executed. If the
+number of result sets exceeds the declared maximum for the SPJ, only the
+first set of result sets up to the maximum number are returned. The
+database engine discards the other result sets and returns a warning to
+the calling application.
+
+When an SPJ method returns a ResultSet object through a
+`java.sql.ResultSet[]` parameter, Trafodion SQL exposes the underlying rows
+of data as an SQL cursor in the calling application.
+
+If a returned result set is a scrollable cursor, all underlying rows are
+included in the result set and are available to the calling application.
+If a returned result set is not scrollable, only those rows not
+processed by the SPJ method are included in the result set and are
+available to the calling application. If an SPJ method returns multiple
+occurrences of the same ResultSet object, the database engine ignores
+all but one occurrence and makes the underlying rows available to the
+calling application as a single result set.
+
+For information about processing result sets in different calling
+applications, see:
+
+* <<returning-result-sets-in-trafci, Returning Result Sets in trafci>>
+* <<returning-result-sets-in-an-odbc-client-application, Returning Result Sets in an ODBC Client Application>>
+* <<returning-result-sets-in-a-jdbc-client-application, Returning Result Sets in a JDBC Client Application>>
+
+[[using-the-main-method]]
+=== Using the main() Method
+
+You can use the `main()` method of a Java class file as an SPJ method. The
+`main()` method is different from other Java methods because it accepts
+input values in an array of `java.lang.String` objects and does not return
+any values in its array parameter.
+
+For example, you can register this main() method as an SPJ:
+
+[source,java]
+----
+public static void main (java.lang.String [] args)
+{
+
+...
+
+}
+----
+
+When you register a `main()` method as an SPJ, you can specify zero or
+more SQL parameters, even though the underlying `main()` method has only
+one array parameter. All the SQL parameters of the SPJ must have the
+character string data type, CHAR or VARCHAR, and be declared with the IN
+mode.
+
+If you specify the optional Java signature, the signature must be
+(`java.lang.String []`). For more information about registering an SPJ,
+see <<create-spjs, Create SPJs>>.
+
+[[null-input-and-output]]
+=== Null Input and Output
+
+You can pass a `null` value as input to or output from an SPJ method,
+provided that the Java data type of the parameter supports nulls. Java
+primitive data types do not support nulls. However, Java wrapper classes
+that correspond to primitive data types do support nulls. If a null is
+input or output for a parameter that does not support nulls, the
+database engine raises an error condition.
+
+To anticipate null input or output for your SPJ, use Java wrapper
+classes instead of primitive data types in the method signature.
+
+For example, this Java method uses a Java primitive data type in its
+signature where no null values are expected:
+
+[source, java]
+----
+public static void employeeJob( int empNum, Integer[] jobCode )
+----
+
+This Java method also uses a Java wrapper class in its signature to
+anticipate a possible returned null value:
+
+[source, java]
+----
+public static void employeeJob( int empNum, Integer[] jobCode )
+----
+
+[[static-java-variables]]
+=== Static Java Variables
+
+To ensure that your SPJ method is portable, you should avoid using
+static variables in the method. The database engine does not ensure the
+scope and persistence of static Java variables.
+
+[[nested-java-method-invocations]]
+=== Nested Java Method Invocations
+
+An SPJ that invokes another SPJ by issuing a CALL statement causes
+additional system resources to be used. If you want an SPJ method to
+call another SPJ method, consider invoking the other Java method
+directly through Java instead of using a CALL statement. The other Java
+method should be packaged in the same JAR file as the SPJ method. For
+more information, see
+<<compiling-and-packaging-java-classes, Compiling and Packaging Java Classes>>.
+
+[[accessing-a-trafodion-database]]
+== Accessing a Trafodion Database
+
+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
+
+The Trafodion database engine supports a default connection in an SPJ
+execution environment, which has a data source URL of
+`"jdbc:default:connection"`. For example:
+
+[source, java]
+----
+Connection conn =
+   DriverManager.getConnection( "jdbc:default:connection" ) ;
+----
+
+`java.sql.Connection` objects that use the `"jdbc:default:connection"` URL
+are portable to the Trafodion platform from other database management
+systems (DBMSs).
+
+[[closing-default-connections]]
+==== Closing Default Connections
+
+The Trafodion database engine controls default connections in the SPJ
+environment and closes default connections when they are no longer
+needed. Therefore, you do not need to use the `close()` method in an SPJ
+method to explicitly close a default connection when the connection is
+no longer needed.
+
+IMPORTANT: If an SPJ method returns result sets, you should not explicitly
+close the default connection. The database engine closes the connection used
+to return result sets after it finishes processing the result sets. If an SPJ
+method closes the connection on which the result sets are being returned, those
+result sets will be lost, and the calling application will not be able to
+process them. The JVM does not return an error or warning when the
+connection is closed.
+
+A default connection that is acquired when an SPJ method executes does
+not necessarily remain open for future invocations of the SPJ method.
+Therefore, do not store default connections in static variables for
+future use.
+
+[[default-connection-url]]
+==== Default Connection URL
+
+The default connection URL, `"jdbc:default:connection"`, is invalid when
+the Java method is invoked outside the DBMS, such as when you execute
+the Java method in a client application. To write an SPJ method that
+operates in a DBMS, in a client application, or both, without having to
+change and recompile the code, use the `sqlj.defaultconnection` system
+property:
+
+[source, java]
+----
+String s = System.property( "sqlj.defaultconnection" ) ;
+if ( s == null )
+{
+   s = other-url ;
+}
+
+Connection c = DriverManager.getConnection( s ) ;
+----
+
+The value of `sqlj.defaultconnection` is `"jdbc:default:connection"` in a
+DBMS and `null` outside a DBMS.
+
+[[connection-pooling]]
+==== Connection Pooling
+
+Connection pooling, where a cache of database connections is assigned to
+a client session and reused, is enabled by default in the SPJ
+environment. The SPJ environment sets the initial connection pool size
+to `1`, but it does not limit the number of connections an SPJ method can
+make.
+
+The SPJ environment also sets the minimum connection pool size to 1 so that
+there is always at least one connection available in the pool. The
+default settings in the SPJ environment are:
+
+* `maxPoolSize=0`
+* `minPoolSize=1`
+* `initialPoolSize=1`
+
+To change these settings, use the properties parameter of the
+`DriverManager.getConnection()` method as shown below:
+
+[source, java]
+----
+java.util.Properties props = new Properties() ;
+
+props.setProperty( "maxPoolSize", "10" ) ;
+props.setProperty( "minPoolSize", "5" ) ;
+props.setProperty( "initialPoolSize", "5" ) ;
+
+Connection conn =
+   DriverManager.getConnection( "jdbc:default:connection", props ) ;
+----
+
+[[using-jdbc-method-calls]]
+=== Using JDBC Method Calls
+
+The Trafodion platform uses a JDBC Type-4 driver internally to execute
+the SQL statements inside an SPJ method. To enable an SPJ to perform SQL
+operations on a Trafodion database, use JDBC method calls in the SPJ
+method. The JDBC method calls must be supported by the JDBC Type-4
+driver on the Trafodion platform.
+
+For example, if you want the SPJ method to operate on a Trafodion database,
+use the JDBC API that is supported by Trafodion.
+
+NOTE: You do not have to explicitly load the JDBC driver before
+establishing a connection to the Trafodion database. The database engine
+automatically loads the JDBC driver when the SPJ is called.
+
+Here is an example of an SPJ method, `adjustSalary()`, that uses JDBC
+method calls to adjust an employee's salary in the EMPLOYEE table:
+
+[source, java]
+----
+public class Payroll
+{
+   public static void adjustSalary( BigDecimal empNum
+                                  , double percent
+                                  , BigDecimal[] newSalary
+                                  ) throws SQLException
+   {
+      Connection conn =
+         DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+      PreparedStatement setSalary =
+         conn.prepareStatement( "UPDATE demo.persnl.employee "
+                              + "SET salary = salary * (1 + (? / 100)) "
+                              + "WHERE empnum = ?"
+                              ) ;
+ 
+      PreparedStatement getSalary =
+         conn.prepareStatement( "SELECT salary "
+                              + "FROM demo.persnl.employee "
+                              + "WHERE empnum = ?"
+                              ) ;
+
+      setSalary.setDouble( 1, percent ) ;
+      setSalary.setBigDecimal( 2, empNum ) ;
+      setSalary.executeUpdate() ;
+
+      getSalary.setBigDecimal( 1, empNum ) ;
+      ResultSet rs = getSalary.executeQuery() ;
+      rs.next() ;
+
+      newSalary[0] = rs.getBigDecimal( 1 ) ;
+
+      rs.close();
+      conn.close();
+   }
+}
+----
+
+For other examples of SPJ methods, see <<sample-spjs, Appendix A: Sample SPJs>>.
+
+[[referring-to-database-objects-in-an-spj-method]]
+=== Referring to Database Objects in an SPJ Method
+
+In an SPJ method, you can refer to SQL database objects by specifying
+three-part ANSI names that include the catalog, schema, and object name.
+For more information about database object names, see the
+http://trafodion.incubator.apache.org/docs/sql_reference/index.hmtl[Trafodion SQL Reference Manual].
+
+The database engine propagates the names of the catalog and schema where
+the SPJ is registered to the SPJ environment. By default, database
+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
+database object, ORDERS:
+
+[source, java]
+----
+public static void numDailyOrders( Date date
+                                 , int[] numOrders
+				 ) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   PreparedStatement getNumOrders =
+      conn.prepareStatement( "SELECT COUNT( order_date ) "
+                           + "FROM orders "
+			   + "WHERE order_date = ?"
+			   ) ;
+
+   getNumOrders.setDate( 1, date ) ;
+
+   ResultSet rs = getNumOrders.executeQuery() ;
+   rs.next() ;
+
+   numOrders[0] = rs.getInt( 1 ) ;
+
+   rs.close() ;
+   conn.close() ;
+
+}
+----
+
+In the SPJ environment, the ORDERS table is qualified by default with
+the same catalog and schema as the SPJ, DEMO.SALES.
+
+The default behavior takes effect only when `getConnection()` does not
+contain catalog and schema properties. Catalog and schema property
+values in `getConnection()` have higher precedence over the default
+behavior. To override the default schema name and associate a database
+connection with a different schema, specify the schema property during
+connection creation. For example, `getConnection()` in this SPJ method
+specifies the schema, SALES2, which overrides the default schema, SALES:
+
+[source, java]
+----
+public static void numDailyOrders( Date date
+                                 , int[] numOrders
+				 ) throws SQLException
+{
+   Properties prop = new Properties() ;
+   prop.setProperty( "schema", "SALES2" ) ;
+
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection", prop) ;
+
+   PreparedStatement getNumOrders =
+      conn.prepareStatement( "SELECT COUNT( order_date ) "
+                           + "FROM orders "
+			   + "WHERE order_date = ?"
+			   ) ;
+
+   getNumOrders.setDate( 1, date ) ;
+
+   ResultSet rs = getNumOrders.executeQuery() ;
+   rs.next() ;
+
+   numOrders[0] = rs.getInt( 1 ) ;
+
+   rs.close() ;
+   conn.close() ;
+
+}
+----
+
+Be aware that overriding the default values by using getConnection()
+requires you to hard-code the catalog or schema name and might make SPJ
+methods less portable across systems.
+
+[[using-the-session_user-or-current_user-function-in-an-spj-method]]
+=== Using the SESSION_USER or CURRENT_USER Function in an SPJ Method
+
+SESSION_USER is an SQL function that returns the name of the
+authenticated database user who started the session and invoked the
+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.
+
+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
+invoke the function:
+
+[source, java]
+----
+public static void getUser( ResultSet [] rs ) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   Statement stmt = conn.createStatement() ;
+
+   rs[0] =
+      stmt.executeQuery( "SELECT CURRENT_USER FROM (VALUES(1)) X(A) ; " ) ;
+}
+----
+
+If this method is used in a stored procedure with external security
+defined as _invoker_, the CURRENT_USER function returns the name of
+the database user who is authorized to invoke the function, which
+happens to be the authenticated database user who started the session
+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
+EXECUTE privilege on the stored procedure, calls GETINVOKER, the procedure
+returns his name:
+
+```
+Welcome to Apache Trafodion Command Interface
+Copyright (c) 2015 Apache Software Foundation
+
+User Name:PAULLOW1 Password:
+
+Connected to Data Source: TDM_Default_DataSource
+
+SQL> CALL demo.persnl.getinvoker() ;
+
+(EXPR)
+--------------------------------------------------------------------------------
+PAULLOW1
+
+--- 1 row(s) selected.
+
+--- SQL operation complete.
+```
+
+If the method is used in a stored procedure with external security
+defined as _definer_, the CURRENT_USER function returns the name of
+the database user who is authorized to invoke the function, which
+happens to be the user who created the stored procedure (that is, the
+definer of the stored procedure). When a stored procedure's external
+security is set to definer, any user who has the execute privilege on
+the stored procedure can call the procedure using the privileges of the
+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
+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:
+
+```
+SQL> SHOW USER
+
+USER PAULLOW1 (NONE)
+
+SQL> CALL demo.persnl.getdefiner() ;
+
+(EXPR)
+--------------------------------------------------------------------------------
+DB USERADMINUSER
+
+--- 1 row(s) selected.
+
+--- SQL operation complete.
+```
+
+Suppose that you write this Java method, which uses the SESSION_USER
+function to return the name of the authenticated database user who
+started the session and invoked the function:
+
+[source, java]
+----
+public static void getSessionUser( ResultSet [] rs ) throws SQLException
+{
+   Connection conn =
+      DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+   Statement stmt = conn.createStatement() ;
+
+   rs[0] = stmt.executeQuery( "SELECT SESSION_USER FROM (VALUES(1) ) X(A) ; " ) ;
+}
+----
+
+The SESSION_USER function returns the name of the authenticated database
+user who started the session and invoked the function, regardless of the
+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
+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:
+
+```
+SQL> SHOW USER
+
+USER PAULLOW1 (NONE)
+
+SQL> CALL demo.persnl.getsessionuser() ;
+
+(EXPR)
+--------------------------------------------------------------------------------
+PAULLOW1
+
+--- 1 row(s) selected.
+
+--- SQL operation complete.
+```
+
+For more information about external security, see
+<<understand-external-security, Understand External Security>>.
+
+[[exception-handling]]
+=== Exception Handling
+
+For SPJ methods that access a Trafodion database, no special code is
+necessary for handling exceptions. If an SQL operation fails inside an
+SPJ, the error message associated with the failure is returned to the
+application that issues the CALL statement.
+
+[[handling-java-exceptions]]
+== Handling Java Exceptions
+
+If an SPJ method returns an uncaught Java exception or an uncaught chain
+of `java.sql.SQLException` objects, the database engine converts each Java
+exception object into an SQL error condition, and the CALL statement
+fails. Each SQL error condition contains the message text associated
+with one Java exception object.
+
+If an SPJ method catches and handles exceptions itself, those exceptions
+do not affect SQL processing.
+
+[[user-defined-exceptions]]
+=== User-Defined Exceptions
+
+The SQLSTATE values 38001 to 38999 are reserved for you to define your
+own error conditions that SPJ methods can return. By coding your SPJ
+method to throw a `java.sql.SQLException` object, you cause the CALL
+statement to fail with a specific user-defined SQLSTATE value and your
+own error message text.
+
+If you define the SQLSTATE to be outside the range of 38001 to 38999,
+the database engine raises SQLSTATE 39001, external routine invocation
+exception.
+
+This example uses the throw statement in the SPJ method named
+`numMonthlyOrders()` to raise a user-defined error condition when an
+invalid argument value is entered for the month:
+
+[source, java]
+----
+public static void numMonthlyOrders( int month
+                                   , int[] numOrders
+				   ) throws java.sql.SQLException
+{
+   if ( month < 1 || month > 12 )
+   {
+      throw new
+         java.sql.SQLException ( "Invalid value for month. "
+                               + "Retry the CALL statement using a number "
+                               + "from 1 to 12 to represent the month."
+			       , "38001"
+			       ) ;
+   }
+
+   ....
+}
+----
+
+For more information about the numMonthlyOrders() method, see the
+<<monthlyorders-procedure, MONTHLYORDERS Procedure>>.
+
+For information about specific SQL errors, see the
+http://trafodion.incubator.apache.org/docs/messages_guide/index.html[Trafodion Messages Manual], which lists
+the SQLCODE, SQLSTATE, message text, and cause-effect-recovery information for all SQL errors.
+
+[[compiling-and-packaging-java-classes]]
+== Compiling and Packaging Java Classes
+
+On the Trafodion database, the class files of SPJ methods must be
+packaged in Java archive (JAR) files. After writing an SPJ method,
+compile the Java source file of the SPJ method into Java bytecode and
+package the Java bytecode in a JAR file. A Java method that you register
+as an SPJ might need to access, either directly or indirectly, other
+Java classes to operate properly. Those Java classes might include other
+application classes. To enable an SPJ method to refer to other
+application classes, put the application classes in the same JAR file as
+the SPJ class. All classes stored in the same JAR file as the SPJ class
+are accessible by default to the SPJ method.
+
+.After writing the SPJ method
+
+1.  Compile the Java source file into Java bytecode by using the Java
+programming language compiler, `javac`:
++
+```
+javac Payroll.java
+```
+
+2.  Put the SPJ class file and all associated class files into a Java
+archive (JAR) file:
++
+```
+jar cvf Payroll.jar Payroll.class
+```
++
+A manifest file is not needed for the JAR file.
+
+

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/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
new file mode 100644
index 0000000..a2557c9
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/_chapters/execute_spjs.adoc
@@ -0,0 +1,730 @@
+////
+/**
+ *@@@ START COPYRIGHT @@@
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * @@@ END COPYRIGHT @@@
+ */
+////
+
+[[execute-spjs]]
+= Execute SPJs
+
+This chapter describes how to execute SPJs by using the CALL statement
+and assumes that you have already registered the SPJs in the Trafodion
+database and that you have granted privileges to execute the SPJs to the
+appropriate database users. For information, see
+<<deploy-spj-jar-files, Deploy SPJ JAR Files>>
+and <<create-spjs, Create SPJs>>.
+
+This chapter covers these topics:
+
+* <<required-privileges-for-calling-an-spj, Required Privileges for Calling an SPJ>>
+* <<transaction-behavior, Transaction Behavior>>
+* <<multithreading, Multithreading>>
+* <<using-the-call-statement, Using the CALL Statement>>
+* <<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>>
+* <<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
+Trafodion platform:
+
+* trafci command-line interface or script file
+* JDBC Type-4 client applications
+* ODBC client applications
+
+You can use a CALL statement as a stand-alone SQL statement in
+applications or in command-line interfaces, such as trafci. You can also
+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
+
+To execute the CALL statement, you must have the EXECUTE privilege on
+the procedure. For more information, see
+<<grant-privileges, Grant Privileges>>.
+
+== Transaction Behavior
+
+The stored procedure's transaction attribute determines whether it
+inherits the transaction from the calling application (TRANSACTION
+REQUIRED) or whether it runs without inheriting the calling
+application's transaction (NO TRANSACTION REQUIRED). The transaction
+attribute is set during the creation of the stored procedure. For more
+information, see the Transaction Required attribute in
+<<create-a-procedure, Create a Procedure>>.
+
+Typically, you want the stored procedure to inherit the transaction
+from the calling application. See <<transaction-required, Transaction Required>>.
+However, if the SPJ method does not access the database or if you want the
+stored procedure to manage its own transactions, you should set the stored
+procedure's transaction attribute to NO TRANSACTION REQUIRED.
+See <<no-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
+transaction attribute is TRANSACTION REQUIRED, a CALL statement
+automatically initiates a transaction if there is no active transaction.
+
+==== 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
+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
+application that calls the stored procedure and allow the calling
+application to manage the transactions.
+
+===== Committing or Rolling Back a Transaction
+
+If you do not use transaction control statements in the calling
+application, the transaction initiated by the CALL statement might not
+automatically commit or roll back changes to the database. When
+AUTOCOMMIT is ON (the default setting), the database engine
+automatically commits or rolls back any changes made to the database at
+the end of the CALL statement execution. However, when AUTOCOMMIT is
+OFF, the current transaction remains active until the end of the client
+session or until you explicitly commit or roll back the transaction.
+
+To ensure an atomic unit of work when calling an SPJ, use the COMMIT
+WORK statement in the calling application to commit the transaction when
+the CALL statement succeeds, and use the ROLLBACK WORK statement to roll
+back the transaction when the CALL statement fails. For more information
+about transaction management, see the __Trafodion SQL Reference Manual__.
+
+=== No Transaction Required
+
+In some cases, you might not want the SPJ method to inherit the
+transaction from the calling application. Instead, you might want the
+stored procedure to manage its own transactions or to run without a
+transaction. Not inheriting the calling application's transaction is
+useful in these cases:
+
+* The stored procedure performs several long-running operations, such as
+multiple DDL or table maintenance operations, on the database. In this
+case, you might want to commit those operations periodically from within
+the SPJ method to avoid locking tables for a long time.
+* The stored procedure performs certain SQL operations that must run
+without an active transaction. For example, INSERT, UPDATE, and DELETE
+statements with the WITH NO ROLLBACK option are rejected when a
+transaction is already active, as is the case when a stored procedure
+inherits a transaction from the calling application. The PURGEDATA
+utility is also rejected when a transaction is already active.
+* The stored procedure does not access the database. (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.
+
+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)].
+
+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
+can either use the autocommit transactions or disable autocommit
+(conn.setAutoCommit(false);) and use the JDBC transaction methods,
+Connection.commit() and Connection.rollback(), to commit or roll back
+work where needed.
+
+== 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
+given SPJ environment.
+
+== 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]:
+
+image:media/image8.png[image]Figure 2 CALL Statement Elements
+
+For the syntax of the CALL statement, see the __Trafodion SQL Reference
+Manual__.
+
+=== Specifying the Name of the SPJ
+
+In the CALL statement, specify the name of an SPJ that you have already
+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, ?);
+```
+
+Or, for example:
+
+```
+SET SCHEMA demo.persnl;
+
+CALL adjustsalary(202, 5.5, ?);
+```
+
+If you do not fully qualify the procedure name, the database engine
+qualifies the procedure according to the catalog and schema of the
+current session.
+
+=== Listing the Parameter Arguments of the SPJ
+
+Each argument that you list in the CALL statement must correspond to an
+SQL parameter of the SPJ. A result set in the Java signature of the SPJ
+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:
+
+```
+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();
+```
+
+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
+argument list:
+
+```
+CALL demo.sales.ordersummary('01-01-2011', ?);
+```
+
+==== 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.
+
+==== 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)].
+
+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__.
+
+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
+
+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)].
+
+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 ?).
+
+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)]
+
+==== 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
+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:
+
+* 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)]
+
+== Calling SPJs in trafci
+
+In trafci, you can invoke an SPJ by issuing a CALL statement directly or
+by preparing and executing a CALL statement.
+
+Use named or unnamed parameters anywhere in the argument list of an SPJ
+invoked in trafci. A named parameter is set by the SET PARAM command, and
+an unnamed parameter is set by the USING clause of the EXECUTE
+statement.
+
+You must use a parameter for an OUT or INOUT parameter argument. trafci
+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__.
+
+=== 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)].
+
+Set the input value for the INOUT parameter by entering a SET PARAM
+command before calling the SPJ:
+
+```
+SQL>**set param ?p 10;**
+
+SQL>**call demo.sales.totalprice(23, 'standard', ?p);**
+```
+
+The CALL statement returns the total price of the item:
+
+```
+p
+--------------------
+253.97
+
+--- SQL operation complete.
+```
+
+The value of the named parameter, ?p, changes from 10 to the returned
+value, 253.97:
+
+```
+SQL>**show param**
+
+p 253.97
+```
+
+=== Using Unnamed Parameters
+
+In an trafci session, invoke the SPJ named TOTALPRICE by preparing and
+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',?);**
+
+--- SQL command prepared.
+
+SQL>**execute stmt1 using 2.25;**
+```
+
+The output of the prepared CALL statement is:
+
+```
+PRICE
+--------------------
+136.77
+
+--- SQL operation complete.
+```
+
+In an trafci session, invoke the SPJ named TOTALPRICE again by preparing
+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(?,?,?);**
+
+--- SQL command prepared.
+
+SQL>**execute stmt2 using 3, 'economy', 16.99;**
+```
+
+The output of the prepared CALL statement is:
+
+```
+PRICE
+--------------------
+
+57.13
+
+--- SQL operation complete.
+```
+
+=== Returning Result Sets in trafci
+
+If a CALL statement returns result sets, trafci displays column headings
+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', ?);**
+
+NUM_ORDERS
+--------------------
+13
+
+ORDERNUM NUM_PARTS            AMOUNT               ORDER_DATE LAST_NAME 
+-------- -------------------- -------------------- ---------- --------------------
+HUGHES HUGHES SCHNABL
+
+...
+
+--- 13 row(s) selected.
+
+ORDERNUM PARTNUM UNIT_PRICE QTY_ORDERED PARTDESC
+
+-------- ------- ---------- ----------- ------------------ 100210 244
+3500.00 3 PC GOLD, 30 MB
+
+100210 2001 1100.00 3 GRAPHIC PRINTER,M1
+
+100210 2403 620.00 6 DAISY PRINTER,T2
+
+... ... ... ... ...
+
+--- 70 row(s) selected.
+
+--- SQL operation complete.
+```
+
+For other result set examples, see link:#_bookmark221[Appendix A (page
+62)].
+
+== 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__]]...)}
+```
+
+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
+can execute the CALL statement.
+
+In this example, a CALL statement is executed from an ODBC client
+application:
+
+```
+/* Declare variables. */
+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);
+
+/* Execute the CALL statement. */
+
+SQLExecDirect(hstmt, "{call demo.persnl.adjustsalary(?,?,?)}", SQL_NTS);
+```
+
+For more information about ODBC client applications, see the __Neoview
+ODBC Drivers Manual__.
+
+=== 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
+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
+processing, where more than one returned result set can be open at a
+time.
+
+```
+/* Allocate a statement handle */
+SQLHSTMT 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));
+
+/* 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);
+
+/* Execute the CALL */
+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)
+{
+  /* The inner while loop processes each row of the current result set */
+  while (SQL_SUCCEEDED(rc = SQLFetch(hStmt)))
+  {
+    /* Process the row */
+  }
+}
+
+== 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__}...]])}
+```
+
+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.
+
+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.
+
+In this example, a CALL statement is executed from a JDBC client application:
+
+```
+CallableStatement stmt =
+
+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();
+
+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__.
+
+=== 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
+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', ?)}" );
+
+// Register an output parameter s.registerOutParameter(1,
+java.sql.Types.BIGINT);
+
+// Execute the CALL
+
+boolean rsAvailable = s.execute();
+
+// Process all returned result sets. The outer while loop continues
+
+// 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();
+
+}
+```
+
+This example shows how a JDBC client application can have more than one
+stored procedure result set open at a given time. The
+`java.sql.Statement.getMoreResults(int)` method
+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.
+
+```
+// Prepare a CALL statement
+
+java.sql.CallableStatement s = myConnection.prepareCall ( "{call
+demo.sales.ordersummary('01-01-2011', ?)}" );
+
+// Register an output parameter s.registerOutParameter(1,
+java.sql.Types.BIGINT);
+
+// Execute the CALL s.execute();
+
+// 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();
+
+// The outer loop processes each row of the FIRST result set while
+(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();
+
+}
+
+}
+```
+
+== 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)].
+
+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)].
+
+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:
+
+```
+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__.
+

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/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
new file mode 100644
index 0000000..647ed54
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/_chapters/get_started.adoc
@@ -0,0 +1,121 @@
+////
+/**
+ *@@@ START COPYRIGHT @@@
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * @@@ END COPYRIGHT @@@
+ */
+////
+
+[[get-started]]
+= Get Started
+
+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
+with the software required to support SPJs.
+
+[[required-client-software]]
+== Required Client Software
+
+[[java-development-kit]]
+=== Java Development Kit
+
+To develop Java methods to be used as SPJs, you must have a Java
+Development Kit (JDK) installed on the client workstation. To download a
+JDK, go to
+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:
+
+* 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.
+(To use the on-platform trafci client, see the
+http://trafodion.apache.org/docs/command_interface/index.html[Trafodion Command Interface Guide.)
+For example:
++
+```
+SQL> lh java -version
+
+java version "1.6.0_06"
+Java(TM) SE Runtime Environment (build 1.6.0_06-b02)
+Java HotSpot(TM) Client VM (build 10.0-b22, mixed mode)
+
+SQL>
+```
++
+In this example, the returned Java version indicates that the Trafodion
+instance supports Java SE 6 or JDK 6 (1.6.0), or earlier versions of the
+JDK.
+
+NOTE: If you plan to install the Trafodion JDBC Type-4 Driver on the client
+workstation, you must have JDK 6 (1.6.0) or higher installed on the
+client workstation.
+
+[[recommended-client-software]]
+== Recommended Client Software
+
+[[trafodion-command-interface-trafci]]
+=== Trafodion Command Interface (trafci)
+
+`trafci` is a command-line interface in which you can run SQL statements,
+such as GRANT PROCEDURE and CALL statements, interactively or from
+script files. To install `trafci` on a client workstation, see the
+http://trafodion.apache.org/docs/client_install/index.html[Trafodion Client Installation Guide].
+
+[[hp-jdbc-type-4-driver]]
+=== HP JDBC Type 4 Driver
+
+If you plan to use `trafci`, you must have a compatible version of the Trafodion
+JDBC Type-4 Driver installed on the client workstation.
+
+To install the JDBC Type-4 driver on the client workstation, see the 
+http://trafodion.apache.org/docs/client_install/index.html[Trafodion Client Installation Guide].
+
+

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/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
new file mode 100644
index 0000000..9ff668c
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/_chapters/grant_privileges.adoc
@@ -0,0 +1,312 @@
+////
+/**
+ *@@@ START COPYRIGHT @@@
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * @@@ END COPYRIGHT @@@
+ */
+////
+
+[[grant-privileges]]
+== Grant Privileges
+
+Security for SPJs is implemented by schema ownership rules and by
+granting privileges to specified database users and roles.
+
+The schema in which an SPJ is registered is the unit of ownership. The
+database user who creates the schema is the owner of that schema and all
+objects associated with it. In a Trafodion database, the schema owner
+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
+* 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
+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)]
+
+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.
+
+== 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__.
+
+If you own the SPJ, 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
+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.
+
+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.
+
+```
+GRANT EXECUTE
+
+ON PROCEDURE demo.persnl.adjustsalary
+
+TO "payrolldir1", "payrolldir2" WITH GRANT OPTION;
+```
+
+One of the Payroll directors grants the EXECUTE privilege on
+ADJUSTSALARY to the regional department managers:
+
+```
+GRANT EXECUTE
+ON PROCEDURE demo.persnl.adjustsalary TO "rgn1mgr", "rgn2mgr", "rgn3mgr"
+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
+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:
+
+```
+GRANT EXECUTE
+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
+
+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
+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)].
+
+For example, users with the EXECUTE privilege on the SPJ named
+ADJUSTSALARY, which is defined with EXTERNAL SECURITY INVOKER and which
+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:
+
+```
+GRANT SELECT, UPDATE (salary)
+ON TABLE demo.persnl.employee
+TO "payrolldir1", "payrolldir2" WITH GRANT OPTION;
+```
+
+One of the Payroll directors then grants these access privileges to the
+regional department managers:
+
+```
+GRANT SELECT, UPDATE (salary)
+ON TABLE demo.persnl.employee
+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
+database tables because that SPJ does not access any database tables.
+
+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__.
+
+== 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__.
+
+If you own the SPJ, 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
+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
+Region 2 department manager:
+
+```
+REVOKE EXECUTE
+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
+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:
+
+```
+REVOKE GRANT OPTION FOR EXECUTE
+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:
+
+```
+REVOKE GRANT OPTION FOR EXECUTE
+ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1"
+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
+SPJ named TOTALPRICE from all users and roles (that is, PUBLIC):
+
+```
+REVOKE EXECUTE
+ON PROCEDURE demo.sales.totalprice FROM PUBLIC;
+```
+
+== Using Script Files to Grant and Revoke Privileges
+
+Consider keeping your GRANT or REVOKE statements in script files. That
+way, you can quickly and easily grant or revoke privileges to the SPJs,
+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:
+
+```
+?SECTION GrantSalesProcs
+
+GRANT EXECUTE
+ON demo.sales.monthlyorders
+TO PUBLIC;
+
+GRANT SELECT
+ON TABLE demo.sales.orders TO PUBLIC;
+
+?SECTION GrantPersnlProcs
+
+GRANT EXECUTE
+ON PROCEDURE demo.persnl.adjustsalary TO "payrolldir1", "payrolldir2"
+WITH GRANT OPTION;
+
+GRANT SELECT, UPDATE(salary)
+ON TABLE demo.persnl.employee
+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)
+```
+
+=== 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
+a series of REVOKE PROCEDURE and REVOKE statements:
+
+```
+?SECTION RevokeSalesProcs
+
+REVOKE EXECUTE
+ON PROCEDURE demo.sales.monthlyorders FROM PUBLIC;
+
+REVOKE SELECT
+ON TABLE demo.sales.orders FROM PUBLIC;
+
+?SECTION RevokePersnlProcs
+
+REVOKE EXECUTE
+ON PROCEDURE demo.persnl.adjustsalary FROM "payrolldir1", "payrolldir2"
+CASCADE;
+
+REVOKE SELECT, UPDATE(salary)
+ON TABLE demo.persnl.employee
+FROM "payrolldir1", "payrolldir2" CASCADE;
+```
+
+To revoke privileges on the SPJs, run the script file in the trafci
+interface:
+
+```
+OBEY c:\revokeprocs.sql (RevokeSalesProcs)
+```
+

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/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
new file mode 100644
index 0000000..eb8d690
--- /dev/null
+++ b/docs/spj_guide/src/asciidoc/_chapters/introduction.adoc
@@ -0,0 +1,255 @@
+////
+/**
+ *@@@ START COPYRIGHT @@@
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * @@@ END COPYRIGHT @@@
+ */
+////
+
+[[introduction]]
+= Introduction
+
+This chapter introduces stored procedures in Java (SPJs) in a Trafodion
+database and covers these topics:
+
+[[what-is-an-spj]]
+== What Is an SPJ?
+
+A stored procedure is a type of user-defined routine (UDR) that operates
+within a database server and typically performs SQL operations on a
+database. The database server contains information about the stored
+procedure and controls its execution. A client application executes a
+stored procedure by issuing an SQL CALL statement. Unlike a user-defined
+function, which returns a value directly to the calling application, a
+stored procedure returns each output value to a dynamic parameter in its
+parameter list or returns a set of values to a result set array.
+
+The Trafodion database supports stored procedures written in the Java
+programming language. The Trafodion implementation of stored procedures
+complies mostly, unless otherwise specified, with SQL/JRT (Java Routines
+and Types), which extends the ANSI SQL/Foundation standard. A stored
+procedure in Java (SPJ) is a Java method contained in a Java archive
+(JAR) file on the Trafodion platform, registered in the database, and
+executed by the database engine when a client application issues a CALL
+statement.
+
+The body of a stored procedure consists of a public, static Java method
+that returns void. These Java methods, called _SPJ methods_, are
+contained in classes within JAR files on the cluster hosting the
+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
+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
+http://trafodion.apache.org/docs/sql_reference/index.html#create_procedure_statement[CREATE PROCEDURE]
+statement.
+
+[[benefits-of-spjs]]
+== Benefits of SPJs
+
+SPJs provide an efficient and secure way to implement business logic in
+the database. SPJs offer these advantages:
+
+* <<java-methods-callable-from-sql, Java Methods Callable From SQL>>
+* <<common-packaging-technique,Common Packaging Technique>>
+* <<security,Security>>
+* <<increased-productivity,Increased Productivity>>
+* <<portability,Portability>>
+
+[[java-methods-callable-from-sql]]
+=== Java Methods Callable From SQL
+
+With support for SPJs, Java methods are callable from any client
+application that connects to the Trafodion platform. For example, you can
+invoke the same SPJ method from JDBC client applications and ODBC client
+applications. By using the database engine to invoke Java methods, you
+can extend the functionality of the database and share business logic
+among different applications.
+
+For more information, see <<execute-spjs, Execute SPJs>>.
+
+[[common-packaging-technique]]
+=== Common Packaging Technique
+
+Different applications can invoke the same SPJ to perform a common
+business function. By encapsulating business logic in an SPJ, you can
+maintain consistent database operations and avoid duplicating code in
+applications.
+
+Applications that call SPJs are not required to know the structure of
+the database tables that the SPJ methods access. The application does
+not need to use any table or column names; it needs only the name of the
+stored procedure in the CALL statement. If the table structure changes,
+you might need to change the SPJ methods but not necessarily the CALL
+statements within each application.
+
+[[security]]
+=== Security
+
+By using SPJs, you can conceal sensitive business logic inside SPJ
+methods instead of exposing it in client applications. You can also
+grant privileges to execute an SPJ to specific users and restrict the
+privileges of other users. For more information, see
+<<grant-privileges, Grant Privileges>>
+
+[[increased-productivity]]
+=== Increased Productivity
+
+Use SPJs to reduce the time and cost of developing and maintaining
+client applications. By having several applications call the same SPJ,
+you need only change the SPJ method once when business rules or table
+structures change instead of changing every application that calls the
+SPJ.
+
+Using the Java language to implement stored procedures increases
+productivity. Given the popularity of the Java language, you can
+leverage the existing skill set of Java programmers to develop SPJs.
+
+The portability of the Java language enables you to write and compile
+Java class files for SPJs once and deploy them anywhere.
+
+[[portability]]
+=== Portability
+
+Because SPJ methods are written in Java, and SPJs conform to the ANSI
+SQL standard, SPJs are portable across different database servers. With
+minimal changes to SPJ methods, you can port existing SPJ JAR files from
+another database server to a Trafodion platform and register the methods
+as stored procedures in a Trafodion database. You can also port client
+applications that call SPJs in other databases to Trafodion SQL with
+minimal changes to the CALL statements in the application.
+
+<<<
+[[use-spjs]]
+== Use SPJs
+
+To use SPJs in a Trafodion database:
+
+1.  Verify that you have the required software installed on the client
+workstation. See <<get-started, Get Started>>.
+
+2.  Develop a Java method to be used as an SPJ:
+.. Write a static Java method:
++
+[source, java]
+----
+public class Payroll
+{
+   public static void adjustSalary( BigDecimal empNum
+                                  , double percent, BigDecimal[] newSalary
+				  ) throws SQLException
+   {
+      Connection conn =
+         DriverManager.getConnection( "jdbc:default:connection" ) ;
+
+      PreparedStatement setSalary =
+         conn.prepareStatement( "UPDATE demo.persnl.employee "
+	                      + "SET salary = salary * (1 + (? / 100)) "
+			      + "WHERE empnum = ?"
+			      ) ;
+
+      PreparedStatement getSalary =
+         conn.prepareStatement( "SELECT salary "
+	                      + "FROM demo.persnl.employee "
+			      + "WHERE empnum = ?"
+			      ) ;
+
+      setSalary.setDouble( 1, percent ) ;
+      setSalary.setBigDecimal( 2, empNum ) ;
+      setSalary.executeUpdate() ;
+
+      getSalary.setBigDecimal( 1, empNum ) ;
+      ResultSet rs = getSalary.executeQuery() ;
+      rs.next() ;
+
+      newSalary[0] = rs.getBigDecimal( 1 ) ;
+
+      rs.close() ;
+      conn.close() ;
+   }
+}
+----
+
+.. Compile the Java source file to produce a class file:
++
+```
+javac Payroll.java
+```
+
+.. Package the SPJ class file in a JAR file:
++
+```
+jar cvf Payroll.jar Payroll.class
+```
++
+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
+```
++
+For details, see <<develop-spj-methods, Develop SPJ Methods>>.
+
+3. Deploy the SPJ JAR file on the Trafodion platform by creating a
+library object for the JAR file in one of the database schemas. For
+details, see <<Deploy-spj-jar-files, Deploy SPJ JAR Files>>.
+
+4.  As the schema owner, create the SPJ in the database. For details,
+see <<create-spjs, Create SPJs>>.
+
+5.  Grant privileges to database users for executing the SPJ and for
+operating on the referenced database objects. For example, you can issue
+GRANT statements in an trafci session, as shown below:
++
+[source,sql]
+----
+GRANT EXECUTE
+ON PROCEDURE demo.persnl.adjustsalary
+TO "payrolldir1", "payrolldir2"
+WITH GRANT OPTION ;
+
+GRANT SELECT, UPDATE (salary)
+ON TABLE demo.persnl.employee
+TO "payrolldir1", "payrolldir2"
+WITH GRANT OPTION ;
+----
++
+For details, see <<grant-privileges, Grant Privileges>>.
+
+6. Execute an SPJ by using a CALL statement in a client application.
+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, ? ) ;
+
+NEWSALARY
+------------
+   139400.00
+
+--- SQL operation complete.
+```
++
+For details, see <<execute-spjs, Execute SPJs>>.
+
+7. Monitor the performance of SPJs and resolve common problems with
+SPJs in the database. See <<performance-and-troubleshooting, Performance and Troubleshooting>>.
+



Mime
View raw message