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 ?

Mark Barinstein · Accepted Answer · 2022-12-06 07:56:07Z

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...

edited Dec 6, 2022 at 7:56

answered Dec 5, 2022 at 6:40

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rômulo Sorato Over a year ago

I still dont get it, i'm just trying to save data, not select. How can i solve that?

Mark Barinstein Over a year ago

@RômuloSorato I've edited the answer with an example.

Rômulo Sorato Over a year ago

Hi, you answer help us solve the problem, we have just change to row organized table since this is an transactional aplication and row organized tables is more used to data analytics in data warehouses.Change to row organized tables solve the problem. Thanks again

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related