Stardust/Knowledge Base/Infrastructure System Administration Maintenance/SQL

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 );

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 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)