I've been playing with Java UDFs for the last couple of days and I've got
some questions about scratchpads. I'm running DB2 LUW V8 (FP8) on WinXP.
Somewhere in the manuals, I found some remarks that said I could either
manage my scratchpad with the getScratchpad() and setScratchpad() methods
*OR* set up my own class variables to keep the state between invocations of
the UDF. (I can't find those remarks again right now but I know I saw them.)
I proved that those remarks were correct by developing two successful
variants of the same simple UDF. The code is appended below.
My question is, having tried writing both, why would anyone use
getScratchpad() and setScratchpad() when it is so much easier to just write
each value that needs to be saved to a class variable? The latter approach
is easier and much more concise since you don't have to set up and read the
byte streams that are required to work with getScratchpad() and
setScratchpad().
Still, I know that the developers at IBM are not fools so I assume there are
some genuine benefits to use getScratchpad() and setScratchpad() or some
major risks or limitations to just using class variables so I would be very
interested in hearing more about those risks and benefits. That would help
me choose the best coding style for UDFs.
Here is the code I wrote contrasting the two approaches to scratchpads so
that everyone can see the differences in the size of the two methods. This
code was written using the DB2GENERAL parameter style since the JAVA
parameter style doesn't support scratchpads.
----------------------------------------------------------------------------
---------------------
public class CountRows extends UDF {
/**
* The scratchpad for the numberLines() method. It *must* be initialized
outside of
* the method.
*/
private int lineCounter = 0;
/**
* The row counter for the numberRows() method. It *must* be initialized
outside of
* the method.
*/
private int rowCounter = 0;
/** The scratchpad buffer for the numberRows() method. */
private byte[] scratchpadBuffer;
/**
* Method numberLines() is used to number the lines of a result set.
*
* <p>This method accomplishes the same result as the numberRows()
method elsewhere
* in this class but uses a class variable instead of the
'getScratchpad()' and
* 'setScratchpad()' methods so that developers can make an informed
comparison
* between the two approaches.</p>
*
* @return int the current number of lines in the result set
* @throws Exception
*/
public void numberLines(int lineCount) throws Exception {
String METHOD_NAME = "numberLines()";
try {
/*
* Increment the line counter. The counter starts at 0 and
increments once for each
* row in the result set of the query. Therefore, the lines of
the result set will be
* numbered 1, 2, 3, etc.
*/
lineCounter++;
/* Return the value of the line counter. */
set(1, lineCounter);
} catch (Exception excp) {
excp.printStackTrace();
throw new IllegalArgumentException("countRows() - " +
excp.getMessage());
}
}
/**
* Method numberRows() is used to number the rows of a result set.
*
* <p>This method accomplishes the same result as the numberLines()
method elsewhere
* in this class but uses the 'getScratchpad()' and 'setScratchpad()'
methods
* instead of a class variable so that developers can make an informed
comparison
* between the two approaches.</p>
*
* @return int the current number of rows in the result set
* @throws Exception
*/
public void numberRows(int lineCount) throws Exception {
String METHOD_NAME = "numberRows()";
try {
/* Initialize the scratchpad buffer. */
scratchpadBuffer = getScratchpad();
/* Read the contents of the DB2 scratchpad. */
ByteArrayInputStream byteArrayInputStream = new
ByteArrayInputStream(scratchpadBuffer);
DataInputStream dataInputStream = new
DataInputStream(byteArrayInputStream);
/* Write a byte stream containing the initial value of the row
counter, which is 0. */
ByteArrayOutputStream byteArrayOutputStream = new
ByteArrayOutputStream();
DataOutputStream dataOutputStream = new
DataOutputStream(byteArrayOutputStream);
dataOutputStream.writeInt(rowCounter);
/*
* Copy the byte stream to a byte array. Copy that byte array to
*another* byte array.
* Set the DB2 scratchpad equal to the second byte array.
*/
byte[] byteArrayBuffer = byteArrayOutputStream.toByteArray();
for(int ix = 0; ix < byteArrayBuffer.length; ix++) {
scratchpadBuffer[ix] = byteArrayBuffer[ix];
}
setScratchpad(scratchpadBuffer);
/* Get the DB2 scratchpad and parse it to get the current value
of the row counter. */
byte[] scratchpadBuffer = getScratchpad();
rowCounter = dataInputStream.readInt();
/*
* Increment the line counter. The counter starts at 0 and
increments once for each
* row in the result set of the query. Therefore, the lines of
the result set will
* be numbered 1, 2, 3, etc.
*/
rowCounter++;
/* Return the value of the row counter. */
set(1, rowCounter);
} catch (IOException excp) {
excp.printStackTrace();
throw new IllegalArgumentException("numberRows() - " +
excp.getMessage());
} catch (Exception excp) {
excp.printStackTrace();
throw new IllegalArgumentException("numberRows() - " +
excp.getMessage());
}
}
}
----------------------------------------------------------------------------
---------------------
--
Rhino
