"Michael" <in************ *@nowhere.com> wrote in message
news:WH******** **********@news 20.bellglobal.c om...
Running DB2 v7 UDB ("DB2 v7.1.0.93", "n031208" and "WR21333") on Windows
XP, I am unable to find out why the "Build for Debug" option within Stored
Procedure Builder is not enabled on Java stored procedures. It is enabled
for SQL stored procedures.
It is possible to "Build" and "Run" the Java SPs, it just isn't possible
to click on the "Build for Debug" option. Thanks for any help in advance.
I was never able to get the Debugger to work for Java SPs either, despite
carefully following all instructions that were supposed to work; they worked
for many others but never for me. Mind you, I've never been great with
installing and configuring!
Let me suggest that you search the archives via Google Groups for the
technique of getting the debugger to work with Java SPs in Version 7 of UDB:
they may well work for you. Or consider upgrading to Version 8.
If an upgrade to Version 8 isn't possible and you can't get the debugger to
work, you can always resort to more old-fashioned techniques. I debugged
several Java SPs by writing System.out.prin tln() statements to a simple log.
In fact, I even wrote a class called DB2RoutineLogge r which I call from my
Java stored procs and UDFs. Here is the code for that class, followed by an
illustration of the technique for invoking it. It's not as nice as a full
screen interactive debugger but it does the job....
------------------------------------------------------------------------------------------------
package com.mydomain.ud f.java;
import java.io.Buffere dWriter;
import java.io.File;
import java.io.FileWri ter;
import java.io.IOExcep tion;
import java.io.PrintWr iter;
import java.io.StringW riter;
import java.text.Simpl eDateFormat;
import java.util.Date;
import java.util.Local e;
/**
* @author Rhino
*
*/
public class DB2RoutineLogge r {
/*
*
* Variables and constants needed to support logging of this UDF.
*
*/
/**
* The path identifying the directory where the log will be written if
the
* program is running on a Windows operating system, e.g. Windows 2000,
* Windows XP. NOTE: The file separator character for Windows, '\',
needs to
* be written TWICE each time it occurs; the first backslash in each
pair is
* the Java escape character and the second backslash in each pair is
the
* actual file separator. Example: "C:\\RDS\\l ogs"
*/
private String logPathWindows = "C:\\RDS\\logs" ;
/**
* The path identifying the directory where the log will be written if
the
* program is running on a Unix operating system, e.g. AIX/6000, Linux.
* Example: "/home/rds/logs"
*/
private String logPathUnix = "/home/rds/logs";
/**
* The text that will begin all logging-related error messages to the
user,
* assuring them that they did nothing wrong and telling them (and
reminding
* support staff) that further information is available in DB2's
db2diag.log
* file, which is usually situated in SQLLIB\DB2.
*
* DB2 is only capable of showing around 40 characters worth of
* programmer-supplied text in the SQL4302N message that is displayed
when
* the program throws an exception. Since the following text is the most
* important information for the *USER* to see, this is made the leading
* part of the information that is displayed. Additional information is
* normally written to the db2diag.log for the use of systems personnel
in
* diagnosing the problem.
*/
private static final String USER_MESSAGE = "Internal error. Support: See
db2diag.log. ";
/** The desired format of the timestamp used in the log. */
private static final String TIMESTAMP_PATTE RN = "yyyy-MM-dd
HH.mm.ss.SSS";
/**
* The Simple Date Format that will be used for creating the current
* timestamp.
*/
private static final SimpleDateForma t TIMESTAMP_FORMA T = new
SimpleDateForma t(
TIMESTAMP_PATTE RN, Locale.getDefau lt());
/** The log file which is written by this class. */
private File outputFile = null;
/** A String concatenation that identifies which class has called this
class. */
private String sourceCodeLocat ion = null;
/**
* This constructor establishes a reference to an instance of the class
and creates
* the String concatenation which identifies the class which invoked the
constructor,
* which is the class that needs diagnostics generated.
*
* @param incomingPackage Name the name of the package containing the
class which invoked this class
* @param incomingClassNa me the name of the class which invoked this
class
* @param incomingMethodN ame the name of the method which invoked this
class
*/
public DB2RoutineLogge r(String incomingPackage Name, String
incomingClassNa me, String incomingMethodN ame) {
this.sourceCode Location = incomingPackage Name + "." +
incomingClassNa me + "." + incomingMethodN ame;
}
/*
*
* Utility methods used primarily to support logging.
*
*/
/**
* Set the log path for a Unix environment.
*
* @param String the desired log path in a Unix environment
*/
public void setLogPathUnix( String logPathUnix) {
this.logPathUni x = logPathUnix;
}
/**
* Get the log path for a Unix environment.
*
* @return String the log path currently set for a Unix environment
*/
public String getLogPathUnix( ) {
return logPathUnix;
}
/**
* Set the log path for a Windows environment.
*
* @param String logPathWindows the desired log path in a Windows
environment
*/
public void setLogPathWindo ws(String logPathWindows) {
this.logPathWin dows = logPathWindows;
}
/**
* Get the log path for a Windows environment.
*
* @return String the log path currently set for a Windows environment
*/
public String getLogPathWindo ws() {
return logPathWindows;
}
/**
* Method getLogPath() chooses the log path based on the operating
system name.
*
* @version 1.0
* @since 2005-03-03
*
* @return String log path
*/
public String getLogPath() {
/*
* Determine the directory in which the log file will be written
based on the
* current operating system.
*/
if (System.getProp erty("os.name") .startsWith("Wi ndows")) {
return logPathWindows;
}
else {
return logPathUnix;
}
}
/**
* Method createFile() creates an output file.
*
* <p>
* If the log file cannot be created for some reason, carry on
regardless with the
* "main mission" of the UDF. Don't inform the user. [The
System.err.prin tln()
* statements in the catch blocks are just placeholders; they won't
actually
* be written when running in a UDF.] The developer who wanted to
activate logging
* will realize that logging failed when the log isn't where it was
intended to be.
* </p>
*
* @version 1.0
* @since 2005-03-03
*
* @see #writeToFile(Bu fferedWriter, String)
* @see #closeFile(Buff eredWriter)
*
* @param path the directory path into which the file will be written
* @param fileName the name of the file which will be written
* @return BufferedWriter
*/
public BufferedWriter createFile(Stri ng path, String fileName) {
String METHOD_NAME = "createFile ()";
/*
* See if the output directory exists; if it doesn't, create it and
any
* needed parent directories.
*/
File outputPath = new File(path);
if (!outputPath.ex ists()) {
try {
outputPath.mkdi rs();
} catch (SecurityExcept ion s_excp) {
System.err.prin tln(USER_MESSAG E + "Problem:
SecurityExcepti on ("
+ s_excp.getMessa ge() + "). Code location: "
+ sourceCodeLocat ion
+ ". Programmer message: Could not create output
path, "
+ outputPath + ".");
}
}
/* See if the output file exists; if it doesn't, create it. */
outputFile = new File(outputPath , fileName);
if (!outputFile.ex ists()) {
try {
outputFile.crea teNewFile();
} catch (IOException io_excp) {
System.err.prin tln(USER_MESSAG E + "Problem: IOException ("
+ io_excp.getMess age() + "). Code location: "
+ sourceCodeLocat ion
+ ". Programmer message: Could not create log file,
" + outputFile + ".");
} catch (SecurityExcept ion s_excp) {
System.err.prin tln(USER_MESSAG E + "Problem:
SecurityExcepti on ("
+ s_excp.getMessa ge() + "). Code location: "
+ sourceCodeLocat ion
+ ". Programmer message: Could not create log file,
" + outputFile + ".");
}
}
BufferedWriter bufferedWriter = null;
/* Ensure that the FileWriter is opened in such a way that each
write appends to the file. */
try {
FileWriter fileWriter = new FileWriter(path + File.separator +
fileName, true);
bufferedWriter = new BufferedWriter( fileWriter);
} catch (IOException io_excp) {
System.err.prin tln(USER_MESSAG E + "Problem: IOException ("
+ io_excp.getMess age() + "). Code location: "
+ sourceCodeLocat ion
+ ". Programmer message: Could not create FileWriter or
BufferedWriter. ");
}
return (bufferedWriter );
}
/**
* Method writeToFile() writes a single line a file.
*
* <p>
* If the log file cannot be written for some reason, carry on
regardless with the
* "main mission" of the UDF. Don't inform the user. [The
System.err.prin tln()
* statement in the catch block is just a placeholder; it won't actually
* be written when running in a UDF.] The developer who wanted to write
to the
* log will presumably realize that there was a problem when the log is
empty
* or when some of the expected lines are missing.
* </p>
*
* @version 1.0
* @since 2005-03-03
*
* @see #createFile(Str ing, String)
*
* @param outputFile
* the BufferedWriter for the file
* @param oneLine
* the line which is to be written to the file
*/
public void writeToFile(Buf feredWriter outputFile, String oneLine) {
String METHOD_NAME = "writeToFile()" ;
/* Get the current timestamp from the system. */
Date currentDateTime = new Date(System.cur rentTimeMillis( ));
/* Format the timestamp. */
String formattedDateTi me = TIMESTAMP_FORMA T.format(curren tDateTime);
/*
* Create the locator, which concatenates the formatted current
timestamp
* and the source code location to identify exactly when a given
event took
* place and what source code initiated that event.
*/
String locator = formattedDateTi me + " - " + sourceCodeLocat ion +
" - ";
/*
* Write the incoming line of information after the locator, write a
newline,
* and then flush to ensure that all output is written to the file,
not left
* behind in the buffer.
*/
try {
outputFile.writ e(locator + oneLine);
outputFile.newL ine();
outputFile.flus h();
} catch (IOException io_excp) {
System.err.prin tln(USER_MESSAG E + "Problem: IOException ("
+ io_excp.getMess age() + "). Code location: "
+ sourceCodeLocat ion
+ ". Programmer message: Could not create write line, '"
+ oneLine + "', to log.");
}
}
/**
* Method closeFile() closes the file.
*
* <p>
* If the log file cannot be closed for some reason, carry on regardless
with the
* "main mission" of the UDF. Don't inform the user. [The
System.err.prin tln()
* statement in the catch block is just a placeholder; it won't actually
* be written when running in a UDF.] The developer who wanted to close
the log
* probably won't care if the log isn't closed and will rely on the
operating
* system to close it eventually.
* </p>
*
* @version 1.0
* @since 2005-03-03
*
* @see #createFile(Str ing, String)
*
* @param outputFile
* the BufferedWriter for the file which is to be closed
*/
public void closeFile(Buffe redWriter outputFile) {
String METHOD_NAME = "closeFile( )";
try {
outputFile.clos e();
} catch (IOException io_excp) {
System.err.prin tln(USER_MESSAG E + "Problem: IOException ("
+ io_excp.getMess age() + "). Code location: "
+ sourceCodeLocat ion
+ ". Programmer message: Could not close log.");
}
}
/**
* Method getStack() writes a stackTrace to a String, given a Throwable.
*
* <p>This is a utility method that will appear in every UDF that needs
to return
* a stackTrace.</p>
*
* @version 1.0
* @since 2004
*
* @param Throwable an Error or Exception
* @return String containing the stackTrace for the Throwable in String
format
*/
public String getStack (Throwable throwable) {
StringWriter stringWriter = new StringWriter();
PrintWriter printWriter = new PrintWriter(str ingWriter);
throwable.print StackTrace(prin tWriter);
printWriter.clo se();
return (stringWriter.t oString());
}
}
------------------------------------------------------------------------------------------------
To invoke the DB2RoutineLogge r class from my UDF or stored procedure, I
include the following class variables in the SP or UDF:
-----------------------------------------------------------------------------------------------
/**
* If true, file logging of this UDF will be attempted. If successful,
the log output
* will be written to a file with the name specified in the 'LOG_FILE'
constant. The
* path to that file is operating system dependent; see the code in the
getLogPath()
* method.
*/
private static final boolean DO_LOGGING = true;
/**
* The name of the package. This need not be the actual package name;
instead it is used
* primarily to distinguish between UDFs that are written using
parameter style DB2GENERAL
* from UDFs written with parameter style DB2JAVA on the theory that
UDFs written for one
* parameter style will be in a different package than UDFs written in
the other parameter
* style.
*/
private static final String PACKAGE_NAME = "DB2JAVA";
/** The name of this class. */
private static final String CLASS_NAME = "Counter";
/** The name of the log file. */
private static final String LOG_FILE = "myUDFandSP.log "; //replace the
constant with a name you like better
/** The buffered writer which is used to write the log. */
private static BufferedWriter log = null;
/** The class used to write log information. */
private static DB2RoutineLogge r logger = null;
------------------------------------------------------------------------------------------------
Then, in the constructor for my SP or UDF, I include this code to create the
log file and to close it when the UDF/SP is finished:
------------------------------------------------------------------------------------------------
String METHOD_NAME = "myMethod() "; //replace the constant with the
actual method name
/*
* Create the log file and write the input parameters to the log. If
the log file
* cannot be created for some reason, this will be obvious to the
developer by the
* absence of the log file. Proceed with the "main mission" of the
UDF.
*/
if (DO_LOGGING) {
logger = new DB2RoutineLogge r(PACKAGE_NAME, CLASS_NAME,
METHOD_NAME);
log = logger.createFi le(logger.getLo gPath(), LOG_FILE);
}
// DO THE NORMAL WORK OF THE UDF or SP.
if (DO_LOGGING) {
logger.closeFil e(log);
}
------------------------------------------------------------------------------------------------
Then, every time I want to display a variable value from the SP or UDF, I
execute code like this:
------------------------------------------------------------------------------------------------
if (DO_LOGGING) {
logger.writeToF ile(log, "count: " + count); //replace 'count'
with your variable name
}
------------------------------------------------------------------------------------------------
I hope this helps.
Rhino
