Database Procedures

Discussion created by ca.portal.admin on Apr 18, 2008
Latest reply on Apr 22, 2008 by ca.portal.admin

I know that you are not supposed to put DML statements in a database procedure (though I don't know the reason). What I am wondering is.... would the same restriction apply if you placed a call in the procedure and had the DML performed in the called module be allowed.

The situation is this:

We currently have a database procedure that sets a field to 'y' before store and modify of our person record.

There are two batch programs that walk all the person records to find the ones with the 'y' in field. (one retrieval one to reset the field to spaces). At present we have approximately 1.13 million person records and on a daily basis between 2,000 - 6,000 have been updated with the 'y'.

We would like to only have to read/update the records marked with the 'y' and are thinking of using an Optional Manual index to do this (connecting the record when the 'y' is added and disconnecting it when the batch program removes the 'y'). However, the no DML rule is hindering this idea. We of course realize we could add the connect to every program that stores/modifies the record. But we are big enough and have enough turnover in staff that I do not feel comfortable with the idea of leaving the connection to the memories of the programming staff.

So, if anyone can enlighten me as to why no DML in a database procedure and/or if the called module would be an alternative it would be greatly appreciated. Of course if you have any other ideas as to how to identify these records I am open to that as well.

Scott Van Der Heyden
State of Iowa Dept. of Human Services
IDMS Public Discussion Forum


Re: Database Procedures
"I want to agree with Chuck but add a caveat.

Many years ago I asked CA why DB procedures could not issue a DML and
the straight answer is that IDMSDBMS is not recursive (it cannot call

Why is this? As Chuck says it is the VB50. This contains IDMSDBMS work
areas, variables and internal save areas so if IDMSDBMS calls itself
using THE SAME VB50 then the VB50 areas for the first IDMSDBMS execution
will get corrupted by the second call.

A simple example. The first fields in the VB50 are the user parameter

So IDMSDBMS is called for a STORE the parameters represent this.

During processing the STORE a database procedure is called that issues a
CONNECT. The VB50 parameter list now represents a CONNECT.

Now the procedure returns to IDMSDBMS STORE logic that may get confused
by the fact that the VB50 parameters say that it is performing a
CONNECT. Would really confuse tech support if they get a dump of
IDMSDBMS at this point.

Another simple example is the field VIBVRBNO that represents the current
verb being processed. In the above example on return from the database
procedure VIBVRBNO would say CONNECT but IDMSDBMS would be in STORE

DML can be issued from a database procedure if the DML uses a different
VB50 to the one in use when it is called. This is why PUT QUEUE and
WTL(This may need to read message dictionary) are OK as they use a
different run unit.

I have written procedures that issue DML but the DML is issued using a
different run unit (and therefore VB50) to the one that called the

Also I have seen systems, as suggest by Chris, where the procedure posts
a task to wake up to perform DML. Again the task that is woken up will
have it's own run unit and so it's own VB50.