Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "Stardust/Knowledge Base/Infrastructure System Administration Maintenance/SQL"

m (→‎Introduction)
(→‎Introduction)
 
(14 intermediate revisions by 3 users not shown)
Line 11: Line 11:
 
If you feel the need to access the Stardust audit trail database schema directly then this is probably resulting from a specific requirement. Are there requirements which are not covered by the API which you are trying to fulfill with this approach? Please let the Stardust team know via the [http://www.eclipse.org/forums/index.php/f/225/ Forum]. We are glad to learn about your requirements so we can suggest an approach or initiate a change or feature request that will improve Stardust.  
 
If you feel the need to access the Stardust audit trail database schema directly then this is probably resulting from a specific requirement. Are there requirements which are not covered by the API which you are trying to fulfill with this approach? Please let the Stardust team know via the [http://www.eclipse.org/forums/index.php/f/225/ Forum]. We are glad to learn about your requirements so we can suggest an approach or initiate a change or feature request that will improve Stardust.  
  
−
<br> NOTE to authors: Please make sure to specify the version on which a command was executed<br> [All version numbers &lt; 7.0 refer to the Infinity Process Platform (IPP)]<br>
+
<br> NOTE to authors: Please make sure to specify the version on which a command was executed<br> [All version numbers &lt; 7.0 refer to the Infinity Process Platform (IPP)]<br>  
  
−
Refer documentation for details about the underlying database schema. [https://infinity.sungard.com/documentation/ipp/6.0/topic/ag.carnot.docs.dev/html/handbooks/operation/audit-trail/ag-appendix-audittrail-1.htm]
+
[http://help.eclipse.org/kepler/index.jsp?topic=/org.eclipse.stardust.docs.dev/html/handbooks/operation/audit-trail/ag-appendix-audittrail-1.htm Refer online documentation for more details about the underlying database schema.]
  
 
== Process Data  ==
 
== Process Data  ==
Line 40: Line 40:
 
   pi.OID = sdv.processInstance;
 
   pi.OID = sdv.processInstance;
  
−
</source>
+
</source>  
  
−
==== Retrieving structured non-initialized data<br>  ====
+
==== Retrieving uninitialized Structured Data<br>  ====
  
 
(Versions: 5.3.x)<br>  
 
(Versions: 5.3.x)<br>  
Line 60: Line 60:
 
   dv.data = sd.data and
 
   dv.data = sd.data and
 
   sd.OID not in ( select sdv2.XPATH from structured_data_value sdv2 where sdv2.processinstance = pi.OID );
 
   sd.OID not in ( select sdv2.XPATH from structured_data_value sdv2 where sdv2.processinstance = pi.OID );
 +
 +
</source><br>
 +
 +
==== Retrieving all indexed Structured Data Values of a Process Instance<br>  ====
 +
 +
(Versions: 5.3.x)<br>
 +
 +
<source lang="SQL">
 +
SELECT d.id, d.name, sd.xpath, sdv.*
 +
FROM PROCESS_INSTANCE pi
 +
INNER JOIN PROCESS_DEFINITION pd ON (pi.processdefinition = pd.oid and pi.model = pd.model)
 +
INNER JOIN STRUCTURED_DATA_VALUE sdv on (pi.scopeprocessinstance = sdv.processinstance)
 +
INNER JOIN STRUCTURED_DATA sd on (sd.oid = sdv.xpath and sd.model = pi.model)
 +
INNER JOIN DATA d on (sd.data = d.oid and sd.model = d.model)
 +
INNER JOIN MODEL m on (pi.model = m.oid)
 +
WHERE pi.oid = ?;
  
 
</source>  
 
</source>  
  
−
<br>
+
Note that values that do not fit into the 128 character STRING_VALUE columns of the DATA_VALUE or STRUCTURED_DATA_VALUE table will end up as multiple rows in the STRING_DATA table. This case is not covered by the SQL statements above.
 +
 
 +
== Process Instances  ==
 +
 
 +
==== Terminated Root Process Instances  ====
 +
 
 +
(Version 6.0.2)<br> The statement returns all terminated, meaning completed or aborted, process instances, e.g. as candidates for archiving.<br> <source lang="SQL">
 +
SELECT rootProcessInstance FROM process_instance WHERE state IN (1, 2) AND rootProcessInstance=OID
 +
</source>
 +
 
 +
==== Process Instances per Process Model grouped by State ====
 +
 
 +
(Version 5.3.17,7.1.0)<br> The statement returns the number of process instances per process model OID grouped by the state of the process instance (0: active, 1: aborted, 2: completed, 3: interrupted). This is for instance helpful to identify candidates for archiving or process models.<br> <source lang="SQL">
 +
SELECT m.oid, pi.state, COUNT(pi.oid)
 +
FROM model m
 +
LEFT OUTER JOIN process_instance pi ON (m.oid = pi.model)
 +
GROUP BY m.oid, pi.state
 +
ORDER BY m.oid, pi.state;
 +
</source>
  
 
== Process History  ==
 
== Process History  ==
  
−
(Versions: 5.2.x & 5.3.x)
+
(Versions: 5.2.x &amp; 5.3.x)  
  
−
===== Retrieving process instance history =====
+
==== Retrieving Process Instance History  ====
  
−
Following SQL statements (Oracle specific date formatting used) allow to retrieve activity instance information that belongs to a particular process instance and its hierarchy (root process with all subprocesses).
+
Following SQL statements (Oracle specific date formatting used) allow to retrieve activity instance information that belongs to a particular process instance and its hierarchy (root process with all subprocesses).  
−
+
 
−
The first statement can be used if the access point is the root process instance OID.
+
The first statement can be used if the access point is the root process instance OID.  
  
 
<source lang="SQL">
 
<source lang="SQL">
Line 93: Line 127:
 
ORDER BY ai.starttime asc, ai.oid asc;
 
ORDER BY ai.starttime asc, ai.oid asc;
  
−
</source>
+
</source>  
  
−
The second statement can be used if the access point is somewhere in the mid of the process hierarchy.
+
The second statement can be used if the access point is somewhere in the mid of the process hierarchy.  
  
 
<source lang="SQL">
 
<source lang="SQL">
Line 115: Line 149:
 
ORDER BY ai.starttime asc, ai.oid asc;
 
ORDER BY ai.starttime asc, ai.oid asc;
  
−
</source>
+
</source>  
  
 
Both queries return a process history, showing all activity instances with start and complete times as well as duration in ms.  
 
Both queries return a process history, showing all activity instances with start and complete times as well as duration in ms.  
  
−
===== Retrieving process instance history by process data predicate =====
+
==== Retrieving Process Instance History by Process Data Predicate  ====
  
−
The next example shows how to query by process data to find the process instance in question.
+
The next example shows how to query by process data to find the process instance in question.  
  
 
<source lang="SQL">
 
<source lang="SQL">
Line 159: Line 193:
 
ORDER BY ai.starttime asc, ai.oid asc;
 
ORDER BY ai.starttime asc, ai.oid asc;
  
 +
</source>
 +
 +
<br>
 +
 +
== Log Entry Table  ==
 +
 +
(Version: 5.2.5)<br>
 +
 +
==== Grouping Log Entries by Subject<br>  ====
 +
 +
<source lang="SQL">
 +
SELECT SUBSTR(SUBJECT, 1,30), count(*) from LOG_ENTRY group by SUBSTR(SUBJECT, 1,30)
 
</source>
 
</source>

Latest revision as of 05:41, 28 June 2013

Introduction

Four all purposes but troubshooting we advise against accessing the Stardust audit trail database schema directly. Some reasons are:

  • Potential side effects: The access pattern is not covered by QA and may cause unwanted side effects (locking, …)
  • Separation of concerns: This is a widely accepted best practice. Accessing the db directly breaks the paradigm and creates a dependency between your code and the DB schema. The stability of the db schema is not guarantied. The API encapsulates the schema and makes potential changes transparent to the client code.
  • Knowledge: You have to build up detailed knowledge of our DB schema and the support does not cover the development of SQL statements for this purpose.
  • Performance: The API is optimize to access the database effectively and efficiently. It contains optimizations like e.g. the model cache that improve performance and reduce db workload. Custom direct DB operations circumvent those mechanisms.
  • Effort: additional effort to re-implement existing functionality

If you feel the need to access the Stardust audit trail database schema directly then this is probably resulting from a specific requirement. Are there requirements which are not covered by the API which you are trying to fulfill with this approach? Please let the Stardust team know via the Forum. We are glad to learn about your requirements so we can suggest an approach or initiate a change or feature request that will improve Stardust.


NOTE to authors: Please make sure to specify the version on which a command was executed
[All version numbers < 7.0 refer to the Infinity Process Platform (IPP)]

Refer online documentation for more details about the underlying database schema.

Process Data

Retrieving structured data initialized with non-null or null value (plus non-leaf data in the data structure)

(Versions: 5.3.x)

SELECT
  pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) AS "XPATH",
  SUBSTR(sdv.STRING_VALUE,1,20) AS "STRING_VALUE",
  sdv.NUMBER_VALUE
FROM
  PROCESS_INSTANCE pi,
  PROCESS_DEFINITION pd,
  DATA_VALUE dv,
  STRUCTURED_DATA sd,
  STRUCTURED_DATA_VALUE sdv
WHERE
  pd.OID = pi.processDefinition AND
  pi.OID = dv.processInstance AND
  dv.DATA = sd.DATA AND
  sd.OID = sdv.XPATH AND
  pi.OID = sdv.processInstance;

Retrieving uninitialized Structured Data

(Versions: 5.3.x)

SELECT
  pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) AS "XPATH"
FROM
  PROCESS_INSTANCE pi,
  PROCESS_DEFINITION pd,
  DATA_VALUE dv,
  STRUCTURED_DATA sd
WHERE
  pd.OID = pi.processDefinition AND
  pi.OID = dv.processInstance AND
  dv.DATA = sd.DATA AND
  sd.OID NOT IN ( SELECT sdv2.XPATH FROM structured_data_value sdv2 WHERE sdv2.processinstance = pi.OID );

Retrieving all indexed Structured Data Values of a Process Instance

(Versions: 5.3.x)

SELECT d.id, d.name, sd.xpath, sdv.*
FROM PROCESS_INSTANCE pi 
INNER JOIN PROCESS_DEFINITION pd ON (pi.processdefinition = pd.oid AND pi.model = pd.model)
INNER JOIN STRUCTURED_DATA_VALUE sdv ON (pi.scopeprocessinstance = sdv.processinstance)
INNER JOIN STRUCTURED_DATA sd ON (sd.oid = sdv.xpath AND sd.model = pi.model)
INNER JOIN DATA d ON (sd.DATA = d.oid AND sd.model = d.model)
INNER JOIN MODEL m ON (pi.model = m.oid)
WHERE pi.oid = ?;

Note that values that do not fit into the 128 character STRING_VALUE columns of the DATA_VALUE or STRUCTURED_DATA_VALUE table will end up as multiple rows in the STRING_DATA table. This case is not covered by the SQL statements above.

Process Instances

Terminated Root Process Instances

(Version 6.0.2)
The statement returns all terminated, meaning completed or aborted, process instances, e.g. as candidates for archiving.
SELECT rootProcessInstance FROM process_instance WHERE state IN (1, 2) AND rootProcessInstance=OID

Process Instances per Process Model grouped by State

(Version 5.3.17,7.1.0)
The statement returns the number of process instances per process model OID grouped by the state of the process instance (0: active, 1: aborted, 2: completed, 3: interrupted). This is for instance helpful to identify candidates for archiving or process models.
SELECT m.oid, pi.state, COUNT(pi.oid)
FROM model m
LEFT OUTER JOIN process_instance pi ON (m.oid = pi.model)
GROUP BY m.oid, pi.state
ORDER BY m.oid, pi.state;

Process History

(Versions: 5.2.x & 5.3.x)

Retrieving Process Instance History

Following SQL statements (Oracle specific date formatting used) allow to retrieve activity instance information that belongs to a particular process instance and its hierarchy (root process with all subprocesses).

The first statement can be used if the access point is the root process instance OID. 

SELECT ai.oid, 
       ai.processinstance, 
       pd.name AS "PROCESS",
       ad.name AS "ACTIVITY",
       to_char(to_date('1970-01-01','YYYY-MM-DD') + ( ai.starttime / (1000 * 60 * 60 * 24) ), 
          'YYYY-MM-DD HH24:MI:SS') "STARTTIME", 
       to_char(to_date('1970-01-01','YYYY-MM-DD') + ( ai.lastmodificationtime / (1000 * 60 * 60 * 24) ), 
          'YYYY-MM-DD HH24:MI:SS') "LAST MODIFICATION", (ai.lastmodificationtime - ai.starttime) "DURATION (ms)"
 
FROM activity_instance ai 
INNER JOIN procinst_scope pis ON (ai.processinstance = pis.processinstance)
INNER JOIN activity ad ON (ai.activity = ad.oid AND ai.model = ad.model)
INNER JOIN process_definition pd ON (ad.processdefinition = pd.oid AND ad.model = pd.model)
WHERE pis.rootprocessinstance = ?
ORDER BY ai.starttime ASC, ai.oid ASC;

The second statement can be used if the access point is somewhere in the mid of the process hierarchy. 

SELECT ai.oid, 
       ai.processinstance, 
       pd.name AS "PROCESS",
       ad.name AS "ACTIVITY",
       to_char(to_date('1970-01-01','YYYY-MM-DD') + ( ai.starttime / (1000 * 60 * 60 * 24) ), 
          'YYYY-MM-DD HH24:MI:SS') "STARTTIME", 
       to_char(to_date('1970-01-01','YYYY-MM-DD') + ( ai.lastmodificationtime / (1000 * 60 * 60 * 24) ), 
          'YYYY-MM-DD HH24:MI:SS') "LAST MODIFICATION", (ai.lastmodificationtime - ai.starttime) "DURATION (ms)"
 
FROM activity_instance ai 
INNER JOIN procinst_hierarchy pih ON (ai.processinstance = pih.subprocessinstance)
INNER JOIN activity ad ON (ai.activity = ad.oid AND ai.model = ad.model)
INNER JOIN process_definition pd ON (ad.processdefinition = pd.oid AND ad.model = pd.model)
WHERE pih.processinstance = ?
ORDER BY ai.starttime ASC, ai.oid ASC;

Both queries return a process history, showing all activity instances with start and complete times as well as duration in ms.

Retrieving Process Instance History by Process Data Predicate

The next example shows how to query by process data to find the process instance in question. 

SELECT ai.oid, 
       ai.processinstance, 
       pd.name AS "PROCESS",
       ad.name AS "ACTIVITY",
       to_char(to_date('1970-01-01','YYYY-MM-DD') 
          + ( ai.starttime / (1000 * 60 * 60 * 24) ), 'YYYY-MM-DD HH24:MI:SS') "STARTTIME", 
       to_char(to_date('1970-01-01','YYYY-MM-DD') 
          + ( ai.lastmodificationtime / (1000 * 60 * 60 * 24) ), 'YYYY-MM-DD HH24:MI:SS') "LAST MODIFICATION", 
       (ai.lastmodificationtime - ai.starttime) "DURATION (ms)"
 
FROM activity_instance ai 
INNER JOIN procinst_scope pis ON (ai.processinstance = pis.processinstance)
INNER JOIN activity ad ON (ai.activity = ad.oid AND ai.model = ad.model)
INNER JOIN process_definition pd ON (ad.processdefinition = pd.oid AND ad.model = pd.model)
WHERE pis.rootprocessinstance = 
   (
      SELECT DISTINCT pi.rootProcessInstance
      FROM data_value dv
      INNER JOIN process_instance pi ON (dv.processInstance = pi.oid)
      INNER JOIN process_definition pd ON (pi.processdefinition = pd.oid AND pi.model = pd.model)
      INNER JOIN DATA dd ON (dv.DATA = dd.oid AND dv.model = dd.model)
      INNER JOIN data_value dv2 ON (dv2.processInstance = pi.oid)
      INNER JOIN DATA dd2 ON (dv2.DATA = dd2.oid AND dv2.model = dd2.model)
      WHERE dd.id = 'AccountNumber'
      AND dv.string_value = '1111'
      AND dd2.id = 'RequestDate'
      AND dv2.number_value < (to_date('2011-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
          - to_date('1970-01-01','YYYY-MM-DD HH24:MI:SS')) * (24 * 60 * 60 * 1000)
      AND dv2.number_value > (to_date('2011-10-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
          - to_date('1970-01-01','YYYY-MM-DD HH24:MI:SS')) * (24 * 60 * 60 * 1000)
      AND pd.id = 'MyProcessID'
   )
ORDER BY ai.starttime ASC, ai.oid ASC;


Log Entry Table

(Version: 5.2.5)

Grouping Log Entries by Subject

SELECT SUBSTR(SUBJECT, 1,30), COUNT(*) FROM LOG_ENTRY GROUP BY SUBSTR(SUBJECT, 1,30)
Retrieved from "https://wiki.eclipse.org/index.php?title=Stardust/Knowledge_Base/Infrastructure_System_Administration_Maintenance/SQL&oldid=342037"

This page was last modified 05:41, 28 June 2013 by Simone Seurer. Based on work by Monika Funke, Robert Emsbach and Andreas Muller and others.

Back to the top