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 ?
CodePudding user response:
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...