1. Patching Data¶
The patch framework corrects data that has already been historised in the PSA layer. The patched data is loaded like a normal delivery, so the original data is preserved by technical historisation. All patch metadata is stored in the schema REDPAT.
A patch consists of six steps:
Start the patch.
Add the objects to be patched.
Add the users who may edit the patch data.
Prepare the patch: the data is copied into a temporary patch schema.
Edit the data in the patch schema.
Apply the patch: the changed data is historised into the PSA.
The status of a patch is shown in REDPAT.PAT_STATUS, the status of its objects in REDPAT.PAT_OBJECT_STATUS.
1.1. Start a Patch¶
Start a patch with API_REDPAT.START; the only parameter is a description.
Example: Start a Patch
1EXECUTE SCRIPT API_REDPAT.START(
2 'test_patch' -- p_description
3);
The new patch with its patch ID is stored in REDPAT.PAT_PATCHES:
PAT_ID |
PAT_SCHEMA |
PAT_DBUSER |
PAT_DESCRIPTION |
PAT_STARTED |
PAT_PREPARED |
PAT_APPLIED |
PAT_ABORTED |
|---|---|---|---|---|---|---|---|
25 |
PAT_25 |
TESTUSER |
test_patch |
2024-12-19 15:36:05 |
(null) |
(null) |
(null) |
The patch status is STARTED.
1.2. Add Objects to Be Patched¶
Add the PSA tables to be patched with API_REDPAT.ADD_OBJECT. Only PSA tables and their error tables (_ERR) can be patched.
A WHERE condition restricts the data copied into the patch schema; without a condition, the currently valid rows of all BDomains are copied.
Example: Patch the Rows of Jane in PSA_PERS.PERSON
1EXECUTE SCRIPT API_REDPAT.ADD_OBJECT(
2 25 -- p_patch_id
3 ,'PSA_PERS' -- p_obj_schema
4 ,'PERSON' -- p_obj_table
5 ,'FirstName = ''Jane''' -- p_where
6);
Mind the doubled quotes for string literals within the condition. The object is stored in REDPAT.PAT_OBJECTS:
PAT_OBJECT_ID |
PAT_ID |
PAT_SRC_OBJ_SCHEMA |
PAT_SRC_OBJ_NAME |
PAT_OBJ_SCHEMA |
PAT_OBJ_NAME |
PAT_WHERE |
|---|---|---|---|---|---|---|
7 |
25 |
PSA_PERS |
PERSON |
PAT_25 |
PERSON |
FirstName = ‘Jane’ |
The object status is ADDED. Objects can be removed again with API_REDPAT.REMOVE_OBJECT, and the condition can be changed with API_REDPAT.MODIFY_OBJECT.
1.3. Add Patch Users¶
Define which users may edit the patch data with API_REDPAT.ADD_USER. Users can only be added while the patch is in status STARTED; they are removed with API_REDPAT.REMOVE_USER.
Example: Add the Patch User TESTUSER
1EXECUTE SCRIPT API_REDPAT.ADD_USER(
2 25 -- p_patch_id
3 ,'TESTUSER' -- p_user
4);
The user is stored in REDPAT.PAT_USERS:
PAT_ID |
PAT_USER |
|---|---|
25 |
TESTUSER |
1.4. Prepare the Patch¶
Prepare the patch with API_REDPAT.PREPARE.
This starts an asynchronous background process, which creates the patch schema PAT_<ID> and copies the data of all objects (according to their conditions) into patch tables.
It may take up to one minute until the process starts.
Example: Prepare the Patch
1EXECUTE SCRIPT API_REDPAT.PREPARE(
2 25 -- p_patch_id
3);
The patch status changes to PREPARING and, when all objects are prepared, to PREPARED. The object status changes from ADDED to PREP_SCHEDULED, PREPARING, and PREPARED.
When the patch is PREPARED, the patch users are granted the role R_PAT_<ID>, which allows them to change the patch tables.
Note
In status PREPARING or PREPARED, a patch can be set back to STARTED with API_REDPAT.RESTART, e.g. to correct a faulty condition or to add objects. The role is revoked from the patch users, and the patch has to be prepared again afterwards.
1.5. Edit the Data¶
The patch users change the data in the patch tables of the schema PAT_<ID> (shown in the column PAT_SCHEMA of REDPAT.PAT_STATUS) with UPDATE, INSERT, or DELETE.
Example: Correct the Salary of Jane
1UPDATE PAT_25.PERSON
2 SET SALARY = 3600.00
3 WHERE FIRSTNAME = 'Jane';
Important
A DELETE in a patch table does not end the history of a row, because a patch always acts like an incremental load. To end the validity of a row, set its column RED_ACTIVE to false.
If Exasol auditing is enabled, all DML statements of the patch users are recorded in REDPAT.PAT_AUDIT_LOGS.
1.6. Apply the Patch¶
When all changes are done, apply the patch with API_REDPAT.APPLY. The access to the patch schema is revoked immediately, and an asynchronous background process loads the patched data like any other TSA delivery into the PSA.
Example: Apply the Patch
1EXECUTE SCRIPT API_REDPAT.APPLY(
2 25 -- p_patch_id
3);
The patch status changes from PREPARED to APPLY, APPLY_SCHEDULED, APPLYING, and finally APPLIED; the object status changes to APPLIED. The patch schema and its role are dropped. The corrections appear in the PSA as new versions of the rows.
1.7. Abort a Patch¶
A patch can be aborted with API_REDPAT.ABORT in any status except APPLIED.
The patch schema and its role are dropped; the patch status changes to ABORTED.
Example: Abort the Patch
1EXECUTE SCRIPT API_REDPAT.ABORT(
2 25 -- p_patch_id
3);
Aborted patches and the patch data of applied patches are removed by a housekeeping job after a retention period (parameter pat_retention, default 90 days); the patch metadata is kept.