HP NonStop SQL/MX Guide to Stored Procedures in Java Abstract This manual explains how to use stored procedures in Java (SPJs) within an HP NonStop™ SQL/MX database. Product Version NonStop SQL/MX Releases 2.0, 2.1, 2.2, and 2.3. Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs and H06.04 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
Document History Part Number Product Version Published 523727-001 NonStop SQL/MX Release 2.0 April 2004 523727-002 NonStop SQL/MX Release 2.0 August 2004 523727-003 NonStop SQL/MX Releases 2.0 and 2.1 June 2005 540433-001 NonStop SQL/MX Releases 2.0, 2.1, and 2.2 February 2006 540433-003 NonStop SQL/MX Releases 2.0, 2.1, 2.2, and 2.
Legal Notices © Copyright 2008 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.
HP NonStop SQL/MX Guide to Stored Procedures in Java Index Examples Figures Tables Legal Notices What’s New in This Manual vii Manual Information vii New and Changed Information vii About This Manual ix Audience ix Organization ix Examples in This Manual x Related Documentation xi Notation Conventions xv 1.
2. Getting Started Contents 2.
3. Writing SPJ Methods (continued) Contents 3. Writing SPJ Methods (continued) Use of java.sql.Connection Objects 3-7 JDBC/MX-Based Java Method 3-8 Referring to Database Objects in an SPJ Method Exception Handling 3-11 Handling Java Exceptions 3-11 User-Defined Exceptions 3-11 Writing Data to a File or Terminal 3-13 Compiling Java Classes 3-14 3-8 4.
Contents 5. Invoking SPJs in NonStop SQL/MX (continued) 5.
7. Performance and Troubleshooting (continued) Contents 7. Performance and Troubleshooting (continued) Displaying the Shape of a CALL Statement 7-5 CALL Statements Without a Shape 7-5 CALL Statements With a Shape 7-5 A. Sample SPJs Using Sample SPJs A-1 Installing the SampleSPJs.jar File A-1 Installing the SQL/MX Sample Database A-1 Class Files and Java Methods A-3 Sales Class A-3 Payroll Class A-7 Inventory Class A-9 The createprocs.
Figures (continued) Contents Figures (continued) Figure 1-7. Figure 1-8. Figure 6-1. Figure 6-2. Figure A-1. Java Classes That an SPJ Can Access 1-17 Copies of Java Classes in SPJ Class Loaders 1-19 Copying Java Classes to the Remote Node 6-20 Specifying the Node in the External Path 6-22 Steps for Using Sample SPJs A-2 Tables Table 2-1. Table 3-1. Table 4-1.
What’s New in This Manual Manual Information HP NonStop SQL/MX Guide to Stored Procedures in Java Abstract This manual explains how to use stored procedures in Java (SPJs) within an HP NonStop™ SQL/MX database. Product Version NonStop SQL/MX Releases 2.0, 2.1, 2.2, and 2.3. Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs and H06.04 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
Changes to the 540433-001 Manual What’s New in This Manual • • • • Added the section Using MXCI with SPJ RS on page 5-7. Added the section Output of the SHOWDDL Command of an SPJ with Result Sets on page 6-13. Added the section Using the EXPLAIN function with SPJ RS on page 7-4. Added a sample SPJ ORDERSUMMARY Stored Procedure on page A-23.
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, 2.3 and subsequent releases until otherwise indicated in a replacement publication.
Examples in This Manual About This Manual Section 6, Managing SPJs in NonStop SQL/MX Describes management tasks related to SPJs, such as granting privileges for invoking SPJs, showing the SPJs that exist in a database, and using SPJs in a distributed database environment. Section 7, Performance and Troubleshooting Describes how to improve and monitor the performance of SPJs and provides guidelines for troubleshooting common problems.
Related Documentation About This Manual Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals, which includes: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual Installation Guides SQL/MX Installation and Management Guide NSM/web Installation Guide Connectivity Manuals SQL/MX Connectivity Service Manual Migration Guides SQL/MX Database and Application Migration Guide
Related Documentation About This Manual Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between NonStop SQL/MP and NonStop SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI). Includes information about installing the sample database. Reference Manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements.
Related Documentation About This Manual Data Management Guides SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process. SQL/MX Report Writer Guide Describes how to produce formatted reports using data from an SQL/MX database. DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases.
Related Documentation About This Manual These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
Notation Conventions About This Manual 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 on page 3-2. General Syntax Notation The following list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
General Syntax Notation About This Manual each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list may be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
Notation for Messages About This Manual Line Spacing. If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] LINE [ , attribute-spec ]… !i and !o.
Notation for Messages About This Manual Nonitalic text. Nonitalic letters, numbers, and punctuation indicate text that is displayed or returned exactly as shown. For example: Backup Up. lowercase italic letters. Lowercase italic letters indicate variable items whose values are displayed or returned. For example: p-register process-name [ ] Brackets. Brackets enclose items that are sometimes, but not always, displayed.
Notation for Management Programming Interfaces About This Manual 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.
About This Manual Notation for Management Programming Interfaces HP NonStop SQL/MX Guide to Stored Procedures in Java —540433-003 xx
1 Introduction This section introduces stored procedures in Java (SPJs) in NonStop SQL/MX and covers these topics: • • • • • What Is an SPJ? on page 1-1 How Do I Use SPJs? on page 1-2 Benefits of SPJs on page 1-4 SPJs in NonStop SQL/MX on page 1-6 The SPJ Environment on page 1-12 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.
Introduction How Do I Use SPJs? PROCEDURE, registers a Java method as a stored procedure in the database by storing its name, parameter types, location, and other metadata in SQL/MX system metadata tables. An SPJ method must be registered in NonStop SQL/MX before an SQL/MX application can call it. For more information about how SPJs operate in NonStop SQL/MX, see SPJs in NonStop SQL/MX on page 1-6. Note.
Introduction How Do I Use SPJs? 3. Configure the SPJ environment before registering or invoking SPJs: • • • • • If you installed the NonStop Server for Java in a nonstandard location, set the JREHOME location. See Setting the JREHOME Location on page 2-13. If you installed the JDBC/MX driver in a nonstandard location, set the UDR extensions class path. See Setting the JDBC/MX Location on page 2-16.
Introduction Benefits of SPJs 7. Maintain privileges and manage the metadata of SPJs in the database environment. See Section 6, Managing SPJs in NonStop SQL/MX. 8. Monitor the performance of SPJs and resolve common problems with SPJs in NonStop SQL/MX. See Section 7, Performance and Troubleshooting. Benefits of SPJs SPJs provide an efficient and secure way to implement business logic in an SQL/MX database. SPJs offer the advantages discussed next.
Introduction 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 tables that the SPJ methods access.
Introduction SPJs in NonStop SQL/MX 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 on page 1-6 Effect of Invoking an SPJ on page 1-9 Invoking Different Types of SPJs on page 1-10 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 store
Introduction Effect of Registering an SPJ 2. The catalog manager within the MXCMP process invokes an internal SPJ named VALIDATEROUTINE2, which is in SYSTEM_SQLJ_SCHEMA, and passes information from the CREATE PROCEDURE statement to it. 3. VALIDATEROUTINE2 validates the Java class and method specified in the CREATE PROCEDURE statement within an SQL/MX UDR server process (shown as the MXUDR executable in Figure 1-2 on page 1-6).
Introduction Effect of Registering an SPJ The procedure label is used internally by NonStop SQL/MX to track privileges on an SPJ. For more information, see Showing the Procedure Label on page 6-11.
Introduction Effect of Invoking an SPJ Effect of Invoking an SPJ When an application issues a CALL statement, the Java method of the invoked SPJ executes inside a JVM in an SQL/MX UDR server process. Figure 1-3 shows how SQL/MX processes a CALL statement. Figure 1-3. Invoking an SPJ Application NonStop SQL/MX 1 CALL... 2 SQL Plan Procedure Label 3 5 MXUDR JVM 4 /usr/mydir/Payroll.class VST003.vsd 1. An SQL/MX application invokes an SPJ by issuing a CALL statement.
Introduction Invoking Different Types of SPJs Invoking Different Types of SPJs The next subsections show the layers of code within an SQL/MX UDR server process and how these layers interact when an application issues CALL statements that invoke different types of SPJ methods. Pure Java Method An SPJ can be based on a Java method from a pure Java program (that is, one that does not contain JDBC/MX method calls). A pure Java method does not directly perform database access operations.
Introduction Invoking Different Types of SPJs 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: SQL/MX UDR Server Process Application CALL... NonStop SQL/MX SQL/MX Language Manager NonStop Server for Java JVM Class File Payroll.
Introduction The SPJ Environment The SPJ Environment NonStop SQL/MX processes CALL statements in an SPJ environment, which is hosted within an SQL/MX UDR server process. The SPJ environment includes an embedded JVM inside of which SPJ methods, invoked by CALL statements, execute, as shown in Figure 1-4. Figure 1-4. SPJ Environment in NonStop SQL/MX SQL/MX UDR Server Process Application CALL... NonStop SQL/MX SQL/MX Language Manager NonStop Server for Java JVM Class File Payroll.
Introduction SQL/MX UDR Server Process Single SQL/MX UDR Server Process Per Application An SQL/MX application that does not switch user identities and that uses only one UDR_JAVA_OPTIONS setting has only one SQL/MX UDR server process service the application. All the CALL statements in such an application are processed in one SPJ environment. The UDR_JAVA_OPTIONS default attribute controls the JVM startup options in an SPJ environment. For more information, see Controlling JVM Startup Options on page 2-6.
Introduction • SQL/MX UDR Server Process The CALL statement has a UDR_JAVA_OPTIONS setting (other than ANYTHING) that is the same as the UDR_JAVA_OPTIONS setting in one or more SPJ environments that are servicing the application under the current user identity. See Determining the Uniqueness of UDR_JAVA_OPTIONS Settings on page 2-11. • The CALL statement has a UDR_JAVA_OPTIONS setting of ANYTHING, and one or more SPJ environments are currently servicing the application under the current user identity.
Introduction Multithreading in an SPJ Environment Multithreading in an SPJ Environment NonStop SQL/MX 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. In releases earlier than NonStop SQL/MX Release 2.
Introduction Class Loaders in an SPJ Environment classes that are used by those SPJ classes. The SPJ class loader, instead of the system class loader, loads application classes from directories and JAR files specified in the class path. In Figure 1-6, an SPJ method named myMethod3() in the /usr/myapps/myJar.jar external path accesses an application class named other.class in the /usr/otherapps class path. The SPJ class loader for the /usr/myapps/myJar.jar external path loads a copy of other.
Introduction Application Classes That an SPJ Can Access 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 1-7. Figure 1-7. Java Classes That an SPJ Can Access Application Classes in the External Path Java Class File: myClass.class '/usr/myapps/myJar.jar' SPJ Method EXTERNAL NAME: 'pkg.subpkg.myClass.myMethod()' Refers to...
Introduction Maintaining Class and JAR Files in an SPJ Environment During the execution of a CALL statement, if an SPJ method refers to an application class, an SPJ class loader first searches the external path for the class and, if the class is not found, it searches the class path. For more information, see Setting the Class Path on page 2-18. Maintaining Class and JAR Files in an SPJ Environment You should not modify class and JAR files that are used by SPJs while active SPJ environments exist.
Introduction Maintaining Class and JAR Files in an SPJ Environment Figure 1-8. Copies of Java Classes in SPJ Class Loaders SQL/MX UDR Server Process SPJ Environment Each SPJ class loader loads its own copy of an application class. CALL... SPJA SPJ Class Loader X for External Path Loads... '/usr/myapps' EXTERNAL NAME: 'pkg1.subpkg1.myClass1.myMethod1()' EXTERNAL PATH: '/usr/myapps' Refers to... Copy of Application Class other.class Application Class other.
Introduction Maintaining Class and JAR Files in an SPJ Environment HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003 1- 20
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.
Getting Started NonStop Server for Java Table 2-1. Software Products Required for SPJs (page 2 of 2) SQL/MX Product Version Minimum Supported RVU NonStop Server for Java* JDBC/MX Driver* Relinking MXUDR Required? NonStop SQL/MX Release 2.1 H06.04 T006H10 (SDK 1.4.2) T1275H10 (equivalent to T1225V32) No NonStop SQL/MX Release 2.2 H06.04 T006H10 (SDK 1.4.2) T1275H10 AAA (equivalent to T1225V32) No NonStop SQL/MX Release 2.3 H06.16 T2766 H50 (SDK 1.5.
Getting Started • • • Verifying the SQL/MX UDR Server Verifying the SQL/MX UDR Server on page 2-3 Verifying the NonStop Server for Java on page 2-3 Verifying the JDBC/MX Driver on page 2-4 Verifying the SQL/MX UDR Server To display the product version of the SQL/MX UDR server, enter this vproc command at an OSS prompt: vproc /G/SYSTEM/SYSTEM/MXUDR The command displays output similar to: VPROC - T9617H01 - (01 OCT 2004) SYSTEM \DRP24 Date 14 NOV 2005, 11:29:19 Copyright 2004 Hewlett-Packard Development C
Getting Started Verifying the JDBC/MX Driver The command displays output similar to: VPROC - T9617H01 - (01 OCT 2004) SYSTEM \DRP24 Date 14 NOV 2005, 11:32:33 Copyright 2004 Hewlett-Packard Development Company, L.P.
Getting Started Configuring the SPJ Environment on Systems Running H-Series RVUs 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.
Getting Started UDR Extensions Class Path for the JDBC/MX JAR File 3. Let the JVM of the SQL/MX UDR server use the standard location of the JDBC/MX native library, which the SQL/MX UDR server automatically appends to the java.library.path property. To use UDR_JAVA_OPTIONS, see Controlling JVM Startup Options on page 2-6. To use the _RLD_LIB_PATH environment variable, see the NonStop Server for Java Programmer’s Reference for SDK 1.4.2.
Getting Started JVM Startup Options for Each SPJ Caller VALUES ('UDR_JAVA_OPTIONS', '-Djava.class.path=/usr/myclasses -Xmx32M'); The UDR_JAVA_OPTIONS setting in the SYSTEM_DEFAULTS table overrides the system-defined default setting, which is OFF. For more information, see the SQL/MX Reference Manual.
Getting Started JVM Startup Options for Each SPJ Caller Scope of JVM Startup Options in an SPJ Method A UDR_JAVA_OPTIONS setting within an SPJ method does not affect the SPJ environment in which the SPJ is executing. It affects only the SPJ environment of CALL statements that the SPJ method executes. In general, avoid nesting CALL statements in an SPJ. For more information, see Nested Java Method Invocations on page 3-7.
Getting Started JVM Startup Options for Each SPJ Caller For example, this CONTROL QUERY DEFAULT statement in an embedded SQL program in C sets application-specific system properties for the SPJ environment of the dynamically prepared CALL statement named sqlstmt1: /* Build CALL statements in char buffers. */ strcpy(hv_sql_stmt1, "CALL samdbcat.sales.monthlyorders(?,?)"); /* IN, OUT */ strcpy(hv_sql_stmt2, "CALL samdbcat.persnl.
Getting Started Using Multiple UDR_JAVA_OPTIONS Settings in One Application 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.
Getting Started Using Multiple UDR_JAVA_OPTIONS Settings in One Application If you specify different UDR_JAVA_OPTIONS settings in the same application, be aware of the Performance Considerations of Using Multiple UDR_JAVA_OPTIONS Settings on page 2-11. Determining the Uniqueness of UDR_JAVA_OPTIONS Settings Two or more UDR_JAVA_OPTIONS settings are considered to be different when their character string literals do not match during a case-sensitive string comparison.
Getting Started Using Multiple UDR_JAVA_OPTIONS Settings in One Application different set of UDR_JAVA_OPTIONS in an application. Each SQL/MX UDR server process contains its own embedded JVM. Caution. 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 on page 1-12.
Getting Started Setting the JREHOME Location Setting the JREHOME Location By default, NonStop SQL/MX loads Java components from the standard location of the NonStop Server for Java into the SPJ environment. If you install the NonStop Server for Java in a nonstandard location, you must set the JREHOME location to the current installation directory of the NonStop Server for Java.
Getting Started Setting the JREHOME Environment Variable on page 1-12. For example, use a CONTROL QUERY DEFAULT statement to set the JREHOME location before a CALL statement in an application: CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS '-Dsqlmx.udr.jrehome=/usr/myjavadir/jre'; CALL... A JREHOME setting inserted into the SYSTEM_DEFAULTS table affects all CALL or CREATE PROCEDURE statements that run on the system.
Getting Started Setting the JREHOME Environment Variable 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.
Getting Started Setting the JDBC/MX Location Setting the JDBC/MX Location 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. In some cases, you might want to use another version of JDBC/MX that is compatible with NonStop SQL/MX but that is not installed in the standard location. To use jdbcMx.jar in a nonstandard location, you must set the UDR extensions class path to the JAR file path of jdbcMx.jar.
Getting Started Setting the JDBC/MX Location by Using UDR_JAVA_OPTIONS The jdbcmx-jar-filepath is the JAR file path of jdbcMx.jar. 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 on page 1-12.
Getting Started Setting the Class Path Setting the Class Path The class path contains an ordered list of directories and JAR files in which to search for Java class files. If an SPJ method refers to another application class (for example, loads a class, creates an object of a class, or invokes a method of another class), the other class must either be in the same external path location as the SPJ class or have its location specified in the class path.
Getting Started Setting the Class Path by Using UDR_JAVA_OPTIONS 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. 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.
Getting Started Setting the CLASSPATH Environment Variable Setting the CLASSPATH Environment Variable Use the CLASSPATH environment variable to set the class path in an SPJ environment for a session in which applications that call or create SPJs run. Set the CLASSPATH environment variable in either the OSS or Guardian environment, depending on where you run applications that issue CALL or CREATE PROCEDURE statements. Note.
Getting Started Installing JAR Files in NonStop SQL/MX 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.
Getting Started Establishing Java Security Establishing Java Security Java has built-in security features, such as the Java security manager, that protect against unauthorized use of or access to the system. With Java security enabled, the Java security manager protects the SPJ environment by restricting access to system resources.
Getting Started SPJ Policy File and Required Permissions SPJ Policy File and Required Permissions The default SPJ policy file, mxlangman.policy in the /usr/tandem/sqlmx/udr directory, contains these permissions: grant codeBase "file:/usr/tandem/sqlmx/udr/mxlangman.jar" { permission java.security.AllPermission; }; grant codeBase "file:/usr/tandem/jdbcMx/current/lib/jdbcMx.jar" { permission java.security.AllPermission; }; You can use the default SPJ policy file, mxlangman.
Getting Started SPJ Policy File and Required Permissions Permissions for the Java System and Extension Classes Java system classes, such as Java core API classes, and standard extension packages are granted permissions by the default, system-wide Java policy file, java.policy, not the SPJ policy file. The Java core API classes in the NonStop Server for Java are always granted all permissions. Extension packages in javainstallation-directory/jre/lib/ext are typically granted all permissions.
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.
Writing SPJ Methods Returning Output Values From the Java Method Java Parameters 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 Table 3-1. Table 3-1. Mapping of Java Data Types to SQL/MX Data Types Java Data Type Maps to SQL/MX Data Type... java.lang.String CHAR[ACTER] CHAR[ACTER] VARYING VARCHAR PIC[TURE] X NCHAR NCHAR VARYING NATIONAL CHAR[ACTER] NATIONAL CHAR[ACTER] VARYING java.sql.
Writing SPJ Methods Returning Output Values From the Java Method 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.
Writing SPJ Methods Returning Output Values From the Java Method support SPJs and for cross-references to the appropriate manuals for type mappings between NonStop SQL/MX and the API. Stored Procedure Result Sets SQL/MX 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).
Writing SPJ Methods Using the main() Method // Open a result set for rows s = " SELECT " FROM " AMOUNTS.*, ORDERS.order_date, EMPS.last_name " + ( select o.ordernum, count(d.partnum) as num_parts, " + sum(d.unit_price * d.qty_ordered) as amount " + " from sales.orders o, sales.odetail d " + " where o.ordernum = d.ordernum " + " and o.order_date >= cast(? as date) " group by o.ordernum ) AMOUNTS, " + " + " sales.orders ORDERS, persnl.
Writing SPJ Methods Null Input and Output must have the character string data type, CHAR or VARCHAR, and be declared with the IN mode. If you specify the optional Java signature in the EXTERNAL NAME clause of the CREATE PROCEDURE statement, the signature must be (java.lang.String []). For more information about creating an SPJ, see the Using the CREATE PROCEDURE Statement on page 4-2.
Writing SPJ Methods Nested Java Method Invocations Nested Java Method Invocations An SPJ that invokes another SPJ by issuing a CALL statement causes an additional SQL/MX UDR server process to be created. Nesting SQL/MX UDR server processes in this way wastes resources and diminishes performance. If you want an SPJ method to call another SPJ method, invoke the other Java method directly through Java instead of using a CALL statement.
Writing SPJ Methods JDBC/MX-Based Java Method JDBC/MX-Based Java Method A JDBC/MX-based Java method is from a Java program that contains SQL/MX statements in JDBC/MX method calls. You can use this type of method to create an SPJ that performs SQL operations on an SQL/MP or SQL/MX database.
Writing SPJ Methods Referring to Database Objects in an SPJ Method objects have Guardian file names that include the node, volume, subvolume, and file name. Typically, SQL/MP aliases, which are three-part logical names, are used to refer to SQL/MP database objects. For more information about database object names, see the SQL/MX Reference Manual.
Writing SPJ Methods Referring to Database Objects in an SPJ Method as the SPJ. For example, this SPJ method, which is registered as an SPJ in the SAMDBCAT.SALES schema, refers to the unqualified database object, ORDERS: public static void numDailyOrders(Date date, int[] numOrders) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); PreparedStatement getNumOrders = conn.prepareStatement("SELECT COUNT(order_date) " + "FROM orders " + "WHERE order_date = ?"); getNumOrders.
Writing SPJ Methods Exception Handling Be aware that overriding the default values by using getConnection() or UDR_JAVA_OPTIONS requires you to hard-code the catalog and schema values and might make SPJ methods less portable across systems. Exception Handling For SPJ methods that access an SQL/MP or SQL/MX database, no special code is necessary for handling exceptions.
Writing SPJ Methods User-Defined Exceptions For information about specific SQL/MX errors, see the SQL/MX Messages Manual, which lists the SQLCODE, SQLSTATE, message text, and cause-effect-recovery information for all SQL/MX errors.
Writing SPJ Methods Writing Data to a File or Terminal Writing Data to a File or Terminal Within the SPJ environment, data sent to the System.out and System.err output streams goes nowhere by default because the SQL/MX UDR server runs without a terminal. You can add code to your SPJ methods to write data or debugging information to an OSS file. You can also map one or both of the System.out and System.err streams to a file.
Writing SPJ Methods Compiling Java Classes // This message goes to the output stream. System.out.println("The salary was updated for employee " + empnum); // This message goes to the error stream. System.err.println("The salary was not updated for employee " + empnum); ... } When a CALL statement invokes the SPJ method, a message is written to the file /usr/mydir/spj.output.
4 Registering SPJs in NonStop SQL/MX This section covers these topics: • • • • Creating an SPJ on page 4-1 Using the CREATE PROCEDURE Statement on page 4-2 Dropping an SPJ on page 4-13 Altering an SPJ and Its Java Class on page 4-14 This section assumes that you have already written and compiled the SPJ methods. For more information, see Section 3, Writing SPJ Methods. Creating an SPJ The CREATE PROCEDURE statement registers an existing Java method as an SPJ within an SQL/MX database.
Registering SPJs in NonStop SQL/MX Using the CREATE PROCEDURE Statement PROCEDURE statement fails to execute, NonStop SQL/MX does not update system metadata tables and does not create a procedure label. For more information, see the Effect of Registering an SPJ on page 1-6. Using the CREATE PROCEDURE Statement This subsection explains how to write a CREATE PROCEDURE statement.
Registering SPJs in NonStop SQL/MX Naming the Stored Procedure Naming the Stored Procedure Choose the name of the procedure and specify it in the form of an ANSI logical name, as shown: CREATE PROCEDURE samdbcat.persnl.adjustsalary ... The procedure name you select is used in the CALL statements that invoke the SPJ and identifies the underlying SPJ method.
Registering SPJs in NonStop SQL/MX Specifying SQL Parameters Specifying SQL Parameters If the SPJ method does not accept arguments, like the lowerPrice() method, specify the procedure name with empty parentheses, as shown: samdbcat.sales.lowerprice() If the SPJ method accepts arguments, specify the corresponding SQL parameters.
Registering SPJs in NonStop SQL/MX Specifying SQL Parameters The identifier conforms to the syntax of other SQL identifiers. For information about identifiers, 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: SPJ Method adjustSalary(BigDecimal empNum, double percent, BigDecimal[] newSalary) Stored Procedure adjustsalary(IN empnum NUMERIC(4), IN percent FLOAT, OUT newsalary NUMERIC(8,2)) VST018.
Registering SPJs in NonStop SQL/MX Specifying SQL Parameters Table 4-1. Mapping of SQL/MX Data Types to Java Data Types Ext Ext SQL/MX Data Type Maps to Java Data Type... CHAR[ACTER]* CHAR[ACTER] VARYING * VARCHAR* PIC[TURE] X * NCHAR NCHAR VARYING NATIONAL CHAR[ACTER] NATIONAL CHAR[ACTER] VARYING java.lang.String DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp NUMERIC ** DEC[IMAL]** PIC[TURE] S9 java.math.BigDecimal SMALLINT** short INT[EGER]** int (or java.lang.
Registering SPJs in NonStop SQL/MX Specifying the Maximum Number of Result Sets If the SPJ method passes character string values to or from an SQL/MP table that has a KANJI (Japanese) or KSC5601 (Korean) column, use the ISO88591 character set for the SQL parameter. For more information, see character sets in the SQL/MX Reference Manual.
Registering SPJs in NonStop SQL/MX Specifying the SPJ Method Specifying the SPJ Method Specify the external Java method to be used as the SPJ method in the EXTERNAL NAME clause, as shown: Class Name Method Name EXTERNAL NAME 'Payroll.adjustSalary' VST019.vsd The Java method that you specify in the EXTERNAL NAME clause must be defined as public and static and have a return type of void. For more information, see Guidelines for Writing SPJ Methods on page 3-1.
Registering SPJs in NonStop SQL/MX Specifying the SPJ Method Java signature because the SQL parameters (NUMERIC and FLOAT) map by default to the same Java data types (java.math.BigDecimal and double) as the parameters of the underlying Java method: EXTERNAL NAME 'Payroll.adjustSalary' 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: Java Method Signature EXTERNAL NAME 'Payroll.
Registering SPJs in NonStop SQL/MX Specifying the External Path BigDecimal[] newSalary) ... The Java signature of this method in the CREATE PROCEDURE statement, if you choose to specify it, is: (java.math.BigDecimal, double, java.math.BigDecimal[]) After you register the SPJ (that is, issue the CREATE PROCEDURE statement), you can display the compressed Java signature in the TEXT metadata table. Issue this query: SELECT SUBSTRING(TEXT,1,100) AS "Compressed Signature" FROM samdbcat.
Registering SPJs in NonStop SQL/MX Naming the Procedure Label public static void adjustSalary( BigDecimal empNum, double percent, BigDecimal[] newSalary ) ... The Java compiler generates this class file from the Java source file: /usr/mydir/myclasses/pkg/subpkg/Payroll.class The correct CREATE PROCEDURE clauses that refer to the adjustSalary() method in the class file are: EXTERNAL NAME 'pkg.subpkg.Payroll.
Registering SPJs in NonStop SQL/MX Specifying an SQL Access Mode For information about naming rules for the LOCATION clause, see the SQL/MX Reference Manual. Specifying an SQL Access Mode The SQL access mode indicates whether an SPJ performs SQL operations. Specifying the SQL access mode for an SPJ is entirely optional. If you do not specify the access mode in the CREATE PROCEDURE statement, the default is CONTAINS SQL, and the capability of the SPJ is MODIFIES SQL DATA.
Registering SPJs in NonStop SQL/MX Dropping an SPJ Dropping an SPJ The DROP PROCEDURE statement removes an SPJ from NonStop SQL/MX. You can issue a DROP PROCEDURE statement from any application or interface that calls NonStop SQL/MX, such as: • • • • • SQL/MX conversational interface (MXCI) Embedded SQL programs in C, C++ or COBOL JDBC/MX programs NonStop ODBC/MX clients NSM/web Required Privileges for Dropping an SPJ To issue a DROP PROCEDURE statement, you must own the SPJ or be the super ID.
Registering SPJs in NonStop SQL/MX Altering an SPJ and Its Java Class Altering an SPJ and Its Java Class To alter an SPJ, you must first drop the SPJ from system metadata by using the DROP PROCEDURE statement and then re-create the SPJ by using the CREATE PROCEDURE statement. Note. NonStop SQL/MX does not support the ALTER_JAVA_PATH procedure from SQL/JRT of the ANSI SQL/Foundation standard. To change the SPJ or the Java class of the SPJ method: 1.
Registering SPJs in NonStop SQL/MX Changes to the External Path Changes to the External Path The external path is the OSS directory or JAR file path of the Java class file that contains the SPJ method. If you change the external path of the underlying SPJ method, you must drop the associated SPJ and re-create it. For example, suppose that you move the class, Sales.class, which contains the SPJ method, numMonthlyOrders(), from the /usr/mydir/myclasses directory into a JAR file, myJar.jar.
Registering SPJs in NonStop SQL/MX Changes to the External Name Changes to the External Name The external name identifies the SPJ method that is registered as an SPJ. A change to the external name of an SPJ suggests either a reassignment of the SPJ method or a fundamental change to the underlying Java class of the existing SPJ method.
Registering SPJs in NonStop SQL/MX Changes to the Java Signature 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.
Registering SPJs in NonStop SQL/MX Changes to the Java Signature HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003 4- 18
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 Section 4, Registering SPJs in NonStop SQL/MX.
Invoking SPJs in NonStop SQL/MX Effect of Calling an SPJ Effect of Calling an SPJ When you issue a CALL statement, the SPJ method of the invoked SPJ executes inside a JVM within an SPJ environment. For more information, see the Effect of Invoking an SPJ on page 1-9. Caution. NonStop SQL/MX does not maintain the Java class file that contains the SPJ method. NonStop SQL/MX records only a reference to the location of the Java class file in system metadata.
Invoking SPJs in NonStop SQL/MX Using the CALL Statement Effect of the jdbcmx.transactionMode Property If you set the jdbcmx.transactionMode property in a UDR_JAVA_OPTIONS attribute, you might affect the transaction behavior of an SPJ. By default, an SPJ method runs in the default mixed transaction mode. The mixed and external transaction modes do not interfere with transaction behavior of the calling application. However, if you set jdbcmx.
Invoking SPJs in NonStop SQL/MX Listing the Arguments of the SPJ Listing the Arguments of the SPJ Each argument that you list in the CALL statement must correspond with the SQL parameter that you specified in the CREATE PROCEDURE statement. For example, if you registered the stored procedure with three SQL parameters (two IN parameters and one OUT parameter), you must list three formal parameters, separated by commas, in the CALL statement: CALL samdbcat.persnl.
Invoking SPJs in NonStop SQL/MX Listing the Arguments of the SPJ Type of Argument Examples Scalar subquery CALL totalprice((SELECT qty_ordered FROM odetail WHERE ordernum = 100210 AND partnum = 5100), 'nextday', ?param); Host variable CALL adjustsalary(:empnum, :percent, :OUT newsalary); Dynamic parameter CALL adjustsalary(?, ?, ?); CALL adjustsalary(?param1, ?param2, ?param3); Because an INOUT parameter passes a single value to and accepts a single value from an SPJ, you can specify only host var
Invoking SPJs in NonStop SQL/MX Invoking SPJs in MXCI Invoking SPJs in MXCI In MXCI, you can invoke an SPJ by issuing a CALL statement directly or by preparing and executing a CALL statement. Use MXCI named or unnamed parameters anywhere in the argument list of an SPJ invoked in MXCI. An MXCI named parameter is set by the SET PARAM command, and an MXCI unnamed parameter is set by the USING clause of the EXECUTE statement. You must use an MXCI parameter for an OUT or INOUT parameter argument.
Invoking SPJs in NonStop SQL/MX Using MXCI with SPJ RS The output of the prepared CALL statement is: PRICE --------------------136.77 --- SQL operation complete. In an MXCI 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: PREPARE stmt2 FROM CALL totalprice(?,?,?); EXECUTE stmt2 USING 3, 'economy', 16.
Invoking SPJs in NonStop SQL/MX Using MXCI with SPJ RS The output of the prepared call statement is: NUM_ORDERS -------------------13 ORDERNUM ---------- NUM_PARTS -------------- AMOUNT --------------- Order/Date ---------- Last Name ----------------- 100210 100250 101220 200300 200320 200490 . . . 4 4 4 3 4 2 19020.00 22625.00 45525.00 52000.00 9195.00 1065.00 2003-04-10 2003-01-23 2003-07-21 2003-02-06 2003-02-17 2003-03-19 HUGHES HUGHES SCHNABL SCHAEFFER KARAJAN WEIGL --- 13 row(s) selected.
Invoking SPJs in NonStop SQL/MX Invoking SPJs Statically in an Embedded SQL Program in C, C++, or COBOL Invoking SPJs Statically in an Embedded SQL Program in C, C++, or COBOL In an embedded SQL program in C, C++, or COBOL, you can invoke an SPJ in a statically compiled CALL statement. Place a statically compiled CALL statement within an EXEC SQL directive: C/C++ COBOL EXEC SQL CALL procedure-name ([parameter [{, parameter}...]]); EXEC SQL CALL procedure-name ([parameter [{, parameter}...
Invoking SPJs in NonStop SQL/MX Invoking SPJs Dynamically in an Embedded SQL Program in C, C++, or COBOL Invoking SPJs Dynamically in an Embedded SQL Program in C, C++, or COBOL In an embedded SQL program in C, C++, or COBOL, you can invoke an SPJ in a dynamically executed CALL statement. For dynamic CALL statements, supply dynamic input data either through an SQL descriptor area or by using host variables.
Invoking SPJs in NonStop SQL/MX Input and Output Descriptors number of formal OUT and INOUT parameters because each output parameter must be represented by a single dynamic parameter in the CALL statement. Example of Using Input and Output Descriptors To code an embedded SQL program to use a dynamic CALL statement with SQL descriptor areas: 1. Allocate input and output SQL descriptor areas.
Invoking SPJs in NonStop SQL/MX Input and Output Descriptors EXEC SQL DESCRIBE OUTPUT sqlstmt USING SQL DESCRIPTOR 'out_sda'; For the syntax of the DESCRIBE statement, see the SQL/MX Reference Manual. 4. Set the input parameter values by using SET DESCRIPTOR.
Invoking SPJs in NonStop SQL/MX Argument Lists 7. Retrieve parameter descriptor information by using GET DESCRIPTOR. Aside from obtaining data about the output dynamic parameters, you can also use the GET DESCRIPTOR statement to retrieve the PARAMETER_MODE and PARAMETER_ORDINAL_POSITION descriptor items for dynamic parameters.
Invoking SPJs in NonStop SQL/MX Invoking SPJs in a NonStop ODBC/MX Client Invoking SPJs in a NonStop ODBC/MX Client You can execute a CALL statement in a NonStop ODBC/MX client. 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 (?). Note. You cannot use an empty string as an IN or INOUT parameter in the argument list.
Invoking SPJs in NonStop SQL/MX Invoking SPJs in a JDBC/MX Program Invoking SPJs in a JDBC/MX Program You can execute a CALL statement in a JDBC/MX program by using the JDBC CallableStatement interface. JDBC/MX 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 settype() methods of the CallableStatement interface.
Invoking SPJs in NonStop SQL/MX Invoking SPJs in a JDBC/MX Program HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003 5- 16
6 Managing SPJs in NonStop SQL/MX This section covers management tasks related to SPJs in NonStop SQL/MX: • • • • • Granting Privileges for Invoking SPJs on page 6-1 Displaying Information About SPJs on page 6-7 Keeping SPJ Statements in OBEY Command Files on page 6-15 Backing Up SPJs on page 6-19 Using SPJs in a Distributed Database Environment on page 6-19 Granting Privileges for Invoking SPJs Security for SPJs is implemented by the schema ownership rules and by granting privileges to specified users.
Managing SPJs in NonStop SQL/MX Granting Privileges on an SPJ As the owner of an SPJ, or the super ID acting on behalf of a user with the WITH GRANT OPTION privilege, you can selectively grant the EXECUTE and WITH GRANT OPTION privileges to specified users. 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 number of users.
Managing SPJs in NonStop SQL/MX Granting Privileges on Referenced Database Objects After granting the EXECUTE privilege to PUBLIC, you cannot revoke the privilege from a subset of users. You must revoke the privilege from PUBLIC and then grant the privilege to specific users, excluding users who should not have the privilege.
Managing SPJs in NonStop SQL/MX Revoking Privileges on an SPJ the super ID acting on behalf of a grantor, you can revoke the EXECUTE and WITH GRANT OPTION privileges on the SPJ from any user to whom the grantor granted those privileges. If you are not the owner of the SPJ or the super ID, you must have been granted the WITH GRANT OPTION privilege to revoke privileges from other users, and you can revoke privileges only from other users to whom you have granted privileges.
Managing SPJs in NonStop SQL/MX Revoking Privileges on an SPJ behalf of the SPJ owner, revokes the WITH GRANT OPTION privilege from the HR director of North America: Database Administrator super ID WITH GRANT OPTION EXECUTE REVOKE GRANT OPTION FOR EXECUTE ON PROCEDURE samdbcat.persnl.adjustsalary FROM "HR.MGRNA"; HR Director North America HR Director Europe HR.MGRNA HR.MGREU WITH GRANT OPTION WITH GRANT OPTION EXECUTE EXECUTE Manager Marketing Dept. Manager Research Dept.
Managing SPJs in NonStop SQL/MX Revoking Privileges on an SPJ the HR director of North America and from the department managers to whom the HR director granted privileges: Database Administrator super ID WITH GRANT OPTION EXECUTE REVOKE EXECUTE ON PROCEDURE samdbcat.persnl.adjustsalary FROM "HR.MGRNA" CASCADE; HR Director North America HR Director Europe HR.MGRNA HR.MGREU EXECUTE WITH GRANT OPTION EXECUTE Manager Marketing Dept. Manager Research Dept. Manager Corporate Dept. MKG.RAYMOND RD.
Managing SPJs in NonStop SQL/MX Displaying Information About SPJs 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. For more information, see: • • • • Listing the SPJs in a Catalog on page 6-7 Showing Privileges on the SPJs on page 6-8 Showing the Procedure Label on page 6-11 Showing the Syntax of an SPJ on page 6-12 The first three topics suggest querying SQL/MX system metadata.
Managing SPJs in NonStop SQL/MX Showing Privileges on the SPJs The query returns the catalog, schema, and procedure names of all the SPJs in the catalog, as shown: CATALOG ------------------------- SCHEMA ------------------------- PROCEDURE -------------------- SAMDBCAT SAMDBCAT SAMDBCAT SAMDBCAT SAMDBCAT PERSNL SALES SALES SALES SALES ADJUSTSALARY DAILYORDERS LOWERPRICE MONTHLYORDERS TOTALPRICE --- 5 row(s) selected.
Managing SPJs in NonStop SQL/MX Showing Privileges on the SPJs 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. For the output, see Interpreting Privileges Information on page 6-10. 4.
Managing SPJs in NonStop SQL/MX Showing Privileges on the SPJs Interpreting Privileges Information The second query in Step 3 returns the grantees and the privileges they have on each SPJ in a catalog, as shown: SCHEMA -------- PROCEDURE -------------- PERSNL PERSNL PERSNL PERSNL PERSNL PERSNL SALES SALES SALES SALES SALES ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY ADJUSTSALARY DAILYORDERS LOWERPRICE MONTHLYORDERS MONTHLYORDERS TOTALPRICE GRANTEE -------2053 2069 2068 2055 2054 2
Managing SPJs in NonStop SQL/MX Showing the Procedure Label Showing the Procedure Label The procedure label is used internally by NonStop SQL/MX to track privileges on an SPJ. NonStop SQL/MX creates one procedure label for each SPJ. The procedure label must exist and be available for an SPJ to be invoked successfully. Knowing the locations of the procedure labels is particularly important when you are using SPJs in a distributed database environment.
Managing SPJs in NonStop SQL/MX Showing the Syntax of an SPJ The query returns the names of the schema, procedure, node (SYSTEM_NAME), volume (DATA_SOURCE), and the subvolume and file (FILE_SUFFIX), as shown: SCHEMA -------- PROCEDURE --------------- SYSTEM_NAME ----------- DATA_SOURCE ----------- FILE_SUFFIX ------------------ PERSNL SALES SALES SALES SALES ADJUSTSALARY DAILYORDERS LOWERPRICE MONTHLYORDERS TOTALPRICE \KINGPIN \KINGPIN \KINGPIN \KINGPIN \KINGPIN $TX0115 $TX0115 $TX0115 $TX0115 $TX
Managing SPJs in NonStop SQL/MX Showing the Syntax of an SPJ The output of the SHOWDDL command does not exactly match the original CREATE PROCEDURE statement in these ways: • • • • • • • The SHOWDDL command does not omit the optional clauses, such as LOCATION, CONTAINS SQL, NOT DETERMINISTIC, and ISOLATE. The SHOWDDL command always generates a Java signature for the SPJ.
Managing SPJs in NonStop SQL/MX Showing the Syntax of an SPJ The SHOWDDL command for the ORDER_SUMMARY SPJ returns this output: CREATE PROCEDURE SAMDBCAT.SALES.ORDER_SUMMARY ( IN ON_OR_AFTER_DATE VARCHAR(20) CHARACTER SET ISO88591 , OUT NUM_ORDERS LARGEINT ) EXTERNAL NAME 'SPJMethods.orderSummary (java.lang.String,long[],java.sql.ResultSet[],java.sql.ResultSet[])' EXTERNAL PATH '/usr/mydir/myclasses' LOCATION \ALPINE.$SYSTEM.ZSDCR2C6.
Managing SPJs in NonStop SQL/MX Keeping SPJ Statements in OBEY Command Files Keeping SPJ Statements in OBEY Command Files Consider keeping your SPJ statements in OBEY command files. That way, you can quickly and easily create, drop, or reregister SPJs and grant or revoke privileges to the SPJs, as needed.
Managing SPJs in NonStop SQL/MX OBEY Command Files DROP PROCEDURE Statements in an OBEY Command File You can use another or the same OBEY command file to drop a series of SPJs. For example, the OBEY command file, dropprocs.sql, contains a series of DROP PROCEDURE statements: ?SECTION "DROP SALES SPJs" DROP DROP DROP DROP PROCEDURE PROCEDURE PROCEDURE PROCEDURE samdbcat.sales.lowerprice; samdbcat.sales.dailyorders; samdbcat.sales.monthlyorders; samdbcat.sales.
Managing SPJs in NonStop SQL/MX Regenerating CREATE PROCEDURE Statements 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.monthlyorders FROM PUBLIC; REVOKE SELECT ON TABLE samdbcat.sales.
Managing SPJs in NonStop SQL/MX Regenerating CREATE PROCEDURE Statements 3. To end logging, enter the LOG command without the name of the log file: 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.
Managing SPJs in NonStop SQL/MX Backing Up SPJs Backing Up SPJs Backing up SPJs should be part of a backup and recovery strategy for the entire database.
Managing SPJs in NonStop SQL/MX Copying Java Classes to the Remote Node Copying Java Classes to the Remote Node Copy all the Java classes required for the SPJ from the local node (for example, \NODEA) to the same OSS directory on the remote node (for example, \NODEB). By default, only a relative path of the Java class is stored in the SQL/MX system metadata. Therefore, when an application on the remote \NODEB calls the SPJ, the JVM looks for the Java class in the OSS directory on \NODEB. See Figure 6-1.
Managing SPJs in NonStop SQL/MX Copying Java Classes to the Remote Node 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.
Managing SPJs in NonStop SQL/MX Specifying the Node in the External Path Specifying the Node in the External Path Register the SPJ with an external path that begins with the name of the node. That is, specify the full path (for example, /E/NODEA/usr/mydir/myclasses) in the EXTERNAL PATH clause of the CREATE PROCEDURE statement. The full path of the Java class is stored in the SQL/MX system metadata, as shown in Figure 6-2. Figure 6-2.
Managing SPJs in NonStop SQL/MX Distributing Application Classes Advantages of Specifying the Node The Java classes exist in only one location, which eliminates the need to maintain them on separate nodes. Disadvantages of Specifying the Node By using this approach, you sacrifice local node autonomy. For example, applications running on \NODEB rely on the Java class stored on \NODEA to function properly.
Managing SPJs in NonStop SQL/MX Distributing Application Classes HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003 6- 24
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 on page 7-1 Performance Tips on page 7-2 Displaying an Execution Plan for a CALL Statement on page 7-3 Displaying the Shape of a CALL Statement on page 7-5 Troubleshooting SPJ Problems To resolve problems that occur when you register or invoke an SPJ, follow thes
Performance and Troubleshooting • Performance Tips To identify Java-related errors, execute the SPJ method outside the database by invoking the Java method directly in a Java application that you run on the command line. For more information, see the NonStop Server for Java Programmer’s Reference.
Performance and Troubleshooting Displaying an Execution Plan for a CALL Statement Displaying an Execution Plan for a CALL Statement An execution plan reveals how a CALL statement was optimized. You can display all or part of the execution plan for a CALL statement by using the DISPLAY_EXPLAIN command or the EXPLAIN function. Using the DISPLAY_EXPLAIN Command Suppose that you want to display the execution plan for this CALL statement: CALL samdbcat.persnl.adjustsalary(202, 5.
Performance and Troubleshooting Using the EXPLAIN function with SPJ RS 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.
Performance and Troubleshooting Displaying the Shape of a CALL Statement Displaying the Shape of a CALL Statement The SHOWSHAPE command displays the shape for a given DML statement, such as a CALL statement, in an MXCI session. The result can be used at a later time to force the SQL/MX compiler to choose a particular execution plan (or to force the same execution plan for the statement).
Performance and Troubleshooting CALL Statements With a Shape HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003 7-6
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 on page A-3 The createprocs.sql File on page A-11 Examples of the Sample SPJs on page A-13 Using Sample SPJs The SPJ methods are coded in Java source files, which are located in a JAR file named SampleSPJs.
Sample SPJs Installing the SQL/MX Sample Database Figure A-1. Steps for Using Sample SPJs Download the JAR file from NTL to your PC. NTL View the README file on your PC. PC PC Verify and configure your system environment. Extract the source files in OSS. jar xf SampleSPJs.jar Register the stored procedures in NonStop SQL/MX. OBEY createprocs.sql; 9 9 9 9 NonStop SQL/MX NonStop Server for Java JDBC/MX driver Sample database (T0517) Grant privileges for invoking the stored procedures.
Sample SPJs Class Files and Java Methods Class Files and Java Methods The SPJ methods are stored in these class files: • • • Sales Class on page A-3 Payroll Class on page A-7 Inventory Class on page A-9 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.
Sample SPJs Sales Class Example A-1. Sales.java—The Sales Class (page 1 of 3) import java.sql.*; import java.math.*; public class Sales { public static void lowerPrice() throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); PreparedStatement getParts = conn.prepareStatement("SELECT p.partnum, " + " SUM(qty_ordered) AS qtyOrdered " + "FROM samdbcat.sales.parts p " + "LEFT JOIN samdbcat.sales.odetail o " + " ON p.partnum = o.partnum " + "GROUP BY p.
Sample SPJs Sales Class Example A-1. Sales.java—The Sales Class (page 2 of 3) public static void numDailyOrders(Date date, int[] numOrders) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); 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.
Sample SPJs Sales Class Example A-1. Sales.java—The Sales Class (page 3 of 3) public static void totalPrice(BigDecimal qtyOrdered, String shippingSpeed, BigDecimal[] price) throws SQLException { BigDecimal shipcharge = new BigDecimal(0); if (shippingSpeed.equals("economy")) { shipcharge = new BigDecimal(1.95); } else if (shippingSpeed.equals("standard")) { shipcharge = new BigDecimal(4.99); } else if (shippingSpeed.equals("nextday")) { shipcharge = new BigDecimal(14.
Sample SPJs Payroll Class Payroll Class The Payroll class contains these SPJ methods, which are useful for managing personnel data: • • The adjustSalary() method accepts an employee number and a percentage value and updates the employee’s salary in the database based on this value. This method also returns the updated salary to an output parameter. The employeeJob() method accepts an employee number and returns a job code or null value to an output parameter. The Payroll.java source file in SampleSPJs.
Sample SPJs Payroll Class Example A-2. Payroll.java—The Payroll Class (page 2 of 2) public static void employeeJob(int empNum, java.lang.Integer[] jobCode) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); PreparedStatement getJobcode = conn.prepareStatement("SELECT jobcode " + "FROM samdbcat.persnl.employee " + "WHERE empnum = ?"); getJobcode.setInt(1, empNum); ResultSet rs = getJobcode.executeQuery(); rs.next(); int num = rs.getInt(1); if (rs.
Sample SPJs Inventory Class Inventory Class The Inventory class contains these SPJ methods, which are useful for tracking parts and suppliers: • • The supplierInfo() method accepts a supplier number and returns the supplier’s name, street, city, state, and post code to separate output parameters. The supplyQuantities() method returns the average, minimum, and maximum quantities of available parts in inventory to separate output parameters. The Inventory.java source file in SampleSPJs.
Sample SPJs Inventory Class Example A-3. Inventory.java—The Inventory Class (page 2 of 2) public static void supplyQuantities(int[] avgQty, int[] minQty, int[] maxQty) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); PreparedStatement getQty = conn.prepareStatement("SELECT AVG(qty_on_hand), " + " MIN(qty_on_hand), " + " MAX(qty_on_hand) " + "FROM samdbcat.invent.partloc"); ResultSet rs = getQty.executeQuery(); rs.next(); avgQty[0] = rs.getInt(1); minQty[0] = rs.
Sample SPJs The createprocs.sql File The createprocs.sql File CREATE PROCEDURE statements register SPJ methods in an SQL/MX database. Before registering the methods described in Class Files and Java Methods on page A-3, you must compile the Java source files into class files. See Compiling Java Classes on page 3-14. An OBEY command file named createprocs.sql in SampleSPJs.jar contains the CREATE PROCEDURE statements shown in Example A-4. Example A-4. createprocs.
Sample SPJs The createprocs.sql File Example A-4. createprocs.sql—An OBEY Command File (page 2 of 2) CREATE PROCEDURE samdbcat.persnl.employeejob(IN empnum INT, OUT jobcode INT) EXTERNAL NAME 'Payroll.employeeJob(int, java.lang.Integer[])' EXTERNAL PATH ' ' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; CREATE PROCEDURE samdbcat.invent.
Sample SPJs Examples of the Sample SPJs Examples of the Sample SPJs These examples show each SPJ method, the CREATE PROCEDURE statement that registers the SPJ, the CALL statement that invokes the SPJ, and the output in MXCI: • • • • • • • • • LOWERPRICE Stored Procedure on page A-13 DAILYORDERS Stored Procedure on page A-15 MONTHLYORDERS Stored Procedure on page A-16 TOTALPRICE Stored Procedure on page A-17 ADJUSTSALARY Stored Procedure on page A-18 EMPLOYEEJOB Stored Procedure on page A-19 SUPPLIERINFO
Sample SPJs LOWERPRICE Stored Procedure 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.
Sample SPJs DAILYORDERS Stored Procedure DAILYORDERS Stored Procedure Java Method public static void numDailyOrders (Date date, int[] numOrders) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); 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.
Sample SPJs MONTHLYORDERS Stored Procedure MONTHLYORDERS Stored Procedure Java Method public static void numMonthlyOrders (int month, int[] numOrders) throws SQLException { if ( month < 1 || month > 12 ) throw new SQLException ("Invalid value for month. " + "Retry the CALL statement " + "using a number from 1 to 12 " + "to represent the month.", "38001" ); } Connection conn = DriverManager.getConnection("jdbc:sqlmx:"); PreparedStatement getNumOrders = conn.
Sample SPJs TOTALPRICE Stored Procedure TOTALPRICE Stored Procedure Java Method public static void totalPrice (BigDecimal qtyOrdered, String shippingSpeed, BigDecimal[] price) throws SQLException { BigDecimal shipcharge = new BigDecimal(0); if (shippingSpeed.equals("economy")) shipcharge = new BigDecimal(1.95); } else if (shippingSpeed.equals("standard")) shipcharge = new BigDecimal(4.99); } else if (shippingSpeed.equals("nextday")) shipcharge = new BigDecimal(14.
Sample SPJs ADJUSTSALARY Stored Procedure The TOTALPRICE procedure calculates the total price of a purchase and returns this output in MXCI: PRICE --------------------253.96 --- SQL operation complete. The total price of 23 items, which cost $10 each and which are shipped at the standard rate, is $253.96, including sales tax.
Sample SPJs EMPLOYEEJOB Stored Procedure CALL Statement to Invoke the SPJ To invoke the ADJUSTSALARY procedure in MXCI: CALL samdbcat.persnl.adjustsalary(29, 2.5, ?); The ADJUSTSALARY procedure updates the salary of employee number 29 by 2.5 percent and returns this output in MXCI: NEWSALARY -----------139400.00 --- SQL operation complete. The salary of employee number 29 was originally $136,000.00 and became $139,400.00 after the invocation of ADJUSTSALARY.
Sample SPJs SUPPLIERINFO Stored Procedure 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.
Sample SPJs SUPPLYNUMBERS Stored Procedure CREATE PROCEDURE Statement CREATE PROCEDURE samdbcat.invent.supplierinfo(IN empnum NUMERIC(4), OUT suppname CHAR(18), OUT address CHAR(22), OUT city CHAR(14), OUT state CHAR(12), OUT zipcode CHAR(10)) EXTERNAL NAME 'Inventory.supplierInfo' EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; CALL Statement to Invoke the SPJ To invoke the SUPPLIERINFO procedure in MXCI: CALL samdbcat.invent.
Sample SPJs SUPPLYNUMBERS Stored Procedure CREATE PROCEDURE Statement CREATE PROCEDURE samdbcat.invent.supplynumbers(OUT avrg INT, OUT minm INT, OUT maxm INT) EXTERNAL NAME 'Inventory.supplyQuantities' EXTERNAL PATH '/usr/mydir/myclasses' LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA; CALL Statement to Invoke the SPJ To invoke the SUPPLYNUMBERS procedure in MXCI: CALL samdbcat.invent.
Sample SPJs ORDERSUMMARY Stored Procedure 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.
Sample SPJs ORDERSUMMARY Stored Procedure " FROM sales.odetail D, sales.parts P, sales.orders O " + " WHERE D.partnum = P.partnum AND D.ordernum = O.ordernum " + " O.order_date >= cast(? as date) " + D.ordernum "; AND " ORDER BY java.sql.PreparedStatement ps3 = c.prepareStatement(s); ps3.setString(1, onOrAfter); detail[0] = ps3.executeQuery(); } CREATE PROCEDURE Statement CREATE PROCEDURE SAMDBCAT.SALES.
Sample SPJs ORDERSUMMARY Stored Procedure CALL Statement to Invoke the SPJ CALL samdbcat.sales.order_summary('01/01/2001', ?); The ORDER_SUMMARY procedure accepts the date and returns this output in MXCI: NUM_ORDERS -------------------13 ORDERNUM NUM_PARTS AMOUNT Order/Date Last Name ---------- -------------- --------------- ---------- ------------------ 100210 4 19020.00 2003-04-10 HUGHES 100250 4 22625.00 2003-01-23 HUGHES 101220 4 45525.00 2003-07-21 SCHNABL 200300 3 52000.
Sample SPJs ORDERSUMMARY Stored Procedure HP NonStop SQL/MX Guide to Stored Procedures in Java—540433-003 A-26
Index A ADJUSTSALARY procedure creating 1-3, A-11, A-18 invoking A-19 adjustSalary() method code for 3-8, A-7, A-18 description of A-7 example of 1-2 ALL_UIDS system metadata table 1-7 Altering SPJs 4-14 Application class loader See System class loader Application classes description of 1-17 in a distributed database environment 6-23 Argument list, CALL statement 5-4 Array, output parameter 3-3 AUTOCOMMIT setting 5-2 B Backing up SPJs 6-19 Bootstrap class loader 1-15 C CALL operator 7-3 CALL statement arg
Index E Dynamic CALL statement argument lists 5-13 input and output descriptors 5-10 DYNAMIC RESULT SETS 1-3, 4-7 E EMPLOYEEJOB procedure creating A-12, A-19 invoking A-20 employeeJob() method code for A-8, A-19 description of A-7 Environment variables CLASSPATH 2-20 JREHOME 2-13, 2-14 SQLMX_JAVA_SECURITY 2-22 Error messages, finding 7-1 Exceptions, handling 3-11 EXECUTE privilege 6-1 Execution plan, CALL statement 7-3 EXPLAIN function 7-3 Extension class loader 1-15 Extension classes description of 1-15
Index L Java library path for SPJs 2-5 Java method See SPJ method Java security 2-22 Java signature changes to 4-17 compressed 4-10 description of 4-8 restrictions 3-1 Java variables, static 3-6 Java virtual machine (JVM) invoking an SPJ 1-9 registering an SPJ 1-7 java.sql.Types 5-15 JDBC data types 5-15 JDBC Driver for SQL/MX See JDBC/MX driver jdbcMx.jar file 2-6 jdbcmx.
Index O O OBEY command files 6-15 OBJECTS system metadata table 1-7 ODBC/MX client invoking SPJs 5-14 setting JREHOME 2-13 setting the class path 2-18 OUT parameter mode 4-4 OUT parameters 5-5 Output descriptors 5-10 Output parameter array 3-3 Output parameter, SPJ method 3-2 Overloading of procedure names 4-3 Ownership, displaying information about 6-9 P Parameter array 3-3 Parameter descriptors, retrieving information about 5-13 Parameter mode, IN, OUT, or INOUT 4-4 Parameters, SQL See SQL parameters P
Index S SPJ See Stored procedure in Java (SPJ) SPJ class loader 1-15 See also Class loaders SPJ classes See Java classes SPJ environment configuring 2-6 description of 1-12 diagram of 1-12 initialization 1-13, 1-14 reinitialization 1-14 security 2-22 SPJ method See also JDBC/MX-based Java method See also Pure Java method See also SQLJ-based Java method accessing SQL/MP and SQL/MX databases 3-7 examples of A-3 external path 4-10 guidelines for writing 3-1 naming in a CREATE PROCEDURE statement 4-8 printing
Index T supplierInfo() method code for A-9, A-20 description of A-9 SUPPLYNUMBERS procedure creating A-12, A-22 invoking A-22 supplyQuantities() method code for A-10, A-21 description of A-9 Surrogate file See Procedure label Syntax, SPJ 6-12 System class loader 1-15 System classes description of 1-15 required permissions 2-24 System.err.println() method 3-13 System.out.
Content Feedback First Name: __________________ Phone: _____________________ Company: ___________________ Last Name: _________________ e-mail address: ______________ (All contact information fields are required.) If you’re reporting an error or omission, is your issue: Minor: I can continue to work, but eventual resolution is requested. Major: I can continue to work, but prompt resolution is requested. Critical: I cannot continue to work without immediate response.
