DB2 SQL Error: SQLCODE=-1667, SQLSTATE=42858 while save in db2 database

Question

I have a simple spring boot application that just takes a message from kafka and save to a db2 database:

@Component
@Slf4j
public class KafkaConsumer {


    private final PortalONERepository portalONERepository;

    private final ObjectMapper objectMapper;

    @Autowired
    public KafkaConsumer(PortalONERepository portalONERepository, ObjectMapper objectMapper) {
        this.portalONERepository = portalONERepository;
        this.objectMapper = objectMapper;
    }

    @KafkaListener(topics = "**")
    @Transactional("transactionManager")
    public void consumeEventHubMessage(String consumerMessage) {
        log.info("Received message from kafka queue: {}", consumerMessage);

        //Convert string message to java object
        try {
            DocumentONE[] documentOne = objectMapper.readValue(consumerMessage, DocumentONE[].class);
            //Salvar cada mensagem no db2
            portalONERepository.saveAll(Arrays.asList(documentOne));

        } catch (JsonProcessingException e) {
            log.error("Error receiving message: " + e.getMessage());
        }
    }
}

But when the application tries to save the object, it throws the following error:

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-1667, SQLSTATE=42858, SQLERRMC=D_ODS_STREAM_PORTALONE.TR_RECEP;ORGANIZE BY COLUMN;FINAL|NEW|OLD TABLE, DRIVER=4.31.10

What is could causing the problem ?

Answer 1

SQL1667N.

SQL1667N The operation failed because the operation is not supported with the type of the specified table. Specified table: table-name. Table type: table-type. Operation: operation-keyword.

Your code probably tries to issue SELECT from a data-change operation, which seems isn't supported for column-organized tables.

Update

Steps to reproduce.

CREATE TABLE TEST_COL (I INT NOT NULL GENERATED ALWAYS AS IDENTITY, J INT) 
ORGANIZE BY 
COLUMN
--ROW
;

-- Simple INSERT statement
INSERT INTO TEST_COL (J) VALUES 0;

-- Used often to get inserted row(s) values with a single statement.
-- For example, to get generated ID of the inserted row.
-- Doesn't work with COLUMN-ORGANIZED tables,
-- but works with ROW-ORGANIZED tables.
-- Returns the same error as in the question on a column-organized table.
SELECT * FROM NEW TABLE (INSERT INTO TEST_COL (J) VALUES 1);

The result of the last statement on a row-organized table.

I	J
2	1

Again, your code generates some statement under the hood similar to the last one. I have no idea why and how to make it not do so.
Some spring boot expert might probably help with this...