DM_DELETE_TRACKING
Column Section | Top of Page |
Table-level Detail
Description: | Data Management Delete Tracking |
Definition: | This table is used to identify deleted rows from select Millennium tables. This data is used by the Crawler to update corresponding tables in the Cloud. |
Table Type: | ACTIVITY |
Column Detail - DM_DELETE_TRACKING
Column Name | Type | Null? | Definition |
---|---|---|---|
DATA_TEXT | VARCHAR(4000) | Y | Used in the small chance they need to store a character based parent id value, or if we need to store 2 or more pieces of key data (e.g. a composite key). |
DM_DELETE_TRACKING_ID | DOUBLE | N | Unique generated number that identifies a single row on the DM_DELETE_TRACKING table. |
LAST_UTC_TS | DATETIME(6) | Y | LAST_UTC_TS is the timestamp when the row in the table was last updated or inserted. The timestamp is populated by a trigger on the table. This column is primarily used by the Project Go Millennium crawlers to determine when they need to send new or updated rows to the cloud |
PARENT_ENTITY_ID | DOUBLE | Y | The primary key ID of the 'grouper' table that this table is a part of. Usually this is the foreign key column from the table in column TABLE_NAME pointing back to the parent table in column PARENT_ENTITY_NAME. |
PARENT_ENTITY_NAME | VARCHAR(255) | Y | This is a grouping name. Crawlers are configured into table `groups¿, or concepts. For example the PERSON crawler watches for changes on PERSON, PERSON_PATIENT, PERSON_ALIAS, etc.). The PARENT_ENTITY_NAME for a delete on PERSON_ALIAS will be PERSON, not PERSON_ALIAS. For some tables already having the parent_entity_name concept built into them (like ADDRESS or PHONE), this column is used to help identify which parent the delete belongs to as well. |
PURGE_APPL_NBR | DOUBLE | N | In order to improve Millennium+ crawler's performance, we need to prevent it from crawling Purge deletes. This is for the purpose of tracking Purge deletes, |
TABLE_NAME | VARCHAR(30) | Y | The name of the table the row was deleted from. |
TABLE_PK_VALUE | DOUBLE | Y | The PK value corresponding to the PK of whatever table is in TABLE_NAME. Improve process performance by grabbing this column instead of having to grab and parse DATA_TEXT (when possible). Will only contain the PK if the PK is a numeric single column PK. |
TXN_ID_TEXT | VARCHAR(200) | Y | The Oracle transaction_id value for one or more DML statements in a session before a commit occurred. This column is used to tie a row on this table back to the Millennium table that inserted (using a trigger) the row . |
UPDT_APPLCTX | DOUBLE | N | The application context number from the record info block. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic. |
UPDT_CNT | DOUBLE | N | Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic. |
UPDT_DT_TM | DATETIME | N | The date and time the row was last inserted or updated. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic. |
UPDT_ID | DOUBLE | N | The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic. |
UPDT_TASK | DOUBLE | N | The registered (assigned) task number for the process that inserted or updated the row. The UPDT family of columns are typically used for housekeeping and external system process and should never be depended on for solution specific logic. |
DM_MPLUS_TABLE_EXCLUSION
Column Section | Top of Page |
Table-level Detail
Description: | Data Management Millennium Plus Table Exclusion |
Definition: | This table will contain the names of tables that we do not want set up for Millennnium+ crawling. This table is only processed by a readme. It has no default row and a non-standard PK. The PK is TABLE_NAME. |
Table Type: | REFERENCE |
Column Detail - DM_MPLUS_TABLE_EXCLUSION
Column Name | Type | Null? | Definition |
---|---|---|---|
ACTIVE_IND | DOUBLE | N | The table row is active or inactive. A row is generally active unless it is in an inactive state such as logically deleted, combined away, pending purge, etc. |
EXCLUSION_COMMENT_TXT | VARCHAR(200) | Y | Additional information on why the table was excluded. |
EXCLUSION_REASON_FLAG | DOUBLE | Y | The flag value indicates why the table was excluded.1 - Excluded Data Model2 - Performance3 - Obsolete Table4 - Excluded Table Type5 - Other |
TABLE_NAME | VARCHAR(30) | N | The name of the table that should not be set up with the crawler schema. This is the primary key of the table. |
UPDT_APPLCTX | DOUBLE | N | The application context number from the record info block. |
UPDT_CNT | DOUBLE | N | Set to 0 on insert. Incremented by 1 on update. Used to recognize update conflict where data in a row updated by one application is at risk of being lost by a second application attempting to update the row. |
UPDT_DT_TM | DATETIME | N | The date and time the row was last inserted or updated. |
UPDT_ID | DOUBLE | N | The person_id of the person from the personnel table (prsnl) that caused the last insert or update of the row in the table. |
UPDT_TASK | DOUBLE | N | The registered (assigned) task number for the process that inserted or updated the row. |
DM_TXN_TRACKING
Column Section | Top of Page |
Table-level Detail
Description: | Data Management Transaction Tracking |
Definition: | This table tracks transactions from all tables being crawled as part of the Millennium+ platform. It utilizes the Oracle System Change Number to manage the processing of the data appropriately and accurately. The rows on this table are populated by triggers on Millennium tables. |
Table Type: | ACTIVITY |
Column Detail - DM_TXN_TRACKING
Column Name | Type | Null? | Definition |
---|---|---|---|
APPL_CONTEXT_NBR | DOUBLE | Y | Similar to the purge_appl_nbr on DM_DELETE_TRACKING, we can engineer triggers to watch for and propagate application contexts at the transaction level (e.g. think combines, or purges). Might facilitate design patterns having this at a tranaction level. |
DEL_IND | DOUBLE | Y | Indicates if a delete operation fired the trigger for the transaction. |
OWNER_NAME | VARCHAR(30) | Y | The owner of the table. Defaults to V500. |
ROW_SCN | DOUBLE | Y | Updated with ORA_ROWSCN by the background job. It will get initialized to 0 on insert. ORA_ROWSCN is an Oracle pseudo column that contains the Oracle System Change Number for a transaction. The SCN will be at a block level unless the table is created with option ROWDEPENDANCIES, then it is at the row level. DM_TXN_TRACKING table does have the ROWDEPENDANCIES option. |
TABLE_NAME | VARCHAR(30) | Y | The table_name where the transaction occurred. |
TXN_ID_TEXT | VARCHAR(200) | Y | The Oracle transaction_id value for one or more DML statements in a session before a commit occurred. This column is used to tie a row on this table back to the Millennium table that inserted (using a trigger) the row . |
DM_TXN_TRACKING_STG
Column Section | Top of Page |
Table-level Detail
Description: | Data Management Transaction Tracking Staging |
Definition: | To eliminate continuous insert/update write patterns in DM_TXN_TRACKING an intermediate 'staging' transaction-tracking table is needed as a bridge between clinical trigger write activity and crawler service read activity. Queries will be always be doing a Full Table Scan and then DELETING all the data that was queried. |
Table Type: | ACTIVITY |
Column Detail - DM_TXN_TRACKING_STG
Column Name | Type | Null? | Definition |
---|---|---|---|
APPL_CONTEXT_NBR | DOUBLE | Y | Similar to the purge_appl_nbr on DM_DELETE_TRACKING, we can engineer triggers to watch for and propagate application contexts at the transaction level (e.g. think combines, or purges). Might facilitate design patterns having this at a tranaction level. |
DEL_IND | DOUBLE | Y | Indicates if a delete operation fired the trigger for the transaction. |
INST_ID | DOUBLE | Y | Represents the Oracle instance number from where the INSERT came from. This will be used in the future for partitioning purposes. |
OWNER_NAME | VARCHAR(30) | Y | The owner of the table. Defaults to V500. |
TABLE_NAME | VARCHAR(30) | Y | The table_name where the transaction occurred. |
TXN_ID_TEXT | VARCHAR(200) | Y | The Oracle transaction_id value for one or more DML statements in a session before a commit occurred. This column is used to tie a row on this table back to the Millennium table that inserted (using a trigger) the row . |