SQL updates and mapped tables
Tables that are mapped from nonrelational and relational databases
to the metadata catalog contain some mapping constructs with special meaning
that you need to be aware of when you update mapped tables.
Mappings that contain multiple records
With some connectors, such as IMS and CA-IDMS, you can map multiple records or segments in a database path to a single logical table in the metadata catalog. When you update these mappings with SQL update statements, updates are applied to only the last record that is mapped in the path.
Positions of inserted records
The position of inserted records is determined by any values that are supplied for records other than the last record in a mapped database path.
Record inserts with full and partial mapping
Database record mappings can include columns for all or only part of the underlying database record. When you insert records that contain partial mappings, the areas of the database record that are not mapped are initialized to binary zeros.
Updates and deletions of database records
The UPDATE and DELETE statements apply changes to all database records that meet the WHERE clause criteria specified. You need to specify WHERE criteria correctly, because unqualified updates and deletes change all instances of the target record in the database.
Updates and NULL records
UPDATE statements only update existing records in the database.
Mappings that contain record arrays
SQL updates are not supported on tables that contain record array mappings of OCCURS clauses. If you need to update records that contain multiple occurrences of data items, each occurrence must be mapped as a separate column to allow updates.
Group items and overlapping fields
Do not issue INSERT statements on table mappings that contain group items or overlapping fields. Inserts on tables that map group items or overlapping fields can produce unpredictable results due to initialization of unspecified columns in the underlying database record.
Update processing recommendations
When you set up update processing, consider using separate table mappings to define security and using the NULL IS parameter to define the string length for a column.