Experienced software developer. Skills in Development, Coding, Testing and Debugging. Also skills in Mainframe. View all posts by Srini. Facebook twitter Instagram.
This usually means that parameters are marshaled into arrays for exchange between the stored procedure and the underlying Java. This is essentially transparent to the developer. A fenced procedure is spawned in a separate process to the instance on the other side of a fence, so to speak. However, each has its own set of advantages and disadvantages, which are covered below.
Java and DB2 INTO statement. JDBC presents a string containing an SQL statement to the database at runtime which may have just been constructed within the Java application. When DB2 is passed the SQL statement, it must perform a number of steps to prepare the statement before it is able to execute it including syntax checking, authorization checking, and access path selection , and this can often require longer to perform than the actual SQL itself.
The SQLJ program preparation process extracts this SQL and binds it against the database, allowing DB2 to perform all of the checks and access path selection as a one-off process.
This is a complex area that is covered in more detail in 9. As a general rule, we recommend that you use SQLJ wherever possible, as it offers significant benefits in developer productivity, performance, and security when compared to JDBC.
Table 1. Type Description Contains Portable? Note that this column refers to the portability of the driver itself across various platforms and operating systems, and not the portability of the application using it.
IBM currently supplies Type 2 and some Type 3 drivers for the various members of the DB2 family, as detailed in the following sections. These are installed along with the rest of the Java support components if so requested during the DB2 UDB install process. Simply put, a stored procedure is a program or routine that is invoked via an SQL CALL statement issued from a client program, and executes under the control of the database manager. Parameters can be passed to, and received from, the stored procedure.
Instead of one trip across the network for each of these calls, they can be combined and executed locally within a stored procedure so only a single trip across the network is needed. This performance improvement can also create subsequent benefits in reducing lock contention. In addition, multiple WLM-controlled address spaces for DB2 stored procedures provide improved program isolation.
Table 2 summarizes the language support at the time this book was written. This book concentrates on the development of stored procedures written in the Java language.
An additional consideration is that, unlike stored procedures written in other languages, Java stored procedures have to run in a WLM address space. Code DB2 Bind. If the application architecture changes over time, these same stored procedures could also be called in a large number of alternative ways such as from within Java applets, Enterprise Java beans, ODBC applications, and many more. Our sample application consists of only those components necessary to implement a single business function to add a software order for a new customer.
Of course, in a real situation, many additional business functions would need to be handled, but these could all use the same basic approach as shown in our examples. Internet Call DB2 DB2 Data. Table 3. Finally, to allow us to make use of some of the new network computing features being introduced to DB2 while retaining compatibility with older releases, we produced two versions of the code.
The standard version uses features available today across the entire DB2 family. The enhanced version uses some of the new features available in the DB2 family. These Java package names are shown in full in Table 4 below. Table 4. See 5. Therefore, we prefixed the stored procedure name with the type and level it was aimed at, as follows:. This section discusses the portability of the Java stored procedures built for this book.
No changes were required to any of the DML embedded within the application code. In the following sections we show examples of more complex Java stored procedures. StringWriter; import java. PrintWriter; import java.
DateFormat; import java. Date; import java. The URL and the driver name may need to change depending upon the platform upon which the client runs see 8.
Platform differences This sample ran on NT. A UNIX version would require no change. This client source code could be modified to take advantage of schemas in a Version 6 system. See 3. Example:" ; System. This chapter details the prerequisites for the deployment of Java stored procedures on the major application platforms, together with an overview of the steps necessary to enable this support, and some of the common problems you may encounter.
In addition, we discuss some common setup issues. Table 5. Table 6. It is assumed that all of the products and services listed in the previous section on prerequisites have been installed and properly configured. This makes it simpler to ensure that these DB2 packages are picked up at execution time, as described in 9. Design your library structure and naming standards. Tailor the. WLM environments While it is not strictly necessary for you to do so, we strongly advise you to set up at least one separate WLM application environment and JCL procedure to handle your Java stored procedures.
Tailor the contents of the SQLJ properties file and cursors file, to ensure that correct values are being set. It is recommended that you set up one hierarchical file system HFS per DB2 subsystem or data sharing group. Table 7 provides a summary of the USS libraries required, together with the settings we used in our environment. Table 7. Links directory. The SQLJ properties file: db2sqljjdbc. Serialized profiles directory. Table 8. Site specific. See Figure 4. Application load library.
See Figure 5. See Figure 6. DBRM library. Figure 4 shows an example of this data set definition:. Data Set Name. The application load library must be defined as a PDSE library. Figure 5 shows an example of this data set definition. By creating a standard. It is equivalent to the ISPF logon proc, in that it establishes the initial environment for the user. Some shops may wish to create a centralized script that establishes the basic DB2 environment. Here is a example of the. We did not use a centralized script.
EXEC end Added for tso -t from telnet. To add Java support to the WLM-established address space you will have to add these two steps. Figure 7 shows the RACF command that you should use to do this:. This procedure contains the following statements: 1. This data set applies to the entire WLM environment, not just individual stored procedures. See 4.
Modify this script for your environment. Here are the modifications we made to it. HPJ Storage Requirements This step requires a large amount of storage, so when you log onto TSO, make sure you have set the logon region size to DBZ2 will contain: sqlj. In addition, there are other considerations that you have to keep in mind; see 4.
Finally, the location where you will store the external links for your stored procedure modules must also be referenced. TZ TZ is used to set the local time zone. It is an optional parameter. You must be careful when structuring your libraries to ensure that this limit is not exceed. No error messages are issued if the limit is exceeded: any characters beyond position will be ignored, which may lead to errors such as failure to load classes.
A sample cursor definition file called db2jdbc. This file can be amended or an alternative version defined if you wish to have more cursors available, or to rename the cursors that JDBC will use.
Refer to 4. Here is a sample of the contents of an SQLJ properties file. These parameters are discussed in the sections that follow. For a full explanation of these and other parameters, please refer to the Application Programming Guide and Reference for Java, SC This will allow you to use one JCL procedure for the application environment for all members of the data sharing group.
Example: Start parameters.. You will only have one copy of these data sets in the sysplex environment. You will have to duplicate the HFS directory structure in each member of the sysplex. This will allow you to use the same HOME path name for the WLM stored procedure address space and the same owner name across the sysplex. We recommend a value of The reason code 00E is encountered when attempting to invoke a Java stored procedure. Additional SQLCA information provided with the message has the name of the class that could not be found.
This message is printed out in the Java SP address space and the client receives a Either the db2jdbc. Make sure that the directory is set with This includes the Java JDK 1. A comprehensive set of sample applications is supplied with DB2, and you may want to try running some of these before writing your own code to verify that the environment is properly configured.
Simple Java examples can be found on Web sites that introduce the language to novice Java programmers.
We used AIX for our testing. Other UNIX platforms should be similar, but be sure to read the platform-specific documentation. On AIX, this must be installed by the system administrator using smit or installp or an equivalent system tool.
The JDK may be installed in a location different than the default location. This may be necessary due to multiple applications sharing the same machine requiring different releases of the JDK. This can be done in the. Each developer wishing to create Java stored procedures would use the same. In this chapter we discuss the various issues that an application designer must be aware of when developing Java stored procedures. Therefore, it is a good idea to come up with some naming standards as soon as possible in the design process.
The following sections provide some general naming recommendations for a Java stored procedure environment see Figure 13 on page 95 for a discussion of how the various programming objects interact. Remember that there are some issues surrounding case sensitivity with many of these names. Be consistent in your use of case, and implement some site conventions for everybody to follow. There is a one-to-one relationship between a Java class and a Java source file.
There is a one-to-one relationship between a Java package and an operating system directory which stores all of the Java source files for that package. Most code management products and procedures operate at source code file level. Therefore, we felt that storing any more than one stored procedure within a single source code file can cause version management issues.
For this reason, we chose to implement a single method per class, which resulted in a single stored procedure per source file. This approach is summarized in Figure Within this section we present the conventions that we used during the production of this book. This approach seemed to provide a good compromise between flexibility and manageability. However, your mileage may vary: you must define your own conventions based upon any existing standards and the particular environment that you are working within.
Although the names can be dissimilar, for ease of understanding, we suggest that the stored procedure and the method be named the same. We found it easiest to place one method in a class and to name the method the same as the class, changing the initial character of the class name to upper case. Therefore, we recommend that you name the source file after the Java class it contains. So, if you are following our previous recommendation of one method stored procedure per Java class, you can name the source file after the stored procedure name.
How many stored procedures per source file? We feel that the approach we have described above provides the greatest flexibility in a typical development environment, where version management and concurrent development requirements would make the placement of multiple stored procedures within each source file much more difficult to manage. However, there is nothing to stop you from setting up your environment in such a way as to place multiple stored procedures in each.
In this case, each stored procedure becomes a separate method within the class represented by the source file. You will need to create a meaningful name for the source file that encompasses the stored procedures within it. How many Java packages per load module or JAR? The source files for all stored procedures that belong to a given Java package should be grouped together in a single USS directory named after the Java package.
This may lead to existing modules being overwitten during the program preparation process. For these reasons, we recommend that your program preparation scripts use different names for the Java package and the PDSE member name.
The sample scripts described in 7. We recommend that you name your JAR file the same as your Java package name to ease problem solving. There is no technical limitation requiring the JAR name to be named the same as the Java package name. Therefore, if you have 2 SQLJ procedures in a Java package, that Java package will have 8 DB2 packages associated with it the terminology is a little confusing here — make sure you are clear about the differences between Java packages and DB2 packages.
DB2 package names must be limited to 7 characters, as the db2profc process appends a number between 1 and 4 after the supplied name to generate the 4 packages. You must therefore create a reasonable 7-character abbreviation for your character stored procedure name.
Unless your existing site standards dictate otherwise, we suggest that you use one DB2 package collection for each Java package, and name that collection after it. See 9. These are known as parameter style Java stored procedures. It was developed before industry standards had been established. Any value determined by the stored procedure must return in one of the parameters passed into the stored procedure. Therefore, they are not supported as parameters for Java stored procedures.
However, only the [0] element is used. The SQLJ standard requires this form of parameter style. Failure to do will result in a syntax error. The JDBC statements are executed dynamically.
For recommendations on which API to use, refer to 2. For other platforms, use of Java packages is optional. From a design perspective, use of Java packages helps organize stored procedures into application groups. Most shops host more than one. A Java program becomes part of a Java package if it has the token package followed by the Java package name at the top of the source file.
For recommendations regarding Java package, module, and JAR naming standards, see 5. You should be aware that with the exception of purely DB2 objects such as plans and packages, most object names are case sensitive, so it is vital that you be consistent in your naming. We suggest that you formulate some conventions for use at your site.
This restriction may change in a future version. Stored procedures written in other languages may nest, and they may be written in different languages, if they meet the following requirements:. The Java package name subsequently is mapped to the load module produced by the HPJ. For example, these two techniques are functionally equivalent: a Java package groups a number of class files, and an HPJ-produced load module groups a number of class files into an executable unit.
For instance, placing many very large procedures into a single Java package will result in all of the code being compiled into a single HPJ executable module. At run time, the entire module has to be loaded into the stored procedure address space, which can be wasteful if only a single, small routine is required. On the other hand, grouping commonly-used routines into a single Java package may provide some benefits, as the chances of the required module already being resident in the stored procedure address space are increased.
There are no hard-and-fast rules for this packaging. The program preparation process which is described in detail in 7. Figure 13 shows how these objects are used when a client issues a call to a Java SQLJ stored procedure. Via ound plan USS Directory. This load module is loaded into the relevant WLM stored procedure address space.
This chapter provides some practical guidance on the techniques you can use when coding your DB2 Java stored procedures. The following sections are not intended to be a programming reference. If you need more background information on these subjects, please refer to the resources listed in Appendix G. When calling the stored procedure from a JDBC client, use the special function setNull for marking the parameter as null. You cannot simply pass a null Java object. Because the SQL field is defined as an integer, it appears to make sense to define the stored procedure parameter as an integer.
Unfortunately, this will fail when trying to invoke the stored procedure with a null value. A Java int field is a base type and thus cannot be set to an actual null value. To work around this issue, define the parameter as a String. This gives you the ability to assess if the String is a null object.
Trim a Numeric String If you use a String to pass a numeric value, be sure to trim the spaces from the string. SQLJ is intuitive about null-handling. For example, a null Java object translates to a nullable column without needing to use a special method as in JDBC. Table 9. Using a base Java type as a It is not possible to set a base Java type to parameter null and have it actually understood by downstream processing as a null.
Instead, you can cast parameters to a String type or other Java type subclassed from Object which can then be set to null. Inserting a null from an SQLJ SQLJ understands that when a Java stored procedure object is null and it is being inserted to the database that a null value is to be placed in the associated nullable column in the table.
This allows for some up front cross-checking. There are two reasons why you might choose to return ResultSets instead of Iterators. This may change some time in the future. Once you walk through that Iterator, the cursor is considered exhausted. If you cast to a ResultSet and walk through it, that activity also exhausts the cursor. Therefore, the only ResultSets that can be returned to the client are those that have been freshly created from a select statement without a subsequent review.
Failure to do so may cause data not to be returned. It is good coding practice to close ResultSets that no longer need to be used. Adherence to SQL standards requires that ResultSets be returned to the client in the order in which they are opened.
Consequently, if you need two ResultSets, open the first declared ResultSet before opening the second declared ResultSet. Reversal of the order of the opening will produce odd results in your client. We suggest sending in two parameters for determining successful or unsuccessful execution. One is used to mark where the stored procedure last successfully executed.
The second holds the text produced by the catch block of an exception. The following code fragment illustrates this:. However, that output goes to the standard output or perhaps standard error. What this means is that the invoking client will not see the stack trace.
Our programs rarely work correctly the first time. Stored procedures can be difficult to debug, due to the limitation that they run underneath the control of a separate process, perhaps on a different machine than that of the invoking client.
Write the stored procedure as one class and the driver as a separate class. When you complete the draft version you customize the stored procedure class to fit within the database. One item that must change will be the Connection, which would have been established by the driver in the standalone program. We question the feasibility of this approach, due to rollbacks. Quite frankly, the stored procedures that most need debugging are the ones that will end so badly the system rolls back the work, erasing all the debug data!
Your systems programmer can tell you the name of the data set on your system. These statements must be used with care, and only on the development platform — if they get pushed out to production, the performance of the production system may be negatively impacted.
Refer to 6. However, these types of statements can be directed to a file. In the following example, a file is created with a date and time stamp for the file name.
However, they can be. Observe these two cautions: 1. If the stored procedure fails, the pwx. A better approach is to put the pwx. Never allow this debugging technique to go onto the production system. It will hurt performance and possibly bring down the system. You will need to assess the SPB for your own use. Our investigation uncovered both advantages and limitations. Most GUI tools are improved on a rapid basis, such that the difficulties we see now may be corrected by the time you read this book.
You need not make the source available on the platform upon which the SPB runs; the SPB reads the catalog definitions to build the window that asks for the input parameters. Other errors show in the WLM logs. If this has been incorrectly specified as a lower case name, the link will not work, and this error message will be returned.
The error looks something like this: java. This uppercase name now becomes the name used by the Java client to invoke the stored procedure. This can only happen during autobind. The calling DB2 package has to be explicitly rebound in this case. Another check is whether the caller has execute authority to the stored procedure. Callers of stored procedures that were migrated from V5 and have not been dropped and recreated do not need this authority, as they have no OWNER.
The most common error is that a procedure by this name with this number of parameters was not found in the catalog table. If this is correct, then it's a problem with the stored procedure name.
The name can be either explicitly specified, or contained in a host variable. Either way, the name can be a 1-part, 2-part, or 3-part name. The PATH bind option is used to determine schema name. Refer to the APAR text for further details. Stored procedures are now real DB2 objects, so a reference to a remote stored procedure when binding a DBRM into a plan or DB2 package locally behaves just like a reference to a remote table. P1 doesn't exist at the local site.
Here are some suggestions: 1. Don't bind the calling DB2 package locally, i. This is the recommended approach, and what is usually done for other remote objects, like tables. Modify the calling application to use a 3-part name on the CALL statement. This means that the authorization rules don't change from V5. When running a client the following error is seen: java. The wrong location name was argued in the connect statement, therefore DB2 attempts to find the remote DB2, however, one is not listed.
Details described at 9. Remove hidden whitespace characters that somehow were introduced into the sample. Carefully remove or retype what looks like blanks, especially at the ends of lines. When trying to execute a Java stored procedure, an error message says there was a Java Interpreter start-up or communication failure. Java Stored Procedure Sample debug: drop procedure debug: completed drop procedure.
Your DBA reconfigures this value. It can be seen doing a get dbm cfg, DB2 command, or by looking at the Instance configuration values from Control Center. Example: COM. The JAR or class has not been made available to the database. If you are not using JAR files, you copy the. Example The command: call sqlj. The JAR is already defined to the database.
You need to remove it or replace it. This is a maintenance issue, which has been resolved in Version 7 Fixpak 1. Note that despite this message, the command actually works.
Often, sqlj will complete successfully and a subsequent javac will reveal Java errors that need to be corrected in your program source. If this happens, DB2 may pick up the old version, generating the error. Try to remove the old version using the sqlj. When trying to execute a Java stored procedure that appears to have installed correctly, the following error message returns to the client: COM.
This is typically caused by forgetting to change the Java package name in the Java source code after having copied source code from one directory to another. Everything compiles and installs fine. However, during execution the stored procedure actually has a different Java package name. It is peculiar that the mismatch is not found during the javac step. The system cannot find a matching method stored procedure , even if you were able to install it in the database with no errors.
The number of parameters the client sent to DB2 does not match the number of parameters that DB2 understands the stored procedure to contain. This often is caused by forgetting to set the number of ResultSets when defining the stored procedure. This is somewhat perplexing when comparing source code against the stored procedure definition. The source code may show, for example, 5 parameters, yet the stored procedure definition shows 4.
The client returns an error message similar to this: ava. Recognize where the error is being seen: at the client, or when trying to invoke the stored procedure. From a stored procedure perspective, it means that a previous incarnation of the stored procedure ended badly in the database, putting it into a state that prevents it from being executed. Contact your DBA to determine the best method to make it available again.
One cause may be special characters embedded within the SQL, such as newlines. Carefully replace all whitespace within the SQL or delete the whitespace. During db2profc, an error message returns indicating that several stored procedures match the name. However, the source code has qualified the call with a Schema.
There may be a fix available for this error Alternatively, use a unique stored procedure name that is not shared across schemas. As with any programming language, there are steps required to transform the source code into something that can be executed. This chapter describes the steps required for Java stored procedure preparation. Compile Java.
Package Java. For SQLJ stored procedures, 4 application specific DB2 packages will be produced as part of the program preparation process. Note that this section covers authorization issues from the stored procedure perspective only.
Additional considerations apply to client authorization, and these are covered in 9. Therefore, all client authorization IDs must be given the necessary access to any tables that may be used by the stored procedure, which can be undesirable from a security and administration perspective. The authorization ID of the DB2 package owner will be used for authorization checking, which removes the requirement to give table access to each client authorization ID.
The authorization ID of the owner or schema of the stored procedure will be used for authorization checking.
This allows a number of alternative security strategies to be considered for example, you could create the stored procedure in the same schema as your DB2 tables, which would implicitly give the stored procedure access to any tables in that schema.
Can run on any JDK platform. From within USS, Java-compile the source code using the javac command. This produces one or more. If you wish, you may package multiple. This step is entirely optional, and as we are already packaging multiple. HPJ-compile the. Development platform Steps 1 and 2 of this process can be performed on any platform that contains a suitable JDK. To build this program, go up one directory from the source. Next, you need to perform the HPJ compile.
Refer to the VisualAge for Java Version 2. This must correspond with the Java package name within your stored procedure code. If you have not done so already, you now need to define your stored procedure to DB2.
Script javaspj — prepare JDBC stored procedure This script performs the main program preparation tasks, and calls script hpjsp to perform the HPJ compile. JAVA prog.
SQLJ Translator. From USS, translate your source code using the sqlj command. Serialized profiles are stored in. Your source code will be modified, and placed in a. Java-compile the source code using the javac command. JT Open is the open source version of Toolbox for Java. I am splitting these different approaches to separate posts. Click on the topic above to see the relevant post for that topic. To call a Stored Procedure, we need to create a CallableStatement from the connection and then we can set the values of the parameters using the setter methods.
After all the values are set for the Stored Procedure, you simple call the executeQuery method of the stm object if it returns a ResultSet, or executeUpdate method in case of a StoredProcedure that updates records. This however is not required, you can use the same interfaces provided by the java.
0コメント