HP NonStop SQL/MX Release 3.2.
© Copyright 2013 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
Contents About This Manual........................................................................................7 Supported Release Version Updates (RVUs)..................................................................................7 Audience.................................................................................................................................7 Related Documentation..............................................................................................................
Using Multiple UDR_JAVA_OPTIONS Settings in One Application............................................39 Setting the JREHOME Location.................................................................................................40 Setting JREHOME by Using UDR_JAVA_OPTIONS..................................................................40 Setting the JREHOME Environment Variable...........................................................................41 Setting the JDBC/MX Location......................
Invoking SPJs in NonStop SQL/MX.............................................................71 Calling an SPJ........................................................................................................................71 Required Privileges for Calling an SPJ...................................................................................71 Effect of Calling an SPJ.......................................................................................................71 Transaction Behavior....
Payroll Class....................................................................................................................109 Inventory Class................................................................................................................110 The createprocs.sql File.........................................................................................................111 Examples of the Sample SPJs........................................................................................
About This Manual This manual describes how to develop, deploy, and manage stored procedures in Java (SPJs) in NonStop SQL/MX. NonStop SQL/MX is a relational database management system based on the ANSI/ISO/IEC 9075:1999 SQL standard, commonly referred to as SQL:1999, for the HP NonStop server. Throughout this manual, references to NonStop SQL/MX Release 2.x indicate SQL/MX Release 2.0, 2.1, 2.2, and subsequent releases until otherwise indicated in a replacement publication.
Connectivity Manuals SQL/MX Connectivity Service Manual Describes how to install and manage the HP NonStop SQL/MX Connectivity Service (MXCS), which enables applications developed for the Microsoft Open Database Connectivity (ODBC) application programming interface (API) and other connectivity APIs to use NonStop SQL/MX. SQL/MX Connectivity Service Administrative Command Reference Describes the SQL/MX administrative command library (MACL) available with the SQL/MX conversational interface (MXCI).
The NSM/web, SQL/MX Database Manager, and Visual Query Planner help systems are accessible from their respective applications. You can download the Reference, Messages, and Glossary online help from the HP Software Depot at http://www.software.hp.com. For more information about downloading the online help, see the SQL/MX Release 3.2 Installation and Upgrade Guide.
◦ “Inventory Class” (page 110) ◦ “LOWERPRICE Stored Procedure” (page 113) ◦ “DAILYORDERS Stored Procedure” (page 115) ◦ “MONTHLYORDERS Stored Procedure” (page 115) ◦ “ADJUSTSALARY Stored Procedure” (page 117) ◦ “EMPLOYEEJOB Stored Procedure” (page 118) ◦ “SUPPLIERINFO Stored Procedure” (page 119) ◦ “SUPPLYNUMBERS Stored Procedure” (page 120) • Removed a note from the section “Portability” (page 20). • Updated the Table 1 (page 32).
• Added the section Using the EXPLAIN function with SPJ RS (page 103). • Added a sample ORDERSUMMARY Stored Procedure (page 121).
For the entire set of sample SPJs, see Appendix A: Sample SPJs. Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives General Syntax Notation The following list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
INSPECT { OFF | ON][SAVEABEND } … Ellipsis. An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: M address [ , new-value ]… [ - ] {0|1|2|3|4|5|6|7|8|9}… An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char…" Punctuation.
error := FILE_GETINFO_ ( filenum !i , [ filename:maxlen ] ) ; !o:i Notation for Messages The following list summarizes the notation conventions for the presentation of displayed messages in this manual. Bold Text. Bold text in an example indicates user input entered at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. Nonitalic text.
P=%p-register E=% e-register Notation for Management Programming Interfaces The following list summarizes the notation conventions used in the boxed descriptions of programmatic commands, event messages, and error lists in this manual. UPPERCASE LETTERS. Uppercase letters indicate names from definition files; enter these names exactly as shown. For example: ZCOM-TKN-SUBJ-SERV lowercase letters.
HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to docsfeedback@hp.com. Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
1 Introduction This section introduces stored procedures in Java (SPJs) in NonStop SQL/MX and covers these topics: • What Is an SPJ? (page 17) • How Do I Use SPJs? (page 18) • Benefits of SPJs (page 19) • SPJs in NonStop SQL/MX (page 21) • The SPJ Environment (page 24) What Is an SPJ? A stored procedure is a type of user-defined routine (UDR) that operates within a database server. The database server contains the metadata of stored procedures and controls their execution.
How Do I Use SPJs? To create and invoke SPJs in NonStop SQL/MX: 1. Verify that you have the required software products installed on your HP NonStop system. See Verifying Software Versions (page 33). 2. Write and compile a static Java method to be used as an SPJ: 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.
LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA; For details, see Chapter 4: Registering SPJs in NonStop SQL/MX. 5. Grant privileges to users for invoking each SPJ: GRANT EXECUTE ON PROCEDURE samdbcat.persnl.adjustsalary TO "HR.MGRNA", "HR.MGREU" WITH GRANT OPTION; GRANT SELECT, UPDATE (salary) ON TABLE samdbcat.persnl.employee TO "HR.MGRNA", "HR.MGREU" WITH GRANT OPTION; For details, see Granting Privileges for Invoking SPJs (page 83). 6. Invoke each SPJ by using the CALL statement: CALL samdbcat.
Figure 1 Different Applications Calling the Same SPJ For more information, see Chapter 5: Invoking SPJs in NonStop SQL/MX. 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 tables that the SPJ methods access.
SPJs in NonStop SQL/MX This subsection explains the process and effect of registering and invoking SPJs in NonStop SQL/MX: • Effect of Registering an SPJ (page 21) • Effect of Invoking an SPJ (page 22) • Invoking Different Types of SPJs (page 23) Effect of Registering an SPJ When you register an SPJ by using a CREATE PROCEDURE statement, NonStop SQL/MX verifies that the specified Java method exists and that its signature matches the SQL parameters of the stored procedure.
NOTE: Systems running on J06.04 and earlier J-series RVUs or H06.15 and earlier H-series RVUs have VAILDATEROUTINE internal SPJ only, whereas systems running on J06.05 and later J-series RVUs or H06.16 and later H-series RVUs have both VALIDATEROUTINE and VALIDATEROUTINE2 internal SPJs. Additionally, MXCMP released in H06.16 and later RVUs invokes internal SPJ VALIDATEROUTINE2 while MXCMP released in H06.15 or earlier RVUs invoke the VAILDATEROUTINE internal SPJ. 4.
Figure 3 Invoking an SPJ 1. 2. 3. 4. 5. An SQL/MX application invokes an SPJ by issuing a CALL statement. The SQL/MX executor reads the compiled SQL plan for the CALL statement. The SQL/MX executor checks the procedure label to verify that the caller has permission to invoke the SPJ. If the caller has permission to invoke the SPJ, the SQL/MX executor starts sending messages to the SQL/MX UDR server (shown as the MXUDR executable in Figure 3) to execute the SPJ.
JDBC/MX-Based Java Method An SPJ method can be from a class file that uses standard JDBC method calls to access a NonStop SQL database. The pure Java and native layers of the JDBC/MX driver work together to process database requests issued by the SPJ method: The SPJ Environment NonStop SQL/MX processes CALL statements in an SPJ environment, which is hosted within an SQL/MX UDR server process.
Figure 4 SPJ Environment in NonStop SQL/MX This subsection explains some of the ways in which the SPJ environment in NonStop SQL/MX differs from a typical Java environment: • SQL/MX UDR Server Process (page 25) • Multithreading in an SPJ Environment (page 27) • Class Loaders in an SPJ Environment (page 28) • Application Classes That an SPJ Can Access (page 29) • Maintaining Class and JAR Files in an SPJ Environment (page 30) SQL/MX UDR Server Process The SQL/MX UDR server is a program executable
Applications that initiate multiple SQL/MX UDR server processes include NonStop ODBC/MX and JDBC/MX applications. NonStop ODBC/MX clients often switch user identities. Multithreaded JDBC/MX applications can use different UDR_JAVA_OPTIONS settings for different threads. For these types of applications, new SQL/MX UDR server processes are started as needed, and stop when all the connections that were serviced by the SQL/MX UDR server process end.
Figure 5 Initialization of SQL/MX UDR Server Processes Reinitialization of the SPJ Environment In rare cases, the SQL/MX UDR server might crash unpredictably because of internal system errors or the application code. In such cases, the SPJ environment is reinitialized when a CALL statement is issued following the crash. Multithreading in an SPJ Environment Within an SPJ environment, NonStop SQL/MX manages a single thread of execution, even for multithreaded Java applications.
application. For information about setting the UDR_JAVA_OPTIONS default attribute, see Controlling JVM Startup Options (page 36). CAUTION: NonStop SQL/MX does not prevent SPJ methods from spawning other threads. Doing so is not advised and can lead to unpredictable results. Class Loaders in an SPJ Environment For system and extension classes, class loading in the SPJ environment is the same as other Java environments.
Figure 6 SPJ Class Loaders in an SQL/MX UDR Server Process Application Classes That an SPJ Can Access A Java method that you register as an SPJ might need to access, either directly or indirectly, other Java classes to operate properly, as shown in Figure 7. Figure 7 Java Classes That an SPJ Can Access To enable an SPJ method to refer to application classes, either put the application classes in the same external path as the SPJ class or specify the locations of the application classes in the class path.
Accessing Application Classes in the External Path An SPJ method can access by default all application classes within its external path location, which you specify in the EXTERNAL PATH clause of a CREATE PROCEDURE statement when registering an SPJ. The external path is either an OSS directory or a JAR file path that contains the SPJ class file. See Specifying the External Path (page 66) .
loads an application class, the class loader creates and manages a new copy of the application class and its static variables. For an example, see Figure 8 (page 31). Figure 8 Copies of Java Classes in SPJ Class Loaders If an application class does not change on disk during an active SPJ environment, identical copies of the application class exist in memory and could waste resources.
2 Getting Started Before you can use SPJs in NonStop SQL/MX: • Verify that your system meets software requirements. • Install necessary software products. • Configure the SPJ environment.
Table 1 Software Products Required for SPJs (continued) SQL/MX Product Version Minimum Supported RVU NonStop Server for Java* JDBC/MX Driver* **Starting from NonStop SQL/MX Release 3.2.1, only NSJ7 or later versions of Java are supported. ***Distributed GC feature in NSJ7 is not supported for the SPJ. NonStop Server for Java To create and execute SPJs in NonStop SQL/MX, you must install the NonStop Server for Java on a NonStop system.
On systems running H-series RVUs, notice that the NonStop Server for Java and JDBC/MX driver are not bound into the SQL/MX UDR server. Verifying the NonStop Server for Java To display the product version of the NonStop Server for Java in the Java environment of a NonStop system, enter this vproc command at an OSS prompt: vproc /usr/tandem/nssjava/jdk170_h70/bin/java If you have set the JREHOME environment variable for a nonstandard location of the NonStop Server for Java, enter: vproc $JREHOME/..
Configuring the SPJ Environment on Systems Running H-Series RVUs On systems running H-series RVUs, the libraries of the NonStop Server for Java and the JDBC/MX driver are not statically bound into the SQL/MX UDR server.
To use UDR_JAVA_OPTIONS, see Controlling JVM Startup Options (page 36). To use the _RLD_LIB_PATH environment variable, see the NonStop Server for Java 7.0 Programmer’s Reference. UDR Extensions Class Path for the JDBC/MX JAR File By default, NonStop SQL/MX loads the jdbcMx.jar file of JDBC/MX from the standard location of /usr/tandem/jdbcMx/current/lib into the SPJ environment. To use jdbcMx.jar in a nonstandard location, set the UDR extensions class path as you would on systems running G-series RVUs.
Displaying the UDR_JAVA_OPTIONS in Effect for a Node To show the UDR_JAVA_OPTIONS setting in effect for the system, enter the SHOWCONTROL ALL command in an MXCI session. For more information about SHOWCONTROL, see the SQL/MX Reference Manual. JVM Startup Options for Each SPJ Caller To set JVM startup options for each caller of an SPJ, use a CONTROL QUERY DEFAULT statement.
Scope of JVM Startup Options in Dynamically Compiled Applications The UDR_JAVA_OPTIONS setting in a dynamically executed CONTROL QUERY DEFAULT statement affects CALL statements that are dynamically prepared after the CONTROL QUERY DEFAULT statement has executed.
Scope of JVM Startup Options in an MXCI Session During an MXCI session, you can issue a CONTROL QUERY DEFAULT statement that sets the UDR_JAVA_OPTIONS default attribute. This setting applies only to CALL statements that you issue in MXCI after you issue the CONTROL QUERY DEFAULT statement. For example, the first CONTROL QUERY DEFAULT statement sets the class path for the SPJ environment of subsequent CALL statements that you issue in MXCI.
CALL... CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS '-Xmx32M -Djava.class.path=/usr/otherclasses'; CALL... However, each of these UDR_JAVA_OPTIONS settings is different because of the different order of the options: CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS '-Xmx32M -Djava.class.path=/usr/otherclasses'; CALL... CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS '-Djava.class.path=/usr/otherclasses -Xmx32M'; CALL...
The java-installation-directory is the path of the installation directory of the NonStop Server for Java. CAUTION: NonStop SQL/MX maintains an SQL/MX UDR server process for each different set of UDR_JAVA_OPTIONS in an application. The existence of multiple SQL/MX UDR server processes might significantly affect system performance. Therefore, use UDR_JAVA_OPTIONS settings in your application only when necessary. For more information, see SQL/MX UDR Server Process (page 25).
Setting JREHOME in the OSS Environment To set the JREHOME environment variable in the OSS environment, enter this command at an OSS prompt: export JREHOME=java-installation-directory/jre The java-installation-directory is the path of the installation directory of the NonStop Server for Java.
NOTE: If you specify an alternate JDBC/MX location by using the -Dsqlmx.udr.extensions option and jdbcMx.jar does not exist at that location, NonStop SQL/MX uses the standard location for jdbcMx.jar. Setting the JDBC/MX Location by Using UDR_JAVA_OPTIONS Use the UDR_JAVA_OPTIONS default attribute to set the JDBC/MX location in an SPJ environment for an application or for all processes running on the node.
When an SQL/MX application issues a CALL statement, a class loader in the SPJ environment uses the class path to locate and load Java classes outside the external path of the SPJ method. For more information, see Class Loaders in an SPJ Environment (page 28). When an SQL/MX application issues a CREATE PROCEDURE statement, the JVM resolves references to other Java classes in the signature of the SPJ method but not inside the body of the SPJ method.
SYSTEM_DEFAULTS table to set the class path for all CREATE PROCEDURE statements that are registered on the system: SET SCHEMA NONSTOP_SQLMX_node.SYSTEM_DEFAULTS_SCHEMA; INSERT INTO SYSTEM_DEFAULTS (ATTRIBUTE, ATTR_VALUE) VALUES ('UDR_JAVA_OPTIONS', '-Djava.class.path=/usr/otherclasses:/usr/otherapps/myJar.jar'); For more information about the scope of the UDR_JAVA_OPTIONS setting, see Controlling JVM Startup Options (page 36).
path1 and path2 are paths to JAR files or top-level directories where Java classes or package directories exist. The paths must not include the package name. For example, this command sets the class path for SPJs invoked from embedded SQL programs in C, C++, or COBOL that will run in the current Guardian session: PARAM CLASSPATH /usr/otherapps/myJar.jar:/usr/otherclasses For more information about the PARAM command, see the TACL Reference Manual.
By using a policy file, you can configure the Java security manager so that specific Java classes and methods can perform restricted operations, such as accessing a directory or network address. Using UDR_JAVA_OPTIONS to Enable Java Security By default, Java security is disabled in the SPJ environment of an SQL/MX UDR server process. To enable Java security in the SPJ environment, use this UDR_JAVA_OPTIONS attribute value: '-Djava.security.manager-Djava.security.policy= /usr/tandem/sqlmx/udr/mxlangman.
grant codeBase "file:/usr/tandem/sqlmx/udr/mxlangman.jar" { permission java.security.AllPermission; }; If the SPJ policy file does not contain these permissions, all CALL statements fail and return errors that describe a security-related problem with the SQL/MX language manager.
3 Writing SPJ Methods Before you can create an SPJ in NonStop SQL/MX, you must write and compile the Java method to be used as the body of the SPJ. The Java methods that you use for the body of an SPJ are called SPJ methods.
Table 2 Mapping of Java Data Types to SQL/MX Data Types (continued) Java D ata Type Maps to SQL/MX Data Type... VARYING NATIONAL CHAR[ACTER] NATIONAL CHAR[ACTER] VARYING java.sql.Date DATE java.sql.Time TIME java.sql.Timestamp TIMESTAMP java.math.BigDecimal NUMERICDEC[IMAL]PIC[TURE] S9 short SMALLINT int or java.lang.Integer* INT[EGER] long or java.lang.Long* LARGEINT double or java.lang.Double* FLOAT float or java.lang.Float* REAL double or java.lang.
String[] stateName, String[] postCode) throws SQLException { ... The supplyQuantities() method returns an average quantity, a minimum quantity, and a maximum quantity to separate output parameters of the integer type: public static void supplyQuantities(int[] avgQty, int[] minQty, int[] maxQty) throws SQLException { ... For more information about the SPJ examples, see Appendix A: Sample SPJs.
" sales.orders ORDERS, persnl.employee EMPS " + " WHERE AMOUNTS.ordernum = ORDERS.ordernum " + " AND ORDERS.salesrep = EMPS.empnum " + " ORDER BY ORDERS.ordernum "; PreparedStatement ps2 = c.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 complete example, see the ORDERSUMMARY Stored Procedure (page 121).
Consider these drawbacks to using static variables in your SPJ method: • If an SQL/MX UDR server crashes, the calling application receives a new SQL/MX UDR server and a new SPJ environment, including new copies of all static variables initialized in that server. For more information, see SQL/MX UDR Server Process (page 25).
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: java.util.
To register this method as an SPJ in NonStop SQL/MX, use a CREATE PROCEDURE statement. For details, see Chapter 4: Registering SPJs in NonStop SQL/MX. For other examples of JDBC/MX-based SPJs, see Appendix A: Sample SPJs. For information about JDBC/MX, see the JDBC Driver for SQL/MX Programmer's Reference. Referring to Database Objects in an SPJ Method In an SPJ method, you can refer to both SQL/MP and SQL/MX database objects as you would in other SQL/MX applications.
{ 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, SAMDBCAT.SALES, as the SPJ.
Handling Java Exceptions If an SPJ method returns an uncaught Java exception or an uncaught chain of java.sql. SQLException objects, NonStop SQL/MX converts each Java exception object into an SQL/MX error condition, and the CALL statement fails. Each SQL/MX 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/MX processing.
... printStream.println("The salary was updated for employee " + empnum); ... } Suppose that an employee number of 202 is passed to the SPJ. When a CALL statement invokes this SPJ method, the text "The salary was updated for employee 202" is written to the file /usr/mydir/spj.output. You can also redirect one or both System streams to an output stream of your choice.
4 Registering SPJs in NonStop SQL/MX This section covers these topics: • Creating an SPJ (page 59) • Using the CREATE PROCEDURE Statement (page 59) • Dropping an SPJ (page 67) • Altering an SPJ and Its Java Class (page 68) This section assumes that you have already written and compiled the SPJ methods. For more information, see Chapter 3: Writing SPJ Methods. Creating an SPJ The CREATE PROCEDURE statement registers an existing Java method as an SPJ within an SQL/MX database.
To construct a CREATE PROCEDURE statement, see: • Naming the Stored Procedure (page 60) • Specifying SQL Parameters (page 61) • Specifying the SPJ Method (page 64) • Specifying the External Path (page 66) • Naming the Procedure Label (page 66) • Specifying an SQL Access Mode (page 67) The examples in this subsection are based on the SPJ methods in Appendix A: Sample SPJs. For the syntax of the CREATE PROCEDURE statement, see the SQL/MX Reference Manual.
• Qualify the procedure name with an existing catalog and schema. If you do not fully qualify the procedure name, NonStop SQL/MX qualifies it according to the current settings of CATALOG and SCHEMA in the SYSTEM_DEFAULTS table. If the NAMETYPE attribute is set to NSK instead of ANSI and you do not fully qualify the procedure name, NonStop SQL/MX returns an error. For more information, see the SQL/MX Reference Manual.
SQL Data Type The SQL data types must correspond with the underlying Java data types of the SPJ method, as shown: Because the Java signature of the SPJ method in this example is (BigDecimal, double, BigDecimal[]), you should specify these SQL data types, or similar ones, for the procedure: (NUMERIC, FLOAT, NUMERIC). Recall that all output parameters (OUT and INOUT) of a Java method must be arrays that accept a single value. (See Returning Output Values From the Java Method (page 50).
Table 3 Mapping of SQL/MX Data Types to Java Data Types (continued) SQL/MX Data Type Maps to Java Data Type... DOUBLE PRECISION double (or java.lang.Double if specified)*** * The character set for character string data types can be ISO88591 or UCS2. ** Numeric data types must be SIGNED, which is the default in NonStop SQL/MX. *** By default, the SQL/MX data type maps to a Java primitive data type.
If more than one matching Java method exists, by default SQL/MX does not attempt to resolve the ambiguity and an error is raised. This can happen when two or more methods in the specified Java class have the same overloaded method name but differ only in the number of trailing java.sql.ResultSet[ ] parameters. When a CREATE PROCEDURE statement can potentially map to more than one Java method, users must provide a full Java method signature in the EXTERNAL NAME clause to resolve the ambiguity.
However, if you specify INT for an SQL parameter and the underlying SPJ method uses java.lang.Integer, you must specify the Java signature in the EXTERNAL NAME clause, as shown: The Java signature is case-sensitive and must be placed within parentheses. The signature must specify each of the parameter data types in the order that they appear in the Java method definition within the class file.
The query displays the compressed Java signature of the ADJUSTSALARY procedure: Compressed Signature -------------------------------------------------------------------------(Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V For more information about the TEXT metadata table, see the SQL/MX Reference Manual. Specifying the External Path The external path specifies the location of the Java class file that contains the SPJ method.
If DDL_DEFAULT_LOCATIONS default is not set, then the volume name of the SPJ is retrieved from the =_DEFAULTS define. If you want to control the location of the procedure label, particularly in a distributed database environment, specify the LOCATION clause, as shown: LOCATION \REMOTENODE.$DISKVOL.SUBVOL.PROCLABEL You can create the procedure label only on a node where the catalog of the SPJ is visible.
Using the DROP PROCEDURE Statement To drop an SPJ, specify the name of the SPJ in the DROP PROCEDURE statement, as shown: DROP PROCEDURE samdbcat.persnl.adjustsalary; Do not specify the SQL parameters along with the procedure name. Each procedure name represents a unique SPJ in the database because NonStop SQL/MX does not support the overloading of procedure names. For the syntax of the DROP PROCEDURE statement, see the SQL/MX Reference Manual.
CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT, OUT number INT) EXTERNAL NAME 'pkg.subpkg.Sales.numMonthlyOrders' EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; To drop the SPJ, issue this DROP PROCEDURE statement: DROP PROCEDURE samdbcat.sales.monthlyorders; To re-create the SPJ, issue this CREATE PROCEDURE statement: CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT, OUT number INT) EXTERNAL NAME 'pkg.subpkg.Sales.
Changes to the Java Signature The Java signature specifies each of the parameter data types in the Java method definition. A change to the Java signature of an SPJ suggests a fundamental change to the underlying Java class of the existing SPJ method. For example, suppose that you reassign the SPJ named MONTHLYORDERS from the SPJ method named numMonthlyOrders() to another SPJ method named numMonthlyOrders2(), which has a different Java signature.
5 Invoking SPJs in NonStop SQL/MX This section describes how to execute SPJs and assumes that you have already registered the SPJs in NonStop SQL/MX. For information about registering an SPJ, see Chapter 4: Registering SPJs in NonStop SQL/MX.
1. 2. 3. 4. 5. Suspend the current transaction (if supported by the API). Start a new transaction. Complete the work. End the new transaction. Resume the current transaction (if suspended). You must ensure that the transactions started within the stored procedure are cleaned up properly. Some of the Java APIs do not support the ability to suspend and resume transactions.
For the syntax of the CALL statement, see the SQL/MX Reference Manual. Specifying the Name of the SPJ In the CALL statement, specify the name of an SPJ that you have already registered in NonStop SQL/MX. Qualify the procedure name with the same catalog and schema that you specified in the CREATE PROCEDURE statement. For example: SET SCHEMA samdbcat.persnl; CALL adjustsalary(202, 5.
For an IN parameter argument, use one of these SQL expressions: Type of Argument Examples Literal CALL adjustsalary(202, 5.5, ?); CALL dailyorders(DATE '2003-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.
For more information about MXCI parameters, see the SQL/MX Reference Manual. Using MXCI Named Parameters In an MXCI 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 Sales Class (page 107).
Using MXCI with SPJ RS In an MXCI session, invoking the SPJ named ORDERSUMMARY returns one LARGEINT value as an output parameter and returns a maximum of two result sets. The output parameter row (which in this case contains one column named NUM_ORDERS) is displayed first followed by two stored procedure result sets. Prepare and run the following CALL statement: CALL samdbcat.sales.
In this example of an embedded SQL program in C, the static CALL statement has an IN parameter argument consisting of a host variable, an IN parameter argument of 5.5, and an OUT parameter argument consisting of another host variable: EXEC SQL BEGIN DECLARE SECTION; NUMERIC(4) hv_empnum_param1; double hv_percent_param2; NUMERIC(8,2) hv_newsalary_param3; EXEC SQL END DECLARE SECTION; /* Set the IN arguments. */ hv_empnum_param1 = 202; hv_percent_param2 = 5.5; /* Call the stored procedure.
Number of Entries in Input and Output Descriptors In dynamic SQL, the input descriptor has n entries, where n is the number of actual parameters that map to formal IN and INOUT parameters. n is not necessarily the same as the number of formal IN and INOUT parameters. Conversely, the output descriptor has m entries, where m is the number of actual parameters that map to formal OUT and INOUT parameters.
/* Set the input values of the two IN parameters in * ordinal positions 1 and 2, which are the only entries * in the input descriptor.
In this example, an embedded dynamic CALL statement uses argument lists: EXEC SQL BEGIN DECLARE SECTION; NUMERIC(4) hv_empnum_param1; double hv_percent_param2; NUMERIC(8,2) hv_newsalary_param3; CHAR hv_sql_stmt[64]; EXEC SQL END DECLARE SECTION; /* Build the CALL statement in a char buffer. */ strcpy(hv_sql_stmt, "CALL samdbcat.persnl.adjustsalary(?,?,?)"); /* IN, IN, OUT */ /* Prepare the statement.
/* Execute the CALL statement. */ SQLExecDirect(hstmt, "{call adjustsalary(?,?,?)}", SQL_NTS); NOTE: Nonstop ODBC/MX Client does not support SQLExecDirect API for Stored Procedure in Java with Result Sets (SPJ RS). For more information about NonStop ODBC/MX clients, see the ODBC/MX Driver for Windows Manual. Invoking SPJs in a JDBC/MX Program You can execute a CALL statement in a JDBC/MX program by using the JDBC CallableStatement interface.
problem with using SIGNAL statement in this case is that the encompassing transaction is aborted, and therefore the dues will not be updated properly. DBA wants to update the dues but at the same time, flag the member as being blocked. One solution is to use a CALL statement and delegate the implementation of the external notification to a member-defined routine.
6 Managing SPJs in NonStop SQL/MX This section covers management tasks related to SPJs in NonStop SQL/MX: • Granting Privileges for Invoking SPJs (page 83) • Displaying Information About SPJs (page 88) • Keeping SPJ Statements in OBEY Command Files (page 94) • Backing Up SPJs (page 97) • Using SPJs in a Distributed Database Environment (page 98) Granting Privileges for Invoking SPJs Security for SPJs is implemented by the schema ownership rules and by granting privileges to specified users.
In some cases, all users of a database system might need to invoke an SPJ. For example, the SPJ named MONTHLYORDERS determines the number of product orders during a given month. This SPJ does not handle sensitive information or modify data and might be useful to anyone within the company. Therefore, the database administrator (the super ID), acting on behalf of the SPJ owner, grants the EXECUTE privilege on MONTHLYORDERS to PUBLIC, meaning all present and future user IDs: GRANT EXECUTE ON samdbcat.sales.
All users with the EXECUTE privilege on the SPJ named MONTHLYORDERS must have the SELECT privilege on the ORDERS table: GRANT SELECT ON TABLE samdbcat.sales.orders TO PUBLIC; The types of SQL statements in the underlying SPJ method, such as SELECT, UPDATE, DELETE, and INSERT, should indicate which privileges, such as SELECT, UPDATE, DELETE, and INSERT, are required for the referenced database objects. For the syntax of the GRANT statement, see the SQL/MX Reference Manual.
The HR director of North America cannot revoke the EXECUTE or WITH GRANT OPTION privilege from the HR director of Europe because it was the database administrator who granted the privileges on behalf of the SPJ owner. The database administrator (the super ID), acting on behalf of the grantor of the privilege, can revoke the WITH GRANT OPTION privilege on ADJUSTSALARY from any user with this privilege.
The database administrator, acting on behalf of the grantor of the privilege, can also revoke the EXECUTE privilege from any user with this privilege and from any dependent privileges by using the CASCADE option.
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. For example, this statement revokes the EXECUTE privilege on the SPJ named MONTHLYORDERS from all users (that is, PUBLIC): REVOKE EXECUTE ON PROCEDURE samdbcat.sales.monthlyorders FROM PUBLIC; Displaying Information About SPJs To manage SPJs in NonStop SQL/MX, you might need to gather information about the SPJs that are already registered in the database.
LOG spj_list.txt; 2. In the same MXCI session, enter this query to list all the SPJs in a catalog of the database: SELECT SUBSTRING(cat_name,1,25) AS "CATALOG", SUBSTRING(schema_name,1,25) AS "SCHEMA", SUBSTRING(object_name,1,25) AS "PROCEDURE" FROM catalog.definition_schema_version_vernum.objects ot, nonstop_sqlmx_node.system_schema.schemat ast, nonstop_sqlmx_node.system_schema.catsys ct WHERE ot.object_type = 'UR' AND ot.schema_uid = st.schema_uid AND st.cat_uid = ct.
SELECT SUBSTRING(schema_name,1,8) AS "SCHEMA", SUBSTRING(object_name,1,15) AS "PROCEDURE", grantee, grantee_type, privilege_type, is_grantable FROM catalog.definition_schema_version_vernum.objects ot, nonstop_sqlmx_node.system_schema.schematast, catalog.definition_schema_version_vernum.tbl_privileges pr WHERE ot.object_type = 'UR' AND ot.schema_uid = st.schema_uid AND ot.object_uid = pr.table_uid; The schema version number vernum for NonStop SQL/MX Releases 2.0, 2.1, and 2.2 is 1200.
PERSNL PERSNL PERSNL PERSNL PERSNL PERSNL SALES SALES SALES SALES SALES ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY DAILYORDERS LOWERPRICE MONTHLYORDERS MONTHLYORDERS TOTALPRICE 2053 2069 2068 2055 2054 2051 2053 2053 2053 -1 2053 U U U U U U U U U P U E E E E E E E E E E E Y N N N Y Y Y Y Y N Y --- 11 row(s) selected. The grantees are represented by user ID numbers. Use the USER_GETINFO_ procedure to return the user name associated with the user ID number.
EXTERNAL PATH '/usr/mydir/myclasses' LOCATION \KINGPIN.$TX0115.ZSDX7KT4.SL9FSB00 LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA NOT DETERMINISTIC ISOLATE ; --- SQL operation complete. For more information about the SHOWDDL command, see Showing the Syntax of an SPJ (page 92).
Output of the SHOWDDL Command The SHOWDDL command displays information about an SPJ in this format: CREATE PROCEDURE catalog-name. schema-name.procedure-name ( [sql-parameter [{, sql-parameter}...
For more information about ADJUSTSALARY and its underlying SPJ method, adjustSalary(), see Appendix A: Sample SPJs. Output of the SHOWDDL Command of an SPJ with Result Sets To display information about an SPJ that returns result sets (in this example, ORDER_SUMMARY), enter this SHOWDDL command in an MXCI session: SHOWDDL PROCEDURE samdbcat.sales.order_summary; The SHOWDDL command for the ORDER_SUMMARY SPJ returns this output: CREATE PROCEDURE SAMDBCAT.SALES.
CREATE PROCEDURE samdbcat.persnl.adjustsalary(IN empnum NUMERIC(4), IN percent FLOAT, OUT newsalary NUMERIC(8,2)) EXTERNAL NAME 'Payroll.adjustSalary' EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA; ... To create SPJs from an OBEY command file, issue the OBEY command in MXCI: OBEY createprocs.sql; NOTE: The SPJ class must exist on the disk for the CREATE PROCEDURE statements to run successfully.
TO "HR.MGRNA", "HR.MGREU" WITH GRANT OPTION; To grant privileges on the SPJs, issue the OBEY command in MXCI: OBEY grantprocs.sql "GRANT SALES SPJs"; REVOKE EXECUTE Statements in an OBEY Command File You can use another or the same OBEY command file to revoke privileges on a series of SPJs. For example, the OBEY command file, revokeprocs.sql, contains a series of REVOKE EXECUTE statements: ?SECTION "REVOKE SALES SPJs" REVOKE EXECUTE ON PROCEDURE samdbcat.sales.
LOG; 4. Edit the log file by removing the SHOWDDL commands and by revising the CREATE PROCEDURE statements if necessary. In this example, you must remove the text in boldface from the log file. The output of the SHOWDDL command does not exactly match the original CREATE PROCEDURE statement. For more information, see the Output of the SHOWDDL Command (page 93). >>showddl procedure samdbcat.sales.dailyorders; CREATE PROCEDURE SAMDBCAT.SALES.DAILYORDERS ( IN DATE , OUT NUMBER INTEGER ) EXTERNAL NAME 'Sales.
Using SPJs in a Distributed Database Environment For an SPJ to operate in a distributed database environment, the catalog in which the SPJ is created must be visible on every remote node that requires access to that catalog. In other words, the catalog of the SPJ must be visible on each node where you want users to be able to call the SPJ.
Advantages of Copying the Java Classes The advantage of this approach is that there are fewer cross-node dependencies. If the network is disconnected or if one node goes down (for example, \NODEA), a statically compiled application can still execute the SPJ on the remaining node (for example, \NODEB), as long as the procedure label of the SPJ is still available.
Figure 10 Specifying the Node in the External Path When an application on the remote \NODEB calls the SPJ, the JVM looks for the Java class in the /E/NODEA/usr/mydir/myclasses directory on \NODEA. Regardless of where you issue the CALL statement, the JVM in the SPJ environment always tries to load the Java class from the OSS directory on the node that you specify in the EXTERNAL PATH clause.
7 Performance and Troubleshooting This section describes how to improve and monitor the performance of SPJs in NonStop SQL/MX, provides guidelines for troubleshooting common problems, and covers these topics: • Troubleshooting SPJ Problems (page 101) • Performance Tips (page 101) • Displaying an Execution Plan for a CALL Statement (page 102) • Displaying the Shape of a CALL Statement (page 103) Troubleshooting SPJ Problems To resolve problems that occur when you register or invoke an SPJ, follow the
• Avoid using too many UDR_JAVA_OPTIONS settings in applications that call SPJs. For more information, see Using Multiple UDR_JAVA_OPTIONS Settings in One Application (page 39). • Avoid nesting CALL statements in an SPJ method, which wastes resources and diminishes performance. For more information, see Nested Java Method Invocations (page 53). • Use connection pooling and explicitly close each java.sql.Connection object when it is no longer needed instead of relying on garbage collection.
Using the EXPLAIN function with SPJ RS You can also prepare the CALL statement and return result sets from the result table of the EXPLAIN function, as shown: PREPARE S FROM CALL samdbcat.sales.order_summary(?, ?); SELECT DESCRIPTION FROM TABLE(EXPLAIN(NULL, 'S')) WHERE OPERATOR = 'CALL'; The SELECT statement displays the following output: DESCRIPTION --------------------------------------------------------------------------parameter_modes: I O routine_name: SAMDBCAT.SALES.
AND partnum = 5100), 'standard', ?param); The generated shape describes all subquery trees and contains an ANYTHING token to represent the CALL statement, as shown: control query shape nested_join(nested_join(sort_groupby(partition_access( scan(path 'SAMDBCAT.SALES.ODETAIL', forward, blocks_per_access 1 , mdam off))),tuple),anything); --- SQL operation complete. If the result of the SHOWSHAPE command is undesirable, you can use the CONTROL QUERY SHAPE statement to force the execution plan.
A Sample SPJs This appendix presents the SPJs that are shown in examples throughout this manual. For descriptions of the SPJ methods and the CREATE PROCEDURE statements that register those methods in an SQL/MX database, see: • Class Files and Java Methods (page 106) • The createprocs.sql File (page 111) • Examples of the Sample SPJs (page 113) Using Sample SPJs The SPJ methods are coded in Java source files, which are located in a JAR file named Sam pleSPJs.jar in the NonStop Technical Library (NTL).
Figure 11 Steps for Using Sample SPJs For details of these steps, see the README file in SampleSPJs.jar. Class Files and Java Methods The SPJ methods are stored in these class files: • Sales Class (page 107) • Payroll Class (page 109) • Inventory Class (page 110) These class files are coded as Java source files and use JDBC/MX to access a NonStop SQL database. For information about JDBC/MX, see the JDBC Driver for SQL/MX Programmer's Reference.
Sales Class The Sales class contains these SPJ methods, which are useful for tracking orders and managing sales: • The lowerPrice()method determines which items are selling poorly (that is, have less than 50 orders) and lowers the price of these items in the database by 10 percent. • The numDailyOrders() method accepts a date and returns the number of orders on that date to an output parameter.
Example 1 Sales.java—The Sales Class import java.sql.*; import java.math.*; public class Sales { public static void lowerPrice() throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement getParts = conn.prepareStatement("SELECT p.partnum, " + " SUM(qty_ordered) AS qtyOrdered" + "FROM samdbcat.sales.parts p "+ "LEFT JOIN samdbcat.sales.odetailo " + " ON p.partnum = o.partnum "+ "GROUP BY p.partnum"); PreparedStatement updateParts = conn.
"using a number from 1 to 12 " + "to represent the month.", "38001"); } Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement getNumOrders = conn.prepareStatement("SELECT COUNT(month(order_date))" + "FROM samdbcat.sales.orders "+ "WHERE month(order_date) = ?"); getNumOrders.setInt(1, month); ResultSet rs = getNumOrders.executeQuery(); rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.
Example 2 Payroll.java-The PayrollClass import java.sql.*; import java.math.*; 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 samdbcat.persnl.employee" + "SET salary = salary * (1 + (?/ 100)) " + "WHERE empnum = ?"); PreparedStatement getSalary = conn.
The Inventory.java source file in SampleSPJs.jar contains the code shown in Example 3. Example 3 Inventory.java-The Inventory Class import java.sql.*; import java.math.*; public class Inventory { public static void supplierInfo(BigDecimal suppNum, String[] suppName, String[] streetAddr, String[] cityName, String[] stateName, String[] postCode) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement getSupplier = conn.
Example 4 createprocs.sql-An OBEY Command File CREATE PROCEDURE samdbcat.sales.lowerprice() EXTERNAL NAME 'Sales.lowerPrice' EXTERNAL PATH '' LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA; CREATE PROCEDURE samdbcat.sales.dailyorders(IN DATE, OUT number INT) EXTERNAL NAME 'Sales.numDailyOrders' EXTERNAL PATH '' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT, OUT number INT) EXTERNAL NAME 'Sales.
OUT maxm INT) EXTERNAL NAME 'Inventory.supplyQuantities' EXTERNAL PATH '' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; Before executing the OBEY command file, createprocs.sql, change the external paths from to the OSS directories that contain the SPJ class files. The catalog and schemas of stored procedures must exist before you issue CREATE PROCEDURE statements. The CREATE PROCEDURE statements in createprocs.
} rs.close(); conn.close(); } CREATE PROCEDURE Statement CREATE PROCEDURE samdbcat.sales.lowerprice() EXTERNAL NAME 'Sales.lowerPrice' EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA; CALL Statement to Invoke the SPJ To invoke the LOWERPRICE procedure in MXCI: CALL samdbcat.sales.
DAILYORDERS Stored Procedure Java Method 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 samdbcat.sales.orders " + "WHERE order_date = ?"); getNumOrders.setDate(1, date); ResultSet rs = getNumOrders.executeQuery(); rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.
PreparedStatement getNumOrders = conn.prepareStatement("SELECT COUNT(month(order_date)) "+ "FROM samdbcat.sales.orders " + "WHERE month(order_date) = ?"); getNumOrders.setInt(1, month); ResultSet rs = getNumOrders.executeQuery(); rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.close(); } CREATE PROCEDURE Statement CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT, OUT number INT) EXTERNAL NAME 'Sales.
"'economy' for 7 to 9 days," + "'standard' for 3 to 5 days, or " + "'nextday' for one day.", "38002" ); BigDecimal subtotal = price[0].multiply(qtyOrdered); BigDecimal tax = new BigDecimal(0.0825); BigDecimal taxcharge = subtotal.multiply(tax); BigDecimal charges = taxcharge.add(shipcharge); price[0] = subtotal.add(charges); } CREATE PROCEDURE Statement CREATE PROCEDURE samdbcat.sales.totalprice(IN qty NUMERIC(18), IN rate VARCHAR(10), INOUT price NUMERIC(18,2)) EXTERNAL NAME 'Sales.
ResultSet rs = getSalary.executeQuery(); rs.next(); newSalary[0] = rs.getBigDecimal(1); rs.close(); conn.close(); } CREATE PROCEDURE Statement CREATE PROCEDURE samdbcat.persnl.adjustsalary(IN empnum NUMERIC(4), IN percent FLOAT, OUT newsalary NUMERIC(8,2)) EXTERNAL NAME 'Payroll.adjustSalary' EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA; CALL Statement to Invoke the SPJ To invoke the ADJUSTSALARY procedure in MXCI: CALL samdbcat.persnl.adjustsalary(29, 2.
EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; CALL Statement to Invoke the SPJ To invoke the EMPLOYEEJOB procedure in MXCI: CALL samdbcat.persnl.employeejob(337, ?); The EMPLOYEEJOB procedure accepts the employee number 337 and returns this output in MXCI: JOBCODE ----------900 --- SQL operation complete. The job code for employee number 337 is 900.
CALL Statement to Invoke the SPJ To invoke the SUPPLIERINFO procedure in MXCI: CALL samdbcat.invent.supplierinfo(25, ?, ?, ?, ?, ?); The SUPPLIERINFO procedure accepts the supplier number 25 and returns this output in MXCI: SUPPNAME ------------------ ADDRESS ---------------------- CITY -------------- STATE ZIPCODE ------------ ---------- Schroeder's Ltd 212 Strasse Blvd West Hamburg RhodeIsland 22222 --- SQL operation complete. Supplier number 25 is Schroeder's Ltd.
ORDERSUMMARY Stored Procedure Java Method public static void orderSummary(java.lang.String onOrAfter, long[] numOrders, java.sql.ResultSet[] orders, java.sql.ResultSet[] detail) throws SQLException { java.lang.String s; java.sql.Connection c = DriverManager.getConnection("jdbc:default:connection"); // Get the number of orders on or after this date s = " SELECT count(ordernum) FROM sales.orders " + " WHERE order_date >= cast(? as date) "; java.sql.PreparedStatement ps1 = c.prepareStatement(s); ps1.
ISOLATE ; --- SQL operation complete. CALL Statement to Invoke the SPJ CALL samdbcat.sales.order_summary('2001-01-01', ?); The ORDER_SUMMARY procedure accepts the date and returns this output in MXCI: NUM_ORDERS -------------------13 ORDERNUM NUM_PARTS AMOUNT Order/Date ---------- -------------- --------------- ---------100210 4 19020.00 2003-04-10 100250 4 22625.00 2003-01-23 101220 4 45525.00 2003-07-21 200300 3 52000.00 2003-02-06 200320 4 9195.00 2003-02-17 200490 2 1065.00 2003-03-19 . . .
Index Symbols -Dsqlmx.udr.extensions option, 43 -Dsqlmx.udr.
Granting privileges on an SPJ, 83 Grantor, displaying information about, 90 I IN parameter mode, 61 IN parameters, 73 Initialization, SPJ environment, 26 INOUT parameter mode, 61 INOUT parameters, 74 Input descriptors input and output descriptors, 77 Internal SPJ, registering an SPJ, 21 Internal type signature Compressed Java signature, 65 Inventory class, 110 Invoking SPJs description of, 71 embedded SQL program in C, C++, or COBOL, 76, 77 examples of, 113 internal process, 22 JDBC/MX program, 81 MXCI, 74
description of, 107 O OBEY command files, 94 OBJECTS system metadata table, 22 ODBC/MX client invoking SPJs, 80 setting JREHOME, 40 setting the class path, 44 OUT parameter mode, 61 OUT parameters, 74 Output descriptors, 77 Output parameter array, 50 Output parameter, SPJ method, 50 Overloading of procedure names overloading of, 61 Ownership, displaying information about, 90 P Parameter array, 50 Parameter descriptors, retrieving information about, 79 Parameter mode, IN, OUT, or INOUT, 61 Parameters, SQL
diagram of, 24 initialization, 26 invoking an SPJ, 23 loading Java classes, 28 registering an SPJ, 21 server process, 25 SQLException, 57 Static CALL statement, 76 Static Java variables, 52 Stored procedure in Java (SPJ), 68, 88, 97 argument list, 73 benefits of, 19 description of, 17 granting privileges on, 83 invoking, 22, 71 naming, 60 on systems running H-series RVUs, 35 registering, 21, 59 revoking privileges from, 85 SQL/MX processing of, 21, 23 steps for using, 18 syntax, 92 Stored procedure result s
