Stored Procedure Debugging Tool

Stephen Vagnier

Stored Procedure Debugging Tool
We have been using the IBM Stored Procedure Builder (SPB) tool to setup
Java stored procedures for a large application system. The one
drawback with the SPB tool is that we cannot debug the code in the stored
procedures. The SPB tool has debugging options but they seem
to be turned off. I quickly reviewed the online help in the SPB tool and
it states that the DB2 server needs to implement this.
I am assuming this is the "Distributed Debugger" that IBM gave a
presentation (#Z08) at the technical conference last year.

My question is there a way to setup the SPB tool to allow us to display
output at runtime? In Java there is a System.out.println()
method that writes text to the standard output. We can use this as a
crude debugger if we can get SPB to display the standard
output. Right now all we receive are IBM error codes that is of little
help debugging the code.
How do I get the SPB to display the standard output back?

Any help would be most appreciated...


****************************************
Steve Vagnier
American Electric Power
Information Management Infrastructure
One Riverside Plaza
Columbus, Ohio 43215
Email: [login to unmask email]
Phone: 614-716-3677
Audinet: 200-3677



Abhijit Sinha

Re: Stored Procedure Debugging Tool
(in response to Stephen Vagnier)
Steve,

Anything with System.out.println() will never appear in the "Results" tab of
the SPB, or at the command prompt if you decide to use "CALL
mySchema.mySP()", so other than having to use the IBM Distributed Debugger
(which is quite cumbersome to setup and doesn't work for nested method calls
to other Java SPs), the only way seems to be is to write your debug trace
statements to an external output file.

E.g.
1. Create a lightweight new Java SP like below:
import java.io.*;
public class Mytrace
{
//On NT
//private static final String traceFile = "c:\\sptrace.txt";
//On Solaris
private static final String traceFile = "/home/userid/SPTrace.out";

public static void mytrace() throws Exception
{
return;
}
public static void trace_me (String outStr) throws Exception
{
PrintWriter out = new PrintWriter(new FileWriter(traceFile, true),
true);
out.println(outStr);
out.close();
return;
}
}

2. From the SP you are trying to debug, issue statements like:
Mytrace.trace_me("My stored procedure is being called now...");
Of course, you have to import the MyTrace class if not in same package as
this SP.

3. Open your trace file as specified in MyTrace class to see your debug
outputs. On Unix, you can "tail -f " the trace output file, and as your
debug statements are being executed, you can see the output spewing out in
realtime. No such facilities on Windows of course.

4. Exercise caution over the number of debug statements. Having an
overwhelming number of trace statements can slightly slow down your SP
execution as more I/O to disk will be performed.

Abhijit



-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 09, 2003 12:36 PM
To: [login to unmask email]
Subject: Stored Procedure Debugging Tool


We have been using the IBM Stored Procedure Builder (SPB) tool to setup
Java stored procedures for a large application system. The one
drawback with the SPB tool is that we cannot debug the code in the stored
procedures. The SPB tool has debugging options but they seem
to be turned off. I quickly reviewed the online help in the SPB tool and
it states that the DB2 server needs to implement this.
I am assuming this is the "Distributed Debugger" that IBM gave a
presentation (#Z08) at the technical conference last year.

My question is there a way to setup the SPB tool to allow us to display
output at runtime? In Java there is a System.out.println()
method that writes text to the standard output. We can use this as a
crude debugger if we can get SPB to display the standard
output. Right now all we receive are IBM error codes that is of little
help debugging the code.
How do I get the SPB to display the standard output back?

Any help would be most appreciated...


****************************************
Steve Vagnier
American Electric Power
Information Management Infrastructure
One Riverside Plaza
Columbus, Ohio 43215
Email: [login to unmask email]
Phone: 614-716-3677
Audinet: 200-3677








Stephen Vagnier

Re: Stored Procedure Debugging Tool
(in response to Abhijit Sinha)
Abhijit,

Thank-you for the suggestions...


Regards..

****************************************
Steve Vagnier
American Electric Power
Information Management Infrastructure
One Riverside Plaza
Columbus, Ohio 43215
Email: [login to unmask email]
Phone: 614-716-3677
Audinet: 200-3677




"Sinha, Abhijit"
<[login to unmask email] To: [login to unmask email]
COM> cc:
Sent by: DB2 Data Subject: Re: Stored Procedure Debugging Tool
Base Discussion
List
<[login to unmask email]
ASSOC.COM>


01/09/2003 01:16
PM
Please respond to
DB2 Data Base
Discussion List






Steve,

Anything with System.out.println() will never appear in the "Results" tab
of
the SPB, or at the command prompt if you decide to use "CALL
mySchema.mySP()", so other than having to use the IBM Distributed Debugger
(which is quite cumbersome to setup and doesn't work for nested method
calls
to other Java SPs), the only way seems to be is to write your debug trace
statements to an external output file.

E.g.
1. Create a lightweight new Java SP like below:
import java.io.*;
public class Mytrace
{
//On NT
//private static final String traceFile = "c:\\sptrace.txt";
//On Solaris
private static final String traceFile = "/home/userid/SPTrace.out";

public static void mytrace() throws Exception
{
return;
}
public static void trace_me (String outStr) throws Exception
{
PrintWriter out = new PrintWriter(new FileWriter(traceFile, true),
true);
out.println(outStr);
out.close();
return;
}
}

2. From the SP you are trying to debug, issue statements like:
Mytrace.trace_me("My stored procedure is being called now...");
Of course, you have to import the MyTrace class if not in same package as
this SP.

3. Open your trace file as specified in MyTrace class to see your debug
outputs. On Unix, you can "tail -f " the trace output file, and as your
debug statements are being executed, you can see the output spewing out in
realtime. No such facilities on Windows of course.

4. Exercise caution over the number of debug statements. Having an
overwhelming number of trace statements can slightly slow down your SP
execution as more I/O to disk will be performed.

Abhijit



-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 09, 2003 12:36 PM
To: [login to unmask email]
Subject: Stored Procedure Debugging Tool


We have been using the IBM Stored Procedure Builder (SPB) tool to setup
Java stored procedures for a large application system. The one
drawback with the SPB tool is that we cannot debug the code in the stored
procedures. The SPB tool has debugging options but they seem
to be turned off. I quickly reviewed the online help in the SPB tool and
it states that the DB2 server needs to implement this.
I am assuming this is the "Distributed Debugger" that IBM gave a
presentation (#Z08) at the technical conference last year.

My question is there a way to setup the SPB tool to allow us to display
output at runtime? In Java there is a System.out.println()
method that writes text to the standard output. We can use this as a
crude debugger if we can get SPB to display the standard
output. Right now all we receive are IBM error codes that is of little
help debugging the code.
How do I get the SPB to display the standard output back?

Any help would be most appreciated...


****************************************
Steve Vagnier
American Electric Power
Information Management Infrastructure
One Riverside Plaza
Columbus, Ohio 43215
Email: [login to unmask email]
Phone: 614-716-3677
Audinet: 200-3677



the





the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Mike Polley

Re: Stored Procedure Debugging Tool
(in response to Stephen Vagnier)
Maybe you should check out DevPartner:

PRODUCT REVIEW:
DevPartnerDB for Microsoft SQL Server

By Kay Ewbank

VERDICT: A straightforward way to manage stored procedures

SQL might be a language that consists of only a few verbs, but like any
programming language, those few are enough to get you into a mess. This is
particularly true if you're writing Transact SQL stored procedures, which is
partly due to the less-than-sophisticated development environment.
DevPartnerDB for Microsoft SQL Server offers an integrated development,
debugging and profiling environment where you can build, test and manage
stored procedures in Transact SQL. (If you develop in Oracle, an Oracle
version is also available.)
One problem with testing stored procedures is that they aren't usually used
'cold'. A good thing about stored procedures is that you can use them from
applications outside the database manager. If you do this, though, it is
hard to be sure the procedure works from that application - or rather, it
makes it hard to understand why it's not working. DevPartnerDB's development
environment manages your stored procedures in the context of the calling
application, so you can debug both stored procedures and triggers as they
would be used for real. All the variable data is captured as it is passed
between your application and the database and you can control the steps of
the stored procedure or the trigger while it's running. You can also look at
and change the values in the variables during the execution.
You are given a template editor to get you started writing stored procedures
and an object browser so you can choose the objects you need to reference.
There's also a GREP search utility that allows you to find particular source
code or table data from within your database, so you can do things such as
finding the data that's causing the problems with your stored procedures. If
you're developing with other people, DevPartnerDB has team development
options such as project folders and check in/check out options for database
objects.
Other goodies include a resource lock monitor that lets you see when a
stored procedure has come to a halt because of locking problems. You can see
what the resource locking problem is and who or what has the resources
locked, so you can correct the problem.
Another black hole for most developers is why applications run slowly.
DevPartnerDB provides profiling data so you can analyse which lines in your
stored procedures are taking the time, and compare sessions to see whether
the problem changes or is constant.

(c) Copyright Dennis Publishing Limited licensed by Felden

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 09, 2003 12:36 PM
To: [login to unmask email]
Subject: Stored Procedure Debugging Tool


We have been using the IBM Stored Procedure Builder (SPB) tool to setup
Java stored procedures for a large application system. The one
drawback with the SPB tool is that we cannot debug the code in the stored
procedures. The SPB tool has debugging options but they seem
to be turned off. I quickly reviewed the online help in the SPB tool and
it states that the DB2 server needs to implement this.
I am assuming this is the "Distributed Debugger" that IBM gave a
presentation (#Z08) at the technical conference last year.

My question is there a way to setup the SPB tool to allow us to display
output at runtime? In Java there is a System.out.println()
method that writes text to the standard output. We can use this as a
crude debugger if we can get SPB to display the standard
output. Right now all we receive are IBM error codes that is of little
help debugging the code.
How do I get the SPB to display the standard output back?

Any help would be most appreciated...


****************************************
Steve Vagnier
American Electric Power
Information Management Infrastructure
One Riverside Plaza
Columbus, Ohio 43215
Email: [login to unmask email]
Phone: 614-716-3677
Audinet: 200-3677








The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.



Myron Miller

Re: Stored Procedure Debugging Tool
(in response to Mike Polley)
Mike,
Maybe I'm missing something, but I don't see where this will help with DB2
SQL stored procedures. Microsoft SQL Server and Oracle yes, but I don't see
where they support DB2.

Myron
--- "Polley, Mike" <[login to unmask email]> wrote:
> Maybe you should check out DevPartner:
>
> PRODUCT REVIEW:
> DevPartnerDB for Microsoft SQL Server
>
> By Kay Ewbank
>
> VERDICT: A straightforward way to manage stored procedures
>
> SQL might be a language that consists of only a few verbs, but like any
> programming language, those few are enough to get you into a mess. This is
> particularly true if you're writing Transact SQL stored procedures, which is
> partly due to the less-than-sophisticated development environment.
> DevPartnerDB for Microsoft SQL Server offers an integrated development,
> debugging and profiling environment where you can build, test and manage
> stored procedures in Transact SQL. (If you develop in Oracle, an Oracle
> version is also available.)
> One problem with testing stored procedures is that they aren't usually used
> 'cold'. A good thing about stored procedures is that you can use them from
> applications outside the database manager. If you do this, though, it is
> hard to be sure the procedure works from that application - or rather, it
> makes it hard to understand why it's not working. DevPartnerDB's development
> environment manages your stored procedures in the context of the calling
> application, so you can debug both stored procedures and triggers as they
> would be used for real. All the variable data is captured as it is passed
> between your application and the database and you can control the steps of
> the stored procedure or the trigger while it's running. You can also look at
> and change the values in the variables during the execution.
> You are given a template editor to get you started writing stored procedures
> and an object browser so you can choose the objects you need to reference.
> There's also a GREP search utility that allows you to find particular source
> code or table data from within your database, so you can do things such as
> finding the data that's causing the problems with your stored procedures. If
> you're developing with other people, DevPartnerDB has team development
> options such as project folders and check in/check out options for database
> objects.
> Other goodies include a resource lock monitor that lets you see when a
> stored procedure has come to a halt because of locking problems. You can see
> what the resource locking problem is and who or what has the resources
> locked, so you can correct the problem.
> Another black hole for most developers is why applications run slowly.
> DevPartnerDB provides profiling data so you can analyse which lines in your
> stored procedures are taking the time, and compare sessions to see whether
> the problem changes or is constant.
>
> (c) Copyright Dennis Publishing Limited licensed by Felden
>
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Thursday, January 09, 2003 12:36 PM
> To: [login to unmask email]
> Subject: Stored Procedure Debugging Tool
>
>
> We have been using the IBM Stored Procedure Builder (SPB) tool to setup
> Java stored procedures for a large application system. The one
> drawback with the SPB tool is that we cannot debug the code in the stored
> procedures. The SPB tool has debugging options but they seem
> to be turned off. I quickly reviewed the online help in the SPB tool and
> it states that the DB2 server needs to implement this.
> I am assuming this is the "Distributed Debugger" that IBM gave a
> presentation (#Z08) at the technical conference last year.
>
> My question is there a way to setup the SPB tool to allow us to display
> output at runtime? In Java there is a System.out.println()
> method that writes text to the standard output. We can use this as a
> crude debugger if we can get SPB to display the standard
> output. Right now all we receive are IBM error codes that is of little
> help debugging the code.
> How do I get the SPB to display the standard output back?
>
> Any help would be most appreciated...
>
>
> ****************************************
> Steve Vagnier
> American Electric Power
> Information Management Infrastructure
> One Riverside Plaza
> Columbus, Ohio 43215
> Email: [login to unmask email]
> Phone: 614-716-3677
> Audinet: 200-3677
>
>
>
>
>
>
>
>
> The contents of this e-mail are intended for the named addressee only. It
> contains information that may be confidential. Unless you are the named
> addressee or an authorized designee, you may not copy or use it, or disclose
> it to anyone else. If you received it in error please notify us immediately
> and then destroy it.
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



Mike Polley

Re: Stored Procedure Debugging Tool
(in response to Myron Miller)
Oops, wrong listserv. However, if you're writing your stored procedures in
Cobol or PL/1 then possibly Xpediter would provide a debugging solution.
Not sure though. Contact a DevPartner rep and see what they say.

-----Original Message-----
From: Myron Miller [mailto:[login to unmask email]
Sent: Thursday, January 09, 2003 3:53 PM
To: [login to unmask email]
Subject: Re: Stored Procedure Debugging Tool


Mike,
Maybe I'm missing something, but I don't see where this will help with
DB2
SQL stored procedures. Microsoft SQL Server and Oracle yes, but I don't see
where they support DB2.

Myron
--- "Polley, Mike" <[login to unmask email]> wrote:
> Maybe you should check out DevPartner:
>
> PRODUCT REVIEW:
> DevPartnerDB for Microsoft SQL Server
>
> By Kay Ewbank
>
> VERDICT: A straightforward way to manage stored procedures
>
> SQL might be a language that consists of only a few verbs, but like any
> programming language, those few are enough to get you into a mess. This is
> particularly true if you're writing Transact SQL stored procedures, which
is
> partly due to the less-than-sophisticated development environment.
> DevPartnerDB for Microsoft SQL Server offers an integrated development,
> debugging and profiling environment where you can build, test and manage
> stored procedures in Transact SQL. (If you develop in Oracle, an Oracle
> version is also available.)
> One problem with testing stored procedures is that they aren't usually
used
> 'cold'. A good thing about stored procedures is that you can use them from
> applications outside the database manager. If you do this, though, it is
> hard to be sure the procedure works from that application - or rather, it
> makes it hard to understand why it's not working. DevPartnerDB's
development
> environment manages your stored procedures in the context of the calling
> application, so you can debug both stored procedures and triggers as they
> would be used for real. All the variable data is captured as it is passed
> between your application and the database and you can control the steps of
> the stored procedure or the trigger while it's running. You can also look
at
> and change the values in the variables during the execution.
> You are given a template editor to get you started writing stored
procedures
> and an object browser so you can choose the objects you need to reference.
> There's also a GREP search utility that allows you to find particular
source
> code or table data from within your database, so you can do things such as
> finding the data that's causing the problems with your stored procedures.
If
> you're developing with other people, DevPartnerDB has team development
> options such as project folders and check in/check out options for
database
> objects.
> Other goodies include a resource lock monitor that lets you see when a
> stored procedure has come to a halt because of locking problems. You can
see
> what the resource locking problem is and who or what has the resources
> locked, so you can correct the problem.
> Another black hole for most developers is why applications run slowly.
> DevPartnerDB provides profiling data so you can analyse which lines in
your
> stored procedures are taking the time, and compare sessions to see whether
> the problem changes or is constant.
>
> (c) Copyright Dennis Publishing Limited licensed by Felden
>
> -----Original Message-----
> From: [login to unmask email] [mailto:[login to unmask email]
> Sent: Thursday, January 09, 2003 12:36 PM
> To: [login to unmask email]
> Subject: Stored Procedure Debugging Tool
>
>
> We have been using the IBM Stored Procedure Builder (SPB) tool to setup
> Java stored procedures for a large application system. The one
> drawback with the SPB tool is that we cannot debug the code in the stored
> procedures. The SPB tool has debugging options but they seem
> to be turned off. I quickly reviewed the online help in the SPB tool
and
> it states that the DB2 server needs to implement this.
> I am assuming this is the "Distributed Debugger" that IBM gave a
> presentation (#Z08) at the technical conference last year.
>
> My question is there a way to setup the SPB tool to allow us to display
> output at runtime? In Java there is a System.out.println()
> method that writes text to the standard output. We can use this as a
> crude debugger if we can get SPB to display the standard
> output. Right now all we receive are IBM error codes that is of little
> help debugging the code.
> How do I get the SPB to display the standard output back?
>
> Any help would be most appreciated...
>
>
> ****************************************
> Steve Vagnier
> American Electric Power
> Information Management Infrastructure
> One Riverside Plaza
> Columbus, Ohio 43215
> Email: [login to unmask email]
> Phone: 614-716-3677
> Audinet: 200-3677
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
>
> The contents of this e-mail are intended for the named addressee only. It
> contains information that may be confidential. Unless you are the named
> addressee or an authorized designee, you may not copy or use it, or
disclose
> it to anyone else. If you received it in error please notify us
immediately
> and then destroy it.
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com








The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.