At 10:04 Thursday, 06.06.2002 +0200, you wrote:

> Hi,
> I use DML Cache in my apps.
> It works fine, but the IBO$DMLCACHE table grows and grows ...
> I believe, it is save to delete periodically "old" records from this
> table. Am I right?

Yes. You can automate the deletion of old DMLCache records
with using a trigger. This is my personal solution:

I have a Table TBL_DBCTRL( FldName VARCHAR, IntValue INTEGER )
and the following trigger.

<Code>

CREATE TRIGGER BI_IBO$DMLCACHE FOR IBO$DMLCACHE
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE var_CacheCounter INTEGER;
DECLARE VARIABLE var_LastDMLID INTEGER;
BEGIN
/* make sure that fields to insert are not null */
IF (NEW.DMLID IS NULL) THEN BEGIN
NEW.DMLID = GEN_ID( GEN_IBO$DMLCACHE, 1 );
END
IF (NEW.DMLUSER IS NULL) THEN BEGIN
NEW.DMLUSER = USER;
END
IF (NEW.DMLTIMESTAMP IS NULL) THEN BEGIN
NEW.DMLTIMESTAMP = 'NOW';
END

/* simple counter in TBL_DBCTRL that gets incremented
every time a new record gets inserted. If the counter doesn't
exists then insert it */
SELECT INTVALUE
FROM TBL_DBCTRL
WHERE FLDNAME = 'CACHECOUNTER'
INTO :var_CacheCounter;
IF (var_CacheCounter IS NULL) THEN BEGIN
var_CacheCounter = 0;
INSERT INTO TBL_DBCTRL (FLDNAME, INTVALUE)
VALUES ('CACHECOUNTER', :var_CacheCounter);
END

IF (var_CacheCounter >= 300) THEN BEGIN
/* to save processing time, do this only every 300 inserted
DMLCache records: get last DMLId, then delete all records
except the newest 1000 and, finally, reset the CacheCounter
in TBL_DBCTRL back to 0 */
SELECT MAX(DMLID) FROM IBO$DMLCACHE INTO :var_LastDMLID;
DELETE FROM IBO$DMLCACHE
WHERE (DMLID < :var_LastDMLID - 1000);
UPDATE TBL_DBCTRL
SET INTVALUE = 0
WHERE (FLDNAME = 'CACHECOUNTER');
END ELSE BEGIN
/* no delete, just increment CacheCounter */
UPDATE TBL_DBCTRL
SET INTVALUE = :var_CacheCounter + 1
WHERE (FLDNAME = 'CACHECOUNTER');
END
END ^ /* BI_IBO$DMLCACHE */

</Code>

You can customize how often the deletion of DMLCache
records happens with this line.
e.g. every 300 inserted records:
IF (var_CacheCounter >= 300) THEN

You can specify how many DMLCache records to keep
when deleting. I do this since I don't know if my client apps
have processed all the records already.
e.g. keep newest 1000 records:
DELETE FROM IBO$DMLCACHE
WHERE (DMLID < :var_LastDMLID - 1000);

This works very well for me. Currently I have 1062 records in
IBO$DMLCache with DMLId from 107597 to 108658.

HTH,
Markus

PS: Helen: if you find this snippet really usefull :) you
can put it on the IBO and/or FB website.