3 Replies Latest reply on Nov 6, 2006 9:18 AM by ksdeger

    Deleting data from jbpm tables in database


      We have a system that is successfully using JBPM. It creates a very large number of process instances a day, so we would like to delete them when they have completed to free up space in the database.

      I am going to write code using GraphSession.deleteProcessInstance to do this, but until I can get that done I was hoping to put out a quick SQL script to do the clean up. I have the SQL script completed, but there is something odd going on. The jbpm_bytearray table has a large number of rows with no values in the name_ or filedefinition_ columns. Also, the id_'s for these rows do not appear in the jbpm_variableinstance bytearrayvalue_ column. I looked at the jbpm hibernate cfg.xml files, and I can't figure out where these rows are coming from, or how I would know to which process instance they belongs.

      Just as an example, I ran a single file through our JBPM flow, and after the clean up, which removes all the jbpm_bytearray and jbpm_byteblock rows which have valid references in the jbpm_variableinstance table, there were still 276 new rows in the jbpm_blockbyte table.

      I cannot leave this data to accumulate as it eventually kills the database.

      Any suggestions? We are using JBoss 4.0.1sp1 and JBPM 3.0.2.

        • 1. Re: Deleting data from jbpm tables in database

          Bytearrays....nothing odd, by design. The processdefinition and all accompanying data is stored that way in blocks of configurable size. The other rows are always related to a 'first row'. There are kind of sequencenumbers in there (cannot tell you the details now since I do not have access to any code)

          Just look a little better, you'll see the relation

          • 2. Re: Deleting data from jbpm tables in database

            oh, and btw. the bytearrays are afaik related to a process definition, not process instance

            • 3. Re: Deleting data from jbpm tables in database

              I've examined the sql code that creates the tables and all the relationships when the jbpm database is created. The only relationships I see for bytearray, based on foreign keys are:

              jbpm_bytearray's filedefinition_ has a foreign key relationship to jbpm_moduledefinition (but filedefinition_ is empty in the rows in question)

              jbpm_variableinstance's bytearrayvalue_ has a foreign key relationship to jbpm_bytearray

              jbpm_byteblock's processfile_ has a foreign key relationship to jbpm_bytearray

              (Ok, there are also some relationships from the log, but that's not important right now).

              jbpm_variableinstance has a relationship to a jbpm_processinstance but not to jbpm_processdefiniton, exept indirectly.

              What am I missing? I've looked at the hbm.xml files and found the same relationships and nothing else. Did something change with Jbpm 3.1?