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 and 2.1 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 520368-001 NonStop SQL/MX Release 1.8 December 2002 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 523727-004 NonStop SQL/MX Releases 2.0 and 2.
HP NonStop SQL/MX Guide to Stored Procedures in Java Index Examples What’s New in This Manual vii Manual Information vii New and Changed Information Figures Tables viii 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) Accessing SQL/MP and SQL/MX Databases 3-5 Use of java.sql.Connection Objects 3-5 JDBC/MX-Based Java Method 3-6 SQLJ-Based Java Method 3-7 Referring to Database Objects in an SPJ Method Exception Handling 3-10 Accessing Enscribe Databases 3-10 Using Native Methods 3-10 Handling Java Exceptions 3-10 User-Defined Exceptions 3-11 Writing Data to a File or Terminal 3-11 Compiling Java Classes 3-13 3-8 4.
Contents 5. Invoking SPJs in NonStop SQL/MX (continued) 5.
. Performance and Troubleshooting Contents 7. Performance and Troubleshooting Troubleshooting SPJ Problems 7-1 Performance Tips 7-2 Displaying an Execution Plan for a CALL Statement Using the DISPLAY_EXPLAIN Command 7-3 Using the EXPLAIN Function 7-3 Displaying the Shape of a CALL Statement 7-4 CALL Statements Without a Shape 7-4 CALL Statements With a Shape 7-4 7-3 A. Sample SPJs Using Sample SPJs A-1 Installing the SampleSPJs.
Figures Contents Figures Figure 1-1. Figure 1-2. Figure 1-3. Figure 1-4. Figure 1-5. Figure 1-6. Figure 1-7. Figure 1-8. Figure 6-1. Figure 6-2. Figure A-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 and 2.1 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
New and Changed Information What’s New in This Manual New and Changed Information Note. HP NonStop SQL/MX SQLJ (product T1232) has reached MATURE support status, meaning that requests for enhancements (RFEs) are not considered and that only critical defects are repaired. To develop SPJ methods that access SQL/MP or SQL/MX databases, use JDBC/MX instead of SQLJ. For more information, see JDBC/MX-Based Java Method on page 3-6.
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, 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 NonStop SQL/MX library of manuals: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start Reference Manuals SQL/MX Reference Manual NSM/web Installation Guide SQL/MX Connectivity Service Manual SQL/MX Connectivity Service Administrative Command Reference ODBC/MX Driver for Windows Data Management Guides Migration Guide SQL/MX Database and Application Migration Guide SQL/MX Glossary
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 Migration Guide SQL/MX Database and Application Migration Guide Describes how to migrate databases and applications to NonStop SQL/MX and how to manage different versions of NonStop SQL/MX. 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.
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 —523727-004 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-17. 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-20.
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-8 Invoking Different Types of SPJs on page 1-9 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
Introduction Effect of Registering an SPJ 2. The catalog manager within the MXCMP process invokes an internal SPJ named VALIDATEROUTINE, which is in SYSTEM_SQLJ_SCHEMA, and passes information from the CREATE PROCEDURE statement to it. 3. VALIDATEROUTINE 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 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 or SQLJ statements). 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 Invoking Different Types of SPJs SQLJ-Based Java Method An SPJ method can be from a class file of an SQLJ program (embedded SQL in Java). The SQLJ run time, which is written in Java and runs on top of JDBC/MX, processes database requests issued by the SPJ method: Application SQL/MX UDR Server Process CALL... NonStop SQL/MX SQL/MX Language Manager NonStop Server for Java Class File Payroll.class Java Method JVM SQLJ JDBC/MX (Java) JDBC/MX (C++) NonStop SQL/MX VST026.
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-11.
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-15. • 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 directory or JAR file specified by that external path and for loading all application 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.
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-22. 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—523727-004 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. Relink the SQL/MX UDR server. Configure the SPJ environment.
Getting Started NonStop Server for Java Table 2-1. Software Products Required for SPJs SQL/MX Product Version Minimum Supported RVU NonStop Server for Java* JDBC/MX Driver* Relinking MXUDR Required? NonStop SQL/MX Release 1.8 G06.18 T0083V31 (SDK 1.3.1_01) T1225V20 Yes NonStop SQL/MX Release 2.0 G06.23 T2766V10 (SDK 1.4.1) T1225V31 Yes NonStop SQL/MX Release 2.1 G06.23 T2766V10 (SDK 1.4.1) T1225V31 Yes *The product numbers are required versions on systems running G-series RVUs.
Getting Started Verifying Software Versions Verifying Software Versions Verify that you have compatible product versions of these necessary software products on a NonStop system: • • • Verifying the SQL/MX UDR Server on page 2-3 Verifying the NonStop Server for Java on page 2-4 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 outpu
Getting Started Verifying the NonStop Server for Java 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/java/bin/java If you have set the JREHOME environment variable for a nonstandard location of the NonStop Server for Java, enter: vproc $JREHOME/..
Getting Started Relinking the SQL/MX UDR Server on Systems Running G-Series RVUs If you have already set the class path of the JDBC/MX driver in the session, enter: java JdbcMx -version The command displays output similar to: HP JDBC driver for NonStop(TM) SQL/MX Version T1225V31_08APR2004_JDBCMX_20040326 The JDBCMX information indicates the product version of the JDBC/MX driver that is installed on the system in the specified location.
Getting Started • • ° Using the mxudrlink Script to Relink MXUDR sptcpp.o JDBC/MX library, libjdbcMx.lib JNI objects, if specified Using the mxudrlink Script to Relink MXUDR The mxudrlink script prompts you to answer several questions, each having a default response that corresponds to a standard location for one or more required files.
Getting Started Using the mxudrlink Script to Relink MXUDR If the mxudrlink script cannot find the directory, or if the directory does not contain the mxudr.mak, mxudr.oby, and mxudr.tlo files, the script prompts you to reenter the directory. Please enter the NonStop Server for Java installation directory: (/usr/tandem/java) 3. Enter a nonstandard location for the NonStop Server for Java, or press Return for the default installation directory, /usr/tandem/java.
Getting Started Using the mxudrlink Script to Relink MXUDR The log file will contain your settings and any informational or error messages that occur during the relinking operation.
Getting Started Using the mxudrlink Script to Relink MXUDR Verifying the Relinked SQL/MX UDR Server 1. Press Return, and mxudrlink starts verifying the relinked SQL/MX UDR server. • If the verification fails, mxudrlink displays this message: SQL/MX UDR Server verification failed. • If the verification succeeds, mxudrlink displays this question: Are there any unresolved externals in this SQL/MX UDR Server?(Y/N) 2.
Getting Started Using the Makefile to Relink MXUDR Using the Makefile to Relink MXUDR You can also use the makefile, mxudr.mak, to relink MXUDR. Caution. The mxudr.mak makefile does not back up the original MXUDR file if this file already exists in the target directory, and the makefile does not verify the new MXUDR. Use the make command to relink MXUDR: /bin/make -f MxInstallDir/mxudr.
Getting Started Controlling JVM Startup Options Controlling JVM Startup Options JVM startup options are passed to the Java application launcher (java) and influence the Java run-time environment. To specify JVM startup options for an SPJ environment, set the UDR_JAVA_OPTIONS default attribute. Use the UDR_JAVA_OPTIONS default attribute to: • • • • • Override the NonStop Server for Java and JDBC/MX driver that are configured in the Java environment. Set class paths for an application that calls SPJs.
Getting Started JVM Startup Options for Each SPJ Caller Precedence of JVM Startup Options A UDR_JAVA_OPTIONS setting in a CONTROL QUERY DEFAULT statement takes precedence over the setting in the SYSTEM_DEFAULTS table. See JVM Startup Options for Each SPJ Caller on page 2-12. 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.
Getting Started JVM Startup Options for Each SPJ Caller EXEC SQL CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS '-Xmx32M'; /* Call the stored procedure. */ EXEC SQL CALL samdbcat.sales.lowerprice(); /* Turn off the application-specific system properties for /* the SPJ environment. */ EXEC SQL CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS 'OFF'; /* Call the stored procedure. */ EXEC SQL CALL samdbcat.sales.lowerprice(); ...
Getting Started JVM Startup Options for Each SPJ Caller USING :hv_month_param1 INTO :hv_ordernum_param2; /* IN */ /* OUT */ printf("\nThe number of orders during that month is %d.\n"); /* Turn off the application-specific system properties for /* the SPJ environment. */ strcpy(hv_ctrl_stmt, "CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS 'OFF'"); } /* Prepare another CALL statement. */ EXEC SQL PREPARE sqlstmt2 FROM :hv_sql_stmt2; ... /* Call the stored procedure.
Getting Started Using Multiple UDR_JAVA_OPTIONS Settings in One Application --- SQL operation complete. >>CONTROL QUERY DEFAULT UDR_JAVA_OPTIONS RESET; --- SQL operation complete. For more information about MXCI, see the SQL/MX Reference Manual. Displaying the UDR_JAVA_OPTIONS in Effect for an MXCI Session To show the UDR_JAVA_OPTIONS setting in effect for a CONTROL QUERY DEFAULT statement issued in MXCI, enter the SHOWCONTROL command in the current MXCI session.
Getting Started Using Multiple UDR_JAVA_OPTIONS Settings in One Application 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...
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 the SPJ environment, ends. For more information, see SQL/MX UDR Server Process 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 Precedence of the JREHOME Environment Variable The UDR_JAVA_OPTIONS setting takes precedence over the JREHOME environment variable. See Setting JREHOME by Using UDR_JAVA_OPTIONS on page 2-17.
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; }; grant codeBase "file:/usr/tandem/sqlmx/lib/sqlj.jar" { permission java.security.
Getting Started SPJ Policy File and Required Permissions For more information about JDBC/MX, see the JDBC Driver for SQL/MX Programmer’s Reference. Permissions for the NonStop SQLJ Product If you are planning to call SQLJ-based SPJs, you must grant these permissions in the SPJ policy file for the NonStop SQLJ product file, sqlj.jar, to operate properly: grant codeBase "file:/usr/tandem/sqlmx/lib/sqlj.jar" { permission java.security.
Getting Started SPJ Policy File and Required Permissions classes in the /usr/mydir directory, including the JAR file, add this grant statement to the SPJ policy file: grant codeBase "file:/usr/mydir/*" { permission java.io.FilePermission "/usr/ossfiles", "read,write"; }; The execution of an SQLJ class file requires system properties to be read. Because reading system properties is a restricted operation when Java security is enabled, you must grant all permissions to the codebases of SQLJ-based SPJs.
Getting Started SPJ Policy File and Required Permissions HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004 2- 30
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 Using the main() 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 Currently, NonStop SQL/MX does not support SPJs that return stored procedure result sets. A stored procedure result set is a cursor that is left open when the SPJ method returns control to the database server and from which the invoking application can retrieve multiple rows of data.
Writing SPJ Methods Static Java Variables This Java method also uses a Java wrapper class in its signature to anticipate a possible returned null value: public static void employeeJob(int empNum, Integer[] jobCode) Static Java Variables To ensure that your SPJ method is portable, you should avoid declaring static variables in the method. NonStop SQL/MX does not ensure the scope and persistence of static Java variables.
Writing SPJ Methods JDBC/MX-Based Java Method platforms because their data source URL, "jdbc:sqlmx:", is specific to NonStop SQL/MX. Each JDBC connection has its own SQL context and MXCMP server. If each invocation of an SPJ method creates a new connection object and does not explicitly close that connection, SQL/MX resources are consumed, and performance might diminish.
Writing SPJ Methods SQLJ-Based Java Method rs.close(); conn.close(); } } You do not have to explicitly load the JDBC/MX driver before establishing a connection to the database. The SQL/MX UDR server automatically loads the JDBC/MX driver when the SPJ is called. To register this method as an SPJ in NonStop SQL/MX, use a CREATE PROCEDURE statement. For details, see Section 4, Registering SPJs in NonStop SQL/MX. For other examples of JDBC/MX-based SPJs, see Appendix A, Sample SPJs.
Writing SPJ Methods Referring to Database Objects in an SPJ Method For information about how to write an SQLJ program, see the SQL/MX Programming Manual for Java. 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. SQL/MX database objects have three-part ANSI names that include the catalog, schema, and object name.
Writing SPJ Methods Referring to Database Objects in an SPJ Method Object Name Qualification in NonStop SQL/MX Release 2.1.1 and Later In SQL/MX Release 2.1.1 and later, the SQL/MX UDR server propagates the values of the catalog and schema where the SPJ is registered to the SPJ environment.
Writing SPJ Methods Exception Handling "FROM orders " + "WHERE order_date = ?"); getNumOrders.setDate(1, date); ResultSet rs = getNumOrders.executeQuery(); rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.close(); } Be aware that overriding the default values by using getConnection() or UDR_JAVA_OPTIONS requires you to hard-code the catalog and schema values and might make SPJ methods less portable across systems.
Writing SPJ Methods User-Defined Exceptions User-Defined Exceptions The SQLSTATE values 38001 to 38999 are reserved for you to define your own error conditions that SPJ methods can return. By coding your SPJ method to throw a java.sql.SQLException object, you cause the CALL statement to fail with a specific user-defined SQLSTATE value and your own error message text.
Writing SPJ Methods Writing Data to a File or Terminal { ... String outputFileName = "/usr/mydir/spj.output"; FileOutputStream fileStream = new FileOutputStream(outputFileName); PrintStream printStream = new PrintStream(fileStream, true); ... printStream.println("The salary was updated for employee " + empnum); ... } Suppose that an employee number of 202 is passed to the SPJ.
Writing SPJ Methods Compiling Java Classes Compiling Java Classes Before registering a Java method as an SPJ, you must compile your Java source file into Java bytecode: • • To compile the class file of a JDBC/MX or other Java program by using the Java programming language compiler (javac), see the NonStop Server for Java Programmer’s Reference or the NonStop Server for Java Tools Reference Pages. To translate and compile the class file of an SQLJ program, see the SQL/MX Programming Manual for Java.
Writing SPJ Methods Compiling Java Classes HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004 3- 14
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-12 Altering an SPJ and Its Java Class on page 4-13 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, as shown in this example: Procedure Name SQL Parameters CREATE PROCEDURE samdbcat.persnl.
Registering SPJs in NonStop SQL/MX Naming the Stored Procedure When selecting a procedure name: • Choose a meaningful and unique name that does not already exist for a procedure, table, view, or SQL/MP alias in the same schema. For example, use the procedure name ADJUSTSALARY for a Java method named adjustSalary() that adjusts an employee’s salary in the EMPLOYEE table. • Use delimited identifiers sparingly.
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 SPJ Method 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. 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.
Registering SPJs in NonStop SQL/MX Specifying the SPJ Method Java Method Signature Specifying the Java signature is necessary only when an SQL parameter of the SPJ does not map by default to a Java wrapper class and when the SPJ method uses a Java wrapper class instead of a Java primitive data type in its Java signature. See Table 4-1, Mapping of SQL/MX Data Types to Java Data Types, on page 4-6.
Registering SPJs in NonStop SQL/MX Specifying the External Path NonStop SQL/MX returns an error if the compressed signature exceeds 8192 characters. For more information about javap, see the NonStop Server for Java Tools Reference Pages. For example, consider the SPJ method, adjustSalary(): public static void adjustSalary(BigDecimal empNum, double percent, BigDecimal[] newSalary) ... The Java signature of this method in the CREATE PROCEDURE statement, if you choose to specify it, is: (java.math.
Registering SPJs in NonStop SQL/MX Naming the Procedure Label Example of a Class File Path For example, consider the /usr/mydir/myclasses/Payroll.java source file: package pkg.subpkg; public class Payroll { 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.
Registering SPJs in NonStop SQL/MX Specifying an SQL Access Mode You can also use the LOCATION clause to specify only the volume of the procedure label: LOCATION $DISKVOL In this case, the node is the same node where the SPJ is registered, the subvolume is the same as the schema of the SPJ, and the file name is system generated. For information about naming rules for the LOCATION clause, see the SQL/MX Reference Manual.
Registering SPJs in NonStop SQL/MX Dropping an SPJ Dropping an SPJ The DROP PROCEDURE statement removes an SPJ from NonStop SQL/MX.
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.
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-8. 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 Unnamed Parameters 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 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 an SQLJ Program Invoking SPJs in an SQLJ Program An SQLJ program is a Java program that contains embedded SQL statements in SQLJ clauses. In an SQLJ program, you can invoke an SPJ in a CALL statement. Place the CALL statement within an SQLJ executable clause: #sql {CALL procedure-name ([parameter[{, parameter}...]])}; In an SQLJ program, the host variables and expressions in a CALL statement are in the IN parameter mode by default.
Invoking SPJs in NonStop SQL/MX Invoking SPJs in an SQLJ Program HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004 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-14 Backing Up SPJs on page 6-18 Using SPJs in a Distributed Database Environment on page 6-18 Migrating SPJs From NonStop SQL/MX Release 1.8 to NonStop SQL/MX Release 2.
Managing SPJs in NonStop SQL/MX Granting Privileges on an SPJ you are not the owner of the SPJ or the super ID, you must have been granted the WITH GRANT OPTION privilege to grant privileges to other users. 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.
Managing SPJs in NonStop SQL/MX Granting Privileges on Referenced Database Objects 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.monthlyorders TO PUBLIC; After granting the EXECUTE privilege to PUBLIC, you cannot revoke the privilege from a subset of users.
Managing SPJs in NonStop SQL/MX Revoking Privileges on an SPJ Revoking Privileges on an SPJ Use the REVOKE EXECUTE or REVOKE statement to remove the EXECUTE or WITH GRANT OPTION privilege on an SPJ from specific users. In a REVOKE statement, specify ALL PRIVILEGES to revoke the EXECUTE privilege on an SPJ. For the syntax of the REVOKE EXECUTE and REVOKE statements, see the SQL/MX Reference Manual.
Managing SPJs in NonStop SQL/MX Revoking Privileges on an SPJ 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.
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 and 2.1 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 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.
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-4 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 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).
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; ! ! ! ! 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-13. 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 S
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.
Index A ADJUSTSALARY procedure creating 1-3, A-11, A-18 invoking A-19 adjustSalary() method code for 3-6, A-7, A-18 description of A-7 example of 1-2 ALL_UIDS system metadata table 1-7 Altering SPJs 4-13 Application class loader See System class loader Application classes description of 1-17 in a distributed database environment 6-22 Argument list, CALL statement 5-4 Array, output parameter 3-3 AUTOCOMMIT setting 5-2 B Backing up SPJs 6-18 Bootstrap class loader 1-15 C CALL operator 7-3 CALL statement arg
Index E DROP PROCEDURE statement 4-12 Dropping SPJs 4-12 Dynamic CALL statement argument lists 5-12 input and output descriptors 5-9 E EMPLOYEEJOB procedure creating A-12, A-19 invoking A-20 employeeJob() method code for A-8, A-19 description of A-7 Enscribe databases, accessing 3-10 Environment variables CLASSPATH 2-24 JREHOME 2-17, 2-18 SQLMX_JAVA_SECURITY 2-26 Error messages, finding 7-1 Exceptions, handling 3-10 EXECUTE privilege 6-1 Execution plan, CALL statement 7-3 EXPLAIN function 7-3 Extension c
Index L Java compilation 3-13 Java data types 3-2, 4-6 Java environment See SPJ environment Java exceptions, handling 3-10 Java method See SPJ method Java Native Interface (JNI) objects See JNI objects Java security 2-26 Java signature changes to 4-16 compressed 4-9 description of 4-8 restrictions 3-1 Java variables, static 3-5 Java virtual machine (JVM) invoking an SPJ 1-8 registering an SPJ 1-7 java.sql.Types 5-14 JDBC data types 5-14 JDBC Driver for SQL/MX See JDBC/MX driver jdbcmx.
Index O Nonblocking JDBC/MX 1-15 NonStop Server for Java description of 2-2 using an incompatible version 2-2 verifying the version 2-4 Nowait SQL/MX See Nonblocking JDBC/MX Nulls, input and output 3-4 numDailyOrders() method code for A-5, A-15 description of A-3 numMonthlyOrders() method code for A-5, A-16 description of A-3 O OBEY command files 6-14 OBJECTS system metadata table 1-7 ODBC/MX client invoking SPJs 5-13 setting JREHOME 2-17 setting the class path 2-22 OUT parameter mode 4-4 OUT parameters
Index S REVOKE statement 6-4 Revoking privileges from an SPJ 6-4 ROUTINES system metadata table 1-7 Rowsets 5-1 S Sales class A-3 Sample SPJs, installing A-1 SampleSPJs.
Index T SQL/MX UDR server (continued) registering an SPJ 1-7 relinking of 2-5 server process 1-12 verifying the version 2-3 Static CALL statement 5-8 Static Java variables 3-5 Stored procedure in Java (SPJ) altering 4-13 argument list 5-4 backing up 6-18 benefits of 1-4 description of 1-1 displaying information about 6-7 granting privileges on 6-1 invoking 1-8, 5-1 migrating from Release 1.8 to 2.
Index V V VALIDATEROUTINE 1-7 Versions See Software product versions W WITH GRANT OPTION privilege 6-1 Special Characters -Djava.class.path option 2-22 -Djava.security.manager option 2-26 -Djava.security.policy option 2-26 -Djdbcmx.sqlmx_nowait option 1-15 -Dsqlmx.udr.extensions option 2-20 -Dsqlmx.udr.
Index Special Characters HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004 Index-8
