What is the purpose and usage of ATOMIC and NON-ATOMIC clause in multi row insert?

The ATOMIC and NON ATOMIC clauses are used with the multi-row insert. ATOMIC is always processed by default if any of the options is not given. The ATOMIC clause states that if there is a failure while inserting any one row during multi-row insertion then the entire query will be failed and all the inserts will be rolled back.

The NON ATOMIC clause is just the opposite of the ATOMIC clause. It is used when we have to insert and process all the rows individually in a multi-row insert. For example, this option can be used like below.

MOVE 50 TO MAX-ROWS
EXEC SQL
   INSERT INTO ORDERS (ORDER_ID, ORDER_DATE)
   VALUES(:ORDER-ID, :ORDER-DATE) FOR
   :MAX-ROWS ROWS NON ATOMIC
END-EXEC

Mandalika

Updated on 15-Sep-2020 11:03:38

• Related Questions & Answers
• Atomic variables in Java
• Multiple atomic updates using MongoDB?
• Purpose and usage of ROW-ID and SEQUENCE in a DB2
• What is the purpose and usage of “FOR UPDATE OF” clause in a COBOL-DB2 program
• What is the purpose and usage of “WHERE CURRENT OF” clause in a COBOL-DB2 program?
• Atomic Hydrogen Arc Welding – Working, Advantages, Disadvantages and Applications
• What is the purpose and usage of SCROLLABLE CURSOR in COBOLDB2 program?
• What is the usage of host variables in case of multi row fetch?
• What is the usage and purpose of DCLGEN and host variables used in COBOL-DB2 program
• What is the purpose and usage of SQLCODE within the SQLCA in a COBOL-DB2 program
• Purpose and usage of SAVEPOINT in COBOL-DB2 program
• Purpose and usage of subqueries in DB with the help of an example
• What is the purpose of a register in a CPU and what are special purpose registers?
• How to deal with error “$ operator is invalid for atomic vectors” in R?
• Usage and example of Multi-index and Index-only access path in DB2