Allowing Concurrent Inserts
Data is “inserted” into an X100 table by either an insert or an append. An insert goes to the PDTs, which reside in memory. An append is written directly to the table files on disk. (PDTs eventually are written to disk through update propagation.)
The default behavior is: Single row inserts go through the PDTs (insert), INSERT AS SELECT goes directly to disk (append).
If you need to do concurrent INSERT AS SELECTs into the same table, you must use the SET INSERTMODE ROW statement. If you do not set this option, by default, a second commit will fail with the following error:
E_VW1120 Error committing transaction: conflict appending to table - concurrent append or update.
When you SET INSERTMODE to ROW in both sessions, however, the second commit will succeed.
INSERTMODE can also be set to BULK, which means data is appended directly to disk, which does not allow concurrent inserts. If you have two sessions and at least one of them has INSERTMODE set to BULK, then an error will occur.
Note: The SET INSERTMODE statement also applies to the MERGE operation.
You can control the mode to use for inserts and merges into X100 tables at the installation level by setting the DBMS configuration parameter x100_insertmode in config.dat using CBF.
Last modified date: 04/03/2024