PARAMETER STYLE DB2GENERAL UDFs and stored procedures are written in Java, and hereafter are referred to simply as Java UDFs and stored procedures. Creating DB2GENERAL UDFs and stored procedures is very similar to creating UDFs and stored procedures in other supported programming languages. Once you have created and registered them, you can call them from programs in any language. Typically, you may call JDBC APIs from your stored procedures, but you can not call them from UDFs.
When you call PARAMETER STYLE DB2GENERAL UDFs and stored procedures, DB2
converts SQL types to and from Java types for you as described in Table 42. Several of these classes are provided in the Java
package COM.ibm.db2.app.
Table 42. DB2 SQL Types and Java Objects
| SQL Column Type | Java Type (UDF) | Java Type (Stored Procedure) |
|---|---|---|
| SMALLINT (500/501) | short | short |
| INTEGER (496/497) | int | int |
| BIGINT (492/493) | long | long |
| FLOAT (480/481) | double | double |
| REAL (480/481)(TBLST1) | float | float |
| DECIMAL(p,s) (484/485) | java.math.BigDecimal | java.math.BigDecimal |
| NUMERIC(p,s) (504/505) | java.math.BigDecimal | java.math.BigDecimal |
| CHAR(n) (452/453) | String | String |
| CHAR(n) FOR BIT DATA (452/453) | Blob | Blob |
| C null-terminated string (400/401)(TBLST2) | n/a | String |
| VARCHAR(n)(448/449) | String | String |
| VARCHAR(n) FOR BIT DATA (448/449) | Blob | Blob |
| LONG VARCHAR (456/457) | String | String |
| LONG VARCHAR FOR BIT DATA (456/457) | Blob | Blob |
| GRAPHIC(n) (468/469) | String | String |
| C null-terminated graphic string (460/461)(TBLST2) | n/a | String |
| VARGRAPHIC(n) (464/465) | String | String |
| LONG VARGRAPHIC (472/473)(TBLST3) | String | String |
| BLOB(n)(404/405)(TBLST3) | Blob | Blob |
| CLOB(n) (408/409)(TBLST3) | Clob | Clob |
| DBCLOB(n) (412/413)(TBLST3) | Clob | Clob |
| DATE (384/385)(TBLST4) | String | String |
| TIME (388/389)(TBLST4) | String | String |
| TIMESTAMP (392/393)(TBLST4) | String | String |
|
Notes:
| ||
Instances of classes COM.ibm.db2.app.Blob and COM.ibm.db2.app.Clob represent the LOB data types (BLOB, CLOB, and DBCLOB). These classes provide a limited interface to read LOBs passed as inputs, and write LOBs returned as outputs. Reading and writing of LOBs occur through standard Java I/O stream objects. For the Blob class, the routines getInputStream() and getOutputStream() return an InputStream or OutputStream object through which the BLOB content may be processed bytes-at-a-time. For a Clob, the routines getReader() and getWriter() will return a Reader or Writer object through which the CLOB or DBCLOB content may be processed characters-at-a-time.
If such an object is returned as an output using the set() method, code page conversions may be applied in order to represent the Java Unicode characters in the database code page.
Java stored procedures are very similar to Java UDFs. Like table functions, they can have multiple outputs. They also use the same conventions for NULL values, and the same set routine for output. The main difference is that a Java class that contains stored procedures must inherit from the COM.ibm.db2.app.StoredProc class instead of the COM.ibm.db2.app.UDF class. Refer to COM.ibm.db2.app.StoredProc for a description of the COM.ibm.db2.app.StoredProc class.
This interface provides the following routine to fetch a JDBC connection to the embedding application context:
public java.sql.Connection getConnection()
You can use this handle to run SQL statements. Other methods of the StoredProc interface are listed in the file sqllib/samples/java/StoredProc.java.
There are five classes/interfaces that you can use with Java Stored Procedures or UDFs:
The following sections describe the public aspects of these classes' behavior:
A Java class that contains methods intended to be called as PARAMETER STYLE DB2GENERAL stored procedures must be public and must implement this Java interface. You must declare such a class as follows:
public class <user-STP-class> extends COM.ibm.db2.app.StoredProc{ ... }
You can only call inherited methods of the COM.ibm.db2.app.StoredProc interface in the context of the currently executing stored procedure. For example, you cannot use operations on LOB arguments, result- or status-setting calls, etc., after a stored procedure returns. A Java exception will be thrown if you violate this rule.
Argument-related calls use a column index to identify the column being referenced. These start at 1 for the first argument. All arguments of a PARAMETER STYLE DB2GENERAL stored procedure are considered INOUT and thus are both inputs and outputs.
Any exception returned from the stored procedure is caught by the database and returned to the caller with SQLCODE -4302, SQLSTATE 38501. A JDBC SQLException or SQLWarning is handled specially and passes its own SQLCODE, SQLSTATE etc. to the calling application verbatim.
The following methods are associated with the COM.ibm.db2.app.StoredProc class:
public StoredProc() [default constructor]
This constructor is called by the database before the stored procedure call.
public boolean isNull(int) throws Exception
This function tests whether an input argument with the given index is an SQL NULL.
public void set(int, short) throws Exception public void set(int, int) throws Exception public void set(int, double) throws Exception public void set(int, float) throws Exception public void set(int, java.math.BigDecimal) throws Exception public void set(int, String) throws Exception public void set(int, COM.ibm.db2.app.Blob) throws Exception public void set(int, COM.ibm.db2.app.Clob) throws Exception
This function sets the output argument with the given index to the given value. The index has to refer to a valid output argument, the data type must match, and the value must have an acceptable length and contents. Strings with Unicode characters must be representable in the database code page. Errors result in an exception being thrown.
public java.sql.Connection getConnection() throws Exception
This function returns a JDBC object that represents the calling application's connection to the database. It is analogous to the result of a null SQLConnect() call in a C stored procedure.
A Java class that contains methods intended to be called as PARAMETER STYLE DB2GENERAL UDFs must be public and must implement this Java interface. You must declare such a class as follows:
public class <user-UDF-class> extends COM.ibm.db2.app.UDF{ ... }
You can only call methods of the COM.ibm.db2.app.UDF interface in the context of the currently executing UDF. For example, you cannot use operations on LOB arguments, result- or status-setting calls, etc., after a UDF returns. A Java exception will be thrown if this rule is violated.
Argument-related calls use a column index to identify the column being set. These start at 1 for the first argument. Output arguments are numbered higher than the input arguments. For example, a scalar UDF with three inputs uses index 4 for the output.
Any exception returned from the UDF is caught by the database and returned to the caller with SQLCODE -4302, SQLSTATE 38501.
The following methods are associated with the COM.ibm.db2.app.UDF class:
public UDF() [default constructor]
This constructor is called by the database at the beginning of a series of UDF calls. It precedes the first call to the UDF.
public void close()
This function is called by the database at the end of a UDF evaluation, if the UDF was created with the FINAL CALL option. It is analogous to the final call for a C UDF. For table functions, close() is called after the CLOSE call to the UDF method (if NO FINAL CALL is coded or defaulted), or after the FINAL call (if FINAL CALL is coded). If a Java UDF class does not implement this function, a no-op stub will handle and ignore this event.
public int getCallType() throws Exception
Table function UDF methods use getCallType() to find out the call type for a particular call. It returns a value as follows (symbolic defines are provided for these values in the Com.ibm.db2.app.UDF class definition):
public boolean isNull(int) throws Exception
This function tests whether an input argument with the given index is an SQL NULL.
public boolean needToSet(int) throws Exception
This function tests whether an output argument with the given index needs to be set. This may be false for a table UDF declared with DBINFO, if that column is not used by the UDF caller.
public void set(int, short) throws Exception public void set(int, int) throws Exception public void set(int, double) throws Exception public void set(int, float) throws Exception public void set(int, java.math.BigDecimal) throws Exception public void set(int, String) throws Exception public void set(int, COM.ibm.db2.app.Blob) throws Exception public void set(int, COM.ibm.db2.app.Clob) throws Exception
This function sets the output argument with the given index to the given value. The index has to refer to a valid output argument, the data type must match, and the value must have an acceptable length and contents. Strings with Unicode characters must be representable in the database code page. Errors result in an exception being thrown.
public void setSQLstate(String) throws Exception
This function may be called from a UDF to set the SQLSTATE to be returned from this call. A table UDF should call this function with "02000" to signal the end-of-table condition. If the string is not acceptable as an SQLSTATE, an exception will be thrown.
public void setSQLmessage(String) throws Exception
This function is similar to the setSQLstate function. It sets the SQL message result. If the string is not acceptable (for example, longer than 70 characters), an exception will be thrown.
public String getFunctionName() throws Exception
This function returns the name of the executing UDF.
public String getSpecificName() throws Exception
This function returns the specific name of the executing UDF.
public byte[] getDBinfo() throws Exception
This function returns a raw, unprocessed DBINFO structure for the executing UDF, as a byte array. You must first declare it with the DBINFO option.
public String getDBname() throws Exception public String getDBauthid() throws Exception public String getDBtbschema() throws Exception public String getDBtbname() throws Exception public String getDBcolname() throws Exception public String getDBver_rel() throws Exception public String getDBplatform() throws Exception public String getDBapplid() throws Exception
These functions return the value of the appropriate field from the DBINFO structure of the executing UDF.
public int[] getDBcodepg() throws Exception
This function returns the SBCS, DBCS, and composite code page numbers for the database, from the DBINFO structure. The returned integer array has the respective numbers as its first three elements.
public byte[] getScratchpad() throws Exception
This function returns a copy of the scratchpad of the currently executing UDF. You must first declare the UDF with the SCRATCHPAD option.
public void setScratchpad(byte[]) throws Exception
This function overwrites the scratchpad of the currently executing UDF with the contents of the given byte array. You must first declare the UDF with the SCRATCHPAD option. The byte array must have the same size as getScratchpad() returns.
This class provides utility routines that create temporary Blob or Clob objects for computation inside user-defined functions or stored procedures.
The following methods are associated with the COM.ibm.db2.app.Lob class:
public static Blob newBlob() throws Exception
This function creates a temporary Blob. It will be implemented using a LOCATOR if possible.
public static Clob newClob() throws Exception
This function creates a temporary Clob. It will be implemented using a LOCATOR if possible.
An instance of this class is passed by the database to represent a BLOB as UDF or stored procedure input, and may be passed back as output. The application may create instances, but only in the context of an executing UDF or stored procedure. Uses of these objects outside such a context will throw an exception.
The following methods are associated with the COM.ibm.db2.app.Blob class:
public long size() throws Exception
This function returns the length (in bytes) of the BLOB.
public java.io.InputStream getInputStream() throws Exception
This function returns a new InputStream to read the contents of the BLOB. Efficient seek/mark operations are available on that object.
public java.io.OutputStream getOutputStream() throws Exception
This function returns a new OutputStream to append bytes to the BLOB. Appended bytes become immediately visible on all existing InputStream instances produced by this object's getInputStream() call.
An instance of this class is passed by the database to represent a CLOB or DBCLOB as UDF or stored procedure input, and may be passed back as output. The application may create instances, but only in the context of an executing UDF or stored procedure. Uses of these objects outside such a context will throw an exception.
Clob instances store characters in the database code page. Some Unicode characters may not be representable in this code page, and may cause an exception to be thrown during conversion. This may happen during an append operation, or during a UDF or StoredProc set() call. This is necessary to hide the distinction between a CLOB and a DBCLOB from the Java programmer.
The following methods are associated with the COM.ibm.db2.app.Clob class:
public long size() throws Exception
This function returns the length (in characters) of the CLOB.
public java.io.Reader getReader() throws Exception
This function returns a new Reader to read the contents of the CLOB or DBCLOB. Efficient seek/mark operations are available on that object.
public java.io.Writer getWriter() throws Exception
This function returns a new Writer to append characters to this CLOB or DBCLOB. Appended characters become immediately visible on all existing Reader instances produced by this object's GetReader() call.
To indicate that a DB2DARI stored procedure should run as a NOT FENCED stored procedure, place it in the directory indicated in the Application Building Guide. For more information on NOT FENCED stored procedures, see NOT FENCED Stored Procedures.