MBPM: eProcedure and eAssembly maintenance

Ryan Saul

Hi,

In my dev environment the eProcedure table and eAssembly table quickly grow to over 20gb in a busy month.

Once it is this large, even the MBPM admin page cannot clear these without timing out.

It would be nice to disable logging to these tables at each deployment.

Otherwise a better method to delete or maintain these entries would be great.

Cheers.

Find more posts tagged with

BPM

Comments

Nils Wyldbore

You can use the code below to clear up all entries in the database accept for the latest version of your processes:

DELETE FROM eAssembly WHERE eAssemblyID NOT IN (
    SELECT
        eAssemblyID
    FROM
        eAssembly
    INNER JOIN (
        SELECT
            eProcedureName,
            MAX(eLoadedTime) AS [eLoadedTime]
        FROM
            eAssembly
        GROUP BY
            eProcedureName
        ) AS Latest ON Latest.eProcedureName = eAssembly.eProcedureName AND Latest.eLoadedTime = eAssembly.eLoadedTime
    )

I tend to use this on a frequent basis to clear down my development environment. I also, at the end of each day, delete previous versions of all my processes via the admin tool (once certain I've got a working build).

Hope this helps.

Nils.

Paul Lawrence

What we really need is a feature in Designer where we could right click a version from the Repository tab in Designer and delete! It's not a very unified design environment when you have to go into this tool and that tool just to do some basic and necessary housekeeping tasks. The admin tool just doesn't cut it!

Nils Wyldbore

Agreed, the admin tool does not appear to have any work done on it since 9.0 and is still as, erm, 'feature rich'. The one in 7.6 was at least more useful.

Personally, I tend to do everything via the database direcly. Certainly once a system gets to a certain size.

Ryan Saul

Thanks... after clearing up the eAssembly I also cleanup the eProcedure with the following script (which is rather inefficient but does the trick).

Support have logged an enhancement request for this to be better managed in MBPM administration.

delete from eprocedure

where

eProcedureName+convert(nvarchar(5), eVersion) not in (

select ep.eProcedureName+convert(nvarchar(5), ep.eVersion) from eprocedure ep

inner join (     

select max(eversion) ver, eprocedurename from eprocedure

group by eprocedurename ) as latest on latest.eprocedurename=ep.eprocedurename

and latest.ver=ep.eversion)

BPS_Pid

I've wrote a query (Oracle) that extract the eassembly records related to eprocedure entry; so you could determine which records in eassembly haven't got eprocedure entry. (so i think you could delete all rows that haven't got eprocedure row).

This situation is caused by admin tools, because when i delete a project version, it leave the record in eassembly table and isn't possible to clear this table a tool.

It would be useful to know, from Opentext/Metastorm staff, if all these custom SQLs (wrote to manage repository) are approved/supported.

And another question: when we'll have a usable and "all inclusive" Administration Tool?

select a.eassemblyid,
       a.eprocedurename,
       a.eversion eassembly_version,
       a.eloadedtime eassembly_eloadedtime,
       decode(p.eprocedurename,null,'ROW NOT PRESENT','OK') eprocedure_row_status,
       p.eversion eprocedure_version,
       p.eloadedtime eprocedure_eloadedtime
from eprocedure p
     right join
     (select eassembly.*,to_char(substr(substr(eassemblyname,instr(eassemblyname,',',1,1)+2,instr(eassemblyname,',',1,2)-instr(eassemblyname,',',1,1)-2),length('Version=')+1)) eversion from eassembly -- version number from eassembly.eassemblyname
     ) a
on p.eprocedurename=a.eprocedurename and
   a.eversion like to_char(p.eversion)||'.%'
order by a.eprocedurename, a.eloadedtime asc;