[jboss-user] [JBoss jBPM] - Re: Maintenance and Repair Tools

michaelholtzman do-not-reply at jboss.com
Tue Aug 18 14:42:21 EDT 2009


anonymous wrote : Are 1,2 and 3 valid for all DBMS you use? Would be great if you could confirm that. 

Definitely Oracle and Sybase, don't have much experience with MSSql yet.

anonymous wrote : And maybe also what the default locking mechanisms are on specific database. 

Defaults:
Oracle - Row locking (as long as it has the indexes it needs to group the rows)
Sybase - Table locking (row locking must be specified on a table)
MSSql - Version 7+ defaults to row locking 

anonymous wrote : Surprises me that there is no slowdown btw. And I'm also curious what additional indexes you created that are not already on jBPM. Since I was under the impression that FK relations had an index. 

Surprised us too :)
I think that certain DB's automatically index the foreign key columns, but apparently Oracle, Sybase, and SqlServer do not. We added these indices:

  | /* OLF - Add indices on foreign keys */
  | create index JBPM_ACTION_EVENT_IDX	  on	JBPM_ACTION (EVENT_);
  | create index JBPM_ACTION_EXCEPT_IDX	  on  	JBPM_ACTION (EXCEPTIONHANDLER_);
  | create index JBPM_ACTION_PROCDEFN_IDX	  on  	JBPM_ACTION (PROCESSDEFINITION_);
  | create index JBPM_ACTION_TIMERACTION_IDX  on  	JBPM_ACTION (TIMERACTION_);
  | create index JBPM_ACTION_ACTIONDEL_IDX	  on  	JBPM_ACTION (ACTIONDELEGATION_);
  | create index JBPM_ACTION_REFACTION_IDX	  on  	JBPM_ACTION (REFERENCEDACTION_);
  | 
  | create index JBPM_BYTEARRAY_FILEDEFN_IDX  on  	JBPM_BYTEARRAY (FILEDEFINITION_);
  | create index JBPM_BYTEBLOCK_PROCFILE_IDX  on  	JBPM_BYTEBLOCK (PROCESSFILE_);
  | 
  | create index JBPM_COMMENT_TOKEN_IDX	  on  	JBPM_COMMENT (TOKEN_);
  | create index JBPM_COMMENT_TASKINST_IDX	  on  	JBPM_COMMENT (TASKINSTANCE_);
  | 
  | create index JBPM_DECISIONCOND_DEC_IDX	  on  	JBPM_DECISIONCONDITIONS (DECISION_);
  | 
  | create index JBPM_DELEGATION_PROCDEFN_IDX on  	JBPM_DELEGATION (PROCESSDEFINITION_);
  | 
  | create index JBPM_EVENT_PROCDEFN_IDX	  on  	JBPM_EVENT (PROCESSDEFINITION_);
  | create index JBPM_EVENT_NODE_IDX	  on	JBPM_EVENT (NODE_);
  | create index JBPM_EVENT_TRANSITION_IDX	  on  	JBPM_EVENT (TRANSITION_);
  | create index JBPM_EVENT_TASK_IDX	  on	JBPM_EVENT (TASK_);
  | 
  | create index JBPM_LOG_SRCENODE_IDX	  on	JBPM_LOG (SOURCENODE_);
  | create index JBPM_LOG_TOKEN_IDX		  on	JBPM_LOG (TOKEN_);
  | create index JBPM_LOG_OLDBYTE_IDX	  on	JBPM_LOG (OLDBYTEARRAY_);
  | create index JBPM_LOG_NEWBYTE_IDX	  on	JBPM_LOG (NEWBYTEARRAY_);
  | create index JBPM_LOG_CHILD_IDX		  on	JBPM_LOG (CHILD_);
  | create index JBPM_LOG_DESTNODE_IDX	  on	JBPM_LOG (DESTINATIONNODE_);
  | create index JBPM_LOG_TASKINST_IDX	  on	JBPM_LOG (TASKINSTANCE_);
  | create index JBPM_LOG_SWIMLANEINSTANCE_IDX on 	JBPM_LOG (SWIMLANEINSTANCE_);
  | create index JBPM_LOG_PARENT_IDX	  on	JBPM_LOG (PARENT_);
  | create index JBPM_LOG_NODE_IDX		  on	JBPM_LOG (NODE_);
  | create index JBPM_LOG_ACTION_IDX	  on	JBPM_LOG (ACTION_);
  | create index JBPM_LOG_VARINST_IDX	  on	JBPM_LOG (VARIABLEINSTANCE_);
  | create index JBPM_LOG_TRANSITION_IDX	  on 	JBPM_LOG (TRANSITION_);
  | 
  | create index JBPM_MODULEDEFN_STRTTASK_IDX on  	JBPM_MODULEDEFINITION (STARTTASK_);
  | create index JBPM_MODULEDEFN_PROCDEFN_IDX on  	JBPM_MODULEDEFINITION (PROCESSDEFINITION_);
  | 
  | create index JBPM_MODULEINST_TASKMGMT_IDX on  	JBPM_MODULEINSTANCE (TASKMGMTDEFINITION_);
  | create index JBPM_MODULEINST_PROCINST_IDX on  	JBPM_MODULEINSTANCE (PROCESSINSTANCE_);
  | 
  | create index JBPM_NODE_SUBPDEFN_IDX	  on  	JBPM_NODE (SUBPROCESSDEFINITION_);
  | create index JBPM_NODE_PROCDEFN_IDX	  on  	JBPM_NODE (PROCESSDEFINITION_);
  | create index JBPM_NODE_ACTION_IDX	  on	JBPM_NODE (ACTION_);
  | create index JBPM_NODE_DECISIONDEL_IDX	  on  	JBPM_NODE (DECISIONDELEGATION);
  | create index JBPM_NODE_SUPERSTATE_IDX	  on  	JBPM_NODE (SUPERSTATE_);
  | 
  | create index JBPM_PROCDEFN_STARTSTATE_IDX on  	JBPM_PROCESSDEFINITION (STARTSTATE_);
  | 
  | create index JBPM_PROCINST_PROCDEFN_IDX	  on  	JBPM_PROCESSINSTANCE (PROCESSDEFINITION_);
  | create index JBPM_PROCINST_ROOTTOKEN_IDX  on  	JBPM_PROCESSINSTANCE (ROOTTOKEN_);
  | create index JBPM_PROCINST_SUPERTOKEN_IDX on  	JBPM_PROCESSINSTANCE (SUPERPROCESSTOKEN_);
  | 
  | create index JBPM_RTACTION_PROCINST_IDX	  on  	JBPM_RUNTIMEACTION (PROCESSINSTANCE_);
  | create index JBPM_RTACTION_ACTION_IDX	  on  	JBPM_RUNTIMEACTION (ACTION_);
  | 
  | create index JBPM_TASK_TASKCTRL_IDX	  on  	JBPM_TASK (TASKCONTROLLER_);
  | create index JBPM_TASK_ASSIGNDEL_IDX	  on  	JBPM_TASK (ASSIGNMENTDELEGATION_);
  | create index JBPM_TASK_TASKNODE_IDX	  on  	JBPM_TASK (TASKNODE_);
  | create index JBPM_TASK_PROCDEFN_IDX	  on  	JBPM_TASK (PROCESSDEFINITION_);
  | create index JBPM_TASK_STARTSTATE_IDX	  on  	JBPM_TASK (STARTSTATE_);
  | create index JBPM_TASK_TASKMGMT_IDX	  on  	JBPM_TASK (TASKMGMTDEFINITION_);
  | create index JBPM_TASK_SWIMLANE_IDX	  on  	JBPM_TASK (SWIMLANE_);
  | 
  | create index JBPM_TACTRPOOL_POOLACTR_IDX  on  	JBPM_TASKACTORPOOL (POOLEDACTOR_);
  | create index JBPM_TACTRPOOL_TASKINST_IDX  on  	JBPM_TASKACTORPOOL (TASKINSTANCE_);
  | 
  | create index JBPM_TASKCTRL_TSKCNTDEL_IDX  on  	JBPM_TASKCONTROLLER (TASKCONTROLLERDELEGATION_);
  | 
  | create index JBPM_TASKINST_TASKMGMT_IDX	  on  	JBPM_TASKINSTANCE (TASKMGMTINSTANCE_);
  | create index JBPM_TASKINST_TOKEN_IDX	  on  	JBPM_TASKINSTANCE (TOKEN_);
  | create index JBPM_TASKINST_SWIMINST_IDX	  on  	JBPM_TASKINSTANCE (SWIMLANINSTANCE_);
  | create index JBPM_TASKINST_TASK_IDX	  on  	JBPM_TASKINSTANCE (TASK_);
  | 
  | create index JBPM_TIMER_TOKEN_IDX	  on	JBPM_TIMER (TOKEN_);
  | create index JBPM_TIMER_PROCINST_IDX	  on  	JBPM_TIMER (PROCESSINSTANCE_);
  | create index JBPM_TIMER_ACTION_IDX on  		JBPM_TIMER (ACTION_);
  | create index JBPM_TIMER_TASKINST_IDX	  on 	JBPM_TIMER (TASKINSTANCE_);
  | 
  | create index JBPM_TOKEN_PARENT_IDX	  on	JBPM_TOKEN (PARENT_);
  | create index JBPM_TOKEN_NODE_IDX	  on	JBPM_TOKEN (NODE_);
  | create index JBPM_TOKEN_PROCINST_IDX	  on  	JBPM_TOKEN (PROCESSINSTANCE_);
  | create index JBPM_TOKEN_SUBPROCINST_IDX	  on  	JBPM_TOKEN (SUBPROCESSINSTANCE_);
  | 
  | create index JBPM_TOKVARMAP_CNTXINST_IDX  on  	JBPM_TOKENVARIABLEMAP (CONTEXTINSTANCE_);
  | create index JBPM_TOKVARMAP_TOKEN_IDX	  on  	JBPM_TOKENVARIABLEMAP (TOKEN_);
  | 
  | create index JBPM_TRANSITION_NODETO_IDX	  on  	JBPM_TRANSITION (TO_);
  | create index JBPM_TRANSITION_PROCDEFN_IDX on  	JBPM_TRANSITION (PROCESSDEFINITION_);
  | create index JBPM_TRANSITION_NODEFROM_IDX on  	JBPM_TRANSITION (FROM_);
  | 
  | create index JBPM_VARACC_TASKCTRL_IDX	  on  	JBPM_VARIABLEACCESS (TASKCONTROLLER_);
  | create index JBPM_VARACC_SCRIPT_IDX	  on  	JBPM_VARIABLEACCESS (SCRIPT_);
  | create index JBPM_VARACC_PROCSTATE_IDX	  on  	JBPM_VARIABLEACCESS (PROCESSSTATE_);
  | 
  | create index JBPM_VARINST_TOKEN_IDX	  on  	JBPM_VARIABLEINSTANCE (TOKEN_);
  | create index JBPM_VARINST_TOKVARMAP_IDX	  on  	JBPM_VARIABLEINSTANCE (TOKENVARIABLEMAP_);
  | create index JBPM_VARINST_PROCINST_IDX	  on  	JBPM_VARIABLEINSTANCE (PROCESSINSTANCE_);
  | create index JBPM_VARINST_TASKINST_IDX	  on  	JBPM_VARIABLEINSTANCE (TASKINSTANCE_);
  | create index JBPM_VARINST_BYTEVALUE_IDX	  on  	JBPM_VARIABLEINSTANCE (BYTEARRAYVALUE_);
  | commit;
  | 

anonymous wrote : DBMS have tools to identify some low level issues like partially corrupt tables etc, but I get the impression that is not what you mean. 
We've enslaved -- I mean, enlisted our DBA's to do this for us, I was hoping for something that worked at a higher level.

View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4250250#4250250

Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4250250



More information about the jboss-user mailing list