The Commit step is the process where EDD records are inserted into an EQuIS database. After an EDD package is created in EDP, click the Commit button (located in the Package group on the Professional tab of EDP). Clicking either the icon or the drop-down arrow will display a drop-down menu with the various commit types. Select the desire commit type to commit the EDD package. When the commit step is attempted, ST_EDD_BATCH.COMMIT_TYPE and EDD_ROWS is then populated. EDD_ROWS will be populated with 0 if the EDD failed and no records were committed to the database. If the EDD was loaded via Enterprise EDP (or via Professional and the EarthSoft.EDP.Pro.RegisterFile ST_CONFIG setting exists), a new record is added to ST_FILE_REGISTRATION. This table populates the Enterprise EDP: EDD List form.
Click the drop-down selection above to go to the commit type documentation.
See below for an example of what data is loaded to the database based on the commit type selected.
Commit Type |
Does |
Does NOT |
Description |
|---|---|---|---|
Inserts new records. |
Replace any existing records. |
This option inserts new records, but does not replace existing records. If a record with matching primary keys already exists in the database, the new record remains in the package in EDP. An error is reported, because the new record cannot be inserted, and the existing record is not modified. |
|
Replace existing null records. |
Replace existing records that are not null. Insert new records. |
This option merges data if it already exists in the database, but prevents new records from inserting. Only null values in the existing record will be replaced by the incoming non-null values. If the primary key does not match what is already been loaded, the data will not be committed. |
|
Replace existing records, but not if new data is null. |
Replace existing records if new data is null. Insert new records. |
This option merges data if it already exists in the database, but prevents new records from inserting. All values in an existing record will be replaced by new incoming values, unless the incoming value is null. Any null values will not replace non-null data. If the primary key does not match what has already been loaded, the data will not be committed. |
|
Replace existing records. |
Insert new records. |
This option replaces data if it already exists in the database, but prevents new records from inserting. If a record with matching primary keys already exists in the database, it will be completely overwritten by the new incoming record. If the primary key does not match what has already been loaded, the data will not be committed. |
|
Replace existing null records. Inserts new records. |
Replace existing records that are not null. |
This option inserts new records and replaces existing null values with incoming values. This type of commit will only replace values in an existing record if the existing value is null. |
|
Replace existing records, but not if new data is null. Inserts new records. |
Replace existing records if new data is null. |
This option inserts new records and updates both null and non-null values in existing records. All values in an existing record will be replaced by new incoming values, unless the incoming value is null. Any null values in the incoming data will not replace non-null data in the existing records. |
|
Replace existing records. Inserts new records. |
This option inserts new records and replaces existing records. If a record with matching primary keys already exists in the database, it will be completely overwritten by the new incoming record. |
||
Commit type is set for each table in the format's custom handler. For example, a format may be designed to commit data to DT_SAMPLE and DT_FIELD_SAMPLE tables through Insert and Merge, but all other tables would use Insert and Update. |
These commit types are also available under the EDP section in the EQuIS Enterprise Administration Setup tab. This will determine the default commit type for EDDs being processed by EQuIS Enterprise. During the EDP Commit process, there is an option to cancel during the transaction. Learn more here.
*Note: The Insert and Replace commit type will replace existing records with NULL values with the exception of the refvals format. This is due to the fact that when the format is opened, it loads lookup values from the database. Then, during the Create step, those values are combined with the EDD values. Values that appear null in the EDD will not appear as nulls in the database after the Commit step. |
In this example a portion of the MW-01 data already exists in the database. The EDD contains some data already in the database, adds new values to the MW-01 location, and adds a new location, MW-02. The breakdown of what data is added to the database for each commit type is shown in this example.
Data |
Location |
Total Depth |
Driller |
Loc_Type |
|---|---|---|---|---|
Exists in Database |
MW-01 |
|
E. Miller |
MW |
In EDD |
MW-01 |
30 |
J. Smith |
|
MW-02 |
25 |
J. Smith |
|
This is the data in the database after the EDD is loaded based on the Commit Type selected:
Commit Type |
SYS_LOC_CODE |
TOTAL_DEPTH |
DRILLER |
LOC_TYPE |
Description |
|---|---|---|---|---|---|
Insert Only |
MW-01 |
|
E. Miller |
MW |
The new MW-01 in the EDD does NOT get inserted and the old record remains the same. The MW-02 is "new" and inserted in its entirety. |
MW-02 |
25 |
J. Smith |
|
||
Merge Only |
MW-01 |
30 |
E. Miller |
MW |
Total Depth is merged in for MW-01, and everything else remains the same. The MW-02 is "new" and is not inserted. |
Update Only |
MW-01 |
30 |
J. Smith |
MW |
Total Depth is filled in for MW-01 and Driller is updated with the new value. The MW-02 is "new" and is not inserted. |
Replace Only |
MW-01 |
30 |
J. Smith |
|
Everything is replaced for MW-01 (except the primary key), including the new NULL value for Loc_Type. The MW-02 is "new" and is not inserted. |
Insert and Merge |
MW-01 |
30 |
E. Miller |
MW |
Total Depth is merged in for MW-01, and everything else remains the same. The MW-02 is "new" and inserted in its entirety. |
MW-02 |
25 |
J. Smith |
|
||
Insert and Update |
MW-01 |
30 |
J. Smith |
MW |
Total Depth is filled in for MW-01 and Driller is updated with the new value. The MW-02 is "new" and inserted in its entirety. |
MW-02 |
25 |
J. Smith |
|
||
Insert and Replace |
MW-01 |
30 |
J. Smith |
|
Everything is replaced for MW-01 (except the primary key), including the new NULL value for Loc_Type. The MW-02 is "new" and inserted in its entirety. |
MW-02 |
25 |
J. Smith |
|
Legend:
New values added to database.
In the event that an error occurs at the Create or Commit step in EDP Professional, the process should be canceled and the errors resolved before completing the data load.
In the event that an error occurs at the Create or Commit step in Enterprise EDP, only the Administrator for that Workflow Agent (also referred to as the FileProcess in EDP) is notified.
The administrator is determined by looking in ST_SERVICE_RESERVATION.USER_ID for the FileProcess agent. That USER_ID value is then joined to the ST_USER table to find the user account (i.e. the administrator for that workflow agent).
The data submitter (and others subscribed) will only see the "accept" or "reject" notice if the data either passes, or if there are errors with the data in the EDD itself. The data submitter (and others subscribed) are not notified of create or commit errors, because they are related to either the format or the database, and technically are not the submitters responsibility.
Tip: Format Controls Which Commit Types are Available: Users with write access to the ST_CONFIG table can now limit which "Commit" types are available for a given EDP format. For example, the following records in ST_CONFIG will only allow "Insert and Replace" to be shown: CONFIG_SECTION CONFIG_KEY OBJECT_TYPE OBJECT_VALUE STRING_VALUE EarthSoft.EDP.EddCustomHandler.ShowUpdateMode EFWEDD commitmode Merge false EarthSoft.EDP.EddCustomHandler.ShowUpdateMode EFWEDD commitmode None false EarthSoft.EDP.EddCustomHandler.ShowUpdateMode EFWEDD commitmode Update false
Control of Commit types for a specified format as well as the name of the Commit type, can now be implemented via a format's custom handler (i.e., *.vb file). |