DataBase - Simple debugging of procedures?

Asked By Troels Arvin

05-Sep-10 05:17 PM

Hello,

Is there a simple way to debug DB2 SQL procedures, such as: which branch
of a conditional construct was chosen, what value did a counter reach,
etc, _without_ installing some big, complicated IDE?

My question could maybe be rephrased as "is there a way to perform printf-
style debugging in DB2 SQL procedures?"

If not: I am considering creating a C-based UDF which logs arbitrary
strings to syslog, so that a SQL procedure can inform the surroundings of
its state during development.

--
Troels

05-Sep-10 07:05 PM

-

I do not know if Serge's SQL PL/PL/SQL profiler would be of use:
http://www.google.com/url?sa=3DD&q=3Dhttps://www.ibm.com/developerworks/myd=
eveloperworks/wikis/home%3Flang%3Den_US%23/wiki/DB2%2520for%2520LUW%2520App=
lication%2520Enablement/page/PLSQL%2520Profiler&usg=3DAFQjCNEOV8T4GT-te1x6w=
EjqjrdlB7VRmg

(I hope this link works, otherwise he had posted it a short while
ago.)

What else comes to my mind is a debug table and a debug function,
something like insert into dbg values (current timestamp, message).

PL/SQL should have a debug function built in if you are on V9.7.

Sorry, mainly guesses here.

06-Sep-10 03:33 AM

Hello,

[...]

I am not thinking about performance at this point, but may come in handy
later on.



Yes, but can such inserts be done outside of the currently running
transaction? - Otherwise I suppose that no messages will end up in the
message-table?



That's interesting. I suppose this relates to DBMS_OUTPUT which seems to
be exactly what I was looking for. Thanks.

--
Troels

06-Sep-10 06:42 AM

Hello,


Hmm. DBMS_OUTPUT requires that Oracle compatibility be enabled :-(

Is Oracle compatibility enabled in all DB2 LUW 9.7 editions, or does it
require a special licence?

--
Troels

06-Sep-10 10:28 AM

o

It depends on which specific compatibility feature you want to use.
DB2 Enterprise Edition is the only version that supports the full
spectrum.
Regarding DBMS_OUTPUT (and other system-defined modules), this is
supported in Workgroup Edition but not in Personal Edition or Express(-
C) Edition, see:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=3D/com=
.ibm.db2.luw.sql.rtn.doc/doc/c0053670.html

You may also want to checkout this forum-post on DeveloperWorks:
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=3D14485419&t=
start=3D0

HTH

--
Jeroen

07-Sep-10 03:17 AM

One thing you can do is create a global temporary table, and have
your
procedure insert records into the GTT.  Then, simply query your GTT
to
see your output...

07-Sep-10 08:24 AM

Troels,

DBMS_OUTPUT is supported without compatibility vector.
Also if you search for my name on developerworks you will find an
article on SQL PL "tracing".
Out of the box it only logs entry/exit/error, but I also provided a DATA
function which you can embed as printf.

There was some snafu in DB2 9.7.0 for Windows with the library used
which was rectified no later than FP2. So if you want to go that route
ensure you are on the latest and greatest on Windows.

Also if you use the SQL PL / PL/SQL profiler you can execute it after
flushing the package cache to get an individual run.
(Probably not something to do on a production machine...)

Send me a note and I send you a more streamlined version of the profiler...

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab