Hi All, I need to get the history of the EPV values changed from the DB2 LSW tables. Could someone help me if they have tried this before ?

Question

I need to get the details of the EPV values changed on a particular date, the current value and the default value along with the variable name, EPV name, process name.

I have tried checking the tables but didnt find any column for the current value :(

Kindly help.

Answer 1

EPV values and their history are in the BPMDB (Process Server database, not the PDW):

-- EPV definitions (design time) and the runtime value sets per snapshot
select e.name as epv_name, v.name as variable_name, v.default_value
  from lsw_epv e join lsw_epv_var v on v.epv_id = e.epv_id;

-- runtime values with their validity (what Process Admin > Manage EPVs shows)
select s.name as epv_name, ev.name as variable_name, ev.value, ev.effective_time, ev.updated_by, ev.updated_on
  from lsw_epv_runtime_snapshot s join lsw_epv_runtime_value ev on ev.epv_runtime_snapshot_id = s.epv_runtime_snapshot_id
 where ev.updated_on >= '2021-08-01' order by ev.updated_on desc;

Table names differ slightly by release (LSW_EPV_RUNTIME_* on 8.5.x / 8.6, LSW_EPV_* plus a history table on BAW 20+). The quickest way to find the right ones on your level: select table_name from syscat.tables where tabname like 'LSW_EPV%%' (Db2) and look at the columns. Every change made in Process Admin inserts a new row with an effective time; the row with the latest effective time that is in the past is the current value, the design-time default is in the design table.

The process (app) name: EPVs belong to a snapshot; join lsw_snapshot (snapshot_id, name) and lsw_project (project_id, name, short_name) through the EPV's snapshot / project id column.

Supported alternative (BAW 20+): the Operations REST API returns the same history without touching tables - GET /ops/std/bpm/containers/{acronym}/versions/{snapshot}/epvs?optional_parts=previous_values,current_values,future_values - and the JavaScript API exposes the current values as tw.epv.<EPV>.<variable>.

References

• Operations REST API (EPV resources)
• BAW documentation - exposed process values