Debugging database procedures

Discussion created by ca.portal.admin on Mar 29, 2007
Latest reply on Mar 30, 2007 by ca.portal.admin
What techniques are available for debugging database
procedures? I am talking here about the kind that are tied to
a record via the schema description.

We are getting D003 errors when trying to use DEBUG. Is DEBUG
supposed to work with database procedures?

My personal favorite technique for debugging online programs is
""displaying"" to the log via the WTL command. But you are not
supposed to make DML calls from a database procedure.

Any suggestions would be most welcome.

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna
IDMS Public Discussion Forum


Re: Debugging database procedures

I did say ' This of course is only works if only you are calling the

May be I should have made it clear that this was ONLY for
testing/debugging when you have a single user you is invoking the
procedure and that when running 'for real' .

I do not wish to get into a long discussion on this but I must add the

Defining an ISA and STORAGE PROTECT for a database procedure has no

These options are checked/actioned by IDMSDC program management
(RHDCPCTL) which is not involved when IDMSDBMS calls a database

What IDMSDBMS does when it needs to call a db procedure is check the
variable portion of the PC70 VPCENTR to see if it already has the entry
point for the procedure. If not it issues a #LOAD and populates VPCENTR.
It then issues a #CALL to the entry point.

What #CALL does a stack check and then a branch to CSAMPMOD.

CSAMPOD checks to see if a call to mode manger is required and if not it
just braches to the procedure. In mode manger is required it does the MP
stuff and then branch to the procedure. In either case no RHDCPCTL

If you are running MP all DB procedures should have mode of DB if not it
can seriously impact performance I know because I got it wrong once.

Before I get the response saying that ISA and storage protect do apply
to DB procedures I did a little test.

I have procedure define with storage protected on, ISA 256 and
System has protect on.
The procedure on entry stores R11 (should be ISA) into an area with
itself which if storage protect was on would cause a S0C4.
My procedure does not abend but runs as expected.
Displaying memory I can see that R11 on entry was 0 so no ISA.

Last point is the Times called count displayed when you do a DCMT D PR
for a db procedure this is in fact the number of time that a #LOAD was
issued for it and not the times the IDMSDBMS has called it.

I do have a procedure that counts internally the times it is called. I
run my test program to drive the database procedure and DCMT count for
it is incremented by 1 where as the internal count is incremented by

If you are running MP mode and updating non re-entrant storage as I am
then be sure to us the Compare and Swap instruction to ensure single
thearding of the update to the storage.