1. Data Management Guide
  2. DB.XML Transaction Codes

DB.XML Transaction Codes

When importing db.xml with any of the methods described in this document, there are six transaction codes currently available:

  • I (Insert): Only inserts are performed. If the data already exists in the database, you will get primary key errors.
  • IU (Insert/Update): Attempts to insert data. If a primary key violation occurs, it updates the data. No delete statements are generated.
  • RC (Replace Children): Deletes all child data corresponding to the top level parent, updates the top level parent, and reinserts the child data. This mode allows for a complete replacement of a data object.
  • II (Insert/Ignore Duplicates): Attempts to insert a new record but only if it does not exist already.
  • D (Delete): Deletes the object and all associated child data.
    Note: As this is essentially a ‘cascade delete’ of an object this should only be used with particular care. For example, deleting a LOCATION may result in deleting SHIPMENTs which are related to that LOCATION.

CSVUtil supports a “replace children” (rc) command when processing multi-table CSV files. The rc command will recursively delete all child records and re-insert them from the CSV file. This is useful when you want to completely replace the rows that comprise a complex multi-table business object.

The “C.” table sets are used to determine the hierarchical parent/child relationships.

For example:

TABLE_SET_DETAIL
TABLE_SET,TABLE_NAME
C.GEO_HIERARCHY,GEO_HIERARCHY_DETAIL
C.GEO_HIERARCHY_DETAIL,HNAME_COMPONENT

The C.GEO_HIERARCHY table set indicates that the GEO_HIERARCHY_DETAIL table is a child of geo_hierarchy.

The C.GEO_HIERARCHY_DETAIL table set indicates that the HNAME_COMPONENT table is a child of geo_hierarchy_detail.

Examples:

If you submit the following multi-table CSV file with the “rc” command, all rows in the GEO_HIERARCHY_DETAIL table relating to the GUEST.COUNTRY hierarchy would be deleted (since there are none to replace those records in the CSV file).

$HEADER
GEO_HIERARCHY_DETAIL
GEO_HIERARCHY_GID,HNAME_COMPONENT_GID,HLEVEL,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE
GEO_HIERARCHY
GEO_HIERARCHY_GID,GEO_HIERARCHY_XID,RANK,COUNTRY_CODE3_GID,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS..'
$BODY
GEO_HIERARCHY
"GUEST.COUNTRY","COUNTRY",10,,"GUEST","DBA.ADMIN",2001-08-30 11:01:56.0,"DBA.ADMIN",2005-10-26 14:44:50.0

If you submit the following multi-table CSV file with the “rc” command, there will be two records in the geo_hierarchy_detail table relating to the GUEST.COUNTRY hierarchy, regardless of how many rows were there previously.

$HEADER
GEO_HIERARCHY_DETAIL
GEO_HIERARCHY_GID,HNAME_COMPONENT_GID,HLEVEL,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE
GEO_HIERARCHY
GEO_HIERARCHY_GID,GEO_HIERARCHY_XID,RANK,COUNTRY_CODE3_GID,DOMAIN_NAME,INSERT_USER,INSERT_DATE,UPDATE_USER,UPDATE_DATE
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS..'
$BODY
GEO_HIERARCHY
"COUNTRY","COUNTRY",10,,"PUBLIC","DBA.ADMIN",2001-08-30 11:01:56.0,"DBA.ADMIN",2005-10-26 14:38:33.0
GEO_HIERARCHY_DETAIL
"COUNTRY","COUNTRY_CODE3",1,"PUBLIC","DBA.ADMIN",2001-08-30 11:01:56.0,,
GEO_HIERARCHY_DETAIL
"COUNTRY","CITY",2,"PUBLIC","DBA.ADMIN",2001-08-30 11:01:56.0,,

Sample command line:

java glog.database.admin.CSVUtil -command rc -connectionId localdb -dataDir . -dataFileName geo_hierarchy.csv

In version 5.5 and later, the “rc” command is available after you upload a CSV file via the integration manager.